Insert an excel formula in export

Mario De Carli

I'm trying to add a specific cell in an export using the customize function.
The goal I want to achieve is to add a row at the beginning with a SUBTOTAL function in a cell. I found a few example but I still get stuck in the same error. My code is

{ 
extend: 'excelHtml5', title: null, footer: true, header: false,
customize: function ( xlsx ) {
    var sheet = xlsx.xl.worksheets['sheet1.xml'];
    $('row c[r^='K']', sheet).each(function() {
        if ($('is t', this).text()) {
            console.log(this);
            //change the type to `str` which is a formula
            $(this).attr('t', 'str');
            //append the formula
            $(this).append('<f>' + $('is t', this).text() + '</f>');
            //remove the inlineStr
            $('is', this).remove();
            console.log(this);
        }
    })

},
customizeData: function(data){
    //Add a row.
    var desc = [
        ['Parcheggi Espositori',' ',' ',' ',' ',' ',' ',' ',' ','SUBTOTALE','SUBTOTAL(3;F3:F137)']
    ];
    data.body.unshift(data.header);
    for (var i = 0; i < desc.length; i++) {
        data.body.unshift(desc[i]);
    };
}
}

If I look at the generated xml I find it and it looks correct

<x:c r="K1" t="str" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:f>SUBTOTAL(3;F3:F137)</x:f>
</x:c>

but when I open it in excel it says there is an error and If I want to recover content and got this xml error:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error426200_05.xml</logFileName><summary>Rilevati errori nel file 'xxx.xlsx'</summary><additionalInfo><info>Le operazioni di convalida e ripristino a livello di file sono state completate. È possibile che alcune parti della cartella di lavoro siano state ripristinate o eliminate.</info></additionalInfo><removedRecords><removedRecord>Record rimossi: Formula dalla parte /xl/worksheets/sheet1.xml</removedRecord></removedRecords></recoveryLog>

Why is that? The formula is correct, if I use it in excel it gets a result.

Thanks a lot

Mario De Carli

I tried using a different formula, something like =5+7 and with this it works. So the thing it doesn't recognize it's the SUBTOTAL. I don't know if the problem is that my excel is in italian and formulas are localized. I even tried with the correct SUBTOTALE but I get the same error

Mario De Carli

Ok, I solved this, it's a difference in how a formula is written in italian version.
In italy we use

SUBTOTALE(3;F3:F137)

while in xml I don't only need to use SUBTOTAL that I already did but I have to write it like that:

SUBTOTAL(3,F3:F137)

with a colon instead of a semicolon

colin

Nice, thanks for reporting back,

Colin

rf1234

Same here: Excel has semi colons instead of commas almost everywhere in continental Europe. Commas aren't used because they are decimal points as well ("decimal point is comma"). So instead of using commas for csvs, formulas etc a semi colon is being used.

If you do XML for Excel manipulations you need to pretend to be American though ... Opposite if you create a csv file: you need to anticipate the Excel version that is being used by your customers: American, English or rather Italian or German?

jmbiz2021

Hi All,

Re: Insert an excel formula in export - error

We tried the above(same code) in our application, after reading it clearly. We tried to insert a formula, But We are getting a JavaScript error "Uncaught Syntax Error: missing ) after argument list".

Also shared error screen shot. FYI.

**

**

Kindly guide us to solve this.

Thanks & Regards
M.Muthiah

colin

@jmbiz2021 We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

jmbiz2021

@Colin, Thanks for your response.
As per forum rules, We have created test case in the fiddle for this.
The test case link is https://jsfiddle.net/Anbu_04/kz8Lv7pc/3/.
Kindly look into it and support us.

Regards
Muthiah

colin

Please can you ensure the test case runs and replicates the error you want support with - that link gives syntax errors.

Colin

allan

The debugger is pointing to this line:

$('row c[r^='K']', sheet).each(function() {

It is not valid Javascript. You need to escape the ' inside the string. See the MDN documentation if you aren't sure how to use Javascript strings.

Allan

jmbiz2021

@allan & @colin,
Thanks. I used the code sample given by Mario De Carli. Please refer the earlier thread of Mario De Carli. Ultimately, it was working for Mario De Carli.

Please find attached snap shot of it.

Muthiah.