c# Writing datatable to excel
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: c# Writing datatable to excel

  1. #1
    Join Date
    Jan 2011
    Posts
    11

    c# Writing datatable to excel

    I can open my workbook and copy my selected sheets within it, rename them and insert data, however not all of the data is inserted, its misses the data in one column. It includes the column title but not the data it holds. Every other column works fine but I can't understand why it doesn't work. The code I have is:
    Code:
     Excel.Application oXL;
                Excel.Workbook oWB;
                Excel.Worksheet oSheet;
                Excel.Range oRange;
    
                // Start Excel and get Application object. 
                oXL = new Excel.Application();
    
                // Set some properties 
                oXL.Visible = true;
                oXL.DisplayAlerts = false;
    
                // Get a new workbook. 
                string location = @"C:\Documents and Settings\Nick\My Documents\Visual Studio 2008\Projects\Tourny Manager (Export Test)\Test.xls";
                oWB = oXL.Workbooks.Open(location, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
               
     
                // Process the DataTable 
                // BE SURE TO CHANGE THIS LINE TO USE *YOUR* DATATABLE 
                DataTable dt = this.tournyManagerDataSet.Tournament;
                try
                {
                    dt.Columns.Remove("England Squash Number");
                    dt.Columns.Remove("Home Phone");
                    dt.Columns.Remove("Mobile Phone");
                    dt.Columns.Remove("Email");
                    dt.Columns.Remove("Email 2");
                    dt.Columns.Remove("T-Shirt Size");
                    dt.Columns.Remove("Position");
                }
                catch
                {
                }
                
    
               
    
                DataRow[] Rows;
                int rowCount;
    
    
    
       //BU19
    
                 Rows = dt.Select("[Age Group] = 'BU19' AND [Tournament] = '" + tChoice.Text + "'");
                 if (BU19.Text == "8 Monrad")
                 {
                     // Get the active sheet 
                     oSheet = (Excel.Worksheet)oWB.Sheets["8 M"];
                     oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
                     oSheet = (Excel.Worksheet)oWB.Sheets["8 M (2)"];
                     oSheet.Name = "BU19";
    
                     rowCount = 1;
                     foreach (DataRow dr in Rows)
                     {
                         rowCount += 1;
                         for (int i = 1; i < dt.Columns.Count + 1; i++)
                         {
                             // Add the header the first time through 
                             if (rowCount == 2)
                             {
                                 oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
                             }
                             oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
                         }
                     }
    
                     // Resize the columns 
                     oRange = oSheet.get_Range(oSheet.Cells[1, 115],
                                   oSheet.Cells[rowCount, dt.Columns.Count + 115]);
                     oRange.EntireColumn.AutoFit();
    
                 }
                 else if (BU19.Text == "16 Monrad")
                 {
                     // Get the active sheet 
                     oSheet = (Excel.Worksheet)oWB.Sheets["16 M"];
                     oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
                     oSheet = (Excel.Worksheet)oWB.Sheets["16 M (2)"];
                     oSheet.Name = "BU19";
    
    
                     rowCount = 1;
                     foreach (DataRow dr in Rows)
                     {
                         rowCount += 1;
                         for (int i = 1; i < dt.Columns.Count + 1; i++)
                         {
                             // Add the header the first time through 
                             if (rowCount == 2)
                             {
                                 oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
                             }
                             oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
                         }
                     }
    
                     // Resize the columns 
                     oRange = oSheet.get_Range(oSheet.Cells[1, 115],
                                   oSheet.Cells[rowCount, dt.Columns.Count + 115]);
                     oRange.EntireColumn.AutoFit();
                 }
                 else if (BU19.Text == "32 Monrad")
                 {
                     // Get the active sheet 
                     oSheet = (Excel.Worksheet)oWB.Sheets["32 M"];
                     oSheet.Copy(Type.Missing, oWB.Sheets["Winners"]);
                     oSheet = (Excel.Worksheet)oWB.Sheets["32 M (2)"];
                     oSheet.Name = "BU19";
    
    
                     rowCount = 1;
                     foreach (DataRow dr in Rows)
                     {
                         rowCount += 1;
                         for (int i = 1; i < dt.Columns.Count + 1; i++)
                         {
                             // Add the header the first time through 
                             if (rowCount == 2)
                             {
                                 oSheet.Cells[1, i + 115] = dt.Columns[i - 1].ColumnName;
                             }
                             oSheet.Cells[rowCount, i + 115] = dr[i - 1].ToString();
                         }
                     }
    
                     // Resize the columns 
                     oRange = oSheet.get_Range(oSheet.Cells[1, 115],
                                   oSheet.Cells[rowCount, dt.Columns.Count + 115]);
                     oRange.EntireColumn.AutoFit();
                 }
    That is the consistent code throughout it. It is repeated as I don't know a better way to work it, but all bar copying the last columns data works fine.

    Thanks,

  2. #2
    Join Date
    Jan 2011
    Posts
    11

    Re: c# Writing datatable to excel

    Don't worry, found the problem. It was because when testing other queries the column had been missed out somehow so wasn't being copied into the datatable dt
    Its taken me all day to narrow down the problem to that one step but I've eventually got there

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center