none
Write Data from DataRowCollection to Excel File RRS feed

  • Question

  • 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.

    Monday, June 10, 2019 12:28 PM

All replies

  • Hello,

    In regards to the header, why not keep a new Excel file available (copy the new Excel file to another folder and provide an appropriate name) that has the column merges perdone? Then all you need to do is populate cells values. This is less complicated then attempting to perform the merge columns at runtime.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 10, 2019 1:31 PM
    Moderator
  • Every time this program is run it may be run for a different company. So i couldnt keep a template because no two companys will have the same information. Each column that i need to take up 3 cells is a payComponent. These differ from company to company. So this company may only have 10 PayComponents but another company may have 25 PayComponents.. 

    Having a template may not be the best in this case. Thanks for your answer though.

    Thanks Mark


    • Edited by qwerty95 Monday, June 10, 2019 1:57 PM Spelling Error
    Monday, June 10, 2019 1:55 PM
  • Then consider using SpreadSheetLight to do the merge cells. The library is totally free and installed via NuGet.

    Example

    Public Sub Merge1()
      Dim sl As New SLDocument()
    
      sl.SetCellValue("B1", "This is a merged cell")
    
      ' merge all cells in the cell range B1:D1
      sl.MergeWorksheetCells("B1", "D1")
    
      sl.SaveAs("MergeCells.xlsx")
    End Sub

    See my MSDN code sample for SpreadSheetLight

    https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, June 10, 2019 2:26 PM
    Moderator
  • Hi qwerty,

    Thank you for posting here.

    Based on your description, you want to write data from data collection to excel file.

    You could use SpreadSheetLight to solve it. Although karen' code is right, her solution is for vb.net not for c#.

    You could refer to the following link.

    https://spreadsheetlight.com/downloads/samplecode/MergeCells.cs

    Note:This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; Therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 11, 2019 2:12 AM
    Moderator
  • Hi

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 25, 2019 8:44 AM
    Moderator