Clear data in excel sheet

Aspose.Cells Product Family
1

Hi Team,

I have one doubt to clear the data.
We have an option in Aspose to clear the data in excel.

sheet.Cells.ClearContents(0, 0, sheet.Cells.MaxRow, sheet.Cells.MaxColumn);

I need to delete complete data on sheet if the data is available , other wise i don’t want to delete. Please let me know is there any option in aspose.cells.

Regards,
Bharat.v

2

Iam getting the below error if i use the above statement.

at ئ.ܨ.ݜ(Int32 ݝ, Int32 ڬ, Int32 ݞ, Int32 ڭ)
at Aspose.Cells.Cells.ClearContents(Int32 startRow, Int32 startColumn, Int32 endRow, Int32 endColumn)
at ScriptMain.CreateSourceFile(String AsposeLicencePath, String strFileName, String strFilePath, String stroutputFilePath)
at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

3

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

You can check if the sheet is empty or not by checking worksheet.Cells.Count property, if it is 0, then it means sheet is empty and if it is greater than 0, it means sheet is not empty.

Then you can delete all the rows using the following approach.

This method will delete all the rows inside a workbook and this is elegant method because it uses range to delete only those cells which has data.

Please see the code below and I have attached the source and output xlsx file.

Please also see the screenshot for your reference.

C#

string filePath = @“F:\source.xlsx”;


Workbook workbook = new Workbook(filePath);


foreach (Worksheet worksheet in workbook.Worksheets)

{

  Range maxRange = worksheet.Cells.MaxDisplayRange;<br>


worksheet.Cells.DeleteRange(maxRange.FirstRow, maxRange.FirstColumn, maxRange.RowCount, maxRange.ColumnCount, ShiftType.Up);

}


workbook.Save(filePath + “.out.xlsx”);



Screenshot: