def print_account_totals(self, _xs, xlwt, ws, row_start_account, row_position, current_account, _p): cell_format = _xs['bold'] + _xs['fill'] + \ _xs['borders_all'] + _xs['wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) c_specs = [ ('acc_title', 2, 0, 'text', current_account.name), ('code', 1, 0, 'text', current_account.code), ] for column in range(3, 7): # in case of one single comparison, the column 6 will contain # percentages if (_p.comparison_mode == 'single' and column == 6): total_diff = rowcol_to_cell(row_position, column - 1) total_balance = rowcol_to_cell(row_position, column - 2) account_formula = 'Round(' + total_diff + \ '/' + total_balance + '*100;0)' else: account_start = rowcol_to_cell(row_start_account, column) account_end = rowcol_to_cell(row_position - 1, column) account_formula = 'Round(SUM(' + \ account_start + ':' + account_end + ');2)' c_specs += [('total%s' % column, 1, 0, 'text', None, account_formula, None, cell_style_decimal)] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row( ws, row_position, row_data, cell_style) return row_position + 1
def writetoxls(list,fn): wbook = xlwt.Workbook() wsheet = wbook.add_sheet('sheet1') cellxf_g = xlwt.easyxf('font: color green, bold true') cellxf_r = xlwt.easyxf('font: color red, bold true') cellxf_b = xlwt.easyxf("font: bold false") for i in range(len(list)): wsheet.write(i,0,xlwt.Formula('HYPERLINK("%s";"%s")'%(list[i][6].value,list[i][0].value))) wsheet.write(i,1,list[i][1].value.decode('utf-8')) wsheet.write(i,2,list[i][2].value) wsheet.write(i,3,list[i][3].value.decode('utf-8')) wsheet.write(i,4,list[i][4].value.decode('utf-8')) wsheet.write(i,5,list[i][5].value) wsheet.write(i,6,list[i][6].value.decode('utf-8')) for j in range(7,len(list[i]),2): if list[i][j+1].value > list[i][2].value: cellxf = cellxf_g elif list[i][j+1].value < list[i][2].value: cellxf = cellxf_r else: cellxf = cellxf_b wsheet.write(i,j,list[i][j].value,cellxf) wsheet.write(i,j+1,list[i][j+1].value,cellxf) wsheet.col(0).width = 13000 wsheet.col(2).width = 3000 wsheet.col(6).hidden = True wbook.save(fn)
def render(self, request): result = StringIO.StringIO() header = dict(enumerate(get_flattened_field_names(self.fields))) count = len(header) book = xlwt.Workbook() sheet = book.add_sheet('new') styles = { datetime.date: xlwt.easyxf(num_format_str='M/D/YY'), int: xlwt.easyxf(num_format_str='#,##0'), float: xlwt.easyxf(num_format_str='#,##0.00000'), datetime.datetime: xlwt.easyxf(num_format_str='M/D/YY h:mm'), unicode: xlwt.easyxf(), } styles['default'] = styles[unicode] for i in range(0, count): sheet.write(0, i, header[i]) for row_index, row_contents in enumerate(get_flattened_data(self.construct())): for i in range(0, count): val = row_contents.get(header[i], "") val, key = (val, type(val)) if styles.has_key(type(val)) else detect_style(val) or (val, 'default') sheet.write(row_index + 1, i, val, styles[key]) book.save(result) return result.getvalue()
def show_color(sheet): colNum = 6 width = 5000 height = 500 colors = ['aqua','black','blue','blue_gray','bright_green','brown','coral','cyan_ega','dark_blue','dark_blue_ega','dark_green','dark_green_ega','dark_purple','dark_red', 'dark_red_ega','dark_teal','dark_yellow','gold','gray_ega','gray25','gray40','gray50','gray80','green','ice_blue','indigo','ivory','lavender', 'light_blue','light_green','light_orange','light_turquoise','light_yellow','lime','magenta_ega','ocean_blue','olive_ega','olive_green','orange','pale_blue','periwinkle','pink', 'plum','purple_ega','red','rose','sea_green','silver_ega','sky_blue','tan','teal','teal_ega','turquoise','violet','white','yellow'] for colorIndex in range(len(colors)): rowIndex = colorIndex / colNum colIndex = colorIndex - rowIndex*colNum sheet.col(colIndex).width = width sheet.row(rowIndex).set_style(easyxf('font:height %s;'%height)) color = colors[colorIndex] whiteStyle = easyxf('pattern:pattern solid, fore_colour %s;' 'align: vertical center, horizontal center;' 'font: bold true, colour white;' % color) blackStyle = easyxf('pattern:pattern solid, fore_colour %s;' 'align: vertical center, horizontal center;' 'font: bold true, colour black;' % color) if color == 'black': sheet.write(rowIndex, colIndex, color, style = whiteStyle) else: sheet.write(rowIndex, colIndex, color, style = blackStyle)
def export_xlwt(model, values_list, fields): import xlwt from datetime import datetime, date modelname = model._meta.verbose_name_plural.lower() book = xlwt.Workbook(encoding='utf8') sheet = book.add_sheet(modelname) default_style = xlwt.Style.default_style datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm') date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy') for j, f in enumerate(fields): sheet.write(0, j, fields[j]) for row, rowdata in enumerate(values_list): for col, val in enumerate(rowdata): if isinstance(val, datetime): style = datetime_style elif isinstance(val, date): style = date_style else: style = default_style sheet.write(row + 1, col, val, style=style) response = HttpResponse(mimetype='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=%s.xls' % modelname book.save(response) return response
def write_data_1columnpertimestep(xlSheet, data, ts, startdate=None, format_string=''): tmp = data.copy() if numpy.ma.isMA(tmp): tmp = numpy.ma.filled(tmp,float(c.missing_value)) xlCol = 0 # write the data to the xl file xlSheet.write(1,xlCol,'Day') nrows = numpy.shape(tmp)[0] ncols = numpy.shape(tmp)[1] if startdate is None: for j in range(nrows+1): xlSheet.write(j+2,xlCol,j) else: d_xf = xlwt.easyxf(num_format_str='dd/mm/yyyy') for j in range(nrows): d = startdate + datetime.timedelta(days=j) xlSheet.write(j+2,xlCol,d,d_xf) xlCol = xlCol + 1 if len(format_string)!=0: d_xf = xlwt.easyxf(num_format_str=format_string) else: d_xf = xlwt.easyxf() for m in range(1,ncols+1): xlSheet.write(1,xlCol,float(m)*ts/60) for j in range(nrows): xlSheet.write(j+2,xlCol,float(tmp[j,m-1]),d_xf) xlCol = xlCol + 1
def visitor_excel(request): book = xlwt.Workbook(encoding='utf8') sheet = book.add_sheet('untitled') default_style = xlwt.Style.default_style datetime_style = xlwt.easyxf(num_format_str='mm/dd/yyyy hh:mm') date_style = xlwt.easyxf(num_format_str='mm/dd/yyyy') values_list = Visitor.objects.all().values_list() for row, rowdata in enumerate(values_list): for col, val in enumerate(rowdata): if isinstance(val, datetime): style = datetime_style val=val.replace(tzinfo=None) elif isinstance(val, date): style = date_style else: style = default_style sheet.write(row, col, val, style=style) response = HttpResponse(mimetype='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=visitors.xls' book.save(response) return response
def get_xls_export(self, context): results = self.get_results(context) output = StringIO.StringIO() export_header = (self.request.GET.get('export_xls_header', 'off') == 'on') model_name = self.opts.verbose_name book = xlwt.Workbook(encoding='utf8') sheet = book.add_sheet(u"%s %s" % (_(u'Sheet'), force_unicode(model_name))) styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'), 'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'), 'time': xlwt.easyxf(num_format_str='hh:mm:ss'), 'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'), 'default': xlwt.Style.default_style} datas = [row.values() for row in results] if export_header: datas.insert(0, results[0].keys()) for rowx, row in enumerate(datas): for colx, value in enumerate(row): if export_header and rowx == 0: cell_style = styles['header'] else: if isinstance(value, datetime.datetime): cell_style = styles['datetime'] elif isinstance(value, datetime.date): cell_style = styles['date'] elif isinstance(value, datetime.time): cell_style = styles['time'] else: cell_style = styles['default'] sheet.write(rowx, colx, value, style=cell_style) book.save(output) output.seek(0) return output.getvalue()
def add_to_excel(self, state): """ adds content to the excel file based on the specific statistic type """ import xlwt answers = state['answers'] ws = state['ws'] current_row = state['current_row'] translator = self.getSite().getPortalTranslations() total, unanswered, per_row_and_choice = self.calculate(self.question, answers) #define Excel styles header_style = xlwt.easyxf('font: bold on; align: vert centre') normal_style = xlwt.easyxf('align: vert centre') #write cell elements similarly to the zpt-->html output ws.write(current_row, 1, self.question.title, header_style) current_row += 1 current_col = 2 for choice in self.question.choices: ws.write(current_row, current_col, choice, header_style) current_col += 1 ws.write(current_row, current_col, translator('Not answered'), header_style) current_row += 1 for row in self.question.rows: r = self.question.rows.index(row) ws.write(current_row, 1, row, header_style) current_col = 2 for statistics in per_row_and_choice[r]: ws.write(current_row, current_col, '%u (%.2f%%)' % (statistics[0], round(statistics[1], 2)), normal_style) current_col += 1 ws.write(current_row, current_col, '%u (%.2f%%)' % (unanswered[r][0], round(unanswered[r][1], 2)), normal_style) current_row += 1 state['current_row'] = current_row
def add_toxicities_to_sheet(sheet, imported_rec): sheet.write_merge(0, 0, 0, 1, 'Effective Concentrations') sheet.write(1, 0, 'Index', xlwt.easyxf('font: underline on;')) sheet.write(1, 1, 'Species', xlwt.easyxf('font: underline on;')) sheet.write(1, 2, 'After 24H', xlwt.easyxf('font: underline on;')) sheet.write(1, 3, 'After 48H', xlwt.easyxf('font: underline on;')) sheet.write(1, 4, 'After 96H', xlwt.easyxf('font: underline on;')) idx = 0 for idx, ec in enumerate([t for t in imported_rec.toxicities if t.tox_type == 'EC']): sheet.write(2 + idx, 0, idx) sheet.write(2 + idx, 1, ec.species) sheet.write(2 + idx, 2, ec.after_24h) sheet.write(2 + idx, 3, ec.after_48h) sheet.write(2 + idx, 4, ec.after_96h) v_offset = 2 + idx + 2 sheet.write_merge(v_offset, v_offset, 0, 1, 'Lethal Concentrations') v_offset += 1 sheet.write(v_offset, 0, 'Index', xlwt.easyxf('font: underline on;')) sheet.write(v_offset, 1, 'Species', xlwt.easyxf('font: underline on;')) sheet.write(v_offset, 2, 'After 24H', xlwt.easyxf('font: underline on;')) sheet.write(v_offset, 3, 'After 48H', xlwt.easyxf('font: underline on;')) sheet.write(v_offset, 4, 'After 96H', xlwt.easyxf('font: underline on;')) v_offset += 1 for idx, lc in enumerate([t for t in imported_rec.toxicities if t.tox_type == 'LC']): sheet.write(v_offset + idx, 0, idx) sheet.write(v_offset + idx, 1, lc.species) sheet.write(v_offset + idx, 2, lc.after_24h) sheet.write(v_offset + idx, 3, lc.after_48h) sheet.write(v_offset + idx, 4, lc.after_96h)
def add_scores_to_sheet(sheet, imported_rec): sheet.write_merge(0, 0, 0, 1, 'Oil Category Scores') sheet.write(1, 0, 'Category', xlwt.easyxf('font: underline on;')) sheet.write(1, 1, 'Score', xlwt.easyxf('font: underline on;')) col = sheet.col(0) col.width = 220 * 27 tests = (score_demographics, score_api, score_pour_point, score_flash_point, score_sara_fractions, score_emulsion_constants, score_interfacial_tensions, score_densities, score_viscosities, score_cuts, score_toxicities) idx = 0 for idx, t in enumerate(tests): sheet.write(2 + idx, 0, t.__name__) sheet.write(2 + idx, 1, t(imported_rec)) v_offset = 2 + idx + 2 sheet.write(v_offset, 0, 'Overall Score') sheet.write(v_offset, 1, xlwt.Formula('AVERAGE($B$3:$B${0})'.format(v_offset)))
def make_protected(workbook_male, workbook_female, worksheet_male, worksheet_female, country): row_country_m, col_country_m = find_row_col_index(country, worksheet_male) row_country_f, col_country_f = find_row_col_index(country, worksheet_female) wb_male = copy(workbook_male) wb_female = copy(workbook_female) for i in range(17, 258): for j in range(3, 50): if i != row_country_m: wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;')) else: wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;')) wb_male.get_sheet(0).set_protect(True) wb_male.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.XLS') for i in range(17, 258): for j in range(3, 50): if i != row_country_f: wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;')) else: wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;')) wb_female.get_sheet(0).set_protect(True) wb_female.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_FEMALE.XLS')
def generate_custom_excel_output(self, fields, comments): normal_style = xlwt.easyxf('align: wrap on, horiz left, vert top;') header_style = xlwt.easyxf('font: bold on; align: wrap on, horiz left;') wb = xlwt.Workbook(encoding='utf-8') ws = wb.add_sheet('Sheet 1') row = 0 for col in range(len(fields)): ws.row(row).set_cell_text(col, fields[col][0], header_style) ws.col(0).width = 3000 ws.col(1).width = 20000 ws.col(2).width = 7500 ws.col(3).width = 5000 for item in comments: row += 1 for col in range(len(fields)): ws.row(row).set_cell_text(col, fields[col][1](item), normal_style) row += 1 ws.row(row).set_cell_text(0, 'EEA Comments' , header_style) output = StringIO() wb.save(output) return output.getvalue()
def generateReceipt(itemNames, itemPrices, subTotal, salesTax): """This new method relies on being called from makeSale(). It takes two lists, and two integer values to generate a receipt for the customer. It prints out to an excel file with formatted numbers. This is the best we have to make a receipt for the cashier. It launches the file after it has been created so the customer can print the receipt.""" global ticketID global ALIGN_RIGHT global ALIGN_CENTER receipt = xlwt.Workbook() sale = receipt.add_sheet('Customer Receipt') sale.write_merge(0, 1, 0, 5, "My Tool", BRAND_CELL) sale.write_merge(2, 2, 0, 3, "Today's date:", xlwt.easyxf('align: horiz right')) sale.write_merge(2, 2, 4, 5, str(time.strftime("%m-%d-%Y")), xlwt.easyxf('align: horiz right')) sale.write_merge(3, 3, 0, 3, "Your unique sale ID:", xlwt.easyxf('align: horiz right')) sale.write_merge(3, 3, 4, 5, ticketID, xlwt.easyxf('align: horiz right')) sale.write_merge(4, 4, 0, 3, "Item", BRAND_CELL) sale.write_merge(4, 4, 4, 5, "Price", BRAND_CELL) for x in range(0, len(itemNames)): sale.write_merge(5+x, 5+x, 0, 3, itemNames[x]) sale.write_merge(5+x, 5+x, 4, 5, str(itemPrices[x]/100), ALIGN_RIGHT) sale.write_merge(5+len(itemNames), 5+len(itemNames), 0, 3, "Sales Tax:", ALIGN_RIGHT) sale.write_merge(5+len(itemNames), 5+len(itemNames), 4, 5, str(salesTax/100), ALIGN_RIGHT) sale.write_merge(6+len(itemNames), 6+len(itemNames), 0, 3, "Sub total:", ALIGN_RIGHT) sale.write_merge(6+len(itemNames), 6+len(itemNames), 4, 5, str(subTotal/100), ALIGN_RIGHT) sale.write_merge(7+len(itemNames), 7+len(itemNames), 0, 3, "Total:", ALIGN_RIGHT) sale.write_merge(7+len(itemNames), 7+len(itemNames), 4, 5, str((salesTax+subTotal)/100), ALIGN_RIGHT) sale.write_merge(8+len(itemNames), 8+len(itemNames), 0, 5, "Thanks for shopping at My Tool!", ALIGN_RIGHT) sale.write_merge(9+len(itemNames), 9+len(itemNames), 0, 5, "Have a great day!", ALIGN_CENTER) receipt.save('Customer_receipt_' + str(ticketID)+'.xls') os.system("start Customer_receipt_" + str(ticketID)+ '.xls')
def write_file(result_list, deal_date, company_name, filename): ''' given a list, put it into excel file. deal_date specifies a string which will be rendered as bold company_name and filename are self-explanatory ''' w = Workbook() sheet = w.add_sheet(company_name) row = 2 boldfont = easyxf(strg_to_parse='font: bold on') normalfont = easyxf(strg_to_parse='') sheet.write(0, 0, company_name) sheet.write(1, 0, 'Date') sheet.write(1, 1, 'Open') sheet.write(1, 2, 'Close') for line in result_list: elements = line.decode('utf8').split(',') date_string = elements[0] open_value = elements[1] close_value = elements[4] if datetime.strptime(date_string, '%Y-%m-%d') == deal_date: style = boldfont else: style = normalfont sheet.write(row, 0, date_string, style) sheet.write(row, 1, open_value, style) sheet.write(row, 2, close_value, style) row += 1 print(date_string, open_value, close_value) w.save(filename)
def get(self,request,*args,**kwargs): book = xlwt.Workbook(encoding='utf8') sheet = book.add_sheet('untitled') default_style = xlwt.Style.default_style datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm') date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy') values_list = JobApplication.objects.all().values_list('name','email') for row, rowdata in enumerate(values_list): for col, val in enumerate(rowdata): # if isinstance(val, datetime): # style = datetime_style # elif isinstance(val, date): # style = date_style # else: # style = default_style sheet.write(row, col, val) response = HttpResponse(mimetype='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=example.xls' book.save(response) return response
def __init__(self, name, table, rml=False, parser=False, header=True, store=False): super(AccountBalanceReportingXls, self).__init__(name, table, rml, parser, header, store) # Cell Styles _xs = self.xls_styles # header rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] self.rh_cell_style = xlwt.easyxf(rh_cell_format) self.rh_cell_style_center = xlwt.easyxf( rh_cell_format + _xs['center']) self.rh_cell_style_right = xlwt.easyxf( rh_cell_format + _xs['right']) # lines aml_cell_format = _xs['borders_all'] self.aml_cell_style = xlwt.easyxf(aml_cell_format) self.aml_cell_style_center = xlwt.easyxf( aml_cell_format + _xs['center']) self.aml_cell_style_date = xlwt.easyxf( aml_cell_format + _xs['left'], num_format_str=report_xls.date_format) self.aml_cell_style_decimal = xlwt.easyxf( aml_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # totals rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] self.rt_cell_style = xlwt.easyxf(rt_cell_format) self.rt_cell_style_right = xlwt.easyxf( rt_cell_format + _xs['right']) self.rt_cell_style_decimal = xlwt.easyxf( rt_cell_format + _xs['right'], num_format_str=report_xls.decimal_format)
def write(self): sty = '' header_sty = xlwt.easyxf(sty + 'font: bold on; align: wrap on, vert centre, horiz center;') sty = xlwt.easyxf(sty) ws = self.workbook.add_sheet(self.sheet_name) titles = [_(u'Column'), _(u'Title')] + self.extension_titles for col, title in enumerate(titles): ws.write(0, col, title, style=header_sty) row = 1 for column in self.board.columns: column = column() colname = _('Archived cards') if column.is_archive else column.get_title() for card in column.cards: card = card() ws.write(row, 0, colname, sty) ws.write(row, 1, card.get_title(), sty) card_extensions = dict(card.extensions) for col, key in enumerate(self.extensions, 2): ext = card_extensions[key]() write_extension_data(ext, ws, row, col, sty) row += 1 for col in xrange(len(titles)): ws.col(col).width = 0x3000 ws.set_panes_frozen(True) ws.set_horz_split_pos(1)
def create_excel_file(db, kurzus): book = Workbook(encoding='utf-8') #sheet = book.add_sheet('{0} - {1} - {2}'.format(kurzus['nev'],kurzus['nap'],kurzus['sav'])) sheet = book.add_sheet('Névsor') sheet = create_shit_head(sheet, kurzus_infok) kurzus_hallgatok = get_kurzus_hallgatok(db, kurzus['id']) sorszam = 1 for user in kurzus_hallgatok: sheet.write(sorszam+4,0,sorszam,easyxf( 'borders: left thick, right thick, top thick, bottom thick;' )) sheet.write(sorszam+4,1,user['neptun'],easyxf( 'borders: left thick, right thick, top thick, bottom thick;' )) sheet.write(sorszam+4,2,user['nev'],easyxf( 'borders: left thick, right thick, top thick, bottom thick;' )) for i in xrange(3,20): sheet.write(sorszam+4,i,'',easyxf( 'borders: left thick, right thick, top thick, bottom thick;' )) sorszam = sorszam + 1 book.save('{0}_{1}_{2}.xls'.format(ekezet_eltunteto(kurzus['nev'].lower()), ekezet_eltunteto(kurzus['nap'].lower()), kurzus['sav']))
def add_to_excel(self, state): """ adds content to the excel file based on the specific statistic type """ import xlwt answers = state['answers'] ws = state['ws'] current_row = state['current_row'] translator = self.getSite().getPortalTranslations() total, answered, unanswered = self.calculate(self.question, answers) #define Excel styles header_style = xlwt.easyxf('font: bold on; align: vert centre') normal_style = xlwt.easyxf('align: vert centre') #write cell elements similarly to the zpt-->html output ws.write(current_row, 1, self.question.title, header_style) current_row += 1 ws.write(current_row, 2, translator('Count'), header_style) ws.write(current_row, 3, translator('Percent'), header_style) current_row += 1 ws.write(current_row, 1, translator('Answered'), header_style) ws.write(current_row, 2, answered[0], normal_style) ws.write(current_row, 3, '%.2f%%' % (round(answered[1], 2), ), normal_style) current_row += 1 ws.write(current_row, 1, translator('Not answered'), header_style) ws.write(current_row, 2, unanswered[0], normal_style) ws.write(current_row, 3, '%.2f%%' % (round(unanswered[1], 2), ), normal_style) current_row += 1 ws.write(current_row, 1, translator('Total'), header_style) ws.write(current_row, 2, total, normal_style) ws.write(current_row, 3, '100%%', normal_style) current_row += 1 state['current_row'] = current_row
def configErrorReporting(headers): """ Configure import exception log, which is an Excel spreadsheet in the same format as the input format, but with an extra column added - "Error", which contains the error message. Can only be called after first row of input Excel spreadsheet is read to initialize the global, "headers" """ dateFmt = easyxf( 'font: name Arial, bold True, height 200;', #'borders: left thick, right thick, top thick, bottom thick;', num_format_str='MM-DD-YYYY' ) headerFmt = easyxf( 'font: name Arial, bold True, height 200;', ) global errorsWorkbook, erroutSheet, erroutRow errorsWorkbook = Workbook() erroutSheet = errorsWorkbook.add_sheet('Import Errors') for colnum in range(0, len(headers)): erroutSheet.write(0, colnum, headers[colnum][0], tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt)) # Add extra column for error message erroutSheet.write(0, len(headers), "Error", headerFmt) erroutSheet.flush_row_data() erroutRow = 1 errorsWorkbook.save('errors.xls')
def report_group_summary_monthly_excel(request,year,month): book = xlwt.Workbook(encoding='utf8') sheet = book.add_sheet('untitled') default_style = xlwt.Style.default_style datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm') date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy') row = 1 student_list = Student.objects.all() for student in student_list: row=row+1 col=0 sheet.write(row, col, "%s %s" % (student.people.name_th,student.people.lastname_th) , style=default_style) activity_group_list = ActivityGroup.objects.filter() for activity_group in activity_group_list: activity_list = Activity.objects.filter(group=activity_group,date__month=month,date__year=year) for activity in activity_list: col=col+1 try : sheet.write(row, col, ("%s" % ActivityReport.objects.get(activity=activity,attendance=student,version=activity.version).transaction_status),style=default_style) except ActivityReport.DoesNotExist: sheet.write(row, col, "-",style=default_style) response = HttpResponse(mimetype='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=report_group_summary_monthly_excel-%s-%s.xls' % (year,month) book.save(response) return response
def predict(self,wave): style = xl.easyxf( get_style(height=210,color='green',bold=True,horizontal='center') ) if not wave.has_subwaves: self.sheet.row(self.row_no).write(3,'No subwaves, cannot predict waves for wave',style) return row_title = self.sheet.row(self.row_no) row_title.write(3, 'Predict p3 and p5 for {}'.format(wave.name), style) self.nextline() p31,p51,p52,p53,p54,p54s,fib31,fib51,fib52,fib53,fib54,fib54s = get_prediction(wave) style_n = xl.easyxf( get_style(bold=False,horizontal='center'))# + 'borders: bottom thin;') style_hl = xl.easyxf( get_style(bold=True,horizontal='center'))# + 'borders: bottom thin;') def write(title1,title2,price1,price2,price3,fib,style=style_n): row = self.sheet.row(self.row_no) row.write(1,title1,style); row.write(2,title2,style); row.write(3,price1,style); row.write(4,price2,style) row.write(5,price3,style); row.write(6,fib,style) self.nextline() style_20 = xl.easyxf( get_style(bold=True,horizontal='center') + 'borders: bottom thin;') #write('p3','2.0*i', p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),(p31-wave.ii.end)/wave.i.size,style=style_20) #write('p5','2.382*i', p51,p51-wave.start,compute_percent(wave.start,p51),(p51-wave.i.start)/wave.i.size) #write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),int((p52-wave.i.start)/wave.i.size) ) #write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),int((p53-wave.i.start)/wave.i.size),style=style_hl) #write('p5','p4+1.0*i', p54,p54-wave.start,compute_percent(wave.start,p54),int((p54-wave.i.start)/wave.i.size)) #write('p5','p4+0.618*iii', p54s,p54s-wave.start,compute_percent(wave.start,p54s),int((p54s-wave.i.start)/wave.i.size) ) write('p3','2.0*i', p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),fib31,style=style_20) write('p5','2.382*i', p51,p51-wave.start,compute_percent(wave.start,p51),fib51) write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),fib52) write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),fib53,style=style_hl) write('p5','p4+1.0*i', p54,p54-wave.start,compute_percent(wave.start,p54),fib54) write('p5','p4+0.618*iii', p54s,p54s-wave.start,compute_percent(wave.start,p54s),fib54s)
def generate_xls(Con): wb = Workbook() ws = wb.add_sheet(sheetname='Order Results') headerStyle = easyxf('font: bold True') wrapStyle = easyxf('alignment: wrap True') ws.write(0, 0, 'Date', headerStyle) ws.write(0, 1, 'Particulars', headerStyle) ws.write(0, 2, 'Amount', headerStyle) ws.write(0, 3, 'Revenue', headerStyle) max_width_col0 = 0 max_width_col1 = 0 i = 1 for order in Con.Orders: ws.write(i, 0, order.orderDate) ws.write(i, 1, order.Particulars, wrapStyle) ws.write(i, 2, float(order.Total_Amount)) ws.write(i, 3, float(order.Revenue_Amount)) for s in order.Particulars.split('\n'): if max_width_col1 < len(s): max_width_col1 = len(s) if max_width_col0 < len(str(order.orderDate)): max_width_col0 = len(str(order.orderDate)) i += 1 ws.col(1).width = 256 * max_width_col1 + 2 ws.col(0).width = 256 * max_width_col0 + 20 ws.write(i, 1, 'Total Amount', headerStyle) ws.write(i, 2, Con.Total_Amount, headerStyle) ws.write(i, 3, Con.Revenue_Amount, headerStyle) filename = Con.Username + '_' + Con.Label + '_' filename += Con.FromDate.strftime('%Y%m%d') + '_' filename += Con.ToDate.strftime('%Y%m%d') + '.xls' wb.save(config.get_main_dir() + '/Output/' + filename)
def check_attr_diff(): attributes = ["datanode_java_heapsize","dfs_datanode_failed_volumes_tolerated","dfs_namenode_handler_count","hbase_hregion_max_filesize","hbase_master_java_heapsize","hbase_regionserver_java_heapsize","io_sort_mb","jobtracker_java_heapsize","mapred_child_java_opts_max_heap","mapred_job_tracker_handler_count","mapred_submit_replication","mapred_tasktracker_map_tasks_maximum","mapred_tasktracker_reduce_tasks_maximum","maxSessionTimeout","namenode_java_heapsize","override_mapred_child_java_opts_max_heap","secondary_namenode_java_heapsize","task_tracker_java_heapsize","override_mapred_compress_map_output","override_mapred_map_tasks_speculative_execution","override_mapred_output_compress","override_mapred_reduce_tasks_speculative_execution"] START_ROW = 1 ATTR_COL = 2 VALUE_COL = 3 w_sheets = [0]*10 rb = open_workbook("CombinedClusterConfigs.xls",formatting_info=True) styles = Styles(rb) r_sheets = rb.sheets() # read only copy of the sheets print r_sheets wb = copy(rb) # a writable copy (I can't read values out of this, only write to it) style1 = xlwt.easyxf('pattern: pattern solid, fore_colour red;') style2 = xlwt.easyxf('pattern: pattern solid, fore_colour orange;') style3 = xlwt.easyxf('pattern: pattern solid, fore_colour blue;') for i in range(0,rb.nsheets): last_attrib = 0 last_value = 0 w_sheets[i] = wb.get_sheet(i) # the sheets to write to within the writable copy for row_index in range(START_ROW, r_sheets[i].nrows): current_attrib = r_sheets[i].cell(row_index, ATTR_COL).value current_value = r_sheets[i].cell(row_index, VALUE_COL).value #print w_sheets[i] if current_attrib == last_attrib: if current_value <> last_value: w_sheets[i].write(row_index, VALUE_COL, r_sheets[i].cell(row_index, VALUE_COL).value, style1) continue for attribute in attributes: if r_sheets[i].cell(row_index, ATTR_COL).value == attribute: last_attrib = r_sheets[i].cell(row_index, ATTR_COL).value last_value = r_sheets[i].cell(row_index, VALUE_COL).value break wb.save("CombinedClusterConfigs.xls")
def print_header_data(self, ws, _p, data, row_position, xlwt, _xs, initial_balance_text): cell_format = _xs['borders_all'] + _xs['wrap'] + _xs['top'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-', None, cell_style_center), ('af', 1, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All'), None, cell_style_center), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('\nTo') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 1, 0, 'text', df, None, cell_style_center), ('tm', 1, 0, 'text', _p.display_partner_account(data), None, cell_style_center), ('pf', 1, 0, 'text', _p.display_target_move(data), None, cell_style_center), ('ib', 1, 0, 'text', initial_balance_text[_p.initial_balance_mode], None, cell_style_center), ('coa', 1, 0, 'text', _p.chart_account.name, None, cell_style_center), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_position = self.xls_write_row(ws, row_position, row_data, row_style=cell_style) return row_position
def styleUpdate(target): rb = open_workbook(target) wb = copy(rb) #style headline = xlwt.easyxf('pattern: pattern solid, fore_color yellow, back_color orange; font: height 260, bold true, color black; align: horizontal center, vertical center, wrap true; border: top thick, bottom double') plain = xlwt.easyxf('font: height 200, color black; align: horizontal center, vertical center; border: top thin, bottom thin, right thin, left thin') date_format = xlwt.XFStyle() date_format.num_format_str = 'yyyy-mm-dd' sheetNumber = len(rb.sheet_names()) #loop through the sheet for num in range(sheetNumber): sheet = wb.get_sheet(num) #looping through columns for colNum in range(1,20): sheet.col(0).width = 256*4 sheet.write(0,colNum,rb.sheet_by_index(num).cell(0,colNum).value,style=headline) sheet.col(colNum).width = 256*15 for rowNum in range(1,len(sheet.rows)): sheet.write(rowNum,colNum,rb.sheet_by_index(num).cell(rowNum,colNum).value,style=plain) #write the content with style sheet.row(0).height_mismatch = True sheet.row(0).height = 256*3 wb.save(target)
def from_data(self, fields, rows, file_address): if file_address: bk = xlrd.open_workbook(file_address, formatting_info=True) workbook = xlutils.copy.copy(bk) worksheet = workbook.get_sheet(0) for i, fieldname in enumerate(fields): self.setOutCell(worksheet, 0, i, fieldname) for row, row_vals in enumerate(rows): for col, col_value in enumerate(row_vals): if isinstance(col_value, basestring): col_value = re.sub("\r", " ", col_value) self.setOutCell(worksheet, col, row + 1, col_value) else: workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet 1') base_style = xlwt.easyxf('align: wrap yes') date_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD') datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS') for row_index, row in enumerate(rows): for cell_index, cell_value in enumerate(row): cell_style = base_style if isinstance(cell_value, basestring): cell_value = re.sub("\r", " ", cell_value) elif isinstance(cell_value, datetime.datetime): cell_style = datetime_style elif isinstance(cell_value, datetime.date): cell_style = date_style worksheet.write(row_index + 1, cell_index, cell_value, cell_style) fp_currency = StringIO.StringIO() workbook.save(fp_currency) fp_currency.seek(0) data = fp_currency.read() fp_currency.close() return data
def generate_excel_output(self, fields, comments): header_style = xlwt.easyxf('font: bold on; align: horiz left;') normal_style = xlwt.easyxf('align: horiz left, vert top;') wb = xlwt.Workbook(encoding='utf-8') ws = wb.add_sheet('Sheet 1') ws.row(0).set_cell_text(0, 'Consultation deadline', header_style) ws.row(0).set_cell_text(1, (self.end_date + 1).strftime('%Y/%m/%d %H:%M'), normal_style) ws.row(1).set_cell_text(0, 'Date of export', header_style) ws.row(1).set_cell_text(1, DateTime().strftime('%Y/%m/%d %H:%M'), normal_style) row = 2 for col in range(len(fields)): ws.row(row).set_cell_text(col, fields[col][0], header_style) for item in comments: row += 1 for col in range(len(fields)): ws.row(row).set_cell_text(col, fields[col][1](item), normal_style) output = StringIO() wb.save(output) return output.getvalue()
def _generate_excel(response, columns, headers, grads): import xlwt book = xlwt.Workbook(encoding='utf-8') sheet = book.add_sheet('Search Results') hdrstyle = xlwt.easyxf('font: bold on; pattern: pattern solid, fore_colour grey25; align: horiz centre') evenstyle = xlwt.easyxf('pattern: back_colour gray40') oddstyle = xlwt.easyxf('pattern: pattern sparse_dots, fore_colour grey25') # header row sheet.write(0, 0, u'Graduate Student Search Results', xlwt.easyxf('font: bold on, height 320')) sheet.row(0).height = 400 for i,hdr in enumerate(headers): sheet.write(1, i, hdr, hdrstyle) # data rows for i,grad in enumerate(grads): style = [oddstyle, evenstyle][i%2] for j,column in enumerate(columns): sheet.write(i+2, j, getattribute(grad, column, html=False), style) # set column widths for i,c in enumerate(columns): wid = COLUMN_WIDTHS[c] sheet.col(i).width = wid count = len(grads) sheet.write(count+4, 0, 'Number of students: %i' % (count)) sheet.write(count+5, 0, 'Report generated: %s' % (datetime.datetime.now())) book.save(response)
def case_writer(BusinessDocument, workbook): """ Reads the all sheets of the business document and writes to the worksheet. External and Internal test cases will written in seperate sheets. @param BusinessDocument: Document to read from. @param workbook: Document to write to @return: None """ # Styling stuff font = xlwt.Font() font.bold = True title_text = xlwt.easyxf("align: vert top, horiz left") title_text.font = font style_text = xlwt.easyxf("align: wrap on, vert top, horiz left") #Create the worksheets for the test cases. worksheet = workbook.add_sheet("Dashboard") # for worksheet in [worksheet_ext, worksheet_int]: # Write the Header row worksheet.write(0, 0, "Test Type", title_text) worksheet.write(0, 1, "HPQC Test Plan Folder name", title_text) worksheet.write(0, 2, "Your User ID", title_text) worksheet.write(0, 3, "Residence Type", title_text) worksheet.write(0, 4, "Test Case ID(Business ReqID+ BR/DFS/SC+screen Name)", title_text) worksheet.write(0, 5, "Test Case Description", title_text) worksheet.write(0, 6, "Pre-Condition", title_text) worksheet.write(0, 7, "Execution", title_text) worksheet.write(0, 8, "Expected Result", title_text) worksheet.write(0, 9, "Release", title_text) worksheet.write(0, 10, "Created by(optional)", title_text) worksheet.write(0, 11, "Creation Date(optional)", title_text) # Size adjustments worksheet.col(0).width = 30 * 70 worksheet.col(1).width = 30 * 200 worksheet.col(2).width = 30 * 70 worksheet.col(3).width = 30 * 70 worksheet.col(4).width = 30 * 180 worksheet.col(5).width = 30 * 270 worksheet.col(6).width = 30 * 70 worksheet.col(7).width = 30 * 256 worksheet.col(8).width = 30 * 220 worksheet.col(9).width = 30 * 70 worksheet.col(10).width = 30 * 70 worksheet.col(11).width = 30 * 100 # # The first sheet is revision history so we don't need it. # business_sheets = BusinessDocument.sheets()[1:] # We're going to write to both the external and internal sheets simultaneously # a will serve as the counter for external, and b for internal c = 1 # Iterate through the business documents, first iteration would be CRA, next YJ etc RuleSheet = BusinessDocument.sheets()[0] for i in range(1, RuleSheet.nrows): # easy references Role = RuleSheet.row_values(i)[0] Table = RuleSheet.row_values(i)[1] Section = RuleSheet.row_values(i)[2] col_list = [] for column in range(3, 11): col_list.append(RuleSheet.row_values(i)[column]) # Setting up the content for the cells Description = ("Verify for the " + Role + ", in the table named:\n\n" + Table + "\n\nUnder the section:\n\n" + Section + "\n\nThe columns are oganized correctly.") columnstring = "" counter = 1 for entry in col_list: if entry == "": break #Nothing to do if its blank columnstring += "\nColumn " + str(counter) + ": " + entry counter += 1 Expected_Result = "The columns are organized as follows:" + columnstring Execution = ( "1) Login as " + Role + "\n2) View the dashboard." + "\n3) Verify the table, section, and column labels are correct." + "\n4) Verify that the columns are organized correctly.") case_id = "SO_Dashboard_Roles_" + str(c) # Now that we have all the information, we can just write it to the correct sheet worksheet.write(c, 0, "Manual", style_text) worksheet.write(c, 1, "*****_SO_Dashboard", style_text) worksheet.write(c, 2, "*****", style_text) worksheet.write(c, 3, "All", style_text) worksheet.write(c, 4, case_id, style_text) worksheet.write(c, 5, Description, style_text) worksheet.write(c, 6, "N/A", style_text) worksheet.write(c, 7, Execution, style_text) worksheet.write(c, 8, Expected_Result, style_text) worksheet.write(c, 9, "2.0", style_text) worksheet.write(c, 10, "Sagar", style_text) worksheet.write(c, 11, now.strftime("%m-%d-%Y"), style_text) worksheet.write c += 1 workbook.save("DashboardRoles_test_Cases.xls")
# instantiate the parser and fed it some HTML professor_list = list() # for i in range(1, 10): parser = MyHTMLParser() f = urllib.urlopen("https://www.eecs.mit.edu/people/faculty-advisors") html = f.read() parser.feed(html) professor_list = parser.professor_list parser.close() print professor_list style0 = xlwt.easyxf('font: bold on') book = xlwt.Workbook() sheet1 = book.add_sheet('Mit') sheet1.write(0, 1, 'First Name', style0) sheet1.write(0, 2, 'Last Name', style0) # sheet1.write(0, 1, 'Title', style0) # sheet1.write(0, 2, 'Phone', style0) sheet1.write(0, 0, 'E-Mail', style0) grid = { 'First Name': 2, 'Last Name': 3, # 'Phone': 2, 'E-Mail': 1
def print_excel(self): if not self.price and not self.text and not self.quantity and not self.measure: raise ValidationError( 'Debe escoger al menos una opción a comparar entre precio, texto, cantidad o línea de medición.' ) # Estilos a usar normal_right = xlwt.easyxf('align: wrap yes, horiz right;') bold_left = xlwt.easyxf('align: wrap yes, horiz left; font: bold on;') bold_right = xlwt.easyxf( 'align: wrap yes, horiz right; font: bold on;') red_left = xlwt.easyxf( 'align: wrap yes, horiz left; font: colour red;') red_bold_left = xlwt.easyxf( 'align: wrap yes, horiz left; font: colour red, bold on;') red_right = xlwt.easyxf( 'align: wrap yes, horiz right; font: colour red;') red_bold_right = xlwt.easyxf( 'align: wrap yes, horiz right; font: colour red, bold on;') separator = xlwt.easyxf( 'pattern: pattern solid, fore_colour periwinkle;') workbook = xlwt.Workbook() sheet = workbook.add_sheet('Comparación') # Anchos sheet.col(0).width = 256 * 20 sheet.col(1).width = 256 * 20 sheet.col(2).width = 256 * 6 sheet.col(3).width = 256 * 6 if self.measure: sheet.col(4).width = 256 * 6 sheet.col(5).width = 256 * 3 sheet.col(6).width = 256 * 5 sheet.col(7).width = 256 * 5 sheet.col(8).width = 256 * 5 sheet.col(9).width = 256 * 6 else: sheet.col(4).width = sheet.col(5).width = sheet.col( 6).width = sheet.col(7).width = sheet.col(8).width = sheet.col( 9).width = 1 sheet.col(10).width = 256 sheet.col(11).width = 256 * 20 sheet.col(12).width = 256 * 6 sheet.col(13).width = 256 * 6 if self.measure: sheet.col(14).width = 256 * 6 sheet.col(15).width = 256 * 3 sheet.col(16).width = 256 * 5 sheet.col(17).width = 256 * 5 sheet.col(18).width = 256 * 5 sheet.col(19).width = 256 * 6 else: sheet.col(14).width = sheet.col(15).width = sheet.col( 16).width = sheet.col(17).width = sheet.col( 18).width = sheet.col(19).width = 1 sheet.col(20).width = 256 sheet.col(21).width = 256 * 20 sheet.col(22).width = 256 * 6 sheet.col(23).width = 256 * 6 if self.measure: sheet.col(24).width = 256 * 6 sheet.col(25).width = 256 * 3 sheet.col(26).width = 256 * 5 sheet.col(27).width = 256 * 5 sheet.col(28).width = 256 * 5 sheet.col(29).width = 256 * 6 # Cabecera sheet.write(0, 2, 'Cant', bold_left) sheet.write(0, 3, 'Prec', bold_left) if self.measure: sheet.write(0, 4, 'Esp', bold_left) sheet.write(0, 5, 'n', bold_right) sheet.write(0, 6, 'x', bold_right) sheet.write(0, 7, 'y', bold_right) sheet.write(0, 8, 'z', bold_right) sheet.write(0, 9, 'total', bold_right) sheet.write(0, 10, '', separator) sheet.write(0, 12, 'Cant', bold_left) sheet.write(0, 13, 'Prec', bold_left) if self.measure: sheet.write(0, 14, 'Esp', bold_left) sheet.write(0, 15, 'n', bold_right) sheet.write(0, 16, 'x', bold_right) sheet.write(0, 17, 'y', bold_right) sheet.write(0, 18, 'z', bold_right) sheet.write(0, 19, 'total', bold_right) sheet.write(0, 20, '', separator) sheet.write(0, 22, 'Cant', bold_left) sheet.write(0, 23, 'Prec', bold_left) if self.measure: sheet.write(0, 24, 'Esp', bold_left) sheet.write(0, 25, 'n', bold_right) sheet.write(0, 26, 'x', bold_right) sheet.write(0, 27, 'y', bold_right) sheet.write(0, 28, 'z', bold_right) sheet.write(0, 29, 'total', bold_right) sheet.write(1, 0, self.origin_budget_id.display_name, bold_left) sheet.write(1, 10, '', separator) sheet.write(1, 11, self.compare_budget_id.display_name, bold_left) sheet.write(1, 20, '', separator) sheet.write(1, 21, 'Diferencia', bold_left) types = ['chapter', 'departure' ] if self.compare == 'both' else [self.compare] row = 2 # Buscamos códigos de conceptos en común (los repetidos dañan todo) for origin_concept in self.origin_budget_id.concept_ids: if origin_concept.type not in types: continue diferences1 = [] diferences2 = [] if self.budget_type == 'budget': origin_quantity = origin_concept.quantity origin_price = origin_concept.amount_fixed if origin_concept.amount_type == 'fixed' else origin_concept.amount_compute else: origin_quantity = origin_concept.quantity_cert origin_price = origin_concept.amount_fixed_cert if origin_concept.amount_type == 'fixed' else origin_concept.amount_compute_cert sheet.write(row, 0, origin_concept.code, bold_left) sheet.write(row, 1, origin_concept.name, bold_left) sheet.write(row, 2, origin_quantity, normal_right) sheet.write(row, 3, origin_price, normal_right) # No olvidemos los separadores sheet.write(row, 10, '', separator) sheet.write(row, 20, '', separator) # Líneas de medición measures = [] if self.measure: for i, line in enumerate(origin_concept.measuring_ids, 1): sheet.write(row + i, 4, line.name) sheet.write(row + i, 5, line.qty, normal_right) sheet.write(row + i, 6, line.length, normal_right) sheet.write(row + i, 7, line.width, normal_right) sheet.write(row + i, 8, line.height, normal_right) sheet.write(row + i, 9, line.amount_subtotal, normal_right) sheet.write(row + i, 10, '', separator) sheet.write(row + i, 20, '', separator) measures.append(line.name) for compare_concept in self.compare_budget_id.concept_ids: if compare_concept.type not in types: continue if origin_concept.code == compare_concept.code: # Hora de buscar diferencias if self.text and origin_concept.name != compare_concept.name: diferences1.append( (compare_concept.name, red_bold_left)) else: diferences1.append((compare_concept.name, bold_left)) diferences2.append( (compare_concept.name, red_bold_left if (origin_concept.sequence != compare_concept.sequence or origin_concept.parent_id.code != compare_concept.parent_id.code) else bold_left)) if self.budget_type == 'budget': compare_quantity = compare_concept.quantity compare_price = compare_concept.amount_fixed if compare_concept.amount_type == 'fixed' else compare_concept.amount_compute else: compare_quantity = compare_concept.quantity_cert compare_price = compare_concept.amount_fixed_cert if compare_concept.amount_type == 'fixed' else compare_concept.amount_compute_cert if self.quantity and origin_quantity != compare_quantity: diferences1.append((compare_quantity, red_right)) diferences2.append( (compare_quantity - origin_quantity, red_right)) else: diferences1.append((compare_quantity, normal_right)) diferences2.append( (compare_quantity - origin_quantity, normal_right)) if self.price and origin_price != compare_price: diferences1.append((compare_price, red_right)) diferences2.append( (compare_price - origin_price, red_right)) else: diferences1.append((compare_price, normal_right)) diferences2.append( (compare_price - origin_price, normal_right)) # Líneas de medición if self.measure: for line in compare_concept.measuring_ids: try: i = measures.index(line.name) + 1 except ValueError: continue sheet.write(row + i, 14, line.name) sheet.write(row + i, 15, line.qty, normal_right) sheet.write(row + i, 16, line.length, normal_right) sheet.write(row + i, 17, line.width, normal_right) sheet.write(row + i, 18, line.height, normal_right) sheet.write(row + i, 19, line.amount_subtotal, normal_right) sheet.write(row + i, 24, line.name) sheet.write(row + i, 25, line.qty, normal_right) sheet.write(row + i, 26, line.length, normal_right) sheet.write(row + i, 27, line.width, normal_right) sheet.write(row + i, 28, line.height, normal_right) sheet.write(row + i, 29, line.amount_subtotal, normal_right) break # Encontré el que coincide en código, no buscaré mas... if diferences1: for i, (value, style) in enumerate(diferences1, 11): sheet.write(row, i, value, style) for i, (value, style) in enumerate(diferences2, 21): sheet.write(row, i, value, style) # pasamos a la siguiente fila row += 1 + (len(origin_concept.measuring_ids) if self.measure else 0) if row == 2: raise ValidationError('No hay conceptos en común para comparar.') # Unos cuantos separadores mas, hasta hacer al menos 50 for i in range(row, 53): sheet.write(i, 10, '', separator) sheet.write(i, 20, '', separator) stream = io.BytesIO() workbook.save(stream) stream.seek(0) filename = 'Comparacion%s_%s_%s.xls' % ( '_certificacion' if self.budget_type == 'cert' else '', self.origin_budget_id.code, self.compare_budget_id.code) attach_vals = { 'name': filename, 'datas': base64.b64encode(stream.getvalue()), 'store_fname': filename, } doc_id = self.env['ir.attachment'].create(attach_vals) return { 'name': filename, 'type': 'ir.actions.act_url', 'url': 'web/content/%d?download=true' % doc_id.id, 'close_on_report_download': True, 'target': 'self', }
# -*- coding: utf-8 -*- import xlwt import datetime from django.forms.forms import pretty_name from django.core.exceptions import ObjectDoesNotExist HEADER_STYLE = xlwt.easyxf('font: bold on') DEFAULT_STYLE = xlwt.easyxf() CELL_STYLE_MAP = ( (datetime.date, xlwt.easyxf(num_format_str='DD/MM/YYYY')), (datetime.time, xlwt.easyxf(num_format_str='HH:MM')), (bool, xlwt.easyxf(num_format_str='BOOLEAN')), ) def multi_getattr(obj, attr, default=None): attributes = attr.split(".") for i in attributes: try: obj = getattr(obj, i) except AttributeError: if default: return default else: raise return obj def get_column_head(obj, name): name = name.rsplit('.', 1)[-1]
def generate_xls_report(self, parser, data, obj, wb): c = parser.localcontext['company'] ws = wb.add_sheet(('Partner Ledger')[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 judul = "PARTNER LEDGER" cols_specs = [ # Headers data ('Title', 7, 0, 'text', lambda x, d, p: judul), ('Kosong', 7, 0, 'text', lambda x, d, p: ""), ('Fiscal Year', 5, 0, 'text', lambda x, d, p: self._display_fiscalyear(p, d)), ('Create Date', 2, 0, 'text', lambda x, d, p: 'Create date: ' + p.formatLang(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),date_time = True)), ('Filter', 7, 0, 'text', lambda x, d, p: self._display_filter(p, d)), # Line ('Date', 1, 65, 'text', lambda x, d, p: p.formatLang(x['date'],date=True)), ('JNRL', 1, 28, 'text', lambda x, d, p: x['code']), ('Ref.', 1, 45, 'text', lambda x, d, p: x['ref']), ('Entry Label', 1, 175, 'text', lambda x, d, p: x['name']), ('Debit', 1, 77, 'number', lambda x, d, p: x['debit']), ('Credit', 1, 75, 'number', lambda x, d, p: x['credit']), ('Balance', 1, 75, 'number', lambda x, d, p: x['progress']), # Partner Total ('Initial Balance', 4, 0, 'text', lambda x, d, p: 'Initial Balance'), ('Initial Balance Debit', 1, 0, 'number', lambda x, d, p: p._get_intial_balance(x)[0][0]), ('Initial Balance Credit', 1, 0, 'number', lambda x, d, p: p._get_intial_balance(x)[0][1]), ('Initial Balance Balance', 1, 0, 'number', lambda x, d, p: p._get_intial_balance(x)[0][2]), # Partner Line ('Partner Name', 4, 0, 'text', lambda x, d, p: '%s%s' % (x.ref and ('%s - ' % (x.ref)) or '', x.name)), ('Partner Debit', 1, 0, 'number', lambda x, d, p: p._sum_debit_partner(x)), ('Partner Credit', 1, 0, 'number', lambda x, d, p: p._sum_credit_partner(x)), ('Partner Balance', 1, 0, 'number', lambda x, d, p: (p._sum_debit_partner(x) - p._sum_credit_partner(x)) or 0.0), ] row_hdr0 = self.xls_row_template(cols_specs, ['Title']) row_hdr5 = self.xls_row_template(cols_specs, ['Kosong']) row_hdr1 = self.xls_row_template(cols_specs, ['Fiscal Year', 'Create Date']) row_hdr2 = self.xls_row_template(cols_specs, ['Filter']) row_hdr6 = self.xls_row_template(cols_specs, ['Kosong']) hdr_line = ['Date', 'JNRL', 'Ref.', 'Entry Label', 'Debit', 'Credit', 'Balance'] hdr_partner_total = ['Partner Name', 'Partner Debit', 'Partner Credit', 'Partner Balance'] hdr_initial_total = [ 'Initial Balance', 'Initial Balance Debit', 'Initial Balance Credit', 'Initial Balance Balance'] row_line = self.xls_row_template(cols_specs, hdr_line) tittle_style = xlwt.easyxf('font: height 240, name Arial Black, colour_index black, bold on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;') subtittle_left_style = xlwt.easyxf('font: height 180, name Arial, colour_index brown, bold on, italic on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;') hdr_style = xlwt.easyxf('pattern: pattern solid, fore_color white;',num_format_str='#,##0.00;(#,##0.00)') row_partner_total = self.xls_row_template(cols_specs, hdr_partner_total) row_initial_total = self.xls_row_template(cols_specs, hdr_initial_total) # Style row_hdr_style = xlwt.easyxf( 'pattern: pattern solid, fore_color white;',num_format_str='#,##0.00;(#,##0.00)') row_partner_style = xlwt.easyxf('font: bold on;' 'borders: bottom thin;',num_format_str='#,##0.00;(#,##0.00)') row_style = xlwt.easyxf(num_format_str='#,##0.00;(#,##0.00)') self.xls_write_row(ws, None, data, parser, 0, row_hdr0, tittle_style) self.xls_write_row(ws, None, data, parser, 1, row_hdr5, hdr_style) self.xls_write_row(ws, None, data, parser, 2, row_hdr1, subtittle_left_style) self.xls_write_row(ws, None, data, parser, 3, row_hdr2, row_hdr_style) self.xls_write_row(ws, None, data, parser, 4, row_hdr6, hdr_style) self.xls_write_row_header(ws, 5, row_partner_total, row_hdr_style) self.xls_write_row_header(ws, 6, row_line, row_hdr_style, set_column_size=True) #self.xls_write_row_header(ws, 4, row_forward_total, row_hdr_style) row_count = 7 ws.horz_split_pos = row_count for p in parser.objects: r = ws.row(row_count) self.xls_write_row(ws, p, data, parser, row_count, row_partner_total, row_partner_style) row_count += 1 if parser.initial_balance: self.xls_write_row(ws, p, data, parser, row_count, row_initial_total, row_style) row_count += 1 for l in parser.lines(p): self.xls_write_row(ws, l, data, parser, row_count, row_line, row_style) row_count += 1 pass
def __init__(self, name, table, rml=False, parser=False, header=True, store=False): super(report_request_tracking_xls, self).__init__( name, table, rml, parser, header, store) # Cell Styles _xs = self.xls_styles # header # Report Column Headers format rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] self.rh_cell_style = xlwt.easyxf(rh_cell_format) self.rh_cell_style_center = xlwt.easyxf( rh_cell_format + _xs['center']) self.rh_cell_style_right = xlwt.easyxf(rh_cell_format + _xs['right']) # Partner Column Headers format fill_blue = 'pattern: pattern solid, fore_color 27;' ph_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] self.ph_cell_style = xlwt.easyxf(ph_cell_format) self.ph_cell_style_decimal = xlwt.easyxf( ph_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # Partner Column Data format pd_cell_format = _xs['borders_all'] self.pd_cell_style = xlwt.easyxf(pd_cell_format) self.pd_cell_style_center = xlwt.easyxf( pd_cell_format + _xs['center']) self.pd_cell_style_date = xlwt.easyxf( pd_cell_format + _xs['left'], num_format_str=report_xls.date_format) self.pd_cell_style_decimal = xlwt.easyxf( pd_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # totals rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] self.rt_cell_style = xlwt.easyxf(rt_cell_format) self.rt_cell_style_right = xlwt.easyxf(rt_cell_format + _xs['right']) self.rt_cell_style_decimal = xlwt.easyxf( rt_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) # XLS Template self.col_specs_template_overview = { 'no': { 'header': [1, 5, 'text', _render("_('No')")], 'lines': [1, 0, 'number', _render("p['no']")], 'totals': [1, 5, 'text', None]}, 'date': { 'header': [1, 22, 'text', _render("_('Tanggal')")], 'lines': [1, 0, 'text', _render("p['date']")], 'totals': [1, 22, 'text', None]}, 'branch_id': { 'header': [1, 22, 'text', _render("_('Cabang')")], 'lines': [1, 0, 'text', _render("p['branch_id']")], 'totals': [1, 22, 'text', _render("_('Total')")]}, 'division': { 'header': [1, 22, 'text', _render("_('Divisi')")], 'lines': [1, 0, 'text', _render("p['division']")], 'totals': [1, 22, 'text', None]}, 'number': { 'header': [1, 22, 'text', _render("_('Number')")], 'lines': [1, 0, 'text', _render("p['number']")], 'totals': [1, 22, 'text', None]}, 'state_par': { 'header': [1, 22, 'text', _render("_('State Number (PAR)')")], 'lines': [1, 0, 'text', _render("p['state_par']")], 'totals': [1, 22, 'text', None]}, 'partner_code': { 'header': [1, 22, 'text', _render("_('Supplier')")], 'lines': [1, 0, 'text', _render("p['partner_code']")], 'totals': [1, 22, 'text', None]}, 'partner_name': { 'header': [1, 22, 'text', _render("_('Nama Supplier')")], 'lines': [1, 0, 'text', _render("p['partner_name']")], 'totals': [1, 22, 'text', None]}, 'create_by': { 'header': [1, 22, 'text', _render("_('Created By')")], 'lines': [1, 0, 'text', _render("p['create_by']")], 'totals': [1, 22, 'text', None]}, 'approved_by': { 'header': [1, 22, 'text', _render("_('Approved By')")], 'lines': [1, 0, 'text', _render("p['approved_by']")], 'totals': [1, 22, 'text', None]}, 'par_type': { 'header': [1, 22, 'text', _render("_('Payment Request Type')")], 'lines': [1, 0, 'text', _render("p['par_type']")], 'totals': [1, 22, 'text', None]}, 'spa_name': { 'header': [1, 22, 'text', _render("_('No. Supplier Payment')")], 'lines': [1, 0, 'text', _render("p['spa_name']")], 'totals': [1, 22, 'text', None]}, 'spa_amount': { 'header': [1, 22, 'text', _render("_('Amount Supplier Payment')")], 'lines': [1, 0, 'number', _render("p['spa_amount']"), None, self.pd_cell_style_decimal], 'totals': [1, 22, 'number', _render("p['spa_amount']"), None, self.rt_cell_style_decimal]}, 'spa_method': { 'header': [1, 22, 'text', _render("_('Payment Method')")], 'lines': [1, 0, 'text', _render("p['spa_method']")], 'totals': [1, 22, 'text', None]}, 'a_code':{ 'header': [1, 22, 'text', _render("_('No. Account')")], 'lines': [1, 0, 'text', _render("p['a_code']")], 'totals': [1, 22, 'text', None]}, 'a_name':{ 'header': [1, 22, 'text', _render("_('Nama Account')")], 'lines': [1, 0, 'text', _render("p['a_name']")], 'totals': [1, 22, 'text', None]}, 'aa_combi' :{ 'header': [1, 22, 'text', _render("_('Analytic Combination')")], 'lines': [1, 0, 'text', _render("p['aa_combi']")], 'totals': [1, 22, 'number', None]}, 'aa_company' :{ 'header': [1, 22, 'text', _render("_('Analytic Company')")], 'lines': [1, 0, 'text', _render("p['aa_company']")], 'totals': [1, 22, 'number', None]}, 'aa_bisnisunit' :{ 'header': [1, 22, 'text', _render("_('Analytic Bisnis Unit')")], 'lines': [1, 0, 'text', _render("p['aa_bisnisunit']")], 'totals': [1, 22, 'number', None]}, 'aa_branch' :{ 'header': [1, 22, 'text', _render("_('Analytic Branch')")], 'lines': [1, 0, 'text', _render("p['aa_branch']")], 'totals': [1, 22, 'number', None]}, 'aa_costcenter' :{ 'header': [1, 22, 'text', _render("_('Analytic Cost Center')")], 'lines': [1, 0, 'text', _render("p['aa_costcenter']")], 'totals': [1, 22, 'number', None]}, 'approved_date': { 'header': [1, 22, 'text', _render("_('Approved On')")], 'lines': [1, 0, 'text', _render("p['approved_date']")], 'totals': [1, 22, 'text', None]}, 'nomor_cba': { 'header': [1, 22, 'text', _render("_('No. Clearing Bank')")], 'lines': [1, 0, 'text', _render("p['nomor_cba']")], 'totals': [1, 22, 'text', None]}, 'nomor_giro': { 'header': [1, 22, 'text', _render("_('Nomor Giro')")], 'lines': [1, 0, 'text', _render("p['nomor_giro']")], 'totals': [1, 22, 'text', None]}, 'date_due': { 'header': [1, 22, 'text', _render("_('Tgl Jatuh Tempo')")], 'lines': [1, 0, 'text', _render("p['date_due']")], 'totals': [1, 22, 'text', None]}, 'status_cair': { 'header': [1, 22, 'text', _render("_('Cair / Belum Cair')")], 'lines': [1, 0, 'text', _render("p['status_cair']")], 'totals': [1, 22, 'text', None]}, 'memo': { 'header': [1, 22, 'text', _render("_('Memo')")], 'lines': [1, 0, 'text', _render("p['memo']")], 'totals': [1, 0, 'text', None]}, 'ref': { 'header': [1, 22, 'text', _render("_('Ref')")], 'lines': [1, 0, 'text', _render("p['ref']")], 'totals': [1, 22, 'text', None]}, 'description': { 'header': [1, 22, 'text', _render("_('Description')")], 'lines': [1, 0, 'text', _render("p['description']")], 'totals': [1, 22, 'text', None]}, 'nama_account': { 'header': [1, 22, 'text', _render("_('Account')")], 'lines': [1, 0, 'text', _render("p['nama_account']")], 'totals': [1, 22, 'text', None]}, 'no_btr': { 'header': [1, 22, 'text', _render("_('No HO to Branch')")], 'lines': [1, 0, 'text', _render("p['no_btr']")], 'totals': [1, 22, 'text', None]}, 'no_bta': { 'header': [1, 22, 'text', _render("_('No BTA')")], 'lines': [1, 0, 'text', _render("p['no_bta']")], 'totals': [1, 22, 'text', None]}, 'date_btr': { 'header': [1, 22, 'text', _render("_('Date HO to Branch')")], 'lines': [1, 0, 'text', _render("p['date_btr']")], 'totals': [1, 22, 'text', None]}, 'date_bta': { 'header': [1, 22, 'text', _render("_('Date BTA')")], 'lines': [1, 0, 'text', _render("p['date_bta']")], 'totals': [1, 22, 'text', None]}, 'state_btr': { 'header': [1, 22, 'text', _render("_('State HO to Branch')")], 'lines': [1, 0, 'text', _render("p['state_btr']")], 'totals': [1, 22, 'text', None]}, 'state_bta': { 'header': [1, 22, 'text', _render("_('State BTA')")], 'lines': [1, 0, 'text', _render("p['state_bta']")], 'totals': [1, 22, 'text', None]}, 'no_withdrawal': { 'header': [1, 22, 'text', _render("_('No Withdrawal')")], 'lines': [1, 0, 'text', _render("p['no_withdrawal']")], 'totals': [1, 22, 'text', None]}, 'date_withdrawal': { 'header': [1, 22, 'text', _render("_('Date Withdrawal')")], 'lines': [1, 0, 'text', _render("p['date_withdrawal']")], 'totals': [1, 22, 'text', None]}, 'state_withdrawal': { 'header': [1, 22, 'text', _render("_('State Withdrawal')")], 'lines': [1, 0, 'text', _render("p['state_withdrawal']")], 'totals': [1, 22, 'text', None]}, 'total': { 'header': [1, 22, 'text', _render("_('Total')")], 'lines': [1, 0, 'number', _render("p['total']"), None, self.pd_cell_style_decimal], 'totals': [1, 22, 'number', _render("p['total']"), None, self.rt_cell_style_decimal]}, 'net_total': { 'header': [1, 22, 'text', _render("_('Net Total')")], 'lines': [1, 0, 'number', _render("p['net_total']"), None, self.pd_cell_style_decimal], 'totals': [1, 22, 'number', _render("p['net_total']"), None, self.rt_cell_style_decimal]}, } # XLS Template self.col_specs_template_details = { }
def generate_xls_report(self, _p, _xs, data, objects, wb): wanted_list_overview = _p.wanted_list_overview wanted_list_details = _p.wanted_list_details self.col_specs_template_overview.update(_p.template_update_overview) self.col_specs_template_details.update(_p.template_update_details) _ = _p._ for r in _p.reports: title_short = r['title_short'].replace('/', '-') ws_o = wb.add_sheet(title_short) for ws in [ws_o]: ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos_o = 0 row_pos_d = 0 # set print header/footer for ws in [ws_o]: ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # Title ## Company ## cell_style = xlwt.easyxf(_xs['left']) report_name = ' '.join( [_p.company.name, r['title'], _p.report_info]) c_specs_o = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs_o, ['report_name']) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=cell_style) ## Text + Tgl ## cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' '.join( [_('LAPORAN User Request Tracking Per Tanggal'), _(str(datetime.today().date())), _p.report_info]) c_specs_o = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs_o, ['report_name']) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=cell_style) ## Tanggal Jtp Start Date & End Date ## cell_style = xlwt.easyxf(_xs['left']) report_name = ' '.join( [_('Tanggal Jatuh Tempo'), _('-' if data['start_date'] == False else str(data['start_date'])), _('s/d'), _('-' if data['end_date'] == False else str(data['end_date'])), _p.report_info]) c_specs_o = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs_o, ['report_name']) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=cell_style) ## Tanggal Trx Start Date & End Date ## cell_style = xlwt.easyxf(_xs['left']) report_name = ' '.join( [_('Tanggal Transaksi'), _('-' if data['trx_start_date'] == False else str(data['trx_start_date'])), _('s/d'), _('-' if data['trx_end_date'] == False else str(data['trx_end_date'])), _p.report_info]) c_specs_o = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs_o, ['report_name']) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=cell_style) row_pos_o += 1 # Report Column Headers c_specs_o = map( lambda x: self.render( x, self.col_specs_template_overview, 'header', render_space={'_': _p._}), wanted_list_overview) row_data = self.xls_row_template( c_specs_o, [x[0] for x in c_specs_o]) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=self.rh_cell_style, set_column_size=True) ws_o.set_horz_split_pos(row_pos_o) row_data_begin = row_pos_o # Columns and Rows no = 0 for p in r['id_ai']: c_specs_o = map( lambda x: self.render( x, self.col_specs_template_overview, 'lines'), wanted_list_overview) for x in c_specs_o : if x[0] == 'no' : no += 1 x[4] = no row_data = self.xls_row_template( c_specs_o, [x[0] for x in c_specs_o]) row_pos_o = self.xls_write_row( ws_o, row_pos_o, row_data, row_style=self.pd_cell_style) row_data_end = row_pos_o # Totals ws_o.write(row_pos_o, 0, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style) ws_o.write(row_pos_o, 2, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 3, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 4, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 5, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 6, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 7, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 8, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 9, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 10, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 11, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 12, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 13, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 14, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 15, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 16, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 17, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 18, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 19, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 20, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 21, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 22, xlwt.Formula("SUM(W"+str(row_data_begin)+":W"+str(row_data_end)+")"), self.rt_cell_style_decimal) ws_o.write(row_pos_o, 23, xlwt.Formula("SUM(X"+str(row_data_begin)+":X"+str(row_data_end)+")"), self.rt_cell_style_decimal) ws_o.write(row_pos_o, 24, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 25, xlwt.Formula("SUM(Z"+str(row_data_begin)+":Z"+str(row_data_end)+")"), self.rt_cell_style_decimal) ws_o.write(row_pos_o, 26, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 27, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 28, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 29, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 30, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 31, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 32, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 33, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 34, None, self.rt_cell_style_decimal) ws_o.write(row_pos_o, 35, None, self.rt_cell_style_decimal) # Footer ws_o.write(row_pos_o + 1, 0, None) ws_o.write(row_pos_o + 2, 0, _p.report_date + ' ' + str(self.pool.get('res.users').browse(self.cr, self.uid, self.uid).name))
) CELL_TYPES = { xlrd.XL_CELL_BLANK: fields.TextField, xlrd.XL_CELL_DATE: fields.DatetimeField, xlrd.XL_CELL_ERROR: None, xlrd.XL_CELL_TEXT: fields.TextField, xlrd.XL_CELL_BOOLEAN: fields.BoolField, xlrd.XL_CELL_EMPTY: None, xlrd.XL_CELL_NUMBER: fields.FloatField, } # TODO: add more formatting styles for other types such as currency # TODO: styles may be influenced by locale FORMATTING_STYLES = { fields.DateField: xlwt.easyxf(num_format_str="yyyy-mm-dd"), fields.DatetimeField: xlwt.easyxf(num_format_str="yyyy-mm-dd hh:mm:ss"), fields.PercentField: xlwt.easyxf(num_format_str="0.00%"), } def _python_to_xls(field_types): def convert_value(field_type, value): data = {} if field_type in FORMATTING_STYLES: data["style"] = FORMATTING_STYLES[field_type] if field_type in ( fields.BinaryField, fields.BoolField, fields.DateField,
sheet1.write(1, 0, "Irms [A]") sheet1.write(2, 0, "I1 [A]") # Fundamental sheet1.write(3, 0, "THDi [%]") sheet1.write(4, 0, "PWHC/I1 [%]") sheet1.write(1, 1, rmsI) sheet1.write(2, 1, RMSI[1]) sheet1.write(3, 1, THDI) sheet1.write(4, 1, 100 * PWHC / RMSI[1]) sheet1.write(1, 2, "RMS Current") sheet1.write(2, 2, "RMS Fundamental Current") sheet1.write(3, 2, "Total Harmonic Current Distortion") sheet1.write(4, 2, "Partial Weighted Harmonic Current") sheet1.write(6, 0, "Harmonic content", easyxf('font:bold 1')) sheet1.write(6, 1, "[%]", easyxf('font:bold 1')) sheet1.write(6, 2, "[A rms]", easyxf('font:bold 1')) index = [ 'I1', 'I2', 'I3', 'I4', 'I5', 'I6', 'I7', 'I8', 'I9', 'I10', 'I11', 'I12', 'I13', 'I14', 'I15', 'I16', 'I17', 'I18', 'I19', 'I20', 'I21', 'I22', 'I23', 'I24', 'I25', 'I26', 'I27', 'I28', 'I29', 'I30', 'I31', 'I32', 'I33', 'I34', 'I35', 'I36', 'I37', 'I38', 'I39', 'I40' ] i = 6 for n in index: i = i + 1 if i % 2 == 0:
import xlwt import random from datetime import datetime style1 = xlwt.easyxf('font: name Arial, color-index blue, italic on', num_format_str='#,##0') style2 = xlwt.easyxf(num_format_str='YYYY-MM-DD-HH-MM-SS') wb = xlwt.Workbook() ws = wb.add_sheet('Generated by Python') for row in range(0, 10000): ws.write(row, 0, datetime.now(), style2) for column in range(1, 11): ws.write(row, column, xlwt.Formula('randbetween(1000,100000)')) i = 1 while i <= 20: wb.add_sheet('Generated by Python - ' + str(i)) i = i + 1 wb.save('007_' + str(random.randint(10000, 99999)) + '.xls')
def writeCatalogsToSpreadsheet(grouping, webSiteName, catalogs, outputDir): # Each location is a new sheet in the overall spreadsheet. for currentCatalogName, currentCatalog in catalogs.iteritems(): # Extract the details of interest to use for the file path and name. storeLocation = currentCatalogName.split("-")[1] catalogDate = currentCatalog.mValidFrom # Create the output dir so it has a scrape directory. currentOutputDir = createOutputDir(outputDir, [ "XLSX", webSiteName + "-" + storeLocation, ]) # Create the path to the file. pathToFile = createPathToFile(currentOutputDir, webSiteName, storeLocation, catalogDate) # Must create a new name if the file already exists. SPREAD_SHEET_NAME, uniqueId = createFileName(pathToFile, ".xls") # Store the unique ID so that we can place all the images for this # catalog into same directory. currentCatalog.mUniqueId = uniqueId # Create a new work book. book = xlwt.Workbook(encoding="utf8") # Might be nice to create a nice colour. xlwt.add_palette_colour("custom_grey", 0x21) book.set_colour_RGB(0x21, 128, 128, 128) # Styles to be used in spreadsheet. headerStyle = xlwt.easyxf(HEADER_FONT + \ HEADER_PATTERN + \ HEADER_ALIGN) bodyStyle = xlwt.easyxf(BODY_FONT) bodyStyleCenter = xlwt.easyxf(BODY_FONT_CENTER) # Spreadsheet has a limit of 31 characters for a sheet's name. currentSheetName = currentCatalogName.split( "-")[0] + currentCatalogName.split("-")[1] currentSheet = book.add_sheet(currentSheetName[:31]) # Keep track of the current active row in the spreadsheet. activeRow = 0 # Write out the Title information. for titleName, titleDetails in TITLES.iteritems(): # Populate the title text. currentSheet.write(activeRow, titleDetails["ColumnNum"], titleName.upper(), headerStyle) # Set the header to be a little wider 25% than just the title. titleDetails["ColumnMaxWidth"] = int(len(titleName) * 1.25) # Set the title row height. (Magic 2 for a fuller effect) currentSheet.row( activeRow).height = POINTS_PER_CHAR_HEIGHT * FONT_SIZE * 2 # Write out the catalog items for each page in the catalog. for currentPage, currentItems in sorted( currentCatalog.mPages.iteritems()): # Sort items from left most pixel postion to right most pixel position. # If two store times at the same left pixel position then sort from # top most pixel postion to bottom most pixel position. for currentItem in sorted(currentItems, key=lambda x: (-x.mTop, x.mLeft)): # Only write the row if there is a valid title. if not currentItem.mTitleCleansed: continue # Price of current item. itemPrice = 0 # Bump the current active row just before we write. activeRow += 1 # Set the height of the row. currentSheet.row(activeRow).height = int( POINTS_PER_CHAR_HEIGHT * FONT_SIZE * 1.5) # Write data for the current data item to the current spreadsheet page. # Customer wanted to call the JSON title as the description, so renamed it here. writeDataToSheet(currentSheet, activeRow, TITLES["Complete Description"], currentItem.mTitleCleansed, bodyStyle) writeDataToSheet(currentSheet, activeRow, TITLES["Catalog Page"], currentPage, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Promotion Type"], currentItem.mPromotionType, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Multibuy Qty"], currentItem.mMultibuy, bodyStyleCenter) # if currentItem.mPromotionType == 0: # writeDataToSheet(currentSheet, activeRow, # TITLES["Price"], "XXXX", bodyStyle) # else: writeDataToSheet(currentSheet, activeRow, TITLES["Price"], currentItem.mPriceCleansed, bodyStyle) writeDataToSheet(currentSheet, activeRow, TITLES["Department/Category"], currentItem.mCategory, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Valid Start Date"], currentItem.mStartDate, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Item Type"], currentItem.mType, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Description"], currentItem.mTitle, bodyStyle) # Customer wanted to calle the JSON description as the details, so renamed it here. writeDataToSheet(currentSheet, activeRow, TITLES["Details"], currentItem.mDescription, bodyStyle) writeDataToSheet(currentSheet, activeRow, TITLES["Valid End Date"], currentItem.mEndDate, bodyStyleCenter) writeDataToSheet(currentSheet, activeRow, TITLES["Price Raw"], currentItem.mPrice, bodyStyle) # Update the width of the columns. for titleName, titleDetails in TITLES.iteritems(): # Don't let the columns get too wide, as it does not look very nice. COL_WIDTH = 50 if TITLES[titleName]["ColumnMaxWidth"] < COL_WIDTH: currentSheet.col( TITLES[titleName]["ColumnNum"] ).width = 256 * TITLES[titleName]["ColumnMaxWidth"] else: currentSheet.col( TITLES[titleName]["ColumnNum"]).width = 256 * COL_WIDTH # Save out workbook to disk. book.save(SPREAD_SHEET_NAME) return
# -*- coding: utf-8 -*- from __builtin__ import xrange import itertools from odoo import api, fields, models, _ import xlwt from StringIO import StringIO import base64 from datetime import datetime, timedelta style_title = xlwt.easyxf( 'font: bold 1, name Tahoma, height 160;' 'align: vertical center, horizontal center, wrap on;' 'borders: left thin, right thin, top thin, bottom thin;' 'pattern: pattern solid, pattern_fore_colour gray25, pattern_back_colour black;' ) style_filter = xlwt.easyxf( 'font: bold 1, name Tahoma, height 220;' 'align: vertical center, horizontal center, wrap on;') style_footer = xlwt.easyxf( 'font: bold 1, name Tahoma, height 160;' 'align: vertical center, horizontal center, wrap on;') base_style = xlwt.easyxf('align: wrap yes') class CompareProduct(models.TransientModel): _name = 'compare.product' stock_warehouse = fields.Many2one('stock.warehouse', 'Warehouse', required=True)
def make_excel(ifile, ofile, scenario): tech = defaultdict(list) tech_set = set() sector = set() period = [] emiss = set() row = 0 count = 0 sheet = [] book = [] book_no = 0 flag = None flag1 = None flag2 = None i = 0 # Sheet ID header = ['Technologies', ] header_emiss = [] header_v = ['Technologies', 'Output Name', 'Vintage', 'Cost'] tables = {"Output_VFlow_Out" : ["Activity", "vflow_out"], "Output_CapacityByPeriodAndTech" : ["Capacity", "capacity"], "Output_Emissions" : ["Emissions", "emissions"], "Output_Costs" : ["Costs", "output_cost"]} if ifile is None : raise "You did not specify the input file, remember to use '-i' option" print "Use as :\n python DB_to_Excel.py -i <input_file> (Optional -o <output_excel_file_name_only>)\n Use -h for help." sys.exit(2) else : file_type = re.search(r"(\w+)\.(\w+)\b", ifile) # Extract the input filename and extension if not file_type : print "The file type %s is not recognized. Use a db file." % ifile sys.exit(2) if ofile is None : ofile = file_type.group(1) print "Look for output in %s_*.xls" % ofile con = sqlite3.connect(ifile) cur = con.cursor() # a database cursor is a control structure that enables traversal over the records in a database con.text_factory = str #this ensures data is explored with the correct UTF-8 encoding for k in tables.keys() : if not scenario : cur.execute("SELECT DISTINCT scenario FROM "+k) for val in cur : scenario.add(val[0]) for axy in cur.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='technologies';") : if axy[0] : fields = [ads[1] for ads in cur.execute('PRAGMA table_info(technologies)')] if 'sector' in fields : cur.execute("SELECT sector FROM technologies") for val in cur : sector.add(val[0]) if not sector : sector.add('0') else : flag = 1 if flag is None : cur.execute("SELECT DISTINCT tech FROM "+k) for val in cur : tech['0'].append(val[0]) tech_set.add(val[0]) else : for x in sector : cur.execute("SELECT DISTINCT tech FROM technologies WHERE sector is '"+x+"'") for val in cur : if val[0] not in tech[x] : tech[x].append(val[0]) tech_set.add(val[0]) if k is "Output_Emissions" : cur.execute("SELECT DISTINCT emissions_comm FROM "+k) for val in cur : emiss.add(val[0]) if k is "Output_Costs" : pass else:#if k is not "Output_V_Capacity": cur.execute("SELECT DISTINCT t_periods FROM "+k) for val in cur : val = str(val[0]) if val not in period : period.append(val) header.append(val) header[1:].sort() period.sort() header_emiss = header[:] header_emiss.insert(1, "Emission Commodity") ostyle = easyxf('alignment: vertical centre, horizontal centre;') ostyle_header = easyxf('alignment: vertical centre, horizontal centre, wrap True;') for scene in scenario : book.append(xlwt.Workbook(encoding="utf-8")) for z in sector : for a in tables.keys() : if z is '0' : sheet_name = str(tables[a][0]) if a is "Output_Costs" : flag2 = '1' if a is "Output_Emissions" : flag1 = '1' elif (a is "Output_Costs" and flag2 is None) : sheet_name = str(tables[a][0]) flag2 = '1' elif (a is "Output_Emissions" and flag1 is None) : sheet_name = str(tables[a][0]) flag1 = '1' elif (a is "Output_Costs" and flag2 is not None) or (a is "Output_Emissions" and flag1 is not None) : continue else : sheet_name = str(tables[a][0])+"_"+str(z) sheet.append(book[book_no].add_sheet(sheet_name)) if a is "Output_Emissions" and flag1 is '1': for col in range(0, len(header_emiss)) : sheet[i].write(row, col, header_emiss[col], ostyle_header) sheet[i].col(col).width_in_pixels = 3300 row += 1 for x in tech_set : for q in emiss : sheet[i].write(row, 0, x, ostyle) sheet[i].write(row, 1, q, ostyle) for y in period : cur.execute("SELECT sum("+tables[a][1]+") FROM "+a+" WHERE t_periods is '"+y+"' and scenario is '"+scene+"' and tech is '"+x+"' and emissions_comm is '"+q+"'") xyz = cur.fetchone() if xyz[0] is not None : sheet[i].write(row, count+2, float(xyz[0]), ostyle) else : sheet[i].write(row, count+2, '-', ostyle) count += 1 row += 1 count = 0 row = 0 i += 1 flag1 = '2' elif a is "Output_Costs" and flag2 is '1': for col in range(0, len(header_v)) : sheet[i].write(row, col, header_v[col], ostyle_header) sheet[i].col(col).width_in_pixels = 3300 row += 1 for x in tech_set : cur.execute("SELECT output_name, vintage, "+tables[a][1]+" FROM "+a+" WHERE scenario is '"+scene+"' and tech is '"+x+"'") for xyz in cur : if xyz[0] is not None : sheet[i].write(row, 0, x, ostyle) sheet[i].write(row, count+1, xyz[0], ostyle) sheet[i].write(row, count+2, xyz[1], ostyle) sheet[i].write(row, count+3, xyz[2], ostyle) else : sheet[i].write(row, 0, x, ostyle) sheet[i].write(row, count+1, '-', ostyle) sheet[i].write(row, count+2, '-', ostyle) sheet[i].write(row, count+3, '-', ostyle) row += 1 count = 0 row = 0 i += 1 flag2 = '2' elif (a is "Output_Costs" and flag2 is '2') or (a is "Output_Emissions" and flag1 is '2'): pass elif a is not "Output_V_Capacity": for col in range(0, len(header)) : sheet[i].write(row, col, header[col], ostyle_header) sheet[i].col(col).width_in_pixels = 3300 row += 1 for x in tech[z] : sheet[i].write(row, 0, x, ostyle) for y in period : cur.execute("SELECT sum("+tables[a][1]+") FROM "+a+" WHERE t_periods is '"+y+"' and scenario is '"+scene+"' and tech is '"+x+"'") xyz = cur.fetchone() if xyz[0] is not None : sheet[i].write(row, count+1, float(xyz[0]), ostyle) else : sheet[i].write(row, count+1, '-', ostyle) count += 1 row += 1 count = 0 row = 0 i += 1 if len(scenario) is 1: book[book_no].save(ofile+".xls") else : book[book_no].save(ofile+"_"+scene+".xls") book_no += 1 flag1 = None flag2 = None cur.close() con.close()
def distribute(request): result = [[0 for i in range(6)] for j in range(7)] time1_2 = [] time3_4 = [] time5_6 = [] time7_8 = [] time9_10 = [] time11_12 = [] is_time1_2_free = True is_time3_4_free = True is_time5_6_free = True is_time7_8_free = True is_time9_10_free = True is_time11_12_free = True student_grade = {} result_tmp = [] free_student = [] if request.POST: if request.POST['submit'] == u"自动分配": for is_single_week in range(0, 2): for week_day in range(1, 8): time1_2 = [] time3_4 = [] time5_6 = [] time7_8 = [] time9_10 = [] time11_12 = [] is_time1_2_free = True is_time3_4_free = True is_time5_6_free = True is_time7_8_free = True is_time9_10_free = True is_time11_12_free = True for member in Member.objects.all(): if member.stuID[3] != '4': continue is_time1_2_free = True is_time3_4_free = True is_time5_6_free = True is_time7_8_free = True is_time9_10_free = True is_time11_12_free = True student = Students.objects.filter(stu_no=member.stuID) student_grade[student[0].name] = 0 courseTable = CourseTable.objects.filter( student=student) for ct in courseTable: courses = Course.objects.filter( course_id=ct.course_id) for course in courses: if course.weekday != '0': if is_single_week: if (course.weekday[1] == str(week_day) ) and (course.weekday[0] == '0' or course.weekday[0] == '1'): course_time = course.time.strip( "[']") masses = course_time.split(",") for mass in masses: if mass == '1' or mass == '2': is_time1_2_free = False if mass == '3' or mass == '4': is_time3_4_free = False if mass == '5' or mass == '6': is_time5_6_free = False if mass == '7' or mass == '8': is_time7_8_free = False if mass == '9' or mass == '10': is_time9_10_free = False if mass == '11' or mass == '12': is_time11_12_free = False else: if (course.weekday[1] == str(week_day) ) and (course.weekday[0] == '0' or course.weekday[0] == '2'): course_time = course.time.strip( "[']") masses = course_time.split(",") for mass in masses: if mass == '1' or mass == '2': is_time1_2_free = False if mass == '3' or mass == '4': is_time3_4_free = False if mass == '5' or mass == '6': is_time5_6_free = False if mass == '7' or mass == '8': is_time7_8_free = False if mass == '9' or mass == '10': is_time9_10_free = False if mass == '11' or mass == '12': is_time11_12_free = False if is_time1_2_free: time1_2.append(student[0].name) if is_time3_4_free: time3_4.append(student[0].name) if is_time5_6_free: time5_6.append(student[0].name) if is_time7_8_free: time7_8.append(student[0].name) if is_time9_10_free: time9_10.append(student[0].name) if is_time11_12_free: time11_12.append(student[0].name) if is_single_week == 0: result[week_day - 1][0] = time1_2 result[week_day - 1][1] = time3_4 result[week_day - 1][2] = time5_6 result[week_day - 1][3] = time7_8 result[week_day - 1][4] = time9_10 result[week_day - 1][5] = time11_12 else: for each_student in time1_2: if each_student not in result[week_day - 1][0]: result[week_day - 1][0].append(each_student + u"(单周)") for each_student in time3_4: if each_student not in result[week_day - 1][1]: result[week_day - 1][1].append(each_student + u"(单周)") for each_student in time5_6: if each_student not in result[week_day - 1][2]: result[week_day - 1][2].append(each_student + u"(单周)") for each_student in time7_8: if each_student not in result[week_day - 1][3]: result[week_day - 1][3].append(each_student + u"(单周)") for each_student in time9_10: if each_student not in result[week_day - 1][4]: result[week_day - 1][4].append(each_student + u"(单周)") for each_student in time11_12: if each_student not in result[week_day - 1][5]: result[week_day - 1][5].append(each_student + u"(单周)") for index, each_student in enumerate(result[week_day - 1][0]): if each_student.replace(u"(单周)", "") not in time1_2: result[week_day - 1][0][index] = result[ week_day - 1][0][index] + u"(双周)" for index, each_student in enumerate(result[week_day - 1][1]): if each_student.replace(u"(单周)", "") not in time3_4: result[week_day - 1][1][index] = result[ week_day - 1][1][index] + u"(双周)" for index, each_student in enumerate(result[week_day - 1][2]): if each_student.replace(u"(单周)", "") not in time5_6: result[week_day - 1][2][index] = result[ week_day - 1][2][index] + u"(双周)" for index, each_student in enumerate(result[week_day - 1][3]): if each_student.replace(u"(单周)", "") not in time7_8: result[week_day - 1][3][index] = result[ week_day - 1][3][index] + u"(双周)" for index, each_student in enumerate(result[week_day - 1][4]): if each_student.replace(u"(单周)", "") not in time9_10: result[week_day - 1][4][index] = result[ week_day - 1][4][index] + u"(双周)" for index, each_student in enumerate(result[week_day - 1][5]): if each_student.replace(u"(单周)", "") not in time11_12: result[week_day - 1][5][index] = result[ week_day - 1][5][index] + u"(双周)" result_tmp = copy.deepcopy(result) for i in range(0, 5): for j in range(1, 4): for each_student in result[i][j]: if each_student.find("(") != -1: each_student = each_student.replace(u"(双周)", "") each_student = each_student.replace(u"(单周)", "") student_grade[each_student] = student_grade[ each_student] + 0.5 else: student_grade[ each_student] = student_grade[each_student] + 1 for key in student_grade: print key + "=" + str(student_grade[key]) selected_student = [] tmp = [] for i in range(0, 5): for j in range(1, 4): tmp.append((i, j, len(result[i][j]))) #sort for i in range(0, len(tmp)): for j in range(i + 1, len(tmp)): if tmp[i][2] > tmp[j][2]: tmp[i], tmp[j] = tmp[j], tmp[i] for i in range(0, 5): for j in range(1, 4): for m in range(0, len(result[i][j])): for n in range(m + 1, len(result[i][j])): tmp_m = result[i][j][m].replace(u"(双周)", "") tmp_m = tmp_m.replace(u"(单周)", "") tmp_n = result[i][j][n].replace(u"(双周)", "") tmp_n = tmp_n.replace(u"(单周)", "") if student_grade[tmp_m] > student_grade[tmp_n]: result[i][j][m], result[i][j][n] = result[i][ j][n], result[i][j][m] print tmp #distribute for i in range(0, len(tmp)): if len(result[tmp[i][0]][tmp[i][1]]) <= 1: for each in result[tmp[i][0]][tmp[i][1]]: tmp_each = each.replace(u"(双周)", "") tmp_each = tmp_each.replace(u"(单周)", "") if tmp_each not in selected_student: selected_student.append(tmp_each) else: count = 0 tmp_list = [] for each in result[tmp[i][0]][tmp[i][1]]: tmp_each = each.replace(u"(双周)", "") tmp_each = tmp_each.replace(u"(单周)", "") if tmp_each not in selected_student: if count == 1: if (each.find(u"(双周)") != -1 and tmp_list[0].find(u"(双周)") != -1 ) or (each.find(u"(单周)") != -1 and tmp_list[0].find(u"(单周)") != -1): continue selected_student.append(tmp_each) tmp_list.append(each) count = count + 1 if count == 2: break result[tmp[i][0]][tmp[i][1]] = tmp_list for i in range(0, 5): result[i][0] = [] for member in Member.objects.all(): student = Students.objects.filter(stu_no=member.stuID) if (student[0].name not in selected_student ) and student[0].stu_no[3] == '4': free_student.append(student[0].name) book = xlwt.Workbook(encoding='utf-8', style_compression=0) sheet1 = book.add_sheet(u"值班表", cell_overwrite_ok=True) sheet2 = book.add_sheet(u"参考", cell_overwrite_ok=True) style = xlwt.easyxf('align: wrap on') text = [u'课程表', u'星期一', u'星期二', u'星期三', u'星期四', u'星期五'] for index, each in enumerate(text): sheet1.write(0, index, each, style) sheet2.write(0, index, each, style) sheet1.col(index).width = 10000 sheet2.col(index).width = 10000 text = [u'第1、2节', u'第3、4节', u'第5、6节', u'第7、8节'] for index, each in enumerate(text): sheet1.row(index + 1).height = 500 sheet2.row(index + 1).height = 2000 sheet1.write(index + 1, 0, each, style) sheet2.write(index + 1, 0, each, style) for i in range(0, 5): sheet1.write(index + 1, i + 1, ",".join(result[i][index]), style) sheet2.write(index + 1, i + 1, ",".join(result_tmp[i][index]), style) book.save("FreeTimeCount/info/distribute.xls") else: def file_iterator(file_name, chunk_size=512): with open(file_name) as f: while True: c = f.read(chunk_size) if c: yield c else: break the_file_name = "FreeTimeCount/info/distribute.xls" response = StreamingHttpResponse(file_iterator(the_file_name)) response['Content-Type'] = 'application/octet-stream' response[ 'Content-Disposition'] = 'attachment;filename="{0}"'.format( the_file_name) return response else: print "not a post" print result_tmp response = TemplateResponse( request, 'FreeTimeCount/distribute.html', { "result": json.dumps(result), "result_tmp": json.dumps(result_tmp), "free_student": json.dumps(free_student) }) return response
def generate_xls_report(self, parser, filters, obj, workbook): worksheet = workbook.add_sheet(('Laporan Summary Pegawai')) worksheet.panes_frozen = True worksheet.remove_splits = True worksheet.portrait = True # Landscape worksheet.fit_wiresult_datah_to_pages = 1 worksheet.col(1).wiresult_datah = len("ABCDEFG") * 1024 # Specifying columns, the order doesn't matter # lamda d,f,p: is a function who has filter,data,parser as the parameters it is expected to the value of the column cols_specs = [ # ('header', column_span, column_type, lamda function) # Infos ('Company', 9, 0, 'text', lambda x, d, p: p.get_title('', filters) ), ('Title', 9, 0, 'text', lambda x, d, p: 'Laporan Rekapitulasi'), ('Period', 9, 0, 'text', lambda x, d, p: p.get_period(filters)), # Top Headers # ('Penambahan', 2, 100, 'text', lambda x, d, p: 'Penambahan'), # Main Headers / Rows ('No', 1, 50, 'text', lambda x, d, p: str(d['is_kepala_opd'])), ('Nama Pegawai', 1, 250, 'text', lambda x, d, p: d['employee_id']['name']), ('NIP', 1, 100, 'text', lambda x, d, p: d['employee_id']['nip']), ('Jabatan', 1, 250, 'text', lambda x, d, p: d['employee_id']['job_id']['name']), ('Nama OPD', 1, 250, 'text', lambda x, d, p: d['employee_id']['company_id']['name']), ('Biro', 1, 250, 'text', lambda x, d, p: d['employee_id']['biro_id']['name']), ('Bidang', 1, 250, 'text', lambda x, d, p: d['employee_id']['department_id']['name']), ('Nilai SKP', 1, 90, 'number', lambda x, d, p: d['nilai_skp_percent']), ('Nilai Perilaku', 1, 90, 'number', lambda x, d, p: d['nilai_perilaku_percent']), ('Nilai Tambahan', 1, 90, 'number', lambda x, d, p: d['fn_nilai_tambahan']), ('Nilai Kreatifitas', 1, 90, 'number', lambda x, d, p: d['fn_nilai_kreatifitas']), ('Nilai Total', 1, 100, 'number', lambda x, d, p: d['nilai_total']), # Misc ('single_empty_column', 1, 0, 'text', lambda x, d, p: ''), ('triple_empty_column', 3, 0, 'text', lambda x, d, p: ''), ('quadruple_empty_column', 4, 0, 'text', lambda x, d, p: ''), ] row_spec_value = [ 'Nama Pegawai', 'NIP', 'Jabatan', 'Bidang', 'Biro', 'Nama OPD', 'Nilai SKP', 'Nilai Perilaku', 'Nilai Tambahan', 'Nilai Kreatifitas', 'Nilai Total' ] # Row templates (Order Matters, this joins the columns that are specified in the second parameter) company_template = self.xls_row_template( cols_specs, ['single_empty_column', 'Company']) title_template = self.xls_row_template( cols_specs, ['single_empty_column', 'Title']) period_template = self.xls_row_template( cols_specs, ['single_empty_column', 'Period']) #top_header_template = self.xls_row_template(cols_specs, ['quadruple_empty_column','Penambahan','Pengurangan','single_empty_column']) row_template = self.xls_row_template(cols_specs, row_spec_value) #sums_template = self.xls_row_template(cols_specs, ['triple_empty_column','saldo_awal_sum','penjualan_sum','penjualan_jm_sum','pelunasan_sum','pelunasan_jm_sum','saldo_akhir_sum']) empty_row_template = self.xls_row_template(cols_specs, ['single_empty_column']) # Styles (It's used for writing rows / headers) row_normal_style = xlwt.easyxf(num_format_str='#,##0.00;(#,##0.00)') row_normal_odd_style = xlwt.easyxf( 'pattern: pattern solid, fore_color silver_ega;', num_format_str='#,##0.00;(#,##0.00)') info_style = xlwt.easyxf( 'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color gray50;', num_format_str='#,##0.00;(#,##0.00)') top_style = xlwt.easyxf( 'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_color orange;', num_format_str='#,##0.00;(#,##0.00)') header_style = xlwt.easyxf( 'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color green;', num_format_str='#,##0.00;(#,##0.00)') sum_style = xlwt.easyxf( 'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color gray50;', num_format_str='#,##0.00;(#,##0.00)') # Write infos # xls_write_row(worksheet, filters, data parser, row_number, template, style) self.xls_write_row(worksheet, filters, None, parser, 0, empty_row_template, row_normal_style) self.xls_write_row(worksheet, filters, None, parser, 1, company_template, info_style) self.xls_write_row(worksheet, filters, None, parser, 2, title_template, info_style) self.xls_write_row(worksheet, filters, None, parser, 3, period_template, info_style) self.xls_write_row(worksheet, filters, None, parser, 4, empty_row_template, row_normal_style) # Write top header #self.xls_write_row(worksheet, filters, None, parser, 5, top_header_template, top_style) # Write headers (It uses the first parameter of cols_specs) self.xls_write_row_header(worksheet, 6, row_template, header_style, set_column_size=True) row_count = 7 result = parser.get_skp_recap_report_raw(filters) for skp_recap_data in result: # Write Rows style = row_normal_style if (row_count % 2 == 0): style = row_normal_odd_style self.xls_write_row(worksheet, filters, skp_recap_data, parser, row_count, row_template, style) row_count += 1
def main(process_id, concentration_source, file_id, default_norm_dna, default_vol): lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD) process = Process(lims, id=process_id) book = xlwt.Workbook() sheet1 = book.add_sheet('Sheet1') inputs = [] outputs = [] for i, o in process.input_output_maps: output = o['uri'] if o and o['output-type'] == 'Analyte' and o[ 'output-generation-type'] == 'PerInput': input = i['uri'] inputs.append(input) outputs.append(output) lims.get_batch(inputs + outputs) if concentration_source == "sample": samples = [input.samples[0] for input in inputs] lims.get_batch(samples) try: concentrations = [ sample.udf['Sample conc. (ng/ul)'] for sample in samples ] except KeyError: print "Missing value for 'Sample conc. (ng/ul)'." sys.exit(1) elif concentration_source == "quantit": # Look for sibling processes with QC information. Note that this only works # because we know that the artifacts are generated directly above in the workflow, # and go through a single Quant-iT step. If inputs were e.g. the root analyte, then # this procedure could give unexpected results. processes = sorted(lims.get_processes( inputartifactlimsid=[input.id for input in inputs]), key=lambda proc: proc.id) concentrations = [] missing = [] for input in inputs: qi_conc = None for qc_process in processes: if qc_process.type_name.startswith("Quant-iT"): for i, o in qc_process.input_output_maps: if i['uri'].id == input.id and o['output-type'] == "ResultFile"\ and o['output-generation-type'] == "PerInput": conc = o['uri'].udf.get( 'Concentration (ng/ul)') or o['uri'].udf.get( 'Concentration') if conc is not None: qi_conc = conc if qi_conc is None: missing.append(i['uri'].name) else: concentrations.append(qi_conc) if missing: print "Missing Quant-iT results for", ",".join(missing), "." sys.exit(1) else: concentrations = [None] * len(inputs) missing_udf = [ input.name for input, conc in zip(inputs, concentrations) if conc is None ] if missing_udf: print "Error: input concentration not known for samples", ", ".join( missing_udf) sys.exit(1) warning = [] headers = [] i_o_s = zip(inputs, outputs, concentrations) for index, (input, output, input_conc) in enumerate(sorted(i_o_s, key=sort_key)): sample_name = input.name.encode('utf-8') well = output.location[1].replace(":", "") sample_no = re.match(r"([0-9]+)-", sample_name) # NOTE: NSC output format is no longer used (in fact, it wasn't ever used) # It is still included in some sample prep protocols, but could be removed # soon. # NSC general quantity-based normalization try: norm_mass = output.udf['Input (ng)'] except KeyError: norm_mass = default_norm_dna output.udf['Input (ng)'] = norm_mass try: vol = output.udf['Volume (uL)'] except KeyError: vol = default_vol output.udf['Volume (uL)'] = vol if input_conc == 0.0: sample_volume = vol + 1 # Will produce a warning below else: sample_volume = norm_mass * 1.0 / input_conc buffer_volume = vol - sample_volume if buffer_volume < 0: buffer_volume = 0.0 sample_volume = vol warning.append(output.name) columns = [ ("Sample_Number", sample_no.group(1) if sample_no else sample_name), ("Labware", "Rack%d" % ((index // 32) + 1)), ("Position_ID", str((index % 32) + 1)), ("Volume_DNA", round(sample_volume, 1)), ("Volume_EB", round(buffer_volume, 1)), ("Destination_Well_ID", well), ] if not headers: row = sheet1.row(0) headers = [x[0] for x in columns] for i, header in enumerate(headers): row.write( i, header, xlwt.easyxf('pattern: pattern solid, fore_color yellow;')) row = sheet1.row(index + 1) for i, val in enumerate([x[1] for x in columns]): row.write(i, val) lims.put_batch(outputs) outputstream = StringIO.StringIO() book.save(outputstream) outfile = Artifact(lims, id=file_id) filename = "Inputfil_Hamilton_Normalisering.xls" gs = lims.glsstorage(outfile, filename) file_obj = gs.post() file_obj.upload(outputstream.getvalue()) if warning: print "Warning: too low input concentration for samples:", ", ".join( warning), "." sys.exit(1)
""" Tablib - XLS Support. """ import sys from tablib.compat import BytesIO, xrange import tablib import xlrd import xlwt from xlrd.biffh import XLRDError title = 'xls' extensions = ('xls',) # special styles wrap = xlwt.easyxf("alignment: wrap on") bold = xlwt.easyxf("font: bold on") def detect(stream): """Returns True if given stream is a readable excel file.""" try: xlrd.open_workbook(file_contents=stream) return True except (TypeError, XLRDError): pass try: xlrd.open_workbook(file_contents=stream.read()) return True except (AttributeError, XLRDError): pass
# -*- coding: utf-8 -*- """ Created on Thu Oct 11 17:37:21 2018 @author: Sakshi """ import xlwt import os from datetime import datetime currentpath = os.getcwd() print (currentpath) path1 = "C:/Users/Sakshi/Desktop/python" style1 = xlwt.easyxf('font : name Time New Roman, color-index red, bold on','#,##0.00') style2 = xlwt.easyxf('font : name Rockwell, color-index blue,bold on','DD-MM-YY') style3 = xlwt.easyxf('font : name Arial, color-index blue,bold on') wb = xlwt.Workbook() ws = wb.add_sheet('first sheet#f5') ws.write(0,0,1234.567,style1) ws.write(1,0,datetime.now(),style2) ws.write(2,0,'first excel via python',style2) ws.write(3,0,1) ws.write(3,1,11) ws.write(3,2,111) ws.write(3,3,xlwt.Formula("A4+B4+C4")) os.chdir(path1) wb.save('alv.xls') os.chdir(currentpath)
def __init__(self, data, output_name='excel_data', headers=None, force_csv=False, encoding='utf8'): # Make sure we've got the right type of data to work with valid_data = False if isinstance(data, ValuesQuerySet): data = list(data) elif isinstance(data, QuerySet): data = list(data.values()) if hasattr(data, '__getitem__'): if isinstance(data[0], dict): if headers is None: headers = data[0].keys() data = [[row[col] for col in headers] for row in data] data.insert(0, headers) if hasattr(data[0], '__getitem__'): valid_data = True assert valid_data is True, "ExcelResponse requires a sequence of sequences" import StringIO output = StringIO.StringIO() # Excel has a limit on number of rows; if we have more than that, make a csv use_xls = False if len(data) <= 65536 and force_csv is not True: try: import xlwt except ImportError: # xlwt doesn't exist; fall back to csv pass else: use_xls = True if use_xls: book = xlwt.Workbook(encoding=encoding) sheet = book.add_sheet('Sheet 1') styles = { 'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'), 'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'), 'time': xlwt.easyxf(num_format_str='hh:mm:ss'), 'default': xlwt.Style.default_style } for rowx, row in enumerate(data): for colx, value in enumerate(row): if isinstance(value, datetime.datetime): cell_style = styles['datetime'] elif isinstance(value, datetime.date): cell_style = styles['date'] elif isinstance(value, datetime.time): cell_style = styles['time'] else: cell_style = styles['default'] sheet.write(rowx, colx, value, style=cell_style) book.save(output) mimetype = 'application/vnd.ms-excel' file_ext = 'xls' else: for row in data: out_row = [] for value in row: if not isinstance(value, basestring): value = unicode(value) value = value.encode(encoding) out_row.append(value.replace('"', '""')) output.write('"%s"\n' % '","'.join(out_row)) mimetype = 'text/csv' file_ext = 'csv' output.seek(0) super(ExcelResponse, self).__init__(content=output.getvalue(), mimetype=mimetype) self['Content-Disposition'] = 'attachment;filename="%s.%s"' % \ (output_name.replace('"', '\"'), file_ext)
def generate_cdnur_report(self, wb1): self.ensure_one() ws1 = wb1.add_sheet('cdnur') # Content/Text style header_content_style = xlwt.easyxf( "font: name Arial size 20 px, bold 1, height 170;") sub_header_style = xlwt.easyxf( "font: name Arial size 10 px, bold 1, height 170; align: horiz center" ) sub_header_content_style = xlwt.easyxf( "font: name Arial size 10 px, height 170;") line_content_style = xlwt.easyxf("font: name Arial, height 170;") row = 1 col = -1 ws1.row(row).height = 500 ws1.write_merge(row, row, 2, 6, "Summary For CDNUR(6C)", header_content_style) row += 2 ws1.write(row, col + 1, "From:", sub_header_style) ws1.write(row, col + 2, self.format_date(self.date_from), sub_header_content_style) row += 1 ws1.write(row, col + 1, "To:", sub_header_style) ws1.write(row, col + 2, self.format_date(self.date_to), sub_header_content_style) row += 1 ws1.write(row, col + 1, "Note/Voucher Number", sub_header_style) ws1.write(row, col + 2, "Note/Voucher date", sub_header_style) ws1.write(row, col + 3, "Invoice/Advance Payment Voucher number", sub_header_style) ws1.write(row, col + 4, "Invoice/Advance Payment Voucher date", sub_header_style) ws1.write(row, col + 5, "Pre GST", sub_header_style) ws1.write(row, col + 6, "Document Typ", sub_header_style) ws1.write(row, col + 7, "Reason For Issuing document", sub_header_style) ws1.write(row, col + 8, "Supply Type", sub_header_style) ws1.write(row, col + 9, "Invoice Type", sub_header_style) ws1.write(row, col + 10, "Note/Voucher Value", sub_header_style) ws1.write(row, col + 11, "Rate", sub_header_style) ws1.write(row, col + 12, "Taxable Value", sub_header_style) ws1.write(row, col + 13, "Integrated Tax Paid", sub_header_style) ws1.write(row, col + 14, "Central Tax Paid", sub_header_style) ws1.write(row, col + 15, "State/UT Tax Paid", sub_header_style) ws1.write(row, col + 16, "Cess Paid", sub_header_style) ws1.write(row, col + 17, "Eligibility For ITC", sub_header_style) ws1.write(row, col + 18, "Availed ITC Integrated Tax", sub_header_style) ws1.write(row, col + 19, "Availed ITC Central Tax", sub_header_style) ws1.write(row, col + 20, "Availed ITC State/UT Tax", sub_header_style) ws1.write(row, col + 21, "Availed ITC Cess", sub_header_style) row += 1 invoice_gst_tax_lines = {} igst = 0 cgst = 0 sgst = 0 for invoice in self.refund_invoices.filtered(lambda p: not p.partner_id.x_gstin and p.company_id.state_id != p.partner_id.state_id \ and (p.amount_untaxed_signed * -1) > B2CL_INVOICE_AMT_LIMIT and p.type in ('in_refund', 'out_refund')): #Can't use invoice.tax_line_ids directly because it will contain on individual/leaf taxes (like [email protected]%, [email protected]%) #while gstr2 report needs the 'group' tax (like GST@5%). #Iterate through invoice.invoice_line_ids.invoice_line_tax_line_ids and collect/compute from there #for tax_line in invoice.tax_line_ids: _logger.info(invoice) grouped_tax_lines = {} for invoice_line in invoice.invoice_line_ids: if invoice_line.discount_amount and invoice_line.quantity != 0: price = invoice_line.price_unit - ( invoice_line.discount_amount / invoice_line.quantity) elif invoice_line.discount: price = invoice_line.price_unit * ( 1 - (invoice_line.discount or 0.0) / 100.0) else: price = invoice_line.price_unit if invoice_line.invoice_id.inclusive: line_taxes = invoice_line.invoice_line_tax_ids.with_context( price_include=True, include_base_amount=True).compute_all_inc( price, invoice.currency_id, invoice_line.quantity, product=invoice_line.product_id, partner=invoice.partner_id) else: line_taxes = invoice_line.invoice_line_tax_ids.compute_all( price, invoice.currency_id, invoice_line.quantity, invoice_line.product_id, invoice.partner_id) # line_taxes = invoice_line.invoice_line_tax_ids.compute_all(invoice_line.price_unit, invoice.currency_id, invoice_line.quantity, invoice_line.product_id, invoice.partner_id) #_logger.info(line_taxes) for ln_tx in invoice_line.invoice_line_tax_ids: #.sorted(reverse=True): gst_tax_id = None # if ln_tx.gst_type in ('gst','ugst','igst'): gst_tax_id = ln_tx if grouped_tax_lines.get(ln_tx): grouped_tax_lines[ln_tx][0] += line_taxes[ 'total_excluded'] else: grouped_tax_lines[ln_tx] = [ 0, 0, 0, 0, 0 ] #[Taxable value, Cess amount, IGST, CGST, SGST] grouped_tax_lines[ln_tx][0] = line_taxes[ 'total_excluded'] #Collect the IGST/CGST/SGST breakup for this tax rate for leaf_tax in line_taxes['taxes']: if 'IGST' in leaf_tax['name']: grouped_tax_lines[ln_tx][2] += leaf_tax['amount'] elif 'CGST' in leaf_tax['name']: grouped_tax_lines[ln_tx][3] += leaf_tax['amount'] elif 'SGST' in leaf_tax['name'] or 'UTGST' in leaf_tax[ 'name']: grouped_tax_lines[ln_tx][4] += leaf_tax['amount'] else: #CESS and other non-GST taxes #TODO:Make the bold assumption that CESS is applied *after* GST taxes, so grouped_tax_lines[gst_tx_id] is already present #Calculate CESS amount only grouped_tax_lines[gst_tax_id][1] += sum( l['amount'] for l in line_taxes['taxes'] if 'GST' not in l['name']) invoice_gst_tax_lines[invoice] = grouped_tax_lines #_logger.info(grouped_tax_lines) for invoice, inv_tax_lines in sorted( invoice_gst_tax_lines.items(), key=lambda p: (p[0].date, p[0].number)): # invoice_gst_tax_lines.items(): for tax_id, base_amount in inv_tax_lines.items(): #tax_id = self.env['account.tax'].browse(tax_id_id) for rateObj in tax_id: if rateObj.amount_type == "group": for childObj in rateObj.children_tax_ids: tax_rate = childObj.amount * 2 break else: tax_rate = rateObj.amount break # tax_rate = float( str(tax_id.name).split('@')[1].split('%')[0] ) if float_is_zero( tax_rate, precision_digits=3): #Skip zero rated/exempted rates continue ws1.write(row, col + 1, invoice.number, line_content_style) ws1.write(row, col + 2, self.format_date(invoice.date_invoice), line_content_style) ws1.write(row, col + 3, invoice.refund_invoice_id.number, line_content_style) ws1.write( row, col + 4, self.format_date(invoice.refund_invoice_id.date_invoice), line_content_style) ws1.write(row, col + 5, "N", line_content_style) #Pre-GST ws1.write(row, col + 6, invoice.type == 'in_refund' and "D" or "C", line_content_style) ws1.write(row, col + 7, invoice.name, line_content_style) ws1.write(row, col + 8, "TODO", line_content_style) #Supply-Type ws1.write(row, col + 9, "Regular", line_content_style) ws1.write(row, col + 10, invoice.amount_total, line_content_style) ws1.write(row, col + 11, tax_rate, line_content_style) ws1.write(row, col + 12, base_amount[0], line_content_style) ws1.write(row, col + 13, base_amount[2], line_content_style) ws1.write(row, col + 14, base_amount[3], line_content_style) ws1.write(row, col + 15, base_amount[4], line_content_style) ws1.write(row, col + 16, base_amount[1], line_content_style) ws1.write(row, col + 17, "", line_content_style) ws1.write(row, col + 18, "", line_content_style) ws1.write(row, col + 19, "", line_content_style) ws1.write(row, col + 20, "", line_content_style) ws1.write(row, col + 21, "", line_content_style) row += 1
import xlrd import xlwt from datetime import datetime book = xlwt.Workbook() ws = book.add_sheet('a test sheet') style0 = xlwt.easyxf(num_format_str="D-MMM_YY") style1 = xlwt.easyxf('font:name Times New Roman,color-index red,bold on') ws.write(0, 0, datetime.now(), style0) ws.write(1, 0, 10, style1) ws.write(2, 0, 10, style1) ws.write(3, 0, xlwt.Formula("A2+A3")) book.save('abs.xls')
def generate_xls_report(self, _p, _xs, data, objects, wb): print "data on generate xls report>>>>>>>>>>>>>>>>>>>>>>>>>>>", data from pprint import pprint pprint(data) ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 # set print header/footer ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # cf. account_report_general_ledger.mako initial_balance_text = { 'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No') } # Title cell_style = xlwt.easyxf(_xs['xls_title']) report_name = ' - '.join([ _p.report_name.upper(), _p.company.partner_id.name, _p.company.currency_id.name ]) c_specs = [ ('report_name', 1, 0, 'text', report_name), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style) # write empty row to define column sizes c_sizes = self.column_sizes c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None) for i in range(0, len(c_sizes))] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, set_column_size=True) # Header Table cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _('Chart of Account')), ('fy', 1, 0, 'text', _('Fiscal Year')), ('df', 2, 0, 'text', _p.filter_form(data) == 'filter_date' and _('Dates Filter') or _('Periods Filter')), ('cd', 1, 0, 'text', _('Clearance Date')), ('af', 2, 0, 'text', _('Accounts Filter')), ('tm', 2, 0, 'text', _('Target Moves')), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) cell_format = _xs['borders_all'] cell_style = xlwt.easyxf(cell_format) cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_specs = [ ('coa', 2, 0, 'text', _p.chart_account.name), ('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'), ] df = _('From') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.start_date if _p.start_date else u'' else: df += _p.start_period.name if _p.start_period else u'' df += ' ' + _('To') + ': ' if _p.filter_form(data) == 'filter_date': df += _p.stop_date if _p.stop_date else u'' else: df += _p.stop_period.name if _p.stop_period else u'' c_specs += [ ('df', 2, 0, 'text', df), ('cd', 1, 0, 'text', _p.date_until), ('af', 2, 0, 'text', _p.display_partner_account(data)), ('tm', 2, 0, 'text', _p.display_target_move(data)), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, row_style=cell_style_center) ws.set_horz_split_pos(row_pos) row_pos += 1 # Column Title Row cell_format = _xs['bold'] c_title_cell_style = xlwt.easyxf(cell_format) # Column Header Row cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all'] c_hdr_cell_style = xlwt.easyxf(cell_format) c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right']) c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center']) c_hdr_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) cell_format = _xs['italic'] + _xs['borders_all'] c_init_cell_style = xlwt.easyxf(cell_format) c_init_cell_style_decimal = xlwt.easyxf( cell_format + _xs['right'], num_format_str=report_xls.decimal_format) c_init_cell_style_decimal_bold = xlwt.easyxf( cell_format + _xs['right'] + _xs['bold'], num_format_str=report_xls.decimal_format) c_specs = [ ('partner', 2, 0, 'text', _('Partner'), None, c_hdr_cell_style), ('code', 1, 0, 'text', _('Code'), None, c_hdr_cell_style), ('balance', 1, 0, 'text', _('Balance'), None, c_hdr_cell_style) ] cnt = 0 for range_title in _p.ranges_titles: cnt += 1 c_specs.append(('classification' + str(cnt), 1, 0, 'text', _(range_title), None, c_hdr_cell_style)) c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) # cell styles for aged lines ll_cell_format = _xs['borders_all'] ll_cell_style = xlwt.easyxf(ll_cell_format) ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center']) ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'], num_format_str=report_xls.date_format) ll_cell_style_decimal = xlwt.easyxf( ll_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) cnt = 0 for acc in objects: if _p.agged_lines_accounts[acc.id]: cnt += 1 cumul_debit = 0.0 cumul_credit = 0.0 cumul_balance = 0.0 cumul_balance_curr = 0.0 c_specs = [ ('acc_title', 11, 0, 'text', ' - '.join([acc.code, acc.name])), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, c_title_cell_style) row_pos += 1 row_pos = self.xls_write_row(ws, row_pos, c_hdr_data) row_start = row_pos for partner_name, p_id, p_ref, p_name in _p.partners_order[ acc.id]: if _p.agged_lines_accounts[acc.id].get(p_id): line = _p.agged_lines_accounts[acc.id][p_id] c_specs = [ ('partner', 2, 0, 'text', _(partner_name or '')), ('code', 1, 0, 'text', _(p_ref or '')), ('balance', 1, 0, 'number', line.get('balance'), None, c_init_cell_style_decimal) ] count = 0 for classif in _p.ranges: count += 1 c_specs.append( ('classification' + str(count), 1, 0, 'number', line['aged_lines'][classif] or 0.0, None, c_init_cell_style_decimal)) row_data = self.xls_row_template( c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data) percents = _p.agged_percents_accounts[acc.id] totals = _p.agged_totals_accounts[acc.id] c_specs = [('total', 3, 0, 'text', _('Total'), None, c_hdr_cell_style), ('balance', 1, 0, 'number', totals['balance'], None, c_init_cell_style_decimal_bold)] count = 0 for classif in _p.ranges: count += 1 c_specs.append( ('classification' + str(count), 1, 0, 'number', totals[classif] or 0.0, None, c_init_cell_style_decimal_bold)) row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data) c_specs = [('percents', 4, 0, 'text', _('Percents'), None, c_hdr_cell_style)] count = 0 for classif in _p.ranges: count += 1 c_specs.append( ('classification' + str(count), 1, 0, 'number', percents[classif] or 0.0, None, c_init_cell_style_decimal_bold)) row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data) row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb): # Initialisations ws = wb.add_sheet(_p.report_name[:31]) ws.panes_frozen = True ws.remove_splits = True ws.portrait = 0 # Landscape ws.fit_width_to_pages = 1 row_pos = 0 ws.header_str = self.xls_headers['standard'] ws.footer_str = self.xls_footers['standard'] # Print Title row_pos = self.print_title(ws, _p, row_pos, xlwt, _xs) # Print empty row to define column sizes row_pos = self.print_empty_row(ws, row_pos) # Print Header Table titles (Fiscal Year - Accounts Filter - Periods # Filter...) row_pos = self.print_header_titles(ws, _p, data, row_pos, xlwt, _xs) initial_balance_text = { 'initial_balance': _('Computed'), 'opening_balance': _('Opening Entries'), False: _('No') } # cf. account_report_partner_balance.mako # Print Header Table data row_pos = self.print_header_data(ws, _p, data, row_pos, xlwt, _xs, initial_balance_text) # Print comparison header table if _p.comparison_mode in ('single', 'multiple'): row_pos += 1 row_pos = self.print_comparison_header(_xs, xlwt, row_pos, _p, ws, initial_balance_text) # Freeze the line ws.set_horz_split_pos(row_pos) # cell styles for account data regular_cell_format = _xs['borders_all'] regular_cell_style = xlwt.easyxf(regular_cell_format) regular_cell_style_decimal = xlwt.easyxf( regular_cell_format + _xs['right'], num_format_str=report_xls.decimal_format) row_pos += 1 for current_account in objects: partners_order = _p['partners_order_accounts']\ .get(current_account.id, False) # do not display accounts without partners if not partners_order: continue comparisons = _p['comparisons_accounts']\ .get(current_account.id, False) # in multiple columns mode, we do not want to print accounts # without any rows if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [ comp['partners_amounts'][partner_id[1]] for partner_id in partners_order for comp in comparisons ] if not display_line(all_comparison_lines): continue current_partner_amounts = _p['partners_amounts_accounts']\ .get(current_account.id, False) if _p.comparison_mode in ('single', 'multiple'): comparison_total = {} for i, comp in enumerate(comparisons): comparison_total[i] = {'balance': 0.0} # print row: Code - Account name row_pos = self.print_row_code_account(ws, current_account, row_pos, _xs, xlwt) row_account_start = row_pos # Print row: Titles "Account/Partner Name-Code/ref-Initial # Balance-Debit-Credit-Balance" or "Account/Partner # Name-Code/ref-Balance Year-Balance Year2-Balance C2-Balance C3" row_pos = self.print_account_header(ws, _p, _xs, xlwt, row_pos) for (partner_code_name, partner_id, partner_ref, partner_name) \ in partners_order: partner = current_partner_amounts.get(partner_id, {}) # in single mode, we have to display all the partners even if # their balance is 0.0 because the initial balance should match # with the previous year closings # in multiple columns mode, we do not want to print partners # which have a balance at 0.0 in each comparison column if _p.comparison_mode in ('single', 'multiple'): all_comparison_lines = [ comp['partners_amounts'][partner_id] for comp in comparisons if comp['partners_amounts'].get(partner_id) ] if not display_line(all_comparison_lines): continue # display data row if len(_p.comp_params) == 2: account_span = 3 else: account_span = _p.initial_balance_mode and 2 or 3 c_specs = [('acc_title', account_span, 0, 'text', partner_name if partner_name else _('Unallocated')) ] c_specs += [('partner_ref', 1, 0, 'text', partner_ref if partner_ref else '')] if _p.comparison_mode == 'no_comparison': bal_formula = '' if _p.initial_balance_mode: init_bal_cell = rowcol_to_cell(row_pos, 3) bal_formula = init_bal_cell + '+' debit_col = 4 c_specs += [ ('init_bal', 1, 0, 'number', partner.get('init_balance', 0.0), None, regular_cell_style_decimal), ] else: debit_col = 3 c_specs += [ ('debit', 1, 0, 'number', partner.get('debit', 0.0), None, regular_cell_style_decimal), ('credit', 1, 0, 'number', partner.get('credit', 0.0), None, regular_cell_style_decimal), ] debit_cell = rowcol_to_cell(row_pos, debit_col) credit_cell = rowcol_to_cell(row_pos, debit_col + 1) bal_formula += debit_cell + '-' + credit_cell c_specs += [ ('bal', 1, 0, 'number', None, bal_formula, regular_cell_style_decimal), ] else: c_specs += [ ('bal', 1, 0, 'number', partner.get('balance', 0.0), None, regular_cell_style_decimal), ] if _p.comparison_mode in ('single', 'multiple'): for i, comp in enumerate(comparisons): comp_partners = comp['partners_amounts'] balance = diff = percent_diff = 0 if comp_partners.get(partner_id): balance = comp_partners[partner_id]['balance'] diff = comp_partners[partner_id]['diff'] percent_diff = comp_partners[partner_id][ 'percent_diff'] comparison_total[i]['balance'] += balance c_specs += [ ('balance_%s' % i, 1, 0, 'number', balance, None, regular_cell_style_decimal), ] # no diff in multiple comparisons because it shows too much # data if _p.comparison_mode == 'single': c_specs += [ ('balance_diff', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] if percent_diff is False: c_specs += [ ('balance', 1, 0, 'number', diff, None, regular_cell_style_decimal), ] else: c_specs += [ ('perc_diff', 1, 0, 'number', int(round(percent_diff))), ] row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs]) row_pos = self.xls_write_row(ws, row_pos, row_data, regular_cell_style) row_pos = self.print_account_totals(_xs, xlwt, ws, row_account_start, row_pos, current_account, _p)
class ExcelTable(object): """ xlwt wrapper for managing simple excel tables. Enforces each row of every sheet to have a value for each column """ # keep formatting objects in a dictionary; otherwise, each instance is # treated as a separate entity by excel, which eventually (limit 4k) # gives up trying to interpret them. easyxfs = dict() easyxfs['bold'] = xlwt.easyxf('font: bold 1') easyxfs['bold+rotate'] = xlwt.easyxf('font: bold 1; align: rotation 90') easyxfs['hyperlink'] = xlwt.easyxf('font: colour blue, underline on') easyxfs[None] = xlwt.easyxf('font: colour black') def __init__(self, sheetname=None, colnames=None, header_format='bold'): """ Initialize a workbook """ self.book = xlwt.Workbook() self.name_to_sheet = dict() self.sheet_to_colnames = dict() self.sheet_to_rownum = defaultdict(int) if sheetname is not None: self.add_sheet(sheetname, colnames, header_format) def add_sheet(self, sheetname, colnames, header_format='bold'): """ Add a worksheet to the workbook with given column names """ if sheetname not in self.name_to_sheet: sheet = self.book.add_sheet(sheetname) self.name_to_sheet[sheetname] = sheet self.sheet_to_colnames[sheet] = colnames self.add_row(colnames, header_format, sheetname) else: print 'Sheet "%s" already exists' % sheetname def add_row(self, values, cell_formats=None, sheetname=None): """ Add a row to the specified worksheet. "values" iterator must contain a value for each column specified when the sheet was created (empty strings are fine) """ if sheetname is None: assert len(self.name_to_sheet) == 1, 'Please specify a sheet name' sheet = self.name_to_sheet.values()[0] else: assert sheetname in self.name_to_sheet, \ 'Sheet "%s" has not yet been created.' % sheetname sheet = self.name_to_sheet[sheetname] assert len(values) == len(self.sheet_to_colnames[sheet]), \ 'Please provide a value for each column: %s' % \ self.sheet_to_colnames[sheet] if not isinstance(cell_formats, list): cell_formats = [cell_formats] * len(values) for idx, cell_format in enumerate(cell_formats): if cell_format in self.easyxfs: cell_formats[idx] = self.easyxfs[cell_format] elif not isinstance(cell_format, xlwt.Style.XFStyle): raise RuntimeError('Unexpected format "%s". ' 'Please select from the following: %s' % sorted(str(self.easyxfs.keys()))) row = self.sheet_to_rownum[sheet] if row >= 65530: warnings.warn('Maximum number of rows reached', RuntimeWarning) return self.sheet_to_rownum[sheet] += 1 for (col, (val, cell_format)) in enumerate(zip(values, cell_formats)): sheet.write(row, col, _strip_non_ascii(val), cell_format) def write_to_file(self, filename): """ Export table to disk """ self.book.save(filename)
def CreateExcel(cevObjeclist,portobject,filename): bb = cevObjeclist cc = portobject style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',num_format_str='#,##0.00') style1 = xlwt.easyxf(num_format_str='D-MMM-YY') wb = xlwt.Workbook() ws = wb.add_sheet(u'漏洞情况') wp = wb.add_sheet(u'端口情况情况') wp.write(0,0,u"序号") wp.write(0,1,u"主机ip") wp.write(0,2,u"开放端口") wp.write(0,3,u"端口协议") wp.write(0,4,u"端口服务") wp.write(0,5,u"探测输出") for x in xrange(0,len(cc)): wp.write(x+1,0,x+1) #host for x in xrange(0,len(cc)): wp.write(x+1,1,cc[x].Host) #port for x in xrange(0,len(cc)): wp.write(x+1,2,cc[x].Port) #Protocol for x in xrange(0,len(cc)): wp.write(x+1,3,cc[x].Protocol) #Service for x in xrange(0,len(cc)): wp.write(x+1,4,cc[x].Service) #Output for x in xrange(0,len(cc)): wp.write(x+1,5,cc[x].Output) ws.write(0,0,u"序号") ws.write(0,1,u"CVE编号") ws.write(0,2,u"主机IP") ws.write(0,3,u"风险等级") ws.write(0,4,u"协议") ws.write(0,5,u"端口") ws.write(0,6,u"漏洞名称") ws.write(0,7,u"漏洞描述") ws.write(0,8,u"整改建议") ws.write(0,9,u"探测输出") #顺序 for x in xrange(0,len(bb)): ws.write(x+1,0,x+1) #cve for x in xrange(0,len(bb)): ws.write(x+1,1,bb[x].Cve) #Host for x in xrange(0,len(bb)): ws.write(x+1,2,bb[x].Host) #Risk for x in xrange(0,len(bb)): ws.write(x+1,3,bb[x].Risk) #Protocol for x in xrange(0,len(bb)): ws.write(x+1,4,bb[x].Protocol) #Port for x in xrange(0,len(bb)): ws.write(x+1,5,bb[x].Port) #Name for x in xrange(0,len(bb)): ws.write(x+1,6,bb[x].Name) #Description for x in xrange(0,len(bb)): ws.write(x+1,7,bb[x].Description) #Solution for x in xrange(0,len(bb)): ws.write(x+1,8,bb[x].Solution) #Output for x in xrange(0,len(bb)): ws.write(x+1,9,bb[x].Output) wb.save(filename)
# path = 'E:\\python_my_practice\\filehandling\\addfile.xls' # # # import xlwt # from xlwt import Workbook # # # Workbook is created # wb = xlwt.Workbook(path) # # # # add_sheet is used to create sheet. # sheet = wb.active_sheet # # cell=wb.set_use_cell_values(1, 0, 'ISBT DEHRADUN') # sheet.write(2, 0, 'SHASTRADHARA') # wb.save('xlwt example.xls') from xlwt import Workbook exel_file = Workbook() sheet = exel_file.add_sheet('empinfo') style = xlwt.easyxf('font : bold 1, color red;') row = 0 sheet.write(row, 0, 'empid', style) sheet.write(row, 1, 'empnm', style) sheet.write(row, 2, 'empage', style) sheet.write(row, 3, 'empaddr', style) sheet.write(row, 4, 'empsal', style) exel_file.save(path) print('write into excel file')
import os from xlwt import easyxf import xlwt tittle_style_color = xlwt.easyxf( 'font: height 240, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour 0x1A' ) sub_tittle_style_color = xlwt.easyxf( 'font: height 240, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour 0x1A' ) tittle_style = xlwt.easyxf( 'font: height 240, bold on; align: wrap on, vert centre, horiz center;') main_tittle_style = xlwt.easyxf( 'font: height 240, name Arial, colour_index black, bold on; align: wrap on, vert centre, horiz left;' 'pattern: pattern solid, fore_colour ice_blue') sub_main_tittle_style = xlwt.easyxf( 'font: height 240, name Arial, colour_index black, bold on; align: wrap on, vert centre, horiz left;' 'pattern: pattern solid, fore_colour tan') tittle_style_left = xlwt.easyxf( 'font: height 240, bold on; align: wrap on, vert centre, horiz left;') subTitle_style = xlwt.easyxf( 'font: height 200, bold on, italic on; align: wrap on, vert centre, horiz centre;' ) subTitle_style_color = xlwt.easyxf( 'font: height 200, bold on, italic on; align: wrap on, vert centre, horiz centre;' 'pattern: pattern solid, fore_colour 0x1B') subTitle_style_color_left = xlwt.easyxf(
args = parser.parse_args() if args.inputfile is None: parser.print_help() exit() root = ET.parse(args.inputfile) map_version = root.getroot() first_node = map_version.find('node') wb = xlwt.Workbook() ws = wb.add_sheet('freemind2excel',cell_overwrite_ok=True) #设置行高 ws.width=256*20 tall_style = xlwt.easyxf('font:height 480;') ws_row = ws.row(0) ws_row.set_style(tall_style) # 设置单元格宽度 # ws.col(0).width = 3333 # for i in range(8): # a=[0,1,2,3,4,5,6,7] b=[0, 1, 4, 5] for i in b: ws.col(i).width = 6666 ws.col(2).width = 3333 # 设置边框 perf_func2(first_node, count_row) for row in range(1, g_row2+1):
XL_CELL_ERROR, XL_CELL_BLANK, # for use in debugging, gathering stats, etc ) = range(7) # 看下第一行第一列的单元格是个字符串 sh1 = rdata.sheet_by_index(0) cell_0_0 = sh1.cell(0, 0) print(cell_0_0, cell_0_0.ctype, cell_0_0.value) # 第二行第一列的单元格 cell_1_0 = sh1.cell(1, 0) # 写数据进表格 # 创建一个wbook对象,生成一个新的sheet wbook = xlwt.Workbook() wsheet = wbook.add_sheet(sh1.name) # 在写入第一行,标题栏 # wsheet这个函数(row,col,value,style),这个style其实就是这个内容单元格的格式 style = xlwt.easyxf('align: vertical center, horizontal center') wsheet.write(0, 0, u'时间', style) wsheet.write(0, 1, u'人数1', style) wsheet.write(0, 2, u'人数2', style) wsheet.write(0, 3, u'总分', style) # 写成文件new_data.xls try: wbook.save('new_data.xls') except Exception as e: print(e) else: print('write excel file successful')
from bs4 import BeautifulSoup as soup from random import randint import requests import re import random import time import xlwt # generage the xls file and define the title workbook = xlwt.Workbook() worksheet = workbook.add_sheet('Sheet Name') style = xlwt.easyxf('font: bold 1') titles = [ 'P url', 'EAN', 'ISBN', 'eBay item number', 'Title', 'Brand', 'Condition', 'Condition', 'FORMAT', 'Author', 'PUBLISHER', 'CATEGORY', 'About this product', 'DESCRIPTION', 'Item specifics', 'Product Summary', 'Shipping Weight', 'Product Dime', 'image1', 'imaage2', 'imaage3', 'imaage4', 'imaage5', 'imaage6', 'imaage7', 'Price' ] row = 0 col = 0 for title in titles: worksheet.write(row, col, title, style) col += 1 proxies = [ "korleone:[email protected]:12345", "korleone:[email protected]:12345",