Support for Multi Ranged Worksheet to JSON

k4b1n · August 26, 2024, 4:50pm

I have a usecase
A workbook has two tabs
One tab data can go from row1 to 1000 max; column 1 to 10
Another tab data can go from row 1 to 100 max; column 1 to 10.

How can I achieve so that JSON is valid json and row consists of 1000 or less for first tab and 100 or less for second tab?

I ask this because there is no example for excel to json conversion which supports my use-case.

Also, when there are empty rows I am seeing null in json which makes overall json invalid. Is it something that is lacking in product?

Professionalize.Discourse · August 26, 2024, 4:50pm

@k4b1n

Can you please provide more details about the specific code or method you are using for the Excel to JSON conversion? Additionally, what behavior are you expecting regarding empty rows in the JSON output?

k4b1n · August 26, 2024, 6:01pm

        //aspose-cells version : 22.12
        final Workbook workbook = new Workbook("TwoSheetWorkbook.xlsx");
        JsonSaveOptions jsonSaveOptions = new JsonSaveOptions();
        jsonSaveOptions.setSkipEmptyRows(true);
        jsonSaveOptions.setExportEmptyCells(false);

        //want endRow as maxRows for Sheet1 i.e. 1000
        //want endRow as maxRows for Sheet2 i.e. 100
        jsonSaveOptions.setExportArea(CellArea.createCellArea(0,0,203,10));
        jsonSaveOptions.setSheetIndexes(new int[]{0, 1});


        //JSON String conversion from Stream
        final ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.save(byteArrayOutputStream, jsonSaveOptions);
        System.out.println(byteArrayOutputStream.toString(StandardCharsets.UTF_8));

        /**
         * output produced:
         * output I want: 
         * output.Sheet1.length = 1000 or less depending on data
         * output.Sheet2.length = 100 or less depending on data
         * 
         * {
         *   "Sheet1": [
         *     {
         *       "Date Field": "08\/25\/24",
         *       "Number Field": 2,
         *       "Currency Field": 21,
         *       "General Field": "Sample text 2"
         *     },
         *     ...
         *     null,
         *     null,
         *   ],
         *   "Sheet2": [
         *     {
         *       "Date Field2": "08\/26\/24",
         *       "Number Field2": 2,
         *       "Currency Field2": 41.5,
         *       "General Field2": "Sample text 2"
         *     },
         *     ...
         *     null, //I donot want this because it makes JSON structure invalid
         *     null,
         *   ]
         * }
         */

TwoSheetWorkbook.xlsx.zip (12.1 KB)

amjad.sahi · August 26, 2024, 7:04pm

@k4b1n,

I tested using your template Excel file and following sample code with latest version/fix Aspose.Cells for Java v24.8 (please try it) and it works fine and as expected. If you need to export all data in a worksheet, you do not need to specify the export area as by default, it will export max display range (of cells) to JSON.
e.g.,
Sample code:

        final Workbook workbook = new Workbook("d:\\files\\TwoSheetWorkbook.xlsx");
        JsonSaveOptions jsonSaveOptions = new JsonSaveOptions();
        jsonSaveOptions.setSkipEmptyRows(true);
        jsonSaveOptions.setExportEmptyCells(false);

        jsonSaveOptions.setSheetIndexes(new int[]{0, 1});


        //JSON String conversion from Stream
        final ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.save(byteArrayOutputStream, jsonSaveOptions);

        String string = new String(byteArrayOutputStream.toByteArray(), "UTF-8");
        System.out.println(string);

output:

{
"Sheet1":[
 {
  "Date Field": "08\/25\/24",
  "Number Field": 2,
  "Currency Field": 21,
  "General Field": "Sample text 2"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 3,
  "Currency Field": 31.5,
  "General Field": "Sample text 3"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 4,
  "Currency Field": 42,
  "General Field": "Sample text 4"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 5,
  "Currency Field": 52.5,
  "General Field": "Sample text 5"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 6,
  "Currency Field": 63,
  "General Field": "Sample text 6"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 7,
  "Currency Field": 73.5,
  "General Field": "Sample text 7"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 8,
  "Currency Field": 84,
  "General Field": "Sample text 8"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 9,
  "Currency Field": 94.5,
  "General Field": "Sample text 9"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 10,
  "Currency Field": 105,
  "General Field": "Sample text 10"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 11,
  "Currency Field": 115.5,
  "General Field": "Sample text 11"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 12,
  "Currency Field": 126,
  "General Field": "Sample text 12"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 13,
  "Currency Field": 136.5,
  "General Field": "Sample text 13"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 14,
  "Currency Field": 147,
  "General Field": "Sample text 14"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 15,
  "Currency Field": 157.5,
  "General Field": "Sample text 15"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 16,
  "Currency Field": 168,
  "General Field": "Sample text 16"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 17,
  "Currency Field": 178.5,
  "General Field": "Sample text 17"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 18,
  "Currency Field": 189,
  "General Field": "Sample text 18"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 19,
  "Currency Field": 199.5,
  "General Field": "Sample text 19"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 20,
  "Currency Field": 210,
  "General Field": "Sample text 20"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 21,
  "Currency Field": 220.5,
  "General Field": "Sample text 21"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 22,
  "Currency Field": 231,
  "General Field": "Sample text 22"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 23,
  "Currency Field": 241.5,
  "General Field": "Sample text 23"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 24,
  "Currency Field": 252,
  "General Field": "Sample text 24"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 25,
  "Currency Field": 262.5,
  "General Field": "Sample text 25"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 26,
  "Currency Field": 273,
  "General Field": "Sample text 26"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 27,
  "Currency Field": 283.5,
  "General Field": "Sample text 27"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 28,
  "Currency Field": 294,
  "General Field": "Sample text 28"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 29,
  "Currency Field": 304.5,
  "General Field": "Sample text 29"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 30,
  "Currency Field": 315,
  "General Field": "Sample text 30"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 31,
  "Currency Field": 325.5,
  "General Field": "Sample text 31"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 32,
  "Currency Field": 336,
  "General Field": "Sample text 32"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 33,
  "Currency Field": 346.5,
  "General Field": "Sample text 33"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 34,
  "Currency Field": 357,
  "General Field": "Sample text 34"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 35,
  "Currency Field": 367.5,
  "General Field": "Sample text 35"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 36,
  "Currency Field": 378,
  "General Field": "Sample text 36"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 37,
  "Currency Field": 388.5,
  "General Field": "Sample text 37"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 38,
  "Currency Field": 399,
  "General Field": "Sample text 38"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 39,
  "Currency Field": 409.5,
  "General Field": "Sample text 39"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 40,
  "Currency Field": 420,
  "General Field": "Sample text 40"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 41,
  "Currency Field": 430.5,
  "General Field": "Sample text 41"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 42,
  "Currency Field": 441,
  "General Field": "Sample text 42"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 43,
  "Currency Field": 451.5,
  "General Field": "Sample text 43"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 44,
  "Currency Field": 462,
  "General Field": "Sample text 44"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 45,
  "Currency Field": 472.5,
  "General Field": "Sample text 45"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 46,
  "Currency Field": 483,
  "General Field": "Sample text 46"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 47,
  "Currency Field": 493.5,
  "General Field": "Sample text 47"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 48,
  "Currency Field": 504,
  "General Field": "Sample text 48"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 49,
  "Currency Field": 514.5,
  "General Field": "Sample text 49"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 50,
  "Currency Field": 525,
  "General Field": "Sample text 50"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 51,
  "Currency Field": 535.5,
  "General Field": "Sample text 51"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 52,
  "Currency Field": 546,
  "General Field": "Sample text 52"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 53,
  "Currency Field": 556.5,
  "General Field": "Sample text 53"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 54,
  "Currency Field": 567,
  "General Field": "Sample text 54"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 55,
  "Currency Field": 577.5,
  "General Field": "Sample text 55"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 56,
  "Currency Field": 588,
  "General Field": "Sample text 56"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 57,
  "Currency Field": 598.5,
  "General Field": "Sample text 57"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 58,
  "Currency Field": 609,
  "General Field": "Sample text 58"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 59,
  "Currency Field": 619.5,
  "General Field": "Sample text 59"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 60,
  "Currency Field": 630,
  "General Field": "Sample text 60"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 61,
  "Currency Field": 640.5,
  "General Field": "Sample text 61"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 62,
  "Currency Field": 651,
  "General Field": "Sample text 62"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 63,
  "Currency Field": 661.5,
  "General Field": "Sample text 63"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 64,
  "Currency Field": 672,
  "General Field": "Sample text 64"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 65,
  "Currency Field": 682.5,
  "General Field": "Sample text 65"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 66,
  "Currency Field": 693,
  "General Field": "Sample text 66"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 67,
  "Currency Field": 703.5,
  "General Field": "Sample text 67"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 68,
  "Currency Field": 714,
  "General Field": "Sample text 68"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 69,
  "Currency Field": 724.5,
  "General Field": "Sample text 69"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 70,
  "Currency Field": 735,
  "General Field": "Sample text 70"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 71,
  "Currency Field": 745.5,
  "General Field": "Sample text 71"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 72,
  "Currency Field": 756,
  "General Field": "Sample text 72"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 73,
  "Currency Field": 766.5,
  "General Field": "Sample text 73"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 74,
  "Currency Field": 777,
  "General Field": "Sample text 74"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 75,
  "Currency Field": 787.5,
  "General Field": "Sample text 75"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 76,
  "Currency Field": 798,
  "General Field": "Sample text 76"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 77,
  "Currency Field": 808.5,
  "General Field": "Sample text 77"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 78,
  "Currency Field": 819,
  "General Field": "Sample text 78"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 79,
  "Currency Field": 829.5,
  "General Field": "Sample text 79"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 80,
  "Currency Field": 840,
  "General Field": "Sample text 80"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 81,
  "Currency Field": 850.5,
  "General Field": "Sample text 81"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 82,
  "Currency Field": 861,
  "General Field": "Sample text 82"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 83,
  "Currency Field": 871.5,
  "General Field": "Sample text 83"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 84,
  "Currency Field": 882,
  "General Field": "Sample text 84"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 85,
  "Currency Field": 892.5,
  "General Field": "Sample text 85"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 86,
  "Currency Field": 903,
  "General Field": "Sample text 86"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 87,
  "Currency Field": 913.5,
  "General Field": "Sample text 87"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 88,
  "Currency Field": 924,
  "General Field": "Sample text 88"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 89,
  "Currency Field": 934.5,
  "General Field": "Sample text 89"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 90,
  "Currency Field": 945,
  "General Field": "Sample text 90"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 91,
  "Currency Field": 955.5,
  "General Field": "Sample text 91"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 92,
  "Currency Field": 966,
  "General Field": "Sample text 92"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 93,
  "Currency Field": 976.5,
  "General Field": "Sample text 93"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 94,
  "Currency Field": 987,
  "General Field": "Sample text 94"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 95,
  "Currency Field": 997.5,
  "General Field": "Sample text 95"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 96,
  "Currency Field": 1008,
  "General Field": "Sample text 96"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 97,
  "Currency Field": 1018.5,
  "General Field": "Sample text 97"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 98,
  "Currency Field": 1029,
  "General Field": "Sample text 98"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 99,
  "Currency Field": 1039.5,
  "General Field": "Sample text 99"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 100,
  "Currency Field": 1050,
  "General Field": "Sample text 100"
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 101,
  "Currency Field": 1060.5,
  "General Field": "Sample text 101"
 }
],
"Sheet2":[
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 2,
  "Currency Field2": 41.5,
  "General Field2": "Sample text 2"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 3,
  "Currency Field2": 62.25,
  "General Field2": "Sample text 3"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 4,
  "Currency Field2": 83,
  "General Field2": "Sample text 4"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 5,
  "Currency Field2": 103.75,
  "General Field2": "Sample text 5"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 6,
  "Currency Field2": 124.5,
  "General Field2": "Sample text 6"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 7,
  "Currency Field2": 145.25,
  "General Field2": "Sample text 7"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 8,
  "Currency Field2": 166,
  "General Field2": "Sample text 8"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 9,
  "Currency Field2": 186.75,
  "General Field2": "Sample text 9"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 10,
  "Currency Field2": 207.5,
  "General Field2": "Sample text 10"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 11,
  "Currency Field2": 228.25,
  "General Field2": "Sample text 11"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 12,
  "Currency Field2": 249,
  "General Field2": "Sample text 12"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 13,
  "Currency Field2": 269.75,
  "General Field2": "Sample text 13"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 14,
  "Currency Field2": 290.5,
  "General Field2": "Sample text 14"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 15,
  "Currency Field2": 311.25,
  "General Field2": "Sample text 15"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 16,
  "Currency Field2": 332,
  "General Field2": "Sample text 16"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 17,
  "Currency Field2": 352.75,
  "General Field2": "Sample text 17"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 18,
  "Currency Field2": 373.5,
  "General Field2": "Sample text 18"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 19,
  "Currency Field2": 394.25,
  "General Field2": "Sample text 19"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 20,
  "Currency Field2": 415,
  "General Field2": "Sample text 20"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 21,
  "Currency Field2": 435.75,
  "General Field2": "Sample text 21"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 22,
  "Currency Field2": 456.5,
  "General Field2": "Sample text 22"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 23,
  "Currency Field2": 477.25,
  "General Field2": "Sample text 23"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 24,
  "Currency Field2": 498,
  "General Field2": "Sample text 24"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 25,
  "Currency Field2": 518.75,
  "General Field2": "Sample text 25"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 26,
  "Currency Field2": 539.5,
  "General Field2": "Sample text 26"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 27,
  "Currency Field2": 560.25,
  "General Field2": "Sample text 27"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 28,
  "Currency Field2": 581,
  "General Field2": "Sample text 28"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 29,
  "Currency Field2": 601.75,
  "General Field2": "Sample text 29"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 30,
  "Currency Field2": 622.5,
  "General Field2": "Sample text 30"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 31,
  "Currency Field2": 643.25,
  "General Field2": "Sample text 31"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 32,
  "Currency Field2": 664,
  "General Field2": "Sample text 32"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 33,
  "Currency Field2": 684.75,
  "General Field2": "Sample text 33"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 34,
  "Currency Field2": 705.5,
  "General Field2": "Sample text 34"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 35,
  "Currency Field2": 726.25,
  "General Field2": "Sample text 35"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 36,
  "Currency Field2": 747,
  "General Field2": "Sample text 36"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 37,
  "Currency Field2": 767.75,
  "General Field2": "Sample text 37"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 38,
  "Currency Field2": 788.5,
  "General Field2": "Sample text 38"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 39,
  "Currency Field2": 809.25,
  "General Field2": "Sample text 39"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 40,
  "Currency Field2": 830,
  "General Field2": "Sample text 40"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 41,
  "Currency Field2": 850.75,
  "General Field2": "Sample text 41"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 42,
  "Currency Field2": 871.5,
  "General Field2": "Sample text 42"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 43,
  "Currency Field2": 892.25,
  "General Field2": "Sample text 43"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 44,
  "Currency Field2": 913,
  "General Field2": "Sample text 44"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 45,
  "Currency Field2": 933.75,
  "General Field2": "Sample text 45"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 46,
  "Currency Field2": 954.5,
  "General Field2": "Sample text 46"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 47,
  "Currency Field2": 975.25,
  "General Field2": "Sample text 47"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 48,
  "Currency Field2": 996,
  "General Field2": "Sample text 48"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 49,
  "Currency Field2": 1016.75,
  "General Field2": "Sample text 49"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 50,
  "Currency Field2": 1037.5,
  "General Field2": "Sample text 50"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 51,
  "Currency Field2": 1058.25,
  "General Field2": "Sample text 51"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 52,
  "Currency Field2": 1079,
  "General Field2": "Sample text 52"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 53,
  "Currency Field2": 1099.75,
  "General Field2": "Sample text 53"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 54,
  "Currency Field2": 1120.5,
  "General Field2": "Sample text 54"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 55,
  "Currency Field2": 1141.25,
  "General Field2": "Sample text 55"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 56,
  "Currency Field2": 1162,
  "General Field2": "Sample text 56"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 57,
  "Currency Field2": 1182.75,
  "General Field2": "Sample text 57"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 58,
  "Currency Field2": 1203.5,
  "General Field2": "Sample text 58"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 59,
  "Currency Field2": 1224.25,
  "General Field2": "Sample text 59"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 60,
  "Currency Field2": 1245,
  "General Field2": "Sample text 60"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 61,
  "Currency Field2": 1265.75,
  "General Field2": "Sample text 61"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 62,
  "Currency Field2": 1286.5,
  "General Field2": "Sample text 62"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 63,
  "Currency Field2": 1307.25,
  "General Field2": "Sample text 63"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 64,
  "Currency Field2": 1328,
  "General Field2": "Sample text 64"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 65,
  "Currency Field2": 1348.75,
  "General Field2": "Sample text 65"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 66,
  "Currency Field2": 1369.5,
  "General Field2": "Sample text 66"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 67,
  "Currency Field2": 1390.25,
  "General Field2": "Sample text 67"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 68,
  "Currency Field2": 1411,
  "General Field2": "Sample text 68"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 69,
  "Currency Field2": 1431.75,
  "General Field2": "Sample text 69"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 70,
  "Currency Field2": 1452.5,
  "General Field2": "Sample text 70"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 71,
  "Currency Field2": 1473.25,
  "General Field2": "Sample text 71"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 72,
  "Currency Field2": 1494,
  "General Field2": "Sample text 72"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 73,
  "Currency Field2": 1514.75,
  "General Field2": "Sample text 73"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 74,
  "Currency Field2": 1535.5,
  "General Field2": "Sample text 74"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 75,
  "Currency Field2": 1556.25,
  "General Field2": "Sample text 75"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 76,
  "Currency Field2": 1577,
  "General Field2": "Sample text 76"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 77,
  "Currency Field2": 1597.75,
  "General Field2": "Sample text 77"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 78,
  "Currency Field2": 1618.5,
  "General Field2": "Sample text 78"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 79,
  "Currency Field2": 1639.25,
  "General Field2": "Sample text 79"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 80,
  "Currency Field2": 1660,
  "General Field2": "Sample text 80"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 81,
  "Currency Field2": 1680.75,
  "General Field2": "Sample text 81"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 82,
  "Currency Field2": 1701.5,
  "General Field2": "Sample text 82"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 83,
  "Currency Field2": 1722.25,
  "General Field2": "Sample text 83"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 84,
  "Currency Field2": 1743,
  "General Field2": "Sample text 84"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 85,
  "Currency Field2": 1763.75,
  "General Field2": "Sample text 85"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 86,
  "Currency Field2": 1784.5,
  "General Field2": "Sample text 86"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 87,
  "Currency Field2": 1805.25,
  "General Field2": "Sample text 87"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 88,
  "Currency Field2": 1826,
  "General Field2": "Sample text 88"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 89,
  "Currency Field2": 1846.75,
  "General Field2": "Sample text 89"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 90,
  "Currency Field2": 1867.5,
  "General Field2": "Sample text 90"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 91,
  "Currency Field2": 1888.25,
  "General Field2": "Sample text 91"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 92,
  "Currency Field2": 1909,
  "General Field2": "Sample text 92"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 93,
  "Currency Field2": 1929.75,
  "General Field2": "Sample text 93"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 94,
  "Currency Field2": 1950.5,
  "General Field2": "Sample text 94"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 95,
  "Currency Field2": 1971.25,
  "General Field2": "Sample text 95"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 96,
  "Currency Field2": 1992,
  "General Field2": "Sample text 96"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 97,
  "Currency Field2": 2012.75,
  "General Field2": "Sample text 97"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 98,
  "Currency Field2": 2033.5,
  "General Field2": "Sample text 98"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 99,
  "Currency Field2": 2054.25,
  "General Field2": "Sample text 99"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 100,
  "Currency Field2": 2075,
  "General Field2": "Sample text 100"
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 101,
  "Currency Field2": 2095.75,
  "General Field2": "Sample text 101"
 }
]
}

Please try the latest version/fix Aspose.Cells for .NET v24.8. Let us know with details if you still find any issue or some specific requirements. We will check and assist you accordingly.

k4b1n · August 27, 2024, 2:20am

I think the version you mentioned has fixes for null issue which used to make json invalid.

@amjad.sahi,
One more question if I want to export column
A to C and row A1 to max row which has data for both sheet Sheet1 and Sheet2. How can I do?

John.He · August 27, 2024, 3:22am

@k4b1n
You can refer to the following example code to set the export area.

final Workbook workbook = new Workbook(filePath + "TwoSheetWorkbook.xlsx");
JsonSaveOptions jsonSaveOptions = new JsonSaveOptions();
jsonSaveOptions.setSkipEmptyRows(true);
jsonSaveOptions.setExportEmptyCells(false);

//Set export area
jsonSaveOptions.setExportArea(CellArea.createCellArea("A1", "C101"));
jsonSaveOptions.setSheetIndexes(new int[]{0, 1});


//JSON String conversion from Stream
final ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
workbook.save(byteArrayOutputStream, jsonSaveOptions);

String string = new String(byteArrayOutputStream.toByteArray(), "UTF-8");
System.out.println(string);

The output result:

{
"Sheet1":[
 {
  "Date Field": "08\/25\/24",
  "Number Field": 2,
  "Currency Field": 21
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 3,
  "Currency Field": 31.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 4,
  "Currency Field": 42
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 5,
  "Currency Field": 52.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 6,
  "Currency Field": 63
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 7,
  "Currency Field": 73.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 8,
  "Currency Field": 84
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 9,
  "Currency Field": 94.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 10,
  "Currency Field": 105
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 11,
  "Currency Field": 115.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 12,
  "Currency Field": 126
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 13,
  "Currency Field": 136.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 14,
  "Currency Field": 147
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 15,
  "Currency Field": 157.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 16,
  "Currency Field": 168
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 17,
  "Currency Field": 178.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 18,
  "Currency Field": 189
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 19,
  "Currency Field": 199.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 20,
  "Currency Field": 210
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 21,
  "Currency Field": 220.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 22,
  "Currency Field": 231
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 23,
  "Currency Field": 241.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 24,
  "Currency Field": 252
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 25,
  "Currency Field": 262.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 26,
  "Currency Field": 273
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 27,
  "Currency Field": 283.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 28,
  "Currency Field": 294
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 29,
  "Currency Field": 304.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 30,
  "Currency Field": 315
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 31,
  "Currency Field": 325.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 32,
  "Currency Field": 336
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 33,
  "Currency Field": 346.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 34,
  "Currency Field": 357
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 35,
  "Currency Field": 367.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 36,
  "Currency Field": 378
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 37,
  "Currency Field": 388.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 38,
  "Currency Field": 399
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 39,
  "Currency Field": 409.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 40,
  "Currency Field": 420
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 41,
  "Currency Field": 430.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 42,
  "Currency Field": 441
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 43,
  "Currency Field": 451.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 44,
  "Currency Field": 462
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 45,
  "Currency Field": 472.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 46,
  "Currency Field": 483
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 47,
  "Currency Field": 493.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 48,
  "Currency Field": 504
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 49,
  "Currency Field": 514.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 50,
  "Currency Field": 525
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 51,
  "Currency Field": 535.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 52,
  "Currency Field": 546
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 53,
  "Currency Field": 556.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 54,
  "Currency Field": 567
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 55,
  "Currency Field": 577.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 56,
  "Currency Field": 588
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 57,
  "Currency Field": 598.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 58,
  "Currency Field": 609
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 59,
  "Currency Field": 619.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 60,
  "Currency Field": 630
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 61,
  "Currency Field": 640.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 62,
  "Currency Field": 651
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 63,
  "Currency Field": 661.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 64,
  "Currency Field": 672
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 65,
  "Currency Field": 682.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 66,
  "Currency Field": 693
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 67,
  "Currency Field": 703.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 68,
  "Currency Field": 714
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 69,
  "Currency Field": 724.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 70,
  "Currency Field": 735
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 71,
  "Currency Field": 745.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 72,
  "Currency Field": 756
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 73,
  "Currency Field": 766.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 74,
  "Currency Field": 777
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 75,
  "Currency Field": 787.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 76,
  "Currency Field": 798
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 77,
  "Currency Field": 808.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 78,
  "Currency Field": 819
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 79,
  "Currency Field": 829.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 80,
  "Currency Field": 840
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 81,
  "Currency Field": 850.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 82,
  "Currency Field": 861
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 83,
  "Currency Field": 871.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 84,
  "Currency Field": 882
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 85,
  "Currency Field": 892.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 86,
  "Currency Field": 903
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 87,
  "Currency Field": 913.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 88,
  "Currency Field": 924
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 89,
  "Currency Field": 934.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 90,
  "Currency Field": 945
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 91,
  "Currency Field": 955.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 92,
  "Currency Field": 966
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 93,
  "Currency Field": 976.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 94,
  "Currency Field": 987
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 95,
  "Currency Field": 997.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 96,
  "Currency Field": 1008
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 97,
  "Currency Field": 1018.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 98,
  "Currency Field": 1029
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 99,
  "Currency Field": 1039.5
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 100,
  "Currency Field": 1050
 },
 {
  "Date Field": "08\/25\/24",
  "Number Field": 101,
  "Currency Field": 1060.5
 }
],
"Sheet2":[
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 2,
  "Currency Field2": 41.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 3,
  "Currency Field2": 62.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 4,
  "Currency Field2": 83
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 5,
  "Currency Field2": 103.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 6,
  "Currency Field2": 124.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 7,
  "Currency Field2": 145.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 8,
  "Currency Field2": 166
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 9,
  "Currency Field2": 186.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 10,
  "Currency Field2": 207.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 11,
  "Currency Field2": 228.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 12,
  "Currency Field2": 249
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 13,
  "Currency Field2": 269.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 14,
  "Currency Field2": 290.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 15,
  "Currency Field2": 311.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 16,
  "Currency Field2": 332
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 17,
  "Currency Field2": 352.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 18,
  "Currency Field2": 373.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 19,
  "Currency Field2": 394.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 20,
  "Currency Field2": 415
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 21,
  "Currency Field2": 435.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 22,
  "Currency Field2": 456.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 23,
  "Currency Field2": 477.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 24,
  "Currency Field2": 498
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 25,
  "Currency Field2": 518.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 26,
  "Currency Field2": 539.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 27,
  "Currency Field2": 560.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 28,
  "Currency Field2": 581
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 29,
  "Currency Field2": 601.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 30,
  "Currency Field2": 622.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 31,
  "Currency Field2": 643.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 32,
  "Currency Field2": 664
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 33,
  "Currency Field2": 684.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 34,
  "Currency Field2": 705.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 35,
  "Currency Field2": 726.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 36,
  "Currency Field2": 747
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 37,
  "Currency Field2": 767.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 38,
  "Currency Field2": 788.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 39,
  "Currency Field2": 809.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 40,
  "Currency Field2": 830
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 41,
  "Currency Field2": 850.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 42,
  "Currency Field2": 871.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 43,
  "Currency Field2": 892.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 44,
  "Currency Field2": 913
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 45,
  "Currency Field2": 933.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 46,
  "Currency Field2": 954.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 47,
  "Currency Field2": 975.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 48,
  "Currency Field2": 996
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 49,
  "Currency Field2": 1016.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 50,
  "Currency Field2": 1037.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 51,
  "Currency Field2": 1058.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 52,
  "Currency Field2": 1079
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 53,
  "Currency Field2": 1099.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 54,
  "Currency Field2": 1120.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 55,
  "Currency Field2": 1141.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 56,
  "Currency Field2": 1162
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 57,
  "Currency Field2": 1182.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 58,
  "Currency Field2": 1203.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 59,
  "Currency Field2": 1224.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 60,
  "Currency Field2": 1245
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 61,
  "Currency Field2": 1265.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 62,
  "Currency Field2": 1286.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 63,
  "Currency Field2": 1307.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 64,
  "Currency Field2": 1328
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 65,
  "Currency Field2": 1348.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 66,
  "Currency Field2": 1369.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 67,
  "Currency Field2": 1390.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 68,
  "Currency Field2": 1411
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 69,
  "Currency Field2": 1431.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 70,
  "Currency Field2": 1452.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 71,
  "Currency Field2": 1473.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 72,
  "Currency Field2": 1494
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 73,
  "Currency Field2": 1514.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 74,
  "Currency Field2": 1535.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 75,
  "Currency Field2": 1556.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 76,
  "Currency Field2": 1577
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 77,
  "Currency Field2": 1597.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 78,
  "Currency Field2": 1618.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 79,
  "Currency Field2": 1639.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 80,
  "Currency Field2": 1660
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 81,
  "Currency Field2": 1680.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 82,
  "Currency Field2": 1701.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 83,
  "Currency Field2": 1722.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 84,
  "Currency Field2": 1743
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 85,
  "Currency Field2": 1763.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 86,
  "Currency Field2": 1784.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 87,
  "Currency Field2": 1805.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 88,
  "Currency Field2": 1826
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 89,
  "Currency Field2": 1846.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 90,
  "Currency Field2": 1867.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 91,
  "Currency Field2": 1888.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 92,
  "Currency Field2": 1909
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 93,
  "Currency Field2": 1929.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 94,
  "Currency Field2": 1950.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 95,
  "Currency Field2": 1971.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 96,
  "Currency Field2": 1992
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 97,
  "Currency Field2": 2012.75
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 98,
  "Currency Field2": 2033.5
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 99,
  "Currency Field2": 2054.25
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 100,
  "Currency Field2": 2075
 },
 {
  "Date Field2": "08\/26\/24",
  "Number Field2": 101,
  "Currency Field2": 2095.75
 }
]
}

Hope helps a bit.

k4b1n · August 27, 2024, 3:24pm

@John.He
I am wondering on the approach of setting C101 in the example you provided above.

I do not know how much row will Sheet1 take in advance and how much row will Sheet2 take in advance

There might be any of the cases which might hold positive for the excel file

Sheet1 range might be A1:C2, A1:C3…. Or A1:C1001
Sheet2 range might be A1:C2. A1:C3,… or A1:C101

Thus range A1:C101 does not hold true for both sheets in every instances.

Please guide me.

amjad.sahi · August 27, 2024, 6:21pm

@k4b1n,

Thanks for providing details about your requirements.

For your case, you may try the workaround (see the following sample code below) to accomplish your task.
e.g
Sample code:

        final Workbook workbook = new Workbook("d:\\files\\TwoSheetWorkbook.xlsx");
        JsonSaveOptions jsonSaveOptions = new JsonSaveOptions();
        jsonSaveOptions.setSkipEmptyRows(true);
        jsonSaveOptions.setExportEmptyCells(false);

        //Sheet1
        //Get max data row
        int endRow = workbook.getWorksheets().get(0).getCells().getMaxDataRow() +1;
        //Set export area
        jsonSaveOptions.setExportArea(CellArea.createCellArea("A1", "C" + endRow));
        jsonSaveOptions.setSheetIndexes(new int[]{0});
        //JSON String conversion from Stream
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.save(byteArrayOutputStream, jsonSaveOptions);
        String string = new String(byteArrayOutputStream.toByteArray(), "UTF-8");
        System.out.println("{\n" + "\"Sheet1\":" + string + ",");
        byteArrayOutputStream.reset();

        //Sheet2
        //Get max data row
        endRow = workbook.getWorksheets().get(1).getCells().getMaxDataRow() +1;
        //Set export area
        jsonSaveOptions.setExportArea(CellArea.createCellArea("A1", "C" + endRow));        
        jsonSaveOptions.setSheetIndexes(new int[]{1});
        //JSON String conversion from Stream
        byteArrayOutputStream = new ByteArrayOutputStream();
        workbook.save(byteArrayOutputStream, jsonSaveOptions);
        string = new String(byteArrayOutputStream.toByteArray(), "UTF-8");
        System.out.println("\"Sheet2\":" + string + "\n}");

Hope, this helps a bit.