Hi,
We are in the process of replacing excel with Aspose. However I have come across an example where the code is behaving differently and I was hoping you could help.
I have a spreadsheet that has merged cells in one row across two columns (Row 2 Column A & B). I want to insert an additional 3 columns in between the merged cells so that I end up with a merged cell across 5 columns. This part works OK. I then want to paste the contents of the first column into the 3 new columns. In excel this works leaving the single merged call across five columns. In aspose the merged cell is lost when I paste. (Note: I tried with no options and paste all options)
Attached is the initial workbook with the original sheet as well as the manually created sheet of what I want it to look like. Also attached are two other workbooks. One created via Aspose and the other created via Excel.
Any advice you can offer to get Aspose to behave the same as excel would be greatly appreciated.
Thanks, Julie
Aspose code to replicate is as follows:
Dim loWB As New Aspose.Cells.Workbook("C:\temp\mergedcells.xlsx")
Dim loSheet As Aspose.Cells.Worksheet = loWB.Worksheets(0)
loWB.Worksheets.Add()
Dim loNewSheet As Aspose.Cells.Worksheet = loWB.Worksheets(2)
loNewSheet.Copy(loSheet)
loNewSheet.Name = "Aspose version"
loNewSheet.Cells.InsertColumns(1, 3)
Dim loPasteFromRange As Aspose.Cells.Range = loNewSheet.Cells.CreateRange(0, 1, True)
Dim loPasteToRange As Aspose.Cells.Range = loNewSheet.Cells.CreateRange(1, 3, True)
loPasteToRange.Copy(loPasteFromRange)
loWB.Save("C:\temp\mergedcells_aspose.xlsx")
loWB = Nothing
Equivalent Excel Code I am trying to replace:
Dim loWB As Excel.Workbook = ExcelAppn.Workbooks.Open("C:\temp\mergedcells.xlsx")
Dim loSheet As Excel.Worksheet = loWB.Sheets(1)
loSheet.Copy(After:=loWB.Sheets(loWB.Sheets.Count))
Dim loNewSheet As Excel.Worksheet = loWB.Sheets(3)
loNewSheet.Name = "Excel version"
loNewSheet.Cells.Range("B:B", "D:D").Insert()
Dim loPasteToRange As Excel.Range = loNewSheet.Range("B:B", "D:D")
Dim loPasteFromRange As Excel.Range = loNewSheet.Range("A:A", "A:A")
loPasteFromRange.Copy(loPasteToRange)
loWB.SaveAs("C:\temp\mergedcells_excel.xlsx")
loWB.Close()
loWB = Nothing
mergedcells.zip (19.4 KB)