Write Data from DataRowCollection to Excel File

[qwerty95]

I need to extract the data from my DataRowCollection and insert it into an Excel sheet. I also need to be able to merge column headers like so; Expected Result -



First two rows need to merge foreach Column.

5th Column needs to take up two cells.

And from column 6 onwards i need them to take up 3 cells.

Has anyone any idea how i achieve this?

I can write to CSV Easily but writing to excel is something i have never done. Any help would be greatly appreciated!


Here is my code so far;


Excel.Application excel = new Excel.Application();
excel.Visible = true;
Excel.Workbook wb = excel.Workbooks.Add();
Excel.Worksheet sh = wb.Sheets.Add();
sh.Name = strCompanyNo + "_PayComponentExceptionReport";
//excel.ActiveSheet.Name = "TestSheet";

DataRowCollection dr = sqlDataSet.Tables["PayComponentException"].Rows;
var colCount = sqlDataSet.Tables["PayComponentException"].Columns.Count;

//foreach(DataRow colHeader in dr)
//{
//Looping through each data row. adding column headers and new rows etc
//}


//Anything below is just test i have been doing to try and get it working

//sh.Cells[1 + 1, 1] = "Test";
//sh.Cells[1 + 1, 2] = "Test1";
//sh.Cells[1 + 1, 3] = "Test2";

//var names = new string[5, 2];
//names[0, 0] = "John";
//names[0, 1] = "Smith";
//names[1, 0] = "Tom";
//names[1, 1] = "Brady";

//sh.get_Range("A1", "A2").Merge();
//sh.Range[sh.Cells[1, 1], sh.Cells[2, 2]].Merge();





Thanks Mark.

Continue reading...