def download_ticts_loading_list(**args): args = frappe._dict(args) data = [['Test 5', 'Test 6', args.args], ['Test 7', 'Test 8']] sheet_name = "Loading List" wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet(sheet_name, 0) row1 = ws.row_dimensions[1] row1.font = Font(name='Calibri', bold=True) #data = [['Test 5', 'Test 6'], ['Test 7', 'Test 8']] for row in data: clean_row = [] for item in row: if isinstance(item, basestring) and sheet_name != "Data Import Template": value = handle_html(item) else: value = item clean_row.append(value) ws.append(clean_row) xlsx_file = StringIO() wb.save(xlsx_file) frappe.response['filename'] = sheet_name + '.xlsx' frappe.response['filecontent'] = xlsx_file.getvalue() frappe.response['type'] = 'download'
def write_xlsx(data, sheet_name, wb=None, column_widths=None, file_path=None): # from xlsx utils with changes column_widths = column_widths or [] if wb is None: wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet(sheet_name, 0) for i, column_width in enumerate(column_widths): if column_width: ws.column_dimensions[get_column_letter(i + 1)].width = column_width row1 = ws.row_dimensions[1] row1.font = Font(name='Calibri', bold=True) for row in data: clean_row = [] for item in row: if isinstance(item, string_types) and (sheet_name not in ['Data Import Template', 'Data Export']): value = handle_html(item) else: value = item if isinstance(item, string_types) and next(ILLEGAL_CHARACTERS_RE.finditer(value), None): # Remove illegal characters from the string value = re.sub(ILLEGAL_CHARACTERS_RE, '', value) clean_row.append(value) ws.append(clean_row) wb.save(file_path) return True
def export_query(): """export from report builder""" form_params = get_form_params() form_params["limit_page_length"] = None form_params["as_list"] = True doctype = form_params.doctype add_totals_row = None file_format_type = form_params["file_format_type"] del form_params["doctype"] del form_params["file_format_type"] if 'add_totals_row' in form_params and form_params['add_totals_row'] == '1': add_totals_row = 1 del form_params["add_totals_row"] frappe.permissions.can_export(doctype, raise_exception=True) if 'selected_items' in form_params: si = json.loads(frappe.form_dict.get('selected_items')) form_params["filters"] = {"name": ("in", si)} del form_params["selected_items"] db_query = DatabaseQuery(doctype) ret = db_query.execute(**form_params) if add_totals_row: ret = append_totals_row(ret) data = [['Sr'] + get_labels(db_query.fields, doctype)] for i, row in enumerate(ret): data.append([i + 1] + list(row)) if file_format_type == "CSV": # convert to csv import csv from frappe.utils.xlsxutils import handle_html f = StringIO() writer = csv.writer(f) for r in data: # encode only unicode type strings and not int, floats etc. writer.writerow([handle_html(frappe.as_unicode(v)).encode('utf-8') \ if isinstance(v, string_types) else v for v in r]) f.seek(0) frappe.response['result'] = text_type(f.read(), 'utf-8') frappe.response['type'] = 'csv' frappe.response['doctype'] = doctype elif file_format_type == "Excel": from frappe.utils.xlsxutils import make_xlsx xlsx_file = make_xlsx(data, doctype) frappe.response['filename'] = doctype + '.xlsx' frappe.response['filecontent'] = xlsx_file.getvalue() frappe.response['type'] = 'binary'
def export_query(): """export from report builder""" form_params = get_form_params() form_params["limit_page_length"] = None form_params["as_list"] = True doctype = form_params.doctype add_totals_row = None file_format_type = form_params["file_format_type"] del form_params["doctype"] del form_params["file_format_type"] if 'add_totals_row' in form_params and form_params['add_totals_row']=='1': add_totals_row = 1 del form_params["add_totals_row"] frappe.permissions.can_export(doctype, raise_exception=True) if 'selected_items' in form_params: si = json.loads(frappe.form_dict.get('selected_items')) form_params["filters"] = {"name": ("in", si)} del form_params["selected_items"] db_query = DatabaseQuery(doctype) ret = db_query.execute(**form_params) if add_totals_row: ret = append_totals_row(ret) data = [['Sr'] + get_labels(db_query.fields, doctype)] for i, row in enumerate(ret): data.append([i+1] + list(row)) if file_format_type == "CSV": # convert to csv import csv from frappe.utils.xlsxutils import handle_html f = StringIO() writer = csv.writer(f) for r in data: # encode only unicode type strings and not int, floats etc. writer.writerow([handle_html(frappe.as_unicode(v)).encode('utf-8') \ if isinstance(v, string_types) else v for v in r]) f.seek(0) frappe.response['result'] = text_type(f.read(), 'utf-8') frappe.response['type'] = 'csv' frappe.response['doctype'] = doctype elif file_format_type == "Excel": from frappe.utils.xlsxutils import make_xlsx xlsx_file = make_xlsx(data, doctype) frappe.response['filename'] = doctype + '.xlsx' frappe.response['filecontent'] = xlsx_file.getvalue() frappe.response['type'] = 'binary'
def get_tax_accounts(item_list, columns, company_currency, doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"): import json item_row_map = {} tax_columns = [] invoice_item_row = {} itemised_tax = {} tax_amount_precision = get_field_precision( frappe.get_meta(tax_doctype).get_field("tax_amount"), currency=company_currency) or 2 for d in item_list: invoice_item_row.setdefault(d.parent, []).append(d) item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d) conditions = "" if doctype == "Purchase Invoice": conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0" deducted_tax = get_deducted_taxes() tax_details = frappe.db.sql( """ select name, parent, description, item_wise_tax_detail, charge_type, base_tax_amount_after_discount_amount from `tab%s` where parenttype = %s and docstatus = 1 and (description is not null and description != '') and parent in (%s) %s order by description """ % (tax_doctype, '%s', ', '.join( ['%s'] * len(invoice_item_row)), conditions), tuple([doctype] + list(invoice_item_row))) for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details: description = handle_html(description) if description not in tax_columns and tax_amount: # as description is text editor earlier and markup can break the column convention in reports tax_columns.append(description) if item_wise_tax_detail: try: item_wise_tax_detail = json.loads(item_wise_tax_detail) for item_code, tax_data in item_wise_tax_detail.items(): itemised_tax.setdefault(item_code, frappe._dict()) if isinstance(tax_data, list): tax_rate, tax_amount = tax_data else: tax_rate = tax_data tax_amount = 0 if charge_type == "Actual" and not tax_rate: tax_rate = "NA" item_net_amount = sum([ flt(d.base_net_amount) for d in item_row_map.get( parent, {}).get(item_code, []) ]) for d in item_row_map.get(parent, {}).get(item_code, []): item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \ if item_net_amount else 0 if item_tax_amount: tax_amount = flt(item_tax_amount, tax_amount_precision) tax_amount = (tax_amount * -1 if (doctype == 'Purchase Invoice' and name in deducted_tax) else tax_amount) itemised_tax.setdefault( d.name, {})[description] = frappe._dict({ "tax_rate": tax_rate, "tax_amount": tax_amount }) except ValueError: continue elif charge_type == "Actual" and tax_amount: for d in invoice_item_row.get(parent, []): itemised_tax.setdefault( d.name, {})[description] = frappe._dict({ "tax_rate": "NA", "tax_amount": flt((tax_amount * d.base_net_amount) / d.base_net_total, tax_amount_precision) }) tax_columns.sort() for desc in tax_columns: columns.append(desc + " Rate:Data:80") columns.append(desc + " Amount:Currency/currency:100") columns += [ "Total Tax:Currency/currency:80", "Total:Currency/currency:100" ] return itemised_tax, tax_columns
def test_unescape(self): from frappe.utils.xlsxutils import handle_html val = handle_html("<p>html data ></p>") self.assertIn("html data >", val) self.assertEqual("abc", handle_html("abc"))
def export_query(): """export from report builder""" title = frappe.form_dict.title frappe.form_dict.pop("title", None) form_params = get_form_params() form_params["limit_page_length"] = None form_params["as_list"] = True doctype = form_params.doctype add_totals_row = None file_format_type = form_params["file_format_type"] title = title or doctype del form_params["doctype"] del form_params["file_format_type"] if "add_totals_row" in form_params and form_params["add_totals_row"] == "1": add_totals_row = 1 del form_params["add_totals_row"] frappe.permissions.can_export(doctype, raise_exception=True) if "selected_items" in form_params: si = json.loads(frappe.form_dict.get("selected_items")) form_params["filters"] = {"name": ("in", si)} del form_params["selected_items"] make_access_log( doctype=doctype, file_type=file_format_type, report_name=form_params.report_name, filters=form_params.filters, ) db_query = DatabaseQuery(doctype) ret = db_query.execute(**form_params) if add_totals_row: ret = append_totals_row(ret) data = [["Sr"] + get_labels(db_query.fields, doctype)] for i, row in enumerate(ret): data.append([i + 1] + list(row)) data = handle_duration_fieldtype_values(doctype, data, db_query.fields) if file_format_type == "CSV": # convert to csv import csv from frappe.utils.xlsxutils import handle_html f = StringIO() writer = csv.writer(f) for r in data: # encode only unicode type strings and not int, floats etc. writer.writerow([ handle_html(frappe.as_unicode(v)) if isinstance( v, string_types) else v for v in r ]) f.seek(0) frappe.response["result"] = cstr(f.read()) frappe.response["type"] = "csv" frappe.response["doctype"] = title elif file_format_type == "Excel": from frappe.utils.xlsxutils import make_xlsx xlsx_file = make_xlsx(data, doctype) frappe.response["filename"] = title + ".xlsx" frappe.response["filecontent"] = xlsx_file.getvalue() frappe.response["type"] = "binary"
def get_tax_accounts(item_list, columns, company_currency, doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"): import json item_row_map = {} tax_columns = [] invoice_item_row = {} itemised_tax = {} tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"), currency=company_currency) or 2 for d in item_list: invoice_item_row.setdefault(d.parent, []).append(d) item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d) conditions = "" if doctype == "Purchase Invoice": conditions = " and category in ('Total', 'Valuation and Total') and base_tax_amount_after_discount_amount != 0" deducted_tax = get_deducted_taxes() tax_details = frappe.db.sql(""" select name, parent, description, item_wise_tax_detail, charge_type, base_tax_amount_after_discount_amount from `tab%s` where parenttype = %s and docstatus = 1 and (description is not null and description != '') and parent in (%s) %s order by description """ % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions), tuple([doctype] + invoice_item_row.keys())) for name, parent, description, item_wise_tax_detail, charge_type, tax_amount in tax_details: description = handle_html(description) if description not in tax_columns and tax_amount: # as description is text editor earlier and markup can break the column convention in reports tax_columns.append(description) if item_wise_tax_detail: try: item_wise_tax_detail = json.loads(item_wise_tax_detail) for item_code, tax_data in item_wise_tax_detail.items(): itemised_tax.setdefault(item_code, frappe._dict()) if isinstance(tax_data, list): tax_rate, tax_amount = tax_data else: tax_rate = tax_data tax_amount = 0 if charge_type == "Actual" and not tax_rate: tax_rate = "NA" item_net_amount = sum([flt(d.base_net_amount) for d in item_row_map.get(parent, {}).get(item_code, [])]) for d in item_row_map.get(parent, {}).get(item_code, []): item_tax_amount = flt((tax_amount * d.base_net_amount) / item_net_amount) \ if item_net_amount else 0 if item_tax_amount: tax_amount = flt(item_tax_amount, tax_amount_precision) tax_amount = (tax_amount * -1 if (doctype == 'Purchase Invoice' and name in deducted_tax) else tax_amount) itemised_tax.setdefault(d.name, {})[description] = frappe._dict({ "tax_rate": tax_rate, "tax_amount": tax_amount }) except ValueError: continue elif charge_type == "Actual" and tax_amount: for d in invoice_item_row.get(parent, []): itemised_tax.setdefault(d.name, {})[description] = frappe._dict({ "tax_rate": "NA", "tax_amount": flt((tax_amount * d.base_net_amount) / d.base_net_total, tax_amount_precision) }) tax_columns.sort() for desc in tax_columns: columns.append(desc + " Rate:Data:80") columns.append(desc + " Amount:Currency/currency:100") columns += ["Total Tax:Currency/currency:80", "Total:Currency/currency:100"] return itemised_tax, tax_columns
def get_tax_accounts(item_list, columns, company_currency, doctype="Sales Invoice", tax_doctype="Sales Taxes and Charges"): import json item_row_map = {} tax_columns = [] invoice_item_row = {} itemised_tax = {} conditions = "" tax_amount_precision = get_field_precision(frappe.get_meta(tax_doctype).get_field("tax_amount"), currency=company_currency) or 2 for d in item_list: invoice_item_row.setdefault(d.parent, []).append(d) item_row_map.setdefault(d.parent, {}).setdefault(d.item_code or d.item_name, []).append(d) tax_details = frappe.db.sql(""" select parent, description, item_wise_tax_detail, base_tax_amount_after_discount_amount from `tab%s` where parenttype = %s and docstatus = 1 and (description is not null and description != '') and parent in (%s) %s order by description """ % (tax_doctype, '%s', ', '.join(['%s']*len(invoice_item_row)), conditions), tuple([doctype] + list(invoice_item_row))) for parent, description, item_wise_tax_detail, tax_amount in tax_details: description = handle_html(description) if description not in tax_columns and tax_amount: # as description is text editor earlier and markup can break the column convention in reports tax_columns.append(description) if item_wise_tax_detail: try: item_wise_tax_detail = json.loads(item_wise_tax_detail) for item_code, tax_data in item_wise_tax_detail.items(): if not frappe.db.get_value("Item", item_code, "gst_hsn_code"): continue itemised_tax.setdefault(item_code, frappe._dict()) if isinstance(tax_data, list): tax_amount = tax_data[1] else: tax_amount = 0 for d in item_row_map.get(parent, {}).get(item_code, []): item_tax_amount = tax_amount if item_tax_amount: itemised_tax.setdefault(d.name, {})[description] = frappe._dict({ "tax_amount": flt(item_tax_amount, tax_amount_precision) }) except ValueError: continue tax_columns.sort() for desc in tax_columns: columns.append(desc + " Amount:Currency/currency:160") # columns += ["Total Amount:Currency/currency:110"] return itemised_tax, tax_columns
def make_xlsx(data, report_name): wb = openpyxl.Workbook() ws = wb.create_sheet('All', 0) wb.guess_types = True customers = [] ws.append(['']) ws.append(['']) ws.append(['']) ws.append(['']) ws.append(['']) ws.append(['']) ws.append(['']) for row in data: clean_row = [] if row[1] and row[1] not in customers and str( row[1]).upper() not in ['CUSTOMER', 'CLIENT']: customers.append(row[1]) for item in row: if isinstance( item, string_types) and report_name != "Data Import Template": value = item else: value = item clean_row.append(value) ws.append(clean_row) ws = style_export(ws, report_name) del data[0][1] #Remove the customer heading #Create a sheet for each customer if report_name not in ['Workshop Report', 'Workshop Daily Report']: for customer in customers: clean_title = customer.replace('\\', '') clean_title = clean_title.replace('/', '') wsc = wb.create_sheet(clean_title) wsc.append(['']) wsc.append(['']) wsc.append(['']) wsc.append(['']) wsc.append(['']) wsc.append(['']) wsc.append(['']) wsc.append(data[0]) for row in data: row = list(row) if isinstance(row[1], string_types) and handle_html( row[1]) == customer: clean_row = [] del row[1] for item in row: if isinstance( item, string_types ) and report_name != "Data Import Template": value = item else: value = item clean_row.append(value) wsc.append(clean_row) wsc = style_export(wsc, report_name) xlsx_file = StringIO() wb.remove_sheet(wb.get_sheet_by_name('Sheet')) wb.save(xlsx_file) return xlsx_file