def process_dir(data_dir_path, output_file_path): # crate a new workbook and sheets wb_new = Workbook() ws_deliveries = wb_new.get_active_sheet() ws_deliveries.title = 'Deliveries' ws_returns = wb_new.create_sheet(1) ws_returns.title = 'Returns' ws_wastage = wb_new.create_sheet(2) ws_wastage.title = 'Wastage' ws_staff_meals = wb_new.create_sheet(3) ws_staff_meals.title = 'Staff Meals' ws_transfers_in = wb_new.create_sheet(4) ws_transfers_in.title = 'Transfers In' ws_transfers_out = wb_new.create_sheet(5) ws_transfers_out.title = 'Transfers Out' # get the list of files in the directory onlyfiles = [ f for f in listdir(data_dir_path) if isfile(join(data_dir_path,f))] # process each file for f in onlyfiles: process_file(data_dir_path + f, wb_new) # save the new workbook wb_new.save(output_file_path)
def test_bad_encoding(): test_string = u'Compound Value (£)'.encode('latin1') utf_book = Workbook() utf_sheet = utf_book.get_active_sheet() utf_sheet.cell('A1').value = test_string
def xls2xlsx(filename): # Old .xls workbook book_xls = xlrd.open_workbook(filename) # New .xlsx workbook book_xlsx = Workbook() sheet_names = book_xls.sheet_names() # Iterate through .xls sheet names to create matching ones in new workbook for sheet_index in range(0, len(sheet_names)): sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index]) # If its the first sheet then get the first .xlsx sheet if sheet_index == 0: sheet_xlsx = book_xlsx.get_active_sheet() sheet_xlsx.title = sheet_names[sheet_index] else: sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index]) # Nested for loop to go through each cell and copy over to the new .xlsx sheet for row in range(0, sheet_xls.nrows): for col in range(0, sheet_xls.ncols): sheet_xlsx.cell(row=row + 1, column=col + 1).value = sheet_xls.cell_value( row, col) # After going through each sheet and copying them over to .xlsx # save the new .xlsx workbook by appending 'x' to file extension book_xlsx.save(filename + "x")
def test_bad_encoding(): test_string = u'Compound Value (£)'.encode('latin1') utf_book = Workbook() utf_sheet = utf_book.get_active_sheet() utf_sheet.cell('A1').value = test_string
def test_good_encoding(): test_string = u'Compound Value (£)'.encode('latin1') lat_book = Workbook(encoding='latin1') lat_sheet = lat_book.get_active_sheet() lat_sheet.cell('A1').value = test_string
def make_worksheet(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'data' ws.append(list(range(10))) return ws
def setUp(self): wb = Workbook() ws = wb.get_active_sheet() for i in range(10): ws.cell(row=i, column=0).value = i self.cell = Reference(ws, (0, 0)) self.range = Reference(ws, (0, 0), (9, 0))
def setup(self): wb = Workbook() ws = wb.get_active_sheet() for i in range(10): ws.cell(row=i, column=0).value = 1 values = Reference(ws, (0, 0), (0, 9)) self.range = Serie(values=values)
def test_good_encoding(): test_string = u'Compound Value (£)'.encode('latin1') lat_book = Workbook(encoding='latin1') lat_sheet = lat_book.get_active_sheet() lat_sheet.cell('A1').value = test_string
def process_dir(data_dir_path, output_file_path): # crate a new workbook and sheets wb_new = Workbook() ws_deliveries = wb_new.get_active_sheet() ws_deliveries.title = 'Deliveries' ws_returns = wb_new.create_sheet(1) ws_returns.title = 'Returns' ws_wastage = wb_new.create_sheet(2) ws_wastage.title = 'Wastage' ws_staff_meals = wb_new.create_sheet(3) ws_staff_meals.title = 'Staff Meals' ws_transfers_in = wb_new.create_sheet(4) ws_transfers_in.title = 'Transfers In' ws_transfers_out = wb_new.create_sheet(5) ws_transfers_out.title = 'Transfers Out' # get the list of files in the directory onlyfiles = [ f for f in listdir(data_dir_path) if isfile(join(data_dir_path, f)) ] # process each file for f in onlyfiles: process_file(data_dir_path + f, wb_new) # save the new workbook wb_new.save(output_file_path)
def open_xls_as_xlsx(filename): try: # noinspection PyUnresolvedReferences import xlrd # noinspection PyUnresolvedReferences from openpyxl.workbook import Workbook # noinspection PyUnresolvedReferences from openpyxl.reader.excel import load_workbook book = xlrd.open_workbook(filename) index = 0 nrows, ncols = 0, 0 sheet = None while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 book1 = Workbook() sheet1 = book1.get_active_sheet() for row in range(0, nrows): for col in range(0, ncols): sheet1.cell(row=row + 1, column=col + 1).value = sheet.cell_value(row, col) filename = filename.replace('.xls', '.xlsx') book1.save(filename) return Path(filename) except ImportError: print('xlrd and openpyxl are required in order to convert an xls file to xlsx') raise
def test_image_anchor(self): DummyImage = self._get_dummy_class() wb = Workbook() ws = wb.get_active_sheet() cell = ws.cell('D32') img = DummyImage(None) img.anchor(cell) eq_((img.drawing.top, img.drawing.left), (620, 210))
def make_calenda(task_list, date_from, days, save_as): date_to = date_from + datetime.timedelta(days=days) print date_to from_column = 2 row = 0 # Load the workbook... wb = Workbook() ws = wb.get_active_sheet() ws.cell(row = 0, column = 0).value = u'任务名称' ws.cell(row = 0, column = 1).value = u'任务说明' month_set = set() weekday_column_list = [] for i in range(days): d = date_from + datetime.timedelta(days=i) month = d.month if month not in month_set: cell = ws.cell(row = row, column = i+from_column) cell.value = u'%s月' % d.month month_set.add(month) cell = ws.cell(row = row+1, column = i+from_column) cell.value = '%s' % d.day if d.weekday() in (5,6): weekday_column_list.append(i+from_column) for ws_column in range(from_column+1,from_column+days+1): col_letter = get_column_letter(ws_column) ###~ ws.column_dimensions[col_letter].auto_size = True ws.column_dimensions[col_letter].width = 2.7 for row in range(1,len(task_list)+2): for column in weekday_column_list: cell = ws.cell(row=row,column=column) cell.style.fill.fill_type = Fill.FILL_SOLID cell.style.fill.start_color.index = 'CBCACA' for i,task in enumerate(task_list): cell = ws.cell(row=i+2,column=0) cell.value = task #设置单元格border for row in range(0,len(task_list)+2): for col in range(0, days+2): cell = ws.cell(row=row,column=col) cell.style.borders.top.border_style = Border.BORDER_THIN cell.style.borders.right.border_style = Border.BORDER_THIN cell.style.borders.bottom.border_style = Border.BORDER_THIN cell.style.borders.left.border_style = Border.BORDER_THIN #设置第一列宽 ws.column_dimensions['A'].width = 30 wb.save(save_as)
def test_image_end(self): DummyImage = self._get_dummy_class() wb = Workbook() ws = wb.get_active_sheet() cell = ws.cell('A1') img = DummyImage(None) img.drawing.width, img.drawing.height = (50, 50) end = img.anchor(cell) eq_(end[1], ('A', 3))
def setup(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'reference' for i in range(10): ws.cell(row=i, column=0).value = i self.sheet = ws self.cell = Reference(self.sheet, (0, 0)) self.range = Reference(self.sheet, (0, 0), (9, 0))
def new_landscape_a4_sheet(workbook, title1, title2, header=None, add_print_title=True): if workbook is None: workbook = Workbook() worksheet = workbook.get_active_sheet() else: worksheet = workbook.create_sheet() worksheet = worksheet_setup_landscape_a4(worksheet, title1, title2, add_print_title=add_print_title) if header is not None: worksheet_set_header(worksheet, header) return workbook, worksheet
def setup(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'reference' for i in range(10): ws.cell(row=i, column=0).value = i self.sheet = ws self.cell = Reference(self.sheet, (0, 0)) self.range = Reference(self.sheet, (0, 0), (9, 0))
def setup(self): """Setup a worksheet with one column of data and a line chart""" wb = Workbook() ws = wb.get_active_sheet() ws.title = "Line" for i in range(1, 5): ws.append([i]) self.piechart = LineChart() self.piechart.add_serie(Serie(Reference(ws, (0, 0), (4, 0)))) self.cw = LineChartWriter(self.piechart) self.root = Element("test")
def setup(self): """Setup a worksheet with one column of data and a bar chart""" wb = Workbook() ws = wb.get_active_sheet() ws.title = 'Numbers' for i in range(10): ws.append([i]) self.piechart = BarChart() self.piechart.add_serie(Serie(Reference(ws, (0, 0), (9, 0)))) self.cw = BarChartWriter(self.piechart) self.root = Element('test')
def setup(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = "Scatter" for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ScatterChartWriter(self.scatterchart) self.root = Element("test")
def test_good_encoding(): try: # Python 2 pound = unichr(163) except NameError: # Python 3 pound = chr(163) test_string = ('Compound Value (' + pound + ')').encode('latin1') lat_book = Workbook(encoding='latin1') lat_sheet = lat_book.get_active_sheet() lat_sheet.cell('A1').value = test_string
def test_write_regular_float(): float_value = 1.0 / 3.0 book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = float_value dest_filename = osp.join(TMPDIR, 'float_read_write_issue.xlsx') book.save(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() eq_(test_sheet.cell("A1").value, float_value)
def test_write_regular_float(tmpdir): float_value = 1.0 / 3.0 book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = float_value dest_filename = 'float_read_write_issue.xlsx' book.save(dest_filename) validate_archive(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() assert test_sheet.cell("A1").value == float_value
def test_good_encoding(): try: # Python 2 pound = unichr(163) except NameError: # Python 3 pound = chr(163) test_string = ("Compound Value (" + pound + ")").encode("latin1") lat_book = Workbook(encoding="latin1") lat_sheet = lat_book.get_active_sheet() lat_sheet.cell("A1").value = test_string
def setUp(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'data' for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i self.scatterchart = ScatterChart() self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) self.cw = ChartWriter(self.scatterchart) self.root = Element('test')
def setUp(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = u'data' for i in range(10): ws.cell(row = i, column = 0).value = i self.chart = BarChart() self.chart.title = 'TITLE' self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)))) self.chart._series[-1].color = Color.GREEN self.cw = ChartWriter(self.chart) self.root = Element('test')
def test_good_encoding(): try: # Python 2 pound = unichr(163) except NameError: # Python 3 pound = chr(163) test_string = ('Compound Value (' + pound + ')').encode('latin1') lat_book = Workbook(encoding='latin1') lat_sheet = lat_book.get_active_sheet() lat_sheet.cell('A1').value = test_string
def test_bad_encoding(): try: # Python 2 pound = unichr(163) except NameError: # Python 3 pound = chr(163) test_string = ('Compound Value (' + pound + ')').encode('latin1') utf_book = Workbook() utf_sheet = utf_book.get_active_sheet() with pytest.raises(UnicodeDecodeError): utf_sheet.cell('A1').value = test_string
def test_write_regular_float(): float_value = 1.0 / 3.0 book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = float_value dest_filename = osp.join(TMPDIR, 'float_read_write_issue.xlsx') book.save(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() eq_(test_sheet.cell("A1").value, float_value)
def setUp(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = u'data' for i in range(10): ws.cell(row=i, column=0).value = i self.chart = BarChart() self.chart.title = 'TITLE' self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)))) self.chart._series[-1].color = Color.GREEN self.cw = ChartWriter(self.chart) self.root = Element('test')
def test_write_regular_date(tmpdir): tmpdir.chdir() today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600) book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = today dest_filename = 'date_read_write_issue.xlsx' book.save(dest_filename) validate_archive(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() assert test_sheet.cell("A1").value == today
def setup(self): """Setup a worksheet with one column of data and a pie chart""" wb = Workbook() ws = wb.get_active_sheet() ws.title = 'Pie' for i in range(1, 5): ws.append([i]) self.piechart = PieChart() values = Reference(ws, (0, 0), (9, 0)) series = Serie(values, labels=values) self.piechart.add_serie(series) ws.add_chart(self.piechart) self.cw = PieChartWriter(self.piechart) self.root = Element('test')
def test_write_regular_date(): today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600) book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = today dest_filename = osp.join(TMPDIR, 'date_read_write_issue.xlsx') book.save(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() eq_(test_sheet.cell("A1").value, today)
def test_write_regular_date(): today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600) book = Workbook() sheet = book.get_active_sheet() sheet.cell("A1").value = today dest_filename = osp.join(TMPDIR, 'date_read_write_issue.xlsx') book.save(dest_filename) test_book = load_workbook(dest_filename) test_sheet = test_book.get_active_sheet() eq_(test_sheet.cell("A1").value, today)
def create_xlsx_workbook(queryset, fields, creator='Unknown', description=''): wb = Workbook() wb.properties.description = description wb.properties.creator = creator ws = wb.get_active_sheet() ws.title = "Search Results" if queryset.count: populate_headers(ws, queryset, fields) populate_data(ws, queryset, fields) setup_header_cells(ws) set_column_widths(ws, fields) return wb
def test_no_write_legend(self): wb = Workbook() ws = wb.get_active_sheet() ws.title = 'data' for i in range(10): ws.cell(row=i, column=0).value = i ws.cell(row=i, column=1).value = i scatterchart = ScatterChart() scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1)))) cw = ChartWriter(scatterchart) root = Element('test') scatterchart.show_legend = False cw._write_legend(root) eq_(get_xml(root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test />')
def new_landscape_a4_sheet(workbook, title1, title2, header=None, add_print_title=True): if workbook is None: workbook = Workbook() worksheet = workbook.get_active_sheet() else: worksheet = workbook.create_sheet() worksheet = worksheet_setup_landscape_a4(worksheet, title1, title2, add_print_title=add_print_title) if header is not None: worksheet_set_header(worksheet, header) return workbook, worksheet
def open_xls_as_xlsx(filename): # first open using xlrd book = xlrd.open_workbook(filename) sheet = book.sheet_by_index(0) nrows = sheet.nrows ncols = sheet.ncols # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() if nrows == 0 or ncols == 0: return book1 for row in range(0, nrows): for col in range(0, ncols): sheet1.cell(row=row + 1, column=col + 1).value = sheet.cell_value(row, col) #print(sheet.cell_value(row, col)) return book1
def open_xls_as_xlsx(filename, index): # reading xls into openpyxl workbook # first open using xlrd book = xlrd.open_workbook(filename) nrows, ncols = 0, 0 while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() for row in range(0, nrows): for col in range(0, ncols): sheet1.cell(row=row + 1, column=col + 1).value = sheet.cell_value(row, col) return book1
def open_xls_as_xlsx(filename): # first open using xlrd book = xlrd.open_workbook(filename) index = 0 nrows, ncols = 0, 0 while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() for row in range(1, nrows + 1): for col in range(1, ncols + 1): sheet1.cell(row=row, column=col).value = sheet.cell_value(row - 1, col - 1) return book1
def xlstoxlsx(filename): import xlrd from openpyxl.workbook import Workbook from openpyxl.reader.excel import load_workbook, InvalidFileException # first open using xlrd book = xlrd.open_workbook(filename) index = 0 nrows, ncols = 0, 0 while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() for row in xrange(0, nrows): for col in xrange(0, ncols): sheet1.cell(row=row, column=col).value = sheet.cell_value(row, col) return book1
def open_xls_as_xlsx(filename): """ Returns a openpyxl.workbook object for a .xls file http://stackoverflow.com/questions/9918646/how-to-convert-xls-to-xlsx """ # first open using xlrd book = xlrd.open_workbook(filename) index = 0 nrows, ncols = 0, 0 while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() for row in xrange(0, nrows): for col in xrange(0, ncols): sheet1.cell(row=row, column=col).value = sheet.cell_value(row, col) return book1
def upgrade(self): print(self.filename) # first open using xlrd book = xlrd.open_workbook(self.filename, formatting_info=True) index = 0 nrows, ncols = 0, 0 while nrows * ncols == 0: sheet = book.sheet_by_index(index) nrows = sheet.nrows ncols = sheet.ncols index += 1 # prepare a xlsx sheet book1 = Workbook() sheet1 = book1.get_active_sheet() sheet1.title = 'hoja1' for row in range(1, nrows+1): for col in range(1, ncols+1): sheet1.cell(row=row, column=col).value = self._get_xlrd_cell_value(sheet.cell(row-1, col-1)) book1.save(self.filename + "x")
def test_roundtrip(self): wb = Workbook() ws = wb.get_active_sheet() for address in ('A1', 'D52', 'X11'): eq_(ws.point_pos(*ws.cell(address).anchor), coordinate_from_string(address))
def test_point(self): wb = Workbook() ws = wb.get_active_sheet() eq_(ws.point_pos(top=40, left=150), ('C', 3))
class Html2Excel(object): def __init__(self): self.list = [] self.start_row = 0 self.start_col = 0 self.workbook = Workbook(encoding='utf8') self.worksheet = self.workbook.active def use_existing_wb(self, name_workbook): self.workbook = load_workbook(filename=name_workbook) self.worksheet = self.workbook.get_active_sheet() def create_new_sheet(self, name_sheet): self.worksheet = self.workbook.create_sheet() self.worksheet.title = name_sheet def set_col_width(self, cols_width): for col_i in cols_width.keys(): self.worksheet.column_dimensions[get_column_letter( col_i)].width = int(cols_width.get(col_i)) def add_logo(self, logo_filename): img = drawing.Image(logo_filename) img.anchor(self.worksheet.cell('D3')) self.worksheet.add_image(img) def append_html_table(self, html_string, start_row=1, start_col=1): html_string = document_fromstring(html_string, HTMLParser(encoding='utf8')) last_row = start_row - 1 last_col = start_col for table_el in html_string.xpath('//table'): last_row += 1 for row_i, row in enumerate(table_el.xpath('./tr'), start=last_row): for col_i, col in enumerate(row.xpath('./td|./th'), start=last_col): colspan = int(col.get('colspan', 0)) rowspan = int(col.get('rowspan', 0)) font_bold = False font_size = 11 font_color = BLACK_COLOR if rowspan: rowspan -= 1 if colspan: colspan -= 1 col_data = col.text_content().encode("utf8") valign = 'center' if col_i == start_col and col.tag != 'th' else 'top' while (row_i, col_i) in self.list: col_i += 1 cell = self.worksheet.cell(row=row_i, column=col_i) if rowspan or colspan: self.worksheet.merge_cells(start_row=row_i, end_row=row_i + rowspan, start_column=col_i, end_column=col_i + colspan) cell.value = col_data cell.alignment = Alignment( horizontal=row.get('align', col.get('align')) or 'left', vertical=row.get('valign', col.get('valign')) or valign, shrink_to_fit=True, wrap_text=True) bgcolor = row.get('bgcolor', col.get('bgcolor')) if bgcolor: cell.fill = PatternFill(fill_type='solid', start_color=bgcolor, end_color=bgcolor) for el in col.iter(): if el.tag == 'font': font_color = el.get('color') elif el.tag == 'b': font_bold = True elif el.tag in _TEXT_SIZE: font_bold = True, font_size = _TEXT_SIZE.get(el) cell.font = Font( color=font_color, bold=font_bold, size=font_size, ) if col.tag == 'th': cell.font = Font(bold=True) cell.fill = PatternFill(fill_type='solid', start_color=TH_COLOR, end_color=TH_COLOR) for i in range(0, rowspan + 1, 1): for j in range(0, colspan + 1, 1): if i == rowspan: last_row = row_i + i self.list.append((row_i + i, col_i + j)) cell = self.worksheet.cell(row=row_i + i, column=col_i + j) cell.border = Border( left=Side(border_style=_BORDER_STYLE.get( table_el.get('border') or None), color=BORDER_COLOR), right=Side(border_style=_BORDER_STYLE.get( table_el.get('border') or None), color=BORDER_COLOR), top=Side(border_style=_BORDER_STYLE.get( table_el.get('border') or None), color=BORDER_COLOR), bottom=Side(border_style=_BORDER_STYLE.get( table_el.get('border') or None), color=BORDER_COLOR), ) return last_row, last_col def save_wb(self, name): self.workbook.save(name)
def test_roundtrip(self, value): wb = Workbook() ws = wb.get_active_sheet() assert ws.point_pos(*ws.cell(value).anchor) == coordinate_from_string(value)
def test_roundtrip(self, value): wb = Workbook() ws = wb.get_active_sheet() assert ws.point_pos( *ws.cell(value).anchor) == coordinate_from_string(value)
def test_point(self): wb = Workbook() ws = wb.get_active_sheet() assert ws.point_pos(top=40, left=150), ('C' == 3)
class XlReport: """ Wrapper for openpyxl Using xlsx because xls has limitations and ods has no good python library """ def __init__(self, file_name="Report"): """ file_name does not need an extention """ if file_name.endswith('.xls'): file_name = file_name[:-4] elif file_name.endswith('xlsx'): file_name = file_name[:-5] file_name = file_name.replace( ' ', '_') # Some browsers don't deal well with spaces in downloads self.workbook = Workbook() self.workbook.remove_sheet(self.workbook.get_active_sheet()) self.file_name = file_name def add_sheet(self, data, title=None, header_row=None, heading=None, auto_width=False, max_auto_width=50): """ Add a sheet with data to workbook title: sheet name header_row: List - Column header (bold with bottom border) heading: Sheet heading (very top of sheet) auto_width: will ESTIMATE the width of each column by counting max chars in each column. It will not work with a formula. max_auto_width: is the max number of characters a column to be """ sheet = self.workbook.create_sheet() if title: sheet.title = unicode(title) if heading: sheet.append([unicode(heading)]) if header_row: header_row = map(unicode, header_row) sheet.append(header_row) row = sheet.get_highest_row() for i, header_cell in enumerate(header_row): cell = sheet.cell(row=row - 1, column=i) cell.style.font.bold = True cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN for row in data: row = map(unicode, row) sheet.append(row) if auto_width: column_widths = [] for row in data: row = map(unicode, row) for i, cell in enumerate(row): if len(column_widths) > i: if len(cell) > column_widths[i]: column_widths[i] = len(cell) else: column_widths += [len(cell)] for i, column_width in enumerate(column_widths): if column_width > 3: if column_width < max_auto_width: # * 0.9 estimates a typical variable width font sheet.column_dimensions[get_column_letter( i + 1)].width = column_width * 0.9 else: sheet.column_dimensions[get_column_letter( i + 1)].width = max_auto_width def save(self, filename): self.workbook.save(settings.MEDIA_ROOT + filename) def as_download(self): """ Returns a django HttpResponse with the xlsx file """ myfile = StringIO.StringIO() myfile.write(save_virtual_workbook(self.workbook)) response = HttpResponse( myfile.getvalue(), content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response[ 'Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name response['Content-Length'] = myfile.tell() return response
def test_get_active_sheet(): wb = Workbook() active_sheet = wb.get_active_sheet() assert active_sheet == wb.worksheets[0]
def test_get_active_sheet(): wb = Workbook() active_sheet = wb.get_active_sheet() eq_(active_sheet, wb.worksheets[0])
class ExcelGenerator(object): def __init__(self, publication_extracts: List[PubExtract], language_pack, excel_config): self.publication_extracts = publication_extracts self.language_pack = language_pack self.__CURRENT_ROW = 3 self.__LEFT_COLUMNS = ['B', 'C', 'D', 'E'] self.__RIGHT_COLUMNS = ['G', 'H', 'I', 'J'] self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS self.excel_config = excel_config # a new sheet is created for each publication so the program won't # use the first sheet (it will be empty). Hence, it it removed self.workbook = Workbook() self.workbook.remove_sheet(worksheet=self.workbook.get_active_sheet()) def create_excel_doc(self): if len(self.publication_extracts) == 0: return print('creating excel document...') for publication_extract in self.publication_extracts: print(f"preparing mwb({self.language_pack.lang_key}) {publication_extract.pub_month}/{publication_extract.pub_year}", end=' ') self._add_populated_sheet(publication_extract) print('[\033[92mOK\033[0m]') file_name = 'WREX_{}_{}.xlsx'.format(datetime.now().strftime("%m-%d-%Y_%H:%M"), self.language_pack.lang_key) self.workbook.save(file_name) print('done...', f"saved to '{file_name}'") def _add_populated_sheet(self, publication_extract: PubExtract): sheet = self.workbook.create_sheet(publication_extract.pub_name) meetings_count = 0 self._insert_sheet_title( sheet, self.language_pack.get_month_name(publication_extract.pub_month) + ' ' + publication_extract.pub_year) for meeting in publication_extract.meetings: self._insert_header_content(meeting.week_span, sheet) self._insert_section(meeting.treasures_section, sheet) self._insert_section(meeting.ministry_section, sheet) self._insert_section(meeting.christian_section, sheet) self._insert_footer_content(sheet) meetings_count += 1 if meetings_count == 3: self.__CURRENT_ROW = 5 self.__ACTIVE_COLUMNS = self.__RIGHT_COLUMNS self._finalize_styling(sheet) # reset indexes and make them ready for the next sheet self.__CURRENT_ROW = 3 self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str): current_row = str(self.__CURRENT_ROW) start_cell = self.__LEFT_COLUMNS[0] + str(current_row) end_cell = self.__RIGHT_COLUMNS[3] + str(current_row) sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 2 self._style_sheet_title(sheet) def _style_sheet_title(self, sheet: Worksheet): cell = self.__ACTIVE_COLUMNS[0] + str(3) sheet[cell].font = Font(bold=True, size=self.excel_config.SHEET_TITLE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='center', vertical='center') def _insert_header_content(self, week_span: str, sheet: Worksheet): # week span week_span_row = self.__CURRENT_ROW current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = re.sub('[-|–]', ' – ', week_span) sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 1 # chairman current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = self.language_pack.chairman sheet.merge_cells(start_cell + ':' + end_cell) self.__CURRENT_ROW += 1 # opening prayer current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = self.language_pack.opening_prayer self.__CURRENT_ROW += 1 self._style_header_content(week_span_row, sheet) def _style_header_content(self, week_span_row: int, sheet: Worksheet): alignment = Alignment(horizontal='left', vertical='center') border = Border(bottom=Side(border_style='thin')) # week span cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = alignment # chairman cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row + 1) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 1) # last cell sheet[cell].border = border # opening prayer cell = self.__ACTIVE_COLUMNS[2] + str(week_span_row + 2) sheet[cell].font = Font(size=self.excel_config.SMALL_FONT_SIZE) sheet[cell].alignment = alignment cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 2) # last cell sheet[cell].border = border def _insert_section_title(self, meeting_section: MeetingSection, sheet: Worksheet): current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[0] + current_row sheet[start_cell] = meeting_section.title # by default the right most cell at which merging cells ends is on the 5th column (including the offset) end_cell = self.__ACTIVE_COLUMNS[3] + current_row # but if the section is 'IMPROVE_IN_MINISTRY' and the user requests hall-dividing # rows then it changes to the 3rd row if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and \ self.excel_config.INSERT_HALL_DIVISION_LABELS: end_cell = self.__ACTIVE_COLUMNS[1] + current_row self._insert_hall_divider(sheet, meeting_section.section_kind) sheet.merge_cells(start_cell + ':' + end_cell) self._style_section_title(self.__CURRENT_ROW, sheet, meeting_section.section_kind) self.__CURRENT_ROW += 1 def _style_section_title(self, title_row: int, sheet: Worksheet, section_kind: SectionKind): if section_kind == SectionKind.TREASURES: bg_color = self.excel_config.TREASURES_SECTION_TITLE_BG_COLOR elif section_kind == SectionKind.IMPROVE_IN_MINISTRY: bg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR else: bg_color = self.excel_config.CHRISTIAN_LIFE_SECTION_TITLE_BG_COLOR cell = self.__ACTIVE_COLUMNS[0] + str(title_row) sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='left', vertical='center') sheet[cell].fill = PatternFill(patternType='solid', fgColor=bg_color) sheet[cell].border = Border(top=Side(border_style='thin')) def _insert_hall_divider(self, sheet, section_kind: SectionKind): current_row = str(self.__CURRENT_ROW) main_hall_cell = self.__ACTIVE_COLUMNS[2] + current_row second_hall_cell = self.__ACTIVE_COLUMNS[3] + current_row sheet[main_hall_cell] = self.language_pack.main_hall sheet[second_hall_cell] = self.language_pack.second_hall self._style_hall_divider(self.__CURRENT_ROW, sheet, section_kind) def _style_hall_divider(self, divider_row: int, sheet: Worksheet, section_kind: SectionKind): main_hall_cell = self.__ACTIVE_COLUMNS[2] + str(divider_row) second_hall_cell = self.__ACTIVE_COLUMNS[3] + str(divider_row) font = Font(bold=False, size=self.excel_config.SMALL_FONT_SIZE) alignment = Alignment(horizontal='center', vertical='center') if section_kind == SectionKind.IMPROVE_IN_MINISTRY: fg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR else: fg_color = self.excel_config.DEFAULT_BG_COLOR fill = PatternFill(patternType='solid', fgColor=fg_color) border = Border(top=Side(border_style='thin')) sheet[main_hall_cell].font = font sheet[main_hall_cell].alignment = alignment sheet[main_hall_cell].fill = fill sheet[main_hall_cell].border = border sheet[second_hall_cell].font = font sheet[second_hall_cell].alignment = alignment sheet[second_hall_cell].fill = fill sheet[second_hall_cell].border = border def _insert_section(self, meeting_section: MeetingSection, sheet: Worksheet): self._insert_section_title(meeting_section, sheet) bible_reading = self.language_pack.bible_reading for presentation in meeting_section.presentations: if meeting_section.section_kind == SectionKind.TREASURES: if (bible_reading in presentation) and self.excel_config.INSERT_HALL_DIVISION_LABELS: self._insert_hall_divider(sheet, meeting_section.section_kind) self.__CURRENT_ROW += 1 current_row = str(self.__CURRENT_ROW) start_cell = self.__ACTIVE_COLUMNS[1] + current_row if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and\ self.excel_config.INSERT_HALL_DIVISION_LABELS: end_cell = self.__ACTIVE_COLUMNS[1] + current_row else: end_cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[start_cell] = presentation if (bible_reading not in presentation) or \ (bible_reading in presentation and not self.excel_config.INSERT_HALL_DIVISION_LABELS): sheet.merge_cells(start_cell + ':' + end_cell) self._style_section(self.__CURRENT_ROW, sheet) self.__CURRENT_ROW += 1 def _style_section(self, presentation_row: int, sheet: Worksheet): cell = self.__ACTIVE_COLUMNS[1] + str(presentation_row) border = Border(bottom=Side(border_style='thin')) alignment = Alignment(horizontal='center', vertical='center') sheet[cell].font = Font(size=self.excel_config.LARGE_FONT_SIZE) sheet[cell].alignment = Alignment(horizontal='left', vertical='center') sheet[cell].border = border # underline and center the last two cells of a presentation row (that is where presenter names go) cell = self.__ACTIVE_COLUMNS[2] + str(presentation_row) sheet[cell].border = border sheet[cell].alignment = alignment cell = self.__ACTIVE_COLUMNS[3] + str(presentation_row) sheet[cell].border = border sheet[cell].alignment = alignment def _insert_footer_content(self, sheet: Worksheet): footer_row = self.__CURRENT_ROW current_row = str(self.__CURRENT_ROW) cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[cell] = self.language_pack.reader self.__CURRENT_ROW += 1 current_row = str(self.__CURRENT_ROW) cell = self.__ACTIVE_COLUMNS[2] + current_row sheet[cell] = self.language_pack.concluding_prayer self.__CURRENT_ROW += 3 self._style_footer_content(footer_row, sheet) def _style_footer_content(self, footer_row: int, sheet: Worksheet): font = Font(size=self.excel_config.SMALL_FONT_SIZE) alignment = Alignment(horizontal='left', vertical='center') border = Border(bottom=Side(border_style='thin')) cell = self.__ACTIVE_COLUMNS[2] + str(footer_row) sheet[cell].font = font sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(footer_row) sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[2] + str(footer_row + 1) sheet[cell].font = font sheet[cell].alignment = alignment sheet[cell].border = border cell = self.__ACTIVE_COLUMNS[3] + str(footer_row + 1) sheet[cell].border = border def _finalize_styling(self, sheet: Worksheet): sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4 sheet.sheet_properties.pageSetUpPr.fitToPage = True sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH) sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42 sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4 sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42 for rows in sheet.iter_rows(min_row=3): for row in rows: sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT