Nested SUBTOTAL calculation is not working when opening Excel file

Posted by: jayesh.upadhyay on 3 April 2018, 4:23 am EST

  • Posted 3 April 2018, 4:23 am EST

    The subtotal function with sum (i.e. 9) is not generating correct number when imported from JSON.

    File test.json (attached) is generated from "SpreadSheet Designer" which calculates the formulas and value correctly.

    We take that same JSON file to following Spread.Workbook to generate Excel and PDF as below :


    var workbooks = new GrapeCity.Documents.Spread.Workbook();
    workbooks.Open(mem); // where mem is stread from test.json file
    string spreadJson = workbooks.ToJson();

    Generated spreadJson is below :
    {
    "dataTable": {
    "0": {
    "0": {
    "value": 123
    }
    },
    "1": {
    "0": {
    "value": 234
    }
    },
    "2": {
    "0": {
    "formula": "SUBTOTAL(9,A1:A2)",
    "value": 357
    }
    },
    "3": {
    "0": {
    "formula": "SUBTOTAL(9,A1:A3)",
    "value": 714,
    "style": "__builtIn-1---Sheet1"
    }
    }
    }
    }


    As you see in above Generated JSON, the value for row "2" and "3" should be SAME since SUBTOTAL() function needs to skip the other subtotals in the range. The last SUBTOTAL (we use as Grand Total) is doubling the values up .. it suppose to be (357) and not adding other subtotals.

    Please Advice as our generated PDF is not working correctly for Tax purpose.

    Appreciated!

    Thanks
    Jayesh
  • Replied 4 April 2018, 1:55 am EST

    Hi Jayesh,

    Could you please provide me the ssjson file so that I can test it my end?


    Thanks,
    Deepak Sharma
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels