Exporting multiple gridview or dataset to single or multiple excel sheets

Question

im using this method to export a single grid to excel

savedialog.Filter = "Microsoft Excel Documents|*.xls";
            savedialog.DefaultExt = "xls";
            savedialog.FileName = "Document";
            if (savedialog.ShowDialog() == DialogResult.OK)
            {                    
                dtTable.ExportToXls(savedialog.FileName, true);
            }

i need to export 4 gridview to single excel sheet or multiple worksheets

please help me to figure it out

May [this SO question](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp) be helpful — Rumit Parakhiya, Feb 13 '14 at 05:09

score 2 · Answer 1 · answered Feb 13 '14 at 09:50

You can use this also. this function pass the datatable and file path where you want to export

public void CreateCSVFile(ref DataTable dt, string strFilePath)
            {            
                try
                {
                    // Create the CSV file to which grid data will be exported.
                    StreamWriter sw = new StreamWriter(strFilePath, false);
                    // First we will write the headers.
                    //DataTable dt = m_dsProducts.Tables[0];
                    int iColCount = dt.Columns.Count;
                    for (int i = 0; i < iColCount; i++)
                    {
                        sw.Write(dt.Columns[i]);
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);

                    // Now write all the rows.

                    foreach (DataRow dr in dt.Rows)
                    {
                        for (int i = 0; i < iColCount; i++)
                        {
                            if (!Convert.IsDBNull(dr[i]))
                            {
                                sw.Write(dr[i].ToString());
                            }
                            if (i < iColCount - 1)
                            {
                                sw.Write(",");
                            }
                        }

                        sw.Write(sw.NewLine);
                    }
                    sw.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

score 1 · Answer 2 · answered Feb 13 '14 at 04:48

You can create it from direct table also.. This extension method can be called as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;

DataTable dt;
// fill table data in dt here 
...

// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);

Extension method for DataTable class:

public static class My_DataTable_Extensions
{

    // Export DataTable into an excel file with field names in the header line
    // - Save excel file without ever making it visible if filepath is given
    // - Don't save excel file, just make it visible if no filepath is given
    public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
    {
        try
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            Excel.Application excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (int i = 0; i < Tbl.Columns.Count; i++)
            {
                workSheet.Cells[1, (i+1)] = Tbl.Columns[i].ColumnName;
            }

            // rows
            for (int i = 0; i < Tbl.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < Tbl.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                }
            }

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    workSheet.SaveAs(ExcelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                }
            }
            else    // no filepath is given
            {
                excelApp.Visible = true;
            }
        }
        catch(Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}

score 0 · Answer 3 · answered Feb 13 '14 at 04:50

You have to use Microsoft.Office.Interop.Excel to get full fledge functionality to export content to excel from multiple DataGridView. Here is a good article explains how to do it.

http://www.codeproject.com/Articles/335589/Export-Multiple-Datasets-to-Multiple-Excel-sheets

Exporting multiple gridview or dataset to single or multiple excel sheets

3 Answers3