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 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 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)
