We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

DeleteRange Question

Hi,

quick question, I think I’m doing something wrong. I delete a series of rows on a couple of sheets. First, I did the following (within a loop):



for(int row…)
{
sheets.Cells.DeleteRow(row–);
while(RowIsEmpty(row) == true)
{
sheets.Cells.DeleteRow(row–);
}
}


Now I thought as DeleteRow seems to be rather costly, I would count the rows to delete in a loop like so



for(int row…)
{
int startRow = row + 1;
while(RowIsEmpty(startRow) == true)
{
startRow++;
}
sheet.Cells.DeleteRange(row, 0, startRow - 1, 0, ShiftType.Up);
}



Now when I do this, I can’t save the sheet as an Exception is thrown in the Record constructor. Maybe I’m doing something wrong calling DeleteRange()? Maybe something goes wrong deleting a range of cells?

What about adding shortcut methods such as DeleteColumns() and DeleteRows()?

Thanks

Regards

Kai

Hi Kai,

Thanks for your suggestion.

We will add DeleteRows/DeleteColumns methods in the future release. It will be available within about one week.

If possible, could you please zip and upload your file and sample code here? I will check this issue ASAP.

Laurence,

I would definitely want to send the sample to you, but

a) The Excel designer contains confidential data
b) A sample would be too complex to generate out of my production code

However what is strange is, that when I delete rows by calling DeleteRow() it works just fine, When I call DeleteRange(row1, 0, row2, 0, ShiftType.Up) the Save() fails. The parameters I pass to DeleteRange() seem OK to you (row2 >= row1)?

Regards

Kai

DeleteRange has some differences with DeleteRow. If row2>= row1, it should be ok.

I will check this issue. And your file and sample code will be very helpful.

Could you please:

1. Romove all data in designer but keep formulas
2. Tell me the range you want to delete

Thank you.

Laurence,

I will try to reduce my code in order to supply you with a “working” sample. Don’t know whether I can make it (today)

Regards

Kai

Laurence,

I am unable to cut down my sample in order to make it usable / executable on your side, I’m afraid. However I have emailed the resulting workbook to you. Maybe the stack trace may be helpful?



ERR: [19.04.2005 17:23:02] :: FSNParser.FSNParser.Run(String[] args) (D:\Work\IFRS-Reporting\FSNParser\FSNParser.cs, 158)
>>> Exception
>>> ----------------------------------------------------------------------
>>> Errors in Excel Save method:0
>>> ----------------------------------------------------------------------
>>>
>>> Inner Exception
>>> ----------------------------------------------------------------------
>>> Errors in Excel Save method:0
>>> ----------------------------------------------------------------------
>>>
>>> Inner Exception
>>> ----------------------------------------------------------------------
>>> Arithmetic operation resulted in an overflow.
>>> ----------------------------------------------------------------------
>>>
>>>
>>> Stack Trace
>>> ----------------------------------------------------------------------
>>> at Aspose.Excel.Worksheets.a(UInt32 A_0, Exception A_1)
>>> at Aspose.Excel.Worksheets.a(String A_0, SaveType A_1, FileFormatType A_2, HttpResponse A_3)
>>> at Aspose.Excel.Excel.Save(String fileName, FileFormatType fileFormatType)
>>> at DWS.ITMS.IFRS.ReportGenerator.SaveExcelFile(Excel excel, ExcelFile excelFile) in d:\work\ifrs-reporting\ifrslibrary\reportgenerator.cs:line 375
>>> at DWS.ITMS.IFRS.ReportGenerator.ProcessPackage() in d:\work\ifrs-reporting\ifrslibrary\reportgenerator.cs:line 338
>>> at DWS.ITMS.IFRS.ReportGenerator.GenerateReport(ReportPackage package, String portfolioID, DateTime reportDate, DateTime referenceDate, String outputDirectory) in d:\work\ifrs-reporting\ifrslibrary\reportgenerator.cs:line 280
>>> at FSNParser.FSNParser.Run(String[] args) in D:\Work\IFRS-Reporting\FSNParser\FSNParser.cs:line 134
>>> ----------------------------------------------------------------------
ERR: [19.04.2005 17:23:02] :: FSNParser.FSNParser.Run(String[] args) (D:\Work\IFRS-Reporting\FSNParser\FSNParser.cs, 158)
>>> Exception
>>> ----------------------------------------------------------------------
>>> Errors in Excel Save method:0
>>> ----------------------------------------------------------------------
>>>
>>> Inner Exception
>>> ----------------------------------------------------------------------
>>> Arithmetic operation resulted in an overflow.
>>> ----------------------------------------------------------------------
>>>
>>>
>>> Stack Trace
>>> ----------------------------------------------------------------------
>>> at Aspose.Excel.Worksheets.a(UInt32 A_0, Exception A_1)
>>> at Aspose.Excel.Worksheets.a(String A_0, FileFormatType A_1, MemoryStream& A_2)
>>> at Aspose.Excel.Worksheets.a(String A_0, SaveType A_1, FileFormatType A_2, HttpResponse A_3)
>>> ----------------------------------------------------------------------
ERR: [19.04.2005 17:23:02] :: FSNParser.FSNParser.Run(String[] args) (D:\Work\IFRS-Reporting\FSNParser\FSNParser.cs, 158)
>>> Exception
>>> ----------------------------------------------------------------------
>>> Arithmetic operation resulted in an overflow.
>>> ----------------------------------------------------------------------
>>>
>>>
>>> Stack Trace
>>> ----------------------------------------------------------------------
>>> at Aspose.Excel.Record.df…ctor(UInt16 row, Byte firstColumn, Byte lastColumn, ArrayList xfIndexList)
>>> at Aspose.Excel.Cells.d(bj A_0, FileFormatType A_1)
>>> at Aspose.Excel.Worksheet.c(bj A_0)
>>> at Aspose.Excel.Worksheets.b(bj A_0)
>>> at Aspose.Excel.Worksheets.a(UCOMIStream A_0)
>>> at Aspose.Excel.Record.ax.a(a0 A_0, h A_1, Worksheets A_2)
>>> at Aspose.Excel.Worksheets.a(String A_0, FileFormatType A_1, MemoryStream& A_2)
>>> ----------------------------------------------------------------------


What about the DeleteRows() / DeleteColumns() methods? I guess these could be helpful and speed up things on my side

Regards

Kai


Hi Kai,

DeleteRows/DeleteColumns methods will be available before the start of next week.

Laurence,

this is great news. Thanks again for your support

Regards

Kai

@Kai_Iske,
Aspose.Cells has replaced Aspose.Excel that is no more available now. Aspose.Cells provides rich features to insert/delete rows, columns, and ranges as per the requirement. Here is an example that can be used to demonstrate this feature.

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets[0];

// Create a range of cells based on H1:J4.
Range range = worksheet1.Cells.CreateRange("A1", "C4");

// Name the range.
range.Name = "MyRange";

// Input some data into cells in the range.
range[0, 0].PutValue("USA");
range[0, 1].PutValue("SA");
range[0, 2].PutValue("Turkey");
range[1, 0].PutValue("UK");
range[1, 1].PutValue("AUS");
range[1, 2].PutValue("Canada");
range[2, 0].PutValue("France");
range[2, 1].PutValue("Pakistan");
range[2, 2].PutValue("Egypt");
range[3, 0].PutValue("China");
range[3, 1].PutValue("Philipine");
range[3, 2].PutValue("Brazil");

worksheet1.Cells["D1"].Value = "Save Data 1";
worksheet1.Cells["D2"].Value = "Save Data 2";
worksheet1.Cells["D3"].Value = "Save Data 3";
worksheet1.Cells["D4"].Value = "Save Data 4";
workbook.Save("BeforeDeletingRange.xlsx");
// Getting the specified named range
Range range2 = workbook.Worksheets.GetRangeByName("MyRange");

workbook.Worksheets[0].Cells.DeleteRange(
    range.FirstRow,
    range.FirstColumn,
    range.FirstRow + range.RowCount-1,
    range.FirstColumn+ range.ColumnCount-1, ShiftType.Left);
workbook.Save("AfterDeletingRange.xlsx");

Here are the images of files before and after deleting a range of cells.

Here is an article that can be referred to for more information on working with ranges:
Create Access and Copy Named Ranges

The latest free trial version can be downloaded here:
Aspose.Cells for .NET(Latest version)

Here a comprehensive solution is available that can be used to test a variety of features supported by Aspose.Cells.