Problem with DeleteRange and Merged Cells

Aspose.Cells Product Family
1

Hi, I’ve encountered a bug with using DeleteRange() along with meged cells. My designer file, code, and resultant XLS file are here: ftp://ftp13VDTGY:xWrNKm@ftp.directsupply.com (FireFox doesn’t interpret the FTP addy right, use IE or explorer Sad). I’m using version 2.7.5.0.

After inserting my DataTable I sometimes have to delete an unneeded column but not touch some header text up above, so I’m using DeleteRange with ShiftType.Left to nuke the column and its header. This works great except when there are merged cells to the right of the deletion, their contents get shifted over one but the cell doesn’t get deleted, it’s wierd. When I manually do the same operation in Excel it works as expected.

Here’s my DeleteRange() call:


ws.Cells.DeleteRange(2, 0, 5, 0, ShiftType.Left);

2

Hi David,

Thanks for your report. I fixed this issue and upload the hotfix to

You can download it and have a try.

3

Thanks for the quick response man! I’ll give it a shot tomorrow morning.

-Dave

4

Cool, the new verison works correctly with the test template, but exhibits the old behavor on my real template about half the time. If I copy the contents of the template to a new excel file and save DeleteRange() will work just fine on the column, but it still screws up on the old file. See GoodTemplate and BadTemplate in ftp://ftp13VDTGY:xWrNKm@ftp.directsupply.com/ with a DeleteRange call of ws.Cells.DeleteRange(4, 1, 9, 1, ShiftType.Left); for each. I’ve uploaded the test page, too.

Hopefully it behaves the same way for you!

5

Fixed. Please download the hotfix again.

6
Laurence wrote:
Fixed. Please download

again.



Perfect! What in the heck was the difference between those two spreadsheets???

7

Just some cells with white spaces in the bad template. In previous version, the dll doesn't deal with this case. I fixed it in the new hotfix.

8

@DavidP,
Aspose.Cells has replaced Aspose.Excel which is discontinued and no more under active development. Aspose.Cells provides rich features to work with ranges and merged cells. You can format complete range, convert table to range, access maximum display range, search and replace data in a range, perform calculation on a range of cells, export range of cells to image, work with named ranges and merge/un-merge cells using Aspose.Cells. Following example demonstrates removing named range.

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

// Get all the worksheets in the book.
WorksheetCollection worksheets = workbook.getWorksheets();

// Get the first worksheet in the worksheets collection.
Worksheet worksheet = workbook.getWorksheets().get(0);

// Create a range of cells.
Range range1 = worksheet.getCells().createRange("E12", "I12");

// Name the range.
range1.setName("MyRange");

// Set the outline border to the range.
range1.setOutlineBorder(BorderType.TOP_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.LEFT_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));
range1.setOutlineBorder(BorderType.RIGHT_BORDER, CellBorderType.MEDIUM, Color.fromArgb(0, 0, 128));

// Input some data with some formattings into
// a few cells in the range.
range1.get(0, 0).setValue("Test");
range1.get(0, 4).setValue("123");

// Create another range of cells.
Range range2 = worksheet.getCells().createRange("B3", "F3");

// Name the range.
range2.setName("testrange");

// Copy the first range into second range.
range2.copy(range1);

// Remove the previous named range (range1) with its contents.
worksheet.getCells().clearRange(11, 4, 11, 8);
worksheets.getNames().removeAt(0);

// Save the excel file.
workbook.save(dataDir + "RANRange_out.xls");

// Print message
System.out.println("Process completed successfully");

Following is a list of articles for more information on ranges and merging:
Named Ranges
Merging and Unmerging Cells
Tables and Ranges
Formatting a Range of Cells
Search and Replace Data in a Range
Calculate the sum of a range of cells
Export Range of Cells in a Worksheet to Image

Get the latest free trial version here:
Aspose.Cells for .NET(Latest version)

You can download a ready to run solution here that can be used to test different features of this product without any coding.