Data Import Export through Excel using Python - ashish-greycube/help GitHub Wiki

Download Formatted Excel

in .py

from frappe.utils.xlsxutils import build_xlsx_response

@frappe.whitelist()
def download_formatted_excel(name=None):
    doc = frappe.get_doc("BOM Uploader MW", name) if name else None
    print("Downloading formatted Excel file...")
    data = [
        ["", "", "Dam code", doc.gg_name],
        ["", "", "ORDER NO", doc.order_no],
        ["", "", "CLIENT", doc.client],
        ["", "", "PROJECT", doc.project],
        ["", "", "WT (kg)", doc.total_weight],
        [],
        [
            "Row No",
            "Parent FG",
            "BOM Item Code",
            "SR NO",
            "DESCRIPTION",
            "LENGTH",
            "WIDTH",
            "OD",
            "ID",
            "THK",
            "MATERIAL",
            "TYPE",
            "QTY",
            "GAD/,MFG",
            "CUTTING",
            "BENDING",
            "ASSEMBLY",
        ],
    ]
    return build_xlsx_response(data, "BOM_Uploader_MW.xlsx")

in .js

frappe.ui.form.on("BOM Uploader MW", {
	download_formatted_excel(frm) {
        open_url_post(
		       "/api/method/mech.mech.doctype.bom_uploader_mw.bom_uploader_mw.download_formatted_excel",
			{
                        name: frm.doc.name,
			}
		    );
	},
});

Read Excel

from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file

@frappe.whitelist()
def read_excel(self):
    file_doc = frappe.get_doc("File", {"file_url": self.import_excel})
    data = read_xlsx_file_from_attached_file(fcontent=file_doc.get_content())
    print(data, "--")

create new excel from existing excel template & add data

@frappe.whitelist()
def download_formatted_excel(name=None):

	doc = frappe.get_doc("BOM Uploader MW", name) if name else None
	print("Downloading formatted Excel file...")

	file_name = frappe.db.get_value("File", {"file_url": doc.excel_1}, 'file_name') ### get excel template
	public_file_path = frappe.get_site_path("public", "files")	
	file_url = os.path.join(public_file_path,file_name)

	wb2 = openpyxl.load_workbook(file_url)

	wb = xl_copy(wb2)
	sheet = wb.active
	sheet['D1'].value = doc.dam_code or ''
	sheet['D2'].value = doc.order_no or ''
	sheet['D3'].value = doc.client or ''
	sheet['D4'].value = doc.project or ''
	sheet['D5'].value = doc.total_weight or ''

	xlsx_file = BytesIO()
	wb.save(xlsx_file)

	provide_binary_file(doc.name, 'xlsx', xlsx_file.getvalue())

create new excel using openpyxl

@frappe.whitelist()
def download_formatted_excel(name=None):
	doc = frappe.get_doc("BOM Uploader MW", name) if name else None
	print("Downloading formatted Excel file...")

	workbook = Workbook()
	sheet = workbook.active

	rows_data = [
		["", "", "Dam code", doc.dam_code],
		["", "", "Order No", doc.order_no],
		["", "", "Client", doc.client],
		["", "", "Project", doc.project],
		["", "", "Wt(kg)", doc.total_weight],
		["Instruction : Please input data from row no 9. Donot put blank rows while data input"],
		[],
		TABLE_HEADERS,
	]

	for row in rows_data:
		sheet.append(row)

	sheet.column_dimensions['A'].width = 10
	sheet.column_dimensions['B'].width = 10
	sheet.column_dimensions['C'].width = 15
	sheet.column_dimensions['D'].width = 10
	sheet.column_dimensions['E'].width = 20
	sheet.column_dimensions['F'].width = 10
	sheet.column_dimensions['G'].width = 10
	sheet.column_dimensions['H'].width = 10
	sheet.column_dimensions['I'].width = 10
	sheet.column_dimensions['J'].width = 10
	sheet.column_dimensions['K'].width = 15
	sheet.column_dimensions['L'].width = 10
	sheet.column_dimensions['M'].width = 10

	bg_fill = PatternFill(fill_type='solid', start_color='FF474C', end_color='FF474C')

	cells_to_style = ['A8', 'B8', 'D8','E8', 'K8', 'L8', 'M8']
	for cell_coord in cells_to_style:
		cell = sheet[cell_coord]
		cell.fill = bg_fill

	xlsx_file = BytesIO()
	workbook.save(xlsx_file)

	provide_binary_file(doc.name, 'xlsx', xlsx_file.getvalue())