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