Write to xlsx file becomming a pain

Question

I'm trying to write some data in a excel file using xlsxwriter library but i can't get it work perfectly.I tried a lot to write to the right cells wiht no duplicates ,but no luck.

Here is screen shot of what i want to do :

What i want is HERE

And what i still get is that :

What i achieved is HERE

I can set the values of the total and income right but i can't set the values of the accounts under the income and expense although i don't know how to prevent these duplicates.

Here is what a data example that i want to add :

[[{'account_type': u'sum',
   'balance': -14112.2,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': '',
   'level': 0,
   'name': u'Profit and Loss',
   'type': 'report'},
  {'account_type': u'account_type',
   'balance': 5887.78,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': '',
   'level': 1,
   'name': u'Income',
   'type': 'report'},
  {'account_type': u'other',
   'balance': 5785.0,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': u'Income',
   'level': 4,
   'name': u'200000 Product Sales',
   'type': 'account'},
  {'account_type': u'other',
   'balance': 102.78,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': u'Income',
   'level': 4,
   'name': u'201000 Foreign Exchange Gain',
   'type': 'account'},
  {'account_type': u'account_type',
   'balance': -19999.98,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': '',
   'level': 1,
   'name': u'Expense',
   'type': 'report'},
  {'account_type': u'other',
   'balance': -19999.98,
   'company_id': 1,
   'company_name': u'Company 1',
   'in_type': u'Expenses',
   'level': 4,
   'name': u'211000 Foreign Exchange Loss',
   'type': 'account'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'200 Product Sales'}],
 [{'account_type': u'sum',
   'balance': 5749.99,
   'company_id': 3,
   'company_name': u'Company 2',
   'in_type': '',
   'level': 0,
   'name': u'Profit and Loss',
   'type': 'report'},
  {'account_type': u'account_type',
   'balance': 5749.99,
   'company_id': 3,
   'company_name': u'Company 2',
   'in_type': '',
   'level': 1,
   'name': u'Income',
   'type': 'report'},
  {'account_type': u'other',
   'balance': 5749.99,
   'company_id': 3,
   'company_name': u'Company 2',
   'in_type': u'Income',
   'level': 4,
   'name': u'200 Product Sales',
   'type': 'account'},
  {'account_type': u'account_type',
   'balance': -0.0,
   'company_id': 3,
   'company_name': u'Company 2',
   'in_type': '',
   'level': 1,
   'name': u'Expense',
   'type': 'report'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'200000 Product Sales'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'201000 Foreign Exchange Gain'},
  {'balance': 0,
   'in_type': u'Expenses',
   'level': 4,
   'name': u'211000 Foreign Exchange Loss'}],
 [{'account_type': u'sum',
   'balance': -0.0,
   'company_id': 4,
   'company_name': u'Company 3',
   'in_type': '',
   'level': 0,
   'name': u'Profit and Loss',
   'type': 'report'},
  {'account_type': u'account_type',
   'balance': -0.0,
   'company_id': 4,
   'company_name': u'Company 3',
   'in_type': '',
   'level': 1,
   'name': u'Income',
   'type': 'report'},
  {'account_type': u'account_type',
   'balance': -0.0,
   'company_id': 4,
   'company_name': u'Company 3',
   'in_type': '',
   'level': 1,
   'name': u'Expense',
   'type': 'report'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'200 Product Sales'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'200000 Product Sales'},
  {'balance': 0,
   'in_type': u'Income',
   'level': 4,
   'name': u'201000 Foreign Exchange Gain'},
  {'balance': 0,
   'in_type': u'Expenses',
   'level': 4,
   'name': u'211000 Foreign Exchange Loss'}]]

Here is my python code i tried.I hope i could find some help.

list_or = []
            col_space1_ = 8
            row_space1 = 6
            col_space2 = 8
            col_space3 = 8
            col_space4 = 8
            col_space5 = 8
            account_row = 12
            for record in lines:
                for sub_record in record:
                    if sub_record.get('in_type') == 'Income':
                        list_or.append(1)
            number_of_acc = len(list_or)/len(lines)
            income_co = ((number_of_acc * 2) + 2)
            income_lines = income_co + account_row
            income_lines1 = income_co + account_row
            worksheet.write(row_space1 + 2, 0, "Total", data_cell_format)
            worksheet.write(account_row, 1, "Income", data_cell_format)
            worksheet.write(income_lines, 1, "Expenses", data_cell_format)
            for line in lines:
                for sub_line in line:
                    if sub_line.get('account_type') == 'sum':
                        worksheet.write(row_space1, col_space1_, sub_line['company_name'], data_cell_format)
                        worksheet.write(row_space1 + 2, col_space1_, sub_line['balance'], data_cell_format)
                        col_space1_ = col_space1_ + 3

                    if sub_line.get('name') == 'Income':
                        worksheet.write(12, col_space2, sub_line['balance'], data_cell_format)
                        col_space2 = col_space2 + 3

                    if sub_line.get('name') == 'Expense':
                        worksheet.write(income_lines, col_space3, sub_line['balance'], data_cell_format)
                        col_space3 = col_space3 + 3

                    if sub_line.get('in_type') == 'Income':
                        worksheet.write(account_row, sub_line.get('level') - 2, sub_line['name'], data_cell_format)
                        worksheet.write(account_row, col_space4, sub_line['balance'], data_cell_format)
                        account_row = account_row + 2
                        col_space4 = col_space4 + 3

                    if sub_line.get('in_type') == 'Expenses':
                        worksheet.write(income_lines1, sub_line.get('level') - 2, sub_line['name'], data_cell_format)
                        worksheet.write(income_lines1, col_space5, sub_line['balance'], data_cell_format)
                        income_lines1 = income_lines1 + 2
                        col_space5 = col_space5 + 3

Answer 1

If you're having trouble with the more complex problem, make it simpler! This is a classic problem solving technique, and it's typically useful in solving programming problems as well. Your problem seemed to be with the spacing, so let's just do a simpler spacing for the moment: (P.S., I think you forgot to mention 200000 Product Sales under income, and you mistakenly listed 211000 Foreign Exchange Loss under twice instead):

rows = ["Profit and Loss",
        "Income",
        "201000 Foreign Exchange Gain",
        "200000 Product Sales",  # I think you forgot to mention this one
        "200 Product Sales",
        "Expense",
        "211000 Foreign Exchange Loss"]

# Next, sort each sublist in the order we desire
for sublist in lines:
    sublist.sort(key=lambda x: rows.index(str(x["name"])))

import numpy as np    
x = np.dstack((line for line in lines))

# Create file (Change this to your desired output path)
f = open("C:\Users\Matthew\Desktop\stack_simple_spacing.xls", 'w')

# Write out first row
companies = [item[0]["company_name"] for item in lines]
company_header_string = "".join("," + str(company) for company in companies)
f.write(company_header_string + "\n")

# Write out the rest of the rows (they're already sorted in the order desired)
for row in x[0]:
    output = row[0]["name"] + ","
    for item in row:
        output += str(item["balance"]) + ","
    f.write(output + "\n")

f.close()

Here's the output:

If you really want the spacing, you'll have to add complexity to your code, making it harder to read/understand. But it's a lot easier to modify our existing code to simply include the spacing (as opposed trying to do it all in one shebang):

rows = ["Profit and Loss",
        "Income",
        "201000 Foreign Exchange Gain",
        "200000 Product Sales", # I think you forgot to mention this one
        "200 Product Sales",
        "Expense",
        "211000 Foreign Exchange Loss"]

indents = [0, 1, 2, 2, 2, 1, 2]

# Next, sort each sublist in the order we desire
for sublist in lines:
    sublist.sort(key=lambda x: rows.index(str(x["name"])))

# Stack data so each tuple has data for a row
import numpy as np
x = np.dstack((line for line in lines))

# Create file (Change this to your desired output path)
f = open("C:\Users\Matthew\Desktop\stack_fancy_spacing.xls", 'w')

# Write out first row
companies = [item[0]["company_name"] for item in lines]
company_header_string = (","*8) + "".join(str(company) + ",,," for company in companies)
f.write(company_header_string + "\n")

# Write out the rest of the rows
for index, row in enumerate(x[0]):
    output = ","*indents[index] + row[0]["name"] + ","*(8 - indents[index])
    for item in row:
        output += str(item["balance"]) + ",,,"
    f.write(output + "\n")

f.close()

Here's the output:

Explaination: Excel will interpret csv (comma seperated value) files perfectly fine. Each comma is a delimiter between items in a row. The newline character "\n" signals the start of a new row. Hence, when you see ",,,", excel interprets this as two empty cells in a row (note this literal is in the logic for computing company_header_string). You can make the company columns appear close together by editing the 8 in the code (make it 7 or 6 if you want these columns closer to the edge). No need for the xlswritter library for this task.