############################################################################## # # A simple program to write some dates and times to an Excel file # using the XlsxWriter Python module. # # Copyright 2013, John McNamara, [email protected] # from datetime import datetime from xlsxwriter.workbook import Workbook # Create a workbook and add a worksheet. workbook = Workbook('datetimes.xlsx') worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': True}) # Expand the first columns so that the date is visible. worksheet.set_column('A:B', 30) # Write the column headers. worksheet.write('A1', 'Formatted date', bold) worksheet.write('B1', 'Format', bold) # Create a datetime object to use in the examples. date_time = datetime.strptime('2013-01-23 12:30:05.123', '%Y-%m-%d %H:%M:%S.%f') # Examples date and time formats. In the output file compare how changing # the format codes change the appearance of the date. date_formats = ( 'dd/mm/yy',
def generatexlsheet(request):
"""
to generate Course List of Registered Students
@param:
request - contains metadata about the requested page
@variables:
f_key -
idd - Dictionary value of post data year
course_id - Course object according to idd
obj - Registration object of the year in idd
ans - Formatted Array to be converted to xlsx
k -temporary array to add data to formatted array/variable
output - io Bytes object to write to xlsx file
book - workbook of xlsx file
title - formatting variable of title the workbook
subtitle - formatting variable of subtitle the workbook
normaltext - formatting variable for normal text
sheet - xlsx sheet to be rendered
titletext - formatting variable of title text
dep - temporary variables
z - temporary variables for final output
b - temporary variables for final output
c - temporary variables for final output
st - temporary variables for final output
"""
idd = str(request.POST['year'])
f_key = Course.objects.get(course_name=str(idd))
course_id = str(f_key.course_id)
obj = Register.objects.all().filter(course_id=f_key)
ans = []
for i in obj:
k = []
k.append(i.student_id.id.id)
k.append(i.student_id.id.user.first_name)
k.append(i.student_id.id.user.last_name)
k.append(i.student_id.id.department)
ans.append(k)
ans.sort()
import io
output = io.BytesIO()
from xlsxwriter.workbook import Workbook
book = Workbook(output, {'in_memory': True})
title = book.add_format({
'bold': True,
'font_size': 22,
'align': 'center',
'valign': 'vcenter'
})
subtitle = book.add_format({
'bold': True,
'font_size': 15,
'align': 'center',
'valign': 'vcenter'
})
normaltext = book.add_format({
'bold': False,
'font_size': 15,
'align': 'center',
'valign': 'vcenter'
})
sheet = book.add_worksheet()
title_text = ((str(course_id) + " : " + str(str(idd))))
#width = len(title_text)
sheet.set_default_row(25)
sheet.merge_range('A2:E2', title_text, title)
sheet.write_string('A3', "Sl. No", subtitle)
sheet.write_string('B3', "Roll No", subtitle)
sheet.write_string('C3', "Name", subtitle)
sheet.write_string('D3', "Discipline", subtitle)
sheet.write_string('E3', 'Signature', subtitle)
sheet.set_column('A:A', 20)
sheet.set_column('B:B', 20)
sheet.set_column('C:C', 60)
sheet.set_column('D:D', 15)
sheet.set_column('E:E', 30)
k = 4
num = 1
for i in ans:
sheet.write_number('A' + str(k), num, normaltext)
num += 1
z, b, c = str(i[0]), i[1], i[2]
name = str(b) + " " + str(c)
temp = str(i[3]).split()
dep = str(temp[len(temp) - 1])
sheet.write_string('B' + str(k), z, normaltext)
sheet.write_string('C' + str(k), name, normaltext)
sheet.write_string('D' + str(k), dep, normaltext)
k += 1
book.close()
output.seek(0)
response = HttpResponse(output.read(),
content_type='application/vnd.ms-excel')
st = 'attachment; filename = ' + course_id + '.xlsx'
response['Content-Disposition'] = st
return response
def get_factor_excel_file(request, id):
# create io file
output = io.BytesIO()
# get factor
this_factor = get_object_or_404(Factor, ID=id)
# create xlsx
workbook = Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet(this_factor.FactorNumber)
# set row
row = 0
# set factor filed
worksheet.write(row, 0, 'شماره سریال')
worksheet.write(row, 1, 'تاریخ خرید')
worksheet.write(row, 2, 'کل هزینه')
worksheet.write(row, 3, 'هزینه پست')
worksheet.write(row, 4, 'میزان تخفیف')
worksheet.write(row, 5, 'نوع تخفیف')
worksheet.write(row, 6, 'وضعیت صورت حساب')
worksheet.write(row, 7, 'نوع پرداخت')
worksheet.write(row, 8, 'توضیحات')
# set factor data
row += 1
worksheet.write(row, 0, this_factor.FactorNumber)
worksheet.write(
row, 1,
str(
jdatetime.date.fromgregorian(
day=this_factor.OrderDate.date().day,
month=this_factor.OrderDate.date().month,
year=this_factor.OrderDate.date().year)))
worksheet.write(row, 2, this_factor.TotalPrice)
worksheet.write(row, 3, this_factor.PostPrice)
worksheet.write(row, 4, this_factor.DiscountRate)
worksheet.write(row, 5, this_factor.get_coupon_status())
worksheet.write(row, 6, this_factor.get_factor_status())
worksheet.write(row, 7, this_factor.get_factor_payment_type())
worksheet.write(row, 8, this_factor.Description)
# set user filed
row += 2
worksheet.write(row, 0, 'نام خریدار')
worksheet.write(row, 1, 'موبایل')
worksheet.write(row, 2, 'کد پستی')
worksheet.write(row, 3, 'پیش شماره شهر')
worksheet.write(row, 4, 'شماره ثابت')
worksheet.write(row, 5, 'استان')
worksheet.write(row, 6, 'شهرستان')
worksheet.write(row, 7, 'شهر')
worksheet.write(row, 8, 'آدرس')
# set user data
row += 1
worksheet.write(
row, 0,
this_factor.FK_User.first_name + ' ' + this_factor.FK_User.last_name)
worksheet.write(row, 1, this_factor.MobileNumber)
worksheet.write(row, 2, this_factor.ZipCode)
worksheet.write(row, 3, this_factor.CityPerCode)
worksheet.write(row, 4, this_factor.PhoneNumber)
worksheet.write(row, 5, this_factor.State)
worksheet.write(row, 6, this_factor.BigCity)
worksheet.write(row, 7, this_factor.City)
worksheet.write(row, 8, this_factor.Address)
# set product filed
row += 2
worksheet.write(row, 0, 'نام محصول')
worksheet.write(row, 1, 'قیمت')
worksheet.write(row, 2, 'تعداد')
worksheet.write(row, 3, 'نام حجره')
worksheet.write(row, 4, 'مدیریت حجره')
# set product data
row += 1
for item in this_factor.FK_FactorPost.all():
worksheet.write(row, 0, item.FK_Product.Title)
worksheet.write(row, 1, item.FK_Product.Price)
worksheet.write(row, 2, item.ProductCount)
worksheet.write(row, 3, item.FK_Product.FK_Shop.Title)
worksheet.write(
row, 4, item.FK_Product.FK_Shop.FK_ShopManager.first_name + ' ' +
item.FK_Product.FK_Shop.FK_ShopManager.last_name)
row += 1
workbook.close()
output.seek(0)
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename = " + str(
this_factor.FactorNumber) + ".xlsx"
output.close()
return response
def export(request, schema_id):
"""
Generate a XLSX file for download
"""
# Query for schema
schema = get_object_or_404(Schema, random_id=schema_id)
single_tab = request.GET.get('singleTab')
try:
# Generate output string
output = StringIO.StringIO()
# Create workbook
book = Workbook(output, {'in_memory': True})
# Set up bold format
bold = book.add_format({'bold': True})
# List of unique names, as 31 characters is the limit for an object
# and the worksheets names must be unique
unique_names = []
unique_count = 1
# This puts the objects on different worksheets
if not single_tab:
# create a sheet for each object
for obj in schema.sorted_objects_api():
# strip api name
api_name = obj.api_name[:29]
# If the name exists
if api_name in unique_names:
# Add count integer to name
api_name_unique = api_name + str(unique_count)
unique_count += 1
else:
api_name_unique = api_name
# add name to list
unique_names.append(api_name)
# Create sheet
sheet = book.add_worksheet(api_name_unique)
# Write column headers
sheet.write(0, 0, 'Field Label', bold)
sheet.write(0, 1, 'API Name', bold)
sheet.write(0, 2, 'Type', bold)
sheet.write(0, 3, 'Help Text', bold)
sheet.write(0, 4, 'Formula', bold)
# If the usage needs to be included, add the columns
if schema.include_field_usage:
sheet.write(0, 5, 'Field Usage', bold)
# Iterate over fields in object
for index, field in enumerate(obj.sorted_fields()):
# Set start row
row = index + 1
# Write fields to row
sheet.write(row, 0, field.label)
sheet.write(row, 1, field.api_name)
sheet.write(row, 2, field.data_type)
sheet.write(row, 3, field.help_text)
sheet.write(row, 4, field.formula)
if schema.include_field_usage:
sheet.write(row, 5, field.field_usage_display_text)
# This puts all fields on the one worksheet
else:
# Create sheet
sheet = book.add_worksheet('Schema')
# Write column headers
sheet.write(0, 0, 'Object', bold)
sheet.write(0, 1, 'Field Label', bold)
sheet.write(0, 2, 'API Name', bold)
sheet.write(0, 3, 'Type', bold)
sheet.write(0, 4, 'Help Text', bold)
sheet.write(0, 5, 'Formula', bold)
# If the usage needs to be included, add the columns
if schema.include_field_usage:
sheet.write(0, 6, 'Field Usage', bold)
# Set start row
row = 1
# create a sheet for each object
for obj in schema.sorted_objects_api():
# Iterate over fields in object
for index, field in enumerate(obj.sorted_fields()):
# Write fields to row
sheet.write(row, 0, obj.api_name)
sheet.write(row, 1, field.label)
sheet.write(row, 2, field.api_name)
sheet.write(row, 3, field.data_type)
sheet.write(row, 4, field.help_text)
sheet.write(row, 5, field.formula)
if schema.include_field_usage:
sheet.write(row, 6, field.field_usage_display_text)
row += 1
# Close the book
book.close()
# construct response
output.seek(0)
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response[
'Content-Disposition'] = "attachment; filename=schema_%s.xlsx" % schema.org_id
return response
except Exception as ex:
return HttpResponse(ex)
outfile.write(res_str)
res_str = "\n"
student_name = student_details[1][0][7:]
student_roll = student_details[1][1][11:]
res_str += student_roll + "," + student_name + ","
for i in range(1, len(data) - 1):
res_str += data[i][2] + ","
sgpa_odd = sem_marks[0][0][42:]
res_str += (sgpa_odd)
outfile.write(res_str)
print("Retrieved " + str(counter + 1) + " out of " + str(c) +
" roll numbers successfully.")
counter += 1
outfile.close()
if fileextension == ".xlsx" or fileextension == ".XLSX":
workbook = Workbook(filename + fileextension, {'strings_to_numbers': True})
c = workbook.add_format({'font_name': 'Segoe UI', 'align': 'center'})
b = workbook.add_format({
'font_name': 'Segoe UI',
'align': 'center',
'bold': True
})
g = workbook.add_format({
'color': 'green',
'font_name': 'Segoe UI',
'align': 'center',
'bold': True
})
n = workbook.add_format({
'color': 'red',
'font_name': 'Segoe UI',
import sys
from xlsxwriter.workbook import Workbook
# Add some command-line logic to read the file names.
for tsv_file in sys.argv[1:]:
#tsv_file = sys.argv[1]
print(tsv_file)
ain = tsv_file.split(".")
ain[-1] = "xlsx"
xlsx_file = ".".join(ain)
# Create an XlsxWriter workbook object and add a worksheet.
workbook = Workbook(xlsx_file)
worksheet = workbook.add_worksheet()
# Create a TSV file reader.
tsv_reader = csv.reader(open(tsv_file, 'rt'), delimiter="\t")
# Read the row data from the TSV file and write it to the XLSX file.
for row, data in enumerate(tsv_reader):
ndata = []
for x in data:
try:
y = float(x)
ndata.append(y)
except:
ndata.append(x)
############################################################################## # # A simple formatting example using XlsxWriter. # # This program demonstrates the indentation cell format. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook workbook = Workbook('cell_indentation.xlsx') worksheet = workbook.add_worksheet() indent1 = workbook.add_format({'indent': 1}) indent2 = workbook.add_format({'indent': 2}) worksheet.set_column('A:A', 40) worksheet.write('A1', "This text is indented 1 level", indent1) worksheet.write('A2', "This text is indented 2 levels", indent2) workbook.close()
def get_excel(self):
import io
from xlsxwriter.workbook import Workbook
try:
direccion = self.env['main.parameter.hr'].search(
[])[0].dir_create_file
except:
raise UserError(
'Falta configurar un directorio de descargas en el menu Configuracion/Parametros/Directorio de Descarga'
)
workbook = Workbook(direccion + 'prestamos.xlsx')
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(10)
boldbord.set_bg_color('#DCE6F1')
boldbord.set_font_name('Times New Roman')
especial1 = workbook.add_format()
especial1.set_align('center')
especial1.set_align('vcenter')
especial1.set_border(style=1)
especial1.set_text_wrap()
especial1.set_font_size(10)
especial1.set_font_name('Times New Roman')
especial3 = workbook.add_format({'bold': True})
especial3.set_align('center')
especial3.set_align('vcenter')
especial3.set_border(style=1)
especial3.set_text_wrap()
especial3.set_bg_color('#DCE6F1')
especial3.set_font_size(15)
especial3.set_font_name('Times New Roman')
numberdos = workbook.add_format({'num_format': '0'})
numberdos.set_border(style=1)
numberdos.set_font_size(10)
numberdos.set_font_name('Times New Roman')
dateformat = workbook.add_format({'num_format': 'd-m-yyyy'})
dateformat.set_border(style=1)
dateformat.set_font_size(10)
dateformat.set_font_name('Times New Roman')
hourformat = workbook.add_format({'num_format': 'hh:mm'})
hourformat.set_align('center')
hourformat.set_align('vcenter')
hourformat.set_border(style=1)
hourformat.set_font_size(10)
hourformat.set_font_name('Times New Roman')
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
##########ASISTENCIAS############
worksheet = workbook.add_worksheet("PRESTAMOS")
worksheet.set_tab_color('blue')
worksheet.merge_range(
1, 0, 1, 4,
"PRESTAMO %s %s" % (self.employee_id.name_related, self.date),
especial3)
worksheet.write(3, 0, "Empleado", boldbord)
worksheet.merge_range(3, 1, 3, 2, self.employee_id.name_related,
especial1)
worksheet.write(3, 3, "Fecha de Prestamo", boldbord)
worksheet.write(3, 4, self.date, especial1)
worksheet.write(5, 0, "Tipo de Prestamo", boldbord)
worksheet.merge_range(5, 1, 5, 2, self.loan_type_id.name, especial1)
worksheet.write(5, 3, "Numero de Cuotas", boldbord)
worksheet.write(5, 4, self.fees_number, especial1)
x = 7
worksheet.write(x, 0, "CUOTA", boldbord)
worksheet.write(x, 1, "MONTO", boldbord)
worksheet.write(x, 2, "FECHA DE PAGO", boldbord)
worksheet.write(x, 3, "DEUDA POR PAGAR", boldbord)
worksheet.write(x, 4, "VALIDACION", boldbord)
x = 8
for line in self.line_ids:
worksheet.write(x, 0, line.fee if line.fee else 0, numberdos)
worksheet.write(x, 1, line.amount if line.amount else 0, numberdos)
worksheet.write(x, 2, line.date if line.date else '', especial1)
worksheet.write(x, 3, line.debt if line.debt else 0, numberdos)
worksheet.write(
x, 4,
dict(line._fields['validation'].selection).get(line.validation)
if line.validation else '', especial1)
x += 1
tam_col = [12, 12, 12, 12, 12, 12]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
workbook.close()
f = open(direccion + 'prestamos.xlsx', 'rb')
vals = {
'output_name': 'Prestamo - %s.xlsx' % (self.date),
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['planilla.export.file'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "planilla.export.file",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def sage_xlsx(request, sage_batch_ref):
# Write to Excel
output = io.BytesIO()
workbook = Workbook(output, {'in_memory': True, 'remove_timezone': True})
worksheet = workbook.add_worksheet()
header = workbook.add_format({'bold': True})
header.set_bg_color('#F2F2F2')
header_a = workbook.add_format({'bold': True})
header_a.set_bg_color('#F2F2F2')
header_a.set_align('center')
header_b = workbook.add_format({'bold': True})
header_b.set_bg_color('#F2F2F2')
header_b.set_align('right')
date = workbook.add_format({'num_format': 'dd/mm/yyyy'})
date.set_align('center')
money = workbook.add_format({'num_format': '£#,##0.00'})
money.set_align('right')
center = workbook.add_format()
center.set_align('center')
italic_right = workbook.add_format()
italic_right.set_align('right')
italic_right.set_italic()
italic_center = workbook.add_format()
italic_center.set_align('center')
italic_center.set_italic()
worksheet.set_column('A:A', 14)
worksheet.set_column('B:B', 20)
worksheet.set_column('C:C', 20)
worksheet.set_column('D:D', 20)
worksheet.set_column('E:E', 14)
worksheet.set_column('F:F', 14)
worksheet.set_column('G:G', 30)
worksheet.set_column('H:H', 14)
worksheet.set_column('I:I', 14)
worksheet.set_column('J:J', 14)
worksheet.set_column('K:K', 14)
worksheet.set_column('L:L', 14)
worksheet.set_column('M:M', 14)
worksheet.set_column('N:N', 14)
worksheet.set_column('O:O', 14)
# Write Header
worksheet.write(0, 0, 'Type', header_a)
worksheet.write(0, 1, 'Account Reference', header_a)
worksheet.write(0, 2, 'Nominal A/C Ref', header_a)
worksheet.write(0, 3, 'Department Code', header_a)
worksheet.write(0, 4, 'Date', header_a)
worksheet.write(0, 5, 'Reference', header_a)
worksheet.write(0, 6, 'Details', header_a)
worksheet.write(0, 7, 'Net Amount', header_a)
worksheet.write(0, 8, 'Tax Code', header_a)
worksheet.write(0, 9, 'Tax Amount', header_a)
worksheet.write(0, 10, 'Exchange Rate', header_a)
worksheet.write(0, 11, 'Extra Reference', header_a)
worksheet.write(0, 12, 'User Name', header_a)
worksheet.write(0, 13, 'Project Refn', header_a)
worksheet.write(0, 14, 'Cost Code Refn', header_a)
n = 0
val_summary = 0
recs = SageBatchDetails.objects.filter(
sage_batch_ref_id=sage_batch_ref).order_by('-id')
transactions_for_batch_total = SageBatchTransactions.objects.filter(
sage_batch_ref_id=sage_batch_ref,
transactionsourceid__in=['GO1', 'GO3'],
remove=0).aggregate(Sum('sage_batch_netpayment'))
wip_transactions_for_batch = transactions_for_batch_total[
"sage_batch_netpayment__sum"]
sagebatchheader = SageBatchHeaders.objects.get(
sage_batch_ref=sage_batch_ref)
date = sagebatchheader.sage_batch_date.strftime('%d/%m/%Y')
transaction_total = 0
for transaction in recs:
n += 1
# val_summary += transaction.transnetpayment
if transaction.transactionsourceid == 'SP1' or transaction.transactionsourceid == 'SP2' or transaction.transactionsourceid == 'SP3'\
or transaction.transactionsourceid == 'GO1' or transaction.transactionsourceid == 'GO3':
worksheet.write(n, 0, 'JC', center)
worksheet.write(n, 1, '', italic_center)
worksheet.write(n, 2, transaction.nominal_account_ref)
worksheet.write(n, 3, '0')
worksheet.write(n, 4, '')
worksheet.write(n, 4, date)
worksheet.write(n, 5, '')
worksheet.write(n, 6, transaction.account_reference, center)
worksheet.write(n, 7, transaction.batch_detail_total, money)
worksheet.write(n, 8, transaction.tax_code, center)
if transaction.tax_code == 'T1':
transaction_wip = (transaction.batch_detail_total
or 0) * decimal.Decimal(0.2)
worksheet.write(n, 9, transaction_wip, money)
transaction_total = transaction_total + transaction_wip
else:
worksheet.write(n, 9, 0.00, money)
if wip_transactions_for_batch:
n += 1
worksheet.write(n, 0, 'JD', center)
worksheet.write(n, 1, '', italic_center)
worksheet.write(n, 2, 'XXXX')
worksheet.write(n, 3, '0')
worksheet.write(n, 4, '')
worksheet.write(n, 4, date)
worksheet.write(n, 5, '')
worksheet.write(n, 6, 'DD Call Debtor Control (XXXX)', center)
worksheet.write(n, 7, wip_transactions_for_batch, money)
worksheet.write(n, 8, '', center)
worksheet.write(n, 9, transaction_total, money)
# worksheet.write(n, 9, '=SUM(J2:J'+str(n)+')')
# if transaction.tax_code != 'T1':
# worksheet.write(n, 8, transaction.batch_detail_total*decimal(0.2), center)
# else:
# worksheet.write(n, 8, '0.00', money)
workbook.close()
output.seek(0)
filename = 'Sage Export '+ sage_batch_ref +' @ {date:%Y-%m-%d}.xlsx'\
.format(date=datetime.datetime.now())
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=%s" % filename
output.close()
return response
import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('Список студентов.xlsx') # Открытие и запись в файл
worksheet = workbook.add_worksheet()
conn = sqlite3.connect('students.db') # Подключение к БД
c = conn.cursor()
c.execute("SELECT * FROM students")
print('Выберите направление работы:')
vib = int(
input('''1 - Научное студенческое общество;
2 - Социальная поддержка;
3 - Спорт;
4 - Культура и творчество;
5 - Штаб трудовых дел. \n''')) # Выбор вида работ
if vib == 1:
mysel = c.execute(
"SELECT * FROM students WHERE direction_of_work='Научное студенческое общество'"
)
elif vib == 2:
mysel = c.execute(
"SELECT * FROM students WHERE direction_of_work='Социальная поддержка'"
)
elif vib == 3:
mysel = c.execute("SELECT * FROM students WHERE direction_of_work='Спорт'")
def do_rebuild(self):
self.env.cr.execute(
""" DROP VIEW IF EXISTS saldo_comprobante_empresa;
create or replace view saldo_comprobante_empresa as (
select t1.id as id, ap.name as periodo,t3.nro_documento as ruc,t3.name as empresa,t4.code as code,t4.name as descripcion,
CASE WHEN t5.type= 'payable' THEN 'A pagar' ELSE 'A cobrar' END as tipo_cuenta
,t_debe as debe,t_haber as haber, CASE WHEN abs(t_debe-t_haber) < 0.01 then 0 else t_debe-t_haber end as saldo from (
select min(aml.id) as id, concat(aml.partner_id,'-',aml.account_id) as identificador,sum(aml.debit) as t_debe,sum(aml.credit) as t_haber from account_move_line aml
inner join account_move am on am.id = aml.move_id
inner join account_period api on api.date_start <= am.fecha_contable and api.date_stop >= am.fecha_contable and am.fecha_special = api.special
inner join account_account aa on aa.id = aml.account_id
left join account_account_type aat on aat.id = aa.user_type_id
where (aat.type='receivable' or aat.type='payable' ) -- and aa.reconcile = true
and periodo_num(api.code) >= periodo_num('""" + str(self.periodo_ini.code) +
"""') and periodo_num(api.code) <= periodo_num('""" +
str(self.periodo_fin.code) + """')
and am.state != 'draft'
group by identificador) t1
left join account_move_line t2 on t2.id=t1.id
left join account_move am on am.id = t2.move_id
left join account_period ap on ap.date_start <= am.fecha_contable and ap.date_stop >= am.fecha_contable and am.fecha_special = ap.special
left join res_partner t3 on t3.id=t2.partner_id
left join account_account t4 on t4.id=t2.account_id
left join account_account_type t5 on t5.id = t4.user_type_id
order by code,empresa
)""")
filtro = []
if self.check == True:
filtro.append(('saldo', '!=', 0))
if self.cuenta.id:
filtro.append(('code', '=', self.cuenta.code))
if self.empresa.id:
filtro.append(('empresa', '=', self.empresa.name))
if self.tipo:
filtro.append(('tipo_cuenta', '=', self.tipo))
move_obj = self.env['saldo.comprobante.empresa']
lstidsmove = move_obj.search(filtro)
if (len(lstidsmove) == 0):
raise osv.except_osv('Alerta', 'No contiene datos.')
#DSC_Exportar a CSV por el numero de filas
self.env.cr.execute(
"""select count(*) from saldo_comprobante_empresa""")
rows = self.env.cr.fetchone()
#if self.mostrar == 'excel' and rows[0] > 1000:
# self.mostrar = 'csv'
if self.mostrar == 'pantalla':
return {
'domain': filtro,
'type': 'ir.actions.act_window',
'res_model': 'saldo.comprobante.empresa',
'view_mode': 'tree',
'view_type': 'form',
'views': [(False, 'tree')],
}
#DSC_
if self.mostrar == 'csv':
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
docname = 'SaldoEmpresa.csv'
#CSV
sql_query = """ COPY (SELECT * FROM saldo_comprobante_empresa )TO '""" + direccion + docname + """' WITH DELIMITER ',' CSV HEADER
"""
self.env.cr.execute(sql_query)
#Caracteres Especiales
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
f = open(direccion + docname, 'rb')
vals = {
'output_name': docname,
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
if self.mostrar == 'excel':
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search(
[])[0].dir_create_file
workbook = Workbook(direccion + 'saldoperiodo.xlsx')
worksheet = workbook.add_worksheet("Analisis Saldo x Empresa")
#Print Format
worksheet.set_landscape() #Horizontal
worksheet.set_paper(9) #A-4
worksheet.set_margins(left=0.75, right=0.75, top=1, bottom=1)
worksheet.fit_to_pages(1, 0) # Ajustar por Columna
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(9)
boldbord.set_bg_color('#DCE6F1')
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_text_wrap()
numberdos.set_border(style=1)
numbertres.set_border(style=1)
title = workbook.add_format({'bold': True})
title.set_align('center')
title.set_align('vcenter')
title.set_text_wrap()
title.set_font_size(20)
worksheet.set_row(0, 30)
x = 9
tam_col = [
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
]
tam_letra = 1.2
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 0, 0, 8, u"Análisis de Saldos x Empresa",
title)
worksheet.write(1, 0, u"Año Fiscal", bold)
worksheet.write(1, 1, self.fiscal_id.name, normal)
worksheet.write(2, 0, u"Periodo Inicial", bold)
worksheet.write(2, 1, self.periodo_ini.name, normal)
worksheet.write(3, 0, u"Periodo Final", bold)
worksheet.write(3, 1, self.periodo_fin.name, normal)
worksheet.write(4, 0, u"Solo Pendientes", bold)
worksheet.write(4, 1, 'Si' if self.check else 'No', normal)
worksheet.write(5, 0, u"Empresa", bold)
worksheet.write(5, 1,
self.empresa.name if self.empresa.name else '',
normal)
worksheet.write(6, 0, u"Cuenta", bold)
worksheet.write(6, 1, self.cuenta.name if self.cuenta.name else '',
normal)
worksheet.write(8, 0, "Periodo", boldbord)
worksheet.write(8, 1, "Empresa", boldbord)
worksheet.write(8, 2, "RUC", boldbord)
worksheet.write(8, 3, "Tipo Cuenta", boldbord)
worksheet.write(8, 4, u"Cuenta", boldbord)
worksheet.write(8, 5, u"Descripción", boldbord)
worksheet.write(8, 6, "Debe", boldbord)
worksheet.write(8, 7, "Haber", boldbord)
worksheet.write(8, 8, "Saldo", boldbord)
for line in self.env['saldo.comprobante.empresa'].search(filtro):
worksheet.write(x, 0, line.periodo if line.periodo else '',
bord)
worksheet.write(x, 1, line.empresa if line.empresa else '',
bord)
worksheet.write(x, 2, line.ruc if line.ruc else '', bord)
worksheet.write(x, 3,
line.tipo_cuenta if line.tipo_cuenta else '',
bord)
worksheet.write(x, 4, line.code if line.code else '', bord)
worksheet.write(x, 5,
line.descripcion if line.descripcion else '',
bord)
worksheet.write(x, 6, line.debe, numberdos)
worksheet.write(x, 7, line.haber, numberdos)
worksheet.write(x, 8, line.saldo, numberdos)
x = x + 1
tam_col = [
15, 45, 12, 10, 25, 45, 11, 11, 10, 11, 14, 10, 11, 14, 14, 10,
16, 16, 20, 36
]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
worksheet.set_column('R:R', tam_col[17])
worksheet.set_column('S:S', tam_col[18])
worksheet.set_column('T:T', tam_col[19])
workbook.close()
f = open(direccion + 'saldoperiodo.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'SaldoEmpresa.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def get_excel(self):
import io
from xlsxwriter.workbook import Workbook
ReportBase = self.env['report.base']
direccion = self.env['main.parameter'].search(
[('company_id', '=', self.company_id.id)], limit=1).dir_create_file
if not direccion:
raise UserError(
u'No existe un Directorio Exportadores configurado en Parametros Principales de Contabilidad para su Compañía'
)
workbook = Workbook(direccion + 'Analisis_Vencimientos.xlsx')
workbook, formats = ReportBase.get_formats(workbook)
import importlib
import sys
importlib.reload(sys)
##########ANALISIS VENCIMIENTO############
worksheet = workbook.add_worksheet("ANALISIS VENCIMIENTO")
worksheet.set_tab_color('blue')
HEADERS = [
'FECHA EM', 'FECHA VEN', 'CUENTA', 'DIVISA', 'TDP', 'RUC',
'PARTNER', 'TD', 'NRO COMPROBANTE', u'CU BANCO', u'SALDO MN',
u'SALDO ME', u'0 - 30', u'31 - 60', u'61 - 90', u'91 - 120',
u'121 - 150', u'151 - 180', u'181 - MÁS'
]
worksheet = ReportBase.get_headers(worksheet, HEADERS, 0, 0,
formats['boldbord'])
x = 1
for line in self.env['maturity.analysis.book'].search([]):
worksheet.write(x, 0, line.fecha_emi if line.fecha_emi else '',
formats['dateformat'])
worksheet.write(x, 1, line.fecha_ven if line.fecha_ven else '',
formats['dateformat'])
worksheet.write(x, 2, line.cuenta if line.cuenta else '',
formats['especial1'])
worksheet.write(x, 3, line.divisa if line.divisa else '',
formats['especial1'])
worksheet.write(x, 4, line.tdp if line.tdp else '',
formats['especial1'])
worksheet.write(x, 5, line.doc_partner if line.doc_partner else '',
formats['especial1'])
worksheet.write(x, 6, line.partner if line.partner else '',
formats['especial1'])
worksheet.write(x, 7, line.td_sunat if line.td_sunat else '',
formats['especial1'])
worksheet.write(
x, 8, line.nro_comprobante if line.nro_comprobante else '',
formats['especial1'])
worksheet.write(x, 9, line.cu_banco if line.cu_banco else '',
formats['especial1'])
worksheet.write(x, 10, line.saldo_mn if line.saldo_mn else '0.00',
formats['numberdos'])
worksheet.write(x, 11, line.saldo_me if line.saldo_me else '0.00',
formats['numberdos'])
worksheet.write(x, 12,
line.cero_treinta if line.cero_treinta else '0.00',
formats['numberdos'])
worksheet.write(
x, 13,
line.treinta1_sesenta if line.treinta1_sesenta else '0.00',
formats['numberdos'])
worksheet.write(
x, 14,
line.sesenta1_noventa if line.sesenta1_noventa else '0.00',
formats['numberdos'])
worksheet.write(
x, 15,
line.noventa1_ciento20 if line.noventa1_ciento20 else '0.00',
formats['numberdos'])
worksheet.write(
x, 16,
line.ciento21_ciento50 if line.ciento21_ciento50 else '0.00',
formats['numberdos'])
worksheet.write(
x, 17,
line.ciento51_ciento80 if line.ciento51_ciento80 else '0.00',
formats['numberdos'])
worksheet.write(x, 18,
line.ciento81_mas if line.ciento81_mas else '0.00',
formats['numberdos'])
x += 1
widths = [
10, 12, 8, 8, 6, 11, 40, 6, 21, 10, 16, 16, 15, 15, 15, 15, 15, 15,
15
]
worksheet = ReportBase.resize_cells(worksheet, widths)
workbook.close()
f = open(direccion + 'Analisis_Vencimientos.xlsx', 'rb')
return self.env['popup.it'].get_file(
'Analisis_Vencimientos.xlsx',
base64.encodestring(b''.join(f.readlines())))
import cv2
from numpy import *
from xlsxwriter.workbook import Workbook
ROUND = 7.
img = cv2.imread('123.jpg')
y, x, ch = img.shape
img = cv2.resize(img, (y / 3, x / 3))
y, x, ch = img.shape
t = Workbook('pic.xlsx')
ts = t.add_worksheet()
ts.set_default_row(height=1.8)
ts.set_column(0, 5000, .2)
s = [(i, j) for i in xrange(y) for j in xrange(x)]
for i, j in s:
color = rint(img[i, j] / ROUND) * ROUND
color = '#%02X%02X%02X' % tuple(color.tolist()[::-1])
fmat = t.add_format({'bg_color': color})
ts.write(i, j, '', fmat)
if j == 0: print i, '/', y
t.close()
import sqlite3
from sr_extract_stress import stress_to_database
from sr_extract_sr import sr_to_database
from sr_extract_vm import vm_stress_to_database
from sr_extract_solid import solid_stress_to_database
from patran_input import process_input
import time
from xlsxwriter.workbook import Workbook
from math import sqrt
# Creare fisier excel pentru scrierea rezultatelor
workbook = Workbook('ResultsSR.xlsx')
worksheet = workbook.add_worksheet('Data_Results_SR')
worksheet2 = workbook.add_worksheet('Summary_SR_MOS')
worksheet3 = workbook.add_worksheet('Data_Results_HC')
worksheet4 = workbook.add_worksheet('Summary_HC_MOS')
worksheet5 = workbook.add_worksheet('Data_Results_VM')
worksheet6 = workbook.add_worksheet('Summary_VM')
# Creare baza de date pentru stocarea datelor
conn = sqlite3.connect('ResultsData.db')
conn.execute('pragma journal_mode=wal')
c = conn.cursor()
c.execute('''PRAGMA synchronous = OFF''')
c.execute("BEGIN TRANSACTION")
def nrCazuri():
c.execute('SELECT COUNT(DISTINCT subcase) FROM ElmStrengthRatio')
nrCaz = c.fetchone()
return nrCaz
row_max = int(sys.argv[1]) / 2
else:
row_max = 1000
if len(sys.argv) > 2:
optimise = 1
else:
optimise = 0
col_max = 50
# Start timing after everything is loaded.
start_time = clock()
# Start of program being tested.
workbook = Workbook('py_ewx.xlsx', {'constant_memory': optimise})
worksheet = workbook.add_worksheet()
worksheet.set_column(0, col_max, 18)
for row in range(0, row_max):
for col in range(0, col_max):
worksheet.write_string(row * 2, col, "Row: %d Col: %d" % (row, col))
for col in range(0, col_max + 1):
worksheet.write_number(row * 2 + 1, col, row + col)
# Get total memory size for workbook object before closing it.
total_size = asizeof(workbook)
workbook.close()
alldata = df.values.flatten()
alldata = alldata[~np.isnan(alldata)]
if 'bins' in kwargs:
h, b = np.histogram(alldata, bins=kwargs['bin'])
else:
h, b = np.histogram(alldata)
bins = b
bindf = {}
for colname, data in df.items():
data = data.dropna().values
h, b = np.histogram(data, bins=bins)
bindf[colname] = pandas.Series(h, index=[x for x in b[:-1]])
df = pandas.DataFrame(bindf)
worksheet = writeData(df, wb, sheetname, **kwargs)
params = {'type': 'column'}
if 'subtype' in kwargs:
params['subtype'] = kwargs['subtype']
chart = wb.add_chart(params)
__addAxisInfo(chart, kwargs)
kwargs['gap'] = 0
addSeries(df, chart, sheetname, **kwargs)
# Insert the chart into the worksheet (with an offset).
cell = __getLocation(df, kwargs)
worksheet.insert_chart(cell, chart, {'x_scale': 2.0, 'y_scale': 2.0})
if __name__ == "__main__":
wb = Workbook('test.xlsx')
df = pandas.DataFrame.from_csv('test_dates.csv')
plotLineChart(df, wb, 'test_dates', style=42)
############################################################################## # # A simple example of merging cells with the XlsxWriter Python module. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook # Create an new Excel file and add a worksheet. workbook = Workbook('merge1.xlsx') worksheet = workbook.add_worksheet() # Increase the cell size of the merged cells to highlight the formatting. worksheet.set_column('B:D', 12) worksheet.set_row(3, 30) worksheet.set_row(6, 30) worksheet.set_row(7, 30) # Create a format to use in the merged range. merge_format = workbook.add_format({ 'bold': 1, 'border': 1, 'align': 'center', 'valign': 'vcenter', 'fg_color': 'yellow'}) # Merge 3 cells. worksheet.merge_range('B4:D4', 'Merged Range', merge_format)
def getWorkbook(fname, options=None):
"""Return a xlsxwriter Workbook by the given name"""
if options is not None:
return Workbook(fname, options)
return Workbook(fname)
############################################################################### # # An example of how to create autofilters with XlsxWriter. # # An autofilter is a way of adding drop down lists to the headers of a 2D # range of worksheet data. This allows users to filter the data based on # simple criteria so that some data is shown and some is hidden. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook workbook = Workbook('autofilter.xlsx') # Add a worksheet for each autofilter example. worksheet1 = workbook.add_worksheet() worksheet2 = workbook.add_worksheet() worksheet3 = workbook.add_worksheet() worksheet4 = workbook.add_worksheet() worksheet5 = workbook.add_worksheet() worksheet6 = workbook.add_worksheet() # Add a bold format for the headers. bold = workbook.add_format({'bold': 1}) # Open a text file with autofilter example data. textfile = open('autofilter_data.txt') # Read the headers from the first line of the input file. headers = textfile.readline().strip("\n").split()
def generate_excel(self):
tipeFile = ["extracto_bancario", "cajas_registradoras"]
title_report_list = ["Extracto Bancario", "Cajas registradoras"]
size_ref = 0
# nombre de archivo
if re.match("(.*)CAJA(.*)", self.name):
name_file = tipeFile[1] + \
datetime.now().strftime("%Y%m%d%H%M%S") + '.xlsx'
title_report = title_report_list[1]
size_ref = 40
else:
name_file = tipeFile[0] + \
datetime.now().strftime("%Y%m%d%H%M%S") + '.xlsx'
title_report = title_report_list[0]
size_ref = 20
direccion = self.env['main.parameter'].search([])[0].dir_create_file
# nombre de la direccion
direccion += name_file
workbook = Workbook(direccion, {
'in_memory': True,
'strings_to_numbers': True
})
workbook.formats[0].set_font_size(10)
workbook.formats[0].set_font_name("Arial")
worksheet = workbook.add_worksheet(title_report)
worksheet.set_zoom(80)
worksheet.set_row(1, 60)
worksheet.set_column(1, 1, 15) # ancho de B y C
worksheet.set_column(2, 5, 40) # ancho de B y C
worksheet.set_column(3, 4, 50) # ancho de B y C
worksheet.set_column(6, 6, 18)
########################### Cabecera ##################################
merge_format_left_label = workbook.add_format({
'bold': 1,
'align': 'left',
'valign': 'vcenter',
'font_size': 12,
})
merge_format_left_text = workbook.add_format({
'bold': 0,
'border': 0,
'align': 'left',
'valign': 'vcenter',
})
merge_format_title = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'underline': 0,
'valign': 'top',
'font_size': 14,
'font_name': 'Arial'
})
merge_format_right = workbook.add_format({
'align': 'right',
'valign': 'vcenter',
'right': 1
})
merge_format_center_header = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'font_size': 12,
'font_name': 'Arial',
})
merge_format_center_text = workbook.add_format({
'border': 0,
'align': 'center',
'valign': 'vcenter',
'font_size': 10,
'font_name': 'Arial',
})
merge_format_center_text_wrap = workbook.add_format({
'border':
0,
'align':
'center',
'valign':
'vcenter',
'font_size':
10,
'font_name':
'Arial',
})
merge_format_bottom_center = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'font_size': 12,
'font_name': 'Arial',
})
formatMoneyWithBorder = workbook.add_format({
'valign':
'vcenter',
'align':
'right',
'num_format':
'"%s" #,##0.00' % self.currency_id.symbol
})
formatMoney = workbook.add_format({
'num_format': '#,##0.00',
'border': 1,
'valign': 'vcenter',
'align': 'right'
})
# agregando logo al documento
#worksheet.insert_image('B2:D2', 'company_logo.jpg', {
# 'x_offset': 25, 'y_offset': 5, 'y_scale': 0.95, 'x_scale': 0.85})
# Borde celdas de imagen
#worksheet.merge_range('B2:C2', "", merge_format_title)
# coloca Titulo
# worksheet.merge_range('D2:N2', title_report, merge_format_title)
# Inf - Fecha
merge_format_bottom_center.set_text_wrap()
worksheet.merge_range('B2:G2', title_report + "\n" + self.name,
merge_format_bottom_center)
# Cabezera
worksheet.write('B4', "Diario:", merge_format_left_label)
worksheet.write('B5', "Fecha:", merge_format_left_label)
worksheet.write('F4', "Saldo Inicial:", merge_format_left_label)
worksheet.write('F5', "Balance Final:", merge_format_left_label)
worksheet.write('C4', self.journal_id[0].name, merge_format_left_text)
worksheet.write('C5', self.date, merge_format_left_text)
worksheet.write_number('G4', self.balance_start, formatMoneyWithBorder)
worksheet.write_number('G5', self.balance_end_real,
formatMoneyWithBorder)
# worksheet.set_column(4, 6, 30) # ancho de B y C
x = 7
y = 1
i = 0
worksheet.write("B7", "Fecha", merge_format_center_header)
worksheet.write("C7", "Etiqueta", merge_format_center_header)
worksheet.write("D7", "Partner", merge_format_center_header)
worksheet.write("E7", "Referencia", merge_format_center_header)
worksheet.write("F7", "Medio de Pago", merge_format_center_header)
worksheet.write("G7", "Cantidad", merge_format_center_header)
for item in self.line_ids:
worksheet.set_row(x + i, size_ref)
if (item['date']):
worksheet.write(x + i, y, item['date'],
merge_format_center_text)
if (item['name']):
worksheet.write(x + i, y + 1, item['name'],
merge_format_center_text)
if item.partner_id.name:
worksheet.write(x + i, y + 2, item.partner_id.name,
merge_format_center_text)
if item['ref']:
worksheet.write(x + i, y + 3, item['ref'],
merge_format_center_text)
if item.medio_pago.name:
merge_format_center_text.set_text_wrap()
worksheet.write(x + i, y + 4, item.medio_pago.name,
merge_format_center_text)
if item['amount']:
worksheet.write_number(x + i, y + 5, item['amount'],
formatMoneyWithBorder)
i = i + 1
workbook.close()
# os.remove('company_logo.jpg')
f = open(direccion, 'rb')
vals = {
'output_name': 'Reporte_' + name_file,
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
def excel():
if request.method == 'GET':
user_data = User.query.filter_by(id=current_user.id).first()
build_data = Build.query.filter_by(user_id=user_data.id).first()
if request.args.get("date_start") != None and request.args.get(
"time_start") != None and request.args.get(
"date_end") != None and request.args.get(
"time_end") != None and request.args.get(
"department") != None and request.args.get(
"department") != "0":
if current_user.role_id == 2:
build_data_select = Build.query.all()
department_data = Department.query.filter_by(
build_id=request.args.get("build")).all()
build_log_data = Build_Log.query.filter_by(build_id=request.args.get("build")).filter(Build_Log.created_at >= request.args.get("date_start")+" "+request.args.get("time_start")).filter(Build_Log.created_at <= request.args.get("date_end")+" "+request.args.get("time_end"))\
.filter_by(department_id=request.args.get("department")).all()
else:
build_data_select = Build.query.filter_by(
id=build_data.id).all()
department_data = Department.query.filter_by(
build_id=request.args.get("build")).all()
build_log_data = Build_Log.query.filter_by(build_id=build_data.id).filter(Build_Log.created_at >= request.args.get("date_start")+" "+request.args.get("time_start")).filter(Build_Log.created_at <= request.args.get("date_end")+" "+request.args.get("time_end"))\
.filter_by(department_id=request.args.get("department")).all()
for x in build_log_data:
for j in build_data_select:
if j.id == x.build_id:
x.build_name = j.build_name
for j in department_data:
if j.id == x.department_id:
x.department_name = j.department_name
x.created_at = str(x.created_at).split(".")[0]
x.sum = equation_sum(x.variable_a, x.variable_b, x.variable_c)
x.error = equation_error(x.variable_a, x.variable_b,
x.variable_c)
elif request.args.get("date_start") != None and request.args.get(
"time_start") != None and request.args.get(
"date_end") != None and request.args.get(
"time_end") != None:
if current_user.role_id == 2:
build_data_select = Build.query.all()
department_data = Department.query.filter_by(
build_id=request.args.get("build")).all()
build_log_data = Build_Log.query.filter_by(
build_id=request.args.get("build")
).filter(
Build_Log.created_at >= request.args.get("date_start") +
" " + request.args.get("time_start")).filter(
Build_Log.created_at <= request.args.get("date_end") +
" " + request.args.get("time_end")).all()
else:
build_data_select = Build.query.filter_by(
id=build_data.id).all()
department_data = Department.query.filter_by(
build_id=request.args.get("build")).all()
build_log_data = Build_Log.query.filter_by(
build_id=build_data.id
).filter(
Build_Log.created_at >= request.args.get("date_start") +
" " + request.args.get("time_start")).filter(
Build_Log.created_at <= request.args.get("date_end") +
" " + request.args.get("time_end")).all()
for x in build_log_data:
for j in build_data_select:
if j.id == x.build_id:
x.build_name = j.build_name
for j in department_data:
if j.id == x.department_id:
x.department_name = j.department_name
x.created_at = str(x.created_at).split(".")[0]
x.sum = equation_sum(x.variable_a, x.variable_b, x.variable_c)
x.error = equation_error(x.variable_a, x.variable_b,
x.variable_c)
else:
build_log_data = []
department_data = []
if current_user.role_id == 2:
build_data_select = Build.query.all()
else:
build_data_select = Build.query.filter_by(
id=build_data.id).all()
output = BytesIO()
workbook = Workbook(output)
sheet = workbook.add_worksheet('sheet_1')
header = workbook.add_format({
'bg_color': '#F7F7F7',
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
text = workbook.add_format({
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
# 'fg_color': 'yellow'
})
set_col = 7
set_row = 2
sheet.merge_range(
'C2:J5',
'บริษัท ปตท. จำกัด (มหาชน) \r\n 555 ถนนวิภาวดีรังสิต แขวงจตุจักร เขตจตุจักร 10900 \r\n โทรศัพท์ : 0-2537-2000 โทรสาร : 0-2537-3498-9',
merge_format)
sheet.write(set_col, 1, ("#"), header)
sheet.write(set_col, 2, ("Station"), header)
sheet.write(set_col, 3, ("License Plate"), header)
sheet.write(set_col, 4, ("Oil Type"), header)
sheet.write(set_col, 5, ("API"), header)
sheet.write(set_col, 6, ("API@Ori"), header)
sheet.write(set_col, 7, ("Temp"), header)
sheet.write(set_col, 8, ("API@60F"), header)
sheet.write(set_col, 9, ("Error"), header)
sheet.write(set_col, 10, ("Date"), header)
count = 0
for item in build_log_data:
set_col += 1
sheet.write(set_col, 1, (count), text)
try:
sheet.write(set_col, 2, (item.build_name), text)
sheet.write(set_col, 3, (item.license_plate), text)
except:
sheet.write(set_col, 2, (""), text)
sheet.write(set_col, 3, (""), text)
try:
sheet.write(set_col, 4, (item.department_name), text)
except:
sheet.write(set_col, 4, (""), text)
try:
sheet.write(set_col, 5, (item.variable_a), text)
sheet.write(set_col, 6, (item.variable_b), text)
sheet.write(set_col, 7, (item.variable_c), text)
sheet.write(set_col, 8, (item.sum), text)
sheet.write(set_col, 9, (item.error), text)
sheet.write(set_col, 10, (str(item.created_at).split(".")[0]),
text)
except:
sheet.write(set_col, 5, (""), text)
sheet.write(set_col, 6, (""), text)
sheet.write(set_col, 7, (""), text)
sheet.write(set_col, 8, (""), text)
sheet.write(set_col, 9, (""), text)
sheet.write(set_col, 10, (""), text)
count += 1
workbook.close()
output.seek(0)
return send_file(output,
attachment_filename='data.xlsx',
as_attachment=True,
mimetype=EXCELMIME)
############################################################################## # # A simple example of some of the features of the XlsxWriter Python module. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook # Create an new Excel file and add a worksheet. workbook = Workbook('demo.xlsx') worksheet = workbook.add_worksheet() # Widen the first column to make the text clearer. worksheet.set_column('A:A', 20) # Add a bold format to use to highlight cells. bold = workbook.add_format({'bold': 1}) # Write some simple text. worksheet.write('A1', 'Hello') # Text with formatting. worksheet.write('A2', 'World', bold) # Write some numbers, with row/column notation. worksheet.write(2, 0, 123) worksheet.write(3, 0, 123.456) workbook.close()
import os
import glob
import csv
from xlsxwriter.workbook import Workbook
for csv_file in glob.glob(os.path.join('.', 'data/*.csv')):
workbook = Workbook(csv_file[:-4] + '.xlsx')
worksheet = workbook.add_worksheet()
with open(csv_file, 'rt', encoding='utf8') as f:
reader = csv.reader(f)
for r, row in enumerate(reader):
for c, col in enumerate(row):
worksheet.write(r, c, col)
print("converting...")
workbook.close()
keyword_set = keyword_split
keyword_sep = ''
for each in keyword_set:
keyword_sep = keyword_sep + each + '|'
keyword_sep = keyword_sep.strip('|')
#print('keyword_sep:', keyword_sep)
if (len(keyword_sep)) == 0:
continue
#用所有关键词将整段话分割，再插入富字符串，然后捆绑颜色、关键词和后面的文本，需注意一一对应
temp_list = re.split(keyword_sep, txt)
params = []
temp_list_num = len(temp_list)
for i in range(temp_list_num):
if i != 0:
params.extend((red, keyword_set[i - 1], temp_list[i]))
else:
params.append(temp_list[i])
worksheet.write(work_row, work_col, id_data)
worksheet.write_rich_string(work_row, work_col2, *params)
work_row = work_row + 1
workbook.close()
if __name__ == '__main__':
data = pd.read_excel(r'E:\DLML\DataHandle\data\data.xlsx',
index=None) #原文本文件
data_result = Workbook(r'E:\DLML\DataHandle\data\data-result.xlsx') #标注结果
cate_data = pd.read_excel(r'E:\DLML\DataHandle\data\cate_data.xlsx',
index=None) #匹配的文本文件
model_set_colors(data, data_result, cate_data)
'Florida': 'FL',
'Georgia': 'GA',
'Illinois': 'IL',
'Kansas': 'KS',
'Michigan': 'MI',
'New York': 'NY',
'Ohio': 'OH',
'Pennsylvania': 'PA',
'Texas': 'TX'
}
# In[14]:
import sqlite3
from xlsxwriter.workbook import Workbook
workbook = Workbook('hospital_ranking.xlsx')
SheetNum = 0
c = conn.cursor()
while SheetNum < 11:
worksheet = workbook.add_worksheet(StatesList[SheetNum])
if StatesList[SheetNum] == 'Nationwide':
mysel = c.execute(
"""select hospital_national_ranking.provider_id,hospital_name,city,state,county_name
from hospital_general_information
join hospital_national_ranking
on hospital_national_ranking.provider_id=hospital_general_information.provider_id
order by ranking limit 100""")
else:
l = [StatesDictionary[StatesList[SheetNum]]]
mysel = c.execute(
def get_xlsx_writer(self, response, **kwargs):
return Workbook(response, {'in_memory': True})
def export_reviewer_votes(request, conference_slug):
"""
Write reviewer votes to a spreadsheet.
"""
if not request.user.is_superuser:
raise PermissionDenied
conference = get_object_or_404(Conference, slug=conference_slug)
proposal_sections = conference.proposal_sections.all()
proposals_qs = Proposal.objects.select_related(
'proposal_type',
'proposal_section',
'conference',
'author',
).filter(conference=conference, status=ProposalStatus.PUBLIC)
proposals_qs = sorted(proposals_qs,
key=lambda x: x.get_reviewer_votes_sum(),
reverse=True)
vote_values_list = ProposalSectionReviewerVoteValue.objects.order_by(
'-vote_value')
vote_values_desc = tuple(
i.description
for i in ProposalSectionReviewerVoteValue.objects.order_by(
'-vote_value'))
header = ('Proposal Type', 'Title', 'Sum of reviewer votes', 'No. of reviewer votes') + \
tuple(vote_values_desc) + ('Public votes count', 'Vote Comments')
output = StringIO.StringIO()
with Workbook(output) as book:
for section in proposal_sections:
sheet = book.add_worksheet(section.name[:30])
cell_format = book.add_format({'bold': True})
sheet.write_row(0, 0, header, cell_format)
section_proposals = [
p for p in proposals_qs if p.proposal_section == section
]
for index, p in enumerate(section_proposals, 1):
vote_details = tuple(
p.get_reviewer_votes_count_by_value(v)
for v in vote_values_list)
vote_comment = '\n'.join([
comment.comment
for comment in p.proposalcomment_set.filter(
vote=True,
deleted=False,
)
])
row = (p.proposal_type.name, p.title, p.get_reviewer_votes_sum(),
p.get_reviewer_votes_count(),) + \
vote_details + (p.get_votes_count(), vote_comment,)
if p.get_reviewer_votes_count_by_value(
ProposalSectionReviewerVoteValue.objects.get(
vote_value=ProposalReviewVote.NOT_ALLOWED)) > 0:
cell_format = book.add_format({'bg_color': 'red'})
elif p.get_reviewer_votes_count_by_value(
ProposalSectionReviewerVoteValue.objects.get(
vote_value=ProposalReviewVote.MUST_HAVE)) > 2:
cell_format = book.add_format({'bg_color': 'green'})
elif p.get_reviewer_votes_count() < 2:
cell_format = book.add_format({'bg_color': 'yellow'})
else:
cell_format = None
sheet.write_row(index, 0, row, cell_format)
output.seek(0)
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
file_name = str(uuid.uuid4())[:8]
response[
'Content-Disposition'] = "attachment; filename=junction-{}.xlsx".format(
file_name)
return response
############################################################################## # # A simple formatting example using XlsxWriter. # # This program demonstrates the indentation cell format. # # Copyright 2013, John McNamara, [email protected] # from xlsxwriter.workbook import Workbook workbook = Workbook('text_indent.xlsx') worksheet = workbook.add_worksheet() indent1 = workbook.add_format({'indent': 1}) indent2 = workbook.add_format({'indent': 2}) worksheet.set_column('A:A', 40) worksheet.write('A1', "This text is indented 1 level", indent1) worksheet.write('A2', "This text is indented 2 levels", indent2) workbook.close()
def queryresultalter():
es = Elasticsearch([{'host': '13.235.1.36', 'port': 9200}])
yesterday = "QueryRan"
# print(yesterday)
startdate = date.today() - timedelta(days=7) - timedelta(hours=5, minutes=30)
startdate = int((time.mktime(startdate.timetuple()))-19800)
print("Startdate :{}".format(startdate))
enddate = date.today()
enddate = int((time.mktime(enddate.timetuple()))-19800)
print("EndDate :{}".format(enddate))
# Make a excel sheet store the headers Only
workbook = Workbook('Data_QueryRan.xlsx')
worksheet = workbook.add_worksheet()
lista = ['appid','deviceid','devicemake','devicemodel','platform','apppackage','keyname','mobileoperator','ssid','app','song','album'
,'pstate','source','ipaddress','city','station','duration','timestamp','created_date','created_time']
r = 0
c = 0
for item in lista:
worksheet.write(r, c, item)
c = c + 1
# Query the elasticsearch cluster
i=0
results = helpers.scan(client = es,
scroll = '2m',
query = {"query": {"range": {"timestamp": {"gte": startdate,"lte": enddate}}}},
index = "tendays")
for item in results:
dataJson = item["_source"]
data_appid = dataJson["app_id"]
data_deviceid = dataJson["device_id"]
data_devicemake = '"'+dataJson["events"]["ma"]+'"'
data_devicemodel = '"'+dataJson["events"]["d"]+'"'
data_platform = '"'+dataJson["events"]["p"]+'"'
data_apppackage = '"'+dataJson["events"]["ap"]+'"'
try:
data_keyname = '"'+dataJson["key"]+'"'
except Exception as e:
data_keyname = ""
data_mobileoperator = '"'+dataJson["events"]["network"]["ope"]+'"'
data_ssid = '"'+dataJson["events"]["network"]["ssid"]+'"'
try:
data_App = '"'+dataJson["events"]["segmentation"]["App"]+'"'
except Exception as e:
data_App = ""
try:
data_Song = '"'+dataJson["events"]["segmentation"]["Song"]+'"'
except Exception as e:
data_Song = ""
try:
data_Album = '"'+dataJson["events"]["segmentation"]["Album"]+'"'
except Exception as e:
data_Album = ""
try:
data_PState = '"'+dataJson["events"]["segmentation"]["PState"]+'"'
except Exception as e:
data_PState = ""
try:
data_Source = '"'+dataJson["events"]["segmentation"]["Source"]+'"'
except Exception as e:
data_Source = ""
data_ipAddress = dataJson["ipAddress"]
data_city = dataJson["city"]
try:
data_station = '"'+dataJson["events"]["segmentation"]["Station"]+'"'
except Exception as e:
data_station = ""
try:
data_duration = '"'+dataJson["events"]["segmentation"]["Duration"]+'"'
except Exception as e:
data_duration = ""
data_timestamp = dataJson["timestamp"]
data_created_date = dataJson["created_date"]
data_created_time = dataJson["created_time"]
lista = [data_appid,data_deviceid,data_devicemake,data_devicemodel,data_platform,data_apppackage,data_keyname,data_mobileoperator,data_ssid,data_App,data_Song,data_Album,data_PState,data_Source,data_ipAddress,data_city,data_station,data_duration,data_timestamp,data_created_date,data_created_time]
r =r + 1
c = 0
for item in lista:
worksheet.write(r, c, item)
c = c + 1
i=i+1
print("Value of i:{}".format(i))
workbook.close()
def do_rebuild(self):
self.env.cr.execute("""
drop view if exists vst_docs_apertura cascade;
create view vst_docs_apertura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
a1.nro_comprobante,
a2.date,
a1.date_maturity,
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END AS moneda,
CASE
WHEN a3.name IS NULL THEN a1.debit - a1.credit
ELSE NULL::numeric
END AS pventamn,
CASE
WHEN a3.name::text = 'USD'::text THEN a1.amount_currency
ELSE NULL::numeric
END AS pventame
FROM account_move_line a1
LEFT JOIN account_move a2 ON a2.id = a1.move_id
LEFT JOIN res_currency a3 ON a3.id = a1.currency_id
LEFT JOIN account_account a4 ON a4.id = a1.account_id
WHERE a2.fecha_special = true AND date_part('year'::text, a2.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision AND date_part('month'::text, a2.date) <> '12'::double precision AND a4.internal_type::text = 'receivable'::text AND a2.state::text = 'posted'::text
)""")
self.env.cr.execute("""
drop view if exists saldos_doc_apertura cascade;
create view saldos_doc_apertura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
a1.nro_comprobante,
a1.date,
a1.date_maturity,
a1.moneda,
a1.pventamn,
a1.pventame,
sum(a2.debit - a2.credit) AS saldomn,
sum(a2.amount_currency) AS saldome,
max(a2.create_uid) AS propietario
FROM vst_docs_apertura a1
LEFT JOIN account_move_line a2 ON concat(a2.account_id, a2.partner_id, a2.type_document_it, a2.nro_comprobante) = concat(a1.account_id, a1.partner_id, a1.type_document_it, a1.nro_comprobante)
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, a1.nro_comprobante, a1.date, a1.date_maturity, a1.moneda, a1.pventamn, a1.pventame
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_apertura cascade;
create view vst_ctasctes_apertura as (
SELECT a1.date AS fecha_emi,
a1.date_maturity AS fecha_ven,
partner.nro_documento,
partner.name AS cliente,
doc.name AS tdoc,
a1.nro_comprobante,
a1.pventamn,
a1.pventame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.pventamn - a1.saldomn
ELSE NULL::numeric
END AS cancelamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a1.pventame - a1.saldome
ELSE NULL::numeric
END AS cancelame,
a1.saldomn,
a1.saldome,
a1.moneda,
a1.propietario
FROM saldos_doc_apertura a1
LEFT JOIN res_partner partner ON partner.id = a1.partner_id
LEFT JOIN einvoice_catalog_01 doc ON doc.id = a1.type_document_it
)""")
self.env.cr.execute("""
drop view if exists vst_saldos_letras cascade;
create view vst_saldos_letras as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
btrim(btrim(a1.nro_comprobante::text), chr(9)) AS nro_comprobante,
min(a1.invoice_id) AS id_factura,
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END AS moneda,
sum(a1.debit) AS debe,
sum(a1.credit) AS haber,
sum(a1.debit - a1.credit) AS saldomn,
sum(a1.amount_currency) AS saldome
FROM account_move_line a1
LEFT JOIN account_account a2 ON a2.id = a1.account_id
LEFT JOIN res_currency a3 ON a3.id = a2.currency_id
LEFT JOIN account_move a4 ON a4.id = a1.move_id
WHERE a2.internal_type::text = 'receivable'::text AND a2.centralized = true AND a4.state::text = 'posted'::text AND date_part('year'::text, a4.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, (btrim(btrim(a1.nro_comprobante::text), chr(9))), (
CASE
WHEN a3.name IS NULL THEN 'PEN'::text
ELSE 'USD'::text
END)
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_letras cascade;
create view vst_ctasctes_letras as (
SELECT a7.fecha_canje AS fecha_emi,
a2.fecha_vencimiento AS fecha_ven,
a3.nro_documento,
a3.name AS cliente,
'LETRA'::text AS tdoc,
a1.nro_comprobante,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.debe
ELSE 0::numeric
END AS pventamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a2.monto_divisa
ELSE 0::numeric
END AS pventame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.haber
ELSE 0::numeric
END AS cancelamn,
CASE
WHEN a1.moneda = 'USD'::text THEN a2.monto_divisa - a1.saldome
ELSE 0::numeric
END AS cancelame,
CASE
WHEN a1.moneda = 'PEN'::text THEN a1.saldomn
ELSE 0::numeric
END AS saldomn,
CASE
WHEN a1.moneda = 'USD'::text THEN a1.saldome
ELSE 0::numeric
END AS saldome,
a1.moneda,
t10.user AS propietario
FROM vst_saldos_letras a1
INNER JOIN account_letras_payment_letra_manual a2 ON a2.nro_letra::text = a1.nro_comprobante
INNER JOIN res_partner a3 ON a3.id = a1.partner_id
INNER JOIN account_letras_payment a7 ON a7.id = a2.letra_payment_id and a7.partner_id = a3.id
LEFT JOIN (select letra_payment_id, max(ai.create_uid) as user from account_invoice ai
inner join account_letras_payment_factura alpf on alpf.invoice_id = ai.id
group by letra_payment_id) t10 on t10.letra_payment_id = a7.id
)""")
self.env.cr.execute("""
drop view if exists vst_saldos_factura cascade;
create view vst_saldos_factura as (
SELECT a1.account_id,
a1.partner_id,
a1.type_document_it,
btrim(btrim(a1.nro_comprobante::text), chr(9)) AS nro_comprobante,
min(a1.invoice_id) AS id_factura,
min(a1.id) AS id_apunte,
sum(a1.debit) AS debe,
sum(a1.credit) AS haber,
sum(a1.debit - a1.credit) AS saldomn,
sum(a1.amount_currency) AS saldome
FROM account_move_line a1
LEFT JOIN account_account a2 ON a2.id = a1.account_id
LEFT JOIN account_move a3 ON a3.id = a1.move_id
WHERE a2.internal_type::text = 'receivable'::text AND a3.state::text = 'posted'::text AND date_part('year'::text, a3.date) = (( SELECT main_parameter.fiscalyear
FROM main_parameter))::double precision AND COALESCE(a2.centralized, false) = false
GROUP BY a1.account_id, a1.partner_id, a1.type_document_it, (btrim(btrim(a1.nro_comprobante::text), chr(9)))
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_facturas cascade;
create view vst_ctasctes_facturas as (
SELECT a2.date_invoice AS fecha_emi,
a2.date_due AS fecha_ven,
a3.nro_documento,
a3.name AS cliente,
a4.name AS tdoc,
a1.nro_comprobante,
CASE
WHEN a6.name::text = 'PEN'::text THEN a2.amount_total_company_signed
ELSE 0::numeric
END AS pventamn,
CASE
WHEN a6.name::text = 'USD'::text THEN a2.amount_total_signed
ELSE 0::numeric
END AS pventame,
CASE
WHEN a6.name::text = 'PEN'::text THEN a1.haber
ELSE 0::numeric
END AS cancelamn,
CASE
WHEN a6.name::text = 'USD'::text THEN a2.amount_total_signed - a1.saldome
ELSE 0::numeric
END AS cancelame,
CASE
WHEN a6.name::text = 'PEN'::text THEN a1.saldomn
ELSE 0::numeric
END AS saldomn,
CASE
WHEN a6.name::text = 'USD'::text THEN a1.saldome
ELSE 0::numeric
END AS saldome,
a6.name AS moneda,
a2.user_id AS propietario
FROM vst_saldos_factura a1
JOIN account_invoice a2 ON a2.id = a1.id_factura
LEFT JOIN res_partner a3 ON a3.id = a1.partner_id
LEFT JOIN einvoice_catalog_01 a4 ON a4.id = a1.type_document_it
LEFT JOIN res_currency a6 ON a6.id = a2.currency_id
)""")
self.env.cr.execute("""
drop view if exists vst_ctasctes_integral cascade;
create view vst_ctasctes_integral as (
SELECT vst_ctasctes_facturas.fecha_emi,
vst_ctasctes_facturas.fecha_ven,
vst_ctasctes_facturas.nro_documento,
vst_ctasctes_facturas.cliente,
vst_ctasctes_facturas.tdoc,
vst_ctasctes_facturas.nro_comprobante,
vst_ctasctes_facturas.pventamn,
vst_ctasctes_facturas.pventame,
vst_ctasctes_facturas.cancelamn,
vst_ctasctes_facturas.cancelame,
vst_ctasctes_facturas.saldomn,
vst_ctasctes_facturas.saldome,
vst_ctasctes_facturas.moneda,
vst_ctasctes_facturas.propietario
FROM vst_ctasctes_facturas
WHERE (vst_ctasctes_facturas.saldomn + vst_ctasctes_facturas.saldome) <> 0::numeric
UNION ALL
SELECT vst_ctasctes_letras.fecha_emi,
vst_ctasctes_letras.fecha_ven,
vst_ctasctes_letras.nro_documento,
vst_ctasctes_letras.cliente,
vst_ctasctes_letras.tdoc,
vst_ctasctes_letras.nro_comprobante,
vst_ctasctes_letras.pventamn,
vst_ctasctes_letras.pventame,
vst_ctasctes_letras.cancelamn,
vst_ctasctes_letras.cancelame,
vst_ctasctes_letras.saldomn,
vst_ctasctes_letras.saldome,
vst_ctasctes_letras.moneda,
vst_ctasctes_letras.propietario
FROM vst_ctasctes_letras
WHERE (vst_ctasctes_letras.saldomn + vst_ctasctes_letras.saldome) <> 0::numeric
UNION ALL
SELECT vst_ctasctes_apertura.fecha_emi,
vst_ctasctes_apertura.fecha_ven,
vst_ctasctes_apertura.nro_documento,
vst_ctasctes_apertura.cliente,
vst_ctasctes_apertura.tdoc,
vst_ctasctes_apertura.nro_comprobante,
vst_ctasctes_apertura.pventamn,
vst_ctasctes_apertura.pventame,
vst_ctasctes_apertura.cancelamn,
vst_ctasctes_apertura.cancelame,
vst_ctasctes_apertura.saldomn,
vst_ctasctes_apertura.saldome,
vst_ctasctes_apertura.moneda,
vst_ctasctes_apertura.propietario
FROM vst_ctasctes_apertura
WHERE (vst_ctasctes_apertura.saldomn + vst_ctasctes_apertura.saldome) <> 0::numeric)
""")
filtro = []
#usuario = self.env['res.users'].browse(self.env.uid)
#permisos = self.env['res.groups'].search([('name','=','Venta:Vendedor Normal')])[0]
#if self.env.uid in permisos.users.ids:
# filtro.append( ('propietario','=',self.env.uid) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Vendedor Coorporativo')])[0]
#if self.env.uid in permisos.users.ids:
# filtro.append( ('propietario','=',self.env.uid) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Jefe de Equipo')])[0]
#if self.env.uid in permisos.users.ids:
# contenedor = [self.env.uid]
# teams = self.env['crm.team'].search([('user_id','=',self.env.uid)])
# for team in teams:
# for i in team.member_ids:
# if i.id not in contenedor:
# contenedor.append(i.id)
# allpartner = self.env['res.users'].search([])
# for i in allpartner:
# if i.partner_id.team_id.id == usuario.partner_id.team_id.id:
# contenedor.append(i.id)
# filtro.append( ('propietario','in',contenedor ) )
#permisos = self.env['res.groups'].search([('name','=','Venta:Gerente')])[0]
#if self.env.uid in permisos.users.ids:
# filtro = []
self.env.cr.execute(
""" DROP VIEW IF EXISTS saldo_comprobante_periodo_propietario;
create or replace view saldo_comprobante_periodo_propietario as (
select row_number() OVER () AS id,* from
(
select * from vst_ctasctes_integral
) T
)
""")
import io
from xlsxwriter.workbook import Workbook
output = io.BytesIO()
########### PRIMERA HOJA DE LA DATA EN TABLA
#workbook = Workbook(output, {'in_memory': True})
direccion = self.env['main.parameter'].search([])[0].dir_create_file
workbook = Workbook(direccion + 'reporteperiodo.xlsx')
worksheet = workbook.add_worksheet("Saldo Comprobantes x Periodo")
#Print Format
worksheet.set_landscape() #Horizontal
worksheet.set_paper(9) #A-4
worksheet.set_margins(left=0.75, right=0.75, top=1, bottom=1)
worksheet.fit_to_pages(1, 0) # Ajustar por Columna
bold = workbook.add_format({'bold': True})
normal = workbook.add_format()
boldbord = workbook.add_format({'bold': True})
boldbord.set_border(style=2)
boldbord.set_align('center')
boldbord.set_align('vcenter')
boldbord.set_text_wrap()
boldbord.set_font_size(9)
boldbord.set_bg_color('#DCE6F1')
numbertres = workbook.add_format({'num_format': '0.000'})
numberdos = workbook.add_format({'num_format': '0.00'})
bord = workbook.add_format()
bord.set_border(style=1)
bord.set_text_wrap()
numberdos.set_border(style=1)
numbertres.set_border(style=1)
title = workbook.add_format({'bold': True})
title.set_align('center')
title.set_align('vcenter')
title.set_text_wrap()
title.set_font_size(20)
worksheet.set_row(0, 30)
x = 4
tam_col = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
tam_letra = 1.2
import sys
reload(sys)
sys.setdefaultencoding('iso-8859-1')
worksheet.merge_range(0, 0, 0, 11, u"Cuenta Corriente a la fecha",
title)
worksheet.write(3, 0, u"Propietario", boldbord)
worksheet.write(3, 1, u"Fecha Emision", boldbord)
worksheet.write(3, 2, u"Fecha Vencimiento", boldbord)
worksheet.write(3, 3, u"Nro Documento", boldbord)
worksheet.write(3, 4, u"Cliente", boldbord)
worksheet.write(3, 5, u"TD", boldbord)
worksheet.write(3, 6, u"Nro Comprobante", boldbord)
worksheet.write(3, 7, u"Precio Venta MN", boldbord)
worksheet.write(3, 8, u"Precio Venta ME", boldbord)
worksheet.write(3, 9, u"Cancela MN", boldbord)
worksheet.write(3, 10, u"Cancela ME", boldbord)
worksheet.write(3, 11, u"Saldo MN", boldbord)
worksheet.write(3, 12, u"Saldo ME", boldbord)
worksheet.write(3, 13, u"Moneda", boldbord)
for line in self.env['saldo.comprobante.periodo.propietario'].search(
filtro):
worksheet.write(
x, 0, line.propietario_name if line.propietario_name else '',
bord)
worksheet.write(x, 1, line.fecha_emi if line.fecha_emi else '',
bord)
worksheet.write(x, 2, line.fecha_ven if line.fecha_ven else '',
bord)
worksheet.write(x, 3,
line.nro_documento if line.nro_documento else '',
bord)
worksheet.write(x, 4, line.cliente if line.cliente else '', bord)
worksheet.write(x, 5, line.tdoc if line.tdoc else '', bord)
worksheet.write(
x, 6, line.nro_comprobante if line.nro_comprobante else '',
bord)
worksheet.write(x, 7, line.pventamn, numberdos)
worksheet.write(x, 8, line.pventame, numberdos)
worksheet.write(x, 9, line.cancelamn, numberdos)
worksheet.write(x, 10, line.cancelame, numberdos)
worksheet.write(x, 11, line.saldomn, numberdos)
worksheet.write(x, 12, line.saldome, numberdos)
worksheet.write(x, 13, line.moneda if line.moneda else '', bord)
x = x + 1
tam_col = [
15, 11, 14, 14, 14, 12, 13, 11, 10, 14, 14, 10, 14, 13, 14, 10, 16,
16, 20, 36
]
worksheet.set_column('A:A', tam_col[0])
worksheet.set_column('B:B', tam_col[1])
worksheet.set_column('C:C', tam_col[2])
worksheet.set_column('D:D', tam_col[3])
worksheet.set_column('E:E', tam_col[4])
worksheet.set_column('F:F', tam_col[5])
worksheet.set_column('G:G', tam_col[6])
worksheet.set_column('H:H', tam_col[7])
worksheet.set_column('I:I', tam_col[8])
worksheet.set_column('J:J', tam_col[9])
worksheet.set_column('K:K', tam_col[10])
worksheet.set_column('L:L', tam_col[11])
worksheet.set_column('M:M', tam_col[12])
worksheet.set_column('N:N', tam_col[13])
worksheet.set_column('O:O', tam_col[14])
worksheet.set_column('P:P', tam_col[15])
worksheet.set_column('Q:Q', tam_col[16])
worksheet.set_column('R:R', tam_col[17])
worksheet.set_column('S:S', tam_col[18])
worksheet.set_column('T:T', tam_col[19])
workbook.close()
f = open(direccion + 'reporteperiodo.xlsx', 'rb')
sfs_obj = self.pool.get('repcontab_base.sunat_file_save')
vals = {
'output_name': 'ReportePeriodo.xlsx',
'output_file': base64.encodestring(''.join(f.readlines())),
}
sfs_id = self.env['export.file.save'].create(vals)
#import os
#os.system('c:\\eSpeak2\\command_line\\espeak.exe -ves-f1 -s 170 -p 100 "Se Realizo La exportación exitosamente Y A EDWARD NO LE GUSTA XDXDXDXDDDDDDDDDDDD" ')
return {
"type": "ir.actions.act_window",
"res_model": "export.file.save",
"views": [[False, "form"]],
"res_id": sfs_id.id,
"target": "new",
}
