Beispiel #1
0
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)
Beispiel #3
0
 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
Beispiel #4
0
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
Beispiel #5
0
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
Beispiel #7
0
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
Beispiel #8
0
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
Beispiel #9
0
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
Beispiel #10
0
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)
Beispiel #12
0
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
Beispiel #13
0
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
Beispiel #14
0
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')
Beispiel #15
0
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')
Beispiel #17
0
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)
Beispiel #19
0
    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
Beispiel #20
0
    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']],
        }
Beispiel #21
0
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