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