Insert an excel formula in export

Insert an excel formula in export

Mario De CarliMario De Carli Posts: 3Questions: 0Answers: 0

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

Replies

  • Mario De CarliMario De Carli Posts: 3Questions: 0Answers: 0

    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 CarliMario De Carli Posts: 3Questions: 0Answers: 0

    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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    Nice, thanks for reporting back,

    Colin

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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?

  • jmbiz2021jmbiz2021 Posts: 3Questions: 0Answers: 0

    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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

    @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

  • jmbiz2021jmbiz2021 Posts: 3Questions: 0Answers: 0

    @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

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

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

    Colin

  • allanallan Posts: 61,451Questions: 1Answers: 10,055 Site admin

    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

  • jmbiz2021jmbiz2021 Posts: 3Questions: 0Answers: 0

    @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.

Sign In or Register to comment.