Dynamic height and weight in table

dslt

Hello:

Thank you for your attention. I am going to try to explain my problema in more detail. I have this Project (https://playground.jsreport.net/studio/workspace/Hy_V2BSh/53). The only problem that I have is with Sheet5 of the XLSX template. I want the graph on Sheet5 to be fed by the data from Table5. By default the size of the graph in the template is A1:B2. However, I need that the size of the graph to adapt dynamically according to the size indicated by Table 5 (in this case A1:E4, in other cases other sizes). I do not know how to have the size of the graph change dynamically, could you explain to me how to make this happen?

Thank you very much.

jan_blaha

I see. The solution for dynamic number of rows is to use offset excel function. This is described in the linked tutorial
https://jsreport.net/learn/dynamic-excel-pivot-table

Unfortunately this doesn't seems to work for dynamic number of columns. A range defined like this should work at the first glance, but for some reason excel doesn't support it.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$A2:$D2))

This means you need to dig down deep to the excel format and add chart series by hand using xlsx helper functions. Something like this

{{#xlsxMerge "xl/charts/chart1.xml" "['c:chartSpace']['c:chart'][0]['c:plotArea'][0]['c:barChart'][0]"}}
<c:ser>
    <c:ser>
        <c:idx val="0"/>
        <c:order val="0"/>                                           
        <c:cat>
            <c:numRef>
                <c:f>Sheet1!$A$2:$A$5</c:f>                          
            </c:numRef>
        </c:cat>
        <c:val>
            <c:numRef>
                <c:f>Sheet1!$B$2:$B$5</c:f>                          
            </c:numRef>
        </c:val>
    </c:ser>
</c:ser>

I've prepared for you simple demo, which I hope will lead you to the desired results.

https://playground.jsreport.net/studio/workspace/HyqJPOI3x/5

dslt

Hello:

Thank you very much jan_blaha. You are great!

One more question: working on your solution I encountered another problem- I am working with JSReport through its API. To resolve the bug you suggest ("['c:chartSpace']['c:chart'][0]['c:plotArea'][0]['c:barChart'][0]") I need to add the "hackMerge" function to the figure in your example. I have tried to add this function in:

Attribute "helpers" of the API call.
Implement in script file and declare its "shortid" in attribute "shortid" of the object" "script" of the API call

In all cases I receive a similar error (with xlsxMerge):

......mplate[fileName]); } xlsxMerge.call(this, fileName, 'hack()', opts) }.......

SyntaxError: Unexpected identifier

Could you explain to me how to solve this problem?

jan_blaha

You don't want to store the template in jsreport, but rather send the template content, helpers and data in the API call?

dslt

Hello:

No. I store the templates in JSReport, but I don´t store the template content, or the data (I use API call) This configuration works fine in my project. The problem is: I don´t know how to add the "hackMerge" function in the API call. I have tried the last solution and it results in error. How can I solve this problem?

Example to the API call:

{
   template: {
     content: dataContent,
     recipe: 'xlsx',
     engine: "handlebars",
     phantom: optionsPhantom,
     xlsxTemplate: {
        shortid: idXLSXTemplate
     }
   },
   data : dataReport
}

jan_blaha

The helpers are stored in the template.helpers property, so you need to send it there, if you don't store it in the template. This body works

{ "template": { "name": "xlsxtest", "helpers": "function hackMerge(fileName, path, opts) {\n    \/\/ workaround for this bug\n    \/\/ https:\/\/github.com\/jsreport\/jsreport-xlsx\/issues\/20\n    opts.data.root.$xlsxTemplate[fileName].hack = function () {\n        return new Function('obj', 'return obj' + path)(opts.data.root.$xlsxTemplate[fileName]);\n    }\n    \n    xlsxMerge.call(this, fileName, 'hack()', opts)\n}", "content": "{{#hackMerge \"xl\/workbook.xml\" \".workbook.sheets[0].sheet[0]\"}}\n   <sheet name=\"My Sheet Name\"\/>\n{{\/hackMerge}}{{{xlsxPrint}}}" } }

dslt

I tried your solution:

{
  template: {
  content: dataContent,
  recipe: 'xlsx',
  engine: "handlebars",
  phantom: optionsPhantom,
  xlsxTemplate: {
     shortid: idXLSXTemplate
  },
  "helpers": "function hackMerge(fileName, path, opts) {\n    \/\/ workaround for this bug\n    \/\/ https:\/\/github.com\/jsreport\/jsreport-xlsx\/issues\/20\n    opts.data.root.$xlsxTemplate[fileName].hack = function () {\n        return new Function('obj', 'return obj' + path)(opts.data.root.$xlsxTemplate[fileName]);\n    }\n    \n    xlsxMerge.call(this, fileName, 'hack()', opts)\n}"
  },
  data : dataReport
}

And I get this error:

Error during rendering report:

Unexpected identifier evalmachine.<anonymous>:409
function hackMerge(fileName, path, opts) { opts.data.root.$xlsxTemplate[fileName].hack = function () { return new Function('obj', 'return obj' + path)(opts.data.root.$xlsxTemplate[fileName]); } xlsxMerge.call(this, fileName, 'hack()', opts) }
                                                                                                                                                                                                  ^^^^^^^^^
SyntaxError: Unexpected identifier

xlsXmerge it´s not declared

jan_blaha

Did you alter the default jsreport server configuration? What you have in tasks.strategy ?

dslt

I have not altered the default jsreport server configuration and my tasks.strategy file is empty.

jan_blaha

Hm, it works for me...
Can you run like this other helpers, for example in html recipe?
What jsreport version you have?
Do you send the json correctly escaped?
Maybe you could add a console.log(request.template.helpers) here to see if jsreport gets what you expect https://github.com/jsreport/jsreport-templates/blob/master/lib/templates.js#L76

jan_blaha

btw the latest release of jsreport includes fix for complex paths in xlsx helpers. So you can use standard xlsxMerge there.

dslt

Hello:

Thank you very much for your help.

I solved the error, it was silly. Attribute "helpers" in the API call work correctly. The error was in the string content of the attribute "helpers." You and I put:

"function hackMerge(fileName, path, opts) { opts.data.root.$xlsxTemplate[fileName].hack = function () { return new Function('obj', 'return obj' + path)(opts.data.root.$xlsxTemplate[fileName]); } xlsxMerge.call(this, fileName, 'hack()', opts) }"

And the correct way is:

"function hackMerge(fileName, path, opts) { opts.data.root.$xlsxTemplate[fileName].hack = function () { return new Function('obj', 'return obj' + path)(opts.data.root.$xlsxTemplate[fileName]); };  xlsxMerge.call(this, fileName, 'hack()', opts) }"

With special attention to semicolon (";") before the if-else statement.

I have worked very hard creating tables in JSReport. I have created an adapted example for anyone who has the same needs as I did. The example is this (https://playground.jsreport.net/studio/workspace/HyqJPOI3x/21).

jan_blaha

Ah, I can imagine this was easy to overlook. Glad you found it in the end.

Btw here you can find how to do code highlights for your next posts
https://help.github.com/articles/creating-and-highlighting-code-blocks/

dslt

Code highlighted ;-)

dslt

Hello:

One question more. Now, I want do a report with bars and lines in the graph. I am triying this (https://playground.jsreport.net/studio/workspace/HyqJPOI3x/23). Any ideas?

jan_blaha

Try to click Download in the Run context and open it in the excel. It will fix the xml and gives you protocol where you find what was wrong.