if ip_address == '' and last_ip == '':
# 获取不到ip地址，上次ip地址为空，ip地址属性就为空
ip_attr = ''
l.write(i, 4, ip_attr)
elif ip_address == '' and last_ip:
# 获取不到ip地址，有上次ip地址，就什么都不做，保留之前的值
pass
elif ip_address:
# 获取到了，就写入ip地址属性
l.write(i, 4, ip_attr)
if assign_ip:
# 注意使用公式的时候，不要加上=符号
# 只有规划好ip地址的用户，才需要进行比对操作
l.write(
i, 12,
xlwt.Formula('IF(F' + str(i + 1) + '=L' + str(i + 1) +
',"","1")'))
l.write(
i, 13,
xlwt.Formula('IF(J' + str(i + 1) + '=K' + str(i + 1) +
',"","1")'))
# 保存对excel表格的更改
try:
newb.save(u'C:\\Users\\DELL\\Desktop\\详细工作项\\加固计划\\关联表\\关联表.xls')
print '\n已经成功写入数据!'
except IOError:
print '\n报错啦!!!'
print '关联表已经被你用EXCEL打开了，保存不了，请关闭excel。'
def crear_hoja_excel(libro, sheet_name, queryset, titulo, subtitulo,
subsubtitulo, encabezados_1, encabezados, celdas,
tipo_totales):
hoja = libro.add_sheet(sheet_name)
indice_fila, indice_columna = 0, 0
columns_number = len(encabezados) + 1
# ESCRIBIR TITULO
hoja.write_merge(indice_fila, indice_fila, 0,
indice_columna + columns_number, titulo, HEADER2)
indice_fila += 1
# ESCRIBIR SUBTITULO
hoja.write_merge(indice_fila, indice_fila, 0,
indice_columna + columns_number, subtitulo, HEADER2)
# agregando fila para subsubtitulo(Información Historica)
if subsubtitulo:
indice_fila += 1
hoja.write_merge(indice_fila, indice_fila, 0,
indice_columna + columns_number, subsubtitulo,
HEADER2)
indice_fila += 2
if encabezados_1:
columna_index = 0
for i, encabezado in enumerate(encabezados_1):
if isinstance(encabezado, dict):
size = encabezado['size']
hoja.write_merge(indice_fila, indice_fila, columna_index,
(columna_index + size - 1),
encabezado['titulo'],
COLUMN_HEADER_FORMAT_SIN_RELLENO)
columna_index += size
else:
hoja.write(indice_fila, columna_index, encabezado,
COLUMN_HEADER_FORMAT_SIN_RELLENO)
columna_index += 1
indice_fila += 1
# ESCRIBIR ENCABEZADOS
i2 = 0
for i, encabezado in enumerate(encabezados):
hoja.write(indice_fila, indice_columna + i + i2,
encabezado.capitalize(), COLUMN_HEADER_FORMAT_SIN_RELLENO)
hoja.col(indice_columna + i + i2).width = 256 * 30
# excepción de columnas extras para oim7
if '7' in sheet_name or 'aci-ago' in sheet_name \
and i > 0:
i2 += 1
hoja.write(indice_fila, indice_columna + i + i2, '%',
COLUMN_HEADER_FORMAT_SIN_RELLENO)
# ESCRIBIR CELDAS
totales = {}
for row in queryset:
indice_fila += 1
c2 = 0
for c, atributo in enumerate(celdas):
value = obtener_valor(row, atributo)
if isinstance(value, dict):
formato = NUMBER_FORMAT
if len(value.values()) > 1:
for subvalue in value.values():
if '%' in str(subvalue):
subvalue = Decimal(subvalue.replace('%', ''))
hoja.write(indice_fila, indice_columna + c + c2,
subvalue, formato)
c2 += 1
else:
subvalue = Decimal('0')
hoja.write(indice_fila, indice_columna + c + c2, subvalue,
formato)
c2 += 1
hoja.write(indice_fila, indice_columna + c + c2, subvalue,
formato)
c2 += 1
c2 -= 1
elif isinstance(value, Decimal):
value = value if isinstance(value, Decimal) else Decimal(
"{0}".format(value))
valor_anterior = totales.get(atributo, Decimal("0"))
totales[atributo] = valor_anterior + value
formato = PERCENTAGE_FORMAT if "/" in atributo else NUMBER_FORMAT
hoja.write(indice_fila, indice_columna + c + c2, value,
formato)
else:
value = value if value != 0 else "-"
hoja.write(indice_fila, indice_columna + c + c2,
unicode(value), LEFT_FORMAT)
if tipo_totales:
# ESCRIBIR FILA DE TOTALES
indice_fila += 1
if 'descripcion' in celdas:
for anio in celdas[1:]:
celdas.append(anio)
tipo_totales.append('SUM')
for c, atributo in enumerate(celdas):
if 'percent' not in atributo and 'porcentaje' not in atributo:
if c > 0:
if tipo_totales[c] == "/":
formula = 'IF({2}<>0;{0}{1}{2};0)'.format(
0, tipo_totales[c], 0)
formato = TOTAL_PERCENTAGE_FORMAT
else:
formula = '{0}({1}:{2})'.format(
tipo_totales[c],
xlwt.Utils.rowcol_to_cell(4, indice_columna + c),
xlwt.Utils.rowcol_to_cell(indice_fila - 1,
indice_columna + c))
formato = TOTAL_ROW_FORMAT
hoja.write(indice_fila, indice_columna + c,
xlwt.Formula(formula), formato)
else:
hoja.write(indice_fila, indice_columna, tipo_totales[c],
TOTAL_ROW_FORMAT)
return hoja
# usr/bin
# _*_ coding:utf-8 _*_
import xlwt
from datetime import datetime
style0 = xlwt.easyxf('font:name Times New Roman,color-index red,bold on',
num_format_str='#,##0.00')
style2 = xlwt.easyxf('font:name Times New Roman,color-index blue,bold on',
num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet('just_for_test')
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
ws.write(3, 2, xlwt.Formula("A3+B3"), style2)
wb.save("mytest.xls")
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
wanted_list_details = _p.wanted_list_details
self.col_specs_template_overview.update(_p.template_update_overview)
self.col_specs_template_details.update(_p.template_update_details)
_ = _p._
for r in _p.reports:
title_short = r['title_short'].replace('/', '-')
ws_o = wb.add_sheet(title_short)
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos_o = 0
row_pos_d = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Title
## Company ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join(
[_p.company.name, r['title'], _p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
## Text + Tgl ##
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' '.join([
_('LAPORAN Program Subsidi Per Tanggal'),
_(str(datetime.today().date())), _p.report_info
])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
## Tanggal Trx Start Date & End Date ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join([
_('Tanggal Transaksi'),
_('-' if data['trx_start_date'] ==
False else str(data['trx_start_date'])),
_('s/d'),
_('-' if data['trx_end_date'] ==
False else str(data['trx_end_date'])), _p.report_info
])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
row_pos_o += 1
# Report Column Headers
c_specs_o = map(
lambda x: self.render(x,
self.col_specs_template_overview,
'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
# Columns and Rows
no = 0
for p in r['id_ai']:
c_specs_o = map(
lambda x: self.render(x, self.col_specs_template_overview,
'lines'), wanted_list_overview)
for x in c_specs_o:
if x[0] == 'no':
no += 1
x[4] = no
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.pd_cell_style)
row_data_end = row_pos_o
# # Totals
# tot_invoice = 0
# amount_residual = 0
# belum_jatuh_tempo = 0
# overdue_1_30 = 0
# overdue_31_60 = 0
# overdue_61_90 = 0
# for p in r['ids_aml']:
# tampung = map(
# lambda x: self.render(
# x, self.col_specs_template_overview, 'totals'),
# wanted_list_overview)
# for x in tampung :
# if x[0] == 'tot_invoice' and x[4] != False :
# tot_invoice += float(x[4])
# if x[0] == 'amount_residual' and x[4] != False :
# amount_residual += float(x[4])
# if x[0] == 'belum_jatuh_tempo' and x[4] != False :
# belum_jatuh_tempo += float(x[4])
# if x[0] == 'overdue_1_30' and x[4] != False :
# overdue_1_30 += float(x[4])
# if x[0] == 'overdue_31_60' and x[4] != False :
# overdue_31_60 += float(x[4])
# if x[0] == 'overdue_61_90' and x[4] != False :
# overdue_61_90 += float(x[4])
#
# c_specs_o = map(
# lambda x: self.render(
# x, self.col_specs_template_overview, 'totals'),
# wanted_list_overview)
# for x in c_specs_o :
# if x[0] == 'tot_invoice' :
# x[4] = tot_invoice
# if x[0] == 'amount_residual' :
# x[4] = amount_residual
# if x[0] == 'belum_jatuh_tempo' :
# x[4] = belum_jatuh_tempo
# if x[0] == 'overdue_1_30' :
# x[4] = overdue_1_30
# if x[0] == 'overdue_31_60' :
# x[4] = overdue_31_60
# if x[0] == 'overdue_61_90' :
# x[4] = overdue_61_90
# row_data = self.xls_row_template(
# c_specs_o, [x[0] for x in c_specs_o])
# row_pos_o = self.xls_write_row(
# ws_o, row_pos_o, row_data, row_style=self.rh_cell_style,
# set_column_size=True)
# row_pos_o += 1
#
# # Footer
# cell_style = xlwt.easyxf(_xs['left'])
# report_name = ' '.join(
# [_(_p.report_date), _(str(self.pool.get('res.users').browse(self.cr, self.uid, self.uid).name)),
# _p.report_info])
# c_specs_o = [
# ('report_name', 1, 0, 'text', report_name),
# ]
# row_data = self.xls_row_template(c_specs_o, ['report_name'])
# row_pos_o = self.xls_write_row(
# ws_o, row_pos_o, row_data, row_style=cell_style)
# row_pos_o += 2
# Totals
ws_o.write(row_pos_o, 0, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 3, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 4, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 5, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 6, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 7, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 8, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 9, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 10, None, self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 11,
xlwt.Formula("SUM(L" + str(row_data_begin) + ":L" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 12,
xlwt.Formula("SUM(M" + str(row_data_begin) + ":M" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 13,
xlwt.Formula("SUM(N" + str(row_data_begin) + ":N" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 14,
xlwt.Formula("SUM(O" + str(row_data_begin) + ":O" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 15,
xlwt.Formula("SUM(P" + str(row_data_begin) + ":P" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 16,
xlwt.Formula("SUM(Q" + str(row_data_begin) + ":Q" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 17,
xlwt.Formula("SUM(R" + str(row_data_begin) + ":R" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 18,
xlwt.Formula("SUM(S" + str(row_data_begin) + ":S" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 19,
xlwt.Formula("SUM(T" + str(row_data_begin) + ":T" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 20,
xlwt.Formula("SUM(U" + str(row_data_begin) + ":U" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 21,
xlwt.Formula("SUM(V" + str(row_data_begin) + ":V" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 22,
xlwt.Formula("SUM(W" + str(row_data_begin) + ":W" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 23,
xlwt.Formula("SUM(X" + str(row_data_begin) + ":X" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 24,
xlwt.Formula("SUM(Y" + str(row_data_begin) + ":Y" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
# Footer
ws_o.write(row_pos_o + 1, 0, None)
ws_o.write(
row_pos_o + 2, 0, _p.report_date + ' ' + str(
self.pool.get('res.users').browse(self.cr, self.uid,
self.uid).name))
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
self.col_specs_template_overview.update({})
_ = _p._
for r in _p.reports:
ws_o = wb.add_sheet('Laporan Penjualan By Salesman')
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
# Landscape
ws.portrait = 0
ws.fit_width_to_pages = 1
row_pos_o = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Company Name
cell_style = xlwt.easyxf(_xs['left'])
c_specs_o = [('report_name', 1, 0, 'text', _p.company.name)]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
c_specs_o = [('report_name', 1, 0, 'text',
'Laporan Penjualan By Salesman')]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
# Tanggal
cell_style = xlwt.easyxf(_xs['left'])
tanggal_str = ' '.join([
'Tanggal:',
self.format_tanggal(data['start_date']) + ' s.d. ' +
self.format_tanggal(data['end_date'])
])
c_specs_o = [('tanggal', 1, 0, 'text', tanggal_str)]
row_data = self.xls_row_template(c_specs_o, ['tanggal'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
# Longkap
cell_style = xlwt.easyxf(_xs['left'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
'',
row_style=cell_style)
# Report Column Headers
c_specs_o = map(
lambda x: self.render(x,
self.col_specs_template_overview,
'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
# Columns and Rows
no = 0
for p in r['datas']:
c_specs_o = map(
lambda x: self.render(x, self.col_specs_template_overview,
'lines'), wanted_list_overview)
for x in c_specs_o:
if x[0] == 'no':
no += 1
x[4] = no
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.pd_cell_style)
row_data_end = row_pos_o
# Totals
ws_o.write(row_pos_o, 0, None, self.ph_cell_style)
ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.ph_cell_style)
ws_o.write(row_pos_o, 3, None, self.ph_cell_style)
ws_o.write(row_pos_o, 4, None, self.ph_cell_style)
ws_o.write(row_pos_o, 5, None, self.ph_cell_style)
ws_o.write(row_pos_o, 6, None, self.ph_cell_style)
ws_o.write(row_pos_o, 7, None, self.ph_cell_style)
ws_o.write(row_pos_o, 8, None, self.ph_cell_style)
ws_o.write(row_pos_o, 9, None, self.ph_cell_style)
ws_o.write(row_pos_o, 10, None, self.ph_cell_style)
ws_o.write(row_pos_o, 11, None, self.ph_cell_style)
ws_o.write(row_pos_o, 12, None, self.ph_cell_style)
ws_o.write(
row_pos_o, 13,
xlwt.Formula("SUM(N" + str(row_data_begin) + ":N" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 14,
xlwt.Formula("SUM(O" + str(row_data_begin) + ":O" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 15,
xlwt.Formula("SUM(P" + str(row_data_begin) + ":P" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 16,
xlwt.Formula("SUM(Q" + str(row_data_begin) + ":Q" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 17,
xlwt.Formula("SUM(R" + str(row_data_begin) + ":R" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 18,
xlwt.Formula("SUM(S" + str(row_data_begin) + ":S" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 19,
xlwt.Formula("SUM(T" + str(row_data_begin) + ":T" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 20, None, self.ph_cell_style)
ws_o.write(row_pos_o, 21, None, self.ph_cell_style)
# Footer
ws_o.write(row_pos_o + 1, 0, None)
ws_o.write(
row_pos_o + 2, 0,
time.strftime('%d-%m-%Y %H:%M:%S') + ' ' + str(
self.pool.get('res.users').browse(self.cr, self.uid,
self.uid).name))
def generate_xls_report(self, _p, _xs, data, objects, wb):
data = _p.get_data()[0]
#line = _p.get_line(data['fiscalyear_id']['date_start'], data['start_date'], data['end_date'])
cr, uid = self.cr, self.uid
db_pool = pooler.get_pool(self.cr.dbname)
account_pool = db_pool.get('account.account')
company_currency_id = data['company_id']['currency_id']['id']
print "company_currency_id>>>>>>>>>>>>>>", company_currency_id
report_name = _("Summary Cash & Bank")
ws = wb.add_sheet(report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
##########
ws.col(0).width = len("ABC")*100
ws.col(1).width = len("ABC")*100
ws.col(4).width = len("ABC")*1000
##########
##Style
title = xlwt.easyxf('font: height 200, name Arial, colour_index black, bold on; align: horiz left;')
title2 = xlwt.easyxf('font: height 200, name Arial, colour_index black, bold on; align: horiz centre;')
##########
ws.write(2,3, data['company_id']['name'], title)
ws.write(3,3, "Distrik", title)
ws.write(3,4, ": "+data['district_id']['name'], title)
ws.write(4,3, "Tanggal", title)
ws.write(4,4, ": "+data['start_date']+" s/d "+data['end_date'], title)
ws.write_merge(6,6,2,7, "REKAPITULASI KAS / BANK HARIAN", title2)
ws.write_merge(8,9,2,3, "URAIAN", )
ws.write_merge(8,9,4,4, "SALDO AWAL", )
ws.write_merge(8,8,5,6, "MUTASI", )
ws.write(9,5, "DEBIT", )
ws.write(9,6, "CREDIT", )
ws.write_merge(8,9,7,7, "SALDO AKHIR", )
##########
row = 10
account_search = account_pool.search(cr,uid,[('type','=','liquidity')])
for account in account_pool.browse(cr,uid,account_search):
if account.currency_id.id and account.currency_id.id <> company_currency_id:
###Saldo Awal###
initial = 0.0
cr.execute("select sum(amount_currency) from account_move_line where account_id = %s and date >= %s and date < %s", (account.id,data['fiscalyear_id']['date_start'],data['start_date'])),
initial = cr.fetchone()[0] or 0.0
###Mutasi###
cr.execute("""select abs(sum(case when amount_currency>0.0 then amount_currency else 0.0 end)) as debit,
abs(sum(case when amount_currency<0.0 then amount_currency else 0.0 end)) as credit
from account_move_line
where account_id = %s and date >= %s and date <= %s
""", (account.id,data['start_date'],data['end_date'])),
line = cr.fetchall()[0]
else:
###Saldo Awal###
initial = 0.0
cr.execute("select sum(debit-credit) from account_move_line where account_id = %s and date >= %s and date < %s", (account.id,data['fiscalyear_id']['date_start'],data['start_date'])),
initial = cr.fetchone()[0] or 0.0
###Mutasi###
cr.execute("select sum(debit),sum(credit) from account_move_line where account_id = %s and date >= %s and date <= %s", (account.id,data['start_date'],data['end_date'])),
line = cr.fetchall()[0]
ws.write(row,2, account.name, )
ws.write(row+1,2, "Acc No. "+account.code, )
ws.write(row+1,4, initial, )
ws.write(row+1,5, line[0], )
ws.write(row+1,6, line[1], )
cell_initial = xlwt.Utils.rowcol_to_cell(row+1,4)
cell_debit = xlwt.Utils.rowcol_to_cell(row+1,5)
cell_credit = xlwt.Utils.rowcol_to_cell(row+1,6)
formula_sum = "(%s+%s-%s)"%(cell_initial,cell_debit,cell_credit)
ws.write(row+1,7, xlwt.Formula(formula_sum), )
row+=3
ws.write_merge(row,row+1,2,6, "TOTAL SALDO IN IDR", )
ws.write_merge(row,row+1,7,7, "", )
row+=2
ws.write_merge(row,row+1,2,6, "TOTAL SALDO IN USD", )
ws.write_merge(row,row+1,7,7, " ", )
##########
row+=2
ws.write(row,7, "Jakarta, "+time.strftime('%Y-%m-%d'), )
row+=1
ws.write(row,2, "Mengetahui,", )
ws.write(row,5, "Diperiksa,", )
ws.write(row,7, "Dibuat Oleh,", )
row+=5
ws.write(row,2, "Treasury Dept. Head", )
ws.write(row,5, "Treasury Sect. Head", )
ws.write(row,7, "Cashier", )
pass
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
self.col_specs_template_overview.update({})
_ = _p._
for r in _p.reports:
title_short = r['title_short'].replace('/', '-')
ws_o = wb.add_sheet(title_short)
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos_o = 0
row_pos_d = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Company Name
cell_style = xlwt.easyxf(_xs['left'])
c_specs_o = [
('report_name', 1, 0, 'text', _p.company.name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
c_specs_o = [
('report_name', 1, 0, 'text', 'LAPORAN PENJUALAN WO by NOPOL'),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
## Start Date & End Date ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join(
[_('Tanggal'), _('-' if data['start_date'] == False else str(data['start_date'])), _('s/d'), _('-' if data['end_date'] == False else str(data['end_date'])),
_p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
row_pos_o += 1
# Report Column Headers
c_specs_o = map(
lambda x: self.render(
x, self.col_specs_template_overview, 'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(
c_specs_o, [x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
# Columns and Rows
no = 0
for p in r['nopols']:
c_specs_o = map(
lambda x: self.render(
x, self.col_specs_template_overview, 'lines'),
wanted_list_overview)
for x in c_specs_o :
if x[0] == 'no' :
no += 1
x[4] = no
row_data = self.xls_row_template(
c_specs_o, [x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=self.pd_cell_style)
row_data_end = row_pos_o
# Totals
ws_o.write(row_pos_o, 0, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.rt_cell_style_decimal)
ws_o.set_vert_split_pos(3)
ws_o.write(row_pos_o, 3, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 4, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 5, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 6, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 7, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 8, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 9, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 10, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 11, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 12, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 13, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 14, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 15, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 16, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 17, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 18, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 19, xlwt.Formula("SUM(T"+str(row_data_begin)+":T"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 20, xlwt.Formula("SUM(U"+str(row_data_begin)+":U"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 21, xlwt.Formula("SUM(V"+str(row_data_begin)+":V"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 22, xlwt.Formula("SUM(W"+str(row_data_begin)+":W"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 23, xlwt.Formula("SUM(X"+str(row_data_begin)+":X"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 24, xlwt.Formula("SUM(Y"+str(row_data_begin)+":Y"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 25, xlwt.Formula("SUM(Z"+str(row_data_begin)+":Z"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 26, xlwt.Formula("SUM(AA"+str(row_data_begin)+":AA"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 27, xlwt.Formula("SUM(AB"+str(row_data_begin)+":AB"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 28, xlwt.Formula("SUM(AC"+str(row_data_begin)+":AC"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 29, xlwt.Formula("SUM(AD"+str(row_data_begin)+":AD"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 30, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 31, xlwt.Formula("SUM(AF"+str(row_data_begin)+":AF"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 32, xlwt.Formula("SUM(AG"+str(row_data_begin)+":AG"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 33, xlwt.Formula("SUM(AH"+str(row_data_begin)+":AH"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 34, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 35, None, self.rt_cell_style_decimal)
# Footer
ws_o.write(row_pos_o + 1, 0, None)
ws_o.write(row_pos_o + 2, 0, _p.report_date + ' ' + str(self.pool.get('res.users').browse(self.cr, self.uid, self.uid).name))
def sells(request, year, month, other):
month = int(month)
year = int(year)
other = int(other)
response = HttpResponse(mimetype='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=mymodel.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("sells")
ch_dep = CreditsHistory.objects.filter(
date__month=month,
date__year=year,
).values('department')
need_dp = DepartmentsNames.objects.filter(
department__in=ch_dep).values_list('pk', flat=True)
columns = []
columns.append((u'Дата ', 4000, 0))
for dn in DepartmentsNames.list():
if dn not in need_dp:
continue
if DepartmentsNames.list()[dn]:
columns.append((DepartmentsNames.list()[dn], 4000, dn))
else:
columns.append((u'Отдел ' + str(dn), 4000, dn))
columns.append((u'Сумма ', 4000, 9999))
row_num = 0
head = month_name_ru(month) + ' ' + str(year)
col_join = len(columns) - 4
ws.row(row_num).height_mismatch = True
ws.row(row_num).height = 15 * 20
if col_join < 1:
col_join = 1
ws.write_merge(row_num, 0, 0, col_join, head, styleh)
ws.write_merge(row_num, 0, col_join + 1, col_join + 3, report_top_prefix,
styleph)
row_num = 1
last_col = len(columns) - 1
for col_num in xrange(len(columns)):
if col_num == 0:
ws.write_merge(row_num, 2, 0, 0, columns[col_num][0], styleth)
elif col_num == last_col:
ws.write_merge(row_num, 2, last_col, last_col, columns[col_num][0],
styleth)
else:
ws.write(row_num, col_num, columns[col_num][0], styleth)
ws.write(row_num + 1, col_num,
u'Отдел ' + str(columns[col_num][2]), styleth)
# set column width
ws.col(col_num).width = columns[col_num][1]
row_num = 2
total_summ = 0
mdays = monthrange(year, month)[1]
for d in xrange(mdays):
row_num += 1
day = d + 1
day_summ = 0
for col_num in xrange(len(columns)):
if col_num == 0:
data = date(year, month, day).strftime("%d.%m.%Y")
st = stylethd
elif columns[col_num][2] == 9999:
st = styleth
sl = col_idx2str(1)
el = col_idx2str(col_num - 1)
formula = 'SUM(' + sl + str(row_num + 1) + ':'\
+ el + str(row_num + 1) + ')'
data = xlwt.Formula(formula)
else:
st = style
department = columns[col_num][2] # cash section
if other:
dep_summ = CreditsHistory.objects.filter(
date__month=month,
date__year=year,
date__day=day,
department=department,
is_return=0,
).aggregate(Sum('amount'))
else:
dep_summ = CreditsHistory.objects.filter(
date__month=month,
date__year=year,
date__day=day,
department=department,
is_return=0,
).exclude(payment_type=2).aggregate(Sum('amount'))
data = dep_summ['amount__sum']
if data:
day_summ += data
ws.write(row_num, col_num, data, st)
row_num += 1
ws.write(row_num, 0, 'Итого:', styleth)
for col_num in xrange(1, len(columns)):
l = col_idx2str(col_num)
formula = 'SUM(' + l + '4:' + l + str(mdays + 3) + ')'
ws.write(row_num, col_num, xlwt.Formula(formula), styleth)
wb.save(response)
return response
tree[node['text']] = node['id']
all_leaf.append(node['text'])
return tree
except Exception as e:
return 'error:' + str(node_id)
if __name__ == '__main__':
tcprefix = input('请输入测试用例标识：')
root_node_id = input('请输入根节点id：')
if input('是否有PHPSESSID，如果有请输入，如果没有，则输入n：') == 'n':
get_session_id()
g_tree = get_data(root_node_id, tcprefix)
f = open('result.json', 'w+')
f.write(json.dumps(g_tree, ensure_ascii=False, indent=2))
f.close()
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')
title = ['用例', '详情地址', '测试情况']
for col in range(len(title)):
sheet.write(0, col, title[col])
for index, test_title in enumerate(all_leaf):
sheet.write(index + 1, 0, test_title)
sheet.write(
index + 1, 1,
xlwt.Formula('HYPERLINK("' +
'http://10.3.196.2:8081/linkto.php?tprojectPrefix=' +
tcprefix.replace('--', '-') + '&item=testcase&id=' +
test_title.split(':')[0] + '";"详情")'))
book.save('test.xls')
def generate_xls_report(self):
""" Crea un xls con los valores obtenidos de get_report_values"""
values = self.get_report_values()
# Preparamos el workbook y la hoja
wbk = xlwt.Workbook()
style = xlwt.easyxf('font: bold on,height 240,color_index 0X36;'
'align: horiz center;'
'borders: left thin, right thin, top thin')
name = 'Iva Ventas' if self.type == 'sales' else 'Iva Compras'
sheet = wbk.add_sheet(name)
# Ancho de las columnas
sheet.col(0).width = 2500
sheet.col(1).width = 6000
sheet.col(2).width = 4000
sheet.col(3).width = 6000
sheet.col(4).width = 1500
sheet.col(5).width = 1500
sheet.col(6).width = 4000
sheet.col(7).width = 4000
row_number = 0
total_cols = 0
# Header
for col in values[0]:
# Le asignamos el ancho a las columnas de importes
if total_cols > 7:
sheet.col(col).width = 3500
sheet.write(row_number, col, values[0][col], style)
total_cols += 1
# Detalles
row_number += 1
for value in values[1:]:
for col in value:
sheet.write(row_number, col, value[col])
row_number += 1
for x in range(8, total_cols):
column_start = xlwt.Utils.rowcol_to_cell(1, x)
column_end = xlwt.Utils.rowcol_to_cell(row_number - 1, x)
sheet.write(
row_number, x,
xlwt.Formula('SUM(' + column_start + ':' + column_end + ')'))
# Exportamos y guardamos
file_data = StringIO.StringIO()
wbk.save(file_data)
out = base64.encodestring(file_data.getvalue())
self.report = out
date_from = fields.Date.from_string(
self.date_from).strftime('%d-%m-%Y')
date_to = fields.Date.from_string(self.date_to).strftime('%d-%m-%Y')
filename = 'Subdiario ' + name + ' ' + date_from + ' a ' + date_to
return {
'type':
'ir.actions.act_url',
'url':
'/web/binary/download_vat_diary?wizard_id=%s&filename=%s' %
(self.id, filename + '.xls'),
'target':
'new',
}
def ptt(request, syear, smonth, sday, eyear, emonth, eday):
syear = int(syear)
smonth = int(smonth)
sday = int(sday)
eyear = int(eyear)
emonth = int(emonth)
eday = int(eday)
s_date = datetime.combine(date(syear, smonth, sday), time.min)
e_date = datetime.combine(date(eyear, emonth, eday), time.max)
response = HttpResponse(mimetype='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=mymodel.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("ptt")
head = u'Персональные тренировки за период c: '\
+ s_date.strftime("%d.%m.%Y")
head += u' по: ' + e_date.strftime("%d.%m.%Y")
ws.write_merge(0, 0, 0, 10, head, styleh)
row_num = 1
columns = []
columns.append((u'Номер карты ', 4000))
columns.append((u'Дата продажи', 6000))
columns.append((u'Вид блока', 6000))
columns.append((u'Стоимость, руб.', 5000))
columns.append((u'Вид оплаты', 4000))
columns.append((u'Впервые / Повторно', 5000))
columns.append((u'ФИО ', 8000))
columns.append((u'Тренер ', 8000))
columns.append((u'Менеджер ', 8000))
columns.append((u'Продавец', 8000))
columns.append((u'Карта выдана ', 4000, 0))
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], styleth)
# set column width
ws.col(col_num).width = columns[col_num][1]
lst = Client_PTT.objects.filter(date__range=(s_date,
e_date)).order_by('date')
ptt_first = (u'Повторно', u'Впервые')
for ptt in lst:
row_num += 1
fullname = ptt.client.initialsC()
trainer = ptt.employee.initials()
manager = ptt.client.manager.initials()
seller = ptt.user.initials()
if not ptt.is_card:
url = reverse('ptt_card', args=(ptt.pk, ))
url = request.build_absolute_uri(url)
link = u'HYPERLINK("' + url + u'"; "Не выдана")'
is_card = xlwt.Formula(link)
st = style_red
else:
is_card = u'Выдана'
st = style
ws.write(row_num, 0, str(ptt.number()), style)
ws.write(row_num, 1, ptt.date.strftime("%d.%m.%Y %H:%M"), styledt)
ws.write(row_num, 2, ptt.goods.name, style)
ws.write(row_num, 3, ptt.goods.priceondate(ptt.date), stylef)
ws.write(row_num, 4, pay_type[ptt.payment_type], style)
ws.write(row_num, 5, ptt_first[ptt.is_first()], style)
ws.write(row_num, 6, fullname, style)
ws.write(row_num, 7, trainer, style)
ws.write(row_num, 8, manager, style)
ws.write(row_num, 9, seller, style)
ws.write(row_num, 10, is_card, st)
wb.save(response)
return response
def action_excel(self, cr, uid, ids, context=None):
if not ids:
return {'type_pv': 'ir.actions.act_window_close'}
if context is None:
context = {}
form = self.read(cr, uid, ids)[0]
date_from = form.get('date_from')
type_stock = form.get('type_stock')
type_pv = form.get('company_id')
# Formato de la Hoja de Excel
wb = pycel.Workbook(encoding='utf-8')
style_cabecera = pycel.easyxf(
'font: colour black, bold True;'
'align: vertical center, horizontal center;')
style_header = pycel.easyxf(
'font: bold True;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea = pycel.easyxf('borders:bottom 1;')
linea_center = pycel.easyxf(
'font: colour black, height 140;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_der_bold = pycel.easyxf(
'font: colour black, bold true, height 200;'
'align: vertical center, horizontal right, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
view_style = pycel.easyxf(
'font: colour green, bold true, height 200;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq = pycel.easyxf(
'font: colour black, height 140;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq_n = pycel.easyxf(
'font: colour black, height 140;'
'align: vertical center, horizontal left, wrap on;')
linea_der = pycel.easyxf('font: colour black, height 140;'
'align: vertical center, horizontal right;'
'borders: left 1, right 1, top 1, bottom 1;')
title = type_pv[1]
title1 = 'INVENTARIO REAL'
ws = wb.add_sheet(title)
ws.show_grid = False
ws.header_str = u"&LFecha de Impresion: &D Hora: &T&RPagina &P de &N"
ws.footer_str = u""
compania = self.pool.get('res.users').browse(cr, uid, uid).company_id
calle1 = compania.partner_id.street
calle2 = compania.partner_id.street2
ruc = compania.partner_id.part_number
if calle1 and calle2:
direccion = str(calle1.encode('UTF-8')) + " " + str(
calle2.encode('UTF-8'))
elif calle1 and not calle2:
direccion = str(calle1.encode('UTF-8'))
elif calle2 and not calle1:
direccion = str(calle2.encode('UTF-8'))
else:
direccion = ''
if ruc:
ruc_part = ruc
else:
ruc_part = ''
x0 = 6
ws.write_merge(1, 1, 1, x0, compania.name, style_cabecera)
ws.write_merge(2, 2, 1, x0, 'Direccion: ' + str(direccion),
style_cabecera)
ws.write_merge(3, 3, 1, x0, 'Ruc: ' + str(ruc_part), style_cabecera)
ws.write_merge(
5, 5, 1, x0, title1 + " AL " + time.strftime(
'%d/%m/%Y', time.strptime(date_from, '%Y-%m-%d')).upper(),
style_cabecera)
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
ws.portrait = 1
align = pycel.Alignment()
align.horz = pycel.Alignment.HORZ_RIGHT
align.vert = pycel.Alignment.VERT_CENTER
font1 = pycel.Font()
font1.colour_index = 0x0
font1.height = 140
linea_izq_n.width = 150
#Formato de Numero
style = pycel.XFStyle()
style.num_format_str = '#,##0.00'
style.alignment = align
style.font = font1
#Formato de Numero Saldo
font = pycel.Font()
font.bold = True
font.colour_index = 0x27
style1 = pycel.XFStyle()
style1.num_format_str = '#,##0.00'
style1.alignment = align
style1.font = font
font2 = pycel.Font()
font2.bold = True
font2.colour_index = 0x0
style2 = pycel.XFStyle()
style2.num_format_str = '#,##0.00'
style2.alignment = align
style2.font = font2
style3 = pycel.XFStyle()
style3.num_format_str = '0'
style3.alignment = align
style3.font = font1
xi = 8 # Cabecera de Cliente
xe = 9
ws.write(xi, 1, 'ITEM', style_header)
ws.write(xi, 2, 'CODIGO', style_header)
ws.write(xi, 3, 'PRODUCTO', style_header)
ws.write(xi, 4, 'U.M.', style_header)
ws.write(xi, 5, 'STOCK', style_header)
ws.write(xi, 6, 'COSTO', style_header)
ws.write(xi, 7, 'TOTAL', style_header)
totales = self.get_lines_report_wage(cr, uid, form)
xi = xe + 1
columns = [5, 6, 7]
total_formula = [
'SUBTOTAL(9,F9:F{0})', 'SUBTOTAL(9,G9:G{0})', 'SUBTOTAL(9,H9:H{0})'
]
seq = 1
for linea in totales:
# Detail
ws.write(xi, 0, linea.get('ident', ''), linea_izq)
ws.write(xi, 1, seq, linea_izq)
ws.write(xi, 2, linea.get('cod_name', ''), linea_izq)
ws.write(xi, 3, linea.get('prod_name', ''), linea_izq)
ws.write(xi, 4, linea.get('uom', ''), linea_izq)
ws.write(xi, 5, linea.get('qty_sum', ''), linea_der)
ws.write(xi, 6, linea.get('prom', ''), linea_der)
ws.write(xi, 7, linea.get('sum_value', ''), linea_der)
xi += 1
seq += 1
ws.write(xi, 4, 'TOTAL', view_style)
ws.write(xi, columns[0], xlwt.Formula(total_formula[0].format(xi)),
linea_der_bold)
ws.write(xi, columns[1], xlwt.Formula(total_formula[1].format(xi)),
linea_der_bold)
ws.write(xi, columns[2], xlwt.Formula(total_formula[2].format(xi)),
linea_der_bold)
ws.col(0).width = 1000
ws.col(1).width = 2250
ws.col(2).width = 3000
ws.col(3).width = 14900
ws.col(4).width = 2500
ws.col(5).width = 2500
ws.col(6).width = 2500
ws.row(8).height = 750
buf = cStringIO.StringIO()
wb.save(buf)
out = base64.encodestring(buf.getvalue())
buf.close()
name = "%s.xls" % ("Reporte_inventario_real")
archivo = '/opt/temp/' + name
res_model = 'stock.inventary.real'
id = ids and type(ids) == type([]) and ids[0] or ids
self.write(cr, uid, ids, {
'data': out,
'name': 'Reporte_inventario.xls'
})
self.load_doc(cr, uid, out, id, name, archivo, res_model)
return {'type': 'ir.actions.client', 'tag': 'reload'}
'''
使用xlwt
'''
__author__ = "Haipeng Dong"
import xlwt
import xlrd
fileName = r'./tmp002.xls'
oldFile = r'./tmp001.xlsx'
bk = xlrd.open_workbook(oldFile)
try:
sh = bk.sheet_by_name('SQL Results')
except NameError:
print('no sheet name in %s' % fileName)
nrows = sh.nrows
ncols = sh.ncols
# 写一个新的Execl中
writeBook = xlwt.Workbook(encoding='utf-8')
sheet = writeBook.add_sheet('备份数据', cell_overwrite_ok=True)
style = xlwt.XFStyle()
for m in range(1, nrows):
for n in range(ncols):
sheet.write(m, n, sh.cell(m, n).value)
sheet.write(nrows, 0, xlwt.Formula('SUM(a1:a' + str(nrows) + ')'))
writeBook.save(fileName)
def sum_(worksheet,f_name,fixups,needdata,row_index,dl_obj, **kargs):
begin_row = needdata['instance_dict']['table']['begin_row'] +2
end_row = needdata['instance_dict']['table']['end_row']
if end_row > begin_row:
worksheet.write(row_index, 4, xlwt.Formula('SUM(%s%s:%s%s)'%('P',begin_row + 1,'P',end_row+1)),center_style)
return 1# 1 row
def excel_action(self):
wb = pycel.Workbook(encoding='utf-8')
style_cabecera = pycel.easyxf(
'font: colour black, bold True;'
'align: vertical center, horizontal center;')
style_header = pycel.easyxf(
'font: bold True;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
view_style = pycel.easyxf(
'font: colour green, bold true, height 200;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq = pycel.easyxf(
'font: colour black, height 150;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq_n = pycel.easyxf(
'font: colour black, height 140;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_izq_neg = pycel.easyxf(
'font: colour black, bold true, height 200;'
'align: vertical center, horizontal left, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_der = pycel.easyxf('font: colour black, height 150;'
'align: vertical center, horizontal right;'
'borders: left 1, right 1, top 1, bottom 1;')
linea_der_bold = pycel.easyxf(
'font: colour black, bold true, height 200;'
'align: vertical center, horizontal right, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
view_style_out = pycel.easyxf(
'font: colour red, bold true, height 200;'
'align: vertical center, horizontal center, wrap on;'
'borders: left 1, right 1, top 1, bottom 1;')
ws = wb.add_sheet('Reporte Importaciones')
ws.show_grid = False
ws.header_str = u"&LFecha de Impresion: &D Hora: &T&RPagina &P de &N"
ws.footer_str = u""
company = self.env['res.users'].browse(self._uid).company_id
ws.write_merge(1, 1, 1, 5, company.name, style_cabecera)
ws.write_merge(
2, 2, 1, 5, 'Fecha desde: ' + self.date_from + ' - ' +
'Fecha hasta: ' + self.date_to + ' ', style_cabecera)
ws.write_merge(3, 3, 1, 5, 'REPORTE IMPORTACIONES', style_cabecera)
ws.fit_num_pages = 1
ws.fit_height_to_pages = 0
ws.fit_width_to_pages = 1
ws.portrait = 1
align = pycel.Alignment()
align.horz = pycel.Alignment.HORZ_RIGHT
align.vert = pycel.Alignment.VERT_CENTER
font1 = pycel.Font()
font1.colour_index = 0x0
font1.height = 140
linea_izq_n.width = 150
# Formato de Numero
style = pycel.XFStyle()
style.num_format_str = '#,##0.00'
style.alignment = align
style.font = font1
# Formato de Numero Saldo
font = pycel.Font()
font.bold = True
font.colour_index = 0x27
style1 = pycel.XFStyle()
style1.num_format_str = '#,##0.00'
style1.alignment = align
style1.font = font
font2 = pycel.Font()
font2.bold = True
font2.colour_index = 0x0
style2 = pycel.XFStyle()
style2.num_format_str = '#,##0.00'
style2.alignment = align
style2.font = font2
style3 = pycel.XFStyle()
style3.num_format_str = '0'
style3.alignment = align
style3.font = font1
xi = 9 # Cabecera de Cliente
self.set_header(ws, xi, style_header)
xi += 1
data_file_name = "reporte_importaciones.xls"
seq = 0
orders = self.get_data()
columns = [8, 9, 10, 11]
total_formula = [
'SUBTOTAL(9,I10:I{0})', 'SUBTOTAL(9,J10:J{0})',
'SUBTOTAL(9,K10:K{0})', 'SUBTOTAL(9,L10:L{0})'
]
for importation in orders:
moves = ()
lines = ()
if importation.picking_ids:
for pick in importation.picking_ids:
for moves in pick.move_lines:
if self.type == 'done':
if moves.state == 'done':
self.set_body(importation, lines, moves, ws,
xi, linea_der, linea_izq, seq,
linea_izq_n, linea_izq_neg,
view_style, linea_der_bold,
view_style_out)
xi += 1
if self.type == 'assigned':
if moves.state not in ('done', 'cancel'):
self.set_body(importation, lines, moves, ws,
xi, linea_der, linea_izq, seq,
linea_izq_n, linea_izq_neg,
view_style, linea_der_bold,
view_style_out)
xi += 1
if not self.type:
self.set_body(importation, lines, moves, ws, xi,
linea_der, linea_izq, seq,
linea_izq_n, linea_izq_neg,
view_style, linea_der_bold,
view_style_out)
xi += 1
elif not moves and not importation.picking_ids:
for lines in importation.order_lines:
self.set_body(importation, lines, moves, ws, xi, linea_der,
linea_izq, seq, linea_izq_n, linea_izq_neg,
view_style, linea_der_bold, view_style_out)
xi += 1
ws.write(xi, 7, 'TOTAL', view_style)
ws.write(xi, columns[0], xlwt.Formula(total_formula[0].format(xi)),
linea_der_bold)
ws.write(xi, columns[1], xlwt.Formula(total_formula[1].format(xi)),
linea_der_bold)
ws.write(xi, columns[2], xlwt.Formula(total_formula[2].format(xi)),
linea_der_bold)
ws.write(xi, columns[3], xlwt.Formula(total_formula[3].format(xi)),
linea_der_bold)
ws.col(0).width = 1000
ws.col(1).width = 2500
ws.col(2).width = 11000
ws.col(3).width = 11000
ws.col(4).width = 3000
ws.col(5).width = 14000
ws.col(6).width = 1500
ws.col(7).width = 1500
ws.col(8).width = 3000
ws.col(9).width = 3000
ws.col(10).width = 3000
ws.col(11).width = 3000
ws.col(12).width = 3500
ws.col(13).width = 1500
ws.col(14).width = 1500
ws.col(15).width = 2500
ws.col(16).width = 2500
ws.col(17).width = 2500
ws.col(18).width = 2500
ws.col(19).width = 2500
ws.row(9).height = 750
try:
buf = cStringIO.StringIO()
wb.save(buf)
out = base64.encodestring(buf.getvalue())
buf.close()
res_model = 'purchase.importation.order.report'
self.load_doc(out, data_file_name, res_model)
return self.write({
'data': out,
'txt_filename': data_file_name,
'name': 'reporte_importacion.xls'
})
except ValueError:
raise Warning('Error a la hora de salvar el archivo')
def Write_NETInfo(sheetwrite, n, CMXinfo):
for i in range(0, Total_Col):
sheetwrite.write(n, i, "", writestyle0)
#NET part
sheetwrite.write(n, 0, CMXinfo.Message_Name, writestyle0)
sheetwrite.write(n, 1, CMXinfo.Signal_Name, writestyle0)
i1 = Col_NET_n
sheetwrite.write(n, i1, CMXinfo.Signal_Factor, writestyle0)
i1 += 1
sheetwrite.write(n, i1, CMXinfo.Signal_Offset, writestyle0)
i1 += 1
sheetwrite.write(n, i1, CMXinfo.Signal_PhyMin, writestyle0)
i1 += 1
sheetwrite.write(n, i1, CMXinfo.Signal_PhyMax, writestyle0)
i1 += 1
sheetwrite.write(n, i1, CMXinfo.Signal_ValueTab, writestyle0)
i1 += 1
sheetwrite.write(n, i1, 'InvalidVal: ' + CMXinfo.Signal_InvalidVal,
writestyle0)
#ASW part
i2 = Col_ASW_n
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,2,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,3,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,4,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,5,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,6,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,7,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,8,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,9,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,10,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,11,FALSE)"),
writestyle0)
i2 += 1
sheetwrite.write(
n, i2, xlwt.Formula("VLOOKUP(E1:E65536,Sheet2!A1:Z65536,12,FALSE)"),
writestyle0)
def _cost_control_sheet_report(self, _p, _xs, data, objects, wb):
cr = self.cr
uid = self.uid
context = self.context
wl_ccs = _p.wanted_list_cost_control_sheet
template = self.cost_control_sheet_template
project_obj = self.pool['project.project']
purchase_order_line_obj = self.pool.get('purchase.order.line')
expense_line_obj = self.pool.get('hr.expense.line')
project_id = project_obj.browse(
cr, uid, data['project_id'], context=context)
sheet_name = "Cost Control Sheet"
ws = wb.add_sheet(sheet_name)
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
company_id = self.pool['res.users'].browse(
cr, uid, uid, context=context).company_id
titles = [
company_id.name or '',
company_id.street or '',
'TEL. %s FAX %s WEB %s E-mail %s ' % (
company_id.phone or '-',
company_id.fax or '-',
company_id.website or '-',
company_id.email or '-'
),
'Cost Control Sheet',
]
for title in titles:
row_pos = self._report_title(ws, _p, row_pos, _xs, title, merge=11)
project_info = [
'BU No. ' + project_id.operating_unit_id.name,
'Job: ' + project_id.project_number + ' ' + project_id.name,
'Event Date: ' +
('/'.join(project_id.date_start.split('-')) or '') + ' - ' +
('/'.join(project_id.date.split('-')) or ''),
'Place: ' + (project_id.project_place or ''),
]
for info in project_info:
row_pos = self._report_header(ws, _p, row_pos, _xs, info, merge=11)
row_pos = self._report_column_header(ws, _p, row_pos, _xs)
ws.set_horz_split_pos(row_pos)
hr_row_pos = row_pos
quote_ids = project_id.quote_related_ids
entries = []
for quote_id in quote_ids:
line_and_parent = self._ordering_order_line(
ws, _p, row_pos, quote_id)
entries += line_and_parent
for entry in entries:
data_obj = entry[1]
if entry[0] == 'quote':
quote_description = 'Quotation %s' % (data_obj.name)
c_specs = map(
lambda x: self.render(
x, template, 'quote'),
wl_ccs)
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=self.av_cell_style_decimal)
elif entry[0] == 'custom_group':
custom_group_name = data_obj
c_specs = map(
lambda x: self.render(
x, template, 'custom_group'),
wl_ccs)
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=self.rt_cell_style_decimal)
elif entry[0] == 'section':
section_name = data_obj.name
c_specs = map(
lambda x: self.render(
x, template, 'section'),
wl_ccs)
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=self.rt_cell_style_decimal)
elif entry[0] == 'line':
order_line_description = data_obj.name
price_in_contract = data_obj.price_unit * \
data_obj.product_uom_qty
estimate_cost = data_obj.purchase_price * \
data_obj.product_uom_qty
percent_margin = data_obj.sale_order_line_margin
c_specs = map(
lambda x: self.render(
x, template, 'order_line'),
wl_ccs)
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(
ws, row_pos, row_data, row_style=self.an_cell_style)
# purchase
purchase_order_line_ids = purchase_order_line_obj.search(
cr, uid, [
('sale_order_line_ref_id', '=', data_obj.id),
('state', 'in', ('confirmed', 'approved', 'done')),
])
if purchase_order_line_ids:
purchase_row_pos = row_pos
for row_i, purchase_order_line_id in enumerate(
purchase_order_line_ids):
order_line_id = purchase_order_line_obj.browse(
self.cr, self.uid,
purchase_order_line_id, context=self.context)
ws.write(
purchase_row_pos + row_i - 1, 4,
order_line_id.order_id.name,
style=self.an_cell_style)
ws.write(
purchase_row_pos + row_i - 1, 5,
order_line_id.price_subtotal,
style=self.an_cell_style)
ws.write(
purchase_row_pos + row_i - 1, 6,
order_line_id.order_id.partner_id.name,
style=self.an_cell_style)
row_pos += row_i
expense_line_ids = expense_line_obj.search(cr, uid, [
('analytic_account', '=', project_id.analytic_account_id.id),
])
if expense_line_ids:
for row_i, expense_line_id in enumerate(expense_line_ids):
expense_line_id = expense_line_obj.browse(
self.cr, self.uid,
expense_line_id, context=self.context)
ws.write(
hr_row_pos + row_i, 7,
expense_line_id.expense_id.number,
style=self.an_cell_style)
ws.write(
hr_row_pos + row_i, 8,
expense_line_id.ref or '',
style=self.an_cell_style)
ws.write(
hr_row_pos + row_i, 9,
expense_line_id.expense_id.employee_request_id.name or '',
style=self.an_cell_style)
ws.write(
hr_row_pos + row_i, 10,
expense_line_id.amount_line_untaxed,
style=self.an_cell_style)
# totals
sum_price = 'SUM(B%s:B%s)' % (str(hr_row_pos+1), str(row_pos))
sum_estimate = 'SUM(C%s:C%s)' % (str(hr_row_pos+1), str(row_pos))
sum_margin = '(B%s - C%s) * 100.0 / B%s' % (
str(row_pos+1), str(row_pos+1), str(row_pos+1))
sum_po_price = 'SUM(F%s:F%s)' % (str(hr_row_pos+1), str(row_pos))
sum_hr_price = 'SUM(K%s:K%s)' % (str(hr_row_pos+1), str(row_pos))
ws.write(row_pos, 0, 'Totals', style=self.av_cell_style_decimal)
ws.write(row_pos, 1, xlwt.Formula(sum_price), style=self.av_cell_style_decimal)
ws.write(row_pos, 2, xlwt.Formula(sum_estimate), style=self.av_cell_style_decimal)
ws.write(row_pos, 3, xlwt.Formula(sum_margin), style=self.av_cell_style_decimal)
ws.write(row_pos, 4, '', style=self.av_cell_style_decimal)
ws.write(row_pos, 5, xlwt.Formula(sum_po_price), style=self.av_cell_style_decimal)
ws.write(row_pos, 6, '', style=self.av_cell_style_decimal)
ws.write(row_pos, 7, '', style=self.av_cell_style_decimal)
ws.write(row_pos, 8, '', style=self.av_cell_style_decimal)
ws.write(row_pos, 9, '', style=self.av_cell_style_decimal)
ws.write(row_pos, 10, xlwt.Formula(sum_hr_price), style=self.av_cell_style_decimal)
# 如果旧的文件有内容，则利用旧文件的内容
if nt in old:
worksheet.write(k, 4, old[nt][0])
worksheet.write(k, 5, old[nt][1])
worksheet.write(k, 6, old[nt][2])
worksheet.write(k, 7, old[nt][3])
else:
worksheet.write(k, 4, m)
worksheet.write(k, 5, m * 2)
worksheet.write(k, 6, m)
worksheet.write(k, 7, m - 0.5)
# 录入公式计算模块的经费
sk = str(k + 1)
worksheet.write(k, 8,
xlwt.Formula('SUM(E' + sk + ':H' + sk + ')'))
worksheet.write(k, 9, 2)
worksheet.write(k, 10, 0)
worksheet.write(k, 11, xlwt.Formula('I' + sk + '*J' + sk))
# }}}
# 编辑总结公式
# {{{
st = ''
# 遍历所有行获取当前标题编号
for i in range(0, k):
si = str(i + 2)
ni = ns[i]
ss = ''
if re.match(r'^\d+$', ni):
st = st + 'L' + si + ','
def sendExcel(bbox, scodes, siteRecords):
# change dir to the mappers exporter / temp director
os.chdir("/var/www/mapper/exporter/temp")
# create a temporary directory name using a uuid
tempDirName = str(uuid.uuid4())
# create the directory
os.mkdir(tempDirName)
# change dir to the temp dir
os.chdir(tempDirName)
# build a metadata string from the scodes
metaData = getMetaData(bbox, scodes, tempDirName, len(siteRecords))
# create the metadata file
fp = open("Metadata.txt", "w")
# write the metadata
fp.write(metaData)
#fp.write("\n\n")
# close the file
fp.close()
# create an excel workbook
fpBook = xlwt.Workbook()
# create a worksheet in the workbook
nwisSheet = fpBook.add_sheet("NWIS Mapper Export Sheet")
# write a header for the data, start with row 2
nwisSheet.write(1, 0, "SiteNumber")
nwisSheet.write(1, 1, "SiteName")
nwisSheet.write(1, 2, "SiteCategory")
nwisSheet.write(1, 3, "SiteAgency")
nwisSheet.write(1, 4, "SiteLongitude")
nwisSheet.write(1, 5, "SiteLatitude")
nwisSheet.write(1, 6, "SiteNWISURL")
# add each site data to the spreadsheet
row = 2
for site in siteRecords:
siteNum = site[0]
siteName = site[1]
siteCat = site[2]
siteAgc = site[3]
siteLng = site[4]
siteLat = site[5]
siteURL = site[6]
nwisSheet.write(row, 0, siteNum)
nwisSheet.write(row, 1, siteName)
nwisSheet.write(row, 2, siteCat)
nwisSheet.write(row, 3, siteAgc)
nwisSheet.write(row, 4, siteLng)
nwisSheet.write(row, 5, siteLat)
nwisSheet.write(row, 6, xlwt.Formula('HYPERLINK("' + siteURL + '";"Access Data")'))
row = row + 1
# create the excel file
fpBook.save("NWISMapperExport.xls")
# copy the readme file
shutil.copyfile("/var/www/mapper/exporter/Templates/Readme.txt", "./Readme.txt")
shutil.copyfile("/var/www/mapper/exporter/Templates/Readme.html", "./Readme.html")
# now create the zip file
zipFile = zipfile.ZipFile(tempDirName + ".zip", mode="w")
# add the files
zipFile.write("Readme.txt")
zipFile.write("Readme.html")
zipFile.write("NWISMapperExport.xls")
zipFile.write("Metadata.txt")
zipFile.close()
return tempDirName
def attendence(request):
if request.user.is_superuser:
import calendar
if request.method == "POST":
month = request.POST['Month']
if month == '':
return HttpResponse('<h2> Please Select the Month</h2>')
a = {}
i = 1
Name_detail = CustomUser.objects.filter(~Q(Empid=1)).values_list(
'Empid', 'EmpName')
for name in Name_detail:
name = name[1]
a[name.lower()] = i
i += 1
currmonth = datetime.date.today().strftime('%Y-%m')
year = currmonth.split('-')[0]
num_days = calendar.monthrange(int(year), int(month))[1]
days = [
datetime.date(int(year), int(month), day)
for day in range(1, num_days + 1)
]
rows = Reports.objects.filter(
~Q(Empid=1), Report_date__month=int(month)).values_list(
'Empid', 'Name', 'Report_date',
'Attendence').order_by('Empid')
if len(rows) == 0:
return HttpResponse("<h2>No reports for Selected Month</h2>")
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = 'attachment; filename="Attendence.xls"'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Attendence', cell_overwrite_ok=True)
# Sheet header, first row
row_num = 0
font_style = xlwt.XFStyle()
font_style.font.bold = True
aday = ['Empid', 'Name']
for da_t in days:
aday.append(str(da_t))
aday.append('P')
aday.append('EL')
aday.append('HEL')
aday.append('WFH')
columns = aday
for col_num in range(len(columns)):
ws.write(row_num, col_num, columns[col_num], font_style)
# Sheet body, remaining rows
font_style = xlwt.XFStyle()
for row in Name_detail:
row_num += 1
for col_num in range(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
atten = {
'Present': 'P',
'Leave': 'EL',
'Half day leave': 'HEL',
'WO': 'WO',
'OT': 'OT',
'Permission': 'P',
'GH': 'GH',
'WFH': 'WFH',
'HWFH': 'HWFH',
'OTH': 'OTH'
}
name_mat = ''
date_mat = ''
atte_mat = ''
Mday = [str(da_t) for da_t in days]
arr_date = []
for M_week in Mday:
day_ = datetime.datetime.strptime(str(M_week),
'%Y-%m-%d').strftime('%a')
if str(day_) == 'Sun' or str(day_) == 'Sat':
arr_date.append(M_week)
for row in rows:
Name_r = row[1]
date_r = row[2]
atte_r = atten[str(row[3])]
if Name_r != name_mat:
for c in ['P', 'EL', 'HEL', 'WFH']:
ws.write(
row_num, aday.index(str(c)),
xlwt.Formula('COUNTIF(C' + str(row_num + 1) +
':AG' + str(row_num + 1) + ',"' +
str(c) + '")'))
for wend in arr_date:
row_num = int(a[str(Name_r.lower())])
col_num = aday.index(str(wend))
ws.write(row_num, col_num, 'WO', font_style)
if Name_r == name_mat and date_mat == date_r:
if atte_r == 'P' and atte_mat == 'HEL':
atte_r = 'HEL'
row_num = int(a[str(Name_r.lower())])
col_num = aday.index(str(date_r))
ws.write(row_num, col_num, atte_r, font_style)
name_mat = Name_r
date_mat = date_r
atte_mat = atte_r
for c in ['P', 'EL', 'HEL', 'WFH']:
ws.write(
row_num, aday.index(str(c)),
xlwt.Formula('COUNTIF(C' + str(row_num + 1) + ':AG' +
str(row_num + 1) + ',"' + str(c) + '")'))
wb.save(response)
return response
else:
return render(request, 'review/Attendence.html')
else:
return HttpResponse("<h3>Your are not Superuser</h3>")
import xlwt
import xlrd
wb = xlwt.Workbook()
ws = wb.add_sheet('mysheet')
ws.write(0, 0, 1.01) #把表格想象成二维表，前2各参数是行列
ws.write(0, 1, 'haha')
ws.write(1, 0, 'A')
ws.write(1, 1, 'B')
ws.write(1, 2, 'SUM')
ws.write(2, 0, 123)
ws.write(2, 1, 456)
ws.write(2, 2, 110)
ws.write(2, 3, xlwt.Formula("A3+B3"))
wb.save('example.xls')
wb = xlrd.open_workbook('example.xls')
sn = wb.sheet_names() #获得工作所有表名
print('sheet names :' + str(sn))
#wb.sheet_by_name('name') 通过工作表名进入工作表
sh = wb.sheet_by_index(0) #通过index进入工作表
sheets = wb.sheets() #获取所有工作表
for sheet in sheets:
for row in range(sheet.nrows):
for col in range(sheet.ncols):
print(sheet.cell(row, col).value)
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
wanted_list_details = _p.wanted_list_details
self.col_specs_template_overview.update(_p.template_update_overview)
self.col_specs_template_details.update(_p.template_update_details)
_ = _p._
username = self.pool.get('res.users').browse(self.cr, self.uid,
self.uid).name
for r in _p.reports:
title_short = r['title_short'].replace('/', '-')
ws_o = wb.add_sheet(title_short)
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos_o = 0
row_pos_d = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# COMPANY NAME
cell_style_company = xlwt.easyxf(_xs['left'])
c_specs_o = [
('company_name', 1, 0, 'text', str(_p.company.name)),
]
row_data = self.xls_row_template(c_specs_o, ['company_name'])
row_pos_o += self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style_company)
#TITLE
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' '.join(
[_('LAPORAN PENERIMAAN DAN PENGELUARAN HARIAN')])
c_specs_o = [
('title', 1, 20, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['title'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
row_pos_o += 1
#option
option_format = ' '.join([_('Options : ') + str(r['option'])])
c_specs_o = [
('option', 1, 20, 'text', option_format),
]
row_data = self.xls_row_template(c_specs_o, ['option'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style_company)
#TANGGAL
tgl_format = ' '.join([
_('Tanggal : ') + str(r['start_date']) + " s/d " +
str(r['end_date'])
])
c_specs_o = [
('Tanggal', 1, 20, 'text', tgl_format),
]
row_data = self.xls_row_template(c_specs_o, ['Tanggal'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style_company)
row_pos_o += 1
# Report Column Headers
c_specs_o = map(
lambda x: self.render(x,
self.col_specs_template_overview,
'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
no = 0
for p in r['move_lines']:
c_specs_o = map(
lambda x: self.render(x, self.col_specs_template_overview,
'lines'), wanted_list_overview)
for x in c_specs_o:
if x[0] == 'no':
no += 1
x[4] = no
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.pd_cell_style)
row_pos_d += 1
row_data_end = row_pos_o
ws_o.write(row_pos_o, 0, None, self.ph_cell_style)
ws_o.write(row_pos_o, 1, None, self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.ph_cell_style)
ws_o.write(row_pos_o, 3, None, self.ph_cell_style)
ws_o.write(row_pos_o, 4, None, self.ph_cell_style)
ws_o.set_vert_split_pos(3)
ws_o.write(row_pos_o, 5, None, self.ph_cell_style)
ws_o.write(row_pos_o, 6, "Total", self.ph_cell_style)
ws_o.write(
row_pos_o, 7,
xlwt.Formula("SUM(H" + str(row_data_begin) + ":H" +
str(row_data_end) + ")"),
self.pd_cell_style_decimal_fill)
ws_o.write(
row_pos_o, 8,
xlwt.Formula("SUM(I" + str(row_data_begin) + ":I" +
str(row_data_end) + ")"),
self.pd_cell_style_decimal_fill)
ws_o.write(
row_pos_o, 9,
xlwt.Formula("SUM(J" + str(row_data_begin) + ":J" +
str(row_data_end) + ")"),
self.pd_cell_style_decimal_fill)
ws_o.write(
row_pos_o, 10,
xlwt.Formula("SUM(K" + str(row_data_begin) + ":K" +
str(row_data_end) + ")"),
self.pd_cell_style_decimal_fill)
ws_o.write(row_pos_o, 11, None, self.ph_cell_style)
ws_o.write(row_pos_o + 1, 0, _p.report_date + " " + username)
def _dumpDataToExcel(modelDir):
""" Dump data into .xls file in model workspace """
# TODO: Think about a universal function
wb = xlwt.Workbook()
sh1 = wb.add_sheet("All Input Data")
inJson = json.load(open(pJoin(modelDir, "allInputData.json")))
size = len(inJson.keys())
for i in range(size):
sh1.write(i, 0, inJson.keys()[i])
for i in range(size):
sh1.write(i, 1, inJson.values()[i])
outJson = json.load(open(pJoin(modelDir, "allOutputData.json")))
sh1.write(0, 5, "Lat")
sh1.write(0, 6, "Lon")
sh1.write(0, 7, "Elev")
sh1.write(1, 5, outJson["lat"])
sh1.write(1, 6, outJson["lon"])
sh1.write(1, 7, outJson["elev"])
sh2 = wb.add_sheet("Hourly Data")
sh2.write(0, 0, "TimeStamp")
sh2.write(0, 1, "Power(kW-AC)")
sh2.write(0, 2, "Power due to Inverter clipping(kW-AC)")
sh2.write(0, 3, "Plane of Array Irradiance (W/m^2)")
sh2.write(0, 4, "Global Horizontal Radiation(W/m^2)")
sh2.write(0, 5, "Wind Speed (m/s)")
sh2.write(0, 6, "Ambient Temperature (F)")
sh2.write(0, 7, "Cell Temperature (F)")
for i in range(len(outJson["timeStamps"])):
sh2.write(i + 1, 0, outJson["timeStamps"][i])
sh2.write(i + 1, 1, outJson["powerOutputAc"][i])
sh2.write(i + 1, 2, outJson["InvClipped"][i])
sh2.write(i + 1, 3,
outJson["climate"]["Plane of Array Irradiance (W/m^2)"][i])
sh2.write(i + 1, 4,
outJson["climate"]["Global Horizontal Radiation (W/m^2)"][i])
sh2.write(i + 1, 5, outJson["climate"]["Wind Speed (m/s)"][i])
sh2.write(i + 1, 6, outJson["climate"]["Ambient Temperature (F)"][i])
sh2.write(i + 1, 7, outJson["climate"]["Cell Temperature (F)"][i])
sh2.panes_frozen = True
sh2.vert_split_pos = 1
sh3 = wb.add_sheet("Monthly Data")
sh3.write(0, 1, "Monthly Generation")
for i in range(24):
sh3.write(0, 3 + i, i + 1)
for i in range(12):
sh3.write(i + 1, 0, outJson["monthlyGeneration"][i][0])
sh3.write(i + 1, 1, outJson["monthlyGeneration"][i][1])
for i in range(len(outJson["seasonalPerformance"])):
sh3.write(outJson["seasonalPerformance"][i][1] + 1,
outJson["seasonalPerformance"][i][0] + 3,
outJson["seasonalPerformance"][i][2])
sh3.panes_frozen = True
sh3.vert_split_pos = 3
sh3.horz_split_pos = 1
sh4 = wb.add_sheet("Annual Data")
sh4.write(0, 0, "Year No.")
for i in range(len(outJson["netCashFlow"])):
sh4.write(i + 1, 0, i)
sh4.write(0, 1, "Net Cash Flow ($)")
sh4.write(0, 2, "Life O&M Costs ($)")
sh4.write(0, 3, "Life Purchase Costs ($)")
sh4.write(0, 4, "Cumulative Cash Flow ($)")
for i in range(len(outJson["netCashFlow"])):
sh4.write(i + 1, 1, outJson["netCashFlow"][i])
sh4.write(i + 1, 2, outJson["lifeOmCosts"][i])
sh4.write(i + 1, 3, outJson["lifePurchaseCosts"][i])
sh4.write(i + 1, 4, outJson["cumCashFlow"][i])
sh4.write(0, 10, "ROI")
sh4.write(1, 10, outJson["ROI"])
sh4.write(0, 11, "NPV")
sh4.write(1, 11, outJson["NPV"])
sh4.write(0, 12, "IRR")
sh4.write(1, 12, outJson["IRR"])
# sh4.write(2, 11, xlwt.Formula("NPV(('All Input Data'!B15/100,'Annual Data'!B2:B31))"))
sh4.write(2, 12, xlwt.Formula("IRR(B2:B31)"))
filename = "omf.solarFinancial.xls"
wb.save(pJoin(modelDir, filename))
outJson["excel"] = filename
with open(pJoin(modelDir, "allOutputData.json"), "w") as outFile:
json.dump(outJson, outFile, indent=4)
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
self.col_specs_template_overview.update({})
_ = _p._
for r in _p.reports:
title_short = r['title_short'].replace('/', '-')
ws_o = wb.add_sheet(title_short)
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos_o = 0
row_pos_d = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Company Name
cell_style = xlwt.easyxf(_xs['left'])
c_specs_o = [
('report_name', 1, 0, 'text', _p.company.name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
c_specs_o = [
('report_name', 1, 0, 'text', 'LAPORAN PENJUALAN'),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
## Start Date & End Date ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join([
_('Tanggal'),
_('-' if data['start_date'] ==
False else str(data['start_date'])),
_('s/d'),
_('-' if data['end_date'] == False else str(data['end_date'])),
_p.report_info
])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=cell_style)
row_pos_o += 1
# Report Column Headers
c_specs_o = map(
lambda x: self.render(x,
self.col_specs_template_overview,
'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
# Columns and Rows
no = 0
for p in r['dso_ids']:
if not 'disc_quo_incl_tax_no' in p:
p['disc_quo_incl_tax_no'] = None
c_specs_o = map(
lambda x: self.render(x, self.col_specs_template_overview,
'lines'), wanted_list_overview)
for x in c_specs_o:
if x[0] == 'no':
no += 1
x[4] = no
row_data = self.xls_row_template(c_specs_o,
[x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(ws_o,
row_pos_o,
row_data,
row_style=self.pd_cell_style)
row_data_end = row_pos_o
# Totals
ws_o.write(row_pos_o, 0, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.rt_cell_style_decimal)
ws_o.set_vert_split_pos(3)
ws_o.write(row_pos_o, 3, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 4, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 5, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 6, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 7, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 8, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 9, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 10, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 11, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 12, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 13, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 14, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 15, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 16, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 17, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 18, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 19, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 20, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 21, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 22, None, self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 23,
xlwt.Formula("SUM(X" + str(row_data_begin) + ":X" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 24, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 25, None, self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 26,
xlwt.Formula("SUM(AA" + str(row_data_begin) + ":AA" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 27,
xlwt.Formula("SUM(AB" + str(row_data_begin) + ":AB" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 28,
xlwt.Formula("SUM(AC" + str(row_data_begin) + ":AC" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 29,
xlwt.Formula("SUM(AD" + str(row_data_begin) + ":AD" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 30,
xlwt.Formula("SUM(AE" + str(row_data_begin) + ":AE" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 31,
xlwt.Formula("SUM(AF" + str(row_data_begin) + ":AF" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 32,
xlwt.Formula("SUM(AG" + str(row_data_begin) + ":AG" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 33,
xlwt.Formula("SUM(AH" + str(row_data_begin) + ":AH" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 34,
xlwt.Formula("SUM(AI" + str(row_data_begin) + ":AI" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 35,
xlwt.Formula("SUM(AJ" + str(row_data_begin) + ":AJ" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 36,
xlwt.Formula("SUM(AK" + str(row_data_begin) + ":AK" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 37,
xlwt.Formula("SUM(AL" + str(row_data_begin) + ":AL" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 38,
xlwt.Formula("SUM(AM" + str(row_data_begin) + ":AM" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 39,
xlwt.Formula("SUM(AN" + str(row_data_begin) + ":AN" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 40,
xlwt.Formula("SUM(AO" + str(row_data_begin) + ":AO" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 41,
xlwt.Formula("SUM(AP" + str(row_data_begin) + ":AP" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 42,
xlwt.Formula("SUM(AQ" + str(row_data_begin) + ":AQ" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 43,
xlwt.Formula("SUM(AR" + str(row_data_begin) + ":AR" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 44,
xlwt.Formula("SUM(AS" + str(row_data_begin) + ":AS" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 45,
xlwt.Formula("SUM(AT" + str(row_data_begin) + ":AT" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 46,
xlwt.Formula("SUM(AU" + str(row_data_begin) + ":AU" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 47,
xlwt.Formula("SUM(AV" + str(row_data_begin) + ":AV" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 48,
xlwt.Formula("SUM(AW" + str(row_data_begin) + ":AW" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 49,
xlwt.Formula("SUM(AX" + str(row_data_begin) + ":AX" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 50,
xlwt.Formula("SUM(AY" + str(row_data_begin) + ":AY" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 51,
xlwt.Formula("SUM(AZ" + str(row_data_begin) + ":AZ" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 52, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 53, None, self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 54,
xlwt.Formula("SUM(BC" + str(row_data_begin) + ":BC" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 55,
xlwt.Formula("SUM(BD" + str(row_data_begin) + ":BD" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 56,
xlwt.Formula("SUM(BE" + str(row_data_begin) + ":BE" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(
row_pos_o, 57,
xlwt.Formula("SUM(BF" + str(row_data_begin) + ":BF" +
str(row_data_end) + ")"),
self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 58, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 59, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 60, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 61, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 62, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 63, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 64, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 65, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 66, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 67, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 68, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 69, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 70, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 71, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 72, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 73, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 74, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 75, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 76, None, self.rt_cell_style_decimal)
# Footer
ws_o.write(row_pos_o + 1, 0, None)
ws_o.write(
row_pos_o + 2, 0, _p.report_date + ' ' + str(
self.pool.get('res.users').browse(self.cr, self.uid,
self.uid).name))
def write_analysis_wooksheek(self):
"""
写入测试分析excel
:return:
"""
row = 7
temp_list = []
for item in self.__temp_list:
if len(item) >= 1:
if item[0] not in temp_list:
temp_list.append(item[0])
self.analysis_wooksheek.write(row, 1, item[0],
self.style_center)
self.analysis_wooksheek.write(
row, 2, "=COUNTIF(测试用例!A:A,B" + str(row + 1) + ")",
self.style_center)
self.analysis_wooksheek.write(
row, 3, "=COUNTIFS(测试用例!A:A,B" + str(row + 1) +
''',测试用例!K:K,"Pass")''', self.style_center)
self.analysis_wooksheek.write(
row, 4, "=COUNTIFS(测试用例!A:A,B" + str(row + 1) +
''',测试用例!K:K,"Fail")''', self.style_center)
self.analysis_wooksheek.write(
row, 5, "=COUNTIFS(测试用例!A:A,B" + str(row + 1) +
''',测试用例!K:K,"Block")''', self.style_center)
self.analysis_wooksheek.write(
row, 6, "=COUNTIFS(测试用例!A:A,B" + str(row + 1) +
''',测试用例!K:K,"NA")''', self.style_center)
self.analysis_wooksheek.write(
row, 7, "=COUNTIFS(测试用例!A:A,B" + str(row + 1) +
''',测试用例!K:K,"Not Run")''', self.style_center)
self.analysis_wooksheek.write(
row, 8,
xlwt.Formula("SUM(D" + str(row + 1) + ":F" +
str(row + 1) + ")/(C" + str(row + 1) +
"-G" + str(row + 1) + ")"),
self.style_num)
self.analysis_wooksheek.write(
row, 9,
xlwt.Formula("D" + str(row + 1) + "/(C" +
str(row + 1) + "-G" + str(row + 1) + ")"),
self.style_num)
row += 1
else:
lines = ['', 0, 0, 0, 0, 0, 0, '0.00%', '0.00%']
index = 1
for head_item in lines:
self.analysis_wooksheek.write(row, index, head_item,
self.style)
index += 1
row += 1
self.analysis_wooksheek.write(row, 1, '总计', self.style_center)
self.analysis_wooksheek.write(
row, 2, xlwt.Formula("SUM(C8:C" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 3, xlwt.Formula("SUM(D8:D" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 4, xlwt.Formula("SUM(E8:E" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 5, xlwt.Formula("SUM(F8:F" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 6, xlwt.Formula("SUM(G8:G" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 7, xlwt.Formula("SUM(H8:H" + str(row) + ")"),
self.style_center)
self.analysis_wooksheek.write(
row, 8,
xlwt.Formula("SUM(D" + str(row + 1) + ":F" + str(row + 1) +
")/(C" + str(row + 1) + "-G" + str(row + 1) + ")"),
self.style_num)
self.analysis_wooksheek.write(
row, 9,
xlwt.Formula("D" + str(row + 1) + "/(C" + str(row + 1) + "-G" +
str(row + 1) + ")"), self.style_num)
row += 2
self.analysis_wooksheek.write(row, 1, '说明:')
row += 1
self.analysis_wooksheek.write(
row, 1, 'Pass-验证通过 Fail-验证未通过 Block-阻塞 NA-本期不涉及 Not Run-尚未执行')
row += 1
self.analysis_wooksheek.write(row, 1,
'Run Rate=(Pass+Fail+Block)/(Total-NA)')
self.analysis_wooksheek.write(row + 1, 1, 'Pass Rate=Pass/(Total-NA)')
# HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED
# HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.horz = xlwt.Alignment.HORZ_CENTER
# 设置垂直方向
# VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER
# 定义格式对象
style = xlwt.XFStyle()
style.alignment = alignment
# 合并单元格write_merge(开始行, 结束行, 开始列, 结束列, 内容, 格式)
ws.write_merge(0, 0, 0, 5, 'Python网络爬虫', style)
# 写入数据wb.write(行,列,内容)
for i in range(2, 7):
for k in range(5):
ws.write(i, k, i + k)
# Excel公式xlwt.Formula
ws.write(i, 5,
xlwt.Formula('SUM(A' + str(i + 1) + ':E' + str(i + 1) + ')'))
# 插入图片，insert_bitmap(img, x, y, x1, y1, scale_x=0.8, scale_y=1)
# 图片格式必须为bmp
# x表示行数，y表示列数
# x1表示相对原来位置向下偏移的像素
# y1表示相对原来位置向右偏移的像素
# scale_x，scale_y缩放比例
ws.insert_bitmap('E:\\test.bmp', 9, 1, 2, 2, scale_x=0.3, scale_y=0.3)
# 保存文件
wb.save('file.xls')
def generate_xls_report(self, _p, _xs, data, objects, wb):
##Penempatan untuk template rows
title_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; ')
title_style_center = xlwt.easyxf('font: height 220, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz center; ')
normal_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz left;',num_format_str='#,##0.00;-#,##0.00')
normal_style_center = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center;')
normal_style_float = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right;',num_format_str='#,##0.00;-#,##0.00')
normal_style_float_round = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right;',num_format_str='#,##0')
normal_style_float_bold = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz right;',num_format_str='#,##0.00;-#,##0.00')
normal_bold_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; ')
normal_bold_style_a = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; ')
normal_bold_style_b = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on;pattern: pattern solid, fore_color gray25; align: wrap on, vert centre, horiz left; ')
th_top_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick')
th_both_style_left = xlwt.easyxf('font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left;')
th_both_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick, bottom thick')
th_bottom_style = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:bottom thick')
th_both_style_dashed = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick, bottom dashed',num_format_str='#,##0.00;-#,##0.00')
th_both_style_dashed_bottom = xlwt.easyxf('font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right; border:bottom dashed',num_format_str='#,##0.00;-#,##0.00')
subtotal_title_style = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz left; borders: top thin, bottom thin;')
subtotal_style = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right; borders: bottom thin;',num_format_str='#,##0;-#,##0')
subtotal_style2 = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right; borders: top thin, bottom thin;',num_format_str='#,##0.00;-#,##0.00')
total_title_style = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz left;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;')
total_style = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;',num_format_str='#,##0.0000;(#,##0.0000)')
total_style2 = xlwt.easyxf('font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;',num_format_str='#,##0.00;(#,##0.00)')
subtittle_top_and_bottom_style = xlwt.easyxf('font: height 240, name Times New Roman, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;')
ws = wb.add_sheet("Laporan Penjualan Offline")
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
ws.preview_magn = 100
ws.normal_magn = 100
ws.print_scaling=100
ws.page_preview = False
ws.set_fit_width_to_pages(1)
ws.write_merge(0,0,0,4,"LAPORAN PENJUALAN OFFLINE",title_style_center)
ws.write_merge(3,3,0,1,"PERIODE",normal_bold_style_a)
ws.write_merge(3,3,2,4,": "+data['start_date']+" - "+data['end_date'],normal_bold_style_a)
headers = ["No","Nama Customer","Penjualan Wholesale","Penjualan Retail","Total Penjualan Wholesale & retail"]
col = 0
for head in headers:
ws.write(5,col,head,normal_bold_style_b)
col+=1
no=1
col = 0
row=6
max_len = [int(len(x)*1.5) for x in headers]
print "maxxxxxxxxxx",max_len
invoices = _p.get_invoices(objects)
for rec in invoices:
ws.write(row,0,no,normal_style_float_round)
ws.write(row,1,rec.name,normal_style)
ws.write(row,2,invoices[rec]['wholesale'],normal_style)
ws.write(row,3,invoices[rec]['retail'],normal_style)
ws.write(row,4,invoices[rec]['wholesale']+invoices[rec]['retail'],normal_style)
max_len[0]=len(str(no))+3 > max_len[0] and len(str(no))+3 or max_len[0]
max_len[1]=len(str(rec.name)) > max_len[1] and len(str(rec.name)) or max_len[1]
max_len[2]=len(str(invoices[rec]['wholesale'])) > max_len[2] and len(str(invoices[rec]['wholesale'])) or max_len[2]
max_len[3]=len(str(invoices[rec]['retail'])) > max_len[3] and len(str(invoices[rec]['retail'])) or max_len[3]
max_len[4]=len(str(invoices[rec]['wholesale']+invoices[rec]['retail'])) > max_len[4] and len(str(invoices[rec]['wholesale']+invoices[rec]['retail'])) or max_len[4]
row+=1
no+=1
for x in range(0,5):
ws.col(x).width=max_len[x]*256
ws.write_merge(row,row,col,col+1,"TOTAL",title_style)
ws.write(row,2,xlwt.Formula("SUM($C$7:$C$"+str(row)+")"),subtotal_style2)
ws.write(row,3,xlwt.Formula("SUM($D$7:$D$"+str(row)+")"),subtotal_style2)
ws.write(row,4,xlwt.Formula("SUM($E$7:$E$"+str(row)+")"),subtotal_style2)
def generate_xls_report(self, _p, _xs, data, objects, wb):
##Penempatan untuk template rows
grouping = {}
for group in objects:
if group.partner_id in grouping.keys():
dummy = grouping.get(group.partner_id, [])
dummy.append(group)
grouping.update({group.partner_id: dummy})
else:
grouping.update({group.partner_id: [group]})
title_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; '
)
title_style_center = xlwt.easyxf(
'font: height 220, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz center; '
)
normal_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz left;',
num_format_str='#,##0.00;-#,##0.00')
normal_style_center = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center;'
)
normal_style_float = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right;',
num_format_str='#,##0.00;-#,##0.00')
normal_style_float_round = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right;',
num_format_str='#,##0')
normal_style_float_bold = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz right;',
num_format_str='#,##0.00;-#,##0.00')
normal_bold_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; '
)
normal_bold_style_a = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left; '
)
normal_bold_style_b = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on;pattern: pattern solid, fore_color gray25; align: wrap on, vert centre, horiz left; '
)
th_top_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick'
)
th_both_style_left = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz left;'
)
th_both_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick, bottom thick'
)
th_bottom_style = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:bottom thick'
)
th_both_style_dashed = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz center; border:top thick, bottom dashed',
num_format_str='#,##0.00;-#,##0.00')
th_both_style_dashed_bottom = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black; align: wrap on, vert centre, horiz right; border:bottom dashed',
num_format_str='#,##0.00;-#,##0.00')
subtotal_title_style = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz left; borders: top thin, bottom thin;'
)
subtotal_style = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right; borders: bottom thin;',
num_format_str='#,##0;-#,##0')
subtotal_style2 = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right; borders: top thin, bottom thin;',
num_format_str='#,##0.00;-#,##0.00')
total_title_style = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz left;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;'
)
total_style = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;',
num_format_str='#,##0.0000;(#,##0.0000)')
total_style2 = xlwt.easyxf(
'font: name Times New Roman, colour_index black, bold on; align: wrap on, vert centre, horiz right;pattern: pattern solid, fore_color gray25; borders: top thin, bottom thin;',
num_format_str='#,##0.00;(#,##0.00)')
subtittle_top_and_bottom_style = xlwt.easyxf(
'font: height 240, name Times New Roman, colour_index black, bold off, italic on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;'
)
border_all = xlwt.easyxf(
'border:top thick, bottom thick, left thick, right thick')
bold_border_all = xlwt.easyxf(
'font: height 180, name Calibri, colour_index black, bold on; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_color gray25; border:top thin, bottom thin, left thin, right thin'
)
if not grouping:
raise Warning(
_('Record\'s not found between Start Date: %s and End Date: %s. '
) % (data['start_date'], data['end_date']))
for group in grouping.keys():
ws = wb.add_sheet(group.name)
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
ws.preview_magn = 100
ws.normal_magn = 100
ws.print_scaling = 100
ws.page_preview = False
ws.set_fit_width_to_pages(1)
ws.write_merge(0, 0, 0, 4, "AP PER VENDOR", title_style_center)
ws.write_merge(3, 3, 0, 1, "PERIODE", normal_bold_style_a)
ws.write_merge(
3, 3, 2, 4,
": " + data['start_date'] + " - " + data['end_date'],
normal_bold_style_a)
col = 0
row = 6
max_len = [0, 0, 0, 0, 0, 0, 0, 0]
headers1 = ["No", "No Invoice", "Tanggal Invoice", "Nilai Invoice"]
headers2 = ["Payment"]
headers3 = ["Tanggal Payment", "Nilai Payment"]
headers4 = ["Outstanding", "Due Date"]
row += 1
col = 0
for head1 in headers1:
ws.write_merge(row, row + 1, col, col, head1, bold_border_all)
col += 1
col = 4
for head2 in headers2:
ws.write_merge(row, row, col, col + 1, head2, bold_border_all)
col += 1
col = 4
for head3 in headers3:
ws.write_merge(row + 1, row + 1, col, col, head3,
bold_border_all)
col += 1
col = 6
for head4 in headers4:
ws.write_merge(row, row + 1, col, col, head4, bold_border_all)
col += 1
row += 2
no = 1
for o in grouping.get(group, []):
ws.write(row, 0, no, normal_style_float_round)
ws.write(row, 1, o.number, normal_style)
ws.write(row, 2, o.date_invoice, normal_style)
ws.write(row, 3, o.amount_total, normal_style_float)
ws.write(row, 6, o.residual, normal_style_float)
ws.write(row, 7, o.date_due, normal_style)
for pay in o.payment_move_line_ids:
ws.write(row, 4, pay.date, normal_style)
ws.write(row, 5, pay.debit, normal_style_float)
max_len[4] = len(str(pay.date)) > max_len[4] and len(
str(pay.date)) or max_len[4]
max_len[5] = len(str(pay.debit)) + 5 > max_len[5] and len(
str(pay.debit)) + 5 or max_len[5]
row += 1
max_len[0] = len(str(no)) + 3 > max_len[0] and len(
str(no)) + 3 or max_len[0]
max_len[1] = len(str(o.number)) > max_len[1] and len(
str(o.number)) or max_len[1]
max_len[2] = len(str(o.date_invoice)) > max_len[2] and len(
str(o.date_invoice)) or max_len[2]
max_len[3] = len(str(o.amount_total)) + 5 > max_len[3] and len(
str(o.amount_total)) + 5 or max_len[3]
max_len[6] = len(str(o.residual)) + 5 > max_len[6] and len(
str(o.residual)) + 5 or max_len[6]
max_len[7] = len(str(o.date_due)) > max_len[7] and len(
str(o.date_due)) or max_len[7]
row += 1
no += 1
for x in range(0, 8):
ws.col(x).width = max_len[x] * 256
ws.write(row, 1, "TOTAL", title_style)
ws.write(row, 3, xlwt.Formula("SUM($D$7:$D$" + str(row) + ")"),
subtotal_style2)
ws.write(row, 5, xlwt.Formula("SUM($F$7:$F$" + str(row) + ")"),
subtotal_style2)
ws.write(row, 6, xlwt.Formula("SUM($G$7:$G$" + str(row) + ")"),
subtotal_style2)
def makecertexcel(head, data, pdfurl=None):
#create excel book
wb = xlwt.Workbook()
ws = wb.add_sheet('main')
ws.header_str = ''
ws.footer_str = ''
font_style='Arial'
# #default start settings
down = 0
row = 3
#############################
#fnt = xlwt.Font()
#fnt.name = font_style
#fnt.bold = True
#borders = xlwt.Borders()
#borders.left = xlwt.Borders.THIN
#borders.right = xlwt.Borders.THIN
#borders.top = xlwt.Borders.THIN
#borders.bottom = xlwt.Borders.THIN
#al = xlwt.Alignment()
#al.horz = xlwt.Alignment.HORZ_CENTER
#al.vert = xlwt.Alignment.VERT_CENTER
#style = xlwt.XFStyle()
#style.font = fnt
#style.borders = borders
#style.alignment = al
style = xlwt.easyxf('font: name Arial, height 180, bold on, color-index black; alignment: horiz center;')
#ws.write_merge(marginbottom,mergebottom,marginleft, mergeright, u'тест', style)
ws.write_merge(down, 0, 0, row, head, style) #объединить ячейки
##################################
down = down + 1
#fnt = xlwt.Font()
#fnt.name = font_style
#fnt.bold = True
#borders = xlwt.Borders()
#borders.left = xlwt.Borders.THIN
#borders.right = xlwt.Borders.THIN
#borders.top = xlwt.Borders.THIN
#borders.bottom = xlwt.Borders.THIN
#al = xlwt.Alignment()
#al.horz = xlwt.Alignment.HORZ_JUSTIFIED
#al.vert = xlwt.Alignment.VERT_CENTER
#style = xlwt.XFStyle()
#style.font = fnt
#style.borders = borders
#style.alignment = al
style = xlwt.easyxf('font: name Arial, height 180, color-index black; alignment: horiz justified, vertical center, indent 1;')
ws.row(down).height = 1400 #высота ячейки
#ws.write_merge(marginbottom,mergebottom,marginleft, mergeright, u'тест', style)
ws.write_merge(down, down, 0, row, u'Настоящий товарно-сопроводительный документ подготовлен в соответствии с постановлением правительства РФ от 19.01.98 г. № 56 "Об утверждении правил продажи отдельных видов товаров". При продаже товаров, подлежащих обязательной сертификации, продавец доводит до сведения покупателя через товарно-сопроводительные документы, оформленные на основании подлинника сертификата с указанием его номера, срока действия и органа, выдавшего сертификат. Сведения о сертификации должны быть заверены подписью и печатью изготовителя (поставщика, продавца) с указанием телефона."', style) #объединить ячейки
###################################
down = down + 2
#fnt = xlwt.Font()
#fnt.name = font_style
#fnt.bold = True
#borders = xlwt.Borders()
#borders.left = xlwt.Borders.THIN
#borders.right = xlwt.Borders.THIN
#borders.top = xlwt.Borders.THIN
#borders.bottom = xlwt.Borders.THIN
#al = xlwt.Alignment()
#al.horz = xlwt.Alignment.HORZ_CENTER
#al.wrap = xlwt.Alignment.WRAP_AT_RIGHT
#al.vert = xlwt.Alignment.VERT_CENTER
#style = xlwt.XFStyle()
#style.font = fnt
#style.borders = borders
#style.alignment = al
style = xlwt.easyxf('font: name Arial, height 160, bold on, color-index black; alignment: horiz center, vertical center; borders: top thin, bottom thin, right thin, left thin;')
ws.write(down, 0, u'Номенклатура', style)
ws.write(down, 1, u'Номер', style)
ws.write(down, 2, u'Дата сертификата', style)
ws.write(down, 3, u'Орган, выдавший сертификат', style)
for i in range(row+1): #пробежимся по колонкам и устновим ширину столбца
ws.col(i).width = 6000 #ширина столбца
ws.row(i).height_mismatch = True
#ws.row(i).height = 1000
######################################
down = down + 1
#fnt = xlwt.Font()
#fnt.name = font_style
#al = xlwt.Alignment()
#al.wrap = xlwt.Alignment.WRAP_AT_RIGHT
#al.horz = xlwt.Alignment.HORZ_CENTER
#al.vert = xlwt.Alignment.VERT_CENTER
#borders = xlwt.Borders()
#borders.left = xlwt.Borders.THIN
#borders.right = xlwt.Borders.THIN
#borders.top = xlwt.Borders.THIN
#borders.bottom = xlwt.Borders.THIN
#style = xlwt.XFStyle()
#style.font = fnt
#style.borders = borders
#style.alignment = al
style0 = xlwt.easyxf('font: name Arial, height 160, color-index black; alignment: horiz justified, vertical center, indent 1; borders: top thin, bottom thin, right thin, left thin;')
style1 = xlwt.easyxf('font: name Arial, height 160, color-index black; alignment: horiz center, vertical center; borders: top thin, bottom thin, right thin, left thin;')
for i in data:
#ws.row(i).height_mismatch = True
ws.row(down).height = 500
ws.write(down, 0, i.name, style0)
if i.goodscert:
ws.write(down, 1, i.goodscert.name, style1)
ws.write(down, 2, '%s - %s' % (i.goodscert.datestart.strftime('%d.%m.%Y'), i.goodscert.dateend.strftime('%d.%m.%Y')), style1)
ws.write(down, 3, i.goodscert.org, style1)
else:
ws.write(down, 1, u'Нет', style1)
ws.write(down, 2, u'Нет', style1)
ws.write(down, 3, u'Нет', style1)
down=down+1
############################
down = down + 1
#fnt = xlwt.Font()
#fnt.name = font_style
#al = xlwt.Alignment()
#al.vert = xlwt.Alignment.VERT_CENTER
#style = xlwt.XFStyle()
#style.font = fnt
#style.alignment = al
#ws.write_merge(marginbottom,mergebottom,marginleft, mergeright, u'тест', style)
style = xlwt.easyxf('font: name Arial, height 180, color-index black; alignment: horiz justified, vertical center, indent 1;')
ws.write_merge(down, down, 0, row, u'В случае если окончание срока действия не указано, сертификат является бессрочным.', style) #объединить ячейки
if pdfurl: #если есть PDF дописываем
#
down = down + 2
#
fnt = xlwt.Font()
fnt.name = font_style
fnt.bold = True
al = xlwt.Alignment()
al.wrap = xlwt.Alignment.WRAP_AT_RIGHT
al.horz = xlwt.Alignment.HORZ_CENTER
al.vert = xlwt.Alignment.VERT_CENTER
style = xlwt.XFStyle()
style.font = fnt
style.alignment = al
style.font.colour_index = xlwt.Style.colour_map['blue']
#ws.write_merge(marginbottom,mergebottom,marginleft, mergeright, u'тест', style)
ws.write_merge(down, down, 0, row, xlwt.Formula(u'HYPERLINK("%s";"Скачать сертификаты в PDF")' % pdfurl), style) #объединить ячейки
#
# default_book_style = wb.default_style
# default_book_style.font.height = 20 * 36 # 36pt
filename = id_generator()
xlsout = 'media/CACHE/%s.xls' % (filename)
pdfout = 'media/CACHE/%s.pdf' % (filename)
xlsurl = '%s%s' % (urlprefix, xlsout)
xlspath = '%s%s' % (root, xlsout)
wb.save(xlspath)
#convert xls to pdf via libreoffice
os.system('/usr/bin/libreoffice libreoffice --headless --convert-to pdf --outdir /var/www/crm/media/CACHE %s' % (xlspath));
pdfpath = '%s%s' % (root, pdfout)
return (xlsurl, xlspath, pdfpath)
def _add_statistics_sheet(self, req, sheetName, wb, fields, fieldsIndex,
constraints):
neededFields = ['milestone', 'status', 'type']
neededFieldsInfo = [
f['name'] for f in fields if f['name'] in neededFields
]
if len(neededFieldsInfo) != 3:
return wb
milestoneLbl = [
f['label'] for f in fields if f['name'] == 'milestone'
][0]
types = [m.name for m in model.Type.select(self.env)]
milestones = [m.name for m in model.Milestone.select(self.env, True)]
if 'type' in constraints.keys():
types = [t for t in types if t in constraints['type']]
if 'milestone' in constraints.keys():
milestones = [
m for m in milestones if m in constraints['milestone']
]
headerStyle = xlwt.easyxf(
'font: bold on; pattern: pattern solid, fore-colour grey25; borders: top thin, bottom thin, left thin, right thin'
)
ws = wb.add_sheet('Statistics')
r = 0
c = 0
ws.write(r, c, milestoneLbl, headerStyle)
for m in milestones:
c += 1
ws.write(r, c, m, headerStyle)
c = 0
for t in types:
r += 1
ws.write(r, c, t, headerStyle)
r += 1
ws.write(r, c, 'closed', headerStyle)
template = ("SUMPRODUCT(0+('%s'!%s:%s=%%s);0+('%s'!%s:%s=%%s))" % (
sheetName, rowcol_to_cell(1, fieldsIndex['milestone'], True, True),
rowcol_to_cell(65364, fieldsIndex['milestone'], True, True),
sheetName, rowcol_to_cell(1, fieldsIndex['type'], True, True),
rowcol_to_cell(65364, fieldsIndex['type'], True, True)))
closedTemplate = ( ( "IF(SUM(%%s:%%s)=0;"+'""'+";SUMPRODUCT(0+('%s'!%s:%s=%%s);0+('%s'!%s:%s="+'"closed"))*100/SUM(%%s:%%s))' ) % \
( sheetName, rowcol_to_cell(1, fieldsIndex['milestone'], True, True), rowcol_to_cell(65364, fieldsIndex['milestone'], True, True), sheetName, rowcol_to_cell(1, fieldsIndex['status'], True, True), rowcol_to_cell( 65364, fieldsIndex['status'], True, True ) ) )
borders = xlwt.easyxf(
'borders: top thin, bottom thin, left thin, right thin')
percent = xlwt.easyxf(
'borders: top thin, bottom thin, left thin, right thin')
percent.num_format_str = '0.00 \\%'
for m in range(1, len(milestones) + 1):
formula = (closedTemplate %
(rowcol_to_cell(1, m), rowcol_to_cell(len(types), m),
rowcol_to_cell(0, m), rowcol_to_cell(
1, m), rowcol_to_cell(len(types), m)))
ws.write(len(types) + 1, m, xlwt.Formula(formula), percent)
for t in range(1, len(types) + 1):
formula = (
template %
(rowcol_to_cell(0, m), rowcol_to_cell(t, 0, True, True)))
ws.write(t, m, xlwt.Formula(formula), borders)
return wb
