Preserve values when unmerging cells

[Sven Grundmann]

Hi,

on exporting data from an Excel sheet, I want to unmerge all merged cells since otherwise I loose the data in all but the first cell of a merged area.

Since there is no option for this on ExportDataTable(), my idea was to unmerge the cells before exporting them, maybe explicitly set the values. 

Please see the following example code, it runs fine this way but looses the data still. If I simply try to store the cells of the merged area, the loop breaks.

Dim workbook As IWorkbook = excelengine.Excel.Workbooks.Open(Server.MapPath("/upload/") + "upload.xls")

Dim sheet As Syncfusion.XlsIO.IWorksheet = workbook.Worksheets(0)

For Each rng As IRange In sheet.UsedRange

If rng.IsMerged Then

'Dim cells = rng.MergeArea.Cells.ToList       'Breaks as soon as you uncomment this line

rng.UnMerge()

'For Each cell In cells

' cell.Value = rng.Value

'Next

End If

Next

Dim newParts As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)

Anyone got an idea for that? Best way would be either an option on .ExportDataTable() or on the .unmerge() command that doesn't break the loop.

[Sven Grundmann]

Hi,

on exporting data from an Excel sheet, I want to unmerge all merged cells since otherwise I loose the data in all but the first cell of a merged area.

Since there is no option for this on ExportDataTable(), my idea was to unmerge the cells before exporting them, maybe explicitly set the values. 

Please see the following example code, it runs fine this way but looses the data still. If I simply try to store the cells of the merged area, the loop breaks.

Dim workbook As IWorkbook = excelengine.Excel.Workbooks.Open(Server.MapPath("/upload/") + "upload.xls")

Dim sheet As Syncfusion.XlsIO.IWorksheet = workbook.Worksheets(0)

For Each rng As IRange In sheet.UsedRange

If rng.IsMerged Then

'Dim cells = rng.MergeArea.Cells.ToList       'Breaks as soon as you uncomment this line

rng.UnMerge()

'For Each cell In cells

' cell.Value = rng.Value

'Next

End If

Next

Dim newParts As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)

Anyone got an idea for that? Best way would be either an option on .ExportDataTable() or on the .unmerge() command that doesn't break the loop.

Just found out that accessing the property 'MergeArea' breaks 'UsedRange'

For Each rng As IRange In sheet.UsedRange

If rng.IsMerged Then

    Dim cells = rng.MergeArea.Cells    ' Breaks the loop, 'UsedRange' changes

End If

Next

Greetings,

Sven

[Dilli Babu Nandha Gopal]

Hi Sven,

Thank you for using Syncfusion products.

Since you have created a Direct-Trac Incident for this query. We will address your query in that incident and you can follow up it for further updates.

Regards,

Dilli babu.