def TestInput(data): fdp = atheris.FuzzedDataProvider(data) try: cell.absolute_coordinate(fdp.ConsumeString(20)) cell.cols_from_range(fdp.ConsumeString(20)) cell.column_index_from_string(fdp.ConsumeString(20)) cell.coordinate_from_string(fdp.ConsumeString(20)) cell.coordinate_to_tuple(fdp.ConsumeString(20)) cell.get_column_interval(fdp.ConsumeInt(10),fdp.ConsumeInt(10)) cell.get_column_letter(fdp.ConsumeInt(10)) cell.quote_sheetname(fdp.ConsumeString(20)) cell.range_boundaries(fdp.ConsumeString(20)) cell.range_to_tuple(fdp.ConsumeString(20)) cell.rows_from_range(fdp.ConsumeString(20)) except ValueError as e: error_list = [ "is not a valid coordinate range", "Invalid column index", "is not a valid column name", "is not a valid coordinate or range", "Value must be of the form sheetname!A1:E4" ] expected_error = False for error in error_list: if error in str(e): expected_error = True if not expected_error: raise e except CellCoordinatesException: pass
def copy_xl(src, col1, col2, col3): """ Copy files by list in excel document. Parameters: src (Pathlib object): The pathlib object of source xlsx file. col1 (str): Column containing source path + filename string. col2 (str): Column containing destination path + filename string. col3 (str): Column containing log data were copy was successful or not. """ wb = load_workbook(src, read_only=False) # workbook ws = wb["Sheet1"] # workbook sheet activate src_column = column_index_from_string(col1) dst_column = column_index_from_string(col2) log_column = column_index_from_string(col3) for row in range(1, ws.max_row + 1): try: if ws.cell(row, src_column).value is not None: if not Path(ws.cell(row, dst_column).value).parent.exists(): Path(ws.cell(row, dst_column).value).parent.mkdir(parents=True, exist_ok=True) sh.move(ws.cell(row, src_column).value, ws.cell(row, dst_column).value) ws.cell(row, log_column, "successful copy") else: pass except Exception as error: log.exception(f"error copy {get_column_letter(src_column)}{row} => {get_column_letter(dst_column)}{row} {error}", exc_info=False) ws.cell(row, log_column, f"error copy {get_column_letter(src_column)}{row} => {get_column_letter(dst_column)}{row}") wb.save(src) wb.close()
def _p_tittle(sheet, processes: list): """ 性能报告tittle设置 :param sheet: :param processes: :return: """ cur_letter = 'C' for process in processes: if cur_letter == 'C': sheet[cur_letter + '1'] = process sheet[cur_letter + '2'] = '内存' next_letter = cell_utils.get_column_letter( cell_utils.column_index_from_string(cur_letter) + 1) sheet[next_letter + '2'] = 'CPU' sheet.merge_cells(cur_letter + '1:' + next_letter + '1') cur_letter = cell_utils.get_column_letter( cell_utils.column_index_from_string(next_letter) + 1) else: sheet[cur_letter + '1'] = process sheet[cur_letter + '2'] = 'PID' mid_letter = cell_utils.get_column_letter( cell_utils.column_index_from_string(cur_letter) + 1) sheet[mid_letter + '2'] = '内存' end_letter = cell_utils.get_column_letter( cell_utils.column_index_from_string(mid_letter) + 1) sheet[end_letter + '2'] = 'CPU' sheet.merge_cells(cur_letter + '1:' + end_letter + '1') cur_letter = cell_utils.get_column_letter( cell_utils.column_index_from_string(end_letter) + 1)
def writeNoData(sh, tableList): def writeND(br, er, ec): # writes "No Data" above Header/Data Segment & "End of Column" for row in range(br, er): for col in range(1, ec): if sh.cell(row, col).value in [None, '', 0]: sh.cell(row, col).value = "No Data" # ADDED ALL FONT COLORS TO GREEN FOR NOW sh.cell(row, col).font = Font(color="FFFFFF", size=12) # determines what is endCol if col + 1 == endCol: sh.cell(row, col + 1).value = "End Column" sh.cell(row, col + 1).font = Font(color="FFFFFF", size=12) index = 0 for sheetRange in tableList: endCol = column_index_from_string(sheetRange['endData'][0]) + 1 endRow = sheetRange['beginHeader'][1] if index == 0: beginRow = 1 else: beginRow = tableList[index - 1]['endData'][1] + 1 writeND(beginRow, endRow, endCol) index += 1 beginRow = tableList[len(tableList) - 1]['endData'][1] + 1 endRow = sh.max_row + 1 endCol = column_index_from_string(tableList[len(tableList) - 1]['endData'][0]) + 1 writeND(beginRow, endRow, endCol)
def get_properties(wb,excel_index,parameter): headings_start_col = excel_index['Section or material properties col'] values_start_col = excel_index['Section or material values col'] start_row = excel_index['Properties start row'] if parameter == 'Material': ws = wb['Materials'] elif parameter == 'Section': ws = wb['Section Properties'] else: print('Input should be either "Material" or"Section"') #parameter = 'unknown' #if ws['A1'].value == 'Section #': # parameter = 'Section' #else: # parameter = 'Material' parameter_type = {}; current_property_col = headings_start_col; current_value_col = values_start_col; i = 1 while ws[current_property_col+str(1)].value is not None: current_row = start_row parameter_type[parameter+' '+str(i)]={} while ws[current_property_col + str(current_row)].value is not None: properties = {} properties_heading = ws[current_property_col + str(current_row)].value properties_value = ws[current_value_col + str(current_row)].value #enter the new entry into the index parameter_type[parameter+' '+str(i)][properties_heading] = properties_value current_row = current_row + 1 i += 1 current_property_col = get_column_letter(column_index_from_string(current_property_col)+3) current_value_col = get_column_letter(column_index_from_string(current_value_col)+3) return parameter_type
def get_panels(wb, excel_index): cur_panel_col = 'A' ws = wb['Panels'] panels = [] panel_num = 1 while ws[cur_panel_col + '1'].value is not None: xCol = get_column_letter(column_index_from_string(cur_panel_col) + 1) yCol = get_column_letter(column_index_from_string(cur_panel_col) + 2) zCol = get_column_letter(column_index_from_string(cur_panel_col) + 3) x1 = ws[xCol + '4'].value x2 = ws[xCol + '5'].value x3 = ws[xCol + '6'].value x4 = ws[xCol + '7'].value y1 = ws[yCol + '4'].value y2 = ws[yCol + '5'].value y3 = ws[yCol + '6'].value y4 = ws[yCol + '7'].value z1 = ws[zCol + '4'].value z2 = ws[zCol + '5'].value z3 = ws[zCol + '6'].value z4 = ws[zCol + '7'].value panels.append( Panel(num=panel_num, point1=[x1, y1, z1], point2=[x2, y2, z2], point3=[x3, y3, z3], point4=[x4, y4, z4])) panel_num += 1 cur_panel_col = get_column_letter( column_index_from_string(cur_panel_col) + 5) print('Read ' + str(len(panels)) + ' panels') return panels
def get_node_info(wb,excel_index,node_num_col,parameter): if parameter == 'Floor Bracing': ws = wb['Floor Bracing'] elif parameter == 'Bracing': ws = wb['Bracing'] elif parameter == 'Floor Plans': ws = wb['Floor Plans'] horiz_col = get_column_letter(column_index_from_string(node_num_col)+1) vert_col = get_column_letter(column_index_from_string(node_num_col)+2) mass_col = get_column_letter(column_index_from_string(node_num_col)+3) start_row = excel_index['Properties start row'] nodes = [] mass_nodes = [] current_row = start_row while ws[node_num_col + str(current_row)].value is not None: horiz = ws[horiz_col + str(current_row)].value vert = ws[vert_col + str(current_row)].value if parameter == 'Floor Plans': mass_at_node = ws[mass_col + str(current_row)].value if mass_at_node ==1: mass_nodes.append([horiz,vert]) #enter the new entry into the list nodes.append([horiz, vert]) current_row = current_row + 1 return nodes, mass_nodes
def sheet_to_inventory(group_by_col, hostname_col, sheet): if isinstance(group_by_col, six.string_types): group_by_col = (column_index_from_string( coordinate_from_string(group_by_col + "1")[0]) - 1) if isinstance(hostname_col, six.string_types): hostname_col = (column_index_from_string( coordinate_from_string(hostname_col + "1")[0]) - 1) groups = {"_meta": {"hostvars": {}}} rows = list(sheet.rows) for row in rows[1:]: host = row[hostname_col].value if host is None: continue group = row[group_by_col].value if group is None: group = default_group if group not in groups.keys(): groups[group] = {"hosts": [], "vars": {}} groups[group]["hosts"].append(host) groups["_meta"]["hostvars"][row[hostname_col].value] = {} for xlsx_head in rows[:1]: for idx, var_name in enumerate(xlsx_head): if var_name.value is None: var_name.value = "xlsx_" + var_name.coordinate if row[idx].value is not None: groups["_meta"]["hostvars"][row[0].value][ var_name.value.lower().replace(" ", "_")] = row[idx].value return groups
def removeColumns(worksheet): to_idx = find_row_with_key(worksheet, "To") from_idx = find_row_with_key(worksheet, "From") x = find_blank(worksheet) if (x == None): endCol = worksheet.max_column + 1 else: endCol = column_index_from_string(x) delArray = [] for col in worksheet.iter_cols(min_row=to_idx, max_row=to_idx, min_col=3, max_col=endCol): for cell in col: to_cell = str(cell.column_letter) + str(to_idx) from_cell = str(cell.column_letter) + str(from_idx) if worksheet[to_cell].value != None and worksheet[ from_cell].value != None: delArray.append(column_index_from_string(cell.column_letter)) for i in reversed( delArray ): #Must delete in reversed array as it changes excel sheet during manipulation worksheet.delete_cols(i, 1)
def handle_uploaded_file(form=None, f=None, model_name=None): wb = load_workbook(f) print(wb.sheetnames) # ws = wb.active # ws=wb.get_sheet_by_name("data") sheet_name = form.cleaned_data.get('sheet_name') data_type = form.cleaned_data.get('data_type') min_row = form.cleaned_data.get('from_row') max_row = form.cleaned_data.get('to_row') min_col = form.cleaned_data.get('from_col') max_col = form.cleaned_data.get('to_col') header_row = form.cleaned_data.get('header_row') model = apps.get_model(app_label='demandware', model_name=data_type) if sheet_name == None or sheet_name == '': sheet_name = 'data' ws = wb[sheet_name] if max_row == 0: max_row = ws.max_row if max_col == '': max_col = ws.max_column min_col_num = pycell.column_index_from_string(min_col) max_col_num = pycell.column_index_from_string(max_col) dataset = ws.iter_rows(min_row=min_row, min_col=min_col_num, max_col=max_col_num, max_row=max_row) header = ws.iter_rows(min_row=header_row, min_col=min_col_num, max_col=max_col_num, max_row=header_row) header = [cell.value for cell in list(header)[0]] _count = 0 insertDataSet = [] extInsertDataSet = [] fields = get_model_fields(model) print(fields) for row in dataset: values = {} extValues = {} for key, _cell in zip(header, row): if key in fields: values[key] = _cell.value if _cell.value else '' else: extValues[key] = _cell.value if _cell.value else '' _count = _count + 1 insertDataSet.append(values) extInsertDataSet.append(extValues) message = detect_service(model_name=data_type, data=insertDataSet, extData=extInsertDataSet, header=header) result = dict(message=message, count=_count) return result
def set_number_format( _worksheet, _table_begin_cell, _table_end_cell, _number_formats, _has_header = False ): begin_column = _worksheet[_table_begin_cell].column begin_column_index = column_index_from_string(begin_column) begin_row = _worksheet[_table_begin_cell].row end_column = _worksheet[_table_end_cell].column end_column_index = column_index_from_string(end_column) end_row = _worksheet[_table_end_cell].row # print("begin: " + begin_column + ", index: " + str(begin_column_index) + ", row: " + str(begin_row)) # print("end: " + end_column + ", index: " + str(end_column_index) + ", row: " + str(end_row)) styling_header = _has_header styling_first_table_row = True styling_index = 0 styling_max_column = end_column_index - begin_column_index for table_row in range(begin_row, end_row+1): if styling_header: styling_header = False continue for table_col in range(begin_column_index, end_column_index+1): cell = _worksheet.cell(column=table_col, row=table_row) cell.number_format = _number_formats[get_column_letter(table_col)]
def fill_html_table_from_excel_sheet(table, sheet, start_row=1, end_row=None, start_column='A', end_column=None, ignoreEmptyRow=True, datetime_format_mapping=None): if not end_column: end_column = detect_sheet_end_column(sheet) start_column_index = column_index_from_string(start_column) end_column_index = column_index_from_string(end_column) hidden_cells = hidden_coordinate_set_of_merged_cells( sheet.merged_cells.ranges) merged_mapping = merge_cell_mapping_by_top_left(sheet.merged_cells.ranges) for ri, row in enumerate(sheet): ri += 1 if ri < start_row: continue if end_row is not None and ri > end_row: continue if ignoreEmptyRow and all_cells_empty(row): continue html_row = table.append_row() for cell in row[start_column_index - 1:end_column_index]: value = str(cell.value or '').replace('\n', '<br />') if cell.is_date and cell.value: fmt = (datetime_format_mapping or {}).get(cell.number_format) if not fmt: fmt = default_datetime_format_mapping.get( cell.number_format) if fmt: value = cell.value.strftime(fmt) html_cell = html_row.append_cell(value) html_cell.set_escape(False) merged = merged_mapping.get(cell.coordinate) if merged: html_cell.set_colspan(merged.max_col + 1 - merged.min_col) html_cell.set_rowspan(merged.max_row + 1 - merged.min_row) html_cell_style = { 'text-align': cell.alignment.horizontal, 'vertical-align': cell.alignment.vertical, } fill_color = cell.fill.start_color.index if fill_color and fill_color.startswith('FF'): html_cell_style['background-color'] = '#{:s}'.format( fill_color[2:]) html_cell.set_style(html_cell_style)
def sheet_to_inventory(group_by_col, hostname_col, sheet): if isinstance(group_by_col, six.string_types): group_by_col = column_index_from_string( coordinate_from_string(group_by_col + '1')[0]) - 1 if isinstance(hostname_col, six.string_types): hostname_col = column_index_from_string( coordinate_from_string(hostname_col + '1')[0]) - 1 groups = {'_meta': {'hostvars': {}}} rows = list(sheet.rows) for row in rows[1:]: host = row[hostname_col].value if host is None: continue group = row[group_by_col].value if group is None: group = default_group if group not in groups.keys(): groups[group] = {'hosts': [], 'vars': {}} groups[group]['hosts'].append(row[hostname_col].value) groups['_meta']['hostvars'][row[hostname_col].value] = {} for excel_head in rows[:1]: for idx, var_name in enumerate(excel_head): if var_name.value is None: var_name.value = "excel_" + var_name.coordinate if row[idx].value is not None: groups['_meta']['hostvars'][row[0].value][ var_name.value.lower().replace(' ', '_')] = row[idx].value return groups
def getPerimeter(sheetRange): beginCol = column_index_from_string(sheetRange['beginHeader'][0]) beginRow = sheetRange['beginHeader'][1] endCol = column_index_from_string(sheetRange['endData'][0]) endRow = sheetRange['endData'][1] return {'beginCol': beginCol, 'endCol': endCol, 'beginRow': beginRow, 'endRow': endRow}
def delete_columns(self, col_range: str) -> None: col_range = col_range.split(":") start = column_index_from_string(col_range[0]) try: end = column_index_from_string(col_range[1]) except IndexError: end = start + 1 self.sheet.delete_cols(start, end - start)
def distances(df, file_count, files2, fields1, fields2, fields3): df = df.fillna(0) for i in range(file_count): sl = files2.astype(str).loc[i, "Store"] + "_" + files2.astype(str).loc[ i, "Location"] my = files2.astype(str).loc[i, "Month Year"] #get month year string out_fields3 = [sl + " " + s + " " + my for s in fields3] out_fields2 = [sl + " " + s + " " + my for s in fields2] out_fields1 = [sl + " " + s + " " + my for s in fields1] #create labels 1 for output columns if i == 0: df[out_fields3[0]] = 0 df[out_fields3[1]] = 0 df[out_fields3[2]] = 0 x_ref = df[out_fields2[1]] y_ref0 = df[out_fields2[2]] p_ref = df[out_fields1[0]] #y_ref=[0 for x in range (y_ref0)]; y_ref = [0] * y_ref0.shape[0] for j in range(y_ref0.shape[0]): if y_ref0[j] != 0: y_ref[j] = column_index_from_string(y_ref0[j]) else: y_ref[j] = 0 else: x = df[out_fields2[1]] y0 = df[out_fields2[2]] p = df[out_fields1[0]] y = [0] * y0.shape[0] for j in range(y0.shape[0]): if y0[j] != 0: y[j] = column_index_from_string(y0[j]) else: y[j] = 0 #df[out_fields3[0]]=abs(numpy.subtract(x,x_ref))+abs(numpy.subtract(y,y_ref)); #df[out_fields3[1]]=(numpy.square(numpy.subtract(x,x_ref))+numpy.square(numpy.subtract(y,y_ref)))**0.5; for j in range(y0.shape[0]): if x[j] != 0 and y[j] != 0 and x_ref[j] != 0 and y_ref[j] != 0: df.loc[j, out_fields3[0]] = abs(x[j] - x_ref[j]) + abs(y[j] - y_ref[j]) df.loc[j, out_fields3[1]] = ((x[j] - x_ref[j])**2 + (y[j] - y_ref[j])**2)**0.5 df.loc[j, out_fields3[2]] = p[j] - p_ref[j] else: df.loc[j, out_fields3[0]] = 0 df.loc[j, out_fields3[1]] = 0 df.loc[j, out_fields3[2]] = 0 x_ref = x y_ref = y p_ref = p return df
def format_horizontal(ws, loc_range: str, num_format: str): # loc_range: format "A10:D10" multiple capital letters followd by multiple numbers loc = re.match(r'^([A-Z]+)([0-9]+):([A-Z]+)([0-9]+)$', loc_range) row_init = int(loc.group(2)) col_start_num = column_index_from_string(loc.group(1)) col_end_num = column_index_from_string(loc.group(3)) for col in range(col_start_num, col_end_num + 1): loc = get_column_letter(col) + str(row_init) ws[loc].number_format = num_format
def get_floor_plans(wb,excel_index): headings_col = excel_index['Floor plan start col'] section_col = excel_index['Floor plan section col'] start_node_col = excel_index['Floor plan start node col'] end_node_col = excel_index['Floor plan end node col'] start_row = excel_index['Properties start row'] ws = wb['Floor Plans'] all_plans = [] current_headings_col = headings_col current_section_col = section_col current_start_node_col = start_node_col current_end_node_col = end_node_col i = 1 while ws[current_headings_col + str(4)].value is not None: [nodes, mass_nodes] = get_node_info(wb, excel_index, current_headings_col, 'Floor Plans') current_row = start_row cur_members = [] max_node_x = 0 max_node_y = 0 min_node_x = 0 min_node_y = 0 while ws[current_start_node_col + str(current_row)].value is not None: section = ws[current_section_col + str(current_row)].value start_node_num = ws[current_start_node_col + str(current_row)].value end_node_num = ws[current_end_node_col + str(current_row)].value start_node = nodes[start_node_num - 1] end_node = nodes[end_node_num - 1] cur_members.append(Member(start_node, end_node, section)) #find scaling factor in x and y, find area cur_nodes = [] cur_nodes.append(start_node) cur_nodes.append(end_node) for node in cur_nodes: if max_node_x < start_node[0]: max_node_x = node[0] if max_node_y < node[1]: max_node_y = node[1] if min_node_x > node[0]: min_node_x = node[0] if min_node_y > node[1]: min_node_y = node[1] current_row = current_row + 1 scaling_x = max_node_x - min_node_x scaling_y = max_node_y - min_node_y #area = scaling_x*scaling_y all_plans.append(FloorPlan(number=i, members=cur_members, mass_nodes=mass_nodes, scaling_x = scaling_x, scaling_y = scaling_y)) i += 1 current_headings_col = get_column_letter(column_index_from_string(current_headings_col) + 9) current_section_col = get_column_letter(column_index_from_string(current_section_col) + 9) current_start_node_col = get_column_letter(column_index_from_string(current_start_node_col) + 9) current_end_node_col = get_column_letter(column_index_from_string(current_end_node_col) + 9) return all_plans
def generate_short_links(generation): """Сгенерировать короткие ссылки на движке yourls :param generation: Одна генерация :type generation: LinkGeneration """ wb = xl.load_workbook(generation.links_file) sheet_name, links_column = wb.defined_names['link'].attr_text.split('!') ws = wb[sheet_name] links_column_number = column_index_from_string( coordinate_from_string(links_column)[0]) if generation.self_id: ids_column = column_index_from_string( coordinate_from_string( wb.defined_names['id'].attr_text.split('!')[1])[0]) row_number = 2 links = {} while ws.cell(row=row_number, column=links_column_number).value: links[row_number] = {} links[row_number]['link'] = ws.cell(row=row_number, column=links_column_number).value if generation.self_id: links[row_number]['id'] = ws.cell(row=row_number, column=ids_column).value row_number += 1 if generation.self_id: if check_id_validity(links): if check_id_existence(links, generation.engine): generation.state = ST_ERR generation.processed_comments = ID_ALREADY_USED generation.save() return else: generation.state = ST_ERR generation.processed_comments = ID_NOT_FIT_YOURLS generation.save() return elif not generation.self_id: assign_id_to_links(links, generation.engine) ws.insert_cols(links_column_number + 1) ws.cell(row=1, column=links_column_number + 1).value = 'Короткая ссылка' for link in links: ws.cell(row=link, column=(links_column_number + 1)).value = \ r'{eng_name}/{id}'.format(eng_name=generation.engine, id=links[link]['id']) wb.save(generation.links_file.path) if push_links_to_yourls(links, generation.engine, generation.title): generation.state = ST_FIN else: generation.state = ST_ERR generation.processed_comments = YOURLS_DB_ERROR generation.save()
def get_bracing(wb, excel_index, parameter): headings_col = excel_index['Bracing start col'] section_col = excel_index['Bracing section col'] start_node_col = excel_index['Bracing start node col'] end_node_col = excel_index['Bracing end node col'] start_row = excel_index['Properties start row'] if parameter == 'Floor Bracing': ws = wb['Floor Bracing'] elif parameter == 'Bracing': ws = wb['Bracing'] elif parameter == 'Space Bracing': ws = wb['Space Bracing'] else: print( 'Input should be either "Floor Bracing", "Space Bracing" or "Bracing"' ) all_bracing = [] current_headings_col = headings_col current_section_col = section_col current_start_node_col = start_node_col current_end_node_col = end_node_col i = 1 while ws[current_headings_col + str(4)].value is not None: nodes, mass_nodes = get_node_info(wb, excel_index, current_headings_col, parameter) current_row = start_row j = 1 cur_members = [] while ws[current_start_node_col + str(current_row)].value is not None: section = ws[current_section_col + str(current_row)].value start_node_num = ws[current_start_node_col + str(current_row)].value end_node_num = ws[current_end_node_col + str(current_row)].value start_node = nodes[start_node_num - 1] end_node = nodes[end_node_num - 1] cur_members.append(Member(start_node, end_node, section)) current_row = current_row + 1 j += 1 all_bracing.append( BracingScheme(number=i, members=cur_members, mass_nodes=mass_nodes)) i += 1 current_headings_col = get_column_letter( column_index_from_string(current_headings_col) + 9) current_section_col = get_column_letter( column_index_from_string(current_section_col) + 9) current_start_node_col = get_column_letter( column_index_from_string(current_start_node_col) + 9) current_end_node_col = get_column_letter( column_index_from_string(current_end_node_col) + 9) print('Read ' + str(len(all_bracing)) + ' bracing schemes') return all_bracing
def getRangeOfRowCols(xlRange): rangeTuples = [] start,end = xlRange.split(":") startCoord = coordinate_from_string(start) startCol, startRow = column_index_from_string(startCoord[0]), startCoord[1] endCoord = coordinate_from_string(end) endCol, endRow = column_index_from_string(endCoord[0]), endCoord[1] rangeTuples.append((startRow, endRow)) rangeTuples.append((startCol, endCol)) return rangeTuples
def write_blunk_address_for_excel(sheet, start_column, index, address, name): start_column_idx = column_index_from_string(start_column) sheet.cell(row=index, column=start_column_idx, value=util.int2ip4(int(address))) sheet.cell(row=index, column=start_column_idx + 1, value=name)
def set_fill_color(sheet, start_column, index, row): start_column_idx = column_index_from_string(start_column) for column in range(len(row)): cell = sheet.cell(row=index, column=start_column_idx + column) cell.fill = TITLE_FILL_COLOR cell.font = BOLD_FONT
def _get_column_as_letter(self, sheet, column_to_convert, startcol=0): if not isinstance(column_to_convert, (int, str_type, unicode_type, Container)): raise TypeError( "column must be an index, column letter or column name") column_as_letter = None if column_to_convert in self.data_df.columns: # column name column_index = self.data_df.columns.get_loc( column_to_convert ) + startcol + 1 # worksheet columns index start from 1 column_as_letter = cell.get_column_letter(column_index) # column index elif isinstance(column_to_convert, int) and column_to_convert >= 1: column_as_letter = cell.get_column_letter(startcol + column_to_convert) # assuming we got column letter elif isinstance(column_to_convert, (str_type, unicode_type)) < get_column_letter( sheet.max_column): column_as_letter = column_to_convert if column_as_letter is None or cell.column_index_from_string( column_as_letter) > sheet.max_column: raise IndexError("column: %s is out of columns range." % column_to_convert) return column_as_letter
def write_xlsx( template_xlsx: str, sheet_xy_csv: List[Any], output_xlsx: str, delimiter=",", ): """Xlsxファイルへの書き込みを行う Args: template_xlsx (str): 入力Xlsxファイル名 sheet_xy_csv (List[Any]): シートNo + セル位置 + 入力ファイル output_xlsx (str): 出力Xlsxファイル名 """ wb = load_workbook(filename=template_xlsx) for sheet_no, address, input_file in sheet_xy_csv: with open(input_file) as f: reader = csv.reader(f, delimiter=delimiter) input_csv = [row for row in reader] ws = wb.worksheets[sheet_no - 1] start_col_letter, start_row = coordinate_from_string(address) start_col = column_index_from_string(start_col_letter) write_list_2d(ws, input_csv, start_row, start_col) wb.save(output_xlsx)
def find_last_data_row(worksheet, start_row, column_range_start): column_index = cell.column_index_from_string(column_range_start) for current_row in range(start_row, worksheet.max_row): val = worksheet.cell(row=current_row, column=column_index).value if worksheet.cell(row=current_row, column=column_index).value == None: return current_row - 1 return worksheet.max_row
def write_to_config(btn): config = ConfigParser() config.read('config.ini') for i in range(len(ENTRY_LIST)): #loop through entry fields val = ENTRY_LIST[i].get() try: #Convert alphanumeric column to int val = str(cell.column_index_from_string(val)) except ValueError: val = '-1' new_tuple = (LISTINGS[i][0], '-1') LISTINGS[i] = new_tuple #Update LISTINGS new_tuple = (LISTINGS[i][0], val) LISTINGS[i] = new_tuple try: config.set('main', LISTINGS[i][0], val) except (NoSectionError, DuplicateSectionError, MissingSectionHeaderError): init_config() config.read('config.ini') config.set('main', LISTINGS[i][0], val) except NoOptionError: pass # save to config file with open('config.ini', 'w') as configfile: config.write(configfile) btn.configure(state=NORMAL)
def insertScreenReader(sh, tableList): blank = aodaConfig.screen_message table_size = len(tableList) if table_size == 1: dataColumn = column_index_from_string(tableList[0]['beginData'][0]) headerRow = tableList[0]['beginData'][1] - 1 dataCell = "".join((get_column_letter(dataColumn), str(headerRow))) text = blank + "The table starts on " + dataCell + ". Column Titles are in Row " + str(headerRow) + "." else: statement = '' for x in tableList: dataColumn = (x['beginData'][0]) headerRow = x['beginData'][1] - 1 dataCell = "".join((dataColumn, str(headerRow))) headers = ', ' + dataCell + ', Column Title in Row ' + str(headerRow) statement = statement + headers text = blank + 'There are ' + str(table_size) + ' tables in this sheet. The tables start at' + statement + '.' cell = findScreenReader(sh, tableList[0]['beginHeader'][1]) if cell: cell.value = text else: sh.cell(1, 1).value = text
def write_row_for_excel(sheet, start_column, index, row): start_column_idx = column_index_from_string(start_column) for column in range(len(row)): cell_value = row[column] cell = sheet.cell(row=index, column=start_column_idx + column, value=cell_value) if (cell_value is not None) and (1 < len(cell_value.split('\n'))): cell.alignment = Alignment(wrapText=True)
def getRowCol(cellAddress): try: column, row = coordinate_from_string(cellAddress) return column_index_from_string(column), row except: mb.showinfo("Alert", "Please enter a valid cell address (Letter(s) followed by Number(s): 'A67'") print("Please enter a valid cell address (Letter(s) followed by Number(s): 'A67'")
def get_comment_value_by_assignment_id(marked_grade_cell: Cell, marked_comment_cells: Cell, row_number, ws: worksheet): """ Helper method to find the corresponding comment value for a given grade_cell. Column index marked by assignment ID in upload_marker row. Row indexing by given row_number. The list of cells (marked_comment_cells) is cleaned before and do not contain the CommentMarkerPrefix anymore, but assignment IDs. :param marked_grade_cell: Grade cell marked by assignment ID in the upload_marker row. :param marked_comment_cells: List of all comment cells we want to find the assignment ID of marked_grade_cell in. :param row_number: Number of current row. :param ws: Worksheet to read from (data_only). :return: Empty string if cell value is None or if cell is not found. """ if Config.get_behavior_comments_switch(): for coment_cell in marked_comment_cells: # find matching assignment id if str(coment_cell.value) == str(marked_grade_cell.value): # extract coordinates (column index) column_index_of_comment_cell = column_index_from_string( coordinate_from_string(coment_cell.coordinate)[0]) # read value of cell comment_value = ws.cell( row=row_number, column=column_index_of_comment_cell).value if not comment_value: # normalize - we want string only as return type comment_value = '' return comment_value if Config.get_behavior_log(): print('Comment for grade cell ' + str(marked_grade_cell.coordinate) + ' not found!') return ''
def colorize_column( _worksheet, _col_color, _col_reference, _color_level = 0.8 ): """ _col_color: The Column Letter of the Column that should be colorized _col_reference: The Column Letter of the Column that is used as reference _color_level: (float) This "Level" is used for the orange color (Default: 0.8)""" pFill_orange = PatternFill(patternType=fills.FILL_SOLID, start_color='FCD5B4') pFill_red = PatternFill(patternType=fills.FILL_SOLID, start_color='E6B9B8') if _color_level is None: level = 0.8 try: level = float(_color_level) except (ValueError, TypeError) as e: print("Unable to set color_level -> defaults to 0.8") level = float(0.8) col1_index = column_index_from_string(_col_color) col_ref_index = column_index_from_string(_col_reference) table_rows = _worksheet.max_row for row_num in range(2, table_rows+1): col1_value = _worksheet[str(_col_color)+str(row_num)].value col_ref_value = _worksheet[str(_col_reference)+str(row_num)].value try: c1 = float(col1_value) c2 = float(col_ref_value) except (ValueError, TypeError) as e: continue if c1 > (c2*level): if c1 > c2: _worksheet[str(_col_color)+str(row_num)].fill = pFill_red else: _worksheet[str(_col_color)+str(row_num)].fill = pFill_orange