Regarding Hidden data

Aspose.Cells Product Family
1

Hi Team,
I am using aspose cells for grid js.
I have a Xlsx file and it contains the hidden rows and columns, can we show it ? How can we show the hidden data.

2

@sarathepiq

To show hidden rows and columns in an XLSX file using Aspose.Cells for Grid JS, you can set the IsHidden property of the rows and columns to false. Here’s a simple example of how to achieve this:

// Load the workbook
var workbook = new Aspose.Cells.Workbook("path/to/your/file.xlsx");

// Access the first worksheet
var worksheet = workbook.getWorksheets().get(0);

// Unhide all hidden rows
for (var i = 0; i <= worksheet.getCells().getMaxRow(); i++) {
    if (worksheet.getCells().getRowHeight(i) === 0) {
        worksheet.getCells().setRowHeight(i, 15); // Set to default height
    }
}

// Unhide all hidden columns
for (var j = 0; j <= worksheet.getCells().getMaxColumn(); j++) {
    if (worksheet.getCells().getColumnWidth(j) === 0) {
        worksheet.getCells().setColumnWidth(j, 10); // Set to default width
    }
}

// Save the modified workbook
workbook.save("path/to/your/output/file.xlsx");

This code iterates through all rows and columns in the first worksheet, checking if they are hidden and setting their height and width to default values if they are. Adjust the height and width values as needed based on your requirements.

3

@sarathepiq
you can use Aspose.Cells API first. to unhide all rows and columns in the spreadsheet file.
for example:

 var wb = new Workbook("D:\\codebase\\customerissue\\wb\\tempfromdownload\\hiddenrows.xlsx");
 var sheet = wb.Worksheets[0];
 sheet.Cells.UnhideRows(0, sheet.Cells.MaxRow + 1, -1);
 sheet.Cells.UnhideColumns(0, sheet.Cells.MaxColumn + 1, -1);
 wb.Save("D:\\codebase\\customerissue\\wb\\tempfromdownload\\showhiddenrows.xlsx");
4

@sarathepiq,

1). If you are using Aspose.Cells library, you may show any hidden row or column by calling the UnhideRow and UnhideColumn methods of the Cells collection respectively. It is also possible to use the Cells class’ UnhideRows and UnhideColumns methods to make multiple rows and columns visible. See the document with examples for your reference. Hiding and Showing Rows and Columns|Documentation

2). If you are using Aspose.Cells.GridJs, refer to the code snippet:

//Unhide rows

xs.sheet.unhideRows(sri,eri)
    // the parameters are:
	sri:the start row index 
	eri:the end row index

//Unhide columns
xs.sheet.unhideColumns(sci,eci)
    // the parameters are:
	sci:the start column index 
	eci:the end column index

See the document for your reference: https://docs.aspose.com/cells/java/aspose-cells-gridjs/how-to-use-gridjs-client-api/

1 Like
5

We have a file, but we don’t know the row and column indexes. I want to unhide all rows and columns in the sheet. Is this a client-side change or an API-level change?

6

@sarathepiq,

As suggested above, you may consider using the Aspose.Cells library to unhide all potentially hidden rows and columns first in the sheet (refer to the code snippet in the reply). Afterward, re-save the Excel file so it can be loaded into the Aspose.Cells.GridJs control for display within its grid matrix.

7

is this mentioned response is applicable to 24.6.2 version?
as we currently using the mentioned version in our application.
@amjad.sahi @amjad.sahi
The scenario is that our application extracts all the unhidden rows or columns in the excel file.
but when we view in the Aspose cells editor the hidden data is not shown.

Thank you,
Ganesh Kumar.

8

@Ghani0303,

Yes, it is also applicable to the version (24.6.2) that you are using. In case, you still find any issue, please share your template Excel file, sample code snippet and screenshots to demonstrate the issue, we will look into your issue soon.

9

I am using 24.6.2 version I got below error message .

“gridjs-spreadsheet”: “^24.6.2”,

Uncaught (in promise) TypeError: xs.sheet.unhideRows is not a function

10

@sarathepiq
we are sorry this API is available in the version later than v24.8
Please always update to the latest version.

11

I used below logic this will unhide the sheets and columns in the sheet.

export const unhideRowsAndColumns = (xs: any) => {
try {
if (xs.sheet.data.rows[““]) {
Object.keys(xs.sheet.data.rows[””]).forEach(rowIndex => {
const row = xs.sheet.data.rows[“_”][rowIndex];
if (row && row.hide !== undefined) {
row.hide = false;
}
});
}

if (xs.sheet.data.cols["_"]) {
  Object.keys(xs.sheet.data.cols["_"]).forEach(colIndex => {
    const col = xs.sheet.data.cols["_"][colIndex];
    if (col && col.hide !== undefined) {
      col.hide = false;
    }
  });
}

} catch (error) {
console.error(‘Error unhiding rows and columns:’, error);
}
};

I there any way to unhide the sheet which are in hidden ?

12

@sarathepiq
we have no client API to unhide a hidden sheet.
the hidden sheet data is always skipped in client.
You may use Aspose.Cells API to unhide the sheet in the spreadsheet file in server side,and then you can load and show the updated file in GridJs.

13

hi Peter, We have fixed this issue with our internal code. This issue is no longer active.

14

@Ghani0303
We are glad to hear that you have fixed this issue.
And we also opened a ticket for hiding and unhiding worksheets: CELLSGRIDJS-1969