Subscribe to C# codes        RSS Feed
-----

Export a DataSet's Table as an .XLS Excel document

Icon 1 Comments
Hey, I recently had to export some database information in the form of Excel. There's a lightweight way to do it, but no one has documented it well. The closest I got to doing it that way was here but unfortunately the guy didn't explain everything. I hope he forgets how to do it himself down the road =p


Anyway, the lamer way to do it I *believe* requires MS office to be install on the computer which makes this approach extremely cumbersome in a business setting (hopefully I'm wrong though).

Here's my code:
public class WorkbookEngine
    {
        public static void ExportDTToExcel(System.Data.DataTable dt)  
        {  
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();  
            app.Visible = false;

            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;  
 
            // Headers.  
            for (int i = 1; i < dt.Columns.Count; i++)  
                ws.Cells[1, i] = dt.Columns[i].ColumnName;  
 
            // Content.  
            for (int i = 0; i < dt.Rows.Count; i++)  
            {
                for (int j = 1; j < dt.Columns.Count; j++)
                    ws.Cells[i + 2, j] = dt.Rows[i][j].ToString();
                //break;
            }
 
            try
            {
                string pathToReports = "C:\\reports";
                if (!Directory.Exists(pathToReports))
                    Directory.CreateDirectory(pathToReports);
                // Lots of options here. See the documentation.  
                wb.SaveAs(pathToReports + "\\Report.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8 );  // Microsoft.Office.Interop.Excel.XlFileFormat.xl

                wb.Close();
                app.Quit(); 
                // then open it
                System.Diagnostics.Process.Start(pathToReports + "\\Report.xls");
            }
            catch (Exception e)
            {
                wb.Close();
                app.Quit();
                int elf = 0;
            }
        }
    }



Important: Add a reference to the COM "Microsoft Excel 12.0" or one of the other ones if you prefer.

It's usage is simple enough, just call it and pass in a DataTable.

WorkbookEngine.ExportDTToExcel(mydataset.Tables[0]);



It requires write access to your C:/ drive. I figured this implementation worked best for my needs. Oh, it opens the .xls document after it's been saved, you might want to modify that line. Let me know if this entry is incomplete.

1 Comments On This Entry

Page 1 of 1

demausdauth Icon

06 February 2012 - 09:49 AM
Great article!
0
Page 1 of 1

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

October 2014

S M T W T F S
   1234
567891011
12131415161718
19202122232425
2627282930 31