Generate an XLSX with more than 1 million rows

odirleiborgert

Hi People,

I'm generating an "xlsx" file and it contains over 1 million lines, and I need to separate it into tabs inside the XLSX, how could I do that?

And I'm also having another problem ... when I try to render more than 1 million records the following error occurs.

{
  "message": "read ECONNRESET",
  "stack": "Error: read ECONNRESET\n    at TCP.onStreamRead (internal/stream_base_commons.js:162:27)"
}

My POST for http://localhost:5488/api/report

{
	"template": {
		"name": "excel"
	},
	"templatingEngines": {
		"timeout": 600000,
		"strategy": "dedicated-process"
	}
}

jan_blaha

Do you use xlsx recipe or the html-to-xlsx recipe?
Also, can you share the logs?

Maybe you get the error for 1mil of rows because of the reached memory limit.
Try to increase the memory limit for the nodejs.

From this blog post...

Set the NODE_OPTIONS environment variable

NODE_OPTIONS=--max-old-space-size=6096

Run jsreport

jsreport start

odirleiborgert

Hi @jan_blaha

I'm using the 'xlsx' recipe. I made the memory change that happened to me, I ran a new test with 1200000 lines and I'm copying the log for you to see.

2019-05-20T11:22:52.735Z - info: Starting rendering request 11 (user: null)
2019-05-20T11:22:52.736Z - info: Rendering template { name: excel, recipe: xlsx, engine: handlebars, preview: false }
2019-05-20T11:25:25.471Z - info: fs store is loading data
2019-05-20T11:25:26.446Z - info: Starting rendering request 12 (user: null)
2019-05-20T11:25:26.447Z - info: Rendering template { name: excel, recipe: xlsx, engine: handlebars, preview: true }
2019-05-20T11:25:26.522Z - info: Starting rendering request 13 (user: null)
2019-05-20T11:25:26.524Z - info: Rendering template { name: excel, recipe: xlsx, engine: handlebars, preview: true }
2019-05-20T11:25:40.200Z - warn: Error when processing render request read ECONNRESET Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:162:27)
2019-05-20T11:25:40.200Z - warn: Error during processing request at http://localhost:5488/api/report
2019-05-20T11:25:40.209Z - warn: Error when processing render request read ECONNRESET Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:162:27)
2019-05-20T11:25:40.210Z - warn: Error when processing render request read ECONNRESET Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:162:27)
2019-05-20T11:25:40.210Z - warn: Error during processing request at http://localhost:5488/api/report/excel
2019-05-20T11:25:40.210Z - warn: Error during processing request at http://localhost:5488/api/report/excel

My template:

{{xlsxRemove "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row" 1}}

{{#each envios.result}}

    {{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
        <row>
            <c t="inlineStr" s="{{@root.$removedItem.c.[0].$.s}}"><is><t>{{id}}</t></is></c>
            <c t="inlineStr" s="{{@root.$removedItem.c.[1].$.s}}"><is><t>{{data_hora}} {{ 2 }}</t></is></c>
            <c t="inlineStr" s="{{@root.$removedItem.c.[2].$.s}}"><is><t>{{status}}</t></is></c>
        </row>
    {{/xlsxAdd}}
    
{{/each}}

{{{xlsxPrint}}}

My script:

var request = require('request')

function beforeRender(req, res, done) {
    
    var url = 'http://localhost:5000/list';
    
    var options = {
        url: url,
        timeout: 600000
    }
    
    request.get(
        options, 
        function (err, resp, body) {
            
            if (err) {
                return done(err)
            }
            
            req.data = JSON.parse(body)
    
            done()
        }
    ) 
}

jan_blaha

Could you run this with the debug button from the studio and share the output?
We should get more detail information from it.

odirleiborgert

Hi @jan_blaha

The output of the debug. I am trying to generate a file with 1,100,000 rows. With 1,000,000 lines working normally.

read ECONNRESET
logs:
+0      Starting rendering request 16 (user: null)
+2      Rendering template { name: excel, recipe: xlsx, engine: handlebars, preview: true }
+2      Data item not defined for this template.
+2      Resources not defined for this template.
+34     Executing script script
+168787 Base url not specified, skipping its injection.
+168787 Rendering engine handlebars
Error: read ECONNRESET
    at TCP.onStreamRead (internal/stream_base_commons.js:111:27)

jan_blaha

Thanks. Do you use the latest jsreport?

You mentioned at the top your POST looks like this.

{
	"template": {
		"name": "excel"
	},
	"templatingEngines": {
		"timeout": 600000,
		"strategy": "dedicated-process"
	}
}

However, the templatingEngines prop in the POST is wrong.
This is configuration prop which should be part of your jsreport.config.json instead.

Do you get the same error when running this example in your environment?
https://playground.jsreport.net/w/admin/NcMHDYlu
I have it running with some millions of rows and it is not crashing.

odirleiborgert

Hi @jan_blaha ,
Thanks for your time dedicated to helping me, I was able to run your code, I will investigate my code better and I will answer here when I find my problem.