Excel Formatting Using Python - ashish-greycube/help GitHub Wiki

Create and Download Excel file on click of button

• Create custom button using js

frm.add_custom_button(
    __("Bulk TRFR"),
    () => {
      let file_name="SBI-"+frm.doc.name+".xlsx"
      return frappe.call({
          method: "kict.api.get_sbi_account_details",
          args: {
            docname:frm.doc.name,
            file_name:file_name
          },
          callback: function (r) {
            console.log(r.message)
            function downloadURI(uri, name) 
            {
                var link = document.createElement("a");
                // If you don't know the name or want to use
                // the webserver default set name = ''
                link.setAttribute('download', name);
                link.href = uri;
                document.body.appendChild(link);
                link.click();
                link.remove();
            }                    
            downloadURI(r.message,"SBI-"+frm.doc.name)
            console.log("22")
            frappe.call({
              method: "kict.api.delete_file",
              args: {
                file_name:file_name
              },
              callback: function (r) {
                console.log(r.message)
              }
            })
            }
        })
    },
  __("Download CSV")
);

• Collect data and create new excel file using python library openpyxl

1. import library

import openpyxl
from openpyxl.styles import Font, Alignment
from openpyxl.styles import Border, Side
import os

2. Create new file with data

def get_sbi_account_details(docname,file_name):
    
    file_header = [
        
            "IFSC CODE",
            "AMOUNT",
            "BENEFICIARY AC NO",
            "BENFICIARY NAME",
            "BENEFICIARY ADDRESS"
        
    ]
    print(file_header,"file_header")

    po_data = frappe.db.sql("""
                    SELECT
                        ba.branch_code,
                        por.amount,
                        ba.bank_account_no,
                        por.supplier,
                        ad.city
                    FROM
                        `tabPayment Order Reference` as por
                    LEFT OUTER JOIN `tabBank Account` as ba on
                        por.bank_account = ba.name
                    LEFT OUTER JOIN `tabDynamic Link` as dl on
                        ba.bank = dl.link_name
                    LEFT OUTER JOIN `tabAddress` as ad on
                        ad.name = dl.parent
                    WHERE ba.bank = 'State Bank of India' and por.parent = '{0}'
                """.format(docname),as_dict = 1)
    print(po_data,"po_data",type(po_data))

    total_amount = 0
    if len(po_data)>0:
        for row in po_data:
            total_amount = total_amount + row.amount

    file_footer = [
        "Total Amount",
        total_amount,
        "",
        "",
        ""
    ]
    
# xlsx file creation

    public_file_path = frappe.get_site_path("public", "files")
    workbook = openpyxl.Workbook(write_only=True)
    # file_name=f"SBI-{docname}.xlsx"
    file_url=os.path.join(public_file_path,file_name)

    sheet = workbook.create_sheet("SBI", 0)

    sheet.append(file_header)
    for ele in po_data:
        sheet.append([ele.branch_code,ele.amount,ele.bank_account_no,ele.supplier,ele.city])
    sheet.append(file_footer)

    workbook.save(file_url)

3. Excel Formatting

# excel fromatting

    workBook = openpyxl.load_workbook(file_url)
    workSheet = workBook.active

    for i in range(1, workSheet.max_column + 1):
        workSheet.cell(1, i).font = Font(bold=True, size=12, name="Calibri")
        workSheet.cell(workSheet.max_row, i).font = Font(bold=True, size=10, name="Calibri")
        workSheet.row_dimensions[1].height = 20
        workSheet.column_dimensions['A'].width = 20
        workSheet.column_dimensions['B'].width = 20
        workSheet.column_dimensions['C'].width = 35
        workSheet.column_dimensions['D'].width = 35
        workSheet.column_dimensions['E'].width = 30

    border_thin = Side(style='thin')
    for i in range (1, workSheet.max_row + 1):
        for j in range(1, workSheet.max_column + 1):
            workSheet.cell(i, j).alignment = Alignment(horizontal="center", vertical="center")
            workSheet.cell(i, j).border = Border(top=border_thin, left=border_thin, right=border_thin, bottom=border_thin)

    workBook.save(file_url)
    return frappe.utils.get_url()+"/files/"+file_name

refer below links:

https://github.com/ashish-greycube/KICT/blob/main/kict/public/js/payment_order.js https://github.com/ashish-greycube/KICT/blob/main/kict/api.py#L890