.NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge

[ToddinMN]

I have spreadsheets containing multiple merged columns. Our C# code is designed to map defined column names and import data using licensed Aspose.Cells for .NET version 19.5.0.0.

Issue: it is not possible to UnMerge columns without having duplicate headers for each of the extra merged empty columns. Using Excel UnMerge has the same result as wkSheet.Cells.UnMerge, both create duplicate Column headers of original merged column name with empty content.
Why? This seems unproductive to me... Can someone provide an updated UnMerge sample that can resolve this?

Detailed example below: Merged spreadsheet has column 'Header 1' as 5 merged columns, column 'Header 2 as 2 merged columns, (underscores in my sample are empty content for viewing format) | col1 | col2 | col3 | col4 | col5 | col6 | col7 | | Header1 | Header 2 | | Data1____ | Data 2___ |

UnMerge via Excel or Aspose.Cells.NET UnMerge(..) and looks like this with columns denoted | col1__| col2_ | col3__ | col4__ | col5 | col6__ | col7_ | | Header 1 | Header 1 | Header 1 | Header 1 | Header 1 | Header 2 | Header 2 | | Data 1| __ | __ | __ | __ | Data2 | __ |

How can I UnMerge so it looks like a good spreadsheet without duplicated columns: | col1 | col2 | | Header 1 | Header 2 | | Data 1 | Data2 | or It would even be okay it they were present but empty (so data remains in first column) and columns 2 through 5 would be completely empty with no duplicate headers - that would also solve this issue. | col1__ | col2 | col3 | col4 | col5 | col6 | col7 | | Header 1 | | _ | ____ | | Header 2 | | | Data 1_| | ____ | | _ | Data2 | ____ |

I tried using the Aspose sample code from https://docs.aspose.com/cells/net/detect-merged-cells-in-a-worksheet/ Which has a flaw - should remove these lines or no data is exported: 10 // Clear its contents 11 wkSheet.Cells.Clear();

Question/Project:
Can someone help me modify the sample code to not copy the duplicate column names? Maybe analyze the number of cells that are merged from each cell merged area in the sample for loop, and re-index the next column for the next actual data column.
I could guess and try coding - but I am betting people will want the ability to UnMerge columns 'without' the duplicate column headers. This might be a good case for a new method like 'UnMergeToSingleCol', but do not want to update our license to get fix, unless this has already been fixed in a newer version and I can justify to management to update our version???

Thanks, Todd

[amjad-sahi]

@ToddinMN,

Aspose.Cells follows MS Excel standards and specifications when merging and unmerging cells. Aspose.Cells works the same way as MS Excel does regarding merging and unmerging commands.

It would even be okay it they were present but empty (so data remains in first column) and columns 2 through 5 would be completely empty with no duplicate headers - that would also solve this issue.
| col1______ | col2 | col3 | col4 | col5 | col6_____ | col7 |
| Header 1 | _____ | ____ | _____ | ____ | Header 2 | ____ |
| Data 1___| _____ | ____ | _____ | ____ | Data2___ | ____ |

This is what both MS Excel and Aspose.Cells do or should do. I tested and it works as expected. May be it is due to your sample code or logic you would have written for it, so you should debug and correct it. Could you please share your template Excel file containing those merged range of cells. We will test to unmerge the cells if we could find the issue you mentioned. Moreover, since you are using an older version of the APIs (Aspose.Cells for .NET version 19.5.0.0), so we recommend you to kindly try using our newer version of the APIs (Aspose.Cells for .NET v23.2) if it makes any difference.

I tried using the Aspose sample code from https://docs.aspose.com/cells/net/detect-merged-cells-in-a-worksheet/
Which has a flaw - should remove these lines or no data is exported:
10 // Clear its contents
11 wkSheet.Cells.Clear();

You might be right. But, we just demonstrated what Clear method could do --> it clears existing cells contents/data. If you don't want to clear the existing contents, you may skip or comment out this line.

We also recommend you to post your queries or issues in dedicated forums where we assist users efficiently.

[ToddinMN]

The table you pasted below from my sample is NOT what Aspose (from the Aspose UnMerge sample url I pasted in my query) or Excel do – both your sample and Excel duplicate columns so col 1 to 5 will all have the same Header1 as I stated. Not just the first column as I put in my “It would be okay…” sample. The sample data you pasted is what I want to see.

I literally copy/pasted code from your sample – no changes other than assigning the filepath.

Here is a paste of the result of your sample code on a real spreadsheet: Line# Vendor part number Vendor part number Product description Product description Product description Product description Product description Product description Category Category Qty Unit MSRP Unit MSRP Extended MSRP Extended MSRP Unit reseller price Unit reseller price Extended reseller price Extended reseller price You can clearly see the Product description columns were 5 merged cells – unmerge and suddenly we have 5 columns named Product description and other duplicates. with the data in the first wish is correct, the issue is our application processes the file by column name and Aspose can’t load the workbook with duplicate column names. Yes, this is what Excel does – but think about it – WHY would you want the same column name over columns that are empty – it defeats the UnMerge call concept.

If I worked for Aspose – I would bring this issue to management and create an ‘enhancement’ to have an UnMerge call like this: wkSheet.Cells.UnMerge(frow, fcol, trows, tcols, noduplicates = false); // so the new noduplicates default it Excel functionality, with duplicates – but allows a user to pass true and have the first column (with data) have the column name and remaining columns be cleared, or even better - removed.

And regarding demonstrating the .Clear call – your sample is to provide usage of UnMerge – so if you Clear the content – how can you demonstrate the UnMerge call – I tried it – it clears the entire document so no, it doesn’t UnMerge… My suggestion would be to not include it in this demo, or comment it out. Just trying to help…

Regarding your ‘forums’ – tell your web development team to put a link on your site – there is no longer a ‘forum’ link on any mouse-over menu or bottom of site – just blogs which isn’t the same… I will try posting there. Considering how obtrusive this function is, literally failing to load the worksheet since it has duplicate column names… thought it might be a good idea to share the issue with you guys. Been using Aspose for many years… 😊

Todd

From: Amjad Sahi @.> Sent: Friday, February 17, 2023 3:25 AM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!KiOiJaWVmvUMAjPObuMQxVcAn-RhVzCa47HfYT0A8ygA3FJbHfNH_cDZdWxhtJlBLNFuDP1SFZMF4GDaVvLhq3nMnCk$,

Aspose.Cells follows MS Excel standards and specifications when merging and unmerging cells. Aspose.Cells works the same way as MS Excel does regarding merging and unmerging commands.

It would even be okay it they were present but empty (so data remains in first column) and columns 2 through 5 would be completely empty with no duplicate headers - that would also solve this issue.

| col1__ | col2 | col3 | col4 | col5 | col6_____ | col7 |

| Header 1 | | ____ | | | Header 2 | |

| Data 1_| ___ | _ | ____ | ___ | Data2 | ____ |

This is what both MS Excel and Aspose.Cells do or should do. I tested and it works as expected. May be it is due to your sample code or logic you would have written for it, so you should debug and correct it. Could you please share your template Excel file containing those merged range of cells. We will test to unmerge the cells if we could find the issue you mentioned. Moreover, since you are using an older version of the APIs (Aspose.Cells for .NET version 19.5.0.0), so we recommend you to kindly try using our newer version of the API (Aspose.Cells for .NET v23.2) if it makes any difference.

I tried using the Aspose sample code from https://docs.aspose.com/cells/net/detect-merged-cells-in-a-worksheet/https://urldefense.com/v3/__https:/docs.aspose.com/cells/net/detect-merged-cells-in-a-worksheet/__;!!Ppvbp3ya850!KiOiJaWVmvUMAjPObuMQxVcAn-RhVzCa47HfYT0A8ygA3FJbHfNH_cDZdWxhtJlBLNFuDP1SFZMF4GDaVvLhJVzl3Zw$

Which has a flaw - should remove these lines or no data is exported:

10 // Clear its contents

11 wkSheet.Cells.Clear();

You might be right. But, we just demonstrated what Clear method could do --> it clears existing cells contents/data. If you don't want to clear the existing contents, you may skip or comment out this line.

We also recommend you to post your queries or issues in dedicated forumshttps://urldefense.com/v3/__https:/forum.aspose.com/c/cells/9__;!!Ppvbp3ya850!KiOiJaWVmvUMAjPObuMQxVcAn-RhVzCa47HfYT0A8ygA3FJbHfNH_cDZdWxhtJlBLNFuDP1SFZMF4GDaVvLhjnTJHiE$ where we assist users efficiently.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1434367776__;Iw!!Ppvbp3ya850!KiOiJaWVmvUMAjPObuMQxVcAn-RhVzCa47HfYT0A8ygA3FJbHfNH_cDZdWxhtJlBLNFuDP1SFZMF4GDaVvLhFG_Zbkk$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJQQ733CIVVGN2JJXW3WX47XRANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!KiOiJaWVmvUMAjPObuMQxVcAn-RhVzCa47HfYT0A8ygA3FJbHfNH_cDZdWxhtJlBLNFuDP1SFZMF4GDaVvLhsjWgdz0$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[amjad-sahi]

@ToddinMN,

I simply tested your scenario and created a test (case) code, it works fine and as expected. I am using Aspose.Cells for .NET v23.2. Here is my complete sample code: e.g. Sample code:

//Create a new (empty) workbook
Workbook workbook = new Workbook();

//Create a Worksheet and get the first (default) sheet.
Worksheet worksheet = workbook.Worksheets[0];

//Create a Cells object or fetch all the cells.
Cells cells = worksheet.Cells;

//Get the first cell
Cell cell = cells["A1"];
//Input value
cell.PutValue("A1Value");

//Get the B1 cell
cell = cells["B1"];
//Input value
cell.PutValue("B1Value");

//Get the C1 cell
cell = cells["C1"];
//Input value
cell.PutValue("C1Value");

//Get the D1 cell
cell = cells["D1"];
//Input value
cell.PutValue("D1Value");

//Get the E1 cell
cell = cells["E1"];
//Input value
cell.PutValue("E1Value");

//Merge cells (A1:E1)
cells.Merge(0, 0, 1, 5);

//Save the file
workbook.Save("e:\\test2\\out1.xlsx");//Check the file it has merged cell which only contains "A1Value", other cells data is removed

//Now open the existing file
Workbook workbook1 = new Workbook("e:\\test2\\out1.xlsx");

//Unmerge the cells in A1:E1 range.
workbook1.Worksheets[0].Cells.UnMerge(0, 0, 1, 5);

//Save the file.
workbook1.Save("e:\\test2\\out2.xlsx");//Check the file it has unmerged cells. Also, A1 cell has "A1Value" and all other cells (B1:E1) have blank/null values.

out1.xlsx out2.xlsx

Please try my sample code and if you still find any issue, please provide complete use case, i.e., input and output files, sample (runnable) code. We will check your issue soon.

And regarding demonstrating the  .Clear call – your sample is to provide usage of UnMerge – so if you Clear the content – how can you demonstrate the UnMerge call – I tried it – it clears the entire document so no, it doesn’t UnMerge… My suggestion would be to not include it in this demo, or comment it out

We will consider your suggestion and may remove the line of code you pointed out in the sample code segment.

Regarding your ‘forums’ – tell your web development team to put a link on your site – there is no longer a ‘forum’ link on any mouse-over menu or bottom of site – just blogs which isn’t the same… I will try posting there.  Considering how obtrusive this function is, literally failing to load the worksheet since it has duplicate column names… thought it might be a good idea to share the issue with you guys

The link is there under "Support" menu but link's text is "Free Support", so you may use it. See the screenshot for your reference.

[ToddinMN]

Hi again,

I tried cut/pasting your code into a windows form quick app I made to test with, your code sample works. It is not created the same way apparently as the vendor provided file is.

I am guessing the Vendor uses a template that has the columns pre-merged? Not something I can find out. I wiped the vendor info from their file, renamed the content in a couple columns to protect confidentiality. This file fails – maybe you can try it out and figure out how to get your code to correctly work with this file. I’ve attached VendorFile.xlsx and VendorFileOut.xlsx

Free Support doesn’t sound like a forum… thanks… I’ve known about the free support, a long time ago I used it, swear it was called a Forum then – maybe not, a couple years ago now.

Thanks, Todd

From: Amjad Sahi @.> Sent: Monday, February 20, 2023 12:10 PM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZlYUOdGzg$,

I simply tested your scenario and created a test (case) code, it works fine and as expected. I am using Aspose.Cells for .NET v23.2. Here is my complete sample code: e.g. Sample code:

//Create a new (empty) workbook

Workbook workbook = new Workbook();

//Create a Worksheet and get the first (default) sheet.

Worksheet worksheet = workbook.Worksheets[0];

//Create a Cells object or fetch all the cells.

Cells cells = worksheet.Cells;

//Get the first cell

Cell cell = cells["A1"];

//Input value

cell.PutValue("A1Value");

//Get the B1 cell

cell = cells["B1"];

//Input value

cell.PutValue("B1Value");

//Get the C1 cell

cell = cells["C1"];

//Input value

cell.PutValue("C1Value");

//Get the D1 cell

cell = cells["D1"];

//Input value

cell.PutValue("D1Value");

//Get the E1 cell

cell = cells["E1"];

//Input value

cell.PutValue("E1Value");

//Merge cells (A1:E1)

cells.Merge(0, 0, 1, 5);

//Save the file

workbook.Save("e:\test2\out1.xlsx");//Check the file it has merged cell which only contains "A1Value", other cells data is removed

//Now open the existing file

Workbook workbook1 = new Workbook("e:\test2\out1.xlsx");

//Unmerge the cells in A1:E1 range.

workbook1.Worksheets[0].Cells.UnMerge(0, 0, 1, 5);

//Save the file.

workbook1.Save("e:\test2\out2.xlsx");//Check the file it has unmerged cells. Also, A1 cell has "A1Value" and all other cells (B1:E1) have blank/null values.

out1.xlsxhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/files/10786879/out1.xlsx__;!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZlqQK_Qvc$ out2.xlsxhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/files/10786882/out2.xlsx__;!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZl83c09co$

Please try my sample code and if you still find any issue, please provide complete use case, i.e., input and output files, sample (runnable) code. We will check your issue soon.

And regarding demonstrating the .Clear call – your sample is to provide usage of UnMerge – so if you Clear the content – how can you demonstrate the UnMerge call – I tried it – it clears the entire document so no, it doesn’t UnMerge… My suggestion would be to not include it in this demo, or comment it out

We will consider your suggestion and may remove the line of code you pointed out in the sample code segment.

Regarding your ‘forums’ – tell your web development team to put a link on your site – there is no longer a ‘forum’ link on any mouse-over menu or bottom of site – just blogs which isn’t the same… I will try posting there. Considering how obtrusive this function is, literally failing to load the worksheet since it has duplicate column names… thought it might be a good idea to share the issue with you guys

The link is there under "Support" menu but link's text is "Free Support", so you may use it. See the screenshot for your reference. [Image removed by sender. sc_shot1]https://urldefense.com/v3/__https:/user-images.githubusercontent.com/17720471/220175777-55820887-0568-494a-8283-6f2682f0e87d.png__;!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZlxj3YKXw$

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1437392228__;Iw!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZlMVJT5fY$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJWQNVCQLRJIED3XM6TWYOXNZANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!O46RPBvBZDoJBcLQWME8DYU9vMDqcmaT6f5_3gujVkmBjf4qaWWgHTuqezn2WXOnB0B2T2mFrbiXtnA3qZZlCb9OARE$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[amjad-sahi]

@ToddinMN ,

I could not spot/find your attachments "VendorFile.xlsx and VendorFileOut.xlsx". Please attach the files here so we could test your scenario/case a bit.

Free Support doesn’t sound like a forum… thanks…
I’ve known about the free support, a long time ago I used it, swear it was called a Forum then – maybe not, a couple years ago now

Yes, previously it was known as forums. But now since we have two types of forums, i.e., free forums and paid forums. So, we changed to Free Support and Paid Support.

[ToddinMN]

Hi,

I zipped them this time.

Thank you, Todd

From: Amjad Sahi @.> Sent: Tuesday, February 21, 2023 2:42 AM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKTcDoJTo$ ,

I could not spot/find your attachments "VendorFile.xlsx and VendorFileOut.xlsx". Please attach the files here so we could test your scenario/case a bit.

Free Support doesn’t sound like a forum… thanks…

I’ve known about the free support, a long time ago I used it, swear it was called a Forum then – maybe not, a couple years ago now

Yes, previously it was known as forums. But now since we have two types of forums, i.e., free forums and paid forums. So, we changed to Free Support and Paid Support.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1438057036__;Iw!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKd3ooplc$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJXR4W2AZ3KSYWYIDITWYR5UJANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKGSGUk90$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[amjad-sahi]

@ToddinMN ,

I am afraid, I still could not spot your attachment(s) on this page. Could you please share screenshot to show where you have attached the zipped archive? Alternatively, you may upload to some file sharing service (dropbox, Google drive, etc.) and share the Download link to get the file seamlessly (without using any kind of credentials).

[ToddinMN]

VendorFile.zip I have attached the zip file to my GitHub account.

[ToddinMN]

Attached to my Github account entry for this issue.

From: Amjad Sahi @.> Sent: Tuesday, February 21, 2023 2:42 AM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKTcDoJTo$ ,

I could not spot/find your attachments "VendorFile.xlsx and VendorFileOut.xlsx". Please attach the files here so we could test your scenario/case a bit.

Free Support doesn’t sound like a forum… thanks…

I’ve known about the free support, a long time ago I used it, swear it was called a Forum then – maybe not, a couple years ago now

Yes, previously it was known as forums. But now since we have two types of forums, i.e., free forums and paid forums. So, we changed to Free Support and Paid Support.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1438057036__;Iw!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKd3ooplc$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJXR4W2AZ3KSYWYIDITWYR5UJANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!IlSjOJUDwEsskT3rSW5lMRc6fwVgkofVoFBExAgVrbQk6WxFEYqOpapP8beizpElxt1u6Awv-4Exc28PeCMKGSGUk90$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[amjad-sahi]

@ToddinMN ,

Thanks for the file.

I checked your file and found the issue is only with first (headers) row. Both Aspose.Cells and MS Excel work the same way when unmerging the headers row, i.e., duplicate values (headers) appear in all relevant cells of the merged range/area instead of retaining left-most cell's value. I also noticed it works as expected when unmerging all other merged ranges after first row. How did you create such a file, it does seems the file is created in MS Excel but with some other tool? Anyways, we got to workaround the issue via some relevant code especially for the first (headers) row. See and try the following sample code to cope with it: e.g. Sample code:

            Workbook wb = new Workbook("e:\\test2\\VendorFile.xlsx");
            var cells = wb.Worksheets[0].Cells;
            Cell cell = null;
            Row row = cells.Rows[0];
            //First browse only first row which are headers and unmerge ranges.
           // get each merged cell and its range/area
            for (IEnumerator ie = row.GetEnumerator(); ie.MoveNext();)
            {
                cell = (Cell)ie.Current;
                if (cell.IsMerged)
                {
                        string val = cell.StringValue;//Get the value
                        var range = cell.GetMergedRange();
                        IEnumerator ie1 = range.GetEnumerator();
                        while (ie1.MoveNext())
                        {
                            Cell cell1 = (Cell)ie1.Current;
                            cell1.PutValue(null);
                        }
                        range.UnMerge();
                    cell.PutValue(val);//re-insert value                    
                }
            }
            //your existing code
            // Create an arraylist object
            ArrayList al = new ArrayList();
            // Get the merged cells list to put it into the arraylist object
            al = cells.MergedCells;
            // Define cellarea
            CellArea ca;
            // Define some variables
            int frow, fcol, erow, ecol, trows, tcols;
            // Loop through the arraylist and get each cellarea
            // To unmerge it
            for (int i = 0; i < al.Count; i++)
            {
                ca = new CellArea();
                ca = (CellArea)al[i];
                frow = ca.StartRow;
                fcol = ca.StartColumn;
                erow = ca.EndRow;
                ecol = ca.EndColumn;

                trows = erow - frow + 1;
                tcols = ecol - fcol + 1;
                cells.UnMerge(frow, fcol, trows, tcols);
            }

            wb.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

[ToddinMN]

Thanks, will try it. Appreciate the help…

From: Amjad Sahi @.> Sent: Tuesday, February 21, 2023 1:51 PM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkFxoxpk0$ ,

Thanks for the file.

I checked your file and found the issue is only with first (headers) row. Both Aspose.Cells and MS Excel work the same way when unmerging the headers row, i.e., duplicate values (headers) appear in all relevant cells of the merged range/area instead of retaining left-most cell's value. I also noticed it works as expected when unmerging all other merged ranges after first row. How did you create such a file, it does seems the file is created in MS Excel but with some other tool? Anyways, we got to workaround the issue via some relevant code especially for the first (headers) row. See and try the following sample code to cope with it: e.g. Sample code:

        Workbook wb = new Workbook("e:\\test2\\VendorFile.xlsx");

        var cells = wb.Worksheets[0].Cells;

        Cell cell = null;

        Row row = cells.Rows[0];

        //First browse only first row which are headers and unmerge ranges.

       // get each merged cell and its range/area

        for (IEnumerator ie = row.GetEnumerator(); ie.MoveNext();)

        {

            cell = (Cell)ie.Current;

            if (cell.IsMerged)

            {

                    string val = cell.StringValue;//Get the value

                    var range = cell.GetMergedRange();

                    IEnumerator ie1 = range.GetEnumerator();

                    while (ie1.MoveNext())

                    {

                        Cell cell1 = (Cell)ie1.Current;

                        cell1.PutValue(null);

                    }

                    range.UnMerge();

                cell.PutValue(val);//re-insert value

            }

        }

        //your existing code

        // Create an arraylist object

        ArrayList al = new ArrayList();

        // Get the merged cells list to put it into the arraylist object

        al = cells.MergedCells;

        // Define cellarea

        CellArea ca;

        // Define some variables

        int frow, fcol, erow, ecol, trows, tcols;

        // Loop through the arraylist and get each cellarea

        // To unmerge it

        for (int i = 0; i < al.Count; i++)

        {

            ca = new CellArea();

            ca = (CellArea)al[i];

            frow = ca.StartRow;

            fcol = ca.StartColumn;

            erow = ca.EndRow;

            ecol = ca.EndColumn;

            trows = erow - frow + 1;

            tcols = ecol - fcol + 1;

            cells.UnMerge(frow, fcol, trows, tcols);

        }

        wb.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1439010642__;Iw!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkvzHj5HU$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJUE33E3SIF74UPZ2MTWYUMARANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkkgRno24$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[ToddinMN]

Tried it – works like a charm.

Thanks! Todd

From: Amjad Sahi @.> Sent: Tuesday, February 21, 2023 1:51 PM To: aspose-free-consulting/projects @.> Cc: Todd R Nelson @.>; Mention @.> Subject: [EXTERNAL] Re: [aspose-free-consulting/projects] .NET Aspose.Cells UnMerge - how to do without duplicated column headers for each extra column after unmerge (Issue #282)

You don't often get email from @.*** Learn why this is importanthttps://aka.ms/LearnAboutSenderIdentification CAUTION! This is an EXTERNAL email originated from outside the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

---

@ToddinMNhttps://urldefense.com/v3/__https:/github.com/ToddinMN__;!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkFxoxpk0$ ,

Thanks for the file.

I checked your file and found the issue is only with first (headers) row. Both Aspose.Cells and MS Excel work the same way when unmerging the headers row, i.e., duplicate values (headers) appear in all relevant cells of the merged range/area instead of retaining left-most cell's value. I also noticed it works as expected when unmerging all other merged ranges after first row. How did you create such a file, it does seems the file is created in MS Excel but with some other tool? Anyways, we got to workaround the issue via some relevant code especially for the first (headers) row. See and try the following sample code to cope with it: e.g. Sample code:

        Workbook wb = new Workbook("e:\\test2\\VendorFile.xlsx");

        var cells = wb.Worksheets[0].Cells;

        Cell cell = null;

        Row row = cells.Rows[0];

        //First browse only first row which are headers and unmerge ranges.

       // get each merged cell and its range/area

        for (IEnumerator ie = row.GetEnumerator(); ie.MoveNext();)

        {

            cell = (Cell)ie.Current;

            if (cell.IsMerged)

            {

                    string val = cell.StringValue;//Get the value

                    var range = cell.GetMergedRange();

                    IEnumerator ie1 = range.GetEnumerator();

                    while (ie1.MoveNext())

                    {

                        Cell cell1 = (Cell)ie1.Current;

                        cell1.PutValue(null);

                    }

                    range.UnMerge();

                cell.PutValue(val);//re-insert value

            }

        }

        //your existing code

        // Create an arraylist object

        ArrayList al = new ArrayList();

        // Get the merged cells list to put it into the arraylist object

        al = cells.MergedCells;

        // Define cellarea

        CellArea ca;

        // Define some variables

        int frow, fcol, erow, ecol, trows, tcols;

        // Loop through the arraylist and get each cellarea

        // To unmerge it

        for (int i = 0; i < al.Count; i++)

        {

            ca = new CellArea();

            ca = (CellArea)al[i];

            frow = ca.StartRow;

            fcol = ca.StartColumn;

            erow = ca.EndRow;

            ecol = ca.EndColumn;

            trows = erow - frow + 1;

            tcols = ecol - fcol + 1;

            cells.UnMerge(frow, fcol, trows, tcols);

        }

        wb.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/aspose-free-consulting/projects/issues/282*issuecomment-1439010642__;Iw!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkvzHj5HU$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/A55NZJUE33E3SIF74UPZ2MTWYUMARANCNFSM6AAAAAAU5F3DTU__;!!Ppvbp3ya850!McW3LYKt6_v8u4FkgBwER_obCZmBde5senxhquFxfD7tmclb_xFsj99RkQH0_osywKaDQ15sczCIRjIg9nbkkgRno24$. You are receiving this because you were mentioned.Message ID: @.***>

NOTICE: This email message and any attachments hereto may contain confidential information. Any unauthorized review, use, disclosure, or distribution of such information is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy the original message and all copies of it.

[amjad-sahi]

@ToddinMN ,

Good to know that it works for your needs.