def parse_font(self, font_node): """Read individual font""" font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') bold = font_node.find('{%s}b' % SHEET_MAIN_NS) if bold is not None: font.bold = bool(bold.get('val', True)) italic = font_node.find('{%s}i' % SHEET_MAIN_NS) if italic is not None: font.italic = bool(italic.get('val', True)) underline = font_node.find('{%s}u' % SHEET_MAIN_NS) if underline is not None: font.underline = underline.get('val', 'single') strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS) if strikethrough is not None: font.strikethrough = True color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: font.color.index = self._get_relevant_color(color) return font
def parse_fonts(root, color_index): """Read in the fonts""" font_list = [] fonts = root.find('{%s}fonts' % SHEET_MAIN_NS) if fonts is not None: font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS) for font_node in font_nodes: font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)): underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val') font.underline = underline if underline else 'single' color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index): font.color.index = color_index[int(color.get('indexed'))] elif color.get('theme') is not None: if color.get('tint') is not None: font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): font.color.index = color.get('rgb') font_list.append(font) return font_list
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == { 'font': font, 'border': borders, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def format_cell(self,cell,type = 'normal'): # my_font = Font(size = 8) my_font = Font(name='Calibri', size=8, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000' # color='FFBB00' ) if type == "error": my_font.color = 'F60' my_font.size = 11 cell.font = my_font
def format_cell(self, cell, type='normal'): # my_font = Font(size = 8) my_font = Font(name='Calibri', size=8, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000' # color='FFBB00' ) if type == "error": my_font.color = 'F60' my_font.size = 11 cell.font = my_font
def test_conditional_formatting_addDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK dxfId = cf.addDxfStyle(self.workbook, font, borders, fill) assert dxfId == 0 dxfId = cf.addDxfStyle(self.workbook, None, None, fill) assert dxfId == 1 assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def exportTable(matrix, fname, format, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save format: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = unicode(QApplication.translate("pychemqt", "Table")) if fname.split(".")[-1] != format: fname+=".%s" % format # Format title if title: header = [] for ttl in title: line = unicode(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) c_newline=maketrans(os.linesep, " ") if format == "csv": import csv with open(fname, "w") as archivo: writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE) # Add Data if title: writer.writerow([ttl.translate(c_newline) for ttl in header]) c_float=maketrans(".", ",") for row in matrix: writer.writerow([str(data).translate(c_float) for data in row]) elif format == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet=ezodf.Table(sheetTitle) sheets+=sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif format == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif format == "xlsx": import openpyxl from openpyxl.styles import Style, Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)] = ttl sheet["%s%i"%(spreadsheetColumn(i), 1)].style.font= font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)] = data sheet["%s%i"%(spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + format)
def exportTable(matrix, fname, ext, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save ext: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = QApplication.translate("pychemqt", "Table") if fname.split(".")[-1] != ext: fname += ".%s" % ext # Format title header = [] if title: for ttl in title: line = str(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "[" + line[-1] + "]" header.append(" ".join(line)) if ext == "csv": import csv with open(fname, 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() for row in matrix: kw = {} for ttl, value in zip(header, row): kw[ttl] = value writer.writerow(kw) elif ext == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix) + 1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data) spreadsheet.save() elif ext == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j + 1, i, data) spreadsheet.save(fname) elif ext == "xlsx": import openpyxl from openpyxl.styles import Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data sheet["%s%i" % (spreadsheetColumn(i), j + 2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError( QApplication.translate("pychemqt", "Unsopported format") + " " + ext)
def exportTable(matrix, fname, ext, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save ext: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = QApplication.translate("pychemqt", "Table") if fname.split(".")[-1] != ext: fname += ".%s" % ext # Format title header = [] if title: for ttl in title: line = str(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) if ext == "csv": import csv with open(fname, 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() for row in matrix: kw = {} for ttl, value in zip(header, row): kw[ttl] = value writer.writerow(kw) elif ext == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif ext == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif ext == "xlsx": import openpyxl from openpyxl.styles import Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)] = data sheet["%s%i" % (spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + ext)
print '' print '----Questions that need answering----' exportlocation = raw_input( 'Please enter the file path for the report (e.g. C:\Scripts\SWManager):') if exportlocation == '': exportlocation = 'C:\Scripts\SWManager' exportlocation = str(exportlocation) importlocation = raw_input( 'Please enter the import folder path (it will check all subfolders (e.g. C:\Scripts\SWManager\HC)):' ) if importlocation == '': importlocation = 'C:\Scripts\SWManager\HC' importlocation = str(importlocation) # Report Header Style HeaderFont = Font(bold=True) HeaderFont.size = 12 HeaderStyle = NamedStyle(name='BoldHeader') HeaderStyle.font = HeaderFont # Create Exportlocation folder if its missing if not os.path.exists(exportlocation): os.makedirs(exportlocation) #### Menu print '' print '#################################################' print '### ###' print '### Please select an option below ###' print '### ###' print '### 1. Export health check TXT to XLSX ###' print '### 2. PLACEHOLDER ###' print '### 3. PLACEHOLDER ###' print '### 4. Exit ###'
def exportTable(matrix, fname, format, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save format: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = unicode(QApplication.translate("pychemqt", "Table")) if fname.split(".")[-1] != format: fname += ".%s" % format # Format title if title: header = [] for ttl in title: line = unicode(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "[" + line[-1] + "]" header.append(" ".join(line)) c_newline = maketrans(os.linesep, " ") if format == "csv": import csv with open(fname, "w") as archivo: writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE) # Add Data if title: writer.writerow([ttl.translate(c_newline) for ttl in header]) c_float = maketrans(".", ",") for row in matrix: writer.writerow([str(data).translate(c_float) for data in row]) elif format == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix) + 1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)].set_value(data) spreadsheet.save() elif format == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j + 1, i, data) spreadsheet.save(fname) elif format == "xlsx": import openpyxl from openpyxl.styles import Style, Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j + 2)] = data sheet["%s%i" % (spreadsheetColumn(i), j + 2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError( QApplication.translate("pychemqt", "Unsopported format") + " " + format)