def _xls_row(self, row, fromtype, absolute=True): rowi = self._convert_rowindex(row, fromtype, self.indextypes.xlscell) head = xlsxutils.xl_rowcol_to_cell(rowi, 0, True, True) if absolute: ran = xlsxutils.xl_range_abs(rowi, self._xls_coloff, rowi, self._xls_ncol + self._xls_coloff - 1) else: ran = xlsxutils.xl_range(rowi, self._xls_coloff, rowi, self._xls_ncol + self._xls_coloff - 1) return head, ran
def get_data_cells_reference(self): """Gets an absolute cell reference to the cells and worksheet stored in this area. @return: str representing the data stored in the area. """ self._check_worksheet_data_cells_reference() data_ref = xl_range_abs(self._initial_row, self._initial_col, len(self.data), self._initial_col) return '=' + self._worksheet.get_name() + '!' + data_ref
def _xls_column(self, col, fromtype, absolute=True): coli = self._convert_colindex(col, fromtype, self.indextypes.xlscell) head = xlsxutils.xl_rowcol_to_cell(0, coli, True, True) if absolute: ran = xlsxutils.xl_range_abs(self._xls_rowoff, coli, self._xls_nrow + self._xls_rowoff - 1, coli) else: ran = xlsxutils.xl_range(self._xls_rowoff, coli, self._xls_nrow + self._xls_rowoff - 1, coli) return head, ran
def plotData(data, level, caption): global WS global xrow global xcol #print(level) if level < (dims - 2): for key in data.keys(): #print(key) plotData(data[key], level + 1, "%s%s=%s " % (caption, dims_name[level], key)) else: # xrow: current row # xcol: current column # drow: delta row shift # dcol: delta column shift # brow: base row # bcol: base column # dmrow: metric wise delta row shift, the maximum will be taken # bmcol: metric wise base col drow = 0 brow = xrow bcol = xcol bmcol = bcol for metric in metrics: finalcaption = "%s|| Metric: %s" % (caption, metric) #print(finalcaption) xrow = brow xcol = bmcol isFirst = True isFirstDataCell = True dmrow = 0 dcol = 0 isFirstCol = True worksheet.write(xrow, xcol, finalcaption, bold_format) column_chart = workbook.add_chart({'type': 'column'}) xrow = xrow + 1 dmrow = dmrow + 1 for row in data.keys(): dmrow = dmrow + 1 if isFirst == True: isFirst = False xcol = xcol + 1 # Skips the first column for HC dcol = dcol + 1 # Header row dmrow = dmrow + 1 HR_start_row = xrow xrow = xrow + 1 HR_end_row = xrow - 1 # Column Captions dmrow = dmrow + 1 #print(" ", end='') xcol = xcol + 1 # Skips the first column for Column caption HR_start_col = xcol dcol = dcol + 1 for col in data[row].keys(): #print("%s " % str(col), end='') worksheet.write(xrow, xcol, str(col), RC_caption_format) xcol = xcol + 1 dcol = dcol + 1 #print() xrow = xrow + 1 HR_end_col = xcol - 1 worksheet.merge_range(HR_start_row, HR_start_col, HR_end_row, HR_end_col, dims_name[level + 1], HR_merge_format) xcol = bmcol # Header Column if isFirstCol: isFirstCol = False HC_start_row = xrow HC_start_col = xcol xcol = xcol + 1 #print("%s " % str(row), end='') worksheet.write(xrow, xcol, str(row), CC_caption_format) xcol = xcol + 1 if isFirstDataCell: isFirstDataCell = False CF_start_row = xrow CF_start_col = xcol S_start_row = xrow S_start_col = xcol for col in data[row].keys(): #print("%s " % str(data[row][col][metric]), end='') #worksheet.write(xrow, xcol, str(data[row][col][metric])) #worksheet.write(xrow, xcol, data[row][col][metric], cell_format) worksheet.write_formula(xrow, xcol, data[row][col][metric], cell_format) xcol = xcol + 1 #print() xrow = xrow + 1 S_end_row = xrow - 1 S_end_col = xcol - 1 #cell_range = xl_range_abs(S_start_row, S_start_col, S_end_row, S_end_col) #column_chart.add_series({'values': '=Sheet1!%s' % (cell_range)}) #column_chart.add_series({'values': [WS, S_start_row, S_start_col, S_end_row, S_end_col]}) HC_end_row = xrow - 1 HC_end_col = HC_start_col worksheet.merge_range(HC_start_row, HC_start_col, HC_end_row, HC_end_col, dims_name[level], HC_merge_format) CF_end_row = xrow - 1 CF_end_col = xcol - 1 #print(CF_start_row, CF_start_col, CF_end_row, CF_end_col) worksheet.conditional_format( CF_start_row, CF_start_col, CF_end_row, CF_end_col, { 'type': '3_color_scale', 'min_color': "#63BE7B", 'mid_color': "#FFEB84", 'max_color': "#F8696B" }) chart_row = CF_start_row chart_col = CF_start_col category_start_row = CF_start_row category_start_col = CF_start_col - 1 category_end_row = CF_start_row + dmrow - 2 - 2 category_end_col = CF_start_col - 1 #print("### Chart:") while chart_col < (CF_start_col + dcol - 2): #print("=>> ### " , end='') #print(chart_row-2, chart_col, chart_row-1, chart_col) column_chart.add_series({ #'name': [WS, chart_row-2, chart_col, chart_row-1, chart_col], 'name': '=%s!%s' % (WS, xl_range_abs(chart_row - 2, chart_col, chart_row - 1, chart_col)), #'name': '=%s!%s' % (WS, xl_range_abs(chart_row-1, chart_col, chart_row-1, chart_col)), 'categories': [ WS, category_start_row, category_start_col - 1, category_end_row, category_end_col ], 'values': [ WS, CF_start_row, chart_col, CF_start_row + dmrow - 2 - 2, chart_col ] }) #print("name:", chart_row-1, chart_col) #print("categories:", category_start_row, category_start_col, category_end_row, category_end_col) #print("values:", CF_start_row, chart_col, CF_start_row + dmrow - 2 -2, chart_col) column_chart.set_legend({'position': 'bottom'}) column_chart.set_title({ 'name': finalcaption, 'name_font': { #'name': 'Calibri', 'name': 'Calibri (Body)', #'color': 'blue', 'size': 14, 'bold': 0 } }) column_chart.set_y_axis({'name': 'Latency(usec)'}) #column_chart.set_y_axis({'name': 'Sample length (mm)'}) #column_chart.set_style(1) chart_col = chart_col + 1 worksheet.insert_chart(HR_start_row, xcol + 1, column_chart) #print() if drow < dmrow: drow = dmrow bmcol = bmcol + dcol + 2 + 8 xrow = brow + drow + 3 + 5 xcol = bcol
def excel_report(returns, w, rf=0, alpha=0.05, t_factor=252, name="report"): r""" Create an Excel report (with formulas) with useful information to analyze risk and profitability of investment portfolios. Parameters ---------- returns : DataFrame Assets returns. w : DataFrame of size (n_assets, n_portfolios) Portfolio weights. rf : float, optional Risk free rate or minimum aceptable return. The default is 0. alpha : float, optional Significante level of VaR, CVaR, EVaR, DaR and CDaR. The default is 0.05. t_factor : float, optional Factor used to annualize expected return and expected risks for risk measures based on returns (not drawdowns). The default is 252. .. math:: \begin{align} \text{Annualized Return} & = \text{Return} \, \times \, \text{t_factor} \\ \text{Annualized Risk} & = \text{Risk} \, \times \, \sqrt{\text{t_factor}} \end{align} name : str, optional Name or name with path where the Excel report will be saved. If no path is provided the report will be saved in the same path of current file. Raises ------ ValueError When the report cannot be built. Example ------- :: rp.excel_report(returns, w, MAR=0, alpha=0.05, name='report', files=None) .. image:: images/Excel.png """ n1 = w.shape[0] n2 = returns.shape[0] portfolios = w.columns.tolist() dates = returns.index.tolist() year = str(datetime.datetime.now().year) days = (returns.index[-1] - returns.index[0]).days + 1 # Create a Pandas Excel writer using XlsxWriter as the engine. writer = pd.ExcelWriter(name + ".xlsx", engine="xlsxwriter") # Convert the dataframe to an XlsxWriter Excel object. w.to_excel(writer, sheet_name="Resume", startrow=35, startcol=0) returns.to_excel(writer, sheet_name="Returns", index_label=["Date"]) # Get the xlsxwriter objects from the dataframe writer object. workbook = writer.book worksheet1 = writer.sheets["Resume"] worksheet2 = writer.sheets["Returns"] worksheet3 = workbook.add_worksheet("Portfolios") worksheet4 = workbook.add_worksheet("Absdev") worksheet5 = workbook.add_worksheet("CumRet") worksheet6 = workbook.add_worksheet("Drawdown") worksheet7 = workbook.add_worksheet("devBelowTarget") worksheet8 = workbook.add_worksheet("devBelowMean") worksheet1.hide_gridlines(2) worksheet2.hide_gridlines(2) worksheet3.hide_gridlines(2) worksheet4.hide_gridlines(2) worksheet5.hide_gridlines(2) worksheet6.hide_gridlines(2) worksheet7.hide_gridlines(2) worksheet8.hide_gridlines(2) # Cell Formats cell_format1 = workbook.add_format({"bold": True, "border": True}) cell_format2 = workbook.add_format({"bold": True, "font_size": 28, "right": True}) cell_format3 = workbook.add_format({"num_format": "0.0000%"}) cell_format4 = workbook.add_format({"num_format": "0.0000%", "border": True}) cell_format5 = workbook.add_format({"num_format": "yyyy-mm-dd", "bold": True}) cell_format6 = workbook.add_format({"num_format": "0.0000", "border": True}) cell_format7 = workbook.add_format( {"num_format": "yyyy-mm-dd", "bold": True, "border": True} ) cell_format8 = workbook.add_format({"num_format": "0,000", "border": True}) cols = xl_col_to_name(1) + ":" + xl_col_to_name(n2) worksheet1.set_column(cols, 11, cell_format3) worksheet2.set_column(cols, 9, cell_format3) worksheet2.write(0, 0, "Date", cell_format1) worksheet3.write(0, 0, "Date", cell_format1) worksheet4.write(0, 0, "Date", cell_format1) worksheet5.write(0, 0, "Date", cell_format1) worksheet6.write(0, 0, "Date", cell_format1) worksheet7.write(0, 0, "Date", cell_format1) worksheet8.write(0, 0, "Date", cell_format1) worksheet1.set_column("A:A", 35) worksheet2.set_column("A:A", 10, cell_format5) worksheet3.set_column("A:A", 10, cell_format5) worksheet4.set_column("A:A", 10, cell_format5) worksheet5.set_column("A:A", 10, cell_format5) worksheet6.set_column("A:A", 10, cell_format5) worksheet7.set_column("A:A", 10, cell_format5) worksheet8.set_column("A:A", 10, cell_format5) for i in range(0, n2): r = xl_rowcol_to_cell(i + 1, 0) formula = "=Returns!" + r + "" worksheet2.write(i + 1, 0, dates[i], cell_format7) worksheet3.write_formula(i + 1, 0, formula, cell_format7) worksheet4.write_formula(i + 1, 0, formula, cell_format7) worksheet5.write_formula(i + 1, 0, formula, cell_format7) worksheet6.write_formula(i + 1, 0, formula, cell_format7) worksheet7.write_formula(i + 1, 0, formula, cell_format7) worksheet8.write_formula(i + 1, 0, formula, cell_format7) labels_1 = [ "", "", "", "", "Profitability and Other Inputs", "Total Days in DataBase", "Mean Return (1)", "Compound Annual Growth Rate (CAGR)", "Minimum Acceptable Return (MAR) (1)", "Alpha", "", "Risk Measures based on Returns", "Standard Deviation (2)", "Mean Absolute Deviation (MAD) (2)", "Semi Standard Deviation (2)", "First Lower Partial Moment (FLPM) (2)", "Second Lower Partial Moment (SLPM) (2)", "Value at Risk (VaR) (2)", "Conditional Value at Risk (CVaR) (2)", "Entropic Value at Risk (EVaR) (2)", "Worst Realization (2)", "Skewness", "Kurtosis", "", "Risk Measures based on Drawdowns (3)", "Max Drawdown (MDD)", "Average Drawdown (ADD)", "Drawdown at Risk (DaR)", "Conditional Drawdown at Risk (CDaR)", "Ulcer Index (ULC)", ] for i in range(0, len(labels_1)): if labels_1[i] != "": worksheet1.write(i, 0, labels_1[i], cell_format1) for i in range(0, len(portfolios)): a = "Portfolio " + str(i + 1) worksheet1.write(3, 1 + i, a, cell_format1) worksheet1.write(35, 1 + i, a, cell_format1) worksheet3.write(0, 1 + i, a, cell_format1) worksheet4.write(0, 1 + i, a, cell_format1) worksheet5.write(0, 1 + i, a, cell_format1) worksheet6.write(0, 1 + i, a, cell_format1) worksheet7.write(0, 1 + i, a, cell_format1) worksheet8.write(0, 1 + i, a, cell_format1) for j in range(0, len(portfolios)): r_0 = xl_rowcol_to_cell(8, 1 + j) # MAR cell r_1 = xl_range_abs(36, 1 + j, 35 + n1, 1 + j) r_2 = xl_range_abs(1, 1 + j, n2, 1 + j) for i in range(0, n2): r_3 = xl_range(i + 1, 1, i + 1, n1) r_4 = xl_rowcol_to_cell(i + 1, 1 + j) r_5 = xl_range_abs(1, 1 + j, i + 1, 1 + j) formula1 = "{=MMULT(" + "Returns!" + r_3 + ",Resume!" + r_1 + ")}" formula2 = "=ABS(Portfolios!" + r_4 + "-AVERAGE(Portfolios!" + r_2 + "))" formula3 = "=SUM(Portfolios!" + r_5 + ")" formula4 = "=MAX(CumRet!" + r_5 + ")-CumRet!" + r_4 formula5 = ( "=MAX(Resume!" + r_0 + "/ " + str(t_factor) + "-Portfolios!" + r_4 + ", 0)" ) formula6 = "=MAX(AVERAGE(Portfolios!" + r_2 + ")-Portfolios!" + r_4 + ", 0)" worksheet3.write_formula(i + 1, 1 + j, formula1, cell_format3) worksheet4.write_formula(i + 1, 1 + j, formula2, cell_format3) worksheet5.write_formula(i + 1, 1 + j, formula3, cell_format3) worksheet6.write_formula(i + 1, 1 + j, formula4, cell_format3) worksheet7.write_formula(i + 1, 1 + j, formula5, cell_format3) worksheet8.write_formula(i + 1, 1 + j, formula6, cell_format3) r_6 = xl_rowcol_to_cell(9, 1 + j) # Alpha cell r_7 = xl_rowcol_to_cell(17, 1 + j) # Value at Risk cell AVG = "=AVERAGE(Portfolios!" + r_2 + ") * " + str(t_factor) + "" CUM = "{=PRODUCT(1 + Portfolios!" + r_2 + ")^(360/" + str(days) + ")-1}" STDEV = "=STDEV(Portfolios!" + r_2 + ") * SQRT(" + str(t_factor) + ")" MAD = "=AVERAGE(Absdev!" + r_2 + ") * SQRT(" + str(t_factor) + ")" ALPHA = "=" + str(alpha) VaR = ( "=-SMALL(Portfolios!" + r_2 + ",ROUNDUP(COUNT(Portfolios!" + r_2 + ")*" + r_6 + ",0)) * SQRT(" + str(t_factor) + ")" ) CVaR = ( "=-((SUMIF(Portfolios!" + r_2 + ',"<="&(-' + r_7 + "/SQRT(" + str(t_factor) + ")),Portfolios!" + r_2 + ")" ) CVaR += ( "-ROUNDUP(COUNT(Portfolios!" + r_2 + ")*" + r_6 + ",0)*(-" + r_7 + "/SQRT(" + str(t_factor) + ")))/(COUNT(Portfolios!" + r_2 + ")*" + r_6 + ")-" + r_7 + "/SQRT(" + str(t_factor) + ")) * SQRT(" + str(t_factor) + ")" ) EVaR = ( "=" + str(rk.EVaR_Hist(returns @ w, alpha=alpha)[0]) + " * SQRT(" + str(t_factor) + ")" ) WR = "=-MIN(Portfolios!" + r_2 + ") * SQRT(" + str(t_factor) + ")" MDD = "=MAX(Drawdown!" + r_2 + ")" ADD = "=AVERAGE(Drawdown!" + r_2 + ")" DaR = ( "=+LARGE(Drawdown!" + r_2 + ",ROUNDUP(COUNT(Drawdown!" + r_2 + ")*" + r_6 + ",0))" ) CDaR = ( "=((SUMIF(Drawdown!" + r_2 + ',">="&' + DaR[2:] + ",Drawdown!" + r_2 + ")" ) CDaR += ( "-ROUNDUP(COUNT(Drawdown!" + r_2 + ")*" + r_6 + ",0)*" + DaR[2:] + ")/(COUNT(Drawdown!" + r_2 + ")*" + r_6 + ")+" + DaR[2:] + ")" ) ULC = "=SQRT(SUMSQ(Drawdown!" + r_2 + ")/COUNT(Drawdown!" + r_2 + "))" MAR = "=" + str(rf) FLPM = "=AVERAGE(devBelowTarget!" + r_2 + ") * SQRT(" + str(t_factor) + ")" SLPM = ( "=SQRT(SUMSQ(devBelowTarget!" + r_2 + ")/(COUNT(devBelowTarget!" + r_2 + ") - 1))" + " * SQRT(" + str(t_factor) + ")" ) SDEV = ( "=SQRT(SUMSQ(devBelowMean!" + r_2 + ")/(COUNT(devBelowMean!" + r_2 + ") - 1))" + " * SQRT(" + str(t_factor) + ")" ) SKEW = "=SKEW(Portfolios!" + r_2 + ")" KURT = "=KURT(Portfolios!" + r_2 + ")" labels_2 = [ "", "", "", "", "", str(days), AVG, CUM, MAR, ALPHA, "", "", STDEV, MAD, SDEV, FLPM, SLPM, VaR, CVaR, EVaR, WR, SKEW, KURT, "", "", MDD, ADD, DaR, CDaR, ULC, ] for i in range(0, len(labels_2)): if labels_1[i] in ["Skewness", "Kurtosis"]: worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format6) elif labels_1[i] in ["Total Days in DataBase"]: worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format8) elif labels_2[i] != "": worksheet1.write_formula(i, 1 + j, labels_2[i], cell_format4) merge_format = workbook.add_format({"align": "Left", "valign": "vjustify"}) merge_format.set_text_wrap() worksheet1.set_row(1, 215) worksheet1.merge_range("A2:K2", __LICENSE__.replace("2021", year), merge_format) worksheet1.write(30, 0, "(1) Annualized, multiplied by " + str(t_factor)) worksheet1.write(31, 0, "(2) Annualized, multiplied by √" + str(t_factor)) worksheet1.write(32, 0, "(3) Based on uncompounded cumulated returns") worksheet1.write(0, 0, "Riskfolio-Lib Report", cell_format2) writer.save() workbook.close()
def get(self, request, *args, **kwargs): # Get data - here data list example data = list() data.append(('Apple', 25)) data.append(('Peach', 28)) data.append(('Orange', 17)) # Create and open the file wb = xlsxwriter.Workbook(self.filename) # Add Worksheet sheet = wb.add_worksheet('sheet1') # Set formats section_header_format = wb.add_format({ 'bold': True, 'align': 'left', 'font_size': 16, }) #define num format num_format = wb.add_format({ 'num_format': '0', 'align': 'right', 'font_size': 12, }) #define general format general_format = wb.add_format({ 'align': 'left', 'font_size': 12, }) # Define cols Width sheet.set_column(0, 0, 45) sheet.set_column(1, 1, 20) # Insert a header image. sheet.insert_image(1, 0, 'logo.jpeg') # Write and merge cells sheet.merge_range(6, 0, 6, 1, 'El WebMaster - Excel Sample', section_header_format) options = { 'data': data, 'total_row': data.__len__(), 'banded_rows': False, 'style': 'Table Style Light 9', 'columns': [ {'header': 'Fruit', 'total_string': 'Total', 'format': general_format}, {'header': 'Count', 'total_function': 'sum', 'format': num_format} ] } start_row = 9 end_row = start_row + data.__len__() + 1 sheet.add_table(start_row, 0, end_row, 1, options) # Create a chart chart = wb.add_chart({'type': 'pie'}) # Set chart title chart.title_name = 'Fruits piechart' # Set width fixed to cols chart.width = sheet._size_col(0) + sheet._size_col(1) # Set value range i.e. =sheet1!$B$11:$B$13 values = '=%s!%s' % (sheet.name, xl_range_abs(start_row+1, 1, end_row-1, 1)) # Set category range i.e. =sheet1!$A$11:$A$13 categories = '=%s!%s' % (sheet.name, xl_range_abs(start_row+1, 0, end_row-1, 0)) # Add series to chart chart.add_series({'values': values, 'categories': categories, 'smooth': True}) # Insert chart into the sheet sheet.insert_chart(end_row + 2, 0, chart) # Close file wb.close() # Response return HttpResponse(open(self.filename, 'r').read(), content_type='application/ms-excel')
dt1 = dt[i+6] # print dt1 dt2 = int(dt1[:-8]) dt3 = float(dt2/163676.0)*100 dt4 = format(dt3, '.2f') worksheet.write(row_track[coloumn], coloumn, dt4) row_track[coloumn] +=1 # Create a new chart object. chart = workbook.add_chart({'type': 'line'}) # Add a series to the chart. for c in range(0,45): cell_range = xl_range_abs(0, c, 45, c) chart.add_series({'values': '=Sheet1!'+cell_range+'', 'name': ['Sheet1', 0, c], }) chart.set_title ({'name': 'Results of Memory Usage'}) chart.set_x_axis({'name': 'Time Period'}) chart.set_y_axis({'name': 'Memory Usage (%)'}) worksheet.insert_chart('C1', chart) pharseFile.close() workbook.close()
def coordinador_asignaturas_xls_asig(request, id, id2): docente = get_object_or_404(Docente, user_ptr=id) periodo = get_object_or_404(Periodo, estado=True) asignatura = get_object_or_404(Asignatura, codigo=id2) asignaturas = DocenteAsignaturaPeriodo.objects.filter(periodo=periodo, docente=docente, asignatura=asignatura) filename = docente.cedula + '-'+asignatura.codigo+'.xls' wb = xlsxwriter.Workbook(filename) reporte = wb.add_worksheet('sheet1') reporte.set_column(0, 0,35) reporte.set_column(1, 0, 35) reporte.set_column(2, 0, 35) num_format = wb.add_format({ 'num_format': '0', 'align': 'right', 'font_size': 12, }) formato_negrita = wb.add_format({ 'bold': True }) general_format = wb.add_format({ 'align': 'left', 'font_size': 12, }) asignaturasClean = list() temasClean = list() for i in asignaturas: asignaturasClean.append(i.asignatura) horario = Horario.objects.filter(asignatura__in=asignaturas) temas = Tema.objects.filter(horario__in=horario, fecha__range=(periodo.fechainicio, periodo.fechafin)) reporte.merge_range(0, 0, 0, 2, asignatura.descripcion) reporte.merge_range(1, 0, 1, 2, (docente.first_name + " " + docente.last_name)) reporte.write(3, 0, "Tema", formato_negrita) reporte.write(3, 1, "Unidad", formato_negrita) reporte.write(3, 2, "Estado", formato_negrita) row = 3 valores = {'0':0, '1': 0, '2':0} for tema in temas: row += 1 reporte.write_row(row, 0, (tema.nombre, tema.unidad.nombre, tema.tema_string())) valores[tema.estado] = valores[tema.estado] + 1 row = row + 3 reporte.write(row, 0, "Estado", formato_negrita) reporte.write(row, 1, "Cantidad", formato_negrita) row += 1 inicio = row reporte.write(row, 0 , "Sin Revisar") reporte.write(row, 1, valores['0']) row += 1 reporte.write(row, 0, "Aprobados") reporte.write(row, 1, valores['1']) row += 1 reporte.write(row, 0, "No Aprobados") reporte.write(row, 1, valores['2']) chart = wb.add_chart({'type': 'pie'}) chart.title_name = 'Temas' chart.width = reporte._size_col(0) values = '=%s!%s' % (reporte.name, xl_range_abs(inicio, 1, inicio + 2, 1)) categories = '=%s!%s' % (reporte.name, xl_range_abs(inicio, 0, inicio + 2, 0)) chart.add_series({'values': values, 'categories': categories, 'smooth': True}) reporte.insert_chart(inicio + 4, 0, chart) chartBarras = wb.add_chart({'type': 'column'}) chartBarras.title_name = 'Temas' chartBarras.width = reporte._size_col(0) chartBarras.add_series({'values': values, 'categories': categories, 'smooth': True}) reporte.insert_chart(inicio + 4, 1, chartBarras) wb.close() output = open(filename) nombre = 'attachment; filename='+filename response = HttpResponse(output, content_type="application/ms-excel") response['Content-Disposition'] = nombre return response
def get(self, request, *args, **kwargs): periodo = kwargs['periodo'].upper() tipo = kwargs['tipo'].upper() proces = ProcesoActividad.objects.filter(proceso__tipo=tipo) list_actividades = [] for p in proces: dic = {} plan_sap = 0 plan_meta = 0 real = 0 plan_hh = 0 real_hh = 0 pln = Planificacion.objects.filter(actividad_id=p.actividad.id) for pl in pln: plan_sap = pl.plan_sap + plan_sap plan_meta = pl.plan_meta + plan_meta real = pl.real_mc + real plan_hh = pl.plan_hh + plan_hh real_hh = pl.real_hh + real_hh dic = {'nombre': p.actividad.nombre, 'ponderacion': p.actividad.ponderacion, 'unidad': p.actividad.unidad, 'plan_sap': str("%.2f" % (((plan_sap / 12) * (real / 12)) / 100)) + '%', 'plan_meta': str("%.2f" % (((plan_meta / 12) * (real / 12)) / 100)) + '%', 'avance_ponderado': str("%.2f" % (((plan_meta / 12) * (p.actividad.ponderacion)))) + '%', 'total_real_hh': str("%.2f" % (((plan_hh / 12) * (real_hh / 12)) / 100)) + '%'} list_actividades.append(dic) wb = xlsxwriter.Workbook(self.filename) sheet = wb.add_worksheet('Hoja1') section_header_format = wb.add_format({'bold': True, 'align': 'center', 'font_size': 16, }) section_subheader_format = wb.add_format({'bold': True, 'align': 'center', 'font_size': 13, }) num_format = wb.add_format({'num_format': '0.00%', 'align': 'center', 'font_size': 12, }) general_format = wb.add_format({'align': 'center', 'font_size': 12, }) sheet.set_column(0, 0, 15, num_format) sheet.set_column(1, 1, 50, general_format) sheet.set_column(2, 2, 15, general_format) sheet.set_column(3, 3, 15, general_format) sheet.set_column(4, 4, 15, general_format) sheet.set_column(5, 5, 20, general_format) sheet.set_column(6, 5, 20, general_format) sheet.insert_image(1, 0, 'logo.jpeg') sheet.merge_range(0, 0, 0, 6, 'TABLA DE RESUMEN ' + periodo + '(' + tipo + ')', section_header_format) row = 1 sheet.write(row, 0, 'Ponderacion', section_subheader_format) sheet.write(row, 1, 'Actividades', section_subheader_format) sheet.write(row, 2, 'Unidad', section_subheader_format) sheet.write(row, 3, '% Plan Sap', section_subheader_format) sheet.write(row, 4, '% Plan Meta', section_subheader_format) sheet.write(row, 5, 'Total Real HorasH', section_subheader_format) sheet.write(row, 6, 'Avance Ponderado', section_subheader_format) print list_actividades for x in list_actividades: row += 1 sheet.write(row, 0, ((float(x['ponderacion']) / 100))) sheet.write(row, 1, str(x['nombre'])) sheet.write(row, 2, str(x['unidad'])) sheet.write(row, 3, str(x['plan_sap'])) sheet.write(row, 4, str(x['plan_meta'])) sheet.write(row, 5, str(x['total_real_hh'])) sheet.write(row, 6, str(x['avance_ponderado'])) chart = wb.add_chart({'type': 'pie'}) chart.title_name = 'RESUMEN ' + periodo + '(' + tipo + ')' chart.width = sheet._size_col(0) + sheet._size_col(1) start_row = 1 end_row = start_row + list_actividades.__len__() + 1 values = '=%s!%s' % (sheet.name, xl_range_abs(start_row + 1, 1, end_row - 1, 0)) categories = '=%s!%s' % (sheet.name, xl_range_abs(start_row + 1, 1, end_row - 1, 1)) chart.add_series({'values': values, 'categories': categories, 'smooth': True}) sheet.insert_chart(end_row + 2, 0, chart) wb.close() return HttpResponse(open(self.filename, 'r').read(), content_type='application/ms-excel')
def _ranges_by_col_row(df, startrow, startcol): for row, i in enumerate(df.index, start=startrow): i = _convert_index(i) for col, c in enumerate(df.columns, start=startcol): yield i + _convert_index(c), xl_utl.xl_range_abs( row, col, row, col)
def ExtractData(self, Name='Doc', Pathout=''): ''' DESCRIPTION: This method extracts the data to an Excel File with several sheets. _________________________________________________________________ ''' if self.Data['H'] is None: self.Error('Model_AngstromPrescott', 'ExtractData', 'Cannot extract information if Rad is not added') # Parameters Vars = ['Dates', 'H', 'H0', 'n', 'N'] # Create Folder utl.CrFolder(Pathout) # Create document B = xlsxwl.Workbook(Pathout + Name + '.xlsx') # Add Data Sheet S = B.add_worksheet('Data') # Headers S.write(0, 0, 'Dates') S.write(0, 1, 'Julian Day') S.write(0, 2, 'H') S.write(0, 3, 'H0') S.write(0, 4, 'n') S.write(0, 5, 'N') S.write(0, 6, 'RelH') S.write(0, 7, 'RelN') # Fill data for iD, D in enumerate(self.Dates.str): # Dates S.write(iD + 1, 0, D) # Julian Day S.write(iD + 1, 1, self.Julian_Day[iD]) x = 2 for var in Vars[1:]: if np.isnan(self.Data[var][iD]): D = '' else: D = self.Data[var][iD] S.write(iD + 1, x, D) x += 1 # RelH if np.isnan(self.RelRad[iD]): D = '' else: D = self.RelRad[iD] S.write(iD + 1, 6, D) # RelN if np.isnan(self.RelN[iD]): D = '' else: D = self.RelN[iD] S.write(iD + 1, 7, D) # Add Graph Sheet S = B.add_worksheet('Graphs') # --------------- # Create graphs # --------------- # Ranges of the data VarCol = {'Dates': 0, 'H': 2, 'H0': 3, 'n': 4, 'N': 5} VarsRel = ['RelH', 'RelN'] VarsRelCol = {'RelH': 6, 'RelN': 7} Ranges = dict() Names = dict() RangesRel = dict() for ivar, var in enumerate(Vars): Ranges[var] = xl_range_abs(1, VarCol[var], iD + 1, VarCol[var]) Names[var] = xl_range_abs(0, VarCol[var], 0, VarCol[var]) for ivar, var in enumerate(VarsRelCol): RangesRel[var] = xl_range_abs(1, VarsRelCol[var], iD + 1, VarsRelCol[var]) x = 1 xx = 1 for ichart in range(2): chart = B.add_chart({'type': 'line'}) chart.add_series({ 'name': "=Data!" + Names[Vars[x]], 'categories': "=Data!" + Ranges['Dates'], 'values': "=Data!" + Ranges[Vars[x]] }) chart.add_series({ 'name': "=Data!" + Names[Vars[x + 1]], 'categories': "=Data!" + Ranges['Dates'], 'values': "=Data!" + Ranges[Vars[x + 1]] }) S.insert_chart(xx, 1, chart, { 'x_offset': 0, 'y_offset': 0, 'x_scale': 2., 'y_scale': 1.0 }) x += 2 xx += 15 # Scatter Linear chart = B.add_chart({'type': 'scatter'}) chart.add_series({ 'name': "Relación", 'categories': "=Data!" + RangesRel['RelN'], 'values': "=Data!" + RangesRel['RelH'], 'trendline': { 'type': 'linear', 'display_equation': True, 'display_r_squared': True } }) chart.set_title({'name': 'Relación entre índices lineal'}) chart.set_x_axis({'name': 'Fracción de Brillo Solar (n/N)'}) chart.set_y_axis({'name': 'Índice de claridad (H/H0)'}) chart.set_style(1) chart.set_legend({'none': True}) S.insert_chart(32, 1, chart, { 'x_offset': 0, 'y_offset': 0, 'x_scale': 1.5, 'y_scale': 1.5 }) # Scatter Polynomial chart = B.add_chart({'type': 'scatter'}) chart.add_series({ 'name': "Relación", 'categories': "=Data!" + RangesRel['RelN'], 'values': "=Data!" + RangesRel['RelH'], 'trendline': { 'type': 'polynomial', 'display_equation': True, 'display_r_squared': True, 'order': 3 } }) chart.set_title({'name': 'Relación entre índices Polinomica'}) chart.set_x_axis({'name': 'Fracción de Brillo Solar (n/N)'}) chart.set_y_axis({'name': 'Índice de claridad (H/H0)'}) chart.set_style(1) chart.set_legend({'none': True}) S.insert_chart(32 + 25, 1, chart, { 'x_offset': 0, 'y_offset': 0, 'x_scale': 1.5, 'y_scale': 1.5 }) B.close()
def test_xlsxwriter(): '''20170406 发起安装周报表格测试''' xlsxsrc = r'F:\工作\用户行为数据_SQL\分发市场应用数据\201702\test\分发市场统计_周报_2017-03-24.xlsx' reader = baseutil.XlsxReader(xlsxsrc) sn_install = '发起安装' dataset_active = reader.read_sheet(sn_install) xlsxout = r'F:\工作\用户行为数据_SQL\分发市场应用数据\201702\test\分发市场统计_测试.xlsx' wbout = baseutil.WorkBook(xlsxout) ws = wbout.add_sheet(sn_install, dataset_active) chart = wbout._workbook.add_chart({'type': 'line'}) chart.set_y_axis({ 'name': '其他应用', 'magor_gridlines': { 'line': { 'color': '#B6C9F1' } } }) chart.set_y2_axis({'name': '应用商店'}) chart.set_chartarea({'fill': {'color': '#B6C9F1'}}) chart.add_series({ 'categories': "'%s'!%s" % ('发起安装', utility.xl_range_abs(0, 2, 0, 34)), 'values': '=%s!%s' % ('发起安装', utility.xl_range_abs(1, 2, 1, 34)), 'overlap': 10, 'line': { 'color': 'blue' }, 'y2_axis': True, 'name': '应用商店', 'marker': { 'type': 'square', 'fill': { 'color': 'blue' }, 'border': { 'color': 'blue' } } }) for x in range(2, 10): chart.add_series({ 'categories': "'%s'!%s" % ('发起安装', utility.xl_range_abs(0, 2, 0, 34)), 'values': '=%s!%s' % ('发起安装', utility.xl_range_abs(x, 2, x, 34)), 'overlap': 10, 'name': dataset_active[x][1], 'marker': { 'type': 'automatic' } }) ws.insert_chart('A1', chart) wbout.close()
def SaveRepo(self,wb,rep): if len(self.ReposBranches)== 0: if debugLog >= debugLogLevel[1]: print "WARNING ",rep," ReposBranches is NULL! No author haved committed!" return if self.ReposBranches.has_key(rep): # add new sheet if len(rep)<=31: SheetName=rep else: SheetName=rep[8:] #del android_ if len(SheetName)>31: SheetName=SheetName[-31:-1] if SheetName.find("-")!= -1: print "!!!WARN!!! Sheet Name:",SheetName,"contain of "+"-"+" will delete!!!" SheetName=SheetName.replace("-","") ###!!!fix name contain "-" sheet,add_chart will be blank!!! if debugLog >= debugLogLevel[2]: print "Add worksheet:",rep,"Save sheet is:",SheetName ws = wb.add_worksheet(SheetName) # get Repo info Repo=self.ReposBranches.get(rep) if Repo: row_num=0 col_num=0 BraAutCiCnt={} if debugLog >= debugLogLevel[2]: print "Repo info:" print Repo for i in self.__RepoBranchTitle: ws.write(row_num,col_num,self.__RepoBranchTitle[col_num]) col_num+=1 row_num+=1 #!!! row + 1 col_num=0 BranchesInfos = Repo.items() #!!!Info!!!:Tuple(branch,list[dict]) if debugLog >= debugLogLevel[-2]: print "Branches info:" print BranchesInfos,"\n" #list for BraInfo in BranchesInfos: if debugLog >= debugLogLevel[-2]: print "Branch info: " print BraInfo #Tupple for Info in BraInfo: # string and list if type(Info) is str: Branch=Info if debugLog >= debugLogLevel[2]: print "Sheet(",SheetName,")","Branch:",Branch ws.set_column(row_num,col_num,len(Branch)) ws.write(row_num,col_num,Branch) col_num+=1 elif type(Info) is list: cur_col=col_num for AutInfo in Info: # -->dict author=AutInfo.keys() # -->list if debugLog >= debugLogLevel[-2]: print "Author Name: ",author ws.set_column(row_num,cur_col,len(author[0])) ws.write(row_num,cur_col,author[0]) # Save Author Name cur_col+=1 CiLog = AutInfo.values() patten = re.compile(r"\w{39}\s") CiInfo = re.findall(patten,CiLog[0]) CiCnt = len(CiInfo) ws.write(row_num,cur_col,CiCnt) # Save Ci Num cur_col+=1 for x in CiLog: if debugLog >= debugLogLevel[-2]: print " Commit info: " print x ws.write(row_num,cur_col,x) # Save Ci Log row_num += 1 #!!! Row + 1!!! cur_col += 1 cur_col=col_num # Save Author Commit Num Chart chart_author_ci = wb.add_chart({"type":"column"}) chart_author_ci.set_style(11) auth_x_abs = xl_range_abs(row_num-len(Info),col_num,row_num-1,col_num) auth_y_abs = xl_range_abs(row_num-len(Info),col_num+1,row_num-1,col_num+1) auth_cat="="+ws.get_name()+"!"+auth_x_abs auth_val="="+ws.get_name()+"!"+auth_y_abs chart_author_ci.add_series({ "categories":auth_cat, "values":auth_val }) ws.insert_chart(row_num,col_num,chart_author_ci) row_num += charts_interval_row else: print "ERROR: Branches info in not Branch name and Author commit info:" print Info print "Type is ",type(Info) col_num = 0 row_num += branch_interval_lines # Add lines interval else: print "WARNING: NO Author commmit in repo",rep," branch"
def SaveRepoStat(self,wb,ws_repo): row_num=0 col_num=0 #Save All repos if debugLog >= debugLogLevel[-1]: print "Repos:" print self.RepoCntSum repo_list = self.RepoCntSum.keys() for i in range(0,len(repo_list)): ws_repo.set_column(row_num,col_num,len(repo_list[i])) ws_repo.write(row_num,col_num,repo_list[i]) col_num += 1 row_num += 1 #!!!row + 1 counte save to next row col_num = 0 repo_values = self.RepoCntSum.values() for i in range(0,len(repo_values)): ws_repo.write(row_num,col_num,repo_values[i]) col_num += 1 row_num += 1 #!!!row + 1 # Repos chart chart_repo = wb.add_chart({"type":"column"}) chart_repo.set_style(11) repo_x_abs = xl_range_abs(0,0,0,len(repo_list)-1) repo_y_abs = xl_range_abs(1,0,1,len(repo_list)-1) rep_cat="="+ws_repo.get_name()+"!"+repo_x_abs rep_val="="+ws_repo.get_name()+"!"+repo_y_abs chart_repo.add_series({ "categories":rep_cat, "values":rep_val }) ws_repo.insert_chart(row_num,1,chart_repo) row_num += charts_interval_row row_num += 2 col_num = 0 #Save every repo branches info for x in self.RepoBraCntSum.items(): if debugLog >= debugLogLevel[1]: print "Stat: Repo Branch:" print x ws_repo.write(row_num,col_num,x[0]) col_num += 1 branches_list = x[1].keys() for i in range(0,len(branches_list)): ws_repo.set_column(row_num,col_num,len(branches_list[i])) ws_repo.write(row_num,col_num,branches_list[i]) col_num += 1 row_num += 1 # counte save to next row col_num -= len(branches_list) branches_values = x[1].values() for i in range(0,len(branches_values)): ws_repo.write(row_num,col_num,branches_values[i]) col_num += 1 row_num += 1 # !!!row + 1 # Save Branch Chart chart_branch = wb.add_chart({"type":"column"}) chart_branch.set_style(11) bran_x_abs = xl_range_abs(row_num-2,1,row_num-2,len(branches_list)) bran_y_abs = xl_range_abs(row_num-1,1,row_num-1,len(branches_values)) bran_cat="="+ws_repo.get_name()+"!"+bran_x_abs bran_val="="+ws_repo.get_name()+"!"+bran_y_abs chart_branch.add_series({ "categories":bran_cat, "values":bran_val }) ws_repo.insert_chart(row_num,1,chart_branch) row_num += charts_interval_row col_num = 0 # fix col_num is not reset bug row_num += 2 col_num = 0 # Save Author commit num info ws_repo.write(row_num,col_num,"Author:") row_num += 1 ws_repo.write(row_num,col_num,"Commit Num:") row_num -= 1 col_num += 1 auth_list = self.AuthorCiSum.keys() for i in range(0,len(auth_list)): ws_repo.write(row_num,col_num,auth_list[i]) col_num += 1 row_num += 1 # counte save to next row col_num -= len(auth_list) auth_values = self.AuthorCiSum.values() for i in range(0,len(auth_values)): ws_repo.write(row_num,col_num,auth_values[i]) col_num += 1 row_num += 1 #!!! row + 1 # Save Author Chart chart_author = wb.add_chart({"type":"column"}) chart_author.set_style(11) auth_x_abs = xl_range_abs(row_num-2,1,row_num-2,len(auth_list)) auth_y_abs = xl_range_abs(row_num-1,1,row_num-1,len(auth_values)) auth_cat="="+ws_repo.get_name()+"!"+auth_x_abs auth_val="="+ws_repo.get_name()+"!"+auth_y_abs chart_author.add_series({ "categories":auth_cat, "values":auth_val }) ws_repo.insert_chart(row_num,1,chart_author) row_num += charts_interval_row
def _ranges_by_col(df, startrow, startcol): for col, (k, v) in enumerate(df.items(), start=startcol): yield k, xl_utl.xl_range_abs(startrow, col, startrow + len(v) - 1, col)
def _ranges_by_row(df, startrow, startcol): for row, (k, v) in enumerate(df.iterrows(), start=startrow): yield k, xl_utl.xl_range_abs(row, startcol, row, startcol + len(v) - 1)
def into_xl(): """Creates an excel spreadsheet with results.""" # Filter out wave direction df = pd.read_table('results.txt').groupby(['WaveHs', 'WaveTp']).max().reset_index() writer = pd.ExcelWriter('Results.xlsx') for i, col in enumerate(df.columns[3:]): pv = df.pivot(index='WaveHs', columns='WaveTp', values=col).fillna('na') pd.DataFrame(data=[col]).to_excel(writer, sheet_name='Results', # overkill =P header=False, index=False, startrow=i*(len(pv)+3)) pv.to_excel(writer, sheet_name='Results', index_label='Hs\Tp', startrow=1+i*(len(pv)+3)) workbook = writer.book worksheet = writer.sheets['Results'] # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': True}) bold_borders = workbook.add_format({'bold': True, 'border': True}) borders = workbook.add_format({'border': True}) italic = workbook.add_format({'italic': True}) # Light red fill with dark red text. red = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) # Light yellow fill with dark yellow text. yellow = workbook.add_format({'bg_color': '#FFEB9C', 'font_color': '#9C6500'}) # Green fill with dark green text. green = workbook.add_format({'bg_color': '#C6EFCE', 'font_color': '#006100'}) # Blank fill with black text. blank = workbook.add_format({'bg_color': 'white', 'font_color': 'black'}) # Write cells for user input of systems limitations limits_col = len(pv.columns) + 2 worksheet.write(1, limits_col, "AHC System's Limitations", bold) worksheet.write(2, limits_col, "Amplitude [m]") worksheet.write(2, limits_col+3, "Note: this is amplitude, half of the displacement.", italic) worksheet.write(3, limits_col, "Velocity [m/s]") worksheet.write(4, limits_col, "Acceleration [m/s²]") worksheet.write(2, limits_col+2, 1, yellow) worksheet.write(3, limits_col+2, 1, yellow) worksheet.write(4, limits_col+2, 1, yellow) # Apply a conditional format to the tables. height = len(pv) # height of table gap = 3 # gap between tables for i in range(3): top_rw = 2+i*(height+gap) target = xlutil.xl_range_abs(top_rw, 1, top_rw+height-1, len(pv.columns)) limit = xlutil.xl_rowcol_to_cell(2+i, limits_col+2, row_abs=True, col_abs=True) worksheet.conditional_format(target, {'type': 'cell', 'criteria': '==', 'value': '"na"', 'format': blank}) worksheet.conditional_format(target, {'type': 'cell', 'criteria': '>', 'value': limit, 'format': red}) worksheet.conditional_format(target, {'type': 'cell', 'criteria': '<=', 'value': limit, 'format': green}) # Finally create an overall weather window table worksheet.write(6, limits_col, "Operability Window", bold) worksheet.write(7, limits_col, "Hs\Tp", bold_borders) for i, hs in enumerate(pv.index.values): rw = 7+i+1 worksheet.write(rw, limits_col, hs, bold_borders) for j, tp in enumerate(pv.columns.values): cl = limits_col+j+1 worksheet.write(7, cl, tp, bold_borders) worksheet.write(rw, cl, do_formula(i, j, limits_col, height, gap), borders) # and apply green-red formating target = xlutil.xl_range_abs(7+1, limits_col+1, 7+height, limits_col+len(pv.columns)) worksheet.conditional_format(target, {'type': 'cell', 'criteria': '==', 'value': 'TRUE', 'format': green}) worksheet.conditional_format(target, {'type': 'cell', 'criteria': '==', 'value': 'FALSE', 'format': red}) writer.save()
def create_spreadsheet(parts, prj_info, spreadsheet_filename, user_fields, variant): '''Create a spreadsheet using the info for the parts (including their HTML trees).''' logger.log(DEBUG_OVERVIEW, 'Creating the \'{}\' spreadsheet...'.format( os.path.basename(spreadsheet_filename)) ) MAX_LEN_WORKSHEET_NAME = 31 # Microsoft Excel allows a 31 caracheters longer # string for the worksheet name, Google #SpreadSheet 100 and LibreOffice Calc have no limit. DEFAULT_BUILD_QTY = 100 # Default value for number of boards to build. WORKSHEET_NAME = os.path.splitext(os.path.basename(spreadsheet_filename))[0] # Default name for pricing worksheet. if len(variant) > 0: # Append an indication of the variant to the worksheet title. # Remove any special characters that might be illegal in a # worksheet name since the variant might be a regular expression. # Fix the maximum worksheet name, priorize the variant string cutting # the board project. variant = re.sub('[\[\]\\\/\|\?\*\:\(\)]','_', variant[:(MAX_LEN_WORKSHEET_NAME)]) WORKSHEET_NAME += '.' WORKSHEET_NAME = WORKSHEET_NAME[:(MAX_LEN_WORKSHEET_NAME-len(variant))] WORKSHEET_NAME += variant # Create spreadsheet file. with xlsxwriter.Workbook(spreadsheet_filename) as workbook: # Create the various format styles used by various spreadsheet items. wrk_formats = { 'global': workbook.add_format({ 'font_size': 14, 'font_color': 'white', 'bold': True, 'align': 'center', 'valign': 'vcenter', 'bg_color': '#303030' }), 'header': workbook.add_format({ 'font_size': 12, 'bold': True, 'align': 'center', 'valign': 'top', 'text_wrap': True }), 'board_qty': workbook.add_format({ 'font_size': 13, 'bold': True, 'align': 'right' }), 'total_cost_label': workbook.add_format({ 'font_size': 13, 'bold': True, 'align': 'right', 'valign': 'vcenter' }), 'unit_cost_label': workbook.add_format({ 'font_size': 13, 'bold': True, 'align': 'right', 'valign': 'vcenter' }), 'total_cost_currency': workbook.add_format({ 'font_size': 13, 'font_color': 'red', 'bold': True, 'num_format': '$#,##0.00', 'valign': 'vcenter' }), 'unit_cost_currency': workbook.add_format({ 'font_size': 13, 'font_color': 'green', 'bold': True, 'num_format': '$#,##0.00', 'valign': 'vcenter' }), 'proj_info_field': workbook.add_format({ 'font_size': 13, 'bold': True, 'align': 'right', 'valign': 'vcenter' }), 'proj_info': workbook.add_format({ 'font_size': 12, 'align': 'left', 'valign': 'vcenter' }), 'part_format': workbook.add_format({ 'valign': 'vcenter' }), 'found_part_pct': workbook.add_format({ 'font_size': 10, 'bold': True, 'italic': True, 'valign': 'vcenter' }), 'best_price': workbook.add_format({'bg_color': '#80FF80', }), 'not_manf_codes': workbook.add_format({'bg_color': '#AAAAAA'}), 'not_available': workbook.add_format({'bg_color': '#FF0000', 'font_color':'white'}), 'order_too_much': workbook.add_format({'bg_color': '#FF0000', 'font_color':'white'}), 'too_few_available': workbook.add_format({'bg_color': '#FF9900', 'font_color':'black'}), 'too_few_purchased': workbook.add_format({'bg_color': '#FFFF00'}), 'not_stocked': workbook.add_format({'font_color': '#909090', 'align': 'right', 'valign': 'vcenter'}), 'currency': workbook.add_format({'num_format': '$#,##0.00', 'valign': 'vcenter'}), } # Add the distinctive header format for each distributor to the dict of formats. for d in distributor_dict: wrk_formats[d] = workbook.add_format(distributor_dict[d]['wrk_hdr_format']) # Create the worksheet that holds the pricing information. wks = workbook.add_worksheet(WORKSHEET_NAME) # Set the row & column for entering the part information in the sheet. START_COL = 0 BOARD_QTY_ROW = 0 UNIT_COST_ROW = BOARD_QTY_ROW + 1 TOTAL_COST_ROW = BOARD_QTY_ROW + 2 START_ROW = 1+3*len(prj_info) LABEL_ROW = START_ROW + 1 COL_HDR_ROW = LABEL_ROW + 1 FIRST_PART_ROW = COL_HDR_ROW + 1 LAST_PART_ROW = COL_HDR_ROW + len(parts) - 1 next_row = 0 # Load the global part information (not distributor-specific) into the sheet. # next_col = the column immediately to the right of the global data. # qty_col = the column where the quantity needed of each part is stored. next_col, refs_col, qty_col = add_globals_to_worksheet( wks, wrk_formats, START_ROW, START_COL, TOTAL_COST_ROW, parts, user_fields) # Create a defined range for the global data. workbook.define_name( 'global_part_data', '={wks_name}!{data_range}'.format( wks_name= "'" + WORKSHEET_NAME + "'", data_range=xl_range_abs(START_ROW, START_COL, LAST_PART_ROW, next_col - 1))) for i_prj in range(len(prj_info)): # Add project information to track the project (in a printed version # of the BOM) and the date because of price variations. i_prj_str = (str(i_prj) if len(prj_info)>1 else '') wks.write(next_row, START_COL, 'Prj{}:'.format(i_prj_str), wrk_formats['proj_info_field']) wks.write(next_row, START_COL+1, prj_info[i_prj]['title'], wrk_formats['proj_info']) wks.write(next_row+1, START_COL, 'Co.:', wrk_formats['proj_info_field']) wks.write(next_row+1, START_COL+1, prj_info[i_prj]['company'], wrk_formats['proj_info']) wks.write(next_row+2, START_COL, 'Prj date:', wrk_formats['proj_info_field']) wks.write(next_row+2, START_COL+1, prj_info[i_prj]['date'], wrk_formats['proj_info']) # Create the cell where the quantity of boards to assemble is entered. # Place the board qty cells near the right side of the global info. wks.write(next_row, next_col - 2, 'Board Qty{}:'.format(i_prj_str), wrk_formats['board_qty']) wks.write(next_row, next_col - 1, DEFAULT_BUILD_QTY, wrk_formats['board_qty']) # Set initial board quantity. # Define the named cell where the total board quantity can be found. workbook.define_name('BoardQty{}'.format(i_prj_str), '={wks_name}!{cell_ref}'.format( wks_name="'" + WORKSHEET_NAME + "'", cell_ref=xl_rowcol_to_cell(next_row, next_col - 1, row_abs=True, col_abs=True))) # Create the cell to show total cost of board parts for each distributor. wks.write(next_row + 2, next_col - 2, 'Total Cost{}:'.format(i_prj_str), wrk_formats['total_cost_label']) # Define the named cell where the total cost can be found. workbook.define_name('TotalCost{}'.format(i_prj_str), '={wks_name}!{cell_ref}'.format( wks_name="'" + WORKSHEET_NAME + "'", cell_ref=xl_rowcol_to_cell(next_row + 2*(1+i_prj), next_col - 1, row_abs=True, col_abs=True)) ) # Create the cell to show unit cost of (each project) board parts. wks.write(next_row+1, next_col - 2, 'Unit Cost{}:'.format(i_prj_str), wrk_formats['unit_cost_label']) wks.write(next_row+1, next_col - 1, "=TotalCost{}/BoardQty{}".format(i_prj_str, i_prj_str), wrk_formats['unit_cost_currency']) next_row += 3 # Add geral information of the scrap to track price modifications. wks.write(next_row, START_COL, '$ date:', wrk_formats['proj_info_field']) wks.write(next_row, START_COL+1, datetime.now().strftime("%Y-%m-%d %H:%M:%S"), wrk_formats['proj_info']) # Add the total cost of all projcts together. if len(prj_info)>1: # Create the row to show total cost of board parts for each distributor. wks.write(next_row, next_col - 2, 'Total Prjs Cost:', wrk_formats['total_cost_label']) # Define the named cell where the total cost can be found. workbook.define_name('TotalCost', '={wks_name}!{cell_ref}'.format( wks_name="'" + WORKSHEET_NAME + "'", cell_ref=xl_rowcol_to_cell(next_row, next_col - 1, row_abs=True, col_abs=True))) next_row += 1 # Freeze view of the global information and the column headers, but # allow the distributor-specific part info to scroll. wks.freeze_panes(COL_HDR_ROW, next_col) # Make a list of alphabetically-ordered distributors with web distributors before locals. logger.log(DEBUG_OVERVIEW, 'Sorting the distributors...') web_dists = sorted([d for d in distributor_dict if distributor_dict[d]['scrape'] != 'local']) local_dists = sorted([d for d in distributor_dict if distributor_dict[d]['scrape'] == 'local']) dist_list = web_dists + local_dists # Load the part information from each distributor into the sheet. logger.log(DEBUG_OVERVIEW, 'Writting the distributors parts informations...') for dist in dist_list: dist_start_col = next_col next_col = add_dist_to_worksheet(wks, wrk_formats, START_ROW, dist_start_col, UNIT_COST_ROW, TOTAL_COST_ROW, refs_col, qty_col, dist, parts) # Create a defined range for each set of distributor part data. workbook.define_name( '{}_part_data'.format(dist), '={wks_name}!{data_range}'.format( wks_name="'" + WORKSHEET_NAME + "'", data_range=xl_range_abs(START_ROW, dist_start_col, LAST_PART_ROW, next_col - 1))) # Add the KiCost package inormation at the end of the spreadsheet to debug # information at the forum and "advertising". wks.write(START_ROW+len(parts)+3, START_COL, 'Distributors scraped by KiCost\N{REGISTERED SIGN} v.' + __version__, wrk_formats['proj_info'])
def enter_order_info(info_col, order_col, numeric=False, delimiter=''): # This function enters a function into a spreadsheet cell that # prints the information found in info_col into the order_col column # of the order. # This very complicated spreadsheet function does the following: # 1) Computes the set of row indices in the part data that have # non-empty cells in sel_range1 and sel_range2. (Innermost # nested IF and ROW commands.) sel_range1 and sel_range2 are # the part's catalog number and purchase quantity. # 2) Selects the k'th smallest of the row indices where k is the # number of rows between the current part row in the order and the # top row of the order. (SMALL() and ROW() commands.) # 3) Gets the cell contents from the get_range using the k'th # smallest row index found in step #2. (INDEX() command.) # 4) Converts the cell contents to a string if it is numeric. # (num_to_text_func is used.) Otherwise, it's already a string. # 5) CONCATENATES the string from step #4 with the delimiter # that goes between fields of an order for a part. # (CONCATENATE() command.) # 6) If any error occurs (which usually means the indexed cell # contents were blank), then a blank is printed. Otherwise, # the string from step #5 is printed in this cell. order_info_func = ''' IFERROR( CONCATENATE( {num_to_text_func}( INDEX( {get_range}, SMALL( IF( {sel_range2} <> "", IF( {sel_range1} <> "", ROW({sel_range1}) - MIN(ROW({sel_range1})) + 1, "" ), "" ), ROW()-ROW({order_first_row})+1 ) ) {num_to_text_fmt} ), {delimiter} ), "" ) ''' # Strip all the whitespace from the function string. order_info_func = re.sub('[\s\n]', '', order_info_func) # This sets the function and conversion format to use if # numeric cell contents have to be converted to a string. if numeric: num_to_text_func = 'TEXT' num_to_text_fmt = ',"##0"' else: num_to_text_func = '' num_to_text_fmt = '' # This puts the order column delimiter into a form acceptable in a spreadsheet formula. if delimiter != '': delimiter = '"{}"'.format(delimiter) # These are the columns where the part catalog numbers and purchase quantities can be found. purch_qty_col = start_col + columns['purch']['col'] part_num_col = start_col + columns['part_num']['col'] # Now write the order_info_func into every row of the order in the given column. for r in range(ORDER_FIRST_ROW, ORDER_LAST_ROW + 1): wks.write_array_formula( xl_range(r, order_col, r, order_col), '{{={func}}}'.format(func=order_info_func.format( order_first_row=xl_rowcol_to_cell(ORDER_FIRST_ROW, 0, row_abs=True), sel_range1=xl_range_abs(PART_INFO_FIRST_ROW, purch_qty_col, PART_INFO_LAST_ROW, purch_qty_col), sel_range2=xl_range_abs(PART_INFO_FIRST_ROW, part_num_col, PART_INFO_LAST_ROW, part_num_col), get_range=xl_range_abs(PART_INFO_FIRST_ROW, info_col, PART_INFO_LAST_ROW, info_col), delimiter=delimiter, num_to_text_func=num_to_text_func, num_to_text_fmt=num_to_text_fmt)))
def into_xl(): """Creates an excel spreadsheet with results.""" # Filter out wave direction df = pd.read_table('results.txt').groupby(['WaveHs', 'WaveTp']).max().reset_index() writer = pd.ExcelWriter('Results.xlsx') for i, col in enumerate(df.columns[3:]): pv = df.pivot(index='WaveHs', columns='WaveTp', values=col).fillna('na') pd.DataFrame(data=[col]).to_excel( writer, sheet_name='Results', # overkill =P header=False, index=False, startrow=i * (len(pv) + 3)) pv.to_excel(writer, sheet_name='Results', index_label='Hs\Tp', startrow=1 + i * (len(pv) + 3)) workbook = writer.book worksheet = writer.sheets['Results'] # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': True}) bold_borders = workbook.add_format({'bold': True, 'border': True}) borders = workbook.add_format({'border': True}) italic = workbook.add_format({'italic': True}) # Light red fill with dark red text. red = workbook.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'}) # Light yellow fill with dark yellow text. yellow = workbook.add_format({ 'bg_color': '#FFEB9C', 'font_color': '#9C6500' }) # Green fill with dark green text. green = workbook.add_format({ 'bg_color': '#C6EFCE', 'font_color': '#006100' }) # Blank fill with black text. blank = workbook.add_format({'bg_color': 'white', 'font_color': 'black'}) # Write cells for user input of systems limitations limits_col = len(pv.columns) + 2 worksheet.write(1, limits_col, "AHC System's Limitations", bold) worksheet.write(2, limits_col, "Amplitude [m]") worksheet.write(2, limits_col + 3, "Note: this is amplitude, half of the displacement.", italic) worksheet.write(3, limits_col, "Velocity [m/s]") worksheet.write(4, limits_col, "Acceleration [m/s²]") worksheet.write(2, limits_col + 2, 1, yellow) worksheet.write(3, limits_col + 2, 1, yellow) worksheet.write(4, limits_col + 2, 1, yellow) # Apply a conditional format to the tables. height = len(pv) # height of table gap = 3 # gap between tables for i in range(3): top_rw = 2 + i * (height + gap) target = xlutil.xl_range_abs(top_rw, 1, top_rw + height - 1, len(pv.columns)) limit = xlutil.xl_rowcol_to_cell(2 + i, limits_col + 2, row_abs=True, col_abs=True) worksheet.conditional_format(target, { 'type': 'cell', 'criteria': '==', 'value': '"na"', 'format': blank }) worksheet.conditional_format(target, { 'type': 'cell', 'criteria': '>', 'value': limit, 'format': red }) worksheet.conditional_format(target, { 'type': 'cell', 'criteria': '<=', 'value': limit, 'format': green }) # Finally create an overall weather window table worksheet.write(6, limits_col, "Operability Window", bold) worksheet.write(7, limits_col, "Hs\Tp", bold_borders) for i, hs in enumerate(pv.index.values): rw = 7 + i + 1 worksheet.write(rw, limits_col, hs, bold_borders) for j, tp in enumerate(pv.columns.values): cl = limits_col + j + 1 worksheet.write(7, cl, tp, bold_borders) worksheet.write(rw, cl, do_formula(i, j, limits_col, height, gap), borders) # and apply green-red formating target = xlutil.xl_range_abs(7 + 1, limits_col + 1, 7 + height, limits_col + len(pv.columns)) worksheet.conditional_format(target, { 'type': 'cell', 'criteria': '==', 'value': 'TRUE', 'format': green }) worksheet.conditional_format(target, { 'type': 'cell', 'criteria': '==', 'value': 'FALSE', 'format': red }) writer.save()
print(xl_rowcol_to_cell(0, 0)) # A1 print(xl_rowcol_to_cell(0, 1)) # B1 print(xl_rowcol_to_cell(1, 0)) # A2 print(xl_rowcol_to_cell(0, 0, col_abs=True)) # $A1 print(xl_rowcol_to_cell(0, 0, row_abs=True)) # A$1 print(xl_rowcol_to_cell(0, 0, row_abs=True, col_abs=True)) # $A$1 (row, col) = xl_cell_to_rowcol("A1") # (0, 0) (row, col) = xl_cell_to_rowcol("B1") # (0, 1) (row, col) = xl_cell_to_rowcol("C2") # (1, 2) (row, col) = xl_cell_to_rowcol("$C2") # (1, 2) (row, col) = xl_cell_to_rowcol("C$2") # (1, 2) (row, col) = xl_cell_to_rowcol("$C$2") # (1, 2) column = xl_col_to_name(0) # A column = xl_col_to_name(1) # B column = xl_col_to_name(702) # AAA column = xl_col_to_name(0, False) # A column = xl_col_to_name(0, True) # $A column = xl_col_to_name(1, True) # $B cell_range = xl_range(0, 0, 9, 0) # A1:A10 cell_range = xl_range(1, 2, 8, 2) # C2:C9 cell_range = xl_range(0, 0, 3, 4) # A1:E4 cell_range = xl_range_abs(0, 0, 9, 0) # $A$1:$A$10 cell_range = xl_range_abs(1, 2, 8, 2) # $C$2:$C$9 cell_range = xl_range_abs(0, 0, 3, 4) # $A$1:$E$4