Hide unused rows/columns

Aspose.Cells Product Family
1

Is it possible to hide all unused rows/columns? For example, lets say I am using A1:C3. Is it possible to hide ALL rows below row 3 and all columns to the right of column C so that when the user opens the worksheet, they will ONLY see A1:C3?

2

@cometrics

Thanks for using Aspose APIs.

Please see the following code. It achieves your requirement and it is an optimized code because it generates much smaller output Excel file which opens quickly in Microsoft Excel.

Download Link:
Output Excel File.zip (5.6 KB)

C#

// Create workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Note down standard width and height of the cells
double dc = ws.Cells.StandardWidth;
double dr = ws.Cells.StandardHeight;

//Set both of them to 0
ws.Cells.StandardHeight = 0;
ws.Cells.StandardWidth = 0;

//Restore first three rows and columns
for(int i=0; i<3; i++)
{
    ws.Cells.SetRowHeight(i, dr);
    ws.Cells.SetColumnWidth(i, dc);
}

//Save the output Excel file
wb.Save("output.xlsx");

Screenshot:

3

That worked great! I also appreciate the changes you made to keep the file size small. The only downside of this approach is that you are explicitly setting row/column height on every visible row and column. As such, if you put in some text into A2 and set it to a large font, then it gets cut-off. It doesn’t auto-size. But that works just fine for me.

4

@cometrics

Thanks for your feedback and using Aspose APIs.

You may employ Worksheet.AutoFitColumn() or Worksheet.AutoFitColumns() methods for this purpose. It is good to know that you liked the solution. Let us know if you encounter any other problem, we will look into it and help you further.

5

Hi,
I have used your above logic in order to achieve my req. but it’s partially worked for me.
my Req. -
Hide Unused columns after “y” …As per attached image…
Code:
Used above code , just changed limit to loop i.e.
//Restore first three rowsheet and columns
for (int i = 0; i < 25; i++)
{
sheet.Cells.SetRowHeight(i, dr);
sheet.Cells.SetColumnWidth(i, dc);
}
Result:
Using above code, i get only 25 * 25 columns ,rows… whereas I want ‘n’ no of rows by hiding Columns after Y. Let me know is this can be achieved by Aspose.cells…
image.png (7.8 KB)

Looking forward your help…
Thanks in advance!

6

@RenukaH,

Please create your expected file in MS Excel manually and share it with us, we will check and help you on how to do it via Aspose.Cells APIs.

PS. please zip the file prior attaching here.

7

Book1_sample.zip (9.5 KB)
PFA file.

8

@RenukaH,
You may try the following sample code and share the feedback.

// Load workbook
Workbook wb = new Workbook("book1_sample.xlsx");
int n = wb.Worksheets[0].Cells.MaxDataRow;

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Note down standard width of the cells
double dc = ws.Cells.StandardWidth;

//Set width to 0
ws.Cells.StandardWidth = 0;

//Restore first 25 columns
for (int i = 0; i < 25; i++)
    ws.Cells.SetColumnWidth(i, dc);

//Save the output Excel file
wb.Save("output.xlsx");
9

@ahsaniqbalsidiqui
Tried your code… output file is as below which is blank with 0 rows , columns…

Req: There is change in requirement.Limitation on no. of rows as well… Hide columns after “Y” and hide rows after 1001th row.

output.zip (7.0 KB)

10

@RenukaH,

See the following sample code to accomplish your task:
e.g.
Sample code:

 //Access first worksheet
            Worksheet ws = wb.Worksheets[0];
            
            //Note down standard width of the cells
            double dc = ws.Cells.StandardWidth;
            double dr = ws.Cells.StandardHeight;

            //Set both of them to 0
            ws.Cells.StandardHeight = 0;
            ws.Cells.StandardWidth = 0;

            //Restore first 25 columns
            for (int i = 0; i < 25; i++)
                ws.Cells.SetColumnWidth(i, dc);

            //Restore first 1001 rows
            for (int j = 0; j <= 1000; j++)
                ws.Cells.SetRowHeight(j, dr);

            //Save the output Excel file
            wb.Save("e:\\test2\\output.xlsx");

Hope, this helps a bit.

11

Hi ,
Still not worked for me…
Wel this is on hold … will re-visit once required…

Thanks for quick turn around !!

output1.zip (7.9 KB)

12

@RenukaH,

I am using the following sample code while instantiating new workbook. The output file (attached) is fine tuned:
e.g
Sample code:

 // Load new workbook
            Workbook wb = new Workbook();
            
            //Access first worksheet
            Worksheet ws = wb.Worksheets[0];
            
            //Note down standard width and  height of the cells
            double dc = ws.Cells.StandardWidth;
            double dr = ws.Cells.StandardHeight;

            //Set both of them to 0
            ws.Cells.StandardHeight = 0;
            ws.Cells.StandardWidth = 0;

            //Restore first 25 columns
            for (int i = 0; i < 25; i++)
                ws.Cells.SetColumnWidth(i, dc);

            //Restore first 1001 rows
            for (int j = 0; j <= 1000; j++)
                ws.Cells.SetRowHeight(j, dr);



            //Save the output Excel file
            wb.Save("e:\\test2\\out1.xlsx");

files1.zip (7.2 KB)

Please run the exact code (above) to generate the output file and then check if you still find the issue with the output file. Also, if you use some different code and template file, kindly do paste your sample code and attach your files (you may zip the files prior attaching), we will check it soon.