def test_get_sheet_names(): wb = Workbook() names = ["Sheet", "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"] for count in range(5): wb.create_sheet(0) actual_names = wb.get_sheet_names() eq_(sorted(actual_names), sorted(names))
def test_get_sheet_names(): wb = Workbook() names = ['Sheet', 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5'] for count in range(5): wb.create_sheet(0) actual_names = wb.get_sheet_names() assert sorted(actual_names) == sorted(names)
def test_get_sheet_names(): wb = Workbook() names = ['Sheet', 'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Sheet5'] for count in range(5): wb.create_sheet(0) actual_names = wb.get_sheet_names() eq_(sorted(actual_names), sorted(names))
class op(): def __init__(self): self.file_name = "" self.openst = 0 self.closest = 0 self.wb = "" self.ws = "" self.ew = "" self.strow = 1 def openex(self): if os.path.exists(self.file_name): self.wb = load_workbook(self.file_name) else: self.wb = Workbook() def creatsht(self, shtname, shtindex): shtnum = len(self.wb.get_sheet_names()) if shtindex <= shtnum: self.ws = self.wb.worksheets[shtnum - 1] self.ws.title = shtname else: # use ExcelWriter method to write file and save it self.ws = self.wb.create_sheet(shtname, shtindex) def closeex(self): self.wb.save(filename=self.file_name) def labelrow(self, df, rnum, cnum): rows = len(df) if isinstance(df, pd.DataFrame): if rows > 0: for rx in range(rows): self.ws.cell(row=rnum, column=rx + cnum).value = df.iloc[:, rx] elif isinstance(df, list): if rows > 0: for rx in range(rows): self.ws.cell(row=rnum, column=rx + cnum).value = df[rx] def labelcol(self, df, rnum, cnum): rows = len(df) if isinstance(df, pd.DataFrame): if rows > 0: for rx in range(rows): self.ws.cell(row=rx + rnum, column=cnum).value = df.iloc[rx, :] elif isinstance(df, list): if rows > 0: for rx in range(rows): self.ws.cell(row=rx + rnum, column=cnum).value = df[rx] def write_df(self, df): rows, cols = df.shape for ii in range(rows): self.ws.append(list(df.iloc[ii, :]))
def report(self, exceptions=[]): if len(self.exceptions) > 0: report = Workbook() for (contentbook, exceptions) in self.exceptions.iteritems(): fname = os.path.basename(contentbook) template = os.path.splitext(fname)[0] if "Sheet" in report.get_sheet_names(): ws = report.get_sheet_by_name("Sheet") else: ws = report.create_sheet() t = re.compile("([A-Z][A-Z\s\-]+) Template") title = t.search(template).groups()[0] ws.title = title sheet = report.get_sheet_by_name(title) for (idx, colname) in enumerate( ["Sheet", "Field", "Column", "Issue"], 1): col_letter = get_column_letter(idx) _cell = sheet.cell("%s1" % col_letter) _cell.style.font.name = 'Arial' _cell.style.font.size = 12 _cell.style.font.bold = True _cell.style.alignment.vertical = openpyxl.style.Alignment.VERTICAL_TOP _cell.style.alignment.wrap_text = True _cell.value = colname for (row_idx, exc_item) in enumerate(exceptions, 2): for (col_idx, field) in enumerate(exc_item, 1): _cell = sheet.cell( "%s%s" % (get_column_letter(col_idx), row_idx)) _cell.value = field for column in range(1, 5): if column in [1, 4]: width = "30" elif column in [2]: width = 15 elif column in [3]: width = 75 sheet.column_dimensions[get_column_letter( column)].width = width report.save("Content_Template_Check_%s.xlsx" % time.strftime("%Y-%m-%d")) else: print "Nothing to report"
def report(self, exceptions=[]): if len(self.exceptions) > 0: report = Workbook() for (contentbook, exceptions) in self.exceptions.iteritems(): fname = os.path.basename(contentbook) template = os.path.splitext(fname)[0] if "Sheet" in report.get_sheet_names(): ws = report.get_sheet_by_name("Sheet") else: ws = report.create_sheet() t = re.compile("([A-Z][A-Z\s\-]+) Template") title = t.search(template).groups()[0] ws.title = title sheet = report.get_sheet_by_name(title) for (idx, colname) in enumerate(["Sheet", "Field", "Column", "Issue"], 1): col_letter = get_column_letter(idx) _cell = sheet.cell("%s1" % col_letter) _cell.style.font.name = 'Arial' _cell.style.font.size = 12 _cell.style.font.bold = True _cell.style.alignment.vertical = openpyxl.style.Alignment.VERTICAL_TOP _cell.style.alignment.wrap_text = True _cell.value = colname for (row_idx, exc_item) in enumerate(exceptions, 2): for (col_idx, field) in enumerate(exc_item, 1): _cell = sheet.cell("%s%s" % (get_column_letter(col_idx), row_idx)) _cell.value = field for column in range(1, 5): if column in [1, 4]: width = "30" elif column in [2]: width = 15 elif column in [3]: width = 75 sheet.column_dimensions[get_column_letter(column)].width = width report.save("Content_Template_Check_%s.xlsx" % time.strftime("%Y-%m-%d")) else: print "Nothing to report"
def save_to_excel(sector, all_news, filename='News.xlsx'): print 'save content into ', filename if os.path.exists(filename): wb = load_workbook(filename=filename) else: wb = Workbook() if sector in wb.get_sheet_names(): ws = wb[sector] row = ws.max_row else: ws = wb.create_sheet(sector) ws.cell(row=1, column=1, value='Title') ws.cell(row=1, column=2, value='Time') ws.cell(row=1, column=3, value='Symbol') ws.cell(row=1, column=4, value='Content') row = 1 for i, c in zip(range(row + 1, row + 1 + len(all_news)), all_news): ws.cell(row=i, column=1, value=c['title']) ws.cell(row=i, column=2, value=c['time']) ws.cell(row=i, column=3, value=c['symbol']) ws.cell(row=i, column=4, value=c['content']) wb.save(filename)
def write_excel_result(q_id, q_label, q_exemplar, G_q, RESULT_EXCEL, SHEET_COUNT, graph_opt, argv_print): #print " creating excel result....." wb=Workbook() for i in range(0,SHEET_COUNT): ws = wb.create_sheet() sheetList = wb.get_sheet_names() first = wb.get_sheet_by_name(sheetList[0]) second = wb.get_sheet_by_name(sheetList[1]) third = wb.get_sheet_by_name(sheetList[2]) fourth = wb.get_sheet_by_name(sheetList[3]) fifth = wb.get_sheet_by_name(sheetList[4]) excel_qid_insert(third, q_id, q_label) # q_id insert and same q_id process in sheet 3 excel_qid_insert(fourth, q_id, q_label) # q_id insert and same q_id process in sheet 3 excel_qid_insert(fifth, q_id, q_label) # q_id insert and same q_id process in sheet 3 make_sheet1(wb, first, q_id, q_label, q_exemplar, RESULT_EXCEL, graph_opt, argv_print) make_sheet2(wb, second, G_q, RESULT_EXCEL, graph_opt, argv_print) make_sheet3(wb, third, q_id, q_label, G_q, RESULT_EXCEL, graph_opt, argv_print) make_sheet4(wb, fourth, q_id, q_label, G_q, RESULT_EXCEL, graph_opt, argv_print) make_sheet5(wb, fifth, q_id, q_label, G_q, RESULT_EXCEL, graph_opt, argv_print)
class Excel: def __init__(self, fileName): self.filename = fileName self.wb = None self.ws = None self.ids = {} self.updated = None self.removed = None self.new = None def createheaders(self, sheet, type): title = [] title.append(WriteOnlyCell(sheet, "Changes")) title.append(WriteOnlyCell(sheet, "Employee ID")) title.append(WriteOnlyCell(sheet, "Last Name")) title.append(WriteOnlyCell(sheet, "First Name")) title.append(WriteOnlyCell(sheet, "PL Name")) title.append(WriteOnlyCell(sheet, "Sex")) title.append(WriteOnlyCell(sheet, "Birthdate")) title.append(WriteOnlyCell(sheet, "Process Level")) title.append(WriteOnlyCell(sheet, "Department")) title.append(WriteOnlyCell(sheet, "R Name")) title.append(WriteOnlyCell(sheet, "Date Hired")) for i in title: i.font = Font(bold=True) if type == "Updated": sheet.append(title) else: sheet.append(title[1:]) def setupColumnSize(self): self.updated.column_dimensions["A"].width = 20 self.updated.column_dimensions["B"].width = 15 self.updated.column_dimensions["C"].width = 30 self.updated.column_dimensions["D"].width = 20 self.updated.column_dimensions["E"].width = 35 self.updated.column_dimensions["F"].width = 5 self.updated.column_dimensions["G"].width = 20 self.updated.column_dimensions["H"].width = 15 self.updated.column_dimensions["I"].width = 15 self.updated.column_dimensions["J"].width = 40 self.updated.column_dimensions["K"].width = 20 self.removed.column_dimensions["A"].width = 15 self.removed.column_dimensions["B"].width = 30 self.removed.column_dimensions["C"].width = 20 self.removed.column_dimensions["D"].width = 35 self.removed.column_dimensions["E"].width = 5 self.removed.column_dimensions["F"].width = 20 self.removed.column_dimensions["G"].width = 15 self.removed.column_dimensions["H"].width = 15 self.removed.column_dimensions["I"].width = 40 self.removed.column_dimensions["J"].width = 20 self.new.column_dimensions["A"].width = 15 self.new.column_dimensions["B"].width = 30 self.new.column_dimensions["C"].width = 20 self.new.column_dimensions["D"].width = 35 self.new.column_dimensions["E"].width = 5 self.new.column_dimensions["F"].width = 20 self.new.column_dimensions["G"].width = 15 self.new.column_dimensions["H"].width = 15 self.new.column_dimensions["I"].width = 40 self.new.column_dimensions["J"].width = 20 def setupfile(self): self.wb = Workbook(write_only=True) self.updated = self.wb.create_sheet("Updated") self.removed = self.wb.create_sheet("Removed") self.new = self.wb.create_sheet("New") self.setupColumnSize() self.createheaders(self.updated, "Updated") self.createheaders(self.removed, "Removed") self.createheaders(self.new, "New") def add_new(self, data): self.new.append(data) def add_removed(self, data): self.removed.append(data) def add_updated(self, dataold, datanew, change): for i in change: temp_old = WriteOnlyCell(self.updated, dataold[i]) temp_new = WriteOnlyCell(self.updated, datanew[i]) temp_old.font = Font(color=RED) temp_new.font = Font(color="008000") dataold[i] = temp_old datanew[i] = temp_new self.updated.append(dataold) self.updated.append(datanew) self.updated.append([""]) def loadfile(self): self.wb = load_workbook(self.filename, read_only=True) first_sheet = self.wb.get_sheet_names()[0] self.ws = self.wb.get_sheet_by_name(first_sheet) def getRow(self, rownumber): data = [] for column in range(1, self.ws.max_column + 1): temp = self.ws.cell(row=rownumber, column=column).value data.append(temp) return data def getIDs(self): rownum = 1 for row in self.ws.rows: # Skip headers if rownum == 1: rownum += 1 continue # Get ID Value id = row[0].value # Reached end of data if ID is none if id is None: break # Collect Data from row rowdata = [] for cell in row: if cell.value == None: break rowdata.append(cell.value) self.ids[id] = rowdata rownum += 1 def savefile(self): self.wb.save(self.filename + ".xlsx")
def saveTableToExelle(rFilesDir): # get reports from FilingSummary reports = [] try: fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml")) for rElt in fsdoc.iter(tag="Report"): reports.append(Report(rElt.findtext("LongName"), rElt.findtext("ShortName"), rElt.findtext("HtmlFileName"))) except (EnvironmentError, etree.LxmlError) as err: print("FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err)) wb = Workbook(encoding='utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31-len(str(reportNum))] + str(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string #htmlSource = '' #with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh: # htmlSource = fh.read() #rdoc = html.document_fromstring(htmlSource) rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName)) row = -1 mergedAreas = {} # colNumber: (colspan,lastrow) for tableElt in rdoc.iter(tag="table"): # skip pop up tables if tableElt.get("class") == "authRefData": continue if tableElt.getparent().tag == "div": style = tableElt.getparent().get("style") if style and displayNonePattern.match(style): continue colWidths = {} for rowNum, trElt in enumerate(tableElt.iter(tag="tr")): # remove passed mergedAreas for mergeCol in [col for col, mergedArea in mergedAreas.items() if mergedArea[1] > rowNum]: del mergedAreas[mergeCol] col = 0 for coltag in ("th", "td"): for cellElt in trElt.iter(tag=coltag): if col == 0: row += 1 # new row if col in mergedAreas: col += mergedAreas[col][0] - 1 text = cellElt.text_content() colspan = intCol(cellElt, "colspan", 1) rowspan = intCol(cellElt, "rowspan", 1) #if col not in colWidths: # colWidths[col] = 10.0 # some kind of default width for elt in cellElt.iter(): style = elt.get("style") if style and "width:" in style: try: kw, sep, width = style.partition("width:") if "px" in width: width, sep, kw = width.partition("px") width = float(width) * 0.67777777 else: width = float(width) colWidths[col] = width except ValueError: pass if rowspan > 1: mergedAreas[col] = (colspan, row + rowspan - 1) cell = ws.cell(row=row,column=col) if text: cell.value = text if numberPattern.match(text): cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT else: cell.style.alignment.wrap_text = True if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=row, end_row=row+rowspan-1, start_column=col, end_column=col+colspan-1) cell.style.alignment.vertical = Alignment.VERTICAL_TOP if coltag == "th": cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER cell.style.font.bold = True cell.style.font.size = 9 # some kind of default size col += colspan for col, width in colWidths.items(): ws.column_dimensions[get_column_letter(col+1)].width = width except (EnvironmentError, etree.LxmlError) as err: print("{0}: directory {1} error: {2}".format(report.htmlFileName, rFilesDir, err)) wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
def saveTableToExelle(rFilesDir): # get reports from FilingSummary reports = [] try: fsdoc = etree.parse(os.path.join(rFilesDir, "FilingSummary.xml")) for rElt in fsdoc.iter(tag="Report"): reports.append( Report(rElt.findtext("LongName"), rElt.findtext("ShortName"), rElt.findtext("HtmlFileName"))) except (EnvironmentError, etree.LxmlError) as err: print("FilingSummary.xml: directory {0} error: {1}".format( rFilesDir, err)) wb = Workbook(encoding='utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31 - len(str(reportNum))] + str(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string #htmlSource = '' #with open(os.path.join(rFilesDir, report.htmlFileName), 'rt', encoding='utf-8') as fh: # htmlSource = fh.read() #rdoc = html.document_fromstring(htmlSource) rdoc = html.parse(os.path.join(rFilesDir, report.htmlFileName)) row = -1 mergedAreas = {} # colNumber: (colspan,lastrow) for tableElt in rdoc.iter(tag="table"): # skip pop up tables if tableElt.get("class") == "authRefData": continue if tableElt.getparent().tag == "div": style = tableElt.getparent().get("style") if style and displayNonePattern.match(style): continue colWidths = {} for rowNum, trElt in enumerate(tableElt.iter(tag="tr")): # remove passed mergedAreas for mergeCol in [ col for col, mergedArea in mergedAreas.items() if mergedArea[1] > rowNum ]: del mergedAreas[mergeCol] col = 0 for coltag in ("th", "td"): for cellElt in trElt.iter(tag=coltag): if col == 0: row += 1 # new row if col in mergedAreas: col += mergedAreas[col][0] - 1 text = cellElt.text_content() colspan = intCol(cellElt, "colspan", 1) rowspan = intCol(cellElt, "rowspan", 1) #if col not in colWidths: # colWidths[col] = 10.0 # some kind of default width for elt in cellElt.iter(): style = elt.get("style") if style and "width:" in style: try: kw, sep, width = style.partition( "width:") if "px" in width: width, sep, kw = width.partition( "px") width = float(width) * 0.67777777 else: width = float(width) colWidths[col] = width except ValueError: pass if rowspan > 1: mergedAreas[col] = (colspan, row + rowspan - 1) cell = ws.cell(row=row, column=col) if text: cell.value = text if numberPattern.match(text): cell.style.alignment.horizontal = Alignment.HORIZONTAL_RIGHT else: cell.style.alignment.wrap_text = True if colspan > 1 or rowspan > 1: ws.merge_cells(start_row=row, end_row=row + rowspan - 1, start_column=col, end_column=col + colspan - 1) cell.style.alignment.vertical = Alignment.VERTICAL_TOP if coltag == "th": cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER cell.style.font.bold = True cell.style.font.size = 9 # some kind of default size col += colspan for col, width in colWidths.items(): ws.column_dimensions[get_column_letter(col + 1)].width = width except (EnvironmentError, etree.LxmlError) as err: print("{0}: directory {1} error: {2}".format( report.htmlFileName, rFilesDir, err)) wb.save(os.path.join(rFilesDir, "exelleOut.xlsx"))
# get reports from FilingSummary reports = [] try: fsdoc = etree.parse(os.path.join(rFilesDir, u"FilingSummary.xml")) for rElt in fsdoc.iter(tag=u"Report"): reports.append(Report(rElt.findtext(u"LongName"), rElt.findtext(u"ShortName"), rElt.findtext(u"HtmlFileName"))) except (EnvironmentError, etree.LxmlError), err: print u"FilingSummary.xml: directory {0} error: {1}".format(rFilesDir, err) wb = Workbook(encoding=u'utf-8') # remove predefined sheets for sheetName in wb.get_sheet_names(): ws = wb.get_sheet_by_name(sheetName) if ws is not None: wb.remove_sheet(ws) sheetNames = set() # prevent duplicates for reportNum, report in enumerate(reports): sheetName = report.shortName[:31] # max length 31 for excel title if sheetName in sheetNames: sheetName = sheetName[:31-len(unicode(reportNum))] + unicode(reportNum) sheetNames.add(sheetName) ws = wb.create_sheet(title=sheetName) try: # doesn't detect utf-8 encoding the normal way, pass it a string