Hello community,
We have a script report and need to generate csv file based on data shown on it. Currently we use Export > CSV menu built-in of frappe. The problem is that we want to modify some parts of csv (e.g. removing column headers on first row, adding quotation marks on integer field). Is it possible to do that ?
Thank you.
I have done this dirty trick that you can use:
frappe.query_reports["Report Name"] = {
"tree": true,
"filters": [
// ...
//Some filters
// ...
],
"formatter": function(value, row, column, data, default_formatter) {
// ...
//Some format
// ...
value = default_formatter(value, row, column, data);
return value;
},
get_data_for_csv: function(with_indentation = false, filters) {
filters.file_format_type = "CSV"
var column_headers = ["COLUMN1", "COLUMN2", "COLUMN3", "COLUMN4"]
var result = []
result.push(column_headers)
frappe.call({
method: "path.to.report.execute",
args: {
filters: filters
},
async: false,
callback: function(r, rt) {
if (r.message) {
for(var i in r.message[1]){
result.push([r.message[1][i].DATA_FIELD1, r.message[1][i].DATA_FIELD2, r.message[1][i].DATA_FIELD4, r.message[1][i].DATA_FIELD5)
}
}
}
})
return result
}
}
if (!frappe.query_report.old_get_data_for_csv) {
frappe.query_report.old_get_data_for_csv = frappe.query_report.get_data_for_csv
}
frappe.query_report.get_data_for_csv = function(with_indentation = false){
if(this.report_name == "Report Name"){
return frappe.query_reports["Report Name"].get_data_for_csv(with_indentation, this.get_filter_values(true))
}
else{
return frappe.query_report.old_get_data_for_csv(with_indentation)
}
}
It’s not nice, but it works.
You can execute your own function in place of the report execute function or manage the file_format_type filter in the function to know that it is for the CSV.
Thank you. I tried your solution but it didn’t work (I’m on v10 branch). But I found this on query_report.js
Inspired by your trick, I copied that function to js file of my report like this:
frappe.query_reports["Report Name"] = {
"filters": [
// ...
//Some filters
// ...
],
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\nCSV",
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") {
// ...
//Modify CSV output here (`result` variable)
// ...
frappe.tools.downloadify(result, null, me.title);
} else if (data.file_format_type == "Excel") {
try {
var filters = me.get_values(true);
} catch (e) {
return;
}
var args = {
cmd: 'frappe.desk.query_report.export_query',
report_name: me.report_name,
file_format_type: data.file_format_type,
filters: filters,
visible_idx: visible_idx,
}
open_url_post(frappe.request.url, args);
}
}, __("Export Report: " + me.title), __("Download"));
return false;
}
}
if (!frappe.query_report.old_make_export) {
frappe.query_report.old_make_export = frappe.query_report.make_export
}
frappe.query_report.make_export = function () {
if (this.report_name == "Report Name") {
return frappe.query_reports["Report Name"].make_export()
} else {
return frappe.query_report.old_make_export()
}
}
@michaeldvr Oh, sorry, I’ve tried mine in v11. Does that one works in v10?
I’m glad that my trick helps you haha
@Nahuel_Nso Yeah, it works. Perhaps get_data_for_csv function is exclusive for v11+ branch. Btw, thank you.