def increase_col(): wb=load_workbook('report.xlsx') sheet = wb.get_sheet_by_name('Sheet') wb.active curr_col = sheet.max_column col_count = sheet.max_column col_count += 1 pre_col =sheet.max_column -1 curr_col=get_column_letter(curr_col) #pre_col =get_column_letter(pre_col) new_col= get_column_letter(col_count) today = datetime.date.today() first = today.replace(day=1) lastMonth = first - datetime.timedelta(days=1) lastMonth= lastMonth.strftime("%B %Y") next_month = "{:%B %Y}".format(datetime.date.today() + relativedelta.relativedelta(months=1)) month="{:%B %Y}".format(datetime.date.today()) while sheet.cell('%s1' % curr_col).value == month: pass else: sheet['%s1' % new_col] = month wb.save('report.xlsx') #increase_col()
def logs (a,b): wb=load_workbook('report.xlsx',read_only = False, data_only = True) sheet =wb.get_sheet_by_name('Sheet') wb.active mydate = datetime.date.today() this_month= mydate.strftime("%B %Y") curr_col= sheet.max_column curr_row = sheet.max_row min_col=sheet.min_column min_col=get_column_letter(min_col) curr_col = get_column_letter(curr_col) sheet['%s1' % curr_col] = this_month sheet.column_dimensions['%s' % curr_col].width = 50 time=datetime.datetime.now().strftime("%d %H:%M") if b != None: new_row=curr_row+1 #sheet['%s%d' % (curr_col,new_row)] = a+ " " + b +" "+ time sheet['%s%d' % (curr_col,column_to_add(curr_col))] = time+">>> "+a+" "+b else: new_row=curr_row+1 b = " " sheet['%s%d' % (curr_col,column_to_add(curr_col))] = time+">>> "+a+" "+b #print (column_to_add(curr_col)) wb.save('report.xlsx')
def header(): italic24Font = Font(size=18, italic=False) wb=load_workbook('testing.xlsx',read_only = False, data_only = True) sheet =wb.get_sheet_by_name('Sheet') wb.active month="{:%B %Y}".format(datetime.date.today()) today = datetime.date.today() sheet['a1'].font=italic24Font sheet['b1'].font=italic24Font sheet['c1'].font=italic24Font sheet.column_dimensions['A'].width = 20 sheet.column_dimensions['B'].width = 30 sheet.column_dimensions['C'].width = 30 sheet['a1']="UID" sheet['b1']="Name" sheet['c1']="Email" start_col =4 start_month=get_column_letter(start_col) sheet['%s1' % start_month] = "August 2016" curr_col = sheet.max_column new_col= curr_col+1 first = today.replace(day=1) new_col = get_column_letter(new_col) lastMonth = first - datetime.timedelta(days=1) lastMonth= lastMonth.strftime("%B %Y") #print (curr_col) this_month=get_column_letter(curr_col) if sheet.cell('%s1' % this_month).value == lastMonth: sheet['%s1' % new_col] = month sheet.column_dimensions['%s' % this_month].width = 20 wb.save('testing.xlsx')
def unmerge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): """ Remove merge on a cell range. Range is a cell range (e.g. A1:E1) """ if not range_string: if start_row is None or start_column is None or end_row is None or end_column is None: msg = ( "You have to provide a value either for " "'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'" ) raise InsufficientCoordinatesException(msg) else: range_string = "%s%s:%s%s" % ( get_column_letter(start_column), start_row, get_column_letter(end_column), end_row, ) elif len(range_string.split(":")) != 2: msg = "Range must be a cell range (e.g. A1:E1)" raise InsufficientCoordinatesException(msg) else: range_string = range_string.replace("$", "") if range_string in self._merged_cells: self._merged_cells.remove(range_string) else: msg = "Cell range %s not known as merged." % range_string raise InsufficientCoordinatesException(msg)
def add_column(): wb=load_workbook('testing.xlsx') sheet = wb.get_sheet_by_name('Sheet') wb.active curr_col = sheet.max_column col_count = sheet.max_column col_count += 1 pre_col =sheet.max_column -1 curr_col=get_column_letter(curr_col) pre_col =get_column_letter(pre_col) new_col= get_column_letter(col_count) #print (new_col) today = datetime.date.today() first = today.replace(day=1) lastMonth = first - datetime.timedelta(days=1) lastMonth= lastMonth.strftime("%B %Y") next_month = "{:%B %Y}".format(datetime.date.today() + relativedelta.relativedelta(months=1)) month="{:%B %Y}".format(datetime.date.today()) #print (next_month) #sheet['%s1' % curr_col] = month #print (lastMonth) if sheet.cell('%s1' % curr_col).value != month: sheet['%s1' % new_col] = next_month wb.save('testing.xlsx')
def exportResolve(fileName): if not os.path.exists("output"): os.makedirs("output") disPattern = re.compile("(\xa0){0,}(?P<dis>[0-9/]+).*") savePath = "output/" + fileName b = Workbook() s = b.active resolve = dbm.getAllResolve() for case in resolve: case['Disposition Date'] = disPattern.match(case['Disposition']).group(2) dataCounts = len(resolve) if dataCounts == 0: return False keys = sorted(resolve[0].keys()) keySize = len(keys) # Write column head for col_index in range(1, keySize + 1): s["%s1"%get_column_letter(col_index)] = keys[col_index - 1] # Write data from second row i = 0 for row_index in range(2, dataCounts + 2): # Reduce index calculation when access a data data = resolve[i] for col_index in range(1, keySize + 1): s["%s%s"%(get_column_letter(col_index), row_index)] = \ data[keys[col_index - 1]] i += 1 b.save(filename = savePath) return True
def write_to_spreadsheet(self, spreadsheet_name): '''Write values from self.data_table to spreadsheet''' r = 1 c = 1 wb = openpyxl.Workbook() sheet = wb.active # Write sheet for item in self.data_table: # Number cells if item.lstrip('-').isdigit(): sheet.cell(row=r, column=c).value = int(item) sheet.cell(row=r, column=c).number_format = '0' sheet.column_dimensions[get_column_letter(c)].width = len(item) * 1.5 c += 1 # Emty cells elif item == '': c += 1 # Text cells else: r += 1 c = 1 sheet.cell(row=r, column=c).value = item sheet.column_dimensions[get_column_letter(c)].width = len(item) * 2 c += 1 wb.save(spreadsheet_name + '.xlsx')
def write_qc_header(qc_sheet): headers = ["comment", "pass/fail", "#chr", "gene", "pos", "ref", "alt", "mutation type", "status", "main consequence", "T VAF", "N VAF", "expected VAF", "transcript link", "expected HGVSc", "expected HGVSp", "mutation ID" ] row_counter = 1 column_counter = 1 for header in headers: cell = qc_sheet.cell(row=row_counter, column=column_counter) cell.value = header if qc_sheet.column_dimensions[get_column_letter(column_counter)].width < len(str(cell.value)): qc_sheet.column_dimensions[get_column_letter(column_counter)].width = len(str(cell.value)) + 5 column_counter += 1 row_counter += 1 return row_counter
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) """ if not range_string and not all((start_row, start_column, end_row, end_column)): msg = "You have to provide a value either for 'coordinate' or for\ 'start_row', 'start_column', 'end_row' *and* 'end_column'" raise ValueError(msg) elif not range_string: range_string = '%s%s:%s%s' % (get_column_letter(start_column), start_row, get_column_letter(end_column), end_row) elif ":" not in range_string: if COORD_RE.match(range_string): return # Single cell, do nothing raise ValueError("Range must be a cell range (e.g. A1:E1)") else: range_string = range_string.replace('$', '') if range_string not in self._merged_cells: self._merged_cells.append(range_string) min_col, min_row, max_col, max_row = range_boundaries(range_string) rows = range(min_row, max_row+1) cols = range(min_col, max_col+1) cells = product(rows, cols) # all but the top-left cell are removed for c in islice(cells, 1, None): if c in self._cells: del self._cells[c]
def calculate_dimension(self): """Return the minimum bounding range for all cells containing data.""" return '%s%d:%s%d' % ( get_column_letter(1), self.min_row, get_column_letter(self.max_column or 1), self.max_row or 1)
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None): """ Set merge on a cell range. Range is a cell range (e.g. A1:E1) """ if not range_string: if (start_row is None or start_column is None or end_row is None or end_column is None): msg = "You have to provide a value either for "\ "'coordinate' or for 'start_row', 'start_column', 'end_row' *and* 'end_column'" raise InsufficientCoordinatesException(msg) else: range_string = '%s%s:%s%s' % (get_column_letter(start_column), start_row, get_column_letter(end_column), end_row) elif ":" not in range_string: if COORD_RE.match(range_string): return # Single cell msg = "Range must be a cell range (e.g. A1:E1)" raise InsufficientCoordinatesException(msg) else: range_string = range_string.replace('$', '') if range_string not in self._merged_cells: self._merged_cells.append(range_string) cells = cells_from_range(range_string) # only the top-left cell is preserved for c in islice(chain.from_iterable(cells), 1, None): if c in self._cells: del self._cells[c]
def write_key_action_block(self, keyaction, int_row, int_col): #Write the action name ka_cell = '%s%s' % (Utils.get_column_letter(int_col), int_row) worksheet[ka_cell] = action.name # worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col), int_row, Utils.get_column_letter(int_col), int_row + action.numParams())) #Write the action description desc_cell = '%s%s' % (Utils.get_column_letter(int_col+1), int_row) worksheet[desc_cell] = action.description # worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col+1), int_row, Utils.get_column_letter(int_col + 1), int_row + action.numParams())) #Write the input parameters for the key action param_row = int_row for param in action.input_parameters: param_cell = '%s%s' % (Utils.get_column_letter(int_col+2), param_row) worksheet[param_cell] = param.name param_value_cell = '%s%s' % (Utils.get_column_letter(int_col+3), param_row) worksheet[param_value_cell] = param.value param_row+=1 #Write Expected Result er_cell = '%s%s' % (Utils.get_column_letter(int_col+4), int_row) worksheet[er_cell] = action.expected_result # worksheet.merge_cells('%s%s:%s%s' % (Utils.get_column_letter(int_col+4), int_row, Utils.get_column_letter(int_col + 1), int_row + action.numParams())) if action.numParams() == 0: int_row+=1 else: int_row+=action.numParams()
def __str__(self): fmt = u"{0}!${1}${2}:${3}${4}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = u"{0}!${1}${2}" return fmt.format(self.sheetname, get_column_letter(self.min_col), self.min_row, get_column_letter(self.max_col), self.max_row )
def _check_changes_other_than_code_and_year(first_data, second_data, line_index): modifications = [] for col_index, obj in enumerate(first_data): if col_index == ACRONYM_COL_NUMBER and second_data[ACRONYM_COL_NUMBER] != EMPTY_VALUE: modifications.append('{}{}'.format(get_column_letter(col_index + 1), line_index)) else: if obj != second_data[col_index] and col_index != ACADEMIC_COL_NUMBER: modifications.append('{}{}'.format(get_column_letter(col_index + 1), line_index)) return modifications
def setCellValueByColumnAndRow(self,hang,lie,value = None): for i in self.mergeCellsColumnAndRow: if hang>=i[1] and hang<=i[3] and lie>=i[0] and lie <=i[2]: self.__setDanYuanGeWidth(utils.get_column_letter(i[0])); self.sheet.cell(row=i[1],column=i[0]).value = value; self.wb.save(self.path); self.__setDanYuanGeWidth(utils.get_column_letter(lie)); self.sheet.cell(row=hang,column=lie).value = value; self.wb.save(self.path);
def calculate_dimension(self, force=False): if not all([self.max_column, self.max_row]): if force: self._calculate_dimension() else: raise ValueError("Worksheet is unsized, use calculate_dimension(force=True)") return '%s%d:%s%d' % ( get_column_letter(self.min_column), self.min_row, get_column_letter(self.max_column), self.max_row )
def coord(self): """ Excel style representation of the range """ fmt = "{min_col}{min_row}:{max_col}{max_row}" if (self.min_col == self.max_col and self.min_row == self.max_row): fmt = "{min_col}{min_row}" return fmt.format( min_col=get_column_letter(self.min_col), min_row=self.min_row, max_col=get_column_letter(self.max_col), max_row=self.max_row )
def Convert_Image_To_Excel_Spreadsheet(image_path, resolution=.3, output_file="results.xlsx"): wb = openpyxl.Workbook() #first create an excel workbook sheet = wb.get_sheet_by_name("Sheet") #each workbook has a default sheet, so we grab it to use im = Image.open(image_path)#next we read in our image width, height = im.size #get the width and height of the image, this will correspond to how many pixels tall and wide the image is. rgb_im = im.convert('RGB') #now get the rgb data for the image. #now we need to loop through the image, taking into account the resolution, for x in range(0, int(width * resolution)): #for each pixel we use 3 cells in the spreadsheet, each in the same row, #so only the column value will change for each cell in the pixel. #also important to note, excel is base 1, so a value of 0 is invalid, this is a slight diversion from how #we normally code stuff...so we need to take that into acctoun #calculate and store the colum_indicies for this column. column_indicies = [get_column_letter((x * 3) + 1), get_column_letter((x * 3) + 2), get_column_letter((x * 3) + 3)] #in order to preserve the aspect ratio of the image we want to scale down the width of the pixels. #Our goal is to get 3 cells in a neat square, according to the documentation the height of a cell is 10 px, #since we have 3 this would normally mean we would have the width be 10/3, however this still resulted in a more rectangle shape, #with a little tweaking the below creates a close enough square for our 'pixels' sheet.column_dimensions[column_indicies[0]].width = 10 / 9 sheet.column_dimensions[column_indicies[1]].width = 10 / 9 sheet.column_dimensions[column_indicies[2]].width = 10 / 9 #next we loop through all of the rows for this set of columns. for row in range(1, int(height * resolution)): #now we get the rgb data for this pixel. we store this in rgb_data rgb_data = rgb_im.getpixel((int(x / resolution), int(row / resolution))) for i in range(3):#next we loop through the 3 cells for this pixel. colors = [0, 0, 0]#start with pure white, colors[i] = rgb_data[i]#update a single value from our rgb data col = get_column_letter((x * 3) + i + 1)#since excel uses a A1 type of coordinate system we need to convert the column number with a letter, thankfully there is allready a function for that in our libraries, #now get the cell cell = sheet[col + str(row)] #just for propserity, I also store the rgb value for this cell, just so it's not blank, cell.value = rgb_data[i] #next we recolor the cell. #the libraries use a string based color scheme, this code takes our colors list and combines them in a way that the libraries #can make sense of and convert to a color for the cell. color_string = "".join([str(hex(i))[2:].upper().rjust(2, "0") for i in colors]) cell.fill = PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string) #don't forget to save. wb.save(output_file)
def rows(self): """ Return all cells in range by column """ for row in range(self.min_row, self.max_row+1): yield tuple('%s%d' % (get_column_letter(col), row) for col in range(self.min_col, self.max_col+1))
def dump_layout_to_excel(layout): """ https://djangotricks.blogspot.co.uk/2013/12/how-to-export-data-as-excel.html """ array, items = build_pos_array(layout) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=Price list.xlsx' wb = Workbook() ws = wb.active ws.title = 'Price List' widths = [10, 40, 10] for col_num in range(len(widths)): ws.column_dimensions[get_column_letter(col_num + 1)].width = widths[col_num] c = ws.cell(row=1, column=2, value='Price List') row_num = 2 for row in array: for col_num in range(len(row)): if col_num == 0: if len(row[col_num]) > 2: description = row[col_num][2] ws.cell(row=row_num, column=1, value=description) row_num += 1 else: if len(row[col_num]) > 2: item = row[col_num][2] ws.cell(row=row_num, column=2, value=item.description) c = ws.cell(row=row_num, column=3, value=item.sale_price) c.number_format = '£0.00' row_num += 1 wb.save(response) return response
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 _parse_row_internal(self, row: ReadOnlyRow): """ Parses whole row and returns parsed instance. """ instance = {} required_columns = self.get_required_columns() for column in self.get_all_columns(): try: self.parse_column(row, column, instance, column in required_columns) except XLSImportError as exc: column_id = self.column_mappings.get(column) exc.column = get_column_letter(column_id + 1) exc.column_enum = column raise exc for column in self.missing_columns: parser = self.get_all_columns()[column] mock_cell = ReadOnlyCell(None, None, None, None) parser.read_column(row, mock_cell, instance) self.on_row_read(instance) return instance
def record(self, data): from openpyxl import Workbook from openpyxl.compat import range from openpyxl.utils import get_column_letter wb = Workbook() dest_filename = 'empty_book.xlsx' ws1 = wb.active ws1.title = "range names" for row in range(1, 40): ws1.append([1,2,3,4,5]) ws2 = wb.create_sheet(title="Pi") ws2['F5'] = 3.14 ws3 = wb.create_sheet(title="Data") for row in range(10, 20): for col in range(27, 54): _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) print(ws3['AA10'].value) wb.save(filename = dest_filename)
def export_xls_zip_all_classes(StudentAdmin, request, queryset): import openpyxl from django.utils.encoding import smart_str from openpyxl.utils import get_column_letter response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=journal.xlsx' wb = openpyxl.Workbook() wb.remove(wb.active) pos = 1 columns = [ (u"ID", 15), (u"ФИО", 20), (u"Класс", 20), (u"Оценка", 10), (u"Комментарий", 40), ] dv = DataValidation(type="decimal", operator="between", formula1=0, formula2=10, allow_blank=True) for parallel in Parallel.objects.all(): for group in parallel.class2_set.all(): isinstance(group, Class2) ws = wb.create_sheet(str(parallel) + str(group.name), pos) ws.protection.sheet = True ws.protection.password = '******' pos += 1 row_num = 0 for col_num in xrange(len(columns)): c = ws.cell(row=row_num + 1, column=col_num + 1) c.value = columns[col_num][0] #c.style.font.bold = True # set column width ws.column_dimensions[get_column_letter(col_num + 1)].width = columns[col_num][1] for student in group.student_set.all(): row_num += 1 row = [ student.pk, smart_str(student.fname + " " + student.lname + ' ' + student.fathers_name), smart_str(str(parallel.name) + str(group.name)), smart_str(''), smart_str(''), ] for col_num in xrange(len(row)): c = ws.cell(row=row_num + 1, column=col_num + 1) c.alignment = Alignment(wrap_text=True) lock = False if col_num + 1 <= 3: lock = True c.protection = Protection(locked=lock) c.value = row[col_num] response.write(save_virtual_workbook(wb)) return response
def createTimeColumn(self, row, col): ws = self.__ws wf = self.__wf cellWidth = wf.timeCellWidth cellHeight = wf.cellHeight fontSize = wf.timeFontSize self.createHeader(row, col, 'Time') ws.column_dimensions[get_column_letter(col)].width = cellWidth wf.setTopAlignment(row + 1, row + 29, col, col) for i in xrange(0, 29): currentRow = row + i + 1 currentCell = ws.cell(row=currentRow, column=col) ws.row_dimensions[currentRow].height = cellHeight if i%2 == 0: currentCell.value = datetime.datetime.strptime(str(i/2 + 8), '%H') currentCell.number_format = FORMAT_DATE_TIME1 currentCell.font = Font(size=fontSize) if (currentRow + 1)%4 < 2: currentCell.fill = wf.whiteFill else: currentCell.fill = wf.lightgrayFill wf.setBorder(row + 1, row + 29, col, col)
def collapse_cell_addresses(cells, input_ranges=()): """ Collapse a collection of cell co-ordinates down into an optimal range or collection of ranges. E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation object applied, attempt to collapse down to a single range, A1:B3. Currently only collapsing contiguous vertical ranges (i.e. above example results in A1:A3 B1:B3). """ ranges = list(input_ranges) # convert cell into row, col tuple raw_coords = (coordinate_to_tuple(cell) for cell in cells) # group by column in order grouped_coords = defaultdict(list) for row, col in sorted(raw_coords, key=itemgetter(1)): grouped_coords[col].append(row) # create range string from first and last row in column for col, cells in grouped_coords.items(): col = get_column_letter(col) fmt = "{0}{1}:{2}{3}" if len(cells) == 1: fmt = "{0}{1}" r = fmt.format(col, min(cells), col, max(cells)) ranges.append(r) return " ".join(ranges)
def build_sheet(self, data, ws, sheet_name='report', header=None, widths=None): first_row = 1 column_base = 1 ws.title = re.sub(r'\W+', '', sheet_name)[:30] if header: for i, header_cell in enumerate(header): cell = ws.cell(row=first_row, column=i + column_base) cell.value = header_cell cell.font = Font(bold=True) if widths: ws.column_dimensions[get_column_letter(i + 1)].width = widths[i] for row in data: for i in range(len(row)): item = row[i] # If item is a regular string if isinstance(item, str): # Change it to a unicode string try: row[i] = text_type(item) except UnicodeDecodeError: row[i] = text_type(item.decode('utf-8', 'ignore')) elif type(item) is dict: row[i] = text_type(item) try: ws.append(row) except ValueError as e: ws.append([e.message]) except: ws.append(['Unknown Error'])
def draw(iterable): for i in iterable: sheet_ranges.cell(row=i.x+top_left_corner[1]+1, column=i.y+top_left_corner[0]+1).value = '█' for i in range(len(sheet_ranges.rows[0])): sheet_ranges.column_dimensions[get_column_letter(i+1)].width = 3.0 wb.save(OUTPUT_FILENAME)
def point_pos(self, left=0, top=0): """ tells which cell is under the given coordinates (in pixels) counting from the top-left corner of the sheet. Can be used to locate images and charts on the worksheet """ current_col = 1 current_row = 1 column_dimensions = self.column_dimensions row_dimensions = self.row_dimensions default_width = points_to_pixels(DEFAULT_COLUMN_WIDTH) default_height = points_to_pixels(DEFAULT_ROW_HEIGHT) left_pos = 0 top_pos = 0 while left_pos <= left: letter = get_column_letter(current_col) current_col += 1 if letter in column_dimensions: cdw = column_dimensions[letter].width if cdw is not None: left_pos += points_to_pixels(cdw) continue left_pos += default_width while top_pos <= top: row = current_row current_row += 1 if row in row_dimensions: rdh = row_dimensions[row].height if rdh is not None: top_pos += points_to_pixels(rdh) continue top_pos += default_height return (letter, row)
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)
import openpyxl, sys, os from openpyxl.utils import get_column_letter #Passed parameters N = int(sys.argv[1]) #Start position of blank rows M = int(sys.argv[2]) #Number of blank rows to insert filename = sys.argv[3] #Loading the sheet for reading readingBook = openpyxl.load_workbook('D:\\Scripts\\produceSalesCopy.xlsx') readingSheet = readingBook.active #Creating the sheet for writing writingBook = openpyxl.Workbook() writingSheet = writingBook.active #Writing first N lines for row in range(1,N): for column in range(1,readingSheet.max_column+1): columnLetter = get_column_letter(column) writingSheet[columnLetter+str(row)] = readingSheet[columnLetter+str(row)].value #Writing next batch of lines after giving space of M number of lines for row in range(N,readingSheet.max_row+1): for column in range(1,readingSheet.max_column+1): columnLetter = get_column_letter(column) writingSheet[columnLetter+str(row+M)] = readingSheet[columnLetter+str(row)].value #Saving the workbook writingBook.save('D:\\Scripts\\'+filename)
import openpyxl, sys, os from openpyxl.utils import get_column_letter from openpyxl.styles import Font n = int(sys.argv[1]) os.chdir(input('Save to Directory: ')) wb = openpyxl.Workbook() sheet = wb.active for i in range(1, n + 1): sheet.cell(row=1, column=i + 1).value = i sheet.cell(row=i + 1, column=1).value = i sheet.cell(row=1, column=i + 1).font = Font(bold=True) sheet.cell(row=i + 1, column=1).font = Font(bold=True) for eachRow in range(1, n + 1): for eachCol in range(2, int(sheet.max_column) + 1): n1 = sheet['A' + str(eachRow + 1)].value n2 = sheet[get_column_letter(eachCol) + '1'].value sheet[get_column_letter(eachCol) + str(eachRow + 1)].value = n1 * n2 wb.save('multiplicationTable.xlsx') print('Multiplication Table is complete.')
from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.utils import get_column_letter wb = Workbook() dest_filename = 'empty_book.xlsx' ws1 = wb.active ws1.title = "range names" for row in range(1, 40): ws1.append(range(600)) ws2 = wb.create_sheet(title="Pi") ws2['F5'] = 3.14 ws3 = wb.create_sheet(title="Data") for row in range(10, 20): for col in range(27, 54): _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) print(ws3['AA10'].value) wb.save(filename=dest_filename) wb2 = load_workbook(filename=dest_filename) sheet_ranges = wb2['range names'] print(sheet_ranges['D18'].value)
def printToFile(xl, results, pairs, winLossMatrix, coasterDict, preferredFixedWidthFont, manuColors): print("Writing the results...", end=" ") if useSpinner: spinner = Spinner() spinner.start() # create and write primary results worksheet resultws = xl.create_sheet("Ranked Results") headerRow = [ "Rank", "Coaster", "Total Win Percentage", "Pairwise Win Percentage", "Total Wins", "Total Losses", "Total Ties", "Pair Wins", "Pair Losses", "Pair Ties", "Number of Riders" ] if args.botherRCDB: headerRow.extend(["Designer/Manufacturer", "Year"]) resultws.append(headerRow) resultws.column_dimensions['A'].width = 4.83 resultws.column_dimensions['B'].width = 45.83 resultws.column_dimensions['C'].width = 16.83 resultws.column_dimensions['D'].width = 18.83 resultws.column_dimensions['E'].width = 8.83 resultws.column_dimensions['F'].width = 9.83 resultws.column_dimensions['G'].width = 7.83 resultws.column_dimensions['H'].width = 8.33 resultws.column_dimensions['I'].width = 9.33 resultws.column_dimensions['J'].width = 7.33 resultws.column_dimensions['K'].width = 13.83 if args.botherRCDB: resultws.column_dimensions['L'].width = 23.83 resultws.column_dimensions['M'].width = 8.83 i = 2 for x in results: resultws.append([ coasterDict[x[0]].overallRank, x[0], coasterDict[x[0]].totalWinPercentage, coasterDict[x[0]].pairwiseWinPercentage, coasterDict[x[0]].totalWins, coasterDict[x[0]].totalLosses, coasterDict[x[0]].totalTies, coasterDict[x[0]].pairwiseWins, coasterDict[x[0]].pairwiseLosses, coasterDict[x[0]].pairwiseTies, coasterDict[x[0]].riders, coasterDict[x[0]].designer, coasterDict[x[0]].year ]) colorizeRow(resultws, i, [2, 12], coasterDict, x[0], manuColors) i += 1 resultws.freeze_panes = resultws['A2'] # append coasters that weren't ranked to the bottom of results worksheet for x in coasterDict.keys(): if x not in [y[0] for y in results] and coasterDict[x].riders > 0: resultws.append([ "N/A", x, "Insufficient Riders, {0}".format( coasterDict[x].totalWinPercentage), "Insufficient Riders, {0}".format( coasterDict[x].pairwiseWinPercentage), coasterDict[x].totalWins, coasterDict[x].totalLosses, coasterDict[x].totalTies, coasterDict[x].pairwiseWins, coasterDict[x].pairwiseLosses, coasterDict[x].pairwiseTies, coasterDict[x].riders, coasterDict[x].designer, coasterDict[x].year ]) colorizeRow(resultws, i, [2, 12], coasterDict, x, manuColors) i += 1 # append coasters that weren't ridden to the bottom of results worksheet for x in coasterDict.keys(): if x not in [y[0] for y in results] and coasterDict[x].riders == 0: resultws.append([ "N/A", x, "No Riders", "No Riders", coasterDict[x].totalWins, coasterDict[x].totalLosses, coasterDict[x].totalTies, coasterDict[x].pairwiseWins, coasterDict[x].pairwiseLosses, coasterDict[x].pairwiseTies, coasterDict[x].riders, coasterDict[x].designer, coasterDict[x].year ]) colorizeRow(resultws, i, [2, 12], coasterDict, x, manuColors) i += 1 # create and write pairwise result worksheet pairws = xl.create_sheet("Ranked Pairs") pairws.append([ "Rank", "Primary Coaster", "Rival Coaster", "Win Percentage", "Wins", "Losses", "Ties" ]) pairws.column_dimensions['A'].width = 4.83 pairws.column_dimensions['B'].width = 45.83 pairws.column_dimensions['C'].width = 45.83 pairws.column_dimensions['D'].width = 12.83 pairws.column_dimensions['E'].width = 4.5 pairws.column_dimensions['F'].width = 5.5 pairws.column_dimensions['G'].width = 3.83 i = 2 for x in pairs: pairws.append([ winLossMatrix[x[0][0], x[0][1]]["Pairwise Rank"], x[0][0], x[0][1], winLossMatrix[x[0][0], x[0][1]]["Win Percentage"], winLossMatrix[x[0][0], x[0][1]]["Wins"], winLossMatrix[x[0][0], x[0][1]]["Losses"], winLossMatrix[x[0][0], x[0][1]]["Ties"] ]) colorizeRow(pairws, i, [2], coasterDict, x[0][0], manuColors) colorizeRow(pairws, i, [3], coasterDict, x[0][1], manuColors) i += 1 pairws.freeze_panes = pairws['A2'] # create and write Mitch Hawker-style mutual rider comparison worksheet hawkerWLTws = xl.create_sheet("Coaster vs Coaster Win-Loss-Tie") headerRow = ["Rank", ""] for coaster in results: headerRow.append(coasterDict[coaster[0]].abbr) hawkerWLTws.append(headerRow) hawkerWLTws.column_dimensions['A'].width = 4.83 hawkerWLTws.column_dimensions['B'].width = 45.83 for col in range(3, len(results) + 3): hawkerWLTws.column_dimensions[get_column_letter(col)].width = 12.83 colorizeRow(hawkerWLTws, 1, [col], coasterDict, results[col - 3][0], manuColors) for i in range(0, len(results)): resultRow = [coasterDict[results[i][0]].overallRank, results[i][0]] winCount = 0 loseCount = 0 tieCount = 0 for j in range(0, len(results)): coasterA = results[i][0] coasterB = results[j][0] cellStr = "" if coasterA != coasterB: if winLossMatrix[coasterA, coasterB]["Wins"] > winLossMatrix[ coasterA, coasterB]["Losses"]: cellStr += "W " winCount += 1 elif winLossMatrix[coasterA, coasterB]["Wins"] < winLossMatrix[ coasterA, coasterB]["Losses"]: cellStr += "L " loseCount += 1 else: cellStr += "T " tieCount += 1 cellStr += str(winLossMatrix[coasterA, coasterB]["Wins"]) + "-" cellStr += str(winLossMatrix[coasterA, coasterB]["Losses"]) + "-" cellStr += str(winLossMatrix[coasterA, coasterB]["Ties"]) resultRow.append(cellStr) hawkerPct = ((winCount + (tieCount / float(2)) / float(len(results) - 1)) * 100) resultRow.append(hawkerPct) hawkerWLTws.append(resultRow) colorizeRow(hawkerWLTws, i + 2, [2], coasterDict, results[i][0], manuColors) hawkerWLTws.freeze_panes = hawkerWLTws['C2'] for col in hawkerWLTws.iter_cols(min_col=3): for cell in col: cell.font = preferredFixedWidthFont # create and write Mitch Hawker-style mutual rider comparison worksheet sorted by Pairwise Win Percentage resortedResults = sorted(results, key=lambda x: x[2], reverse=True) hawkerWLT2 = xl.create_sheet("CvC Win-Loss-Tie by PairWin%") headerRow = ["Rank", ""] if args.verbose > 0: print(" ") for x in resortedResults: headerRow.append(coasterDict[x[0]].abbr) if args.verbose > 0: print("Rank: {0},\tVal: {1}, \tCoaster: {2}".format( coasterDict[x[0]].overallRank, x[2], x[0])) hawkerWLT2.append(headerRow) hawkerWLT2.column_dimensions['A'].width = 4.83 hawkerWLT2.column_dimensions['B'].width = 45.83 for col in range(3, len(resortedResults) + 3): hawkerWLT2.column_dimensions[get_column_letter(col)].width = 12.83 colorizeRow(hawkerWLT2, 1, [col], coasterDict, resortedResults[col - 3][0], manuColors) for i in range(0, len(resortedResults)): resultRow = [ coasterDict[resortedResults[i][0]].overallRank, resortedResults[i][0] ] for j in range(0, len(resortedResults)): coasterA = resortedResults[i][0] coasterB = resortedResults[j][0] cellStr = "" if coasterA != coasterB: if winLossMatrix[coasterA, coasterB]["Wins"] > winLossMatrix[ coasterA, coasterB]["Losses"]: cellStr += "W " elif winLossMatrix[coasterA, coasterB]["Wins"] < winLossMatrix[ coasterA, coasterB]["Losses"]: cellStr += "L " else: cellStr += "T " cellStr += str(winLossMatrix[coasterA, coasterB]["Wins"]) + "-" cellStr += str(winLossMatrix[coasterA, coasterB]["Losses"]) + "-" cellStr += str(winLossMatrix[coasterA, coasterB]["Ties"]) resultRow.append(cellStr) hawkerWLT2.append(resultRow) colorizeRow(hawkerWLT2, i + 2, [2], coasterDict, resortedResults[i][0], manuColors) hawkerWLT2.freeze_panes = hawkerWLT2['C2'] for col in hawkerWLT2.iter_cols(min_col=3): for cell in col: cell.font = preferredFixedWidthFont # create and write sheet to compare where coasters would have ranked if sorted by PairWin% comparisonws = xl.create_sheet("TotalWin% vs PairWin% Rankings") comparisonws.append( ["Coaster", "TotalWin% Rank", "PairWin% Rank", "Difference"]) comparisonws.column_dimensions['A'].width = 45.83 comparisonws.column_dimensions['B'].width = 12.83 comparisonws.column_dimensions['C'].width = 12.83 for i in range(0, len(resortedResults)): coaster = resortedResults[i][0] oldRank = coasterDict[coaster].overallRank newRank = i + 1 diff = oldRank - newRank if diff == 0: comparisonws.append([coaster, oldRank, newRank, ""]) else: comparisonws.append([coaster, oldRank, newRank, diff]) if diff <= -16: diffColor = "ff0000" # maximum red elif diff < 0: diffColor = "ff{0}{0}".format( hex(256 + 16 * diff)[2:]) # gradual red elif diff < 16: diffColor = "{0}ff{0}".format( hex(256 - 16 * diff)[2:]) # gradual green else: diffColor = "00ff00" # maximum green comparisonws.cell(row=i + 2, column=4).fill = PatternFill("solid", fgColor=diffColor) colorizeRow(comparisonws, i + 2, [1], coasterDict, coaster, manuColors) comparisonws.freeze_panes = comparisonws['A2'] if useSpinner: spinner.stop() print(" ")
def validaAux(fila): sheet = wb['hoja_de_trabajo_bys'] tipo = "" for row in range(2, sheet.max_row + 1): if (sheet['F' + str(row)].value == fila["grupo"] and sheet['H' + str(row)].value == fila["subgrupo"] and sheet['L' + str(row)].value == fila["procedencia"] and sheet['Q' + str(row)].value == fila["estado"] and sheet['N' + str(row)].value == str( fila["categoria"])): # Compara filtros if (sheet[fila["celda"] + str(row)].value <> '0' and sheet[fila["celda"] + str(row)].value is not None ): # Valida que exista un ajuste en bienes y servicios if (esMayorSumaCH(fila["numajuste"], sheet['R' + str(row)].value, sheet['P' + str(row)].value) and float(sheet[fila["celda"] + str(row)].value) > 0): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion MAYOR global contador contador += 1 muestra_valores(fila) tipo = "esMayorSumaCH" print tipo return False elif (esMenorSumaCH(fila["numajuste"], sheet['R' + str(row)].value, sheet['P' + str(row)].value) and float(sheet[fila["celda"] + str(row)].value) < 0): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esMenorSumaCH" print tipo return False elif (esMenorSumaDe(fila["numajuste"], sheet['S' + str(row)].value, sheet['R' + str(row)].value, sheet['P' + str(row)].value) and float(sheet[fila["celda"] + str(row)].value) < 0): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esMenorSumaDe" print tipo return False elif (esMayorSumaDe(fila["numajuste"], sheet['S' + str(row)].value, sheet['R' + str(row)].value, sheet['P' + str(row)].value) and float(sheet[fila["celda"] + str(row)].value) > 0): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esMayorSumaDe" print tipo return False elif (esBajaSuma(sheet['Q' + str(row)].value, sheet['P' + str(row)].value, fila["numajuste"])): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esBajaSuma" print tipo return False elif (esEliminacionDepreSuma(fila["numajuste"], sheet['S' + str(row)].value, sheet['P' + str(row)].value, sheet['Q' + str(row)].value)): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esEliminacionDepreSuma" print tipo return False elif (esEliminacionProviValoDesSuma( sheet['Q' + str(row)].value, sheet['R' + str(row)].value, sheet['P' + str(row)].value, fila["numajuste"]) and float(sheet[fila["celda"] + str(row)].value) <> 0): if (sheet[get_column_letter( column_index_from_string(fila["celda"]) + 2) + str(row)].value <> str(fila["auxPartida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 5) + str(row)].value <> str( fila["auxContraPartida"]) and sheet[get_column_letter( column_index_from_string(fila["celda"]) + 1) + str(row)].value <> str(fila["Partida"]) or sheet[get_column_letter( column_index_from_string(fila["celda"]) + 4) + str(row)].value <> str( fila["ContraPartida"]) ): # Valida cuentas de costo hisotrico y depreciacion menor global contador contador += 1 muestra_valores(fila) tipo = "esEliminacionProviValoDesSuma" print tipo return False return True
row_start = 1 col_start = 1 row_ends = 29 col_ends = 12 row_span = row_ends - row_start + 1 modi_value = 202000220 string = '合肥惠丰电子科技有限公司 IT末梢终端耗材、零配件出库单 NO:YS03 ' for n in range(counts): for rows in range(row_start, row_ends + 1): wbsheet_new.row_dimensions[ rows + n * row_span].height = wbsheet.row_dimensions[rows].height for cols in range(col_start, col_ends): wbsheet_new.column_dimensions[get_column_letter( cols)].width = wbsheet.column_dimensions[get_column_letter( cols)].width target_cell = wbsheet_new.cell(row=rows + n * row_span, column=cols, value=wbsheet.cell(rows, cols).value) if (rows == row_start and cols == col_start): target_cell = wbsheet_new.cell(row=rows + n * row_span, column=cols, value=string + str(modi_value)) modi_value += 1 if (rows == row_start + 10 and cols == col_start): target_cell = wbsheet_new.cell(row=rows + n * row_span, column=cols, value=string + str(modi_value)) modi_value += 1
def get_processed_df_with_formula(df, dept_num): df_with_row = df.copy() df_with_row['ROW_NUMBER'] = df_with_row.index + 2 try: df_header, start_index = get_clean_dataframe(df_with_row, 'date', -1) except ValueError: df_header, _ = get_clean_dataframe(df_with_row, 'year', -1) df_header.columns = list(df_header.columns)[:-1] + ['ROW_NUMBER'] columns_orig = [ item.upper() if isinstance(item, str) else item for item in df_header.columns ] df_processed = preprocess_df(df_header, -1, ['ROW_NUMBER']) year_month_list = list( set([item for item in zip(df_processed.YEAR, df_processed.MONTH)])) output_columns = ['DEPARTMENT ID', 'YEAR', 'MONTH', 'PAYMENT', 'BUDGET'] output_df = pd.DataFrame(columns=output_columns) for i, (year, month) in enumerate(year_month_list): output_df.loc[i, 'DEPARTMENT ID'] = dept_num output_df.loc[i, 'YEAR'] = year output_df.loc[i, 'MONTH'] = month section = df_processed[((df_processed.YEAR == year) & (df_processed.MONTH == month))] rows = section['ROW_NUMBER'] rows = sorted(rows) payment_header = [ item for item in columns_orig if isinstance(item, str) and ( item.find('PAYMENT') > -1 or item.find('SPENDING') > -1) and item.find('CUM') == -1 ][0] column_letter_payment = get_column_letter( columns_orig.index(payment_header) + 1) output_df.loc[ i, 'PAYMENT'] = f"=SUM('Support Dept {dept_num}'!{column_letter_payment}{rows[0]}:{column_letter_payment}{rows[-1]})" #print(f'original header is {columns_orig}, \n payment column is {column_letter_payment}') budget_max = section['BUDGET'].max() budget_row = rows[list(section['BUDGET']).index(budget_max)] budget_header = [ item for item in columns_orig if isinstance(item, str) and item.find('BUDGET') > -1 and item.find('CUM') == -1 ][0] column_letter_budget = get_column_letter( columns_orig.index(budget_header) + 1) output_df.loc[ i, 'BUDGET'] = F"='Support Dept {dept_num}'!{column_letter_budget}{budget_row}" #print(f'original header is {columns_orig}, \n budfet column is {column_letter_budget}') return output_df
print(c.value) print('Row' + str(c.row) + ', Column ' + str(c.column) +' is ' + str(c.value)) print('Cell' + c.coordinate + ' is ' +c.value) print(sheet['C1'].value) #specifying column and row using integers print(sheet.cell(row=1, column=2)) print(sheet.cell(row=1, column=2).value) for i in range(1,8,2): print(i, sheet.cell(row=i, column=2).value) print(sheet.max_row) print(sheet.max_column) #Converting between Column Letter and Numbers from openpyxl.utils import get_column_letter, column_index_from_string print(get_column_letter(1)) print(get_column_letter(2)) print(get_column_letter(27)) print(get_column_letter(920)) print(get_column_letter(sheet.max_column)) print(column_index_from_string('A')) print(column_index_from_string('AA')) #Getting rows and columns from the sheets print(tuple(sheet['A1' :'C3'])) for rowOfCellObjects in sheet['A1':'C3']: for cellObj in rowOfCellObjects: print(cellObj.coordinate, cellObj.value) print('---END OF ROW ---')
format=' %(asctime)s - %(levelname)s- %(message)s') wb = openpyxl.load_workbook('bob.xlsx') logging.info(type(wb)) sheet = wb.worksheets[0] #first sheet logging.info('first worksheet: ' + sheet.title) logging.info('first cell: value-' + sheet['A1'].value + ' row-' + str(sheet['A1'].row) + ' column-' + str(sheet['A1'].column) + ' coordinate-' + sheet['A1'].coordinate) logging.info('first cell: value-' + sheet.cell(1, 1).value + ' row-' + str(sheet.cell(1, 1).row) + ' column-' + str(sheet.cell(1, 1).column) + ' coordinate-' + sheet.cell(1, 1).coordinate) logging.info('len(row): ' + str(sheet.max_row)) logging.info('len(column): ' + str(sheet.max_column)) logging.info('translate int to str: ' + get_column_letter(sheet.max_row)) logging.info('translate str to int: ' + str(column_index_from_string('B'))) logging.info(tuple(sheet['A1':'C3'])) logging.info('=================excel write==================') wb1 = openpyxl.Workbook() ##create a new workbook sheet1 = wb1.worksheets[0] logging.info('sheet title: ' + sheet1.title) #default sheet name is 'Sheet' sheet1.title = 'Spam Bacon Eggs sheet' ##Modify first sheet name logging.info('sheet title: ' + sheet1.title) wb1.save('bobTestExcel.xlsx') wb2 = openpyxl.Workbook() wb2.create_sheet() wb2.create_sheet(index=0, title='1st sheet') wb2.create_sheet(index=2, title='middle sheet')
def format_statistics_sheet(self, sheet_name="Statistics"): """ Formatting for Statistics Sheet """ # Statistics number_format_list = { 'C': '0', 'E': '0.000', 'F': '0.000000', 'G': '0.00%', 'I': '0.00', 'J': '0.00', 'K': '0.00%', 'M': '0.0', 'N': '0.0000', 'O': '0.00%', 'Q': '0.0', 'R': '0.0000', 'S': '0.00%', 'U': '0.0', 'V': '0.0000', 'W': '0.00%', 'Y': '0.0', 'Z': '0.000', 'AA': '0.000%' } width_dict = { "B": 12, "C": 2, "D": 1, "E": 8, "F": 11, "G": 8, "H": 1, "I": 13, "J": 12, "K": 10, "L": 1, "M": 8, "N": 10, "O": 10, "P": 1, "Q": 8, "R": 10, "S": 10, "T": 1, "U": 6, "V": 8, "W": 10, "X": 1, "Y": 8, "Z": 9, "AA": 9 } alignment_list = {"B": "center", "C": "center"} ws = self.wb[sheet_name] format_sheet(ws, number_format_list, width_dict, alignment_list) for offset in range(6): ws.merge_cells( get_column_letter(5 + (4 * offset)) + '1:' + get_column_letter(7 + (4 * offset)) + '1') format_header( ws, make_column_property_dict({"2": "center"}, repeat=27, length=1), 1, 2)
# for cell in col: # cell.number_format = "DD/MM/YYYY" # cell.alignment = CENTER for x in range(1, 14): cf.cell(row=2, column=3 + x, value=x) # enter a date with yellow background cf['D3'] = today_date cf['D3'].font = FONT_BOLD cf['D3'].fill = YELLOW_BKGROUND cf['D3'].alignment = CENTER for col in range(4, 17): cf.column_dimensions['{0}'.format(get_column_letter(col))].width = 13 cf['{0}2'.format(get_column_letter(col))].alignment = CENTER cf['{0}2'.format(get_column_letter(col))].font = FONT_BOLD cf['{0}3'.format(get_column_letter(col))].number_format = "DD/MM/YYYY" if col > 4: # calculate weekly dates dt = today_date start = dt - timedelta(days=-7 * (col - 4)) cf['{0}3'.format(get_column_letter(col))].value = start cf['{0}3'.format(get_column_letter(col))].alignment = CENTER cf['{0}3'.format(get_column_letter(col))].font = FONT_BOLD prev_cb = load_workbook(LAST_CASHFLOW_FORECAST_FILENAME)
def read_excel(self): wb = xl.load_workbook(self.main_excel_path) sheet = wb['Лист1'] row_max = sheet.max_row row_min = 1 for i in range(1, row_max): cell = sheet.cell(row=i, column=1) cell_row_val = cell.value if cell_row_val == 'Номер файла' and row_min == 1: row_min = i first_col_row_nums = [] cols_dict = {} try: type_dict = self.read_type_file() except: self.files_classification() type_dict = self.read_type_file() signal_type_nums = type_dict['signal_nums'] file_nums_1, file_nums_2 = [], [] for i_row in range(row_min, row_max + 1): first_col_cell_val = sheet.cell(row=i_row, column=1).value if isinstance(first_col_cell_val, int): first_col_row_nums.append(i_row) fnum_1, fnum_2 = self.fnums_delim(first_col_cell_val) if fnum_1 in signal_type_nums: file_nums_1.append(fnum_1) else: file_nums_1.append(None) if fnum_2 in signal_type_nums: file_nums_2.append(fnum_2) else: file_nums_2.append(None) m = 2 cell = sheet.cell(row=row_min, column=m) col_name = cell.value keys = [] while type(col_name) is str: col_vals = [] keys.append(col_name) #print(get_column_letter(m), sheet.cell(row=row_min, column=m).value) for i_row in range(row_min, row_max): if i_row in first_col_row_nums: cell_val = sheet.cell(row=i_row, column=m).value col_vals.append(cell_val) cols_dict[col_name] = col_vals if m == 2: num_of_vals = len(col_vals) next_col_name = sheet.cell(row=row_min, column=m + 1).value if type(next_col_name) is str: m += 1 col_name = sheet.cell(row=row_min, column=m).value else: break col_max = m print('Последняя колонка:', get_column_letter(col_max), sheet.cell(row=row_min, column=col_max).value) pl_currents = cols_dict['Ток плазмы, А'] magnetron_delays = cols_dict['Задержка магнетрона, нс'] comments = cols_dict['Комментарий'] mampl_nums, noise_nums, other_nums = [], [], [] for i in range(num_of_vals): pl_c_val = pl_currents[i] m_delay = magnetron_delays[i] if isinstance(pl_c_val, float): if isinstance(m_delay, int): if file_nums_1[i] is not None: mampl_nums.append(file_nums_1[i]) if file_nums_2[i] is not None: mampl_nums.append(file_nums_2[i]) else: if file_nums_1[i] is not None: noise_nums.append(file_nums_1[i]) if file_nums_2[i] is not None: noise_nums.append(file_nums_2[i]) else: if file_nums_1[i] is not None: other_nums.append(file_nums_1[i]) if file_nums_2[i] is not None: other_nums.append(file_nums_2[i]) row_dicts = {} for i in range(num_of_vals): row_dict = {} for key in keys: if cols_dict[key][i] is not None: row_dict[key] = cols_dict[key][i] if file_nums_1[i] is not None: row_dicts[file_nums_1[i]] = row_dict if file_nums_2[i] is not None: row_dicts[file_nums_2[i]] = row_dict excel_inf = {'row_dicts': row_dicts, 'comments': comments, 'current_vals': pl_currents, 'noise_nums': noise_nums, 'mamp_nums': mampl_nums, 'other_nums': other_nums} return excel_inf
def exportWorkbook(request): # Create a workbook wb = Workbook(write_only=True) # Create a named style for the header row headerstyle = NamedStyle(name="headerstyle") headerstyle.fill = PatternFill(fill_type="solid", fgColor="70c4f4") wb.add_named_style(headerstyle) readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Loop over all selected entity types exportConfig = {"anonymous": request.POST.get("anonymous", False)} ok = False for entity_name in request.POST.getlist("entities"): try: # Initialize (app_label, model_label) = entity_name.split(".") model = apps.get_model(app_label, model_label) # Verify access rights permname = get_permission_codename("change", model._meta) if not request.user.has_perm("%s.%s" % (app_label, permname)): continue # Never export some special administrative models if model in EXCLUDE_FROM_BULK_OPERATIONS: continue # Create sheet ok = True ws = wb.create_sheet(title=force_text(model._meta.verbose_name)) # Build a list of fields and properties fields = [] modelfields = [] header = [] source = False lastmodified = False owner = False comment = None try: # The admin model of the class can define some fields to exclude from the export exclude = data_site._registry[model].exclude except Exception: exclude = None for i in model._meta.fields: if i.name in ["lft", "rght", "lvl"]: continue # Skip some fields of HierarchyModel elif i.name == "source": source = i # Put the source field at the end elif i.name == "lastmodified": lastmodified = i # Put the last-modified field at the very end elif not (exclude and i.name in exclude): fields.append(i.column) modelfields.append(i) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) if i.name == "owner": owner = True if hasattr(model, "propertyFields"): if callable(model.propertyFields): props = model.propertyFields(request) else: props = model.propertyFields for i in props: if i.export: fields.append(i.name) cell = WriteOnlyCell(ws, value=force_text( i.verbose_name).title()) if i.editable: cell.style = "headerstyle" if isinstance(i, ForeignKey): cell.comment = CellComment( force_text( _("Values in this field must exist in the %s table" ) % force_text(i.remote_field.model. _meta.verbose_name)), "Author", ) elif i.choices: cell.comment = CellComment( force_text( _("Accepted values are: %s") % ", ".join([c[0] for c in i.choices])), "Author", ) else: cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment( force_text(_("Read only")), "Author", height=20, width=80, ) cell.comment = comment header.append(cell) modelfields.append(i) if source: fields.append("source") cell = WriteOnlyCell(ws, value=force_text(_("source")).title()) cell.style = "headerstyle" header.append(cell) modelfields.append(source) if lastmodified: fields.append("lastmodified") cell = WriteOnlyCell(ws, value=force_text( _("last modified")).title()) cell.style = "readlonlyheaderstyle" if not comment: comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) cell.comment = comment header.append(cell) modelfields.append(lastmodified) # Write a formatted header row ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Use the default manager if issubclass(model, HierarchyModel): model.rebuildHierarchy(database=request.database) query = (model.objects.all().using(request.database).order_by( "lvl", "pk")) elif owner: # First export records with empty owner field query = (model.objects.all().using(request.database).order_by( "-owner", "pk")) else: query = model.objects.all().using( request.database).order_by("pk") # Special annotation of the export query if hasattr(model, "export_objects"): query = model.export_objects(query, request) # Loop over all records for rec in query.values_list(*fields): cells = [] fld = 0 for f in rec: cells.append( _getCellValue(f, field=modelfields[fld], exportConfig=exportConfig)) fld += 1 ws.append(cells) except Exception: pass # Silently ignore the error and move on to the next entity. # Not a single entity to export if not ok: raise Exception(_("Nothing to export")) # Write the excel from memory to a string and then to a HTTP response output = BytesIO() wb.save(output) response = HttpResponse( content_type= "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", content=output.getvalue(), ) response["Content-Disposition"] = 'attachment; filename="frepple.xlsx"' response["Cache-Control"] = "no-cache, no-store" return response
def generate_expense_report(salesman, date_range, expense_list, r_format='xls'): expense_report_stream = BytesIO() total_expenses = 0 if r_format == 'xls': expense_report = Workbook() main_sheet = expense_report.active # Set up some initial styles main_sheet.freeze_panes = 'B2' main_sheet.row_dimensions[2].height = 32 main_sheet.row_dimensions[3].height = 22 main_sheet.row_dimensions[4].height = 22 main_sheet.column_dimensions['A'].width = 22 # Enter the name of the Salesman main_sheet['A2'] = 'Name' main_sheet['A2'].font = Font(name='Helvetica Neue', size=12, bold=True, color='FFFFFF') main_sheet['A2'].fill = PatternFill(start_color='004C7F', end_color='004C7F', fill_type='solid') main_sheet['B2'] = str(salesman) # Loop through the days and add the columns date_range_it = maya.MayaInterval( start=maya.when(date_range.split(' - ')[0]), end=maya.when(date_range.split(' - ')[1]).add(days=1)) col_idx = 2 day_col_labels = {} all_col_labels = [] for event in date_range_it.split(duration=timedelta(days=1)): col_label = get_column_letter(col_idx) day_col_labels[event.start.iso8601().split('T')[0]] = col_label # Apply styles to the columns main_sheet.column_dimensions[col_label].width = 16 main_sheet[col_label + '2'].font = Font(name='Helvetica Neue', size=12, bold=True, color='FFFFFF') main_sheet[col_label + '2'].fill = PatternFill( start_color='004C7F', end_color='004C7F', fill_type='solid') main_sheet[col_label + '3'].font = Font(name='Helvetica Neue', size=11, bold=True, color='FFFFFF') main_sheet[col_label + '3'].fill = PatternFill( start_color='2F7115', end_color='2F7115', fill_type='solid') main_sheet[col_label + '3'] = \ event.start.iso8601().split('T')[0] all_col_labels.append(col_label) col_idx += 1 last_col_label = get_column_letter(col_idx) all_col_labels.append(last_col_label) # Set the date range in the sheet main_sheet[all_col_labels[-2] + '2'] = 'Date Range' main_sheet.column_dimensions[last_col_label].width = 16 main_sheet[last_col_label + '2'] = date_range main_sheet[last_col_label + '2'].alignment = Alignment(wrap_text=True) main_sheet[last_col_label + '2'].font = Font(name='Helvetica Neue', size=12, bold=True, color='FFFFFF') main_sheet[last_col_label + '2'].fill = PatternFill( start_color='004C7F', end_color='004C7F', fill_type='solid') main_sheet[last_col_label + '3'] = '(Total)' main_sheet[last_col_label + '3'].font = Font(name='Helvetica Neue', size=11, bold=True, color='FFFFFF') main_sheet[last_col_label + '3'].fill = PatternFill( start_color='2F7115', end_color='2F7115', fill_type='solid') # Loop through the paid by and add it as a group cur_row = 4 for paid_by in expense_list.keys(): # Set the header for cash expenses main_sheet['A%s' % cur_row] = paid_by main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue', size=10, bold=True, color='FFFFFF') main_sheet['A%s' % cur_row].fill = PatternFill( start_color='004C7F', end_color='004C7F', fill_type='solid') for col_label in all_col_labels: main_sheet[col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True, color='FFFFFF') main_sheet[col_label + str(cur_row)].fill = PatternFill( start_color='004C7F', end_color='004C7F', fill_type='solid') cur_row += 1 # Add all the cash expenses here for expense_type, amounts in expense_list[paid_by].items(): if expense_type != 'total': main_sheet['A%s' % cur_row] = expense_type main_sheet['A%s' % cur_row].font = Font( name='Helvetica Neue', size=10, bold=True) main_sheet['A%s' % cur_row].alignment = Alignment(wrap_text=True) line_amount = 0 for t_date, amount in amounts.items(): col_label = day_col_labels[t_date] main_sheet[col_label + str(cur_row)] = amount main_sheet[col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=False) line_amount += amount main_sheet[last_col_label + str(cur_row)] = line_amount main_sheet[last_col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True) cur_row += 1 # Set the trailer for cash expenses main_sheet.row_dimensions[cur_row].height = 22 main_sheet['A%s' % cur_row] = 'Total %s Expenses' % paid_by main_sheet['A%s' % cur_row].font = Font(name='Helvetica Neue', size=10, bold=True) line_amount = 0 for t_date, amount in expense_list[paid_by]['total'].items(): col_label = day_col_labels[t_date] main_sheet[col_label + str(cur_row)] = amount main_sheet[col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True) line_amount += amount main_sheet[last_col_label + str(cur_row)] = line_amount main_sheet[last_col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True) total_expenses += line_amount cur_row += 1 # Set the trailer for the whole file main_sheet.row_dimensions[cur_row].height = 22 main_sheet[all_col_labels[-2] + str(cur_row)] = 'Total:' main_sheet[all_col_labels[-2] + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True) main_sheet[last_col_label + str(cur_row)] = total_expenses main_sheet[last_col_label + str(cur_row)].font = Font( name='Helvetica Neue', size=10, bold=True) # Finally create a table for this wsprops = main_sheet.sheet_properties wsprops.pageSetUpPr = PageSetupProperties(fitToPage=True, autoPageBreaks=False) # Create the report and create the django response from it expense_report.save(expense_report_stream) elif r_format == 'pdf': expense_report = SimpleDocTemplate(expense_report_stream, rightMargin=72, leftMargin=72, topMargin=30, bottomMargin=72, pagesize=landscape(A4)) # Loop through the days and add the columns date_range_it = maya.MayaInterval( start=maya.when(date_range.split(' - ')[0]), end=maya.when(date_range.split(' - ')[1]).add(days=1)) report_days = [] for event in date_range_it.split(duration=timedelta(days=1)): report_days.append(event.start.iso8601().split('T')[0]) total_columns = len(report_days) + 1 report_data = [['Name', str(salesman)] + [''] * (total_columns - 3) + ['Date Range', date_range], [''] + report_days + ['(Total)']] report_style = [ ('INNERGRID', (0, 0), (-1, -1), 0.5, colors.black), ('BOX', (0, 0), (-1, -1), 0.5, colors.black), ('FONTSIZE', (0, 0), (-1, -1), 8), ('BACKGROUND', (0, 0), (total_columns, 0), '#004C7F'), ('FONTNAME', (0, 0), (total_columns, 0), 'Helvetica-Bold'), ('FONTSIZE', (0, 0), (total_columns, 0), 10), ('TEXTCOLOR', (0, 0), (total_columns, 0), colors.white), ('BACKGROUND', (1, 1), (total_columns, 1), '#2F7115'), ('FONTNAME', (1, 1), (total_columns, 1), 'Helvetica-Bold'), ('FONTSIZE', (1, 1), (total_columns, 1), 9), ('TEXTCOLOR', (1, 1), (total_columns, 1), colors.white) ] cur_row = 2 for paid_by in expense_list.keys(): # Add the header for this paid_by report_data.append([paid_by] + [''] * total_columns) report_style.extend([('BACKGROUND', (0, cur_row), (total_columns, cur_row), '#004C7F'), ('FONTNAME', (0, cur_row), (total_columns, cur_row), 'Helvetica-Bold'), ('FONTSIZE', (0, cur_row), (total_columns, cur_row), 8), ('TEXTCOLOR', (0, cur_row), (total_columns, cur_row), colors.white)]) cur_row += 1 # Add all the expenses for this paid_by for expense_type, amounts in expense_list[paid_by].items(): if expense_type != 'total': line = [expense_type] line_amount = 0 for day in report_days: line.append(amounts.get(day, '')) line_amount += amounts.get(day, 0) line.append(line_amount) report_data.append(line) report_style.extend([ ('FONTNAME', (0, cur_row), (0, cur_row), 'Helvetica-Bold'), ('FONTNAME', (total_columns, cur_row), (total_columns, cur_row), 'Helvetica-Bold'), ]) cur_row += 1 # Add the trailer for this paid_by pd_trailer = ['Total %s Expenses' % paid_by] line_totals = 0 for day in report_days: pd_trailer.append(expense_list[paid_by]['total'].get(day, '')) line_totals += expense_list[paid_by]['total'].get(day, 0) pd_trailer.append(line_totals) report_data.append(pd_trailer) report_style.append(('FONTNAME', (0, cur_row), (total_columns, cur_row), 'Helvetica-Bold')) total_expenses += line_totals cur_row += 1 # Add the report trailer report_data.append([''] * (total_columns - 1) + ['Total:', total_expenses]) report_style.append(('FONTNAME', (0, cur_row), (total_columns, cur_row), 'Helvetica-Bold')) # Create the table and Set the style report_table = Table(report_data) report_table.setStyle(TableStyle(report_style)) expense_report.build([report_table]) return expense_report_stream.getvalue()
wb = openpyxl.Workbook() sheet = wb.active make_bold = Font(bold=True) while cells > 1: # Write outer cells values and make them bold sheet.cell(row=cells, column=1).value = cells - 1 sheet.cell(row=1, column=cells).value = cells - 1 sheet.cell(row=cells, column=1).font = make_bold sheet.cell(row=1, column=cells).font = make_bold cells -= 1 # Populate the table innards with correct formula col_length = number + 1 count = 0 while count < number: colLetter = get_column_letter(sheet.max_column - count) while col_length > 1: sheet[colLetter + str(col_length)] = ('=SUM(' + colLetter + '1*A' + str(col_length) + ')') col_length -= 1 col_length = number + 1 count += 1 wb.save('multi_table.xlsx')
def validate(settings, excelFile, sheetName, tmpDir, printErrors=False): '''the main function of valitations, takes settings dictionary (validations) and returns the validation result ''' print("Validate Excel Sheet " + sheetName) errors = [] #open Excel file print("Parse Excel file") wb = load_workbook(excelFile, keep_vba=True, data_only=True, read_only=True) #ws = wb.get_sheet_by_name(sheetName) ws = wb[sheetName] progressBar = Bar('Processing', max=ws.max_row) if 'range' in settings and settings['range'] != None: settings['range'] = settings['range'] + (str)(ws.max_row) # range now equals A1:D(150) for example #iterate excel sheet rowCounter = 0 for row in ws.iter_rows(settings['range']): progressBar.next() columnCounter = 0 rowCounter = rowCounter + 1 #do not parse empty rows if isEmpty(row): continue for cell in row: columnCounter = columnCounter + 1 try: value = cell.value except ValueError: #case when it is not possible to read value at all from any reason column = get_column_letter(columnCounter) coordinates = "%s%d" % (column, rowCounter) errors.append((coordinates, ValueError)) #find header (first) row #if the code founded the first header "ID", then it is the header row if settings['header'] != True: if value == settings['header']: #Replace the header with true incase it meets a value of "ID" downside settings['header'] = True # skip el row break #skip excludes column if hasattr(cell, 'column') and cell.column in settings['excludes']: continue column = get_column_letter(columnCounter) coordinates = "%s%d" % (column, rowCounter) ## column:A Coordinate:A2, for example if column in settings['validators']: for type in settings['validators'][column]: name = list(type.keys())[0] # notblank, Regex, Length if name != 'Conditional': res = isValid(type, value, coordinates, errors) else: fieldB = list(type.values())[0]['fieldB'] value2 = ws[fieldB + str(rowCounter)].value res = isValid(type, value, coordinates, errors, value2) if not res: break elif settings['defaultValidator'] != None: isValid(settings['defaultValidator'], value, coordinates, errors) progressBar.finish() print("Found %d error(s)" % len(errors)) if (len(errors) > 0): return markErrors(errors, excelFile, sheetName, tmpDir, printErrors) return True
def getDateColumn(): for i in range(1, sheet.max_column): col = get_column_letter(i) if sheet.cell('%s%s'% (col,'1')).value == currentDate: return col
async def answer_serial_num(message: types.Message, state=FSMContext): path_to_file_1 = os.path.abspath('out.xlsx') path_to_file_2 = os.path.abspath('stock_balance.xlsx ') path_to_file_3 = os.path.abspath('boriychuk.xlsx') path_to_file_4 = os.path.abspath('other.xlsx') path_to_file = [ path_to_file_1, path_to_file_2, path_to_file_3, path_to_file_4 ] data = await state.get_data() name_list = data.get('answer1') i = data.get('answer2') answer = message.text fin_mess_graph = {} try: for path in path_to_file: graph = {} regular = str( name_list[int(answer) - 1] ) # в переменную answer помещяется то, что ответил пользователь в ТГ wb = openpyxl.load_workbook(path) # Грузим наш прайс-лист sheets_list = wb.sheetnames # Получаем список всех листов в файле sheet_active = wb[ sheets_list[0]] # Начинаем работать с самым первым row_max = sheet_active.max_row # Получаем количество столбцов column_max = 3 row_min = 1 # Переменная, отвечающая за номер строки column_min = 1 # Переменная, отвечающая за номер столбца while column_min <= column_max: row_min_min = row_min row_max_max = row_max while row_min_min <= row_max_max: row_min_min = str(row_min_min) word_column = get_column_letter(column_min) word_column = str(word_column) word_cell = word_column + row_min_min data_from_cell = sheet_active[word_cell].value data_from_cell = str(data_from_cell) result = re.findall(regular.lower(), data_from_cell.lower()) if len(result) > 0: graph['address'] = word_cell graph['path'] = path # каждая ячейка, содержащая в себе нужное нам значение, помещается в лист row_min_min = int(row_min_min) row_min_min = row_min_min + 1 column_min = column_min + 1 if len(graph) > 0: if graph['path'] == path_to_file_1: sel_num = graph['address'][1:] sel_num = int(sel_num) main_product_name = sheet_active[sel_num][1].value product_name = sheet_active[sel_num][1].value main_manufacture = sheet_active[sel_num][2].value main_quantity = sheet_active[sel_num][3].value cost_opt = sheet_active[sel_num][4].value cost_mem = sheet_active[sel_num][5].value main_cost_dlr = sheet_active[sel_num][6].value main_cost_grn = sheet_active[sel_num][7].value fin_mess_graph[ 'main_quantity'] = f'\nСклад "Навионика" кол-во: {main_quantity}' elif graph['path'] == path_to_file_2: sel_num = graph['address'][1:] sel_num = int(sel_num) product_name = sheet_active[sel_num][1].value main_manufacture = sheet_active[sel_num][2].value sh_quantity = sheet_active[sel_num][3].value cost = sheet_active[sel_num][5].value try: sh_opt_cost = sheet_active[sel_num][4].value sh_opt_cost = round(float(sh_opt_cost)) except: sh_opt_cost = 'Уточняйте' fin_mess_graph[ 'sh_quantity'] = f'\nСклад "Магазин" кол-во: {sh_quantity}' elif graph['path'] == path_to_file_3: sel_num = graph['address'][1:] sel_num = int(sel_num) product_name = sheet_active[sel_num][1].value main_manufacture = sheet_active[sel_num][2].value b_quantity = sheet_active[sel_num][5].value try: cost = float(sheet_active[sel_num][4].value) * 1.1 cost = round(cost) b_opt_cost = sheet_active[sel_num][4].value except: cost = 'Уточняйте' b_opt_cost = 'Уточняйте' fin_mess_graph[ 'b_quantity'] = f'\nСклад "Борийчук" кол-во: {b_quantity}' elif graph['path'] == path_to_file_4: sel_num = graph['address'][1:] sel_num = int(sel_num) product_name = sheet_active[sel_num][1].value main_manufacture = sheet_active[sel_num][2].value o_quantity = sheet_active[sel_num][5].value try: cost = float(sheet_active[sel_num][4].value) * 1.1 cost = round(cost) o_opt_cost = sheet_active[sel_num][4].value except: cost = 'Уточняйте' o_opt_cost = 'Уточняйте' fin_mess_graph[ 'o_quantity'] = f'\nСклад "Другие поставщики" кол-во: {o_quantity}' fin_mess = '' try: fin_mess += f'\n{main_product_name}' except: fin_mess += f'\n{product_name}' fin_mess += f'\nШифр производителя: {main_manufacture}' try: fin_mess += f'\nРРЦ долар с главного склада: {main_cost_dlr}' fin_mess += f'\nРРЦ грн с главного склада: {main_cost_grn}' fin_mess += f'\nОпт цена для партнёров: {cost_mem}' except: fin_mess += f'\nОпт цена для партнёров: {cost}' for quantity in fin_mess_graph: fin_mess += fin_mess_graph[quantity] await message.answer(fin_mess) except: await message.answer('Такого номера нет в списке...')
def column(self): """column letter""" return (self.col_idx or '') and get_column_letter(self.col_idx)
def set_col_size(self, col, size): self.ws.column_dimensions[get_column_letter(col)].width = size
def read_data(self): # Use the first sheet ws_name = self.wb.sheetnames[0] self.ws = self.wb[ws_name] # Get row 1 self.header_vals = [] for iCol in range(1, self.ws.max_column+1): iCol_L = get_column_letter(iCol) cCell = self.ws['%s1'%(iCol_L)] self.header_vals.append(cCell.value) # Check for header if 'train' in self.header_vals: self.has_header = True self.iRow_skip = 1 else: self.has_header = False self.header_vals = ['sentences', 'train', 'classify', 'test', 'correct_train', 'correct_test'] self.iRow_skip = 0 # Get the index of columns if 'sentences' in self.header_vals: self.index_sent = self.header_vals.index('sentences') else: self.index_sent = 0 if 'train' in self.header_vals: self.index_train = self.header_vals.index('train') else: self.index_train = 1 if 'classify' in self.header_vals: self.index_class = self.header_vals.index('classify') else: self.index_class = 2 self.index_class_L = get_column_letter(self.index_class+1) if 'test' in self.header_vals: self.index_test = self.header_vals.index('test') self.has_test = True else: self.index_test = 3 self.has_test = False if 'correct_train' in self.header_vals: self.index_correct_train = self.header_vals.index('correct_train') else: self.index_correct_train = 4 self.index_correct_train_L = get_column_letter(self.index_correct_train+1) if 'correct_test' in self.header_vals: self.index_correct_test = self.header_vals.index('correct_test') else: self.index_correct_test = 5 self.index_correct_test_L = get_column_letter(self.index_correct_test+1) # Collect all sentences self.sent_dat = self.collect_column_data(self.iRow_skip, self.index_sent) # Collect paired values of sentences and train self.train_dat = self.collect_column_data(self.iRow_skip, self.index_sent, self.index_train) # If test is available if self.has_test: self.test_dat = self.collect_column_data(self.iRow_skip, self.index_sent, self.index_test)
def processAllBallots(xl, coasterDict, winLossMatrix): # include spreadsheet containing identifying voter info, if requested if args.includeExtraInfo > 0: voterinfows = xl.create_sheet("Voter Info (SENSITIVE)") voterinfows.append([ "Ballot Filename", "Name", "Email", "City", "State/Province", "Country", "Coasters Ridden" ]) voterinfows.column_dimensions['A'].width = 24.83 voterinfows.column_dimensions['B'].width = 16.83 voterinfows.column_dimensions['C'].width = 24.83 for col in ['D', 'E', 'F', 'G']: voterinfows.column_dimensions[col].width = 12.83 # include spreadsheet containing individual ballots, if requested if args.includeExtraInfo > 1: # includes each coaster's rank in a separate column ballotws1 = xl.create_sheet("Ballots with Ranks (SENSITIVE)") headerRow = ["Ballot Filename"] for i in range(0, len(coasterDict)): headerRow.extend(["Rank", "Coaster"]) ballotws1.append(headerRow) ballotws1.column_dimensions['A'].width = 24.83 for i in range(0, len(coasterDict)): col1 = (i * 2) + 2 col2 = col1 + 1 ballotws1.column_dimensions[get_column_letter( col1)].width = 4.83 ballotws1.column_dimensions[get_column_letter( col2)].width = 45.83 # doesn't include rank data; assumes no coasters are ranked the same ballotws2 = xl.create_sheet("Ballots Imprecise (SENSITIVE)") headerRow = ["Ballot Filename"] for i in range(0, len(coasterDict)): headerRow.append("Coaster [Rank {0}]".format(i + 1)) ballotws2.append(headerRow) ballotws2.column_dimensions['A'].width = 24.83 for i in range(0, len(coasterDict)): ballotws2.column_dimensions[get_column_letter(i + 2)].width = 45.83 # loop over ballots, processing each and saving requested info for filepath in getBallotFilepaths(): voterInfo, ballotRanks = processBallot(filepath, coasterDict, winLossMatrix) if args.includeExtraInfo > 0 and voterInfo: voterinfows.append(voterInfo) if args.includeExtraInfo > 1 and ballotRanks: rowVals1 = [voterInfo[0]] rowVals2 = [voterInfo[0]] for coasterAndRank in sorted(ballotRanks.items(), key=lambda x: x[1]): if args.verbose > 2: print("{0}.\t{1}".format(coasterAndRank[1], coasterAndRank[0])) rowVals1.extend([coasterAndRank[1], coasterAndRank[0]]) rowVals2.append(coasterAndRank[0]) ballotws1.append(rowVals1) ballotws2.append(rowVals2) if args.includeExtraInfo > 0: voterinfows.freeze_panes = voterinfows['A2'] if args.includeExtraInfo > 1: ballotws1.freeze_panes = ballotws1['B2'] ballotws2.freeze_panes = ballotws2['B2']
def autosize_ws_columns(worksheet): for col in worksheet.columns: column = get_column_letter(col[0].column) max_length = max(len(str(cell.value)) for cell in col[1:]) adjusted_width = (max_length + 2) * 1.1 worksheet.column_dimensions[column].width = adjusted_width
return e,w,s,n,tb,lb TOPKDICT={} TOPK=[] #way to extract cell indices as integers for ic, col_cells in enumerate(islice(fsheet.columns,fsheet.max_column)): #iterates all rows for each column for ir, row in enumerate(islice(col_cells,fsheet.max_row)): #iterates within each column all rows if type(row.value)==str and row.value[1:4]=='SUM': #print('%s%s: cell.value= %s' % (get_column_letter(ic+1),ir+1,row.value) ) #print("neighborhood", NN_type(ir+1,ic+1,fsheet)) TOPK.append(dsheet.cell(ir+1,ic+1).value) if TOPK: for ic, col_cells in enumerate(islice(dsheet.columns, dsheet.max_column)): for ir, row in enumerate(islice(col_cells, dsheet.max_row)): print("col and row ", ic, ir) if type(row.value) == int or type(row.value)==float: print("neighborhood", NN_type(ir + 1, ic + 1, dsheet)) print('%s%s: cell.value= %s' % (get_column_letter(ic+1),ir+1,row.value) ) TOPKDICT.update({str(ic+1)+ '-'+str(ir+1):dsheet.cell(ir + 1, ic + 1).value}) print(TOPKDICT) print(max(TOPK)) exit()
def load_filters(ws, init_vector): FullRange = init_vector +':' + get_column_letter(ws.max_column) \ + str(ws.max_row) ws.auto_filter.ref = FullRange return ws
############################################# Reading from Excel ############################################# # Access the value of a cell on a sheet using "A1" format. This value being column "A" and row "1". print(sheet["A2"].value) # Access the value of a specific cell on sheet using numbers for both rows and columns. print(sheet.cell(row=2, column=2).value) # Use the above syntax in a loop for i in range(1, 8): print(i, sheet.cell(row=i, column=2).value) # Convert a column location from a letter value to number value or visa versa. Needs to be imported. print(get_column_letter(1)) print(column_index_from_string("C")) # Find the total number of rows or columns on a worksheet. Returns answer as an int print(type(sheet.max_row)) print(sheet.max_column) # Get a pre defined area from the work sheet print(sheet['A1': 'C3']) # Use a loop to print out the area for row in sheet['A1':'C3']: for cell in row: # .coordinate shows the cells location, well surprisingly .value shows the cells value. print(cell.coordinate, cell.value)
def CreaTabella(squadre): #mi importo tutte le istanze delle squadre TabellaFinale=openpyxl.Workbook() Foglio=TabellaFinale.active Foglio.title="Tabella Squadre" numSquadre=len(squadre) c=1 r=3 for squadra in squadre: listaPortieri=squadra.getListaPortieri() listaDifensori=squadra.getListaDifensori() listaCentrocampisti=squadra.getListaCentrocampisti() listaAttaccanti=squadra.getListaAttaccanti() for portiere in listaPortieri: nomePort=portiere.getNome() squadraPort=portiere.getSquadra() valorePort=portiere.getValore() Foglio.cell(row=r,column=c).value=nomePort Foglio.cell(row=r,column=c+1).value=squadraPort Foglio.cell(row=r,column=c+2).value=valorePort r=r+1 r=7 for difensore in listaDifensori: nomeDif=difensore.getNome() squadraDif=difensore.getSquadra() valoreDif=difensore.getValore() Foglio.cell(row=r,column=c).value=nomeDif Foglio.cell(row=r,column=c+1).value=squadraDif Foglio.cell(row=r,column=c+2).value=valoreDif r=r+1 r=16 for centrocampista in listaCentrocampisti: nomeCentr=centrocampista.getNome() squadraCentr=centrocampista.getSquadra() valoreCentr=centrocampista.getValore() Foglio.cell(row=r,column=c).value=nomeCentr Foglio.cell(row=r,column=c+1).value=squadraCentr Foglio.cell(row=r,column=c+2).value=valoreCentr r=r+1 r=25 for attaccante in listaAttaccanti: nomeAtt=attaccante.getNome() squadraAtt=attaccante.getSquadra() valoreAtt=attaccante.getValore() Foglio.cell(row=r,column=c).value=nomeAtt Foglio.cell(row=r,column=c+1).value=squadraAtt Foglio.cell(row=r,column=c+2).value=valoreAtt r=r+1 r=3 c=c+3 a=1 b=1 c=1 d=3 for i in range(numSquadre): Foglio.merge_cells(start_row=a,start_column=b,end_row=c,end_column=d) Foglio.merge_cells(start_row=a+1,start_column=b,end_row=c+1,end_column=d) Foglio.merge_cells(start_row=a+5,start_column=b,end_row=c+5,end_column=d) Foglio.merge_cells(start_row=a+14,start_column=b,end_row=c+14,end_column=d) Foglio.merge_cells(start_row=a+23,start_column=b,end_row=c+23,end_column=d) Foglio.cell(row=2,column=b).value="Portieri" Foglio.cell(row=2,column=b).font=Font(bold=True,size=12,color='FFFF6600') Foglio.cell(row=2,column=b).alignment=Alignment(horizontal='center') Foglio.cell(row=6,column=b).value="Difensori" Foglio.cell(row=6,column=b).font=Font(bold=True,size=12,color='FF0000FF') Foglio.cell(row=6,column=b).alignment=Alignment(horizontal='center') Foglio.cell(row=15,column=b).value="Centrocampisti" Foglio.cell(row=15,column=b).font=Font(bold=True,size=12,color='FF008000') Foglio.cell(row=15,column=b).alignment=Alignment(horizontal='center') Foglio.cell(row=24,column=b).value="Attaccanti" Foglio.cell(row=24,column=b).font=Font(bold=True,size=12,color='FFFF0000') Foglio.cell(row=24,column=b).alignment=Alignment(horizontal='center') letteraColonna=get_column_letter SquadraTemp=squadre[i] nomeSquadra=SquadraTemp.getNomeSquadra() Foglio.cell(row=1,column=b).value=nomeSquadra Foglio.cell(row=1,column=b).font=Font(bold=True,size=18) Foglio.cell(row=1,column=b).alignment=Alignment(horizontal='center') b=b+3 d=d+3 for col in Foglio.columns: column=col[0].column if column in [1,4,7,10,13,16,19,22,25,28,31,34,37]: let=get_column_letter(column) Foglio.column_dimensions[let].width="17" TabellaFinale.save('SquadreFantacalcio.xlsx')
import sys import openpyxl from openpyxl.utils import get_column_letter from openpyxl.styles import Font n = int(sys.argv[1]) wb = openpyxl.Workbook() sheet = wb.active column_letter = get_column_letter(n) for i in range(2, n + 2): column_style = sheet['A' + str(i)] column_style.font = Font(bold=True) sheet['A' + str(i)] = i - 1 row_style = sheet[str(get_column_letter(i)) + '1'] row_style.font = Font(bold=True) sheet[str(get_column_letter(i)) + '1'] = i - 1 for j in range(2, n + 2): sheet[str(get_column_letter(i)) + str(j)] = (i - 1) * (j - 1) wb.save('mt.xlsx')
def format_matches_sheet(self): ws = self.wb["Matches"] header_number_format_dict = make_column_property_dict( { '5': '0.000', '6': '0.00' }, { "A": "0.000", "B": "0.00" }, { "17": "0.000", "18": '0.000000', "19": '0.00%', "21": "0.00", "22": "0.00", "23": '0.00%' }, repeat=self.n_experiments, length=3) header_h_alignment_list = make_column_property_dict( { '5': "right", '6': "right", }, { "A": "right", "B": "right" }, { "17": "right", "18": "right", "19": "right", "21": "right", "22": "right", "23": "right" }, repeat=self.n_experiments, length=3) # header_v_alignment_list = make_column_property_dict( # {'5': "center", '6': "center"}, # {"A": "center", "B": "center", "AF": "center", "AG": "center", "AH": "center", "AJ": "center", # "AK": "center", "AL": "center"}) hits_number_format_dict = make_column_property_dict( { '4': '0', '5': '0', '6': '0' }, {"C": "0"}, { "5": "0.0", "6": "0.0000", "7": "0.00%", "9": "0.0", "10": "0.0000", "11": "0.00%", "13": "0.0", "14": "0.0000", "15": "0.00%" }, repeat=self.n_experiments, length=3) hits_alignment_dict = make_column_property_dict( { '4': 'center', '5': 'center', '6': 'center' }, { "B": "center", "C": "center" }, {"13": "center"}, repeat=self.n_experiments, length=3) width_dict = make_column_property_dict({ "4": 8, "5": 8, "6": 11 }, {"B": 12}, { "5": 6, "6": 9, "7": 9, "9": 6, "10": 9, "11": 9, "13": 5, "14": 7, "15": 9, "17": 8, "18": 9, "19": 9, "21": 14, "22": 12, "23": 9, "4": 1, "8": 1, "12": 1, "16": 1, "20": 1 }, repeat=self.n_experiments, length=3) for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column): for cell in row: cell.alignment = Alignment(vertical="center", wrap_text=False) headers = [x + 1 for x in range(ws.max_row)][3::6] for column_cells in ws.columns: length = max(len(as_text(cell.value)) for cell in column_cells) ws.column_dimensions[get_column_letter( column_cells[0].column)].width = length # ws.column_dimensions[column_cells[0].column].bestFit = True for row in ws.iter_rows(min_row=4, max_row=ws.max_row, min_col=1, max_col=ws.max_column): for cell in row: # Column Headers if cell.row in headers: if header_number_format_dict and ( cell.column in header_number_format_dict): cell.number_format = header_number_format_dict[ cell.column] if header_h_alignment_list and ( cell.column in header_h_alignment_list): cell.alignment = Alignment( horizontal=header_h_alignment_list[cell.column], vertical="bottom", wrap_text=False) # Hit Data else: if hits_number_format_dict and ( cell.column in hits_number_format_dict): cell.number_format = hits_number_format_dict[ cell.column] if hits_alignment_dict and (cell.column in hits_alignment_dict): cell.alignment = Alignment( horizontal=hits_alignment_dict[cell.column], vertical="center", wrap_text=False) if width_dict: for column in width_dict: if width_dict[column] == 0: ws.column_dimensions[column].hidden = True else: ws.column_dimensions[column].width = width_dict[column] format_header(ws, make_column_property_dict({"4": "center"}, repeat=self.n_experiments, length=3)) # First Row format_header(ws, make_column_property_dict( {"2": "center"}, {"A": "right"}, repeat=((self.n_experiments * 3) + 25), length=1), 2, 2) # Second Row format_header(ws, make_column_property_dict( {"2": "center"}, repeat=((self.n_experiments * 3) + 25), length=1), 3, 3) # Third Row for offset in range(self.n_experiments): merge_string = get_column_letter(4 + ( 3 * offset)) + '1:' + get_column_letter(6 + (3 * offset)) + '1' ws.merge_cells(merge_string) offset = self.n_experiments * 3 for index in [5, 9, 13, 17, 21]: merge_string = get_column_letter( index + offset) + '2:' + get_column_letter(index + 2 + offset) + '2' ws.merge_cells(merge_string)
import openpyxl from openpyxl import Workbook from openpyxl.utils import get_column_letter wb0 = Workbook() ws0 = wb0.active df.columns = ['cadenas'] for x in range(len(df.index)): # numero de filas for y in range(len(df.cadenas[x])): # caracter de la cadena/ columna text = (df.cadenas[x])[y] mau = y + 1 column_letter = get_column_letter(mau) #ws.cell(row=x,column=y)=text p = column_letter + str(x + 2) p = "".join(p) ws0[p] = str(text) if x % 10000 == 0 and y == 0: print(str(itanum(x)) + "/" + str(itanum(len(df.index)))) print("") for y in range(len(df.cadenas[1])): text = y mau = y + 1 column_letter = get_column_letter(mau)