Dears,
I am building Custom script report in ERPNext V-13.
I am trying to export the built report to excel.
The standard behaviour exports the raw values without formatting to excel.(eg $23,123.00 is exported as 23123)
I need to include the formatting as well in the excel output. ($23,123.00 as $23,123.00)
is there a way out to achieve this.
Hi Gsarunk,
Have you got any solution for this?
Thanks
One way is to define a data field and format the currency field and store it in the data field.
frappe.db.get_value("Currency", "USD", "symbol")}} {{ frappe.format_value(amount, {"fieldtype":"Float"} )
The other way is to use custom excel download using XLSX js utilities like below
We need to write custom code in the client code of the script report
in the on load function of the reportadd a download button and handle the action of the download button.
below is the complete code of the client code. You can take reference of this and build your own. Pay attention to formatting of currency and float fields
frappe.query_reports['BOM Analysis'] = {
"filters": [{
"fieldname":"BOM",
"label": __("BOM/Design"),
"fieldtype": "Link",
"options": "BOM",
"reqd": 1,
},
{
"fieldname":"cbom",
"label": __("Compare BOM/Design"),
"fieldtype": "Link",
"options": "BOM",
},
{ "fieldname":"include_overheads",
"label": __("Include Overheads"),
"fieldtype": "Check",
}
],
"tree": true,
"name_field": "quotation",
"parent_field": "parent_quotation",
"initial_depth": 1,
"formatter": function(value, row, column, data, default_formatter) {
value = default_formatter(value, row, column, data);
if (data && (data.indent == 0.0 || data.indent == 1.0)) {
value = $(`<span>${value}</span>`);
var $value = $(value).css("font-weight", "bold");
value = $value.wrap("<p></p>").parent().html();
}
if (data){
var val1=0, val2=0
switch(column.fieldname){
case "c_amount":
val1 = data.amount;
val2 = data.c_amount;
break;
case "c_qty":
val1 = data.qty;
val2 = data.c_qty;
break;
case "c_rate":
val1 = data.rate;
val2 = data.c_rate;
break;
}
//console.log('before', $(value).text())
if(val1 < val2){
if(val1) {
percent = ((val2-val1)*(100/val1)).toFixed(2)
} else {
percent = 100.00
}
var $value = $(value).css("color", "red");
value = $(`<div style="text-align: right">${value}</div>`);
$value = $(value).text( "(+" + percent + "%)" + $(value).text() );
$value.addClass("text-danger");
value = $value.wrap("<p></p>").parent().html();
} else if(val2 < val1){
if(val1) {
percent = ((val1-val2)*(100/val1)).toFixed(2)
} else {
percent = 100
}
var $value = $(value).css("color", "green");
value = $(`<div>${value}</div>`);
$value = $(value).text("(-" + percent + "%)" + $(value).text() );
$value.addClass("text-success");
$value.addClass("text-right");
value = $value.wrap("<p></p>").parent().html(); }
}
return value;
},
make_export: function () {
var me = frappe.query_report;
me.title = me.report_name;
if (!frappe.model.can_export(me.report_doc.ref_doctype)) {
frappe.msgprint(__("You are not allowed to export this report"));
return false;
}
frappe.prompt(
{
fieldtype: "Select",
label: __("Select File Type"),
fieldname: "file_format_type",
options: "Excel\nCSL",
default: "Excel",
reqd: 1
},
function (data) {
var view_data = frappe.slickgrid_tools.get_view_data(me.columns, me.dataView);
var result = view_data.map(row => row.splice(1));
// to download only visible rows
var visible_idx = view_data.map(row => row[0]).filter(sr_no => sr_no !== 'Sr No');
if (data.file_format_type == "CSV") {
} else if (data.file_format_type == "Excel") {
}
}, __("Export Report: " + me.title), __("Download"));
return false;
},
refresh: function(report) {console.log(frappe.query_report) },
onload: function(report) {
//frappe.query_report.set_filter_value('BOM', frappe.get_prev_route()[1]=='BOM' ? frappe.get_prev_route()[2] : '');
//frappe.query_report.filters[0].value =
//frappe.get_prev_route()[1]=='BOM' ? frappe.get_prev_route()[2] : '' ;
//console.log(frappe.query_report.filters[0].value)
//
report.page.add_inner_button(__("Download"), async function() {
//Set filters for Get List. BOM and Compare BOM
bom_filters = [frappe.query_report.filters[0].value, frappe.query_report.filters[1].value]
frappe.call({
method: 'frappe.client.get_list',
args: {
doctype: 'BOM',
filters: [
['name','in', bom_filters]
],
fields: ['name', 'll_design', 'owner', 'creation', 'modified_by', 'modified']
},
callback: function(r){
var me = frappe.query_report;
me.title = me.report_name;
columnTitles = []
//Set the column titles of excel from the report field labels
me.columns.map((column) => { columnTitles.push(column.label)})
// Load the XLS JS and download the report data as excel
loadJS("https://cdn.jsdelivr.net/gh/gitbrent/xlsx-js-style/dist/xlsx.bundle.js", true);
function loadJS(FILE_URL, async = true) {
let scriptEle = document.createElement("script");
scriptEle.setAttribute("src", FILE_URL);
scriptEle.setAttribute("type", "text/javascript");
scriptEle.setAttribute("async", async);
document.body.appendChild(scriptEle);
// success event
scriptEle.addEventListener("load", () => {
exportXLS(XLSX)
});
// error event
scriptEle.addEventListener("error", (ev) => {
console.log("Error on loading file", ev);
});
//Function to export XLSX
function exportXLS(XLSX) {
rows = me.data.map((row) => {
return getRowData(row)
} )
hv_import = rows.shift()
//Add six empty rows to accomodate the change log
rows.splice(0, 0, []);
rows.splice(0, 0, []);
rows.splice(0, 0, []);
rows.splice(0, 0, []);
rows.splice(0, 0, []);
rows.splice(0, 0, []);
rows.splice(0, 0, []);
// generate worksheet and workbook
const worksheet = XLSX.utils.json_to_sheet(rows);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, ("Design " + r.message[0].ll_design));
//XLSX.utils.book_append_sheet(workbook, worksheet, "Dates");
// fix headers
XLSX.utils.sheet_add_aoa(worksheet, [ columnTitles ], { origin: "A8" });
// calculate column width
add_bom_meta()
getFormatted()
// XLSX.utils.sheet_add_json(worksheet, reportHeader, {header:columnTitles, origin:'A1', skipHeader:false});
XLSX.writeFile(workbook, (r.message[0].ll_design + '.xlsx') );
//Form the row with indents
function getRowData(row) {
rowData = {}
for (let i = 0; i < me.columns.length; i++) {
var TAB
var colname = me.columns[i].fieldname
if(!row[colname]) { row[colname] = ''}
if (i == 0) {
switch (row.indent) {
case 0:
break
TAB = ''
case 1:
TAB = ' '
break
case 2:
TAB = ' '
break
}
rowData[colname] = TAB? TAB + row[colname] : row[colname]
} else {
var colname = me.columns[i].fieldname
rowData[colname] = row[colname]
}
}
return rowData
}
function isBoldRequired(rowIndex) {
if( rowIndex >= 0 && (me.data[rowIndex].indent == 0 || me.data[rowIndex].indent == 1) ){
return true
} else { return false }
}
function applyCommonFormat(colIndex, rowIndex) {
if (getCellEncoded(colIndex, rowIndex)) {
worksheet[getCellEncoded(colIndex, rowIndex)].s = { // set the style for target cell
border: {
right: {
style: "thin",
color: "000000"
},
left: {
style: "thin",
color: "000000"
},
top: {
style: "thin",
color: "000000"
},
bottom: {
style: "thin",
color: "000000"
},
},
font: { bold: isBoldRequired(rowIndex) } ,
};
}
}
function getNumberFormat(colIndex) {
var format
if (colIndex >= 0 ) {
switch (me.columns[colIndex].fieldtype) {
case 'Float':
format = '#,##0.000'
break
case 'Currency':
format = '#,##0.00'
break
}
}
return format
}
function getCellEncoded( colIndex, rowIndex) {
const ref = XLSX.utils.encode_cell({ r: rowIndex + 1, c: colIndex })
if (worksheet[ref] && worksheet[ref].t === 'n') {
return ref
} else {
return null
}
}
function getFormatted() {
/* for (let i = 0; i < me.columns.length; i++) {
getColFormatted(i)
} */
colMaxWidth = []
for (i in worksheet) {
if (typeof(worksheet[i]) != "object") continue;
let cell = XLSX.utils.decode_cell(i);
if(cell.r > 6) {
worksheet[i].s = { // styling for reprot data cells
font: {
name: "arial",
bold: isBoldRequired(cell.r - 7)
},
alignment: {wrapText: false },
border: {
right: {
style: "thin",
color: "000000"
},
left: {
style: "thin",
color: "000000"
},
},
numFmt: getNumberFormat(cell.c)
};
} else {
worksheet[i].s = { // styling for change log
font: {
name: "arial",
},
alignment: {wrapText: false },
};
}
if (cell.r == 0) {
worksheet[i].s = { // styling for change log
font: {
name: "arial",
sz: "24",
},
alignment: {wrapText: false },
};
}
if (cell.c == 0 || cell.c == me.columns.length/2) { // first column and compare first column
worksheet[i].s.alignment.wrapText = true
}
if (cell.r == 7) { // Header
worksheet[i].s.fill = { // background color
patternType: "solid",
fgColor: { rgb: "b2b2b2" },
bgColor: { rgb: "b2b2b2" }
};
worksheet[i].s.border.bottom = { // bottom border
style: "thin",
color: "000000"
};
}
}
function getMaxColumnWidth1(colMaxWidth, cell, wsi) {
console.log(cell, wsi)
if(colMaxWidth[cell.c]) {
colMaxWidth[cell.c].wch = Math.max(colMaxWidth[cell.c].wch, String(wsi.v).length)
} else {
colWidth = { wch: 10 }
colMaxWidth.push(colWidth)
}
return colMaxWidth
}
function getMaxColumnWidth(fieldname) {
// Do not consider 1st row for width
maxWidth = 14
for(let i = 1; i < rows.length; i++) {
maxWidth = Math.max(maxWidth, String(rows[i][fieldname]).length)
}
return { wch: maxWidth }
}
//Build the max width array
me.columns.map((column) => {
//Compute max width of individual columns
colMaxWidth.push(getMaxColumnWidth(column.fieldname))
} )
//Set maximum width of individual columns with colMaxWidth array
worksheet["!cols"] = colMaxWidth
}
function add_bom_meta() {
change_log = []
//Get the index of BOM and CBOM from report filters to get the value
index_bom = r.message.findIndex(x => x.name ===bom_filters[0]);
index_cbom = r.message.findIndex(x => x.name ===bom_filters[1]);
console.log('Frapee Session', frappe)
// change_log.push(change_log_row)
XLSX.utils.sheet_add_aoa(worksheet, [
["Design", r.message[index_bom].ll_design,'USD',('INR ' + hv_import.rate),'', index_cbom >=0 ? r.message[index_cbom]?.ll_design :'', '', '', '', ''],
["Owner", r.message[index_bom].owner ,'','', '', index_cbom >=0 ? r.message[index_cbom].owner: ''],
["Created On", r.message[index_bom]?.creation ,'','','', index_cbom >=0 ? r.message[index_cbom].creation : ''],
["Modified By", r.message[index_bom]?.modified_by ,'','','', index_cbom >=0 ? r.message[index_cbom].modified_by: ''],
["Modified On", r.message[index_bom]?.modified ,'','','', index_cbom >=0 ? r.message[index_cbom].modified: ''] ,
["Downloaded By", frappe.session.user ,'','','', '', ''],
["Downloaded On", frappe.datetime.now_datetime() ,'','','', '', '']
] , { origin: "A1" });
}
} // Function exportXLS
}
} }) // Frappe.dg.get_value
})
}, //On Load
}