Click here to Skip to main content
14,599,112 members
Rate this:
Please Sign up or sign in to vote.
See more:
I've got this type of data in my Datatable. Imagine that ("2019_WK1", "2019_WK2", "2019_WK3") is the three column names and so i need to take split 2019(year) in one big merged cell, (Upper "WK1", "WK2", "WK3") column cells in C#.

using Excel = Microsoft.Office.Interop.Excel;

Below are my datatable code:

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
Excel.Range chartRange;

xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

//add data
xlWorkSheet.Cells[1, 1] = "Retailer";
xlWorkSheet.Cells[1, 2] = "Brand";
xlWorkSheet.Cells[1, 3] = "2019_WK1";
xlWorkSheet.Cells[1, 4] = "2019_WK2";
xlWorkSheet.Cells[1, 5] = "2019_WK3";


xlWorkSheet.Cells[2, 1] = "Lulu";
xlWorkSheet.Cells[2, 2] = "Perisil";
xlWorkSheet.Cells[2, 3] = "25";
xlWorkSheet.Cells[2, 4] = "26";
xlWorkSheet.Cells[2, 5] = "23";

xlWorkSheet.Cells[3, 1] = "Lulu";
xlWorkSheet.Cells[3, 2] = "Ariel";
xlWorkSheet.Cells[3, 3] = "26";
xlWorkSheet.Cells[3, 4] = "28";
xlWorkSheet.Cells[3, 5] = "29";

xlWorkSheet.Cells[4, 1] = "Danube";
xlWorkSheet.Cells[4, 2] = "Omo";
xlWorkSheet.Cells[4, 3] = "27";
xlWorkSheet.Cells[4, 4] = "28";
xlWorkSheet.Cells[4, 5] = "30";

xlWorkSheet.Cells[5, 1] = "Danube";
xlWorkSheet.Cells[5, 2] = "Tide";
xlWorkSheet.Cells[5, 3] = "24";
xlWorkSheet.Cells[5, 4] = "23";
xlWorkSheet.Cells[5, 5] = "29";

xlWorkSheet.Cells[6, 1] = "Bin Dawood";
xlWorkSheet.Cells[6, 2] = "Perisil";
xlWorkSheet.Cells[6, 3] = "26";
xlWorkSheet.Cells[6, 4] = "27";
xlWorkSheet.Cells[6, 5] = "28";

xlWorkBook.SaveAs("d:\\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);

MessageBox.Show("File created !");
}

What I have tried:

I need like this type of columns

____________________________
| 2019 |
|___________________________|
| WK1 l WK2 l WK3 |
|________l________l_________|
| 25 | 26 | 23 |
| 27 | 29 | 24 |
___24________25_______22_____
Posted
Updated 28-May-19 21:58pm
Comments
Mohibur Rashid 28-May-19 16:54pm
   
Have you tried regular expression?
Member 14376506 28-May-19 16:57pm
   
I dont know about regular expression, actually i want to do it under loops

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

If I understand you correctly, you want to merge the header above three of your columns. This has been answered here: Merging Cells in Excel using C# - Stack Overflow[^]
   
v2
Comments
Member 14376506 29-May-19 5:04am
   
But i want to split it first(Three columns) and after that merged in one big cell named(2019) but how to split
Stefan_Lang 3-Jun-19 4:08am
   
You can't split a column, you can only insert new colomns before or after existing ones.
Member 14376506 3-Jun-19 5:52am
   
can you send me any link about multiple data tables export in excel sheets
Stefan_Lang 4-Jun-19 3:31am
   
Erm.. no. For one the last time I did a program connecting to Excel was in the 90s. Second, I was using COM/OLE2 for that purpose. 3. Your request is about as unclear/unspecific as it can get. You'd be better off googling for it yourself and picking up the links that are relevant to whatever you have in mind.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100