Spaces:
Sleeping
Sleeping
File size: 2,761 Bytes
10ec275 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | """Step F: Excel (.xlsx) export -- the zero-risk deliverable.
Columns: Date, Narration, Ref, Debit, Credit, Balance, Category, VoucherType.
Adds a Totals row, freezes the header, and styles the header. CAs can import
this into Tally via Excel-to-Tally utilities, so it's the safety net.
"""
from constants import voucher_type_for
HEADERS = ["Date", "Narration", "Ref", "Debit", "Credit", "Balance",
"Category", "VoucherType", "Flags"]
def write_excel(transactions, path):
"""Write transactions to an .xlsx file at `path`. Returns the path."""
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active
ws.title = "Transactions"
header_fill = PatternFill("solid", fgColor="1A3C6E")
header_font = Font(bold=True, color="FFFFFF")
thin = Side(style="thin", color="CCCCCC")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
# Header row
for col, name in enumerate(HEADERS, start=1):
c = ws.cell(row=1, column=col, value=name)
c.fill = header_fill
c.font = header_font
c.alignment = Alignment(horizontal="center")
c.border = border
total_debit = 0.0
total_credit = 0.0
for r, t in enumerate(transactions, start=2):
debit = t.get("debit")
credit = t.get("credit")
total_debit += debit or 0
total_credit += credit or 0
vtype = t.get("voucher_type") or voucher_type_for(debit, credit, t.get("category"))
flags = "; ".join(t.get("flags") or [])
values = [
t.get("date", ""),
t.get("narration", ""),
t.get("ref_no") or "",
debit,
credit,
t.get("balance"),
t.get("category", ""),
vtype,
flags,
]
for col, v in enumerate(values, start=1):
cell = ws.cell(row=r, column=col, value=v)
cell.border = border
if col in (4, 5, 6): # amount columns
cell.number_format = "#,##0.00"
# Totals row
total_row = len(transactions) + 2
ws.cell(row=total_row, column=1, value="TOTAL").font = Font(bold=True)
td = ws.cell(row=total_row, column=4, value=round(total_debit, 2))
tc = ws.cell(row=total_row, column=5, value=round(total_credit, 2))
for cell in (td, tc):
cell.font = Font(bold=True)
cell.number_format = "#,##0.00"
# Column widths
widths = [12, 52, 12, 14, 14, 16, 28, 12, 30]
for i, w in enumerate(widths, start=1):
ws.column_dimensions[get_column_letter(i)].width = w
ws.freeze_panes = "A2"
wb.save(path)
return path
|