Wednesday, March 2, 2011

C#.NET - Export To Excel Contents of DataGridView Control


// Must include following libraries.

using System.IO;
using Microsoft.Office.Interop.Excel;

// Creating Object of System Application (e.g. Microsoft Excel)

private ApplicationClass xlApp = new ApplicationClass();

        private void createDataInExcel(DataSet ds)
        {
            Workbook xlWorkBook;
            Worksheet xlWorkSheet;
           
            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0;

       // set rows and columns of excel sheet = the rows and columns of datagridview control

            for (j = 1; j <= DataGirdViewControl.Columns.Count - 3; j++)
            {
                xlWorkSheet.Cells[i + 1, j + 1] = DataGirdViewControl.Columns[j].HeaderText.ToString();
            }

            for (i = 0; i <= DataGirdViewControl.RowCount  - 1; i++)
            {
                for (j = 1; j <= DataGirdViewControl.Columns.Count  - 3; j++)
                {
                    DataGridViewCell cell = DataGirdViewControl[j, i];
                    xlWorkSheet.Cells[i + 3, j + 1] = cell.Value;
                }
            }

        // Creating destination path, you can assign manually

            string strPath = System.Windows.Forms.Application.StartupPath;
            strPath = strPath.Replace(@"\bin\Debug", "");
            strPath = strPath + "\\Folder";

        // checking for exsting path, if exists it will replace it.
            if (!Directory.Exists(strPath))
                Directory.CreateDirectory(strPath);

            string strFileName = strPath + "\\Folder(" +  FileName  + ").xls";
           
            try
            {
        // To save Excel sheet.

                xlWorkBook.SaveAs(strFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();
            }
            catch (Exception oException)
            {
                MessageBox.Show("You can not access " + strFileName + ". This file might be already open.", "Budget Details", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file \\Folder" + strFileName,"",MessageBoxButtons.OK,MessageBoxIcon.Information);
            xlWorkBook = null;
            xlWorkSheet = null;           
        }

private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

No comments:

Post a Comment