We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Preserve values when unmerging cells

Thread ID:

Created:

Updated:

Platform:

Replies:

118292 Feb 20,2015 03:18 PM UTC Feb 23,2015 05:56 AM UTC ASP.NET Web Forms 2
loading
Tags: XlsIO
Sven Grundmann
Asked On February 20, 2015 03:18 PM UTC

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
Replied On February 20, 2015 04:24 PM UTC

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 [Syncfusion]
Replied On February 23, 2015 05:56 AM UTC

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.



CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;