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
