def build_expense_subsection(s, current_row, expense_dict, name):
if name == 'Debt':
debt = True
else:
debt = False
item_counter = 0
s.write(current_row,1, name + ' Expenses',easyxf('borders: bottom thin;'))
current_row += 1
if debt:
debt_payments_rows = {}
for k,v in expense_dict.iteritems():
this_row = current_row+item_counter
if debt:
debt_payments_rows[k] = this_row
s.write(this_row,1, ' '+k)
s.write(this_row,2, float(v))
for x in range(0,12):
s.write(this_row,3+x, Formula(rowcol_to_cell(this_row,2+x)))
item_counter += 1
this_row = current_row + item_counter
s.write(this_row,1, 'Total '+name+' Expenses',easyxf('borders: top thin;'))
if item_counter > 0:
for x in range(0,13):
s.write(this_row,2+x, Formula('sum('+rowcol_to_cell(this_row-item_counter,2+x)+':'+rowcol_to_cell(this_row-1,2+x)+')'),easyxf('borders: top thin;'))
else:
for x in range(0,13):
s.write(this_row,2+x,Formula('0'),easyxf('borders: top thin;'))
if debt:
return (current_row + item_counter + 2), item_counter, debt_payments_rows
else:
return (current_row + item_counter + 2), item_counter
def write_instances(ws, aws_prices):
"Write columns for recording your AWS instances to Excel worksheet."
headers = ('Type', 'Size', 'Num Instances', 'Cost/month (USD)')
for i, header in enumerate(headers):
ws.row(0).write(i, header)
# format prices in dollars
style = xlwt.XFStyle()
style.num_format_str = '"$"#,##0.00_);("$"#,##'
for i, (instance, size, _) in enumerate(aws_prices, start=1):
ws.row(i).write(0, instance)
ws.row(i).write(1, size)
ws.row(i).write(2, 0)
instances_cell = rowcol_to_cell(i, 2)
monthly_price_cell = rowcol_to_cell(i, 3)
formula = "'EC2 Pricing'!%s * %s" % (monthly_price_cell, instances_cell)
ws.row(i).write(3, xlwt.Formula(formula), style)
# calculate total infrastructure cost
num_rows = len(ws.rows)
ws.row(num_rows).write(2, "Total")
first_price_cell = rowcol_to_cell(1, 3)
last_price_cell = rowcol_to_cell(num_rows-1, 3)
total_formula = "SUM(%s:%s)" % (first_price_cell, last_price_cell)
ws.row(num_rows).write(3, xlwt.Formula(total_formula), style)
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
def sum_expenses(s, current_row, exp_start_row, basic_exp_item_counter, debt_exp_item_counter, misc_exp_item_counter):
this_row = current_row
expense_total_row = current_row
s.write(current_row,1, 'Total Expenses',easyxf('font: bold True;'))
total_row_1 = exp_start_row + 3 + basic_exp_item_counter
total_row_2 = total_row_1 + 3 + debt_exp_item_counter
total_row_3 = total_row_2 + 3 + misc_exp_item_counter
for x in range(0,13):
total_formula_string = rowcol_to_cell(total_row_1,2+x) + "+" + rowcol_to_cell(total_row_2,2+x) + "+" + rowcol_to_cell(total_row_3,2+x)
s.write(current_row,2+x,Formula(total_formula_string),easyxf('font: bold True;'))
return (current_row + 3), expense_total_row
def setup_sheet(sheet):
for col in range(20):
for row in range(80):
sheet.write(row, col, rowcol_to_cell(row, col))
sheet.vert_split_pos = 2
sheet.horz_split_pos = 10
sheet.vert_split_first_visible = 5
sheet.horz_split_first_visible = 40
def setup_sheet(sheet):
for col in range(20):
for row in range(80):
sheet.write(row,col,rowcol_to_cell(row,col))
sheet.vert_split_pos = 2
sheet.horz_split_pos = 10
sheet.vert_split_first_visible = 5
sheet.horz_split_first_visible = 40
def get_formulas(template, args, number):
import xlwt.ExcelFormula
from xlwt.Utils import rowcol_to_cell
formulas = []
for n in range(number):
formulas.append(xlwt.Formula(template %
tuple(rowcol_to_cell(i, n + 1) for i in args)
))
return formulas
def build_cash_sub_section(s, current_row, name, rate, balance, net_income_total_row):
s.write(current_row,1, name,easyxf('font: bold True;' 'borders: bottom thin;'))
s.write(current_row,2, 'Return -->',easyxf('font: bold True;'))
s.write(current_row,3, float(rate),easyxf('font: bold True;'))
current_row += 1
checking_row_present = 0
if name == 'Checking':
checking_row_present = 1
s.write(current_row,1, 'Beginning Balance')
for x in range (0,12):
s.write(current_row,3+x,Formula(rowcol_to_cell(current_row+3+checking_row_present,2+x)))
current_row += 1
if checking_row_present:
s.write(current_row,1, ' Net Income')
for x in range (0,12):
s.write(current_row,3+x,Formula(rowcol_to_cell(net_income_total_row,3+x)))
current_row += 1
s.write(current_row,1, ' Less: Withdrawals')
for x in range (0,12):
#s.write(current_row,3+x,Formula('-'+rowcol_to_cell(withdrawal,3+x)))
s.write(current_row,3+x,0)
current_row += 1
s.write(current_row,1, ' Plus: Interest')
for x in range (0,12):
s.write(current_row,3+x,Formula('('+rowcol_to_cell(current_row-3-checking_row_present,3)+'/12)*'+rowcol_to_cell(current_row+1,2+x)))
current_row += 1
s.write(current_row,1, 'Ending Balance',easyxf('borders: top thin;'))
s.write(current_row,2, float(balance),easyxf('borders: top thin;'))
for x in range (0,12):
s.write(current_row,3+x,Formula('sum('+rowcol_to_cell(current_row-3-checking_row_present,3+x)+':'+rowcol_to_cell(current_row-1,3+x)+')'),easyxf('borders: top thin;'))
return (current_row + 2), current_row
def build_income_section(s, current_row, income):
item_counter = 0
s.write(current_row,1, 'Income',easyxf('borders: bottom thick;' 'font: bold True;'))
current_row += 1
for k,v in income.iteritems():
this_row = current_row + item_counter
s.write(this_row,1, ' '+k)
s.write(this_row,2, float(v))
for x in range(0,12):
s.write(this_row,3+x, Formula(rowcol_to_cell(this_row,2+x)))
item_counter += 1
this_row = current_row+item_counter
income_total_row = this_row
s.write(this_row,1, 'Total Income',easyxf('borders: top thin;' 'font: bold True;'))
for x in range(0,13):
s.write(this_row,2+x, Formula('sum('+rowcol_to_cell(this_row-item_counter,2+x)+':'+rowcol_to_cell(this_row-1,2+x)+')'),easyxf('borders: top thin;' 'font: bold True;'))
return (current_row + item_counter + 2), income_total_row
def build_debt_sub_section(s, current_row, name, APR, debt_payments_row, balance):
s.write(current_row,1, name +' Debt',easyxf('font: bold True;' 'borders: bottom thin;'))
s.write(current_row,2, ' APR -->',easyxf('font: bold True;'))
s.write(current_row,3, float(APR),easyxf('font: bold True;'))
current_row += 1
s.write(current_row,1, 'Beginning Balance')
for x in range (0,12):
s.write(current_row,3+x,Formula(rowcol_to_cell(current_row+3,2+x)))
current_row += 1
s.write(current_row,1, ' Less: Payments')
for x in range (0,12):
s.write(current_row,3+x,Formula('-'+rowcol_to_cell(debt_payments_row,3+x)))
current_row += 1
s.write(current_row,1, ' Plus: Interest')
for x in range (0,12):
s.write(current_row,3+x,Formula('('+rowcol_to_cell(current_row-3,3)+'/12)*'+rowcol_to_cell(current_row+1,2+x)))
current_row += 1
s.write(current_row,1, 'Ending Balance',easyxf('borders: top thin;'))
s.write(current_row,2, float(balance),easyxf('borders: top thin;'))
for x in range (0,12):
s.write(current_row,3+x,Formula('sum('+rowcol_to_cell(current_row-3,3+x)+':'+rowcol_to_cell(current_row-1,3+x)+')'),easyxf('borders: top thin;'))
return (current_row + 2), current_row
def write_prices(ws, aws_prices):
"Write AWS prices to an Excel worksheet."
headers = ('Type', 'Size', 'Price/hour (USD)', 'Price/month (USD)')
for i, header in enumerate(headers):
ws.row(0).write(i, header)
# format prices as prices
style = xlwt.XFStyle()
style.num_format_str = '"$"#,##0.00_);("$"#,##'
for i, (instance, size, price) in enumerate(aws_prices, start=1):
ws.row(i).write(0, instance)
ws.row(i).write(1, size)
ws.row(i).write(2, float(price), style=style)
hourly_cell = rowcol_to_cell(i, 2)
hours_in_month = 24 * 30
ws.row(i).write(3, xlwt.Formula("%s * %s" % (hourly_cell, hours_in_month)), style)
def build_cash_summary(s, current_row, cash_balance_rows):
s.write(current_row,1, 'Total Cash/Investment Assets',easyxf('font: bold True;'))
for x in range (0,13):
cash_sum_formula_string = ''
for k,row in cash_balance_rows.iteritems():
cash_sum_formula_string += (rowcol_to_cell(row,2+x)+'+')
s.write(current_row,2+x,Formula(cash_sum_formula_string[:-1]))
"""
current_row += 2
s.write(current_row,1, 'Cash/Investment Summary',easyxf('font: bold True;' 'borders: bottom thin;'))
current_row += 1
s.write(current_row,1, ' Investment Income')
for x in range (1,13):
interest_sum_formula_string = ''
for k,row in cash_balance_rows.iteritems():
interest_sum_formula_string += (rowcol_to_cell(row-1,2+x)+'+')
s.write(current_row,2+x,Formula(interest_sum_formula_string[:-1]))
current_row += 1
s.write(current_row,1, ' Principal Paid')
for x in range (1,13):
principal_sum_formula_string = ''
for k,row in debt_balance_rows.iteritems():
principal_sum_formula_string += '('+rowcol_to_cell(row-2,2+x)+'+'+rowcol_to_cell(row-1,2+x)+') +'
s.write(current_row,2+x,Formula('-('+principal_sum_formula_string[:-1]+')'))
current_row += 1
s.write(current_row,1, 'Total Debt Expense',easyxf('borders: top thin;'))
s.write(current_row,2,style=easyxf('borders: top thin;'))
for x in range (1,13):
s.write(current_row,2+x,Formula('sum('+rowcol_to_cell(current_row-2,2+x)+':'+rowcol_to_cell(current_row-1,2+x)+')'),easyxf('borders: top thin;'))
"""
return current_row +2, current_row
def build_debt_summary(s, current_row, debt_balance_rows):
s.write(current_row,1, 'Total Debt Outstanding',easyxf('font: bold True;'))
for x in range (0,13):
debt_sum_formula_string = ''
for k,row in debt_balance_rows.iteritems():
debt_sum_formula_string += (rowcol_to_cell(row,2+x)+'+')
s.write(current_row,2+x,Formula(debt_sum_formula_string[:-1]))
total_debt_row = current_row
current_row += 2
s.write(current_row,1, 'Debt Expense Summary',easyxf('font: bold True;' 'borders: bottom thin;'))
current_row += 1
s.write(current_row,1, ' Interest Paid')
for x in range (1,13):
interest_sum_formula_string = ''
for k,row in debt_balance_rows.iteritems():
interest_sum_formula_string += (rowcol_to_cell(row-1,2+x)+'+')
s.write(current_row,2+x,Formula(interest_sum_formula_string[:-1]))
current_row += 1
s.write(current_row,1, ' Principal Paid')
for x in range (1,13):
principal_sum_formula_string = ''
for k,row in debt_balance_rows.iteritems():
principal_sum_formula_string += '('+rowcol_to_cell(row-2,2+x)+'+'+rowcol_to_cell(row-1,2+x)+') +'
s.write(current_row,2+x,Formula('-('+principal_sum_formula_string[:-1]+')'))
current_row += 1
s.write(current_row,1, 'Total Debt Expense',easyxf('borders: top thin;'))
s.write(current_row,2,style=easyxf('borders: top thin;'))
for x in range (1,13):
s.write(current_row,2+x,Formula('sum('+rowcol_to_cell(current_row-2,2+x)+':'+rowcol_to_cell(current_row-1,2+x)+')'),easyxf('borders: top thin;'))
return current_row+2, total_debt_row
from xlwt import Workbook
from xlwt.Utils import rowcol_to_cell
w = Workbook()
sheet = w.add_sheet('Freeze')
sheet.panes_frozen = True
sheet.remove_splits = True
sheet.vert_split_pos = 2
sheet.horz_split_pos = 10
sheet.vert_split_first_visible = 5
sheet.horz_split_first_visible = 40
for col in range(20):
for row in range(80):
sheet.write(row,col,rowcol_to_cell(row,col))
w.save('panes.xls')
def calculate_net_worth(s, current_row, total_debt_row, total_cash_row):
s.write(current_row,1, 'Net Worth',easyxf('font: bold True;'))
for x in range (0,13):
s.write(current_row,2+x,Formula(rowcol_to_cell(total_cash_row,2+x)+'-'+rowcol_to_cell(total_debt_row,2+x)),easyxf('font: bold True;'))
return current_row + 2
for i in range(0,10,2):
sheet.row(i).set_style(row)
for i in range(0,10,2):
sheet.col(i).set_style(col)
for i in range(10):
sheet.write(i,i,'YOUPI',cell)
sheet = book.add_sheet('Hiding')
for rowx in range(10):
for colx in range(10):
sheet.write(rowx,colx,rowcol_to_cell(rowx,colx))
for i in range(0,10,2):
sheet.row(i).hidden = True
sheet.col(i).hidden = True
sheet = book.add_sheet('Row height and Column width')
for i in range(10):
sheet.write(0,i,0)
for i in range(10):
sheet.row(i).set_style(easyxf('font:height '+str(200*i)))
sheet.col(i).width = 256*i
book.save('format_rowscols.xls')
from xlwt import Workbook
from xlwt.Utils import rowcol_to_cell
w = Workbook()
sheet = w.add_sheet('Freeze')
sheet.panes_frozen = True
sheet.remove_splits = True
sheet.vert_split_pos = 2
sheet.horz_split_pos = 10
sheet.vert_split_first_visible = 5
sheet.horz_split_first_visible = 40
for col in range(20):
for row in range(80):
sheet.write(row, col, rowcol_to_cell(row, col))
w.save('panes.xls')
def gen_cell(row_count, offset):
return rowcol_to_cell(row=row_count, col=offset, row_abs=True, col_abs=True)
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
def act_getstockreport(self):
temp_dir = tempfile.gettempdir() or '/tmp'
f_name = os.path.join(temp_dir, 'stock_report.xlsx')
workbook = xlsxwriter.Workbook(f_name)
date_format = workbook.add_format({'num_format':'d-m-yyyy',
'align': 'center',
'valign': 'vcenter'})
# Styles
style_header = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter'})
style_data = workbook.add_format({
'border': 1,
'align': 'left'})
style_data2 = workbook.add_format({
'border': 1,
'align': 'center'})
style_data3 = workbook.add_format({
'border': 1,
'align': 'right'})
style_total = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter'})
style_header2 = workbook.add_format({
'bold': 1,
'align': 'center',
'valign': 'vcenter'})
style_header.set_font_size(18)
style_header.set_text_wrap()
style_header.set_bg_color('#d7e4bd')
style_header.set_font_name('Agency FB')
style_header.set_border(style=2)
style_data.set_font_size(12)
style_data.set_text_wrap()
style_data.set_font_name('Agency FB')
style_data2.set_font_size(12)
style_data2.set_font_name('Agency FB')
style_data3.set_font_size(12)
style_data3.set_font_name('Agency FB')
style_total.set_font_size(12)
style_total.set_text_wrap()
style_total.set_border(style=2)
# date_format.set_font_size(12)
# date_format.set_bg_color('#d7e4bd')
# date_format.set_font_name('Agency FB')
# date_format.set_border(style=2)
style_header2.set_font_size(12)
style_header2.set_bg_color('#d7e4bd')
style_header2.set_font_name('Agency FB')
style_header2.set_border(style=2)
worksheet = workbook.add_worksheet('Stock Report')
worksheet.set_column(0, 0, 35)
worksheet.set_column(1, 1, 35)
worksheet.set_column(2, 2, 12)
worksheet.set_column(3, 3, 12)
worksheet.set_column(4, 4, 12)
worksheet.set_column(5, 5, 12)
worksheet.set_column(6, 6, 12)
worksheet.set_column(7, 7, 12)
worksheet.set_row(0, 25)
product_obj = self.env['product.product']
price_prec = self.env['decimal.precision'].precision_get('Product Price')
# Getting locations to fetch report for
locations = self.get_locations()
locations = self.env['stock.location'].browse(list(set(locations.ids)))
locations = locations.sorted(lambda l: l.level)
products = self.product_ids
# products = product_obj.browse([19, 85])
if not products:
products = product_obj.search([])
row, col = 0, 0
worksheet.merge_range(row, col + 1, row, col + 4, "Inventory Report", style_header)
row += 1
worksheet.write(row, col, 'Company', style_header2)
worksheet.write(row + 1, col, self.company_id and self.company_id.name or 'ALL', style_data2)
worksheet.write(row, col + 1, 'Warehouse', style_header2)
warehouse_name = ''
# if self.warehouse_ids:
# for w in self.warehouse_ids:
# warehouse_name = warehouse_name + w.name + ', '
# else:
# warehouse_name = 'ALL'
if self.warehouse_id:
warehouse_name = warehouse_name + self.warehouse_id.name + ', '
else:
warehouse_name = 'ALL'
worksheet.write(row + 1, col + 1, warehouse_name, style_data2)
worksheet.write(row, col + 2, 'Date From', style_header2)
if self.from_date:
worksheet.write_datetime(row + 1, col + 2, self.from_date or ' ', date_format)
worksheet.write(row, col + 3, 'Date To', style_header2)
worksheet.write(row + 1, col + 3, self.to_date or ' ', date_format)
worksheet.write(row, col + 4, ' ', style_header2)
worksheet.write(row, col + 5, ' ', style_header2)
worksheet.write(row, col + 6, ' ', style_header2)
worksheet.write(row, col + 7, ' ', style_header2)
worksheet.write(row, col + 8, ' ', style_header2)
row += 2
col = 0
worksheet.write(row, col, 'Product', style_header2)
worksheet.write(row, col + 1, 'Location', style_header2)
worksheet.write(row, col + 2, 'Opening', style_header2)
worksheet.write(row, col + 3, 'Purchased', style_header2)
worksheet.write(row, col + 4, 'Returned', style_header2)
worksheet.write(row, col + 5, 'Sales', style_header2)
worksheet.write(row, col + 6, 'Internal', style_header2)
worksheet.write(row, col + 7, 'Adjustment', style_header2)
worksheet.write(row, col + 8, 'Production', style_header2)
worksheet.write(row, col + 9, 'Closing', style_header2)
if self.show_valuation:
worksheet.write(row, col + 10, 'Valuation', style_header2)
row += 1
if self.from_date:
print("from date +++++++++++++++++++++", self.from_date)
my_datetime = datetime(self.from_date.year, self.from_date.month, self.from_date.day)
# correct_date_time = my_datetime + timedelta(hours=2, minutes=30)
previous_date = datetime.strptime(
str(my_datetime), DEFAULT_SERVER_DATETIME_FORMAT) - timedelta(days=1)
# correct_date_time = my_datetime + timedelta(hours=2, minutes=30)
# previous_date = self.get_localised_date(previous_date)
print("previous updae", previous_date)
for product in products.sorted(lambda p: p.name):
processed_loc_ids = []
if self.report_by == 'location_wise':
for location in locations:
if location.id in processed_loc_ids:
continue
child_locations = self.get_child_locations(location)
processed_loc_ids += child_locations.ids
col = 0
opening_dict = {}
if self.from_date:
opening_dict = self.get_product_available(product, False, previous_date,
location)
inventory_dict = self.get_product_available(product, self.from_date, self.to_date,
location)
print(inventory_dict)
closing_balance = opening_dict.get('balance', 0.0) + inventory_dict.get('balance', 0.0)
if self.skip_zero_stock and \
float_is_zero(closing_balance, precision_digits=price_prec):
continue
worksheet.write(row, col, product.name_get()[0][1], style_data)
worksheet.write(row, col + 1, location.complete_name, style_data)
worksheet.write(row, col + 2, opening_dict.get('balance', 0.0), style_data3)
worksheet.write(row, col + 3, inventory_dict.get('incoming_purchases', 0.0), style_data3)
worksheet.write(row, col + 4, inventory_dict.get('incoming_returns', 0.0), style_data3)
worksheet.write(row, col + 5, inventory_dict.get('outgoing', 0.0), style_data3)
worksheet.write(row, col + 6, inventory_dict.get('internal', 0.0), style_data3)
worksheet.write(row, col + 7, inventory_dict.get('adjustment', 0.0), style_data3)
worksheet.write(row, col + 8, inventory_dict.get('production', 0.0), style_data3)
worksheet.write(row, col + 9, closing_balance, style_data3)
if self.show_valuation:
product_costing = round(closing_balance * product.standard_price, price_prec)
worksheet.write(row, col + 10, product_costing, style_data3)
row += 1
else:
col = 0
opening_dict = {}
if self.from_date:
opening_dict = self.get_product_available(product, False, previous_date,
locations)
inventory_dict = self.get_product_available(product, self.from_date, self.to_date,
locations)
print(inventory_dict)
closing_balance = opening_dict.get('balance', 0.0) + inventory_dict.get('balance', 0.0)
if self.skip_zero_stock and \
float_is_zero(closing_balance, precision_digits=price_prec):
continue
worksheet.write(row, col, product.name_get()[0][1], style_data)
worksheet.write(row, col + 1, ' ', style_data)
worksheet.write(row, col + 2, opening_dict.get('balance', 0.0), style_data3)
worksheet.write(row, col + 3, inventory_dict.get('incoming_purchases', 0.0), style_data3)
worksheet.write(row, col + 4, inventory_dict.get('incoming_returns', 0.0), style_data3)
worksheet.write(row, col + 5, inventory_dict.get('outgoing', 0.0), style_data3)
worksheet.write(row, col + 6, inventory_dict.get('internal', 0.0), style_data3)
worksheet.write(row, col + 7, inventory_dict.get('adjustment', 0.0), style_data3)
worksheet.write(row, col + 8, inventory_dict.get('production', 0.0), style_data3)
worksheet.write(row, col + 9, closing_balance, style_data3)
if self.show_valuation:
product_costing = round(closing_balance * product.standard_price, price_prec)
worksheet.write(row, col + 10, product_costing, style_data3)
row += 1
# Writing Total Formula
col = 0
worksheet.merge_range(row, col, row, col + 1, "Total", style_total)
col_length = 9
if self.show_valuation:
col_length += 1
for col in range(2, col_length):
amount_start = rowcol_to_cell(4, col)
amount_stop = rowcol_to_cell(row - 1, col)
formula = '=ROUND(SUM(%s:%s), 0)' % (amount_start, amount_stop)
worksheet.write_formula(row, col, formula, style_total)
row += 1
workbook.close()
f = open(f_name, 'rb')
data = f.read()
f.close()
name = "%s.xlsx" % ("StockReport_" + str(self.from_date or '') + '_' + str(self.to_date or ''))
out_wizard = self.env['xlsx.output'].create({'name': name,
'xls_output': base64.encodebytes(data)})
view_id = self.env.ref('gts_stock_xlsx_report.xlsx_output_form').id
return {
'type': 'ir.actions.act_window',
'res_model': 'xlsx.output',
'target': 'new',
'view_mode': 'form',
'res_id': out_wizard.id,
'views': [[view_id, 'form']],
}
def calculate_net_income(s, current_row, income_total_row, expense_total_row):
s.write(current_row,1, 'Net Income',easyxf('font: bold True;'))
for x in range(0,13):
s.write(current_row,2+x,Formula(rowcol_to_cell(income_total_row,2+x)+'-'+rowcol_to_cell(expense_total_row,2+x)),easyxf('font: bold True;'))
return (current_row + 4), current_row
