Writing data inthe Dataset into an Excel (with formattings)

Posted on August 1, 2008

woo themes Premium Wordpress layouts skins

The code below help to writing the data from the DataSet into a Excel. You can also do some style formatting in the Excel output. You need two important namespace to implement this concept, 'Microsoft.Office.Interop.Excel', 'Microsoft.VisualBasic'. You should also need to reference these interopable dll into you project.

This code need the MS Excel to be installed in the machine. As we are reference the Excel dll into the project.

using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic; 
 
        private void TOExcel(string strExcelPath, DataSet ds)
        {
            //declaring the application
            Microsoft.Office.Interop.Excel.Application oAppln;
            //declaring work book
            Microsoft.Office.Interop.Excel.Workbook oWorkBook;
            //declaring worksheet
            Microsoft.Office.Interop.Excel.Worksheet oWorkSheet;
            //declaring the range
            Microsoft.Office.Interop.Excel.Range oRange;
            Microsoft.Office.Interop.Excel.Range oCellRange;
            Microsoft.Office.Interop.Excel.Range oHeadRange;
 
            try
            {
                oAppln = new Microsoft.Office.Interop.Excel.Application();
                oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
                oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
 
                int iRow = 2;
                int totRow = ds.Tables[0].Rows.Count;
                int totCol = ds.Tables[0].Columns.Count;
 
                //check for data
                if (totRow > 0)
                {
                    //headers
                    for (int j = 0; j < totCol; j++)
                    {
                        string colHead = ds.Tables[0].Columns[j].ColumnName;
                        switch (colHead)
                        { 
                            case "employee":
                                oWorkSheet.Cells[1, j + 1] = "EMP ID";
                                break;
                            case "first_name":
                                oWorkSheet.Cells[1, j + 1] = "FIRST NAME";
                                break;
                            case "last_name":
                                oWorkSheet.Cells[1, j + 1] = "LAST NAME";
                                break;
                            case "hrms_att_shift":
                                oWorkSheet.Cells[1, j + 1] = "CURRENT SHIFT";
                                break;
                        }
                    }
                    ///getting the head range and apply header styles
                    oHeadRange = oWorkSheet.get_Range(oWorkSheet.Cells[1, 1], oWorkSheet.Cells[1, totCol]);
                    oHeadRange.Font.Bold = true;
                    oHeadRange.Interior.ColorIndex = 11;
                    oHeadRange.Font.ColorIndex = 2;
 
                    //inserting datas
                    for (int rowNo = 0; rowNo < totRow; rowNo++)
                    {
                        //in each row
                        for (int colNo = 0; colNo < totCol; colNo++)
                        {
                            // in each column
                            oWorkSheet.Cells[iRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString();
                        }
                        if ((rowNo & 1) == 1)
                        {
                            oCellRange = oWorkSheet.get_Range(oWorkSheet.Cells[iRow, 1], oWorkSheet.Cells[iRow, totCol]);
                            oCellRange.Interior.ColorIndex = 15;
                        }
                        //moving to next row
                        iRow++;
                    }
                }
                //range of the excel sheet
                oRange = oWorkSheet.get_Range("A1", oWorkSheet.Cells[totRow + 1, totCol]);
                oRange.EntireColumn.AutoFit();
                oRange.Borders.Value = 1;
                oWorkBook.SaveAs(strExcelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);
            }
 
            catch (Exception theException)
            {
                string s = theException.Message;
                Console.WriteLine("Console: Attachment - " + theException.Message.ToString());
            }
            finally
            {
                ///Clearing all loaded memory variables
                oRange = null;
                oCellRange = null;
                oWorkBook = null;
                oWorkSheet = null;
                oAppln = null;
            }
        }