def _get_cell_data(self, name, value, req, context, writer): if name == 'id': url = self.env.abs_href.ticket(value) value = '#%d' % value width = len(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, 'id', width, 1 if isinstance(value, datetime): return value, '[datetime]', None, None if value and name in ('reporter', 'owner'): value = Chrome(self.env).format_author(req, value) return value, name, None, None if name == 'cc': value = Chrome(self.env).format_emails(context, value) return value, name, None, None if name == 'milestone': url = self.env.abs_href.milestone(value) width, line = writer.get_metrics(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, name, width, line return value, name, None, None
def addTopStudents(self, wb, data): sheet = wb.get_sheet(SHEET_TOP) start_row = 6 # compute best TOTAL ITEMS base = 50.0 # round to nearest 50 items = int(base * math.ceil(float(data[0]["total"]) / base)) self.setHeaders(sheet, "AIEX TOP TEN PERFORMERS") self.setSubheaders(sheet) for rcnt, row in enumerate(data, start_row): formula = Formula("M%s/N%s" % (rcnt + 1, rcnt + 1)) srow = sheet.row(rcnt) srow.write(0, rcnt - start_row + 1, STYLE_DEFAULT) srow.write(1, row["name"], STYLE_DEFAULT) srow.write(2, row["student_num"], STYLE_DEFAULT) srow.write(3, row["bsa_code"], STYLE_DEFAULT) srow.write(4, row["part1"], STYLE_DEFAULT) srow.write(5, row["part2"], STYLE_DEFAULT) srow.write(6, row["part3"], STYLE_DEFAULT) srow.write(7, row["part4"], STYLE_DEFAULT) srow.write(8, row["part5"], STYLE_DEFAULT) srow.write(9, row["part6"], STYLE_DEFAULT) srow.write(10, row["part7"], STYLE_DEFAULT) srow.write(11, row["part8"], STYLE_DEFAULT) srow.write(12, row["part9"], STYLE_DEFAULT) srow.write(13, row["total"], STYLE_DEFAULT) srow.write(14, items, STYLE_DEFAULT) srow.write(15, formula, STYLE_DECIMAL) srow.write(16, 1, STYLE_PERFECT) srow.write(17, formula, STYLE_PERCENTAGE)
def main(logfiles,dir_path): if len(logfiles) != 0: book = Workbook('utf-8') # crate the cover sheet sheet0 = book.add_sheet('Cover', cell_overwrite_ok=True) row0 = sheet0.row(0) row0.write(0, "ID") row0.write(1, "FILE") row0.write(2, "SHEET") tall_style = Style.easyxf('font:height 72;') row0.set_style(tall_style) sheet0.col(1).width = 256 * 50 for item in logfiles: print "processed file:" + os.path.split(item)[1] new_row = sheet0.row(logfiles.index(item) + 1) new_row.write(0, logfiles.index(item) + 1) click = '#sheet%s!A1' % str(logfiles.index(item)) text = os.path.split(item)[1] style = Style.easyxf('font: underline single,color blue;') new_row.write(1, Formula('HYPERLINK("%s";"%s")' % (click, text)), style) new_row.write(2, 'sheet' + str(logfiles.index(item))) # creat other sheet out_putlist = get_formatted_in_a_file(item) Write_To_excel(book, 'sheet' + str(logfiles.index(item)), out_putlist) save_path = os.path.join(dir_path, 'Aresult.xls') book.save(save_path) else: print "the dirtory doesn't contains any log file"
def write_excle(self, excel_table): self.xls = Workbook(encoding='utf-8') self.sheet = self.xls.add_sheet("Sheet") self.excel_table = excel_table for row in range(0, len(excel_table)): self.sheet.row(row).height = self.row_height for col in range(0, len(excel_table[row])): cell = excel_table[row][col] if cell.type == EXCEL_DATA_TYPE_STR: self.sheet.write_merge(row, row + cell.merge_row, col, col + cell.merge_col, cell.data, cell.style) print cell.data if str_len(cell.data) > 11: min_width = self.col_width * (str_len(cell.data) / 12 + 1) self.sheet.col(col).width = ( self.sheet.col(col).width if self.sheet.col(col).width > min_width else min_width) if cell.type == EXCEL_DATA_TYPE_NUM: self.sheet.write_merge(row, row + cell.merge_row, col, col + cell.merge_col, cell.data, cell.style) if cell.type == EXCEL_DATA_TYPE_FORMULA: self.sheet.write_merge(row, row + cell.merge_row, col, col + cell.merge_col, Formula(cell.data), cell.style) return self.xls
def addPlayer(self, sheet, player, index): indexClmn_l = 0 style = easyxf('font: underline single') # for key in player.keys() : for key in DPDataFormat.keyListPlayer: if isnumber(player[key]): try: if player[key].find(".") != -1: value_l = float(player[key]) else: value_l = player[key].replace("\n", " ").replace(" ", " ") sheet.write(index, indexClmn_l, value_l) except: value_l = player[key] sheet.write(index, indexClmn_l, value_l) else: if key == "html": link = 'HYPERLINK("%s";"%s")' % (player[key], player['Name']) value_l = Formula(link) sheet.write(index, indexClmn_l, value_l, style) else: value_l = filter(onlyascii, player[key]) sheet.write(index, indexClmn_l, value_l) indexClmn_l += 1
def main(repo, developers): basic_header = ['URL', 'Number', 'Title'] workbook = Workbook() sheets = [] for developer in developers: sheet_name = "{0} Estimates".format(developer) sheet = workbook.add_sheet(sheet_name) sheets.append(SheetInfo(sheet_name, developer, sheet)) consensus_sheet_name = "Consensus" consensus_sheet_info = SheetInfo(consensus_sheet_name, None, workbook.add_sheet(consensus_sheet_name)) sheets.append(consensus_sheet_info) for sheet_info in sheets: sheet = sheet_info.sheet for column_index, column_header in enumerate(basic_header): sheet.write(0, column_index, column_header) if sheet_info.developer: sheet.write(0, len(basic_header), "{0} Estimate".format(sheet_info.developer)) sheet.write(0, len(basic_header) + 1, "{0} Notes".format(sheet_info.developer)) else: # If it's the consensus sheet, then add two columns for # each developer. column_index = len(basic_header) for developer in developers: sheet.write(0, column_index, "{0} Estimate".format(developer)) sheet.write(0, column_index + 1, "{0} Notes".format(developer)) column_index += 2 sheet.write(0, column_index, "Consensus") for i, issue in enumerate(get_unestimated_open_issues(repo)): row_index = i + 1 for sheet_info in sheets: for column_index in range(len(basic_header)): sheet_info.sheet.write(row_index, column_index, issue[column_index]) # In the Consensus sheet add a reference to each of the # developer sheets' estimate and notes columns. for i, sheet_info in enumerate(s for s in sheets if s.developer): fmt = "'{sheet}'!{column}{row}" for original_column_index in (len(basic_header) + i for i in range(2)): formula_text = fmt.format( sheet=sheet_info.name, column=column_number_to_letters(original_column_index + 1), row=row_index + 1) consensus_sheet_info.sheet.write(row_index, original_column_index + 2 * i, Formula(formula_text)) workbook.save('estimates.xls')
def create_remesas_info_xls(remesa_emitida, destinatarios): importes = remesa_emitida.politica.array_cuotas ctrlsum, no_cobrado, nboftxs = 0, 0, 0 # ctrlsum: Cantidad total, nboftxs: Número total de remesas ruta = MEDIA_CONTABILIDAD + str(destinatarios[0]['ge'].ronda.entidad.code) + '/' if not os.path.exists(ruta): os.makedirs(ruta) fichero_xls = '%s.xls' % (remesa_emitida.grupo) wb = xlwt.Workbook() wr = wb.add_sheet('Remesas') wa = wb.add_sheet('Avisos') fila_excel_remesas = 0 fila_excel_avisos = 0 estilo = xlwt.XFStyle() font = xlwt.Font() font.bold = True estilo.font = font wr.write(fila_excel_remesas, 0, 'Destinatario cobro (dbtrnm)', style=estilo) wr.write(fila_excel_remesas, 1, 'Concepto (rmtinf)', style=estilo) wr.write(fila_excel_remesas, 2, 'Cuenta bancaria (dbtriban)', style=estilo) wr.write(fila_excel_remesas, 3, 'Couta (instdamt)', style=estilo) for destinatario in destinatarios: importe = sum(importes[:destinatario['num']]) try: dbtrnm = destinatario['oa'].gauser.get_full_name()[:69] rmtinf = '%s - %s (%s) - %s' % (remesa_emitida.politica.concepto, remesa_emitida.politica.get_tipo_cobro_display(), datetime.today().strftime('%d-%m-%Y'), destinatario['texto']) dbtriban = destinatario['oa'].debtor_account instdamt = sum(importes[:destinatario['num']]) fila_excel_remesas += 1 wr.write(fila_excel_remesas, 0, dbtrnm) wr.write(fila_excel_remesas, 1, rmtinf) wr.write(fila_excel_remesas, 2, dbtriban) wr.write(fila_excel_remesas, 3, instdamt) ctrlsum += importe nboftxs += 1 except Exception as e: aviso1 = 'Error al crear la remesa para %s' % (destinatario['ge'].gauser.get_full_name()) existe_oa = 'Sí' if destinatario['oa'] else 'No' aviso2 = '%s tiene una orden de adeudo directa firmada' % (existe_oa) fila_excel_avisos += 1 wa.write(fila_excel_avisos, 0, aviso1) wa.write(fila_excel_avisos, 5, aviso2) wa.write(fila_excel_avisos, 10, 'Importe: %s' % (importe)) wa.write(fila_excel_avisos, 15, str(e)) no_cobrado += importe fila_excel_remesas += 1 wr.write(fila_excel_remesas, 3, Formula("SUM(D2:D%s)" % (fila_excel_remesas)), style=estilo) wr.col(0).width = 10000 wr.col(1).width = 15000 wr.col(2).width = 8000 wr.col(3).width = 5000 wb.save(ruta + fichero_xls) return {'ctrlsum': ctrlsum, 'nboftxs': nboftxs}
def create_excel(traceFile): book = Workbook() mySheet = book.add_sheet('salam') mySheet.write(0,1 , "PID") mySheet.write(0,2 , "Arrival Time") mySheet.write(0,3 , "Address") mySheet.write(0,4 , "Size") mySheet.write(0,5 , "Type") with open(traceFile, 'r') as inF: lineIndex = 1 for line in inF: tolenizedLine = [token for token in line.split()] mySheet.write(lineIndex,1,tokenizedLine[0]) mySheet.write(lineIndex,2,tokenizedLine[1]) mySheet.write(lineIndex,3,tokenizedLine[3]) mySheet.write(lineIndex,4,tokenizedLine[4]) mySheet.write(lineIndex,5,tokenizedLine[5]) lineIndex+=1 book.save(traceFile + '.xls') book.save(TemporaryFile()) xf = glob.glob("*.xls")[0] finalBook = Workbook() finalSheet = finalBook.add_sheet('FINAL') finalSheet.write(0,1 , "PID") finalSheet.write(0,2 , "Arrival Time") finalSheet.write(0,3 , "Address") finalSheet.write(0,4 , "Size") finalSheet.write(0,5 , "Type") finalSheet.write(0,6 , "Interval") curBook = open_workbook(xf) curSheet = curBook.sheet_by_index(0) pids = curSheet.col_values(1,1) arrivalTimes = curSheet.col_values(2,1) addresses = curSheet.col_values(3,1) sizes = curSheet.col_values(4,1) types = sizes = curSheet.col_values(5,1) for i in range(len(pids)): curSheet.write(i+1,1,pids[i]) curSheet.write(i+1,2,arrivalTimes[i]) curSheet.write(i+1,3,addresses[i]) curSheet.write(i+1,4,sizes[i]) curSheet.write(i+1,5,types[i]) for i in range(len(opsVals)): opsSheet.write( i+1 ,6, Formula('SUB(C'+str(i+2)+':C'+str(i+1)+')' )) finalBook.save('FINAL_BLKTRACE_EXCEL.xls') finalBook.save(TemporaryFile()) for xf in xlFiles: os.remove(xf)
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None): ws_sit.row(str_row).height_mismatch = 1 ws_sit.row(str_row).height = height cont_list = lines[csvlineno].split(',') ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head) for (i, content) in enumerate(cont_list[1:]): if content == 'Non': cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1) ws_sit.write(str_row, i + cross_col_no + 1, Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style) else: ws_sit.write(str_row, i + cross_col_no + 1, content, style)
def reload_report(workbook, ibm, nc_sp, pvs_sp, fm_sp, gl): # IBMData sheet sheet = workbook.get_sheet(0) # Define cell styles data_xf = easyxf('border: left thin, right thin, top thin, bottom thin;') pad2 = copy(data_xf) pad2.num_format_str = '00' pad3 = copy(data_xf) pad3.num_format_str = '000' pad4 = copy(data_xf) pad4.num_format_str = '0000' # Download hyperlink: sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_RELOAD_URI))) # Insert data: for row, data in enumerate(ibm, 3): sheet.write(row, 0, int(data.costCentre), pad3) sheet.write(row, 1, data.account, pad2) sheet.write(row, 2, data.service, pad2) sheet.write(row, 3, data.activity, pad3) try: sheet.write(row, 4, int(data.project), pad4) except ValueError: sheet.write(row, 4, data.project, pad4) try: sheet.write(row, 5, int(data.job), pad3) except ValueError: sheet.write(row, 5, data.job, pad3) sheet.write(row, 6, data.budgetArea, data_xf) sheet.write(row, 7, data.projectSponsor, data_xf) sheet.write(row, 8, data.regionalSpecificInfo, data_xf) sheet.write(row, 9, data.servicePriorityID, data_xf) sheet.write(row, 10, data.annualWPInfo, data_xf) # Make some columns wider sheet.col(6).width = 5000 sheet.col(7).width = 5000 sheet.col(8).width = 5000 sheet.col(9).width = 5000 sheet.col(10).width = 30000 # Sheet 2 - Service priority checkboxes. sheet = workbook.get_sheet(1) write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp) # Sheet 3 - GL Codes sheet sheet = workbook.get_sheet(2) for row, data in enumerate(gl): sheet.write(row, 0, data.gLCode) sheet.write(row, 1, data.shortCode) sheet.write(row, 2, data.shortCodeName) sheet.col(0).width = 7500 sheet.col(2).width = 12500
def hyperlink(self, row, col, title, link, style=None): import urlparse style = style or 'font: underline single,color 4' style = self.style(style=style) r = urlparse.urlparse(link) if not r.scheme and self.domain: link = self.domain + link title = title.replace('"', '') self.sh.write(row, col, Formula('HYPERLINK("%s";"%s")' % (link, title)), style)
def write_total(ws, row, first_row): row += 1 last_row = row last_column = 1 + 13 * 1 for column in range(2, last_column + 1): ws.write( row, column, Formula("SUM({column}{start}:{column}{end})".format( column=COLUMN_NAMES[column], start=first_row + 1, end=last_row)), Style.currency_bold)
def generate_results(self): self.results = self.wb.add_sheet(u'Totais') self._build_header(self.results, (u'Pessoa', u'Responsabilidade', u'Despesas pagas', u'Interpagamentos feitos', u'Interpagamentos recebidos', u'Total')) for person in Person.objects.all(): self._write_line(self.results, (person.name, Formula('Sumif(Responsabilidades!E2:E%d, Totais!A%d, Responsabilidades!F2:F%d)' % (self.responsibilities.line, self.results.line+1, self.responsibilities.line), ), Formula('Sumif(Despesas!E2:E%d, Totais!A%d, Despesas!F2:F%d)' % (self.expenses.line, self.results.line+1, self.expenses.line), ), Formula('Sumif(Interpagamentos!B2:B%d, Totais!A%d, Interpagamentos!D2:D%d)' % (self.interpayments.line, self.results.line+1, self.interpayments.line), ), Formula('Sumif(Interpagamentos!C2:C%d, Totais!A%d, Interpagamentos!D2:D%d)' % (self.interpayments.line, self.results.line+1, self.interpayments.line), ), Formula('-B%d + C%d + D%d - E%d' % ((self.results.line+1,)*4)), ) )
def _get_cell_data(self, req, col, cell, row, writer): value = cell['value'] if col == 'report': url = self.env.abs_href.report(value) width, line = writer.get_metrics(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, col, width, line if col in ('ticket', 'id'): value = '#%s' % cell['value'] url = get_resource_url(self.env, row['resource'], self.env.abs_href) width = len(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, 'id', width, 1 if col == 'milestone': url = self.env.abs_href.milestone(value) width, line = writer.get_metrics(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, col, width, line if col == 'time': if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[time]', None, None elif col in ('date', 'created', 'modified'): if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[date]', None, None elif col == 'datetime': if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[datetime]', None, None width, line = writer.get_metrics(value) return value, col, width, line
def writeExcel(self): f = xlwt.Workbook(encoding='gbk') sheet1 = f.add_sheet('Template', cell_overwrite_ok=True) style1 = xlwt.XFStyle() row = 0 col = 0 sheet1.write(row, col, self.titleExcel) row += 1 sheet1.write(row, col, self.tips) row += 2 sheet1.write(row, col, self.title) i = 1 for t in self.title: if i <= 8: sheet1.write(row, col, t) col += 1 else: sheet1.write(row, col, Formula("A1*B1")) if not os.path.exists(self.arg + '/Template.xls'): f.save(self.arg + '/Template.xls') else: os.remove(self.arg + '/Template.xls') f.save(self.arg + '/Template.xls')
def write_total_invoice_paid(ws, row, first_row): row += 1 last_row = row column = 2 last_column = 1 + 13 * 5 for i in range(1, last_column): if column % 2 == 0: border_currency = Style.last_col_currency_border_left else: border_currency = Style.currency_bold if i == last_column: border_currency = Style.last_col_currency_border_bold ws.write( row, column, Formula("SUM({column}{start}:{column}{end})".format( column=COLUMN_NAMES[column], start=first_row + 1, end=last_row)), border_currency) column += 1
def _get_cell_data(self, req, col, cell, row, writer): value = cell['value'] if col == 'report': url = self.env.abs_href.report(value) width, line = writer.get_metrics(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, col, width, line if col in ('ticket', 'id'): value = '#%s' % cell['value'] url = get_resource_url(self.env, row['resource'], self.env.abs_href) width = len(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, 'id', width, 1 if col == 'milestone': url = self.env.abs_href.milestone(value) width, line = writer.get_metrics(value) value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value))) return value, col, width, line if col == 'time': if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[time]', None, None elif col in ('date', 'created', 'modified'): if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[date]', None, None elif col == 'datetime': if isinstance(value, basestring) and value.isdigit(): value = from_utimestamp(long(value)) return value, '[datetime]', None, None width, line = writer.get_metrics(value) return value, col, width, line
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm): """This report reads from the readonly workbook in order to perform some cell processing. """ # Sheet 1 sheet = workbook.get_sheet(0) sheet_ro = workbook_ro.get_sheet(0) # Download hyperlink: bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point) url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI)) sheet.write(1, 0, url, bigfont) # Padded zeroes number format pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle() pad2.num_format_str = '00' pad3.num_format_str = '000' pad4.num_format_str = '0000' # For each of the GL code IDs, take a subset of the query # and insert values as required. row = 4 max_col_idx = 21 # Start at column V. for codeID in gl_codeids: gl_pivs = gl.filter(codeID=codeID) g = gl_pivs[0] # Fill the non-resource columns. sheet.write(row, 0, g.codeID) sheet.write(row, 1, int(g.costCentre), pad3) sheet.write(row, 2, g.account, pad2) sheet.write(row, 3, g.service, pad2) sheet.write(row, 4, g.activity, pad3) try: sheet.write(row, 5, int(g.project), pad4) except ValueError: sheet.write(row, 5, g.project, pad4) try: sheet.write(row, 6, int(g.job), pad3) except ValueError: sheet.write(row, 6, g.job, pad3) sheet.write(row, 7, g.jobName) sheet.write(row, 8, g.activityName) sheet.write(row, 9, g.projNameNo) sheet.write(row, 19, g.mPRACategory) # Write the SUM formula. sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1))) # First, find the maximum column index in the template headers (row 4). blank_cell = False while not blank_cell: if not sheet_ro.cell_value(3, max_col_idx): # Blank cell blank_cell = True else: max_col_idx += 1 # Write ytdActual values for matching resource columns. # Find the correct cell index of a matching resource code. # If no match found, use the '0000' column (the first). for gl_piv in gl_pivs: resource_idx = 21 # Column V, '0000' match_resource_code = False for i in range(resource_idx, max_col_idx + 1): if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource: match_resource_code = True break resource_idx += 1 if not match_resource_code: # No match was found. resource_idx = 21 # Insert the ytdActual into column V. # Write the ytdActual to the sheet. sheet.write(row, resource_idx, gl_piv.ytdActual) row += 1 # Advance one row, to the next Code ID. row += 1 # Insert the footer row formulae and '#END OF INPUT' sheet.write(row, 0, '#END OF INPUT') sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1))) for i in range(21, max_col_idx): # For cell V:<end> in the footer row, insert a SUM formula. sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row-1, i)))) # Sheet 2: Service priority checkboxes. sheet = workbook.get_sheet(1) write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp) # Sheet 3: Budget area & project sponsor lookup data. # This is a list of unique budgetArea and projectSponsor values, written in # as reference data for macros. sheet = workbook.get_sheet(2) write_budget_areas(sheet, ibm) write_project_sponsors(sheet, ibm) write_regional_spec_info(sheet, ibm) # Select the first sheet. sheet = workbook.get_sheet(0)
return fileslList except IOError, msg: print msg logfiles = [] logfiles = get_logfile_in_dir( r'c:\arcgisserver\logs\JIANGMB.ESRICHINA.COM\server') book = Workbook('utf-8') #crate the cover sheet sheet0 = book.add_sheet('Cover', cell_overwrite_ok=True) row0 = sheet0.row(0) row0.write(0, "ID") row0.write(1, "FILE") row0.write(2, "SHEET") tall_style = Style.easyxf('font:height 72;') row0.set_style(tall_style) sheet0.col(1).width = 256 * 50 for item in logfiles: new_row = sheet0.row(logfiles.index(item) + 1) new_row.write(0, logfiles.index(item) + 1) click = '#sheet%s!A1' % str(logfiles.index(item)) text = os.path.split(item)[1] style = Style.easyxf('font: underline single,color blue;') new_row.write(1, Formula('HYPERLINK("%s";"%s")' % (click, text)), style) new_row.write(2, 'sheet' + str(logfiles.index(item))) #creat other sheet out_putlist = get_formatted_in_a_file(item) Write_To_excel(book, 'sheet' + str(logfiles.index(item)), out_putlist)
import xlrd from xlwt import Workbook, Formula path = r"D:\Data\Python\export\fichier.xls" classeur = Workbook() # On ajoute une feuille au classeur feuille = classeur.add_sheet("OCB") feuille.write(0, 0, 1) feuille.write(0, 1, 2) feuille.write(0, 2, Formula('A1+B1')) classeur.save(path) print u"Fichier cree: {}".format(path) ''' import glob, csv, xlwt, os wb = xlwt.Workbook() for filename in glob.glob("c:/xxx/*.csv"): (f_path, f_name) = os.path.split(filename) (f_short_name, f_extension) = os.path.splitext(f_name) ws = wb.add_sheet(f_short_name) spamReader = csv.reader(open(filename, 'rb')) for rowx, row in enumerate(spamReader): for colx, value in enumerate(row): ws.write(rowx, colx, value)
def writeResults(C1,C2,C3,R2,R3,f,magCL,magOL,phaseOL,magvcoTF,PFDCPNoiseOut, PrescalerNoiseOut,VCONoiseOut,R2NoiseOut,R3NoiseOut,XTALNoiseOut,SDNoiseOut, TotalNoise,t,fT,lockTime_0p001Pcnt, lockTime_0p0001Pcnt, lockTime_0p00001Pcnt, lockTime_0p000001Pcnt, f2, fInterpol, TotalNoiseV2HzInterpol, enteredKphi, enteredKVCO, enteredPM, enteredLoopBW, enteredFout, enteredFref, enteredR, enteredP, enteredT31, enteredGamma, noiseWorkbook,PFDCPNoise, XTALNoise, PrescalerNoise, VCONoise, SDNoise): book = Workbook() whiteCell = easyxf("pattern: fore_colour white, pattern solid;") parameter = easyxf('font: name Arial, bold True, height 280; alignment: horizontal center') parameterValue = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000E+00') parameterValue2 = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000') parameterValueRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center', num_format_str='0.000E+00') parameterValue2Red = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center', num_format_str='0.000') parameterValue3 = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000000%') columnHeader = easyxf('font: name Arial, bold True, height 280; alignment: horizontal center') notes = easyxf('font: name Arial, bold True, height 280; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;") notesRed = easyxf('font: name Arial, bold True, height 280, colour red; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;") link = easyxf('font: name Arial, bold True, italic True, height 240, underline single, colour red; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;") linkContact = easyxf('font: name Arial, bold True, italic True, height 240, underline single, colour black; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;") columnHeaderBorderL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;') columnHeaderBorderLRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'borders: left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;') columnHeaderBorderBLRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'borders: left thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;') columnHeaderBorderTL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;') columnHeaderBorderBL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;') columnHeaderBorderTLBR = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick, bottom thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;') columnHeaderBorderTLBRAlignleft = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick, bottom thick, right thick;' 'alignment: horizontal left;' 'pattern: pattern solid, fore_colour gray25;') parameterValue2BorderBR = easyxf('font: name Arial, height 280;' 'borders: right thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000') parameterValue2BorderR = easyxf('font: name Arial, height 280;' 'borders: right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000') parameterValueBorderR = easyxf('font: name Arial, height 280;' 'borders: right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00') parameterValueBorderBR = easyxf('font: name Arial, height 280;' 'borders: bottom thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00') parameterValueBorderBRWhite = easyxf('font: name Arial, height 280;' 'borders: bottom thick, right thick, left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000E+00') parameterValueBorderTR = easyxf('font: name Arial, height 280;' 'borders: top thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00') redResult = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center') #Write the Loop Parameters worksheet sheetLoopParam = book.add_sheet('Loop Parameters') for i in range(100): sheetLoopParam.row(i).set_style(whiteCell)#Make everything white first sheetLoopParam.col(0).width = 8000 sheetLoopParam.col(1).width = 5000 sheetLoopParam.insert_bitmap(os.path.abspath("PLL_diagram_Excel.bmp"), 0, 0) sheetLoopParam.write(20,0,'Kphi',columnHeaderBorderTL) sheetLoopParam.write(20,1,enteredKphi,parameterValueBorderTR) sheetLoopParam.write(21,0,'KVCO',columnHeaderBorderL) sheetLoopParam.write(21,1,enteredKVCO,parameterValueBorderR) sheetLoopParam.write(22,0,'Phase Margin',columnHeaderBorderL) sheetLoopParam.write(22,1,enteredPM,parameterValue2BorderR) sheetLoopParam.write(23,0,'Loop Bandwidth',columnHeaderBorderL) sheetLoopParam.write(23,1,enteredLoopBW,parameterValueBorderR) sheetLoopParam.write(24,0,'Fout',columnHeaderBorderL) sheetLoopParam.write(24,1,enteredFout,parameterValueBorderR) sheetLoopParam.write(25,0,'Fref',columnHeaderBorderL) sheetLoopParam.write(25,1,enteredFref,parameterValueBorderR) sheetLoopParam.write(26,0,'R',columnHeaderBorderL) sheetLoopParam.write(26,1,enteredR,parameterValue2BorderR) sheetLoopParam.write(27,0,'P',columnHeaderBorderL) sheetLoopParam.write(27,1,enteredP,parameterValue2BorderR) sheetLoopParam.write(28,0,'T31',columnHeaderBorderL) sheetLoopParam.write(28,1,enteredT31,parameterValue2BorderR) sheetLoopParam.write(29,0,'Gamma',columnHeaderBorderBL) sheetLoopParam.write(29,1,enteredGamma,parameterValue2BorderBR) sheetLoopParam.write(32,0," References:",notes) sheetLoopParam.write(33,0, Formula('HYPERLINK("http://www.ti.com/tool/pll_book";" PLL Performance Simulation and Design Handbook - 4th Edition Dean Banerjee. 2006.")'),link) sheetLoopParam.write(35,0, Formula('HYPERLINK("mailto:[email protected]";" Contact")'),linkContact) #Write the Noise Sources worksheet if noiseWorkbook == "": sheetLoopParam.write(37,0,"***WARNING: Empty noise file or an error occurred while reading the file. Using default noise data instead.***",notesRed) f, XTALNoise, PFDCPNoise, PrescalerNoise, VCONoise, SDNoise = defaultNoise() sheetNoiseSources = book.add_sheet('Noise Sources') for i in range(100): sheetNoiseSources.row(i).set_style(whiteCell)#Make everything white first sheetNoiseSources.col(0).width = 6000 sheetNoiseSources.col(1).width = 8000 sheetNoiseSources.col(2).width = 8000 sheetNoiseSources.col(3).width = 10000 sheetNoiseSources.col(4).width = 8000 sheetNoiseSources.col(5).width = 10000 sheetNoiseSources.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR) sheetNoiseSources.write(0,1,'XTAL Noise (dBc/Hz)',columnHeaderBorderTLBR) sheetNoiseSources.write(0,2,'PFDCP Noise (dBc/Hz)',columnHeaderBorderTLBR) sheetNoiseSources.write(0,3,'Prescaler Noise (dBc/Hz)',columnHeaderBorderTLBR) sheetNoiseSources.write(0,4,'VCO Noise (dBc/Hz)',columnHeaderBorderTLBR) sheetNoiseSources.write(0,5,'Sigma Delta Noise (dBc/Hz)',columnHeaderBorderTLBR) for i in range(len(f)): sheetNoiseSources.write(i+1,0,f[i],parameterValue) sheetNoiseSources.write(i+1,1,XTALNoise[i],parameterValue2) sheetNoiseSources.write(i+1,2,PFDCPNoise[i],parameterValue2) sheetNoiseSources.write(i+1,3,PrescalerNoise[i],parameterValue2) sheetNoiseSources.write(i+1,4,VCONoise[i],parameterValue2) sheetNoiseSources.write(i+1,5,SDNoise[i],parameterValue2) #Write Loop Filter Components worksheet: sheetLoopFilter = book.add_sheet('Loop Filter Components') for i in range(100): sheetLoopFilter.row(i).set_style(whiteCell)#Make everything white first sheetLoopFilter.col(0).width = 4000 sheetLoopFilter.col(1).width = 5000 sheetLoopFilter.write(0,0,' Loop Filter Components',columnHeaderBorderTLBRAlignleft) sheetLoopFilter.write(0,1,None,columnHeaderBorderTLBRAlignleft) sheetLoopFilter.write(1,0,'C1',columnHeaderBorderTL) sheetLoopFilter.write(1,1,C1,parameterValue) sheetLoopFilter.write(2,0,'C2',columnHeaderBorderL) sheetLoopFilter.write(2,1,C2,parameterValue) sheetLoopFilter.write(3,0,'C3',columnHeaderBorderL) sheetLoopFilter.write(3,1,C3,parameterValue) sheetLoopFilter.write(4,0,'R2',columnHeaderBorderL) sheetLoopFilter.write(4,1,R2,parameterValue) sheetLoopFilter.write(5,0,'R3',columnHeaderBorderBL) sheetLoopFilter.write(5,1,R3,parameterValueBorderBRWhite) #Write Loop Response worksheet: sheetLoopResponse = book.add_sheet('Loop Response Data') for i in range(100): sheetLoopResponse.row(i).set_style(whiteCell)#Make everything white first sheetLoopResponse.col(0).width = 6000 sheetLoopResponse.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR) sheetLoopResponse.col(1).width = 15000 sheetLoopResponse.write(0,1,'Closed Loop Response Magnitude (dB)',columnHeaderBorderTLBR) sheetLoopResponse.col(2).width = 14000 sheetLoopResponse.write(0,2,'Open Loop Response Magnitude (dB)',columnHeaderBorderTLBR) sheetLoopResponse.col(3).width = 14000 sheetLoopResponse.write(0,3,'Open Loop Response Phase (dB)',columnHeaderBorderTLBR) sheetLoopResponse.col(4).width = 14000 sheetLoopResponse.write(0,4,'VCO Transfer Function Magnitude (dB)',columnHeaderBorderTLBR) for i in range(len(f)): sheetLoopResponse.write(i+1,0,f[i],parameterValue) sheetLoopResponse.write(i+1,1,magCL[i],parameterValue2) sheetLoopResponse.write(i+1,2,magOL[i],parameterValue2) sheetLoopResponse.write(i+1,3,phaseOL[i],parameterValue2) sheetLoopResponse.write(i+1,4,magvcoTF[i],parameterValue2) #Write Noise Results worksheet: sheetPLLNoise = book.add_sheet('Output Noise Contributors') for i in range(100): sheetPLLNoise.row(i).set_style(whiteCell)#Make everything white first sheetPLLNoise.col(0).width = 6000 sheetPLLNoise.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(1).width = 6000 sheetPLLNoise.write(0,1,'PFDCP (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(2).width = 7000 sheetPLLNoise.write(0,2,'Prescaler (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(3).width = 6000 sheetPLLNoise.write(0,3,'VCO (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(4).width = 6000 sheetPLLNoise.write(0,4,'R2 (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(5).width = 6000 sheetPLLNoise.write(0,5,'R3 (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(6).width = 6000 sheetPLLNoise.write(0,6,'XTAL (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(7).width = 8000 sheetPLLNoise.write(0,7,'Sigma Delta (dBc/Hz)',columnHeaderBorderTLBR) sheetPLLNoise.col(8).width = 8000 sheetPLLNoise.write(0,8,'Total Noise (dBc/Hz)',columnHeaderBorderTLBR) for i in range(len(f)): sheetPLLNoise.write(i+1,0,f[i],parameterValue) sheetPLLNoise.write(i+1,1,PFDCPNoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,2,PrescalerNoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,3,VCONoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,4,R2NoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,5,R3NoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,6,XTALNoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,7,SDNoiseOut[i],parameterValue2) sheetPLLNoise.write(i+1,8,TotalNoise[i],parameterValue2) #Write Time Response worksheet: sheetPLLTime = book.add_sheet('Time Response') for i in range(2050): sheetPLLTime.row(i).set_style(whiteCell)#Make everything white first sheetPLLTime.col(0).width = 5000 sheetPLLTime.write(0,0,'Time (s)',columnHeaderBorderTLBR) sheetPLLTime.col(1).width = 9000 sheetPLLTime.write(0,1,'Output Frequency (Hz)',columnHeaderBorderTLBR) for i in range(len(t)): sheetPLLTime.write(i+1,0,t[i],parameterValue) sheetPLLTime.write(i+1,1,fT[i],parameterValue) #Write lock times sheetLockTimes = book.add_sheet('Lock Times') for i in range(100): sheetLockTimes.row(i).set_style(whiteCell)#Make everything white first sheetLockTimes.col(0).width = 11000 sheetLockTimes.write(0,0,'Locks within what % error',columnHeaderBorderTLBR) sheetLockTimes.col(1).width = 11000 sheetLockTimes.write(0,1,'Locks within how many Hertz',columnHeaderBorderTLBR) sheetLockTimes.col(2).width = 6000 sheetLockTimes.write(0,2,'Lock Time (s)',columnHeaderBorderTLBR) sheetLockTimes.write(1,0,0.00001,parameterValue3) sheetLockTimes.write(1,1,float(scientific(0.00001*f2)),parameterValue) sheetLockTimes.write(1,2,lockTime_0p001Pcnt,parameterValue) sheetLockTimes.write(2,0,0.000001,parameterValue3) sheetLockTimes.write(2,1,float(scientific(0.000001*f2)),parameterValue) sheetLockTimes.write(2,2,lockTime_0p0001Pcnt,parameterValue) sheetLockTimes.write(3,0,0.0000001,parameterValue3) sheetLockTimes.write(3,1,float(scientific(0.0000001*f2)),parameterValue) sheetLockTimes.write(3,2,lockTime_0p00001Pcnt,parameterValue) sheetLockTimes.write(4,0,0.00000001,parameterValue3) sheetLockTimes.write(4,1,float(scientific(0.00000001*f2)),parameterValue) sheetLockTimes.write(4,2,lockTime_0p000001Pcnt,parameterValue) #Phase Error and Jitter worksheets sheetphaseError = book.add_sheet('Phase Error') for i in range(650): sheetphaseError.row(i).set_style(whiteCell)#Make everything white first sheetphaseError.col(0).width = 6000 sheetphaseError.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR) sheetphaseError.col(1).width = 8000 sheetphaseError.write(0,1,'Total Noise (V2/Hz)',columnHeaderBorderTLBR) sheetphaseError.col(2).width = 12500 sheetphaseError.write(0,2,'Frequency Error Integrand (V2*Hz)',columnHeaderBorderTLBR) for i in range(len(fInterpol)): sheetphaseError.write(i+1,0,fInterpol[i],parameterValue) sheetphaseError.write(i+1,1,TotalNoiseV2HzInterpol[i],parameterValue) sheetphaseError.write(i+1,2,fInterpol[i]*fInterpol[i]*TotalNoiseV2HzInterpol[i],parameterValue) sheetphaseError.col(4).width = 12000 sheetphaseError.write(1,4,"Lower Integration Limit (Hz)", columnHeaderBorderTL) sheetphaseError.write(2,4,"Upper Integration Limit (Hz)", columnHeaderBorderBL) sheetphaseError.write(3,4,"RMS Phase Error (degrees)", columnHeaderBorderL) sheetphaseError.write(4,4,"Jitter (s)", columnHeaderBorderL) sheetphaseError.write(5,4,"RMS Frequency Error (Hz)", columnHeaderBorderBL) sheetphaseError.col(5).width = 6000 sheetphaseError.write(1,5,1.7e3,parameterValueBorderTR) sheetphaseError.write(2,5,200e3,parameterValueBorderBR) sheetphaseError.write(7,4,"Enter lower and upper integration limits to calculate RMS Phase Error",notes) sheetphaseError.write(8,4,"Required: (Upper Int. Limit) >= 20*(Lower Int. Limit)",notes) sheetphaseError.write(9,4,"Calculations made using interpolated data",notes) #x="(180/PI())*SQRT(2*((VLOOKUP(E3,A2:B32,1)-VLOOKUP(E2,A2:B32,1))/6)*(VLOOKUP(E2,A2:B32,2)+VLOOKUP(E3,A2:B32,2)+4*VLOOKUP(((VLOOKUP(E2,A2:B32,1)+VLOOKUP(E3,A2:B32,1))/2.0),A2:B32,2)))" freqError="""SQRT( 2*((F3-F3/1.5)/6)*(VLOOKUP(F3,A2:C602,3) + VLOOKUP(F3/1.5,A2:C602,3) + 4*VLOOKUP((F3 + F3/1.5)/2,A2:C602,3)) + 2*((F3/1.5-F3/2.25)/6)*(VLOOKUP(F3/1.5,A2:C602,3) + VLOOKUP(F3/2.25,A2:C602,3) + 4*VLOOKUP((F3/1.5 + F3/2.25)/2,A2:C602,3)) + 2*((F3/2.25-F3/3.375)/6)*(VLOOKUP(F3/2.25,A2:C602,3) + VLOOKUP(F3/3.375,A2:C602,3) + 4*VLOOKUP((F3/2.25 + F3/3.375)/2,A2:C602,3)) + 2*((F3/3.375-F3/5.0625)/6)*(VLOOKUP(F3/3.375,A2:C602,3) + VLOOKUP(F3/5.0625,A2:C602,3) + 4*VLOOKUP((F3/3.375 + F3/5.0625)/2,A2:C602,3)) + 2*((F3/5.0625-F3/7.594)/6)*(VLOOKUP(F3/5.0625,A2:C602,3) + VLOOKUP(F3/7.594,A2:C602,3) + 4*VLOOKUP((F3/5.0625 + F3/7.594)/2,A2:C602,3)) + 2*((F3/7.594-F3/11.39)/6)*(VLOOKUP(F3/7.594,A2:C602,3) + VLOOKUP(F3/11.39,A2:C602,3) + 4*VLOOKUP((F3/7.594 + F3/11.39)/2,A2:C602,3)) + 2*((F3/11.39-F3/17.086)/6)*(VLOOKUP(F3/11.39,A2:C602,3) + VLOOKUP(F3/17.086,A2:C602,3) + 4*VLOOKUP((F3/11.39 + F3/17.086)/2,A2:C602,3)) + 2*((F3/17.086-F2)/6)*(VLOOKUP(F3/17.086,A2:C602,3) + VLOOKUP(F2,A2:C602,3) + 4*VLOOKUP((F3/17.086 + F2)/2,A2:C602,3)) )"""#Take the Simpson integral over several intervals. phaseError="""(180/PI())*SQRT( 2*((F2*1.5-F2)/6)*(VLOOKUP(F2*1.5,A2:C602,2) + VLOOKUP(F2,A2:C602,2) + 4*VLOOKUP((F2*1.5 + F2)/2,A2:C602,2)) + 2*((F2*2.25-F2*1.5)/6)*(VLOOKUP(F2*2.25,A2:C602,2) + VLOOKUP(F2*1.5,A2:C602,2) + 4*VLOOKUP((F2*2.25 + F2*1.5)/2,A2:C602,2)) + 2*((F2*3.375-F2*2.25)/6)*(VLOOKUP(F2*3.375,A2:C602,2) + VLOOKUP(F2*2.25,A2:C602,2) + 4*VLOOKUP((F2*3.375 + F2*2.25)/2,A2:C602,2)) + 2*((F2*5.0625-F2*3.375)/6)*(VLOOKUP(F2*5.0625,A2:C602,2) + VLOOKUP(F2*3.375,A2:C602,2) + 4*VLOOKUP((F2*5.0625 + F2*3.375)/2,A2:C602,2)) + 2*((F2*7.594-F2*5.0625)/6)*(VLOOKUP(F2*7.594,A2:C602,2) + VLOOKUP(F2*5.0625,A2:C602,2) + 4*VLOOKUP((F2*7.594 + F2*5.0625)/2,A2:C602,2)) + 2*((F2*11.39-F2*7.594)/6)*(VLOOKUP(F2*11.39,A2:C602,2) + VLOOKUP(F2*7.594,A2:C602,2) + 4*VLOOKUP((F2*11.39 + F2*7.594)/2,A2:C602,2)) + 2*((F2*17.086-F2*11.39)/6)*(VLOOKUP(F2*17.086,A2:C602,2) + VLOOKUP(F2*11.39,A2:C602,2) + 4*VLOOKUP((F2*17.086 + F2*11.39)/2,A2:C602,2)) + 2*((F3-F2*17.086)/6)*(VLOOKUP(F3,A2:C602,2) + VLOOKUP(F2*17.086,A2:C602,2) + 4*VLOOKUP((F3 + F2*17.086)/2,A2:C602,2)) )"""#Take the Simpson integral over several intervals. jitter="F4/360.0/'Loop Parameters'!B25" y="(180/PI())*SQRT(2*((E3-E2)/6)*(VLOOKUP(E2,A2:B32,2)+VLOOKUP(E3,A2:B32,2)+4*VLOOKUP(((E3+E2)/2.0),A2:B32,2)))" sheetphaseError.write(3,5,Formula(phaseError),parameterValue2BorderR) sheetphaseError.write(4,5,Formula(jitter),parameterValueBorderR) sheetphaseError.write(5,5,Formula(freqError),parameterValue2BorderBR) return book
filepath.write(''' This is my assignment for Lab 9. I know that it is easy to create textfiles in Python I just need to relax and follow the yellow brick road''') filepath.close() # Create the following Excel Spreadsheet using Python. from xlwt import Workbook, Formula, easyxf # xlwt is built in module in python used to work with excelfiles. wb = Workbook() sheet1 = wb.add_sheet('Sheet1') #add_sheet is used to add anew sheet in the excel file. style1 = easyxf('pattern : pattern solid, fore_color yellow;') #easyfx is used to style cells in excel. sheet1.write(0,0,'Column1') # to write the data in format of row, column, value. sheet1.write(0,1,'Column2') sheet1.write(0,2,'Column3') wb.save('xlwt Assignment.xls') # For Column1 values for i in xrange(1,11): sheet1.write(i,0,i) sheet1.write(11,0,Formula('SUM(A2:A11)'),style1) #Formula is used to write fomula in excel. wb.save('xlwt Assignment.xls') # For column2 values for i,j in zip(xrange(1,11),xrange( 100,200,10)): sheet1.write(i,1,j) sheet1.write(11,1,Formula('SUM(B2:B11)'),style1) wb.save('xlwt Assignment.xls') # save is used to save the excel file. # For column3 values for i,j in zip(xrange(1,11),xrange(1500,15001,1500)): sheet1.write(i,2,j) sheet1.write(11,2,Formula('SUM(C2:C11)'),style1) wb.save('xlwt Assignment.xls')
else: col_style = style_num # écriture wc.write(irow+1, icol+1, str(value), col_style) # - bottom for icol in xrange(3): wc.write(irow+2, icol, '', style_bottom) # Deuxième feuille # - nom wn = w.add_sheet('Testouille') # - gros titre wn.write_merge(0, 1, 0, 5, u"Ça c'est un gros titre", xls_style(o=1, s=500)) # - hyperlien uri = "http://relay.vacumm.fr/~raynaud/pydoc" urn = "Python Actimar" wn.write_merge(3,3,1,10,Formula('HYPERLINK("%s";"%s")'%(uri, urn)),xls_style(c=4)) # - groupes de niveaux wn.write(4, 1, 'level1') wn.write(5, 1, 'level2') wn.write(6, 1, 'level2') wn.write(7, 1, 'level1') wn.row(4).level = wn.row(7).level = 1 wn.row(5).level = wn.row(6).level = 2 # - dates from datetime import datetime wn.write(8, 0, 'Dates :') wn.write(8, 1, datetime.now(), xls_style(fmt='h:mm:ss AM/PM')) wn.write(8, 2, datetime.now(), xls_style(fmt='MMM-YY')) # Écriture du document
def work_am(): p = 1 camera = cv2.VideoCapture(0) path = "the_nhan_vien" while True: rb = xlrd.open_workbook('bang1.xlsx') wb = copy(rb) w_sheet = wb.get_sheet(0) sheet = rb.sheet_by_index(0) (grabbed, frame) = camera.read() # doc frame video frame = imutils.resize(frame, width=500) #hinh thi kich thuoc webcam frame_clone = frame.copy() #copy ra 1 ban de xu ly sao nay # detect face faces = detector.detectMultiScale(frame, scaleFactor=1.1, minNeighbors=5, minSize=(30, 30), flags=cv2.CASCADE_SCALE_IMAGE) # Loop over the face bounding boxes for (fX, fY, fW, fH) in faces: roi = frame[fY:fY + fH, fX:fX + fW] # cat khuon mat roi = cv2.cvtColor(roi, cv2.COLOR_BGR2GRAY) # chuyen anh thanh anh xam roi = cv2.resize(roi, (200, 200)) #resize khich thuoc roi = roi.astype("float") / 255.0 # chuan hoa roi = img_to_array(roi) # chuyen thanh mang roi = np.expand_dims( roi, axis=0 ) # them 1 chieu vi dau vao CNN la (None,hight,with,dept) predictions = model.predict(roi) # du doan i = predictions.argmax(axis=1)[0] #lay chi so cao nhat accur = "{:.2f}%".format(predictions[0][i] * 100) #tinh do chinh xac print("do chinh xac: " + accur) result = predictions[0][i] if result > 0.8: # thay doi do chinh xac o day neu predict lon hon 80% thi hien ten nguoc lai hien unknown text = "{}".format(name_class[i]) #lay ten class # Show our detected faces along with smiling/not smiling labels img1 = cv2.imread( os.path.join(path, "{}".format(str(i) + ".png")), cv2.IMREAD_COLOR) winname = "anh_nhan_vien" cv2.namedWindow(winname) # Create a named window cv2.moveWindow(winname, 1000, 70) # Move it to (40,30) cv2.imshow(winname, img1) else: text = "Unknown" i = 10 img1 = cv2.imread( os.path.join(path, "{}".format(str(i) + ".png")), cv2.IMREAD_COLOR) winname = "anh_nhan_vien" cv2.namedWindow(winname) # Create a named window cv2.moveWindow(winname, 1000, 70) # Move it to (40,30) cv2.imshow(winname, img1) #hien thi ten class cv2.putText(frame_clone, text, (fX, fY - 10), cv2.FONT_HERSHEY_SIMPLEX, 0.45, (0, 0, 255), 2) # dong khung mat cv2.rectangle(frame_clone, (fX, fY), (fX + fW, fY + fH), (0, 0, 255), 2) if cv2.waitKey(1) & 0xFF == ord("a"): if i == 0: a = 1 + sheet.cell_value(5, 3) w_sheet.write(5, 3, a) w_sheet.write(5, 5, Formula('IF(D6>=E6,E6,D6)')) w_sheet.write(5, 7, Formula('266000*F6+G6')) elif i == 1: a = 1 + sheet.cell_value(6, 3) w_sheet.write(6, 3, a) w_sheet.write(6, 5, Formula('IF(D7>=E7,E7,D7)')) w_sheet.write(6, 7, Formula('266000*F7+G7')) elif i == 2: a = 1 + sheet.cell_value(7, 3) w_sheet.write(7, 3, a) w_sheet.write(7, 5, Formula('IF(D8>=E8,E8,D8)')) w_sheet.write(7, 7, Formula('266000*F8+G8')) elif i == 3: a = 1 + sheet.cell_value(8, 3) w_sheet.write(8, 3, a) w_sheet.write(8, 5, Formula('IF(D9>=E9,E9,D9)')) w_sheet.write(8, 7, Formula('266000*F9+G9')) elif i == 4: a = 1 + sheet.cell_value(9, 3) w_sheet.write(9, 3, a) w_sheet.write(9, 5, Formula('IF(D10>=E10,E10,D10)')) w_sheet.write(9, 7, Formula('266000*F10+G10')) elif i == 5: a = 1 + sheet.cell_value(10, 3) w_sheet.write(10, 3, a) w_sheet.write(10, 5, Formula('IF(D11>=E11,E11,D11)')) w_sheet.write(10, 7, Formula('266000*F11+G11')) elif i == 6: a = 1 + sheet.cell_value(11, 3) w_sheet.write(11, 3, a) w_sheet.write(11, 5, Formula('IF(D12>=E12,E12,D12)')) w_sheet.write(11, 7, Formula('266000*F12+G12')) elif i == 7: a = 1 + sheet.cell_value(12, 3) w_sheet.write(12, 3, a) w_sheet.write(12, 5, Formula('IF(D13>=E13,E13,D13)')) w_sheet.write(12, 7, Formula('266000*F13+G13')) elif i == 8: a = 1 + sheet.cell_value(13, 3) w_sheet.write(13, 3, a) w_sheet.write(13, 5, Formula('IF(D14>=E14,E14,D14)')) w_sheet.write(13, 7, Formula('266000*F14+G14')) elif i == 9: a = 1 + sheet.cell_value(14, 3) w_sheet.write(14, 3, a) w_sheet.write(14, 5, Formula('IF(D15>=E15,E15,D15)')) w_sheet.write(14, 7, Formula('266000*F15+G15')) wb.save('bang1.xlsx') cv2.imshow("Face", frame_clone) # If the 'q' key is pressed, stop the loop #if cv2.waitKey(1) & 0xFF == ord("q"): if p == 2: break # Cleanup the camera and close any open windows camera.release() cv2.destroyAllWindows()
from xlwt import Formula SUM_WORKING_DAYS = Formula('COUNTA(C7:C37)&"日"') SUM_WORKING_HOURS = Formula('TEXT(SUM(N7:N37), "[h]:mm")') SUM_OVERTIME_HOURS = Formula('TEXT(SUM(O7:O37), "[h]:mm")') # 日々の実働時間を計算するためのエクセル関数を作成 wh_formulas_display = [] wh_formulas_calc = [] for i in range(7, 38): # TODO 時間を表示するのと計算を可能にするのがうまく両立できない # 現状の対応:見た目用の時間(黒文字白背景)と計算用の時間(白文字白背景)を別に用意する c = f'C{i}' d = f'D{i}' e = f'E{i}' wh_formula_display = f'TEXT(IF(OR({c}="", {d}=""), "", {d}-{c}-{e}), "h:mm")' wh_formulas_display.append(wh_formula_display) f = f'F{i}' wh_formula_calc = f'IF({f}="", 0, VALUE({f}))' wh_formulas_calc.append(wh_formula_calc) DISPLAY_WORKING_HOURS = [Formula(f) for f in wh_formulas_display] CALC_WORKING_HOURS = [Formula(f) for f in wh_formulas_calc] # 日々の残業時間を計算するためのエクセル関数を作成 oh_formulas_display = [] oh_formulas_calc = [] for i in range(7, 38): # TODO 時間を表示するのと計算を可能にするのがうまく両立できない
def one2one(self): sourceFile0=self.lineEdit_5.text() sourceFile1=self.lineEdit_6.text() print(sourceFile0) print(sourceFile1) if sourceFile0.strip()=="" or sourceFile1.strip()=="": QMessageBox.critical(self,"注意","请选择数据读取表和写入表!") return else: sourceFile0=sourceFile0.strip() sourceFile1=sourceFile1.strip() filenameOk0=sourceFile0.endswith(".xlsx") or sourceFile0.endswith(".xls") filenameOk1=sourceFile1.endswith(".xlsx") or sourceFile1.endswith(".xls") filenameOk=filenameOk0 and filenameOk1 if filenameOk is False: QMessageBox.critical(self,"注意","必须选择.xls或者.xlsx格式EXCEL文件!") return columnName0=self.comboBox_0.currentText() columnName1=self.comboBox_1.currentText() columnName2=self.comboBox_2.currentText() columnName3=self.comboBox_3.currentText() columnName4=self.comboBox_4.currentText() columnName5=self.comboBox_5.currentText() print(columnName0,columnName1,columnName2,columnName3,columnName4,columnName5) #-------------------------------------------------------- #建立SQLITE3数据库连接 conn = sqlite3.connect('advertisers.db') cursor = conn.cursor() sql='DROP TABLE IF EXISTS book0' cursor.execute(sql) conn.commit() sql='CREATE TABLE IF NOT EXISTS book0(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))' cursor.execute(sql) conn.commit() #将工作表数据存入SQLITE数据库表 args=[] book0=open_workbook(sourceFile0) for sheet in book0.sheets(): for i in range(1,sheet.nrows): vArray=[] for j in range(26): if j<sheet.ncols: v=str(sheet.cell(i,j).value).strip() else: v='' vArray.append(v) arg=tuple(vArray) args.append(arg) #批量执行sql数据插入 try: sql = 'INSERT INTO book0(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' cursor.executemany(sql, args) except Exception as e: print(e) finally: cursor.close() conn.commit() conn.close() self.statusbar.showMessage("数据读取表已经成功导入本地SQLITE数据库") #----------------------------------------------------------------------- #建立SQLITE3数据库连接 conn = sqlite3.connect('advertisers.db') cursor = conn.cursor() sql='DROP TABLE IF EXISTS book1' cursor.execute(sql) conn.commit() sql='CREATE TABLE IF NOT EXISTS book1(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))' cursor.execute(sql) conn.commit() #将工作表数据存入SQLITE数据库表 args=[] book1=open_workbook(sourceFile1) for sheet in book1.sheets(): for i in range(1,sheet.nrows): vArray=[] for j in range(26): if j<sheet.ncols: v=str(sheet.cell(i,j).value).strip() else: v='' vArray.append(v) arg=tuple(vArray) args.append(arg) #批量执行sql数据插入 try: sql = 'INSERT INTO book1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' cursor.executemany(sql, args) except Exception as e: print(e) finally: cursor.close() conn.commit() conn.close() self.statusbar.showMessage("数据写入表已经成功导入本地SQLITE数据库") #-------------------------------------------------------------------------------- #比较两个表 self.statusbar.showMessage("正在比对数据 请稍候") #创建EXCEL表格准备写入数据 book = Workbook() sheet= book.add_sheet('Sheet1',cell_overwrite_ok=True) #单元格格式水蓝色 cellStyle=easyxf('font: name 宋体;pattern: pattern solid, fore_colour aqua') #建立SQLITE3数据库连接 conn = sqlite3.connect('advertisers.db') cursor = conn.cursor() #通过联合多表UPDATE来导入已有广告主到新表中 #覆盖原数据 #sql="UPDATE book1 SET "+columnName3+"=(SELECT "+columnName2+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+")" #不覆盖原数据 sql="UPDATE book1 SET "+columnName3+"=(SELECT "+columnName2+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+") WHERE book1."+columnName3+"=''" print(sql) cursor.execute(sql) conn.commit #覆盖原数据 #sql="UPDATE book1 SET "+columnName5+"=(SELECT "+columnName4+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+")" #不覆盖原数据 sql="UPDATE book1 SET "+columnName5+"=(SELECT "+columnName4+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+") WHERE book1."+columnName5+"=''" print(sql) cursor.execute(sql) conn.commit #从本地数据库表中查询数据输出到EXCEL表 sql="SELECT * FROM book1 ORDER BY l DESC,j ASC,d ASC" cursor.execute(sql) conn.commit rows = cursor.fetchall() if rows: rown=1 for row in rows: for i in range(26): if (i==3 or i==9): if row[i]: link='HYPERLINK("https://www.baidu.com/s?wd='+str(row[i]).replace("&","+")+'";"'+str(row[i])+'")' #print(link) sheet.write(rown,i,Formula(link),cellStyle) else: sheet.write(rown,i,"",cellStyle) elif i==5: link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[5])+'";"'+str(row[5])+'")' sheet.write(rown,i,Formula(link),cellStyle) elif i==17: link='HYPERLINK("https://translate.google.cn/#zh-CN/en/'+str(row[9])+'";"英译")' sheet.write(rown,i,Formula(link),cellStyle) elif i==18: link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[9])+'";"天眼查")' sheet.write(rown,i,Formula(link),cellStyle) else: if row[i]: sheet.write(rown,i,str(row[i]),cellStyle) else: sheet.write(rown,i,"",cellStyle) rown=rown+1 self.statusbar.showMessage("共计写入"+str(rown)+"行") #设置EXCEL表格格式 sheet.col(0).hidden=True sheet.col(1).hidden=True sheet.col(2).hidden=True sheet.col(3).width=8000 sheet.col(4).hidden=True sheet.col(5).width=3000 sheet.col(6).hidden=True sheet.col(7).hidden=True sheet.col(8).hidden=True sheet.col(9).width=10000 sheet.col(10).hidden=True sheet.col(11).width=10000 sheet.col(12).width=2000 sheet.col(13).width=2000 sheet.col(14).width=2000 sheet.col(15).width=2000 sheet.col(16).width=2000 sheet.col(17).width=2000 sheet.col(18).width=2000 savedFileName=re.sub(r'\.xlsx|\.xls','',sourceFile1)+'_ok.xls' if os.path.exists(savedFileName): t=time.localtime() savedFileName=re.sub(r'\.xlsx|\.xls','',sourceFile1)+'_ok_'+str(t.tm_year)+'_'+str(t.tm_mon)+'_'+str(t.tm_mday)+'_'+str(t.tm_hour)+'_'+str(t.tm_min)+'_'+str(t.tm_sec)+'.xls' book.save(savedFileName) hyperlinker="<a href='file:///"+savedFileName+"' title='点击打开'>"+savedFileName+"</a>" QMessageBox.information(self,"注意","处理完的广告主文件已经保存到<br/><br/>"+hyperlinker+"<br/>(点击链接打开文件)<br/><br/>") #关闭SQLITE数据库 cursor.close() conn.close()
def processAdvertisers(self): targetFileName=self.lineEdit_4.text().strip() #先判断文件选择是否正确 if targetFileName is "": QMessageBox.critical(self,'注意', '请先选择要处理的新广告主EXCEL表!') return filenameOk=targetFileName.endswith(".xlsx") or targetFileName.endswith(".xls") if filenameOk is False: QMessageBox.critical(self,"注意","请输入正确的EXCEL文件名!") return print("当前选定要处理的广告主EXCEL表:",targetFileName) #建立SQLITE3数据库连接 conn = sqlite3.connect('advertisers.db') cursor = conn.cursor() sql='DROP TABLE IF EXISTS workbook' cursor.execute(sql) conn.commit() sql='CREATE TABLE IF NOT EXISTS workbook(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))' cursor.execute(sql) conn.commit() #将工作表数据存入SQLITE数据库表 args=[] book=open_workbook(targetFileName) for sheet in book.sheets(): for i in range(1,sheet.nrows): vArray=[] for j in range(26): if j<sheet.ncols: v=str(sheet.cell(i,j).value).strip() else: v="" vArray.append(v) arg=tuple(vArray) args.append(arg) #批量执行sql数据插入 try: sql = 'INSERT INTO workbook(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' cursor.executemany(sql, args) except Exception as e: print(e) finally: cursor.close() conn.commit() conn.close() self.statusbar.showMessage("Excel表已经成功导入本地SQLITE数据库") #-------------------------------------------------------------------------------- #测试是否成功 self.statusbar.showMessage("正在比对数据,请稍候...") #创建EXCEL表格准备写入数据 book = Workbook() sheet= book.add_sheet('Sheet1',cell_overwrite_ok=True) #单元格格式水蓝色 cellStyle=easyxf('font: name 宋体;pattern: pattern solid, fore_colour aqua') #建立SQLITE3数据库连接 conn = sqlite3.connect('advertisers.db') cursor = conn.cursor() #通过联合多表UPDATE来导入已有广告主到新表中 sql="UPDATE workbook SET j=(SELECT cname FROM advertiser WHERE bname=workbook.d) WHERE j=''" cursor.execute(sql) conn.commit sql="UPDATE workbook SET l=(SELECT ename FROM advertiser WHERE cname=workbook.j) WHERE l=''" cursor.execute(sql) conn.commit #从本地数据库表中查询数据输出到EXCEL表 sql="SELECT * FROM workbook ORDER BY l DESC,j ASC,d ASC" cursor.execute(sql) conn.commit rows = cursor.fetchall() if rows: rown=1 for row in rows: for i in range(26): if (i==3 or i==9): if row[i]: link='HYPERLINK("https://www.baidu.com/s?wd='+str(row[i]).replace("&","+")+'";"'+str(row[i])+'")' sheet.write(rown,i,Formula(link),cellStyle) else: sheet.write(rown,i,"",cellStyle) elif i==5: link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[5])+'";"'+str(row[5])+'")' sheet.write(rown,i,Formula(link),cellStyle) elif i==17: link='HYPERLINK("https://translate.google.cn/#zh-CN/en/'+str(row[9])+'";"英译")' sheet.write(rown,i,Formula(link),cellStyle) elif i==18: link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[9])+'";"天眼查")' sheet.write(rown,i,Formula(link),cellStyle) else: if row[i]: sheet.write(rown,i,str(row[i]),cellStyle) else: sheet.write(rown,i,"",cellStyle) rown=rown+1 self.statusbar.showMessage("共计写入"+str(rown)+"行") #设置EXCEL表格格式 sheet.col(0).hidden=True sheet.col(1).hidden=True sheet.col(2).hidden=True sheet.col(3).width=8000 sheet.col(4).hidden=True sheet.col(5).width=3000 sheet.col(6).hidden=True sheet.col(7).hidden=True sheet.col(8).hidden=True sheet.col(9).width=10000 sheet.col(10).hidden=True sheet.col(11).width=10000 sheet.col(12).width=2000 sheet.col(13).width=2000 sheet.col(14).width=2000 sheet.col(15).width=2000 sheet.col(16).width=2000 sheet.col(17).width=2000 sheet.col(18).width=2000 savedFileName=re.sub(r'\.xlsx|\.xls','',targetFileName)+'_ok.xls' if os.path.exists(savedFileName): t=time.localtime() savedFileName=re.sub(r'\.xlsx|\.xls','',targetFileName)+'_ok_'+str(t.tm_year)+'_'+str(t.tm_mon)+'_'+str(t.tm_mday)+'_'+str(t.tm_hour)+'_'+str(t.tm_min)+'_'+str(t.tm_sec)+'.xls' book.save(savedFileName) hyperlinker="<a href='file:///"+savedFileName+"' title='点击打开'>"+savedFileName+"</a>" QMessageBox.information(self,"注意","处理完的广告主文件已经保存到<br/><br/>"+hyperlinker+"<br/>(点击链接打开文件)<br/><br/>") #关闭SQLITE数据库 cursor.close() conn.close()
def write_sheet(sortedinput, sheetnum, header): ws = wb.add_sheet('prion-scores-%d' % sheetnum) header_style = easyxf('font: bold true, color black;') url_style = easyxf('font: underline single, color blue;') corescore_pos_style = easyxf( 'font: bold true; pattern: pattern solid, fore_color light_orange;') corescore_zero_style = easyxf( 'pattern: pattern solid_fill, fore_color light_orange;') llk_pos_style = easyxf( 'font: bold true; pattern: pattern solid, fore_color light_yellow;') llk_zero_style = easyxf('pattern: pattern solid, fore_color light_yellow;') rossprd_high_style = easyxf( 'font: bold true; pattern: pattern solid, fore_color light_turquoise;') rossprd_low_style = easyxf( 'pattern: pattern solid, fore_color light_turquoise;') shrink_col_style = easyxf( 'pattern: pattern solid, fore_color gray25; alignment: horizontal center, vertical center, wrap on;' ) row_style = easyxf('alignment: horizontal center, vertical center;') #default_style = easyxf('') # append new columns to header start_header = header[0:1] rest = header[2:] header = [] header.append("rank_by_corescore") header.extend(start_header) header.append("gene_name") header.append("gene_aliases") header.append("orf_status") header.append("description") # rearrange columns in rest: push lesser-used cols to end of list for item in ["PRDstart", "PRDend", "PRDlen", "PROTlen", "COREaa", "STARTaa", "ENDaa", "PRDaa", "PRDscore", \ "MWstart", "MWend", "MWlen", "HMMall", "HMMvit", "FInumaa", "FImeanhydro", "FImeancharge", "FImeancombo", "FImaxrun"]: move_to_back(rest, item) # now add them back header.extend(rest) # initialize row number row = 0 # write header for col in range(len(header)): ws.row(0).write(col, header[col], header_style) # parse fields and generate output Excel file for inputrow in sortedinput: orf_id = inputrow['SEQid'] # if we are filtering, only print out specified hits if include: if orf_id in include: if debug: print "include:", orf_id, "rank:", inputrow[ "rank_by_corescore"] else: #print "skip:", orf_id, "rank:", inputrow["rank_by_corescore"] continue else: if debug: print orf_id, "rank:", inputrow["rank_by_corescore"] # loop through input cols for col in range(len(header)): # get key key = header[col] # note: offset row by one if key == "rank_by_corescore": # use the current row as the rank value = inputrow[key] if debug: print orf_id, value ws.row(row + 1).write(col, value) # hyperlink ORF name to SGD elif key == "SEQid": try: value = inputrow[key] formula = 'HYPERLINK("http://www.yeastgenome.org/cgi-bin/locus.fpl?locus=%s";"%s")' % ( value, value) link = Formula(formula) ws.row(row + 1).write(col, link, url_style) except: ws.row(row + 1).write(col, value) # get human readable name from GFF3 file elif key == "gene_name": try: common_name = orf_map[orf_id]['gene'] except (NameError, KeyError) as e: # default back to using the ORF id common_name = orf_id ws.row(row + 1).write(col, common_name) elif key == "gene_aliases": try: aliases = orf_map[orf_id]['Alias'] except (NameError, KeyError) as e: # if no aliases leave blank aliases = "" if aliases == common_name: aliases = "" if aliases.startswith(common_name): aliases = string.replace(aliases, common_name + ',', '') # also remove URL quoting, if it exists ws.row(row + 1).write(col, urllib.unquote(aliases)) elif key == "orf_status": try: orf_status = orf_map[orf_id]['orf_classification'] except (NameError, KeyError) as e: orf_status = "NA" ws.row(row + 1).write(col, orf_status) elif key == "description": try: display = orf_map[orf_id]['display'] except (NameError, KeyError) as e: display = orf_id ws.row(row + 1).write(col, urllib.unquote(display), shrink_col_style) elif key == "COREscore": value = inputrow[key] if float(value) > 0: ws.row(row + 1).write(col, value, corescore_pos_style) else: ws.row(row + 1).write(col, value, corescore_zero_style) elif key == "LLR": value = inputrow[key] if float(value) > 0: ws.row(row + 1).write(col, value, llk_pos_style) else: ws.row(row + 1).write(col, value, llk_zero_style) elif key == "PAPAprd": value = inputrow[key] if float(value) > 0.05: ws.row(row + 1).write(col, value, rossprd_high_style) else: ws.row(row + 1).write(col, value, rossprd_low_style) elif key == "PAPAfi": value = inputrow[key] if float(value) < 0.0: ws.row(row + 1).write(col, value, rossprd_high_style) else: ws.row(row + 1).write(col, value, rossprd_low_style) else: # get value at key value = inputrow[key] ws.row(row + 1).write(col, value) ws.row(row + 1).set_style(row_style) ws.row(row + 1).height_mismatch = 1 ws.row(row + 1).height = 600 row += 1 if not (allhits): if row >= numhits: break # set column widths after everything generated set_col_width("description", header, ws, 25) set_col_width("LLR", header, ws, 5) set_col_width("LLRstart", header, ws, 7) set_col_width("LLRend", header, ws, 7) set_col_width("LLRlen", header, ws, 6) set_col_width("NLLR", header, ws, 6) set_col_width("COREscore", header, ws, 8) set_col_width("COREstart", header, ws, 8) set_col_width("CORElen", header, ws, 8) set_col_width("COREend", header, ws, 6) for colname in ["PRDstart", "PRDend", "PRDend", "PRDlen", "PROTlen"]: set_col_width(colname, header, ws, 8)
def produce_summary(self, cr, uid, travel, context=None): number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]') total_fnt = Font() total_fnt.name = 'Calibri' total_fnt.bold = True total_fnt.height = 16 * 20 # font size 12 total_cell_l_style = XFStyle() total_cell_l_style.alignment = Column.title_aln total_cell_l_style.borders = Borders() total_cell_l_style.borders.left = Borders.THICK total_cell_l_style.borders.right = Borders.HAIR total_cell_l_style.borders.top = Borders.THICK total_cell_l_style.borders.bottom = Borders.THICK total_cell_l_style.pattern = Column.title_ptn total_cell_l_style.num_format_str = number_format total_cell_l_style.font = Column.obj_fnt total_cell_r_style = XFStyle() total_cell_r_style.alignment = Column.title_aln total_cell_r_style.borders = Borders() total_cell_r_style.borders.left = Borders.HAIR total_cell_r_style.borders.right = Borders.THICK total_cell_r_style.borders.top = Borders.THICK total_cell_r_style.borders.bottom = Borders.THICK total_cell_r_style.pattern = Column.title_ptn total_cell_r_style.num_format_str = number_format total_cell_r_style.font = Column.obj_fnt sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt, Column.title_aln, total_cell_l_style.borders, Column.title_ptn) total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln, total_cell_r_style.borders, Column.title_ptn, number_format) journeys = [i for i in travel.journey_ids] w = Workbook() ws = w.add_sheet(_('Travel Summary')) ws.row(2).height = 0x0280 ws.row(3 + len(journeys)).height = 0x0140 ws.row(4 + len(journeys)).height = 0x0180 row = 0 row += 2 # Write headers for i, col in enumerate(self._excel_columns): ws.col(i).width = col.width ws.write(row, i, col.text, col.style) row += 1 for i, obj in enumerate(journeys): ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style) for j in xrange(1, len(self._excel_columns)): ws.write(row + i, j, self._excel_columns[j].func(obj), self._excel_columns[j].obj_style) row += len(journeys) rate_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TICKET RATE')) - 1 cost_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('COSTS')) - 1 total_index = next(i for i, x in enumerate(self._excel_columns) if x.text == _('TOTAL')) - 1 # Sub total label ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text, sub_total_cell_label.style) # Sub totals ws.write( row, rate_index + 1, Formula("SUM(%s%d:%s%d)" % (chr(66 + rate_index), 4, chr(66 + rate_index), row)), total_cell_l_style) ws.write( row, rate_index + 2, Formula("SUM(%s%d:%s%d)" % (chr(66 + cost_index), 4, chr(66 + cost_index), row)), total_cell_r_style) total_top_underline_style = XFStyle() total_top_underline_style.borders = Borders() total_top_underline_style.borders.top = Borders.THICK total_top_underline_style.font = Column.obj_fnt # Draw a line above total to close box ws.write(row, total_index + 1, "", total_top_underline_style) row += 1 # Total label ws.write_merge(row, row, 0, rate_index, total_cell_label.text, total_cell_label.style) # Total ws.write_merge( row, row, rate_index + 1, total_index, Formula("%s%d+%s%d" % (chr(66 + rate_index), row, chr(66 + cost_index), row)), total_cell_label.style) return w
def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp, ibm_filtered): # Sheet 1 sheet = workbook.get_sheet(0) # Download hyperlink: bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point) sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)), bigfont) # Padded zeroes number format pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle() pad2.num_format_str = '00' pad3.num_format_str = '000' pad4.num_format_str = '0000' current_row = 3 code_id = '' for row, data in enumerate(gl, current_row): # Only insert GLPivDownload objects with a matching IBMData object. if ibm.filter(ibmIdentifier=data.codeID).exists(): # We have to aggregate all the GLPivotDownload objects with # matching codeID values, and insert one row with total # ytdActual and fyBudget values. if code_id != data.codeID: # Reached the next codeID value. current_row += 1 # Advance one row. code_id = data.codeID i = ibm.get(ibmIdentifier=data.codeID) sheet.write(current_row, 0, data.codeID) sheet.write(current_row, 1, int(data.costCentre), pad3) sheet.write(current_row, 2, data.account, pad2) sheet.write(current_row, 3, data.service, pad2) sheet.write(current_row, 4, data.activity, pad3) try: sheet.write(current_row, 5, int(data.project), pad4) except ValueError: sheet.write(current_row, 5, data.project, pad4) try: sheet.write(current_row, 6, int(data.job), pad3) except ValueError: sheet.write(current_row, 6, data.job, pad3) sheet.write(current_row, 7, data.jobName) sheet.write(current_row, 8, data.activityName) sheet.write(current_row, 9, data.projNameNo) sheet.write(current_row, 10, i.budgetArea) sheet.write(current_row, 11, i.projectSponsor) sheet.write(current_row, 14, i.regionalSpecificInfo) sheet.write(current_row, 15, i.servicePriorityID) sheet.write(current_row, 18, i.annualWPInfo) sheet.write(current_row, 19, data.mPRACategory) ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual')) fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget')) sheet.write(current_row, 20, ytd['ytdActual__sum']) sheet.write(current_row, 21, fy['fybudget__sum']) # Insert the footer row formulae and '#END OF INPUT' sheet.write(current_row+2, 0, '#END OF INPUT') sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20)))) sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21)))) # Sheet 2 - Service priority checkboxes. sheet = workbook.get_sheet(1) write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp) # Sheet 3: Budget area & project sponsor lookup data. # This is a list of unique budgetArea and projectSponsor values, written in # as reference data for macros. sheet = workbook.get_sheet(2) write_budget_areas(sheet, ibm_filtered) write_project_sponsors(sheet, ibm_filtered) # Select the first sheet. sheet = workbook.get_sheet(0)
table = workbook.add_sheet('wahson', cell_overwrite_ok=True) table.col(0).width = 5000 table.col(1).width = 10000 table.col(2).width = 7000 style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.bold = True style.font = font # 为样式设置字体 table.write(0, 0, '型号', style) table.write(0, 1, '型号产品链接', style) table.write(0, 2, '类别', style) link_style = easyxf('font: underline single') i = 1 for item in items: table.write(i, 0, item['name']) table.write( i, 1, Formula('HYPERLINK("' + item['url'] + '";"' + item['url'] + '")'), link_style) table.write(i, 2, item['type']) i = i + 1 workbook.save("d:/wahson.xls") time.sleep(5) browser.close()