Minimum number of columns in GridJS spreadsheet

Aspose.Cells Product Family
1

When exporting a GridJsWorkbook to JSON, we’re seeing that cols for each sheet is always at least 16, even when the source excel file has fewer columns. Similarly, when converting an Excel file with a single row, the output from ExportToJson says that rows len is 13.

{
    "actname": "Sheet1",
    "actrow": 0,
    "actcol": 0,
    "showtabs": true,
    "uniqueid": "d0cdcd17763c5c1eb763e7746a629fc8f6f13c88d71647421fddd31e3af01378",
    "filename": "book1",
    "data": [
        {
            "name": "Sheet1",
            "sheetid": "s0",
            "showGrid": true,
            "sprotected": false,
            "canselectlocked": true,
            "displayRight2Left": false,
            "canselectunlocked": true,
            "styles": [
                {
                    "textwrap": false,
                    "color": "#000000",
                    "valign": "bottom",
                    "font": {
                        "name": "Calibri",
                        "size": 12,
                        "bold": false,
                        "italic": false
                    }
                }
            ],
            "cols": {
                "len": 16,
                "0": {
                    "width": 90
                },
                "1": {
                    "width": 90
                },
                "2": {
                    "width": 90
                },
                "3": {
                    "width": 90
                },
                "4": {
                    "width": 90
                },
                "5": {
                    "width": 90
                },
                "6": {
                    "width": 90
                },
                "7": {
                    "width": 90
                },
                "8": {
                    "width": 90
                },
                "9": {
                    "width": 90
                },
                "10": {
                    "width": 90
                },
                "11": {
                    "width": 90
                },
                "12": {
                    "width": 90
                },
                "13": {
                    "width": 90
                },
                "14": {
                    "width": 90
                },
                "15": {
                    "width": 90
                }
            },
            "rows": {
                "len": 13,
                "height": 21,
                "0": {
                    "cells": {
                        "0": {
                            "text": "Hello",
                            "style": 0
                        }
                    }
                }
            },
            "rowinfo": {
                "0": {
                    "h": 21
                }
            }
        }
    ]
}

Is there a way to tell the GridJs export code to not add in empty columns/cells? I.e. if there’s only one column/row in the worksheet, then the output should be:

{
    "actname": "Sheet1",
    "actrow": 0,
    "actcol": 0,
    "showtabs": true,
    "uniqueid": "d0cdcd17763c5c1eb763e7746a629fc8f6f13c88d71647421fddd31e3af01378",
    "filename": "book1",
    "data": [
        {
            "name": "Sheet1",
            "sheetid": "s0",
            "showGrid": true,
            "sprotected": false,
            "canselectlocked": true,
            "displayRight2Left": false,
            "canselectunlocked": true,
            "styles": [
                {
                    "textwrap": false,
                    "color": "#000000",
                    "valign": "bottom",
                    "font": {
                        "name": "Calibri",
                        "size": 12,
                        "bold": false,
                        "italic": false
                    }
                }
            ],
            "cols": {
                "len": 1,
                "0": {
                    "width": 90
                }
            },
            "rows": {
                "len": 1,
                "height": 21,
                "0": {
                    "cells": {
                        "0": {
                            "text": "Hello",
                            "style": 0
                        }
                    }
                }
            },
            "rowinfo": {
                "0": {
                    "h": 21
                }
            }
        }
    ]
}
2

@jrf.att,

We noticed the issue as you mentioned using the sample file. When converting an Excel file with a single row to JSON via Aspose.Cells.GridJs, the output from ExportToJson says that rows len is 13. We need to investigate your issue/requirements in details.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSGRIDJS-811

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

3

@jrf.att
You may try to set the below two properties:
Conifg.EmptySheetMaxRow
(default value is 12)
and
Config.EmptySheetMaxCol
(default value is 16)

Please check if it can meet your requirements. We will also update the online document to include those config settings.

4

Hey @peter.zhou, thanks for that tip. I’ve tried setting those values to 0 (and 1, same result), and the number of rows/columns from GridJS is now closer to what is in the spreadsheet, but still not exactly the same.

For context, we’re using Aspose.Cells to convert to both PDF and GridJS. The former will be used for some backend stuff, and the latter will be displayed in the client application. I want to ensure that we have the exact same rows/columns in GridJS as we do in the PDF.

I’m using https://www.nab.com.au/content/dam/nab/documents/reports/corporate/2022-sustainability-data-pack.xlsx as my test file. When exporting to PDF, I get the expected number of rows and columns included, based on the PrintArea specified for each sheet.

I have the following code to generate the PDF and GridJS JSON:

        Config.FileCacheDirectory = outputDir;
        Config.PictureCacheDirectory = imgCacheDir.FullName;
        Config.EmptySheetMaxCol = 0;
        Config.EmptySheetMaxRow = 0;

        Workbook workbook = new Workbook(source);

        foreach (Worksheet sheet in workbook.Worksheets)
        {
            if (pageSetup.PrintArea != null)
            {
                // Delete all rows/columns outside the view range.
                Cells cells = sheet.Cells;
                Aspose.Cells.Range printRange = cells.CreateRange(pageSetup.PrintArea);
                Aspose.Cells.Range maxRange = cells.MaxDisplayRange;
                Console.WriteLine("Column Count " + cells.Columns.Count);
                Console.WriteLine("Row Count " + cells.Rows.Count);
                Console.WriteLine("print area " + pageSetup.PrintArea);
                Console.WriteLine("print range columns " + printRange.ColumnCount);
                Console.WriteLine("print range columns " + printRange.RowCount);

                cells.DeleteColumns(printRange.ColumnCount, cells.Columns.Count - printRange.ColumnCount, true);
                cells.DeleteRows(printRange.RowCount, cells.Rows.Count - printRange.RowCount, true);

                Console.WriteLine("FINAL COLS " + cells.Columns.Count);
                Console.WriteLine("FINAL ROWS " + cells.Rows.Count);
                Console.WriteLine();
            }
        }

        PdfSaveOptions pdfSaveOptions = new PdfSaveOptions
        {
            OnePagePerSheet = true,
        };
        workbook.Save(Path.Combine(outputDir, "document.pdf"), pdfSaveOptions);

        GridJsWorkbook jsWorkbook = new GridJsWorkbook();
        jsWorkbook.ImportExcelFile(documentId, workbook);
        jsonContents = jsWorkbook.ExportToJson();

For the first sheet, “Home”, I get the following in the console:

Column Count 41
Row Count 3
print area A1:F68
print range columns 6
print range columns 68
FINAL COLS 6
FINAL ROWS 3

In the JSON output, I get cols.len is 7 and rows.len is 69.

I thought it might be an off-by-one issue, where GridJS is adding an extra row/col, but for sheet 3, “Customer>”, I get the following in the console:

Column Count 63
Row Count 21
print area A1:C22
print range columns 3
print range columns 22
FINAL COLS 3
FINAL ROWS 21

data.json has cols.len as 5 (so off by two), and row.len as 21 (which is off by one, but less instead of more). There are similar issues with the rest of the sheets.

Any ideas what could be going on here? Is there another config option I should use?

5

@jrf.att
Just set Config.IgnoreEmptyContent = false, then it will use the same Range as MaxDisplayRange(in GridJs, its default value is true, as in ui side, it considers more for user experience. If the last row or column contains cells with no value and formula but has custom style then we will not show this row/column when this value is true).

6

@peter.zhou In the spreadsheet above, the “Workforce” sheet has a MaxDisplayRange of ~16000 columns, which is way too large. Rather than display range, it would be good if there was a config option to respect the print area of the sheet. Eg, if the print area is A1:F10, the PDF export already includes just that area; if GridJS could also include just that area (and drop other data) that’d be perfect.

For now, I’ve been able to achieve basically the same outcome by overriding the len of rows and cells in the output JSON:

// Modifies the GridJS JSON data such that the data displayed in the client aligns with what's in the PDF export.
static string FixGridJsJson(string json, Workbook workbook)
{
    JObject root = JObject.Parse(json);
    JArray? jsonData = root["data"] as JArray;

    // Update the number of rows/cols in GridJS JSON to match the print area of the PDF.
    for (var i = 0; i < jsonData!.Count; i++)
    {
        JObject? jsonSheet = jsonData[i] as JObject;
        Worksheet sheet = workbook.Worksheets[i];
        string printArea = sheet.PageSetup.PrintArea;
        int lastRow, lastCol;
        CellsHelper.CellNameToIndex(printArea.Split(':')[1], out lastRow, out lastCol);
        jsonSheet!["rows"]!["len"] = lastRow + 1;
        jsonSheet!["cols"]!["len"] = lastCol + 1;
    }

    return JsonConvert.SerializeObject(root);
}
7

we will add the new setting Config.UsePrintArea

/// Sets whether to use PageSetup.PrintArea for the UI display range when the worksheet has PageSetup setting for PrintArea.
/// the default value is false .
public static bool UsePrintArea

8

Thanks @peter.zhou!

9

@jrf.att,
You are welcome. If you have any questions, please feel free to contact us.

10

@jrf.att,

We are pleased to inform you that the ticket (logged earlier as CELLSGRIDJS-811) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.5) that we plan to release before the end of this week. You will be notified when the next version is released.

11

@jrf.att
It is available now .Please update to v23.5 and set Config.UsePrintArea = true;
you need to update for both server side GridJs dll through nuget package and client side js through npmjs.

12

Thanks all, tested it out and works as expected!

13

@jrf.att,
You are welcome.