def separate_xl_content(src_filepath):
src_wb = load_workbook(src_filepath, use_iterators=True)
src_ws = src_wb.get_sheet_by_name(name = "Sheet")
mytree = {}
for row in src_ws.iter_rows():
subxlfilename = row[0].internal_value
if not mytree.has_key(subxlfilename):
mytree[subxlfilename] = []
values = []
for cell in row[1:]:
values.append(cell.internal_value)
mytree[subxlfilename].append(values)
ret = []
for subxlfilename in mytree.keys():
wb = Workbook()
ws = wb.get_sheet_by_name(name="Sheet")
for values in mytree[subxlfilename]:
ws.append(values)
wb.save(subxlfilename)
ret.append(subxlfilename)
return ret
def get_xlsx(self, data, book, sheet):
try:
wb = load_workbook(r'./' + book + '.xlsx')
except:
wb = Workbook()
try:
wb.remove_sheet(wb.get_sheet_by_name("Sheet"))
except:
pass
try:
ws = wb.create_sheet(sheet)
except:
self.text.insert(1.0, '工作表名不能重复')
try:
rows = len(data)
cols = len(data[0])
i = 1
for rx in range(rows):
for cx in range(cols):
ws.cell(row=rx + 1, column=cx + 1).value = data[rx][cx]
wb.save(filename=r'./' + book + '.xlsx')
self.text.insert(
1.0,
'\n' + book + '.' + sheet + '(收到' + str(rows - 1) + '条数据)')
except:
wb.save(filename=r'./' + book + '.xlsx')
self.text.insert(1.0, '\n' + book + '.' + sheet + '(收到0条数据)')
return 0
def separate_xl_content(src_filepath):
src_wb = load_workbook(src_filepath, use_iterators=True)
src_ws = src_wb.get_sheet_by_name(name="Sheet")
mytree = {}
for row in src_ws.iter_rows():
subxlfilename = row[0].internal_value
if not mytree.has_key(subxlfilename):
mytree[subxlfilename] = []
values = []
for cell in row[1:]:
values.append(cell.internal_value)
mytree[subxlfilename].append(values)
ret = []
for subxlfilename in mytree.keys():
wb = Workbook()
ws = wb.get_sheet_by_name(name="Sheet")
for values in mytree[subxlfilename]:
ws.append(values)
wb.save(subxlfilename)
ret.append(subxlfilename)
return ret
def test1():
wb = Workbook()
ws = wb.get_sheet_by_name(name=r"Sheet")
merge_xl_content(ws, '/home/huzhennan/Works/local/Gallery2/books.xlsx', 'Sheet')
wb.save('test.xlsx')
def render_xlsx(self, outfd, data):
wb = Workbook(optimized_write = True)
ws = wb.create_sheet()
ws.title = 'Timeline Output'
header = ["Time", "Type", "Item", "Details", "Reason"]
ws.append(header)
total = 1
for line in data:
coldata = line.split("|")
ws.append(coldata)
total += 1
wb.save(filename = self._config.OUTPUT_FILE)
if self._config.HIGHLIGHT != None:
wb = load_workbook(filename = self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name = "Timeline Output")
for col in xrange(1, len(header) + 1):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style.font.bold = True
for row in xrange(2, total + 1):
for col in xrange(2, len(header)):
if ws.cell("{0}{1}".format(get_column_letter(col), row)).value in self.suspicious.keys():
self.fill(ws, row, len(header) + 1, self.suspicious[ws.cell("{0}{1}".format(get_column_letter(col), row)).value]["color"])
ws.cell("{0}{1}".format(get_column_letter(col + 1), row)).value = self.suspicious[ws.cell("{0}{1}".format(get_column_letter(col), row)).value]["reason"]
wb.save(filename = self._config.OUTPUT_FILE)
def test_get_sheet_by_name():
wb = Workbook()
new_sheet = wb.create_sheet()
title = 'my sheet'
new_sheet.title = title
found_sheet = wb.get_sheet_by_name(title)
assert new_sheet == found_sheet
def test_get_sheet_by_name():
wb = Workbook()
new_sheet = wb.create_sheet()
title = 'my sheet'
new_sheet.title = title
found_sheet = wb.get_sheet_by_name(title)
eq_(new_sheet, found_sheet)
def test_get_sheet_by_name():
wb = Workbook()
new_sheet = wb.create_sheet()
title = "my sheet"
new_sheet.title = title
found_sheet = wb.get_sheet_by_name(title)
eq_(new_sheet, found_sheet)
def test1():
wb = Workbook()
ws = wb.get_sheet_by_name(name=r"Sheet")
merge_xl_content(ws, '/home/huzhennan/Works/local/Gallery2/books.xlsx',
'Sheet')
wb.save('test.xlsx')
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 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)
def merge_component_xl(dir, xl_name):
def visit_xl_file(workbook, dirname, names):
for name in names:
filepath = os.path.join(dirname, name)
print "filepath: %s" % filepath
print 'match: %s' % isXlsxFile(filepath)
if os.path.isfile(filepath) and isXlsxFile(filepath):
merge_xl_content(workbook, filepath, 'Sheet')
result = Workbook()
ws = result.get_sheet_by_name(name=r"Sheet")
os.path.walk(dir, visit_xl_file, ws)
result.save(xl_name)
def merge_component_xl(dir, xl_name):
def visit_xl_file(workbook, dirname, names):
for name in names:
filepath = os.path.join(dirname, name)
print "filepath: %s" % filepath
print 'match: %s' % isXlsxFile(filepath)
if os.path.isfile(filepath) and isXlsxFile(filepath):
merge_xl_content(workbook, filepath, 'Sheet')
result = Workbook()
ws = result.get_sheet_by_name(name=r"Sheet")
os.path.walk(dir, visit_xl_file, ws)
result.save(xl_name)
def __writeNew(self, outPath, output):
wb = Workbook()
wb.save(outPath)
if self.__worksheet in wb.sheetnames:
ws = wb.get_sheet_by_name(self.__worksheet)
else:
ws = wb.create_sheet(title=self.__worksheet)
if self.__header:
ws.append(output.getColumns())
for row in output:
ws.append((value for value in row))
wb.save(outPath)
def save_openpyxl(self, sheetname):
'''不能覆盖写'''
# 新建一个workbook
wb = Workbook()
# ws1 = wb.create_sheet () # 默认插在最后
# ws2 = wb.create_sheet (0) # 插在开头
sheets = wb.worksheets
sheetnames = wb.sheetnames
for name in sheetnames:
if name == sheetname:
ws = wb.get_sheet_by_name(sheetname)
else:
ws = wb.create_sheet(index=0, title=sheetname)
# archive=ZipFile('001.txt', 'w', 8, allowZip64=True)
# ew = ExcelWriter (workbook=wb,archive = archive)
path = self.path.split('.')[0] + '.xlsx'
data = self.save_txt()
# 设置表头
headers = ['模块', '页面元素', '标签', 'by', 'by_style', '二级定位']
for i in range(len(headers)):
s = ws.cell(row=1, column=i + 1)
s.value = headers[i]
wb.save(path)
n = 1
values = [(k, v) for row, values in data.items()
for k, v in values.items()]
for i in range(len(data)):
# if i == 15: break
for j in range(len(headers)):
k = [k for k, v in values]
v = [v for k, v in values]
if k[j] == headers[j]:
real = data[i][k[j]]
print(real)
s1 = ws.cell(row=i + 1, column=j + 1)
s1.value = real
j += 1
n += 1
i += 1
wb.save(path)
# ew.save(path)(使用ExcelWriter保存)
print('使用openpyxls成功将[%s]行element写入%s文件！' % (i, path))
class excel_writer:
def __init__(self, FILE_NAME):
self.wb = Workbook()
self.ew = ExcelWriter(workbook=self.wb)
self.filename = os.path.join(local_config()['install_path'],'queries/logs/%s/'%(FILE_NAME.split('.')[0]), FILE_NAME)
self.ws_num = 0
self.ws = []
def create_sheet(self, sheet_title):
self.ws.append(self.wb.create_sheet(self.ws_num, sheet_title))
#self.ws[self.ws_num].title = sheet_title
self.ws_num += 1
def set_cell(self, sheet_title, col, row, val):
ws = self.wb.get_sheet_by_name(sheet_title)
ws.cell('%s%s' % (get_column_letter(col), row)).value = '%s' % (val)
# str(val.decode('utf-8')))
def save(self):
self.wb.save(filename=self.filename)
class excel_writer:
def __init__(self,log_dir, FILE_NAME):
self.wb = Workbook()
self.ew = ExcelWriter(workbook=self.wb)
self.filename = os.path.join(log_dir, FILE_NAME)
self.ws_num = 0
self.ws = []
def create_sheet(self, sheet_title):
self.ws.append(self.wb.create_sheet(index = self.ws_num, title = sheet_title))
#self.ws[self.ws_num].title = sheet_title
self.ws_num += 1
def set_cell(self, sheet_title, col, row, val):
ws = self.wb.get_sheet_by_name(sheet_title)
ws.cell('%s%s' % (get_column_letter(col), row)).value = '%s' % (val)
# str(val.decode('utf-8')))
def save(self):
self.wb.save(filename=self.filename)
def render_xlsx(self, outfd, data):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = 'Timeline Output'
header = ["Time", "Type", "Item", "Details", "Reason"]
ws.append(header)
total = 1
for line in data:
coldata = line.split("|")
ws.append(coldata)
total += 1
wb.save(filename=self._config.OUTPUT_FILE)
if self._config.HIGHLIGHT != None:
wb = load_workbook(filename=self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name="Timeline Output")
for col in xrange(1, len(header) + 1):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style.font.bold = True
for row in xrange(2, total + 1):
for col in xrange(2, len(header)):
if ws.cell("{0}{1}".format(
get_column_letter(col),
row)).value in self.suspicious.keys():
self.fill(
ws, row,
len(header) + 1,
self.suspicious[ws.cell("{0}{1}".format(
get_column_letter(col), row)).value]["color"])
ws.cell("{0}{1}".format(
get_column_letter(col + 1),
row)).value = self.suspicious[ws.cell(
"{0}{1}".format(get_column_letter(col),
row)).value]["reason"]
wb.save(filename=self._config.OUTPUT_FILE)
def search(name, key, dir):
CurrentDir = dir
keyword = key
url = "http://index.sogou.com/sidx?type=0&query=" + keyword.decode("gbk").encode("gb2312") +"&newstype=-1"
req = urllib2.urlopen(url)
# url2 = req.geturl()
# req = urllib2.Request(url2)
# doc = urllib2.urlopen(req)
doc = req.read()
# print doc
PeriodMatch = re.search(ur'"\d{4}\W\d{1,2}\W\d{1,2}\|\d{4}\W\d{1,2}\W\d{1,2}"',doc)
UserIndexMatch = re.search(ur'"userIndexes":"((\d)+,)+(\d)+',doc)
MediaIndexMatch = re.search(ur"tmp.mediaIndexes = '((\d)+,)+(\d)+'",doc)
# print PeriodMatch.group()
# print UserIndexMatch.group()
# print UserIndexMatch.group()[15:]
# print MediaIndexMatch.group()
# print MediaIndexMatch.group()[21:-1]
if UserIndexMatch == None or MediaIndexMatch == None:
print ("|-------------------------------------------------------------------------")
print ("|Keyword: " + keyword)
print ("|Sorry, but this keyword hasn't been included by SogouIndex.")
else:
UserIndex = UserIndexMatch.group()[15:].split(",")
MediaIndex = MediaIndexMatch.group()[21:-1].split(",")
# print UserIndex
# print MediaIndex
# print len(UserIndex)
# print len(MediaIndex)
print ("|Data received.")
# print PeriodMatch.group()[1:11]
Start = time.mktime(time.strptime(PeriodMatch.group()[1:11],'%Y-%m-%d'))
# print Start
# print PeriodMatch.group()[12:-1]
End = time.mktime(time.strptime(PeriodMatch.group()[12:-1],'%Y-%m-%d'))
# print End
DayLength = (End-Start)/3600/24 + 1
if len(UserIndex)==DayLength and len(MediaIndex)==DayLength:
print ("|Check: The data length is correct.")
else:
print ("|Check: Error in data length.")
Datetime = datetime.datetime.strptime(PeriodMatch.group()[1:11],'%Y-%m-%d')
# print StartDatetime
RecordDate = []
for i in range(0, int(DayLength)):
RecordDate.append(Datetime.strftime('%Y-%m-%d'))
Datetime = Datetime + datetime.timedelta(days=1)
k = 0
# print RecordDate
if os.path.isfile(CurrentDir+'\\'+name+'.xlsx') and (keyword!=name):
wb = load_workbook(filename = name +".xlsx")
ws = wb.get_sheet_by_name("SogouIndex")
k = ws.get_highest_column()
# print(CurrentDir+'\\'+name+'.xlsx')
# wb = load_workbook
# print WorkbookTemp.get_sheet(0).cell(0,0).value
# Workbook = copy(WorkbookTemp)
# Worksheet = Workbook.get_sheet(0)
#print Worksheet.cell(0,0),value
ws.cell(row=1,column=1+k).value=keyword.decode('gbk')
ws.cell(row=1,column=2+k).value=keyword.decode('gbk')
ws.cell(row=2,column=1+k).value='UserIndex'
ws.cell(row=2,column=2+k).value='MediaIndex'
for i in range(0+k,2+k):
for j in range(2,int(DayLength)+2):
ws.cell(row=j+1,column=1+k).value=int(UserIndex[j-2])
ws.cell(row=j+1,column=2+k).value=int(MediaIndex[j-2])
else:
wb = Workbook( )
ws = wb.get_sheet_by_name("Sheet")
ws.title = 'SogouIndex'
k=0
ws.cell(row=1,column=2+k).value=keyword.decode('gbk')
ws.cell(row=1,column=3+k).value=keyword.decode('gbk')
ws.cell(row=2,column=1+k).value='Date'
ws.cell(row=2,column=2+k).value='UserIndex'
ws.cell(row=2,column=3+k).value='MediaIndex'
for i in range(0+k,2+k):
for j in range(2,int(DayLength)+2):
ws.cell(row=j+1,column=1+k).value='%s'%RecordDate[j-2]
ws.cell(row=j+1,column=2+k).value=int(UserIndex[j-2])
ws.cell(row=j+1,column=3+k).value=int(MediaIndex[j-2])
wb.save(CurrentDir+'\\'+name+'.xlsx')
print ("|Data saved")
print ("|-------------------------------------------------------------------------")
print ("|Keyword: " + keyword)
if (keyword!=name):
print ("|Saving Structure: multi-keywords")
else:
print ("|Saving Structure: single-keywords")
print ("|Time span: "+ str(int(DayLength)) +' days')
print ("|Excel File saved at "+CurrentDir+'\\'+name+'.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"))
class TSSummary:
colName = \
[
'METRICS',
' Code Based',
' Key Accounts',
' Ericsson',
' Nokia',
' ALU',
' All Others',
' Regional Key Accounts',
' Qualcomm',
' AT&T',
' Sprint',
' Carriers',
' AT&T',
' Sprint',
' T-Mobile',
' Small Cell',
' Qualcomm',
' Intel',
' Parallel',
' SpiderCloud',
' Modular Instruments',
' Qor',
' Ter',
' Semiconductor',
' Air',
' Van',
' Others',
' OTH',
' COB',
' TTT',
' Overhead',
' X4x',
' X1x',
' Team Based',
' DMR',
' MI',
' Total Hours',
' All',
' Permenent',
' Contract',
' Labour',
' Travel',
' Standby',
' FAEs'
]
def __init__(self,fname):
self.filename = fname
self.sslist = [] # list of spreadsheets by week
self.tsdict = {} # dict of timesheet data by week
self.week = None # current week number
self.year = None # year of query
self.swb = None # summary workbook
self.metrics = {} # summary metrics
#---------------------------------------------------------------------
def Process(self,tsdata,year,week,region,team):
self.year = year
self.week = week
self.Sort(tsdata,week)
for i,week in enumerate(self.sslist):
wsDate = Calendar.week[i+1]
self.tsdict[wsDate] = []
for j,ssdata in enumerate(week):
ts = Timesheet(ssdata, wsDate)
ts.ReadFile()
self.tsdict[wsDate].append(ts)
self.createWorkbook(region)
self.writeRawDataSheet(region,team)
self.saveWorkbook()
#---------------------------------------------------------------------
def createWorkbook(self,region):
self.swb = Workbook()
self.swb.create_sheet(region + ' Charts')
self.swb.create_sheet(region + ' Tables')
self.swb.create_sheet(region + ' Metrics')
self.swb.create_sheet(region + ' Data')
self.swb.remove_sheet(self.swb.get_sheet_by_name('Sheet'))
#---------------------------------------------------------------------
def saveWorkbook(self):
self.swb.save(self.filename)
#---------------------------------------------------------------------
def setCell(self,cell,align,fmt,value):
if (align == 'C'):
align = Alignment(horizontal='center',vertical='center')
elif (align == 'L'):
align = Alignment(horizontal='left',vertical='center')
elif (align == 'R'):
align = Alignment(horizontal='right',vertical='center')
else:
align = Alignment(horizontal='right',vertical='center')
side = Side(style='thin')
border = Border(left=side,right=side,top=side,bottom=side)
#style = Style(border=border,alignment=align,number_format=fmt)
#cell.style = style
if (fmt == 'F'):
fmt = '0.00'
cell.number_format = fmt
cell.alignment = align.copy()
cell.border = border.copy()
cell.value = value
#---------------------------------------------------------------------
def flagCell(self,cell):
side = Side(style='medium',color=RED)
border = Border(left=side,right=side,top=side,bottom=side)
#style = Style(border=border)
#cell.style = style
cell.border = border.copy()
#---------------------------------------------------------------------
def writeRawDataSheet(self,region,team):
ws_name = region + ' Data'
ws = self.swb.get_sheet_by_name(ws_name)
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 3
ws.column_dimensions['D'].width = 5
ws.column_dimensions['E'].width = 3
ws.column_dimensions['F'].width = 11
ws.column_dimensions['G'].width = 11
ws.column_dimensions['H'].width = 7
ws.column_dimensions['I'].width = 5
ws.column_dimensions['J'].width = 5
ws.column_dimensions['K'].width = 5
ws.column_dimensions['L'].width = 8
ws.column_dimensions['M'].width = 10
ws.column_dimensions['N'].width = 80
wsRow = 2
wsCol = 2
#-------------------------------------------------------------------
# Process each week
#-------------------------------------------------------------------
for weekIndex in range(1,self.week+1):
wsDate = Calendar.week[weekIndex]
tslist = self.tsdict[wsDate]
metrics = Metrics(wsDate)
#-----------------------------------------------------------------
# Process timesheet specific informaton
#-----------------------------------------------------------------
for ts in tslist:
fae = ts.ssdata.fae
#---------------------------------------------------------------
# Process each entry in the timesheet
#---------------------------------------------------------------
for entry in ts.entries:
self.setCell(ws.cell(row=wsRow,column=wsCol+ 0),'L','G',fae.fullname.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 1),'C','G',fae.laborType.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 2),'C','G',fae.team.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 3),'C','G',fae.loc.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 4),'C','G',ts.ssdata.wsDate.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 5),'C','D',entry.date.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 6),'C','G',entry.code.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 7),'C','G',entry.location.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 8),'C','G',entry.activity.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+ 9),'C','G',entry.product.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+10),'R','F',entry.hours.GetWSVal())
self.setCell(ws.cell(row=wsRow,column=wsCol+11),'C','G',entry.workType.GetWSVal())
# Flag as red things to check
if (entry.code.GetVal() == 'OTH'):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 6))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.code.GetVal() == 5):
if (entry.location.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7))
if (entry.activity.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 8))
if (entry.product.GetVal() != None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9))
if (entry.location.GetVal() == 128):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 7))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.product.GetVal() == 22):
self.flagCell(ws.cell(row=wsRow,column=wsCol+ 9))
self.setCell(ws.cell(row=wsRow,column=wsCol+12),'L','G',entry.note.GetWSVal())
self.flagCell(ws.cell(row=wsRow,column=wsCol+12))
if (entry.hours.GetVal() == None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+10))
if (entry.workType.GetVal() == None):
self.flagCell(ws.cell(row=wsRow,column=wsCol+11))
# TODO: if hours == 0 or hours > 12 FLAG
# TODO: Blue Strips
# TODO: Check Cust vs Loc
# TODO: Add Local/Remote
# TODO: Add Local Text (AM-NE, AM-BA, etc)
metrics.Update(fae,entry)
# end of entry
wsRow += 1
#---------------------------------------------------------------
# end of a week
self.metrics[wsDate] = metrics
wsRow += 2
#-----------------------------------------------------------------
# Write out raw metrics
#-----------------------------------------------------------------
#for i in range(1,self.week+1):
# wsDate = Calendar.week[i]
# metrics = self.metrics[wsDate]
# metrics.Log()
wsRow = 2
wsCol = 2
ws_name = region + ' Metrics'
ws = self.swb.get_sheet_by_name(ws_name)
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 10
i = 1
while (i < len(TSSummary.colName)):
self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',TSSummary.colName[i])
i += 1
for fae in team.list:
name = fae.fullname.GetVal()
self.setCell(ws.cell(row=i+3,column=wsCol+0),'L','G',' ' + name)
i += 1
for i in range(1,self.week+1):
wsDate = Calendar.week[i]
metrics = self.metrics[wsDate]
self.setCell(ws.cell(row= 2,column=wsCol+0+i),'C','G',str(wsDate))
self.setCell(ws.cell(row= 3,column=wsCol+0+i),'C','G',str(i))
self.setCell(ws.cell(row= 6,column=wsCol+0+i),'R','F',metrics.codes.kam.erc)
self.setCell(ws.cell(row= 7,column=wsCol+0+i),'R','F',metrics.codes.kam.nok)
self.setCell(ws.cell(row= 8,column=wsCol+0+i),'R','F',metrics.codes.kam.alu)
self.setCell(ws.cell(row= 9,column=wsCol+0+i),'R','F',metrics.codes.kam.oth)
self.setCell(ws.cell(row=11,column=wsCol+0+i),'R','F',metrics.codes.rka.qcm)
self.setCell(ws.cell(row=12,column=wsCol+0+i),'R','F',metrics.codes.rka.att)
self.setCell(ws.cell(row=13,column=wsCol+0+i),'R','F',metrics.codes.rka.spr)
self.setCell(ws.cell(row=15,column=wsCol+0+i),'R','F',metrics.codes.car.att)
self.setCell(ws.cell(row=16,column=wsCol+0+i),'R','F',metrics.codes.car.spr)
self.setCell(ws.cell(row=17,column=wsCol+0+i),'R','F',metrics.codes.car.tmo)
self.setCell(ws.cell(row=19,column=wsCol+0+i),'R','F',metrics.codes.smc.qcm)
self.setCell(ws.cell(row=20,column=wsCol+0+i),'R','F',metrics.codes.smc.itl)
self.setCell(ws.cell(row=21,column=wsCol+0+i),'R','F',metrics.codes.smc.prw)
self.setCell(ws.cell(row=22,column=wsCol+0+i),'R','F',metrics.codes.smc.spd)
self.setCell(ws.cell(row=24,column=wsCol+0+i),'R','F',metrics.codes.mod.qor)
self.setCell(ws.cell(row=25,column=wsCol+0+i),'R','F',metrics.codes.mod.ter)
self.setCell(ws.cell(row=27,column=wsCol+0+i),'R','F',metrics.codes.sem.air)
self.setCell(ws.cell(row=28,column=wsCol+0+i),'R','F',metrics.codes.sem.van)
self.setCell(ws.cell(row=30,column=wsCol+0+i),'R','F',metrics.codes.oth.oth)
self.setCell(ws.cell(row=31,column=wsCol+0+i),'R','F',metrics.codes.oth.cob)
self.setCell(ws.cell(row=32,column=wsCol+0+i),'R','F',metrics.codes.oth.ttt)
self.setCell(ws.cell(row=34,column=wsCol+0+i),'R','F',metrics.codes.ovr.x4x)
self.setCell(ws.cell(row=35,column=wsCol+0+i),'R','F',metrics.codes.ovr.x1x)
self.setCell(ws.cell(row=37,column=wsCol+0+i),'R','F',metrics.team.dmr)
self.setCell(ws.cell(row=38,column=wsCol+0+i),'R','F',metrics.team.mi)
self.setCell(ws.cell(row=40,column=wsCol+0+i),'R','F',metrics.total.tot)
self.setCell(ws.cell(row=41,column=wsCol+0+i),'R','F',metrics.total.prm)
self.setCell(ws.cell(row=42,column=wsCol+0+i),'R','F',metrics.total.con)
self.setCell(ws.cell(row=43,column=wsCol+0+i),'R','F',metrics.total.lbr)
self.setCell(ws.cell(row=44,column=wsCol+0+i),'R','F',metrics.total.trv)
self.setCell(ws.cell(row=45,column=wsCol+0+i),'R','F',metrics.total.sby)
rowoffs = 0
for fae in team.list:
name = fae.fullname.GetVal()
if (name in metrics.fae.dict):
hours = metrics.fae.dict[name].hours
else:
hours = None
self.setCell(ws.cell(row=47+rowoffs,column=wsCol+0+i),'R','F',hours)
if (hours == None):
weDate = wsDate + datetime.timedelta(days=4)
sDate = team.dict[name].startDate.GetVal()
tDate = team.dict[name].endDate.GetVal()
if (wsDate >= sDate and weDate <= tDate):
self.flagCell(ws.cell(row=27+rowoffs,column=wsCol+0+i))
rowoffs += 1
#-----------------------------------------------------------------------
def Sort(self,tsdata,week):
self.sslist = []
for i in range(1,week+1):
list = []
dict = {}
wsDate = Calendar.week[i]
dict = tsdata.weeks[wsDate]
for key,value in dict.items():
list.append(value)
self.sslist.append(sorted(list))
def search(name, key, dir):
CurrentDir = dir
keyword = key
url = "http://index.sogou.com/sidx?type=0&query=" + keyword.decode(
"gbk").encode("gb2312") + "&newstype=-1"
req = urllib2.urlopen(url)
# url2 = req.geturl()
# req = urllib2.Request(url2)
# doc = urllib2.urlopen(req)
doc = req.read()
# print doc
PeriodMatch = re.search(
ur'"\d{4}\W\d{1,2}\W\d{1,2}\|\d{4}\W\d{1,2}\W\d{1,2}"', doc)
UserIndexMatch = re.search(ur'"userIndexes":"((\d)+,)+(\d)+', doc)
MediaIndexMatch = re.search(ur"tmp.mediaIndexes = '((\d)+,)+(\d)+'", doc)
# print PeriodMatch.group()
# print UserIndexMatch.group()
# print UserIndexMatch.group()[15:]
# print MediaIndexMatch.group()
# print MediaIndexMatch.group()[21:-1]
if UserIndexMatch == None or MediaIndexMatch == None:
print(
"|-------------------------------------------------------------------------"
)
print("|Keyword: " + keyword)
print("|Sorry, but this keyword hasn't been included by SogouIndex.")
else:
UserIndex = UserIndexMatch.group()[15:].split(",")
MediaIndex = MediaIndexMatch.group()[21:-1].split(",")
# print UserIndex
# print MediaIndex
# print len(UserIndex)
# print len(MediaIndex)
print("|Data received.")
# print PeriodMatch.group()[1:11]
Start = time.mktime(
time.strptime(PeriodMatch.group()[1:11], '%Y-%m-%d'))
# print Start
# print PeriodMatch.group()[12:-1]
End = time.mktime(time.strptime(PeriodMatch.group()[12:-1],
'%Y-%m-%d'))
# print End
DayLength = (End - Start) / 3600 / 24 + 1
if len(UserIndex) == DayLength and len(MediaIndex) == DayLength:
print("|Check: The data length is correct.")
else:
print("|Check: Error in data length.")
Datetime = datetime.datetime.strptime(PeriodMatch.group()[1:11],
'%Y-%m-%d')
# print StartDatetime
RecordDate = []
for i in range(0, int(DayLength)):
RecordDate.append(Datetime.strftime('%Y-%m-%d'))
Datetime = Datetime + datetime.timedelta(days=1)
k = 0
# print RecordDate
if os.path.isfile(CurrentDir + '\\' + name +
'.xlsx') and (keyword != name):
wb = load_workbook(filename=name + ".xlsx")
ws = wb.get_sheet_by_name("SogouIndex")
k = ws.get_highest_column()
# print(CurrentDir+'\\'+name+'.xlsx')
# wb = load_workbook
# print WorkbookTemp.get_sheet(0).cell(0,0).value
# Workbook = copy(WorkbookTemp)
# Worksheet = Workbook.get_sheet(0)
#print Worksheet.cell(0,0),value
ws.cell(row=1, column=1 + k).value = keyword.decode('gbk')
ws.cell(row=1, column=2 + k).value = keyword.decode('gbk')
ws.cell(row=2, column=1 + k).value = 'UserIndex'
ws.cell(row=2, column=2 + k).value = 'MediaIndex'
for i in range(0 + k, 2 + k):
for j in range(2, int(DayLength) + 2):
ws.cell(row=j + 1,
column=1 + k).value = int(UserIndex[j - 2])
ws.cell(row=j + 1,
column=2 + k).value = int(MediaIndex[j - 2])
else:
wb = Workbook()
ws = wb.get_sheet_by_name("Sheet")
ws.title = 'SogouIndex'
k = 0
ws.cell(row=1, column=2 + k).value = keyword.decode('gbk')
ws.cell(row=1, column=3 + k).value = keyword.decode('gbk')
ws.cell(row=2, column=1 + k).value = 'Date'
ws.cell(row=2, column=2 + k).value = 'UserIndex'
ws.cell(row=2, column=3 + k).value = 'MediaIndex'
for i in range(0 + k, 2 + k):
for j in range(2, int(DayLength) + 2):
ws.cell(row=j + 1,
column=1 + k).value = '%s' % RecordDate[j - 2]
ws.cell(row=j + 1,
column=2 + k).value = int(UserIndex[j - 2])
ws.cell(row=j + 1,
column=3 + k).value = int(MediaIndex[j - 2])
wb.save(CurrentDir + '\\' + name + '.xlsx')
print("|Data saved")
print(
"|-------------------------------------------------------------------------"
)
print("|Keyword: " + keyword)
if (keyword != name):
print("|Saving Structure: multi-keywords")
else:
print("|Saving Structure: single-keywords")
print("|Time span: " + str(int(DayLength)) + ' days')
print("|Excel File saved at " + CurrentDir + '\\' + name + '.xlsx')
def write_records_to_excel(output, population, markers, alleles = ['A','B']):
wb = Workbook()
ew = ExcelWriter(workbook = wb)
ws = wb.worksheets[0]
ws.title = "Level0"
# add marker columns - 2 columns for each allele
header = ["Individual"] + ["{0}_{1}".format(m,a) for m in markers for a in alleles]
#pdb.set_trace()
ws = write_header(ws, header)
max_depth = get_max_depth(population)
wb = create_additional_sheet(wb, header, max_depth)
pop_keys = population.keys()
pop_keys.sort()
for name_idx, name in enumerate(pop_keys):
# pdb.set_trace()
# get depth of stack
depth = population[name].shape[0]
# write invididual name at all depths
#for level in xrange(max_depth):
# ws = wb.get_sheet_by_name("Level{0}".format(level))
# ws.cell('A{0}'.format(name_idx + 2)).value = name
# ensure there is workbook at max stack depth
for level in xrange(max_depth):
# write the sample id for each row of all levels
ws = wb.get_sheet_by_name("Level{0}".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
# but only write the genotypes for the sample where there
# is a level
if level < depth:
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = population[name].lix[[str(level)]].lix[[marker]].x[pos]
# check all non-zero entries for similarity and store to summary
ws = wb.get_sheet_by_name("Summary".format(level))
ws.cell('A{0}'.format(name_idx + 2)).set_value_explicit(value = name, data_type = 's')
for marker_idx, marker in enumerate(header[1:]):
cl = get_column_letter(marker_idx + 2)
marker, allele = marker.split('_')
if allele == 'A':
pos = 0
else:
pos = 1
# if only one element, self = self
if depth <= 1:
identical = True
elif (population[name].lix[:,[marker],pos].x == '').all():
identical = True
else:
# don't penalize comparisons having '' in >= one column
empties = population[name].lix[:,[marker],pos].x != ''
genotypes_no_empties = population[name].lix[:,[marker],pos].x[empties]
# just test first element against all elements
identical = (genotypes_no_empties == genotypes_no_empties[0]).all()
if identical:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "T"
else:
ws.cell('{0}{1}'.format(cl, name_idx + 2)).value = "F"
#pdb.set_trace()
ew.save(filename = output)
ws4.cell(row=ws_line,
column=ws_col).value = curSheet_delphi.cell(
row=row_line, column=6).value
if curSheet_delphi.cell(
row=row_line, column=2).value == curSheet_delphi.cell(
row=row_line + 1, column=2).value: #same item？
ws_line += 1
# print(str(currentDir+'\\delphi\\'+tableName))
else:
#save the current item table for delphi
tableName = curSheet_delphi.cell(row=row_line,
column=2).value
#print(str(currentDir+'\\delphi\\'+tableName))
#wb.save(filename=str(currentDir+'\\delphi\\'+tableName))
wb.remove_sheet(wb.get_sheet_by_name('Sheet'))
#wb.remove_sheet( )
wb.save(filename=str(currentDir + '\\' + tableName +
'.xlsx'))
#create
wb = Workbook()
ws1 = wb.create_sheet(0)
ws1.title = u'功能匹配度'
ws1.cell(row=1, column=1).value = 'No.'
ws1.cell(row=1, column=2).value = '需求类别'
ws1.cell(row=1, column=3).value = '客户需求'
ws1.cell(row=1,
column=4).value = '第' + str(ws_col - 3) + '位专家'
ws1['A1'].style = 'Accent3'
ws1['B1'].style = 'Accent3'
ws1['C1'].style = 'Accent3'
def download_template(self, path=None):
"""This function parses variable and value labels and output template file to path in xlsx file.
Manager should fill xlsx file and upload it to server """
if not path:
path = self.template_file_path
wb = Workbook()
ws = wb.get_active_sheet()
# filling tables
ws.title = 'tables'
for col, lb in enumerate([
'QuestionID', 'Variables', 'Title', 'Subtitle\\Question',
'Caption', 'Corner', 'Properties'
]):
# making some headers on the list "table"
cell = ws.cell(row=1, column=col + 1)
cell.value = lb
# do some nice-looking things with header
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 30
ws.column_dimensions['D'].width = 60
ws.column_dimensions['E'].width = 22
ws.column_dimensions['F'].width = 10
ws.column_dimensions['G'].width = 30
row = 2
for question_id in self.hierarchical_structure.get_all_questions_ids():
# fill sheet by variable labels information
if question_id in DUMMY_FIELDS:
continue
question_structure = self.hierarchical_structure.get_variable_by_id(
question_id)
ws.cell(row=row, column=1).value = question_id
ws.cell(row=row, column=2).value = ' '.join(
question_structure['variable_children'])
ws.cell(row=row,
column=4).value = question_structure['variable_label']
ws.cell(row=row, column=5).value = u'База: все респонденты'
row += 1
wb.create_sheet(title='labels')
ws = wb.get_sheet_by_name('labels')
for col, lb in enumerate(['QuestionID', 'Variable', 'Value', 'Label']):
cell = ws.cell(row=1, column=col + 1)
cell.value = lb
# cell.fill.fill_type = FILL_SOLID
# cell.style.fill.start_color.index = 'FFD3D3D3'
ws.column_dimensions['A'].width = 30
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 100
row = 2
for question_id in self.hierarchical_structure.get_all_questions_ids():
if question_id in DUMMY_FIELDS:
continue
question_structure = self.hierarchical_structure.get_variable_by_id(
question_id)
ws.cell(row=row, column=1).value = question_id
ws.cell(row=row, column=2).value = ' '.join(
question_structure['variable_children'])
for (k, v) in question_structure['variable_values'].items():
ws.cell(row=row, column=3).value = k
ws.cell(row=row, column=4).value = v
row += 1
row += 1
wb.save(path)
def render_xlsx(self, outfd, data):
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append([
"Offset (P)", "Name", "PID", "pslist", "psscan", "thrdproc",
"pspcid", "csrss", "session", "deskthrd", "Exit Time"
])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in [
"system", "smss.exe", "csrss.exe"
]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([
hex(offset),
str(process.ImageFileName),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')
])
total += 1
wb.save(filename=self._config.OUTPUT_FILE)
wb = load_workbook(filename=self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name="Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style.font.bold = True
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col),
row)).value == "False":
ws.cell("{0}{1}".format(
get_column_letter(col),
row)).style.fill.fill_type = Fill.FILL_SOLID
ws.cell("{0}{1}".format(
get_column_letter(col),
row)).style.fill.start_color.index = "FFFF0000"
else:
ws.cell("{0}{1}".format(
get_column_letter(col),
row)).style.fill.fill_type = Fill.FILL_SOLID
ws.cell("{0}{1}".format(
get_column_letter(col),
row)).style.fill.start_color.index = "FF00FF00"
wb.save(filename=self._config.OUTPUT_FILE)
gistuff = gi.record_by_addr(item)
ip = "/".join(item.split("."))
robtex_url = "{0}/{1}".format(robtex, ip)
bl = checkbl(rev)
line = [item, gistuff["country_code"], gistuff["country_name"], bl[0], bl[1], bl[2], robtex_url]
if excelfile == None:
print ",".join(x for x in line)
else:
ws.append(line)
total += 1
if excelfile:
wb.save(filename = excelfile)
if not color:
return
wb = load_workbook(filename = excelfile)
ws = wb.get_sheet_by_name(name = "IP Address Info")
for col in xrange(1, len(header) + 1):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(1, len(header) + 1):
if ws.cell("{0}{1}".format(get_column_letter(4), row)).value == "Yes":
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = GreenStyle
wb.save(filename = excelfile)
if __name__ == "__main__":
main()
print("Creation Time for all Functional Tables was ", time2 - time1,
"seconds.")
##Function 2: create_tabs
time1 = time.time()
for key in sorted(sheet_dict.keys(), reverse=True):
create_tabs(sheet_dict[key], key)
create_tabs(fullTable, 'Headcount Summary Sorted')
time2 = time.time()
#print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable)
print("Creation Time for ALL tabs was ", time2 - time1, "seconds.")
#remove 'Sheet' worksheet, that gets created by default
target.remove_sheet(
target.get_sheet_by_name("Sheet")
) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name
#Make headers bold; format will probably be the later home of functions for number & alignment formatting
# import format
# format.bold_headers_footers(target)
##Writing that worksheet to a file
##Moved later in file, so Exceptions sheet could be written
end = time.time()
dur = end - start
print("Total processing time", dur)
def funcSheets_check_figures(d, ft):
def render_xlsx(self, outfd, data):
BoldStyle = Style(font=Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFFFF'),
fill=PatternFill(fill_type="solid",
start_color='FF000000',
end_color='FF000000'))
RedStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
border=Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'),
diagonal=Side(border_style="thick",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thick",
color='FF000000'),
vertical=Side(border_style="thick",
color='FF000000'),
horizontal=Side(border_style="thick",
color='FF000000')),
fill=PatternFill(start_color = 'FFFF0000',
end_color = 'FFFF0000',
fill_type = 'solid'))
GreenStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
fill=PatternFill(start_color = "FF00FF00",
end_color = "FF00FF00",
fill_type = "solid"))
wb = Workbook(optimized_write = True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append(["Offset (P)",
"Name",
"PID",
"pslist",
"psscan",
"thrdproc",
"pspcid",
"csrss",
"session",
"deskthrd",
"Exit Time"])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in ["system", "smss.exe", "csrss.exe"]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([hex(offset),
str(utils.remove_unprintable(str(process.ImageFileName)) or ""),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')])
total += 1
wb.save(filename = self._config.OUTPUT_FILE)
wb = load_workbook(filename = self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name = "Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col), row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = GreenStyle
wb.save(filename = self._config.OUTPUT_FILE)
#!/usr/bin/env python3
import openpyxl
import os
os.chdir(
"/home/fabio/Desktop/estudo_ti/Python/python_and_spreadsheets/Automate-the-boring-stuff-with-python"
)
from openpyxl.workbook import Workbook
from openpyxl.styles import Font, NamedStyle
wb = Workbook()
ws = wb.active
sheet = wb.get_sheet_by_name('Sheet')
fontObj1 = NamedStyle("fontObj1")
fontObj1.font = Font(name="Times New Roman", bold=True)
wb.add_named_style(fontObj1)
ws['A1'].style = "fontObj1"
sheet["A1"] = "Bold Times New Roman"
fontObj2 = NamedStyle("fontObj2")
fontObj2.font = Font(size=24, italic=True, name="Arial", color="000111")
wb.add_named_style(fontObj2)
ws['B3'].style = 'fontObj2'
sheet["B3"] = '24pt Italic'
wb.save("styles.xlsx")
time2 = time.time()
print("Creation Time for all Functional Tables was ", time2-time1, "seconds.")
##Function 2: create_tabs
time1 = time.time()
for key in sorted(list(sheet_dict.keys()), reverse = True):
create_tabs(sheet_dict[key], key)
create_tabs(fullTable, 'Headcount Summary Sorted')
time2 = time.time()
#print "Length of all tables is", len(subsTable + estSTable + prjCTable + infoTable + procTable + legaTable + engiTable + humaTable + prjMTable + accoTable + ethiTable + hsesTable + qualTable)
print("Creation Time for ALL tabs was ", time2-time1, "seconds.")
#remove 'Sheet' worksheet, that gets created by default
target.remove_sheet(target.get_sheet_by_name("Sheet")) #the .remove_sheet() function seems to REQUIRE a worksheet object, not just a name
#Make headers bold; format will probably be the later home of functions for number & alignment formatting
import format
format.bold_headers_footers(target)
##Writing that worksheet to a file
##Moved later in file, so Exceptions sheet could be written
end = time.time()
dur = end - start
print("Total processing time", dur)
def funcSheets_check_figures(d, ft):
'''
def render_xlsx(self, outfd, data):
wb = Workbook(optimized_write = True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append(["Offset (P)",
"Name",
"PID",
"pslist",
"psscan",
"thrdproc",
"pspcid",
"csrss",
"session",
"deskthrd",
"Exit Time"])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in ["system", "smss.exe", "csrss.exe"]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([hex(offset),
str(process.ImageFileName),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')])
total += 1
wb.save(filename = self._config.OUTPUT_FILE)
wb = load_workbook(filename = self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name = "Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style.font.bold = True
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col), row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col), row)).style.fill.fill_type = Fill.FILL_SOLID
ws.cell("{0}{1}".format(get_column_letter(col), row)).style.fill.start_color.index = "FFFF0000"
else:
ws.cell("{0}{1}".format(get_column_letter(col), row)).style.fill.fill_type = Fill.FILL_SOLID
ws.cell("{0}{1}".format(get_column_letter(col), row)).style.fill.start_color.index = "FF00FF00"
wb.save(filename = self._config.OUTPUT_FILE)
def write_excel(excel_name, result_dicts):
from openpyxl.workbook import Workbook
#ExcelWriter,里面封装好了对Excel的写操作
from openpyxl.writer.excel import ExcelWriter
#get_column_letter函数将数字转换为相应的字母，如1-->A,2-->B
from openpyxl.cell import get_column_letter
from openpyxl.reader.excel import load_workbook
if os.path.isfile(excel_name):
# #读取excel2007文件
wb = load_workbook(excel_name)
else:
#新建一个workbook
wb = Workbook()
#新建一个excelWriter
ew = ExcelWriter(workbook=wb)
#设置文件输出路径与名称
dest_filename = excel_name
# # 获取第一个sheet
try:
ws = wb.get_sheet_by_name('sheet1')
except KeyError:
ws = wb.worksheets[0]
ws.title = "sheet1"
#第一个sheet是ws
# ws = wb.worksheets[0]
# #设置ws的名称
# ws.title = "sheet1"
line = 1
print(u'定位写入坐标')
while ws.cell("A%s" % line).value:
# print(ws.cell("A%s" % line).value)
line += 1
print(u'从第%s行开始写入' % line)
if not os.path.isfile(excel_name):
ws.cell("A%s" % line).value = u'期数'
ws.cell("B%s" % line).value = u'定向计划提取'
ws.cell("C%s" % line).value = u'大写'
ws.cell("D%s" % line).value = u'到期时间'
ws.cell("E%s" % line).value = u'交付金额'
line += 1
for i, result in enumerate(result_dicts):
print(u'正在写入第%s条数据到excel' % (i + 1))
ws.cell("A%s" % line).value = result['stage']
ws.cell("B%s" % line).value = result['money']
ws.cell("C%s" % line).value = ''
ws.cell("D%s" % line).value = result['date']
ws.cell("E%s" % line).value = result['money']
line += 1
#最后保存文件
ew.save(filename=excel_name)
class XlWorkBook:
def __init__(self):
self.wsByName = {}
self.wsByPyWs = {}
self.wsByXlWs = {}
self.wb = Workbook()
pyws = self.wb.active
name = pyws.title
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
self.activeSheet = name
#--------------------------------------------------------------------
def Read(self,filename):
self.wsByName = {}
self.wsByPyWs = {}
self.wsByXlWs = {}
self.wb = load_workbook(filename)
for item in self.wb.worksheets:
pyws = item
name = item.title
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
self.activeSheet = self.wb.active.title
#--------------------------------------------------------------------
def CreateXlWorkSheet(self,name):
if (name not in self.wsByName):
self.wb.create_sheet(name)
pyws = self.wb.get_sheet_by_name(name)
xlws = XlWorkSheet(self.wb,pyws,name)
self.wsByName[name] = (pyws,xlws)
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
return self.wsByName[name][1]
else:
logging.error('Attempting to create duplicate name in workbook: ' + name)
return None
#--------------------------------------------------------------------
def CreateXlChrtSheet(self,name):
if (name not in self.wsByName):
pycs = self.wb.create_chartsheet(name)
#pycs = self.wb.get_sheet_by_name(name)
xlcs = XlChrtSheet(self.wb,pycs,name)
self.wsByName[name] = (pycs,xlcs)
#self.wsByPyWs[pycs] = (xlcs,name)
self.wsByXlWs[xlcs] = (pycs,name)
return self.wsByName[name][1]
else:
logging.debug.error('Attempting to create duplicate name in workbook: ' + name)
return None
#--------------------------------------------------------------------
# def CreateSheet(self,name):
# if (name not in self.wsByName):
# self.wb.create_sheet(name)
# pyws = self.wb.get_sheet_by_name(name)
# xlws = XlWorkSheet(self.wb,pyws,name)
#
# self.wsByName[name] = (pyws,xlws)
# self.wsByPyWs[pyws] = (xlws,name)
# self.wsByXlWs[xlws] = (pyws,name)
#
# return self.wsByName[name][1]
#
# else:
# debug.error('Attempting to create duplicate name in workbook: ' + name)
# return None
#--------------------------------------------------------------------
def GetActiveSheet(self):
pyws = self.wb.active
name = pyws.title
if (name in self.wsByName):
if (name == self.activeSheet):
return self.wsByName[name][1]
else:
logging.debug('XlWorkBook data corrupt')
else:
logging.debug('XlWorkBook data corrupt')
#--------------------------------------------------------------------
def SetName(self,ws,name):
if (name not in self.wsByName):
xlws = ws
pyws,orig = self.wsByXlWs[xlws]
pyws.title = name
xlws.name = name
self.wsByPyWs[pyws] = (xlws,name)
self.wsByXlWs[xlws] = (pyws,name)
if (self.activeSheet == orig):
self.activeSheet = name
del self.wsByName[orig]
self.wsByName[name] = (pyws,xlws)
else:
debug.error('Attempting to create duplicate name in workbook: ' + name)
return self.wsByName[name][1]
#--------------------------------------------------------------------
def GetSheetByName(self,name):
return self.wsByName[name][1]
#--------------------------------------------------------------------
def RemoveSheetByName(self,name):
self.wb.remove_sheet(self.wb.get_sheet_by_name(name))
#--------------------------------------------------------------------
def Save(self,name):
self.wb.save(name)
# 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
#htmlSource = ''
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"))
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")
line = [
item, gistuff["country_code"], gistuff["country_name"], bl[0],
bl[1], bl[2], robtex_url
]
if excelfile == None:
print ",".join(x for x in line)
else:
ws.append(line)
total += 1
if excelfile:
wb.save(filename=excelfile)
if not color:
return
wb = load_workbook(filename=excelfile)
ws = wb.get_sheet_by_name(name="IP Address Info")
for col in xrange(1, len(header) + 1):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(1, len(header) + 1):
if ws.cell("{0}{1}".format(get_column_letter(4),
row)).value == "Yes":
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = GreenStyle
wb.save(filename=excelfile)
class ExcelWorkbook(object):
def __init__(self):
self.__thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
self.__greyTheme = Style(border=self.__thin_border,
fill=PatternFill(fill_type='solid',
start_color='FFD8D8D8'))
self.__whiteTheme = Style(border=self.__thin_border,
fill=PatternFill(fill_type='solid',
start_color='FFFFFFFF'))
self._repoToColumnTuples = [('B', 'chromium'),
('C', 'blink'),
('D', 'trace-viewer'),
('E', 'skia'),
('F', 'v8'),
('G', 'Total')]
self._workbook = Workbook()
self._author_col_width = helper.GetAuthorColumnWidth()
self._data_col_width = 0
self._header_row_height = 30
self._author_data = None
self._current_sheet = None
def column_number_to_letter(self, col):
return get_column_letter(col)
def GenerateCompleteContributionsReport(self):
self._ReadContributionsDataFromJson()
self._GenerateTotalContributionSheet()
self._GenerateYearlyContributionSheet()
self._GenerateWeeklyContributionSheet(str(date.today().year))
self._workbook.save('weeklyReport.xlsx')
def _ReadContributionsDataFromJson(self):
with open('weeklyReport.json') as jsonFile:
self._author_data = json.loads(jsonFile.read())
def _NextSheetIndex(self):
return self._workbook.get_index(self._workbook.get_sheet_by_name('Sheet'))
def _GenerateTotalContributionSheet(self):
self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(), title='Total')
self._PopulateContributions('total')
self._CalculateContributionsSummation()
self._BeautifyWorksheet()
def _GenerateYearlyContributionSheet(self):
for year in helper.GetContributionsReportingYearRange():
self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(),
title=str(year) + ' Contributions')
self._PopulateContributions('total', str(year))
self._CalculateContributionsSummation()
self._BeautifyWorksheet()
def _GenerateWeeklyContributionSheet(self, year):
for repo in repositories:
repoName = repo['name']
self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(),
title='%s %s Weekly' % (year, repoName))
for week in xrange(52, 0, -1):
(begin, end) = helper.GetDatesFromWeekNumber(int(year), week)
key = 'W%02d %s to %s' % (week, begin, end)
self._PopulateContributions(key, year, repoName)
self._CalculateContributionsSummation()
self._BeautifyWorksheet()
def _GenerateWeeklyClosedContributionsSheet(self, year):
# Patches which got closed during the week.
self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(),
title='Weekly closed contributions')
self._PopulateWeeklyClosedContributionsDetails()
self._BeautifyWorksheet()
def _GenerateWeeklyOpenContributionsSheet(self, year):
# Patches which are under review during the week.
self._current_sheet = self._workbook.create_sheet(index=self._NextSheetIndex(),
title='Weekly open contributions')
self._PopulateWeeklyOpenContributionsDetails()
self._BeautifyWorksheet()
def _PopulateWeeklyClosedContributionsSheet(self):
currentWeek = helper.GetCurrentWeek()
key = currentWeek[0] + ' to ' + currentWeek[1]
self._current_sheet['A1'] = 'Name'
# self._current_sheet['A2']
def _PopulateContributions(self, key, year=None, repo=None):
codeReviewSearchURL = """https://codereview.chromium.org/search?closed=1&owner=%s&reviewer=&cc=&repo_guid=&base=&project=&private=1&commit=1&created_before=&created_after=&modified_before=&modified_after=&order=&format=html&keys_only=False&with_messages=False&cursor=&limit=30"""
self._CreateSheetHeaderRow(key)
for i in xrange(len(self._author_data)):
author = self._author_data[i]
index = str(i + 2)
self._current_sheet['A' + index] = author['name']
# TODO: Correct the hyperlink as per sheet being filled.
if type(author['email']) == list:
self._current_sheet['A' + index].hyperlink = codeReviewSearchURL % author['email'][0]
else:
self._current_sheet['A' + index].hyperlink = codeReviewSearchURL % author['email']
if 'contributions' not in author:
continue
contributions = author['contributions']
if key.startswith('W'):
self._PopulateWeeklyContributions(contributions, index, key, year, repo)
else:
self._PopulateYearlyContributions(contributions, index, key, year)
def _PopulateWeeklyContributions(self, contributions, rowIndex, key, year, repo):
assert(year is not None and repo is not None)
column = self.column_number_to_letter(self._current_sheet.get_highest_column())
if repo in contributions:
if year in contributions[repo] and key in contributions[repo][year]:
self._current_sheet[column + rowIndex] = contributions[repo][year][key]
else:
self._current_sheet[column + rowIndex] = 0
else:
self._current_sheet[column + rowIndex] = 0
def _PopulateYearlyContributions(self, contributions, rowIndex, key, year=None):
for (col, repo) in self._repoToColumnTuples:
if repo in contributions:
if year:
if year in contributions[repo]:
self._current_sheet[col + rowIndex] = contributions[repo][year][key]
else:
self._current_sheet[col + rowIndex] = 0
else:
if key in contributions[repo]:
self._current_sheet[col + rowIndex] = contributions[repo][key]
else:
self._current_sheet[col + rowIndex] = 0
else:
self._current_sheet[col + rowIndex] = 0
self._current_sheet['G' + rowIndex] = '=sum(b%s:f%s)' % (rowIndex, rowIndex)
def _CalculateContributionsSummation(self):
totalAuthors = len(self._author_data)
finalRowIndex = str(totalAuthors + 2)
dataRange = '(%s2:%s' + str(totalAuthors + 1) + ')'
self._current_sheet['A' + finalRowIndex] = 'Total'
for col in xrange(2, self._current_sheet.get_highest_column() + 1):
column = self.column_number_to_letter(col)
self._current_sheet[column + finalRowIndex] = '=sum' + (dataRange % (column, column))
def _BeautifyWorksheet(self):
sheet = self._current_sheet
sheet.column_dimensions['A'].width = self._author_col_width
sheet.row_dimensions[1].height = self._header_row_height
sheet.row_dimensions[sheet.get_highest_row()].height = self._header_row_height
lastRow = str(sheet.get_highest_row())
header = [ 'A1' ]
footer = [ 'A' + lastRow ]
for col in xrange(2, sheet.get_highest_column() + 1):
column = self.column_number_to_letter(col)
sheet.column_dimensions[column].width = self._data_col_width
header.append(column + '1')
footer.append(column + lastRow)
for col in header:
sheet[col].style = Style(alignment=Alignment(vertical='bottom', horizontal='center'),
border=self.__thin_border,
fill=PatternFill(fill_type='solid', start_color='FFFFFF99'),
font=Font(bold=True))
for col in footer:
sheet[col].style = Style(alignment=Alignment(vertical='bottom', horizontal='center'),
border=self.__thin_border,
fill=PatternFill(fill_type='solid', start_color='FFFFFF99'),
font=Font(bold=True))
for col in xrange(1, sheet.get_highest_column() + 1):
for row in xrange(2, sheet.get_highest_row() - 1):
column = self.column_number_to_letter(col)
cell = sheet[str(column + str(row))]
if (row % 2) == 0:
cell.style = self.__greyTheme
else:
cell.style = self.__whiteTheme
def _CreateSheetHeaderRow(self, key):
subkey = key.split(' ', 1)
self._current_sheet['A1'] = 'Name'
if subkey[0].startswith('W'):
column = self.column_number_to_letter(self._current_sheet.get_highest_column() + 1) + '1'
self._current_sheet[column] = subkey[0]
self._current_sheet[column].comment = Comment(subkey[1], 'OSS')
# TODO: Better way to determine width of weekly column?
self._data_col_width = 5
else:
for (col, repo) in self._repoToColumnTuples:
self._current_sheet[col + '1'] = repo.title()
self._data_col_width = max(self._data_col_width, len(repo) + 1)
def render_xlsx(self, outfd, data):
BoldStyle = Style(font=Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFFFF'),
fill=PatternFill(fill_type="solid",
start_color='FF000000',
end_color='FF000000'))
RedStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
border=Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'),
diagonal=Side(border_style="thick",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thick",
color='FF000000'),
vertical=Side(border_style="thick",
color='FF000000'),
horizontal=Side(border_style="thick",
color='FF000000')),
fill=PatternFill(start_color = 'FFFF0000',
end_color = 'FFFF0000',
fill_type = 'solid'))
GreenStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
fill=PatternFill(start_color = "FF00FF00",
end_color = "FF00FF00",
fill_type = "solid"))
wb = Workbook(optimized_write = True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append(["Offset (P)",
"Name",
"PID",
"pslist",
"psscan",
"thrdproc",
"pspcid",
"csrss",
"session",
"deskthrd",
"Exit Time"])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in ["system", "smss.exe", "csrss.exe"]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in ["system", "smss.exe"]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([hex(offset),
str(utils.remove_unprintable(str(process.ImageFileName)) or ""),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')])
total += 1
wb.save(filename = self._config.OUTPUT_FILE)
wb = load_workbook(filename = self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name = "Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col), 1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col), row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col), row)).style = GreenStyle
wb.save(filename = self._config.OUTPUT_FILE)
