Clear Formatting Only on Rows/Columns with no Data

Aspose.Cells Product Family
1

Hello,

I need to clear any cell formatting on empty rows at the top/left/bottom/right of a spreadsheet. Only cells with no content but that are formatted (color/borders/etc.) are to be affected. This is to clear cells that may be accidentally formatted many rows down or columns to the right in the spreadsheet, causing potentially many extra empty pages to be printed.

See this image: ExcelFormattedCells.png (6.4 KB)

Here, only the first page contains any data; that is all I want to be printed. The default, in Excel anyway, is to print the 4 pages.

Using Excel I was able to use the Application.WorksheetFunction to use CountA to return me a collection of cells with data; I used that to divide and conquer through the rows until I found where the empty rows ended or started and from there I was able to clear the formatting.

How can I get Aspose to calculate the COUNTA for me - I couldn’t find an match to the Appplication.WorksheetFunction.

Thanks,

Sheri

2

@sheri_steeves

Thanks for using Aspose APIs.

Please try the following sample code. It uses the ClearFormats() methods to clear the cell formatting. Please check the sample input and output Excel files used inside the code for a reference. Please also see the screenshot that shows the effect of the code on input Excel file.

Download Link:
Sample Excel Files.zip (12.1 KB)

C#

Workbook wb = new Workbook("ss.xlsx");

Worksheet ws = wb.Worksheets[0];

CellArea ca = CellArea.CreateCellArea("A1", "M30");
ws.Cells.ClearFormats(ca);

wb.Save("output.xlsx");

Screenshot:

3

@shakeel.faiz

Sorry if I was not clear in what I was trying to do.

The code as specified would clear all the formatting in that entire range. I am looking to clear any formatting found outside of the cells area that has data, and leave any formatting in the area that contains data.

See this image: ExcelFormattedCells-ClearedFormatting.png (7.2 KB)

I could do brute force for loops through the rows/columns to check each cell but that seems like a very inefficient way of handling this scenario.

In Excel automation I can call COUNTA on a group of rows/columns and it returns the count of cells with data. This function is much more efficient with Exel COM than looping through each cells. I keep dividing my range in half and counting successively smaller areas until I find areas with no data or run out of rows/columns; and then clear any area left over.

Is there an equivalent of calling COUNTA in Aspose?

Meanwhile, I will try some timing tests using the the brute force loop to test the time it takes.

Thanks
Sheri

4

@sheri_steeves

For your requirement, you may use the methods:

Cells.DeleteBlankRows();
Cells.DeleteBlankColumns();

Another way is to detect the minimum range that contains cells with data is:

Cells.MinDataRow/MinDataColumn/MaxDataRow/MaxDataColumn

You can use those values to set the Print Area so that Microsoft Excel can print the range only.

If you want to export the sheet to Pdf, you can use:

PdfSaveOptions.PrintingPageType = PrintingPageType.IgnoreStyle;

In this way if all cells in one page have no data, then this page will be omitted.

5

@shakeel.faiz,

Awesome, I don’t know how I missed those properties! Looks exactly like what I need. I will test and get back to you.

6

@sheri_steeves

Sure. Please feel free to let us know if you face any other issue. We will be glad to help you further.