DeleteRange Question

Aspose.Cells Product Family
1

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

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              2
              
            

            

              
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.

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              3
              
            

            

              
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

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              4
              
            

            

              
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.

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              5
              
            

            

              
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

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              6
              
            

            

              
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
 
 

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              7
              
            

            

              
Hi Kai,

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

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              8
              
            

            

              
Laurence,
 
 this is great news. Thanks again for your support
 
 Regards
 
 Kai

            


            

              
              
              
            


            

                
                
              


          

          

            

              

              


              
                  
                  
              9
              
            

            

              
@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.