Error total row in xlsx

Haritz Lopez

Hello,

I have the next playground : https://playground.jsreport.net/w/haritz.lopez/TMDEG0XP

As you can see, if you run it, it works fine but what I would like to do is to add that row just 2 lines below the last one. Right now, if I change the "+8" to "+7" or even smaller, I get an error and the total is not showing. I do not understand what's going on and I do not know if there is another way to do what I am trying to do.

Is just an test playground, the real one has more data.

Thank you in advance.

jan_blaha

You can find the max row number like this

{{maxRowNumber @root}}

function maxRowNumber(root) {
    const rows = root.$xlsxTemplate['xl/worksheets/sheet1.xml'].worksheet.sheetData[0].row
    return Math.max.apply(Math, rows.map(r => parseInt(r.$.r)))
}

Haritz Lopez

Thank you.

I have done that and the method returns 9. I can not write in that line. If I try, the data is not showing and the excel says that there was an error but if to that return I add 1, +1, it works fine. I am trying to understand why it returns 9 when I only have 1 persons data. When you run it the rows between 5 and 9 are empty but it's imposible to write there without getting an error.

Thank you again.

Haritz Lopez

Fixed.

Haritz Lopez

I am facing the error that number is saved as text and with that I can not make sums and so on. How can I fix that issue? Is happening with the totals and the data of the #each.

Thanks.

jan_blaha

https://playground.jsreport.net/w/anon/r1vaurbw-3

{{#xlsxAdd "xl/worksheets/sheet1.xml" "worksheet.sheetData[0].row"}}
    <row>
        <c><v>11</v></c>
    </row>
{{/xlsxAdd}}

Haritz Lopez

Thank you works fine but the issue I am facing now is with the json data. Is there any way to do the same with that data?

I updated a little bit my demo. If the number is an integer and you download the file, you can see that it says the number is saved as text. Also, if I try to do a SUM() using the ones that do not show that error, still does not work.

Thank you again.

jan_blaha

I'm not sure I get it right, however, if you don't know if the data are int or string upfront, you just need to implement a condition with handlebars. Using a helper for example like here
https://playground.jsreport.net/w/anon/kBLsukni

function cellWithType(v) {
    if (isNaN(v)) {
         return `<c t="inlineStr"><is><t>${v}</t></is></c>`
    }

    return `<c><v>${v}</v></c>`
}

Haritz Lopez

In my playground you can see that I have a template, a xlsx. Inside that, I have a #each datos to iterate the data from the array of jsons. Almost all values are numbers, except the first two, separated by dots which are created with JsonConvert.SerializeObject. Then I use jsreport.Client and jsreport.Types.

The issue is, when I open the excel, the numbers are not numbers and I can not make any formula or whatever I want. I do not need to add formulas in the xlsx but maybe someone would need at any moment.

Haritz Lopez

For anyone wondering, I was able to solve the problem removing the #if inside the template. Yet I do not understand the reason behind that but removing it solved my issue about numbers being saved as text.