def setCellColorByCharAndRow(self,stringColumn,row,R=255,G=255,B=255): if(utils.column_index_from_string(stringColumn.strip())<0 ): raise ColumnIsNotVaild("列 不能小于0"); if(row<0 ): raise ColumnIsNotVaild(str(row)+"不能小于0行"); if(R==255 and G==255 and B == 255): fill = PatternFill(patternType = fills.FILL_NONE,fgColor=Color(rgb=self.rgbConvertToHex(R,G,B))); else: fill = PatternFill(patternType = fills.FILL_SOLID,fgColor=Color(rgb=self.rgbConvertToHex(R,G,B))); stringColumn = str(stringColumn).upper().strip(); # print("hdsjafaksldf"); # print(self.mergeCellsColumnAndRow); for i in self.mergeCellsCharAndIndex: # print("stringcolumn "+str(stringColumn)); if stringColumn>=str(i[0]).upper() and stringColumn<=str(i[2]).upper() and row>=i[1] and row <=i[3]: for neiHang in range((i[1]),i[3]+1): mybegin = utils.column_index_from_string(i[0]); myend = utils.column_index_from_string(i[2]); for neiCol in range(mybegin,myend+1): self.sheet.cell(row=neiHang,column=neiCol).fill = fill; self.wb.save(self.path); return; self.sheet.cell(str(stringColumn)+str(row)).fill = fill; self.wb.save(self.path);
def read_dimension(source): if hasattr(source, "encode"): return min_row = min_col = max_row = max_col = None DIMENSION_TAG = '{%s}dimension' % SHEET_MAIN_NS DATA_TAG = '{%s}sheetData' % SHEET_MAIN_NS it = iterparse(source, tag=[DIMENSION_TAG, DATA_TAG]) for _event, element in it: if element.tag == DIMENSION_TAG: dim = element.get("ref") m = ABSOLUTE_RE.match(dim.upper()) if m is None: return min_col, min_row, sep, max_col, max_row = m.groups() min_row = int(min_row) if max_col is None or max_row is None: max_col = min_col max_row = min_row else: max_row = int(max_row) return ( column_index_from_string(min_col), min_row, column_index_from_string(max_col), max_row ) elif element.tag == DATA_TAG: # Dimensions missing break element.clear()
def style_range(ws, cell_range, alignment=None, font=None, fill=None, border=None): """ :param ws: Excel worksheet instance :param range: An excel range to style (e.g. A1:F20) :param alignment: An openpyxl Alignment object :param font: An openpyxl Font object :param fill: An openpyxl Fill object :param border: An openpyxl Border object """ start_cell, end_cell = cell_range.split(':') start_coord = coordinate_from_string(start_cell) start_row = start_coord[1] start_col = column_index_from_string(start_coord[0]) end_coord = coordinate_from_string(end_cell) end_row = end_coord[1] end_col = column_index_from_string(end_coord[0]) for row in range(start_row, end_row + 1): for col_idx in range(start_col, end_col + 1): col = get_column_letter(col_idx) if alignment: ws.cell('%s%s' % (col, row)).alignment = alignment if font: ws.cell('%s%s' % (col, row)).font = font if fill: ws.cell('%s%s' % (col, row)).fill = fill if border: ws.cell('%s%s' % (col, row)).border = border
def search_data(ws): row_start = None row_end = None column_start = None for row in ws.rows: for cell in row: cell_content = cell.value column_index = cell.column row_index = cell.row if cell_content == KEY_CELL_NAME: row_start = row_index column_start = column_index if (row_start is not None and column_start == cell.column and cell_content is None): row_end = cell.row - 1 if column_start is None or row_start is None: raise RuntimeError('No data found in excel') return (row_start, row_end, column_index_from_string(column_start)) else: row_end = cell.row if column_start is None or row_start is None: raise RuntimeError('No data found in excel') return row_start, row_end, column_index_from_string(column_start)
def getColRowSpan(mergeDef): #merge def is like A1:B2 cells = mergeDef.split(":") sC,sR=coordinate_from_string(cells[0]) sC = column_index_from_string(sC) eC,eR=coordinate_from_string(cells[1]) eC = column_index_from_string(eC) colspan= int(eC) - int(sC) + 1 rowspan = int(eR) - int(sR) + 1 if colspan == 1: colspan=None if rowspan == 1: rowspan=None return colspan, rowspan
def get_excel_data(self): all_transactions = [] for ws in self.workbook: program = ws.cell(row = 1, column = 1).value transaction = ws.cell(row = 2, column = 1).value transaction_grid = [] last_field = None for row in ws.get_squared_range(2, 4, self.max_columns, self.rows): if row[0].value: grid_index = row[0].row - 4 transaction_grid.append([]) for field in row: if field.value and not last_field: transaction_grid[grid_index].append(field.value) elif not last_field: last_field = utils.column_index_from_string(field.column) - 1 elif utils.column_index_from_string(field.column) <= last_field: transaction_grid[grid_index].append(field.value) process_rows = [] for i in range(len(transaction_grid[0])): for j in range(len(transaction_grid)): if i == 0 and j > 0: process_rows.append({}) process_rows[j-1]['excel_cell'] = {'column': 1, 'row': j + 4} elif j == 0: field_name = transaction_grid[j][i] elif transaction_grid[j][i]: process_rows[j - 1][field_name] = transaction_grid[j][i] for process_row in process_rows: cell_ref = process_row.pop('excel_cell', {}) all_transactions.append({'program': program, 'transaction': transaction, 'parameters': process_row, 'result_cell': cell_ref, 'ws': ws }) return all_transactions
def getCellColorByCharAndRow(self,stringColumn,row): if(utils.column_index_from_string(stringColumn.strip())<0 ): raise ColumnIsNotVaild("列 不能小于0"); if(row<0 ): raise ColumnIsNotVaild(str(row)+"不能小于0行"); stringColumn = str(stringColumn).upper().strip(); cell = self.sheet.cell(str(stringColumn)+str(row)); for i in self.mergeCellsCharAndIndex: # print("stringcolumn "+str(stringColumn)); if stringColumn>=str(i[0]).upper() and stringColumn<=str(i[2]).upper() and row>=i[1] and row <=i[3]: cell = self.sheet.cell(str(i[0])+str(i[1])); if(cell.fill.fgColor.type =='rgb'): return self.__colorHexStringToRGB(cell.fill.fgColor.rgb) ; elif(cell.fill.fgColor.type =='theme'): fgColor = cell.fill.fgColor; return {'theme':fgColor.value,'persentage':fgColor.tint}; # for i in self.sheet.cell(str(stringColumn)+str(row)).fill.fgColor: # print (i); # print(self.sheet.cell(str(stringColumn)+str(row)).fill.fgColor.tint); if(cell.fill.fgColor.type=='rgb'): return self.__colorHexStringToRGB(cell.fill.fgColor.rgb); elif(cell.fill.fgColor.type=='theme'): fgColor = cell.fill.fgColor; return {'theme':fgColor.value,'persentage':fgColor.tint};
def write_cols(worksheet): """Write worksheet columns to xml. <cols> may never be empty - spec says must contain at least one child """ cols = [] for label, dimension in iteritems(worksheet.column_dimensions): col_def = dict(dimension) if col_def == {}: continue idx = column_index_from_string(label) cols.append((idx, col_def)) if not cols: return el = Element('cols') for idx, col_def in sorted(cols): v = "%d" % idx cmin = col_def.get('min') or v cmax = col_def.get('max') or v col_def.update({'min': cmin, 'max': cmax}) el.append(Element('col', col_def)) return el
def translate_formula(self, dest): """ Convert the formula into A1 notation. The formula is converted into A1 assuming it is assigned to the cell whose address is `dest` (no worksheet name). """ tokens = self.get_tokens() if not tokens: return "" elif tokens[0].type == Token.LITERAL: return tokens[0].value out = ['='] # per the spec: # A compliant producer or consumer considers a defined name in the # range A1-XFD1048576 to be an error. All other names outside this # range can be defined as names and overrides a cell reference if an # ambiguity exists. (I.18.2.5) dcol, drow = coordinate_from_string(dest) dcol = column_index_from_string(dcol) row_delta = drow - self.row col_delta = dcol - self.col for token in tokens: if token.type == Token.OPERAND and token.subtype == Token.RANGE: out.append(self.translate_range(token.value, row_delta, col_delta)) else: out.append(token.value) return "".join(out)
def write_comments_vml(self): root = Element("xml") shape_layout = SubElement(root, "{%s}shapelayout" % officens, {"{%s}ext" % vmlns: "edit"}) SubElement(shape_layout, "{%s}idmap" % officens, {"{%s}ext" % vmlns: "edit", "data": "1"}) shape_type = SubElement(root, "{%s}shapetype" % vmlns, {"id": "_x0000_t202", "coordsize": "21600,21600", "{%s}spt" % officens: "202", "path": "m,l,21600r21600,l21600,xe"}) SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"}) SubElement(shape_type, "{%s}path" % vmlns, {"gradientshapeok": "t", "{%s}connecttype" % officens: "rect"}) for idx, comment in enumerate(self.comments, 1026): shape = _shape_factory() col, row = coordinate_from_string(comment.ref) row -= 1 column = column_index_from_string(col) - 1 shape.set('id', "_x0000_s%04d" % idx) client_data = shape.find("{%s}ClientData" % excelns) client_data.find("{%s}Row" % excelns).text = str(row) client_data.find("{%s}Column" % excelns).text = str(column) root.append(shape) return tostring(root)
def _get_row(self, element, min_col=1, max_col=None): """Return cells from a particular row""" col_counter = min_col for cell in safe_iterator(element, CELL_TAG): coord = cell.get('r') column_str, row = coordinate_from_string(coord) column = column_index_from_string(column_str) if max_col is not None and column > max_col: break if min_col <= column: if col_counter < column: for col_counter in range(max(col_counter, min_col), column): # pad row with missing cells yield EMPTY_CELL data_type = cell.get('t', 'n') style_id = int(cell.get('s', 0)) formula = cell.findtext(FORMULA_TAG) value = cell.find(VALUE_TAG) if value is not None: value = value.text if formula is not None: if not self.parent.data_only: data_type = 'f' value = "=%s" % formula yield ReadOnlyCell(self, row, column_str, value, data_type, style_id) col_counter = column + 1 if max_col is not None: for _ in range(col_counter, max_col+1): yield EMPTY_CELL
def anchor(self): """ returns the expected position of a cell in pixels from the top-left of the sheet. For example, A1 anchor should be (0,0). :rtype: tuple(int, int) """ left_columns = (column_index_from_string(self.column) - 1) column_dimensions = self.parent.column_dimensions left_anchor = 0 default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH) for col_idx in range(left_columns): letter = get_column_letter(col_idx + 1) if letter in column_dimensions: cdw = column_dimensions.get(letter).width or default_width if cdw > 0: left_anchor += points_to_pixels(cdw) continue left_anchor += default_width row_dimensions = self.parent.row_dimensions top_anchor = 0 top_rows = (self.row - 1) default_height = points_to_pixels(DEFAULT_ROW_HEIGHT) for row_idx in range(1, top_rows + 1): if row_idx in row_dimensions: rdh = row_dimensions[row_idx].height or default_height if rdh > 0: top_anchor += points_to_pixels(rdh) continue top_anchor += default_height return (left_anchor, top_anchor)
def __init__(self, formula, origin): # Excel errors out when a workbook has formulae in R1C1 notation, # regardless of the calcPr:refMode setting, so I'm assuming the # formulae stored in the workbook must be in A1 notation. col, self.row = coordinate_from_string(origin) self.col = column_index_from_string(col) self.tokenizer = Tokenizer(formula)
def __init__(self, col, row, sheet='', excel=None, value=None): self.row = row self.col = col self.col_idx = column_index_from_string(col) self.sheet = sheet self.excel = excel self.address = AddressCell('{}{}'.format(col, row), sheet=sheet) self.value = value
def add_comment_shape(self, root, idx, coord, height, width): col, row = coordinate_from_string(coord) row -= 1 column = column_index_from_string(col) - 1 shape = _shape_factory(row, column, height, width) shape.set('id', "_x0000_s%04d" % idx) root.append(shape)
def _readMatrix (self,xlsxFilename,xlsxSheetname,**kwargs): workBook = load_workbook(xlsxFilename) inSheet = workBook[xlsxSheetname] returnMatrix = [] if 'range' in kwargs.keys(): rangeString = kwargs["range"] startRange, endRange = rangeString.split (":") startColumn, startRow = coordinate_from_string (startRange) endColumn, endRow = coordinate_from_string (endRange) for row in range (startRow,endRow + 1): lineArray = [] for column in range (column_index_from_string(startColumn),column_index_from_string(endColumn) + 1): #print inSheet['%s%s'%(get_column_letter(column), row)].value lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value) column += 1 returnMatrix.append(lineArray) column = 1 row += 1 return returnMatrix else: if 'start' in kwargs.keys(): startCell = kwargs["start"] startColumnLetter, startRow = coordinate_from_string (startCell) startColumn = column_index_from_string(startColumnLetter) else: startColumn, startRow = 1,1 if 'cellsPerRow' in kwargs.keys(): numberOfCellsPerRow = kwargs["cellsPerRow"] row = startRow column = startColumn while inSheet['%s%s'%(get_column_letter(column), row)].value: lineArray = [] if numberOfCellsPerRow: while column <= startColumn + numberOfCellsPerRow - 1: #print inSheet['%s%s'%(get_column_letter(column), row)].value lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value) column += 1 else: while inSheet['%s%s'%(get_column_letter(column), row)].value: #print inSheet['%s%s'%(get_column_letter(column), row)].value lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value) column += 1 returnMatrix.append(lineArray) column = startColumn row += 1 return returnMatrix
def add_shape_vml(self, root, idx, comment): col, row = coordinate_from_string(comment.ref) row -= 1 column = column_index_from_string(col) - 1 shape = _shape_factory(row, column) shape.set('id', "_x0000_s%04d" % idx) root.append(shape)
def _get_cell(self, coordinate): """Cells are returned by a generator which can be empty""" col, row = coordinate_from_string(coordinate) col = column_index_from_string(col) cell = tuple(self.get_squared_range(col, row, col, row))[0] if cell: return cell[0] return EMPTY_CELL
def row_col_from_cell(cell): """ :param cell: a cell string e.g. 'B12' :return: row, col in numerical index """ from openpyxl.utils import column_index_from_string, coordinate_from_string (col,row) = coordinate_from_string(cell) return row, column_index_from_string(col)
def grab_value(sheet, reference, field='title'): sel = reference[0] if target_titles[sel]['direction'] == 'row': # reference is (sel, (row, None)) column = column_index_from_string(target_titles[sel][field]) return get_merged_cell(sheet, row=reference[1][0], col=column) elif target_titles[sel]['direction'] == 'column': # reference is (sel, (None, col)) return get_merged_cell(sheet, row=target_titles[sel][field], col=reference[1][1]) else: raise ValueError
def get_highest_column(self): """Get the largest value for column currently stored. :rtype: int """ if self.column_dimensions: return max([column_index_from_string(column_index) for column_index in self.column_dimensions]) else: return 1
def __copy_col_cells(in_ws, out_ws, in_data_range, out_col, has_header=True): out_col_index = column_index_from_string(out_col) row_offset = 2 if has_header else 1 for (i,row) in enumerate(in_ws.iter_rows(in_data_range)): for cell in row: out_ws.cell(row=i+row_offset,column=out_col_index,value=cell.value) return out_ws
def _calculate_dimension(self): """ Loop through all the cells to get the size of a worksheet. Do this only if it is explicitly requested. """ max_col = 0 for r in self.rows: cell = r[-1] max_col = max(max_col, column_index_from_string(cell.column)) self.max_row = cell.row self.max_col = get_column_letter(max_col)
def get_cell_range(start_cell, num_row = 1, num_col = 1): start_cell = start_cell.upper() m = re.match(r"([a-zA-Z]+)([0-9]+)", start_cell) letter_value = m.group(1) number_value = m.group(2) start_addr = "$" + letter_value + "$" + number_value letter_value = get_column_letter( column_index_from_string(letter_value) + num_col - 1) number_value = str(eval(number_value) + num_row - 1) end_addr = "$" + letter_value + "$" + number_value ref_addr = start_addr + ":" + end_addr return ref_addr
def translate_col(col_str, cdelta): """ Translate a range col-snippet by the given number of columns """ if col_str.startswith('$'): return col_str else: try: return get_column_letter( column_index_from_string(col_str) + cdelta) except ValueError: raise TranslatorError("Formula out of range")
def autoscale_col_width(self, sheet, column): if type(column) is int: column_letter = get_column_letter(column) column_id = column else: column_letter = column column_id = column_index_from_string(column) cells_len = [] for row_id in range(1, len(sheet.rows)+1): cell = sheet.cell(row=row_id, column=column_id) if cell.value: cells_len.append(len(cell.value)) sheet.column_dimensions[column_letter].width = max(cells_len) * 1.5
def __init__(self, worksheet, column=None, row=None, value=None, col_idx=None, style_array=None): super(Cell, self).__init__(worksheet, style_array) self.row = row # _value is the stored value, while value is the displayed value self._value = None self._hyperlink = None self.data_type = 'n' if value is not None: self.value = value self._comment = None if column is not None: col_idx = column_index_from_string(column) self.col_idx = col_idx
def offset(self, row=0, column=0): """Returns a cell location relative to this cell. :param row: number of rows to offset :type row: int :param column: number of columns to offset :type column: int :rtype: :class:`openpyxl.cell.Cell` """ offset_column = column_index_from_string(self.column) + column offset_row = self.row + row return self.parent.cell(column=offset_column, row=offset_row)
def anchor(self, cell, anchortype="absolute"): """ anchors the image to the given cell optional parameter anchortype supports 'absolute' or 'oneCell'""" self.drawing.anchortype = anchortype if anchortype == "absolute": self.drawing.left, self.drawing.top = cell.anchor return ((cell.column, cell.row), cell.parent.point_pos(self.drawing.top + self.drawing.height, self.drawing.left + self.drawing.width)) elif anchortype == "oneCell": self.drawing.anchorcol = column_index_from_string(cell.column) - 1 self.drawing.anchorrow = cell.row - 1 return ((self.drawing.anchorcol, self.drawing.anchorrow), None) else: raise ValueError("unknown anchortype %s" % anchortype)
def append(self, iterable): """Appends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param iterable: list, range or generator, or dict containing values to append :type iterable: list|tuple|range|generator or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({1 : 'This is A1', 3 : 'This is C1'}) :raise: TypeError when iterable is neither a list/tuple nor a dict """ row_idx = self._current_row + 1 if (isinstance(iterable, (list, tuple, range)) or isgenerator(iterable)): for col_idx, content in enumerate(iterable, 1): if isinstance(content, Cell): # compatible with write-only mode cell = content if cell.parent and cell.parent != self: raise ValueError("Cells cannot be copied from other worksheets") cell.parent = self cell.col_idx = col_idx cell.row = row_idx else: cell = Cell(self, row=row_idx, col_idx=col_idx, value=content) self._cells[(row_idx, col_idx)] = cell elif isinstance(iterable, dict): for col_idx, content in iterable.items(): if isinstance(col_idx, basestring): col_idx = column_index_from_string(col_idx) cell = Cell(self, row=row_idx, col_idx=col_idx, value=content) self._cells[(row_idx, col_idx)] = cell else: self._invalid_row(iterable) self._current_row = row_idx
def write2excel(SheetTitle: str, MSGTestReportList: list, PathName: str) -> None: wb = Workbook() sheet = wb.worksheets[0] sheet.title = SheetTitle for tmp in MSGTestReportList: sheet.append(tmp) for i in range(sheet.max_row): for j in range(column_index_from_string('M')): if sheet[get_column_letter(j + 1) + str(i + 1)].value == "None": sheet[get_column_letter(j + 1) + str(i + 1)].value = '' #font = Font(color = colors.RED) #sheet["A1"].font = font wb.save(PathName)
def append(self, iterable): """Appends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param iterable: list, range or generator, or dict containing values to append :type iterable: list|tuple|range|generator or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({1 : 'This is A1', 3 : 'This is C1'}) :raise: TypeError when iterable is neither a list/tuple nor a dict """ row_idx = self._current_row + 1 if (isinstance(iterable, (list, tuple, range)) or isgenerator(iterable)): for col_idx, content in enumerate(iterable, 1): if isinstance(content, Cell): # compatible with write-only mode cell = content if cell.parent and cell.parent != self: raise ValueError("Cells cannot be copied from other worksheets") cell.parent = self cell.column = col_idx cell.row = row_idx else: cell = Cell(self, row=row_idx, column=col_idx, value=content) self._cells[(row_idx, col_idx)] = cell elif isinstance(iterable, dict): for col_idx, content in iterable.items(): if isinstance(col_idx, basestring): col_idx = column_index_from_string(col_idx) cell = Cell(self, row=row_idx, column=col_idx, value=content) self._cells[(row_idx, col_idx)] = cell else: self._invalid_row(iterable) self._current_row = row_idx
def keywordExtraction(path, columnName): # jieba分词提取关键词 jieba.load_userdict("dict.txt") # 加载字典 wb = load_workbook(path) sheet = wb.active # 这个算法好像没tf-idf好,好像 #textRank = analyse.textrank countItems = 2 contentInColumn = str(columnName) # 内容所在列 contentTheOutputColumns = get_column_letter( column_index_from_string(contentInColumn) + 1) sheet["{}1".format(contentTheOutputColumns)] = "MachineToExtract" # 内容输出列 for i in sheet["{0}2:{1}{2}".format(contentInColumn, contentInColumn, sheet.max_row)]: for j in i: # 这段提取标签 #keywords = textRank(j.value) try: # 到底用什么词性才是最贴切呢? keywords = jieba.analyse.extract_tags(j.value, 3, allowPOS=('n', 'v', 'a')) keywordsConvert = " ".join(keywords) print(j.value, keywords) sheet["{0}{1}".format(contentTheOutputColumns, countItems)].value = keywordsConvert except: pass countItems += 1 #for keyword in keywords: # print(j.value,keyword) # 这段用来比较句子的相似程度,但是词库缺乏词条 #for i2 in sheet["{}{}:{}{}".format(contentInColumn,j.row+1,contentInColumn,sheet.max_row)]: # for j2 in i2: # r = synonyms.compare(j.value,j2.value) # if r>0.7: # sheet.delete_rows(j2.row,1) #print(j.value) wb.save(filename=path)
def append(self, iterable): """Appends a group of values at the bottom of the current sheet. * If it's a list: all values are added in order, starting from the first column * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters) :param iterable: list, range or generator, or dict containing values to append :type iterable: list/tuple/range/generator or dict Usage: * append(['This is A1', 'This is B1', 'This is C1']) * **or** append({'A' : 'This is A1', 'C' : 'This is C1'}) * **or** append({1 : 'This is A1', 3 : 'This is C1'}) :raise: TypeError when iterable is neither a list/tuple nor a dict """ row_idx = self.max_row + 1 if (isinstance(iterable, (list, tuple, range)) or isgenerator(iterable)): for col_idx, content in enumerate(iterable, 1): col = get_column_letter(col_idx) if isinstance(content, Cell): # compatible with write-only mode cell = content cell.parent = self cell.column = col cell.row = row_idx cell.coordinate = "%s%s" % (col, row_idx) self._add_cell(cell) else: cell = self._new_cell(col, row_idx, content) elif isinstance(iterable, dict): for col_idx, content in iteritems(iterable): if isinstance(col_idx, basestring): col_idx = column_index_from_string(col_idx) self.cell(row=row_idx, column=col_idx, value=content) else: self._invalid_row(iterable) self.row_dimensions[row_idx] = RowDimension(worksheet=self, index=row_idx)
def getTableHeaders(filepath): wb = load_workbook(filename=filepath, read_only=True) sheet = wb.active col_headings = [] index = 0 dim = sheet.calculate_dimension() dim = dim.split(':') xy = coordinate_from_string(dim[1]) max_col = column_index_from_string(xy[0]) for row in sheet.iter_rows(min_row=1, min_col=1, max_row=1, max_col=max_col): for cell in row: index += 1 if cell.value != None: col_headings.append((index, cell.value)) return col_headings
def resolve( d ): #Function: erase marks from same column, row and box as given number change = True x = column_index_from_string( d[0]) - 1 #converts xcel x coord to marks x coord xstart = x // 3 * 3 #y conversion y = int(d[1]) - 1 ystart = y // 3 * 3 boxes[(y // 3) * 3 + x // 3] = boxes[(y // 3) * 3 + x // 3].replace( str(sheets[coords].value), '') rows[y] = rows[y].replace(str(sheets[coords].value), '') columns[x] = columns[x].replace(str(sheets[coords].value), '') marks[x][y] = '' #Erases all marks in box with known value for i in range(0, 9): remove(i, y, d) #Row remove(x, i, d) #Column remove(xstart + i % 3, ystart + i // 3, d) #Box
def build_des_column_data_all(self): LineNumber = 1 diag_LineNumber = 1 for num in range(len(self.src_row_data_all)): if self.src_row_data_all[num][column_index_from_string('Q') - 1] != 'Y': TxChList, ChangeIDList = self.get_TxChannal(num) #print(TxChList) if len(TxChList) > 0: for index, TxCh in enumerate(TxChList): self.des_column_data_all.append( self.build_des_column_data(LineNumber, TxCh, num, ChangeIDList[index])) LineNumber += 1 else: self.des_column_data_all.append( self.build_des_column_data(LineNumber, 0, num, '0')) LineNumber += 1
def load_from_workbook(wb): ret = excel_config() ws_data = [ws for ws in wb.worksheets if ws.title == "data"] if ws_data.__len__() == 0: raise (Exception("Invalid workbook. The workbook must contains one worksheet with name is 'data'")) ret.workbook = wb ret.data_sheet = ws_data[0] ret.names = [] for x in wb.defined_names.definedName: from openpyxl.utils import coordinate_from_string, column_index_from_string if hasattr(x, "name") and x.value!='data!#REF!': item = excel_name_range() item.address = x.value item.name = x.name item.col = column_index_from_string(x.value.split("!")[1].split(":")[0].replace("$", "")) - 1 ret.names.append(item) ret.names = sorted(ret.names, key=lambda x: x.name) return ret
def xlsx_to_txt(file): """Converts the given excel spreadsheet to text files. Splits into files for each column.""" wb = openpyxl.load_workbook(file) workingsheet = wb.active maxcolumn_letter = get_column_letter(workingsheet.max_column) maxcolumn_index = column_index_from_string(maxcolumn_letter) # Loops over every column with content and writes each individual column to a file. # TODO - Make this work for rows too, but not sure how I'd detect that. Maybe ask the user? for i in range(1, maxcolumn_index + 1): with open("output/column{0}.txt".format(i), "w") as f: for cell in workingsheet[get_column_letter(i)]: f.write(str(cell.value)) f.write("\n")
def _fill_bi(self, workbook, data_dict, worksheet_range): for sheet_name in data_dict: worksheet = data_dict[sheet_name] if isinstance(sheet_name, str): st = get_sheet_by_name(workbook, sheet_name) elif isinstance(sheet_name, int): st = workbook.worksheets[sheet_name - 1] if not st: raise ValidationError(_('Sheet %s not found!') % sheet_name) if not worksheet.get('_BI_', False): continue for rc, bi_dict in worksheet.get('_BI_', {}).iteritems(): req_field = ['df', 'oper_code'] key_field = bi_dict.keys() if set(req_field) != set(key_field): raise ValidationError( _('_BI_ requires \n' ' - df: initial DataFrame from worksheet\n' ' - oper_code: pandas operation code')) # Get dataframe src_df = bi_dict['df'] src_st = get_sheet_by_name(workbook, src_df) df = load_workbook_range(worksheet_range[src_df], src_st) eval_context = {'df': df, 'pd': pd, 'np': np} # Get DF using safe_eval method df = safe_eval(bi_dict['oper_code'], eval_context, mode="exec", nocopy=True) if 'result' in eval_context: # use result=... df = eval_context['result'] if df is None: df = eval(bi_dict['oper_code'], eval_context) if df.empty: continue df = df.reset_index() rows = dataframe_to_rows(df, index=False, header=False) # Get init cell index xy = coordinate_from_string(rc) c = column_index_from_string(xy[0]) r = xy[1] for r_idx, row in enumerate(rows, r): for c_idx, value in enumerate(row, c): st.cell(row=r_idx, column=c_idx, value=value)
def _apply_styles_to_ws(ws, column_styles=None, cell_styles=None): # dict de las columnas que corresponden a cada campo header_row = next(ws.rows) headers_cols = {cell.value: cell.column for cell in header_row} # aplica estilos de columnas if column_styles: for col, properties in iteritems(column_styles): # la col puede ser "A" o "nombre_campo" col = headers_cols.get(col, col) for prop_name, prop_value in iteritems(properties): setattr(ws.column_dimensions[col], prop_name, prop_value) # aplica estilos de celdas if cell_styles: for i in moves.xrange(1, ws.max_row + 1): for j in moves.xrange(1, ws.max_column + 1): cell = ws.cell(row=i, column=j) # si el valor es una URL válida, la celda es un hyperlink if helpers.validate_url(cell.value): cell.hyperlink = cell.value cell.font = Font(underline='single', color='0563C1') for cell_style in cell_styles: match_all = ( "col" not in cell_style and "row" not in cell_style ) match_row = ( "row" in cell_style and cell_style["row"] == i ) match_col = ( "col" in cell_style and column_index_from_string( headers_cols.get(cell_style["col"], cell_style["col"])) == j ) if match_all or match_row or match_col: for prop_name, prop_value in iteritems(cell_style): if prop_name != "col" and prop_name != "row": setattr(cell, prop_name, prop_value)
def set_title(self, field, pattern_fill=None, font=None, alignment=None, new_line=True, passed_title=None, merge_list=None): """ 設定資料的 title, 會上底色以及字體加粗 :param field: :param pattern_fill: :param font: :param alignment: :param new_line: :param passed_title: :param merge_list: :return: """ next_col_index = None title = passed_title or self.titles[field] _pattern_fill = pattern_fill or self.pattern_fill _font = font or self.font # _alignment = alignment or self.alignment # 如果有要 merge 欄位 if merge_list is not None: self.__set_column_merge(merge_list) # merge 過後的欄位, 只有合併欄位的第一欄能給值 # e.g. A 與 B 欄合併, 只有 A 欄能給值, 若嘗試給 B 欄值會 Error # AB欄合併後,下一個能給值的欄位就是C欄, 若C欄合併到E欄 # CDE欄只有C欄能給值,接著下一個能給值的欄位是F欄 # merge_list 每個元素是 Tuple, tuple[0] 為合併欄位的起始欄位 for val, col_index in zip(title, [column_index_from_string(t[0]) for t in merge_list]): if alignment is not None: self.set_cell(self.row_index, col_index, val, _pattern_fill, _font, alignment) else: self.set_cell(self.row_index, col_index, val, _pattern_fill, _font) next_col_index = col_index else: for col_index, val in enumerate(title, start=1): self.set_cell(self.row_index, col_index, val, _pattern_fill, _font, alignment) next_col_index = col_index if new_line: self.row_index = 1 return next_col_index + 1
def set_sample_info(self, field, data): """ 設定調查名冊戶長的基本資料 :param field: :param data: :return: """ merge_list = [('A', 'B'), ('C', 'C'), ('D', 'D'), ('E', 'E'), ('F', 'G'), ('H', 'I'), ('J', 'Q')] col_idex_list = [column_index_from_string(t[0]) for t in merge_list] self.set_title(field, merge_list=merge_list) # 因為 Excel 不需要寫入 ID 因此先 pop 出來 data.pop('id') for val, col_index in zip(data.values(), col_idex_list): self.set_cell(row=self.row_index, col=col_index, value=val) self.__set_column_merge(merge_list) self.row_index = 2
def styling_cells(self, ws: Worksheet, columns: ColumnPattern): """Applies the referenced style to the cells of a column. :param Worksheet ws: worksheet into write headers :param ColumnPattern columns: column table """ # wrap for v in columns.values(): # ignore empties columns if v.letter is None or v.style is None: continue # apply wrap style depending on value col_idx = column_index_from_string(v.letter) for row_cols in ws.iter_rows(min_row=2, min_col=col_idx, max_col=col_idx): for cell in row_cols: # print(cell.style) cell.style = v.style
def loadxlsxTest(): wb = openpyxl.load_workbook('./automate_online-materials/example.xlsx') print(type(wb)) print(wb.get_sheet_names()) # 每个表由一个 Worksheet 对象表示,可以通过向工作簿方法 get_sheet_by_name()传递表名字符串获得 sheet = wb.get_sheet_by_name('Sheet1') print(sheet) # 可以通过 title 属性取得它的名称 print(sheet.title) # 取得工作簿的活动表 antherSheet = wb.get_active_sheet() print(antherSheet) print(sheet['A1']) print(sheet['A1'].value) c = sheet['B1'] print(c.value) print('Row ' + str(c.row) + ', Column ' + ' is ' + str(c.column) + str(c.value)) print('Cell ' + str(c.coordinate) + ' is ' + str(c.value)) # 可以通过 Worksheet 对象的 get_highest_row()和 get_highest_column()方法,确定 表的大小 # get_highest_row()和get_highest_column()在最新版的openpyxl模块中已经被删除了,取而代之的是max_row和max_column两个方法 rows = sheet.max_row colunms = sheet.max_column print(colunms) for i in range(1, rows, 2): print(i, sheet.cell(row=i, column=2).value) print(get_column_letter(900)) print(get_column_letter(colunms)) print(column_index_from_string('AA')) # 取得电子表格中一行、一列或一个矩形区域中的所有 Cell 对象 print(tuple(sheet['A1':'C3'])) for rowOfCellObjects in sheet['A1':'C3']: for cellObj in rowOfCellObjects: print(cellObj.coordinate, cellObj.value) print('--- END OF ROW ---')
def _getCells(self, strCell): if type(strCell) == str: expTemp = strCell.upper(); if ":" in expTemp: min_col, min_row, max_col, max_row = utils.cell.range_boundaries(expTemp); return (min_col-1, min_row-1, max_col, max_row); else: column,row=utils.cell.coordinate_from_string(expTemp); column = utils.column_index_from_string(column); return (column-1, row-1); elif type(strCell) == list and len(strCell) == 2: valueTemp = strCell[0]; if type(valueTemp) == int: return (strCell[1]-1,strCell[0]-1); elif type(valueTemp) == list: return (strCell[0][1]-1, strCell[0][0]-1, strCell[1][1], strCell[1][0]); else: self._getException(strCell) else: self._getException(strCell);
def update_mysheet(self, wb): ws = wb.active for row in ws.rows: if not isinstance(row[0].value, str): continue key = key_map.get(row[0].value.strip()) if not key: continue datarow = self.calculate_datarow(key, row[0].value) for idx, datacol in enumerate(datarow): if not idx: continue cell = row[idx + 1] col = column_index_from_string(cell.column) if type(datacol) != float: newcell = ws.cell(row=cell.row, column=col, value=float(datacol.replace(',', ''))) else: newcell = ws.cell(row=cell.row, column=col, value=float(datacol)) self.copy_cellformat(cell, newcell)
def load_selector(self, selector_csv_path): selector = OutputDataSelector(selector_csv_path) xl_labels = [i.value for i in self.data_sheet[self.label_row]] selector_index = xl_labels.index(selector.label) selector_index_letter = get_column_letter(selector_index+1) xl_selector_col = self.data_sheet[selector_index_letter] xl_selector_col_str = [str(i.value) for i in xl_selector_col] row_selected = [] for select_set in selector.selectors: data_set = [] for i in select_set: i_index = xl_selector_col_str.index(i) data_set.append(xl_selector_col[i_index].row) row_selected.append(data_set) data_column_range = [column_index_from_string(i) for i in selector.data_cols_range] return [data_column_range, row_selected]
def _write_leaguesheet_headers(self, worksheet, no_of_weeks): worksheet['A2'].value = "Name" worksheet['B2'].value = "Weeks Played" worksheet['A2'].fill = self.blue_fill worksheet['B2'].fill = self.blue_fill for week in range(1, no_of_weeks + 1): week_header_cell = worksheet.cell(row=2, column=week + 2) week_header_cell.value = week week_header_cell.fill = self.blue_fill week_header_cell.alignment = self.center_alignment worksheet.column_dimensions[week_header_cell.column].width = 3 last_column = column_index_from_string(week_header_cell.column) worksheet.merge_cells(start_row=1, start_column=3, end_row=1, end_column=last_column) week_title_cell = worksheet.cell(row=1, column=3) week_title_cell.value = "Weeks" week_title_cell.fill = self.orange_fill week_title_cell.alignment = self.center_alignment
def freeze_panes(self, topLeftCell): if not topLeftCell: topLeftCell = None elif isinstance(topLeftCell, str): topLeftCell = topLeftCell.upper() else: # Assume a cell topLeftCell = topLeftCell.coordinate if topLeftCell == 'A1': topLeftCell = None if not topLeftCell: self.sheet_view.pane = None return if topLeftCell is not None: colName, row = coordinate_from_string(topLeftCell) column = column_index_from_string(colName) view = self.sheet_view view.pane = Pane(topLeftCell=topLeftCell, activePane="topRight", state="frozen") view.selection[0].pane = "topRight" if column > 1: view.pane.xSplit = column - 1 if row > 1: view.pane.ySplit = row - 1 view.pane.activePane = 'bottomLeft' view.selection[0].pane = "bottomLeft" if column > 1: view.selection[0].pane = "bottomRight" view.pane.activePane = 'bottomRight' if row > 1 and column > 1: sel = list(view.selection) sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None)) sel.insert( 1, Selection(pane="bottomLeft", activeCell=None, sqref=None)) view.selection = sel
def update_user_bonuses(self, nickname_row, session_column): how_much_to_go_back = 6 for col in self.sheet.iter_cols(min_col=session_column, min_row=nickname_row, max_row=nickname_row): cell = col[0] if column_index_from_string( cell.column) - session_column >= how_much_to_go_back: break #skip if colored if cell.fill.start_color.index != self.cell_no_bonus_color: break #if has value, start counting count = 0 if cell.value: count += cell.value
def count(arr, c): # 科学学位定向单考统计 middle = [] for index, v in enumerate(colleges): middle.append(0) # del middle[0] # print(len(middle)) for i in arr: j = sheet_read.cell(row=i, column=column_index_from_string('F')) for index, v in enumerate(colleges): if j.value == v: middle[index] += 1 # 从 A4 开始,前面三行标题信息 # print(middle) for i, v in enumerate(middle): if v == 0: middle[i] = '' for i, v in enumerate(middle): # print(i, v) # 从 A4 开始,前面三行标题信息 sheet_write[(c + "%d") % (i + 4)].value = v
def setstyle(PathName: str, SheetTitle: str) -> None: # 默认可读写,若有需要可以指定write_only和read_only为True wb = load_workbook(PathName) sheet = wb[SheetTitle] for i in range(sheet.max_row): for j in range(column_index_from_string('U')): sheet[get_column_letter(j + 1) + str(i + 1)].alignment = Alignment( horizontal='center', vertical='center') sheet[get_column_letter(j + 1) + str(i + 1)].font = Font(name='Times New Roman') if i < 2: sheet[get_column_letter(j + 1) + str(i + 1)].font = Font( name='楷体', size=12, bold=True) if sheet['U' + str(i + 1)].value == "Success": sheet['U' + str(i + 1)].fill = styles.fills.GradientFill( stop=["00FF00", "00FF00"]) sheet[get_column_letter(j) + str(i + 1)].font = Font(size=12, bold=True) elif sheet['U' + str(i + 1)].value == "Fail": sheet['U' + str(i + 1)].fill = styles.fills.GradientFill( stop=["FF0000", "FF0000"]) sheet[get_column_letter(j + 1) + str(i + 1)].font = Font(size=12, bold=True) #设置行高 for i in range(sheet.max_row): sheet.row_dimensions[i + 1].height = 18 #设置列宽 sheet.column_dimensions['B'].width = 20 for i in range(column_index_from_string('G'), column_index_from_string('U')): sheet.column_dimensions[get_column_letter(i + 1)].width = 10 sheet.column_dimensions['U'].width = 15 #合并单元格 for i in range(column_index_from_string('H')): sheet.merge_cells( get_column_letter(i + 1) + '1:' + get_column_letter(i + 1) + '2') for i in range(column_index_from_string('L'), column_index_from_string('P')): sheet.merge_cells( get_column_letter(i + 1) + '1:' + get_column_letter(i + 1) + '2') sheet.merge_cells("I1:L1") sheet.merge_cells("Q1:T1") sheet.merge_cells("U1:U2") wb.save(PathName)
def take_info_bridge(): info_original_1.clear() for i in range(0, len(source_sheet3_info_loc)): val = sheet_doc_3[source_sheet3_info_loc[i]].value print("val",val) if type(val) == str: val = val.replace('\n', '') if val == None and '2018年桥梁记录表(昆西)/武昆(176座)' in open_contentfile: loc_col = re.sub(r'\d', '', source_sheet3_info_loc[i]) loc_col = column_index_from_string(loc_col) loc_row = re.sub(r'[A-Z]', '', source_sheet3_info_loc[i]) loc_row = int(loc_row) + 1 val = sheet_doc_3.cell(row=loc_row, column=loc_col).value if val == None and open_contentfile == '2018年桥梁记录表(昆西)/永武(433座)/K2611+051上行线(波纹管外露).xlsx': val = '4×20m+4×19m+18.5m+24m+18.5m现浇箱梁' if type(val) == str: val = val.replace(' ', '') info_original_1.append(val) return info_original_1
def find_session_column(self, id): """Find column where to place current session.""" # check if it exists already # place it if it doesn't tokens_first_column = self.nicknames_column + 1 session_column = None new_column_placement = tokens_first_column for col in self.sheet.iter_cols(min_col=tokens_first_column, max_row=1): column_session_number = int(col[0].value.replace('#', '')) current_column_index = column_index_from_string(col[0].column) if id < column_session_number: new_column_placement = current_column_index + 1 if column_session_number == int(id): session_column = current_column_index break return session_column, new_column_placement
def rows_with_data(df_avancor, banks, all_razdels): """ Находим номера строк с данными, которые будем копировать""" avancoreTitle = '1.1. Виды и суммы операций, совершенных по банковским счетам некредитной \n' \ 'финансовой организации' col = 'B' # файл-Аванкор: интервал строк, в пределах которых ищем строки с разделами row_start = 32 row_end = 91 # Кол-во строк и столбцов в файле-Аванкор index_max = df_avancor.shape[0] collumn_max = df_avancor.shape[1] title_row = razdel_name_row(df_avancor, avancoreTitle, title_col=column_index_from_string(col), row_start=row_start, row_end=row_end) # номер строки с названиями валют row_currency = title_row[0] + 3 # номера строк с разделами rows_of_title = rows_of_razdel(df_avancor, all_razdels, col='D', row_start=row_start, row_end=row_end) rows_itogi = rows_of_razdel(df_avancor, all_razdels, col='B', row_start=row_start, row_end=row_end, rows_under=False) # устанавливаем нижнюю строку последнего раздела перед итогами rows_of_title[-1]['row_razdel_end'] = rows_itogi[0]['row_razdel_begin'] - 1 return rows_of_title
def addsheet (self, hols): ws = self._month.tag () self._table.copy_sheet (self._wsMonth, ws) # 1st row self._table.setCell (CONF.ATTENDANCE.attendance_cell_monthM, "%s %04d" % (str (self._month), self._month.year ()), sheet=ws) col0x = column_index_from_string (self._daystartcol) - 1 # Build a list of style items for the space to fill, according to the calendar year = self._month.year () mon = self._month.month () stlist = [] for day in range (33): col = get_column_letter (col0x + day) try: date = datetime.date (year, mon, day) if date.weekday () > 4: # Weekend stlist.append (self._st_W) elif date in hols: # Holiday weekday stlist.append (self._st_F) else: # A normal schoolday stlist.append (self._st_N) self._table.setCell (col + self._validdaysrow, "+" , sheet=ws) except: # Invalid date (or other "error" ...) stlist.append (self._st_X) # For each pupil, style the attendance cells, and the margin cells for row in range (self._row0, self._rowlimit): col = col0x for st in stlist: self._table.setCell (get_column_letter (col) + str (row), style = st, sheet = ws) col += 1 self._table.page_setup (ws, landscape=True, fitHeight=True, fitWidth=True) self._month.increment () return ws
def _writeMatrix (self,xlsxFilename,xlsxSheetname,contentMatrix,**kwargs): def checkFileAndSheet(): if os.path.isfile(xlsxFilename): outWorkbook = load_workbook(xlsxFilename) if xlsxSheetname in outWorkbook.get_sheet_names(): outSheet = outWorkbook[xlsxSheetname] else: outSheet = outWorkbook.create_sheet() outSheet.title = xlsxSheetname else: outWorkbook = Workbook() outWorkbook.remove_sheet(outWorkbook.worksheets[0]) outSheet = outWorkbook.create_sheet() outSheet.title = xlsxSheetname return outWorkbook, outSheet def writeLine(lineRow,columnOffset,contentList): for myColumn in range (1,len(contentList) +1 ): myCell = self.outSheet['%s%s'%(get_column_letter(myColumn + columnOffset), lineRow)] #print contentList[myColumn - 1 ] ###FIXME### myCell.value = contentList[myColumn - 1 ] #myCell.style = cellStyle self.outWorkbook, self.outSheet = checkFileAndSheet() if 'start' in kwargs.keys(): startCell = kwargs["start"] startColumn, startRow = coordinate_from_string (startCell) rowOffset = startRow - 1 columnOffset = column_index_from_string(startColumn) - 1 else: rowOffset = 0 columnOffset = 0 for myRowIndex in range (1 ,len(contentMatrix) +1 ): writeLine (rowOffset + myRowIndex,columnOffset,contentMatrix[myRowIndex - 1]) self.outWorkbook.save(filename = xlsxFilename) return True
def _resolve_cell_coordinates(self, locator): """Resolves the cell coordinates based on several possible forms of `locator` parameter. Currently supports the following locator forms: - __Coordinates__ - __A1 Notation__ For more detail see the keyword documentation of the `Read From Cell` keyword. """ locator = locator.strip() if "," in locator: # Coordinates. if locator.startswith("coords:"): locator = locator[7:] locator = locator.lstrip('(').rstrip(')') coords_parts = locator\ .lstrip('(')\ .rstrip(')')\ .split(',') row_nr, col_nr = coords_parts[1].strip(),\ coords_parts[0].strip() else: # Assume A1 notation. if locator.startswith("a1:"): locator = locator[3:] a1_col = "" a1_row = "" for char in locator: if char.isdigit(): a1_row += char else: a1_col += char col_nr = column_index_from_string(a1_col) row_nr = a1_row return int(row_nr), int(col_nr)