Yet another issue regarding cell merging?


#1

Cell merging setting is not copied when the contents of a range is copied. To reproduce, try the following:

----------------------
// create an Excel
Excel excel = new Excel();

// apply some formatting
Range range = excel.Worksheets[0].Cells.CreateRange(4, 4, 1, 1);
range.RowHeight = 40;

range = excel.Worksheets[0].Cells.CreateRange(3, 3, 3, 3);
range.Merge();

for (int i = 2; i < 7; i++)
for (byte j = 2; j < 7; j++)
excel.Worksheets[0].Cells[i, j].PutValue(i + "/" + j);

excel.Worksheets[0].Cells[3, 3].PutValue("**DESTINATION**");

// copy horizontal range of cells
Range srcRange = excel.Worksheets[0].Cells.CreateRange(2, 6, false);
Range destRange = excel.Worksheets[0].Cells.CreateRange(12, 6, false);
destRange.Copy(srcRange);

// copy vertical range of cells
srcRange = excel.Worksheets[0].Cells.CreateRange(2, 6, true);
destRange = excel.Worksheets[0].Cells.CreateRange(9, 6, true);
destRange.Copy(srcRange);

excel.Worksheets[0].Cells[3, 3].PutValue("**SOURCE**");
excel.Save("sample1.xls", FileFormatType.Default);
----------------------

Then look at sample1.xls, compare areas marked as **SOURCE** and **DESTINATION**.

P.S. Sorry for lots of posts and thanks for your patience.


#2

I will check this issue. You can try to set merged cells after copying range.


#3

The problem is that I don't know what cells are merged. I just load the user's template, then copy portions of a worksheets to the locations needed, and fill in the data. But after copying, cell merging is lost.

I tried to use Cells.MergedCells property, but it returns an empty ArrayList.

One more interesting issue:

----------------------------------
// create an Excel

Excel excel = new Excel();

// create some content
for (int i = 1; i <= 3; i++)
for (int j = 1; j <= 8; j++)
excel.Worksheets[0].Cells[i - 1, (byte)(j - 1)].PutValue(" " + (i * j));

Range range = excel.Worksheets[0].Cells.CreateRange(0, 0, 1, 3);
range.Merge();
range = excel.Worksheets[0].Cells.CreateRange(0, 4, 1, 2);
range.Merge();

// delete the topmost row
excel.Worksheets[0].Cells.DeleteRow(0);
excel.Save("sample.xls", FileFormatType.Default);
----------------------------------

Before the highlighted line is executed, the topmost row contains some merged cells. DeleteRow() doesn't modify cell merging (this bug was partially fixed in 2.4.0.1 hotfix). I can workaround it by saving/reopening excel object as follows:

----------------------------------
// create an Excel

Excel excel = new Excel();

// create some content
for (int i = 1; i <= 3; i++)
for (int j = 1; j <= 8; j++)
excel.Worksheets[0].Cells[i - 1, (byte)(j - 1)].PutValue(" " + (i * j));

Range range = excel.Worksheets[0].Cells.CreateRange(0, 0, 1, 3);
range.Merge();
range = excel.Worksheets[0].Cells.CreateRange(0, 4, 1, 2);
range.Merge();

// save/reopen
excel.Save("temp.xls", FileFormatType.Default);
excel.Open("temp.xls");

// delete the topmost row
excel.Worksheets[0].Cells.DeleteRow(0);
excel.Save("sample.xls", FileFormatType.Default);
----------------------------------

This is obviously a bug, as save/open sequence should not change the state of the object (this is not critical, though).


#4

Almost forget!

Cells.ClearRange(), doesn't clear cell merging. If it shouldn't, there must be some another method that clears cell merging setting.


#5

Please try Range.UnMerge method to remove merging setting.


#6

I must have Range object that contains merged cells to do this.
But I don't know what cells are merged, so I can't create this Range object to UnMerge() it.


#7

I will add a Cells.UnMerge method to enable you to remove merged cells in a range of a worksheet.


#8

Thank you!