Export to Excel – Multiple GridView into Multiple Worksheet

There are many articles available on the web which explain the functionality “Export gridview data into Excel“. Which is like “Exporting a single gridview data into an Excel workbook with a single worksheet“.

What-if we want to “Export multiple gridview data into multiple worksheets”? I decided to write an article which explains such a scenario.

The focus of the article is the export to Excel functionality – Gridview and its data binding are only for demonstrating the export functionality.

Here it goes, step-by-step code snippets which help us to achieve the functionality mentioned above.

Step 1: Create a simple website and add two gridviews like this:

<form id="form1" runat="server">
<div>
    <h1>Patient Data</h1>
    <br />
    <asp:GridView ID="gvPatient" runat="server">
    </asp:GridView>
    <h1>Student Data</h1>
    <br />
    <asp:GridView ID="gvStudent" runat="server">
    </asp:GridView>
    <br />
    <asp:Button ID="btnExportBoth" runat="server"
        Text="Export Both Grid Data to Excel" onclick="btnExportBoth_Click" />
</div>
</form>

Step 2: Create properties which help us to store the datatable data.

/// <summary>
/// Property to store patient datatable
/// </summary>
public DataTable PatientData
{
   get { return (DataTable)(ViewState["PatientData"] ?? null); }
   set { ViewState["PatientData"] = value; }
}

/// <summary>
/// Property to store student datatable
/// </summary>
public DataTable StudentData
{
   get { return (DataTable)(ViewState["StudentData"] ?? null); }
   set { ViewState["StudentData"] = value; }
}

Step 3: Page_Load event will load the sample data in Gridview on the page [Patient and Student data, respectively]

protected void Page_Load(object sender, System.EventArgs e)
{
    DataTable dt = new DataTable();
    //Patient Data Binding
    dt = GetPatientData();
    gvPatient.DataSource = dt;
    gvPatient.DataBind();
    PatientData = dt;
    //Student Data Binding
    dt = GetStudentData();
    gvStudent.DataSource = dt;
    gvStudent.DataBind();
    StudentData = dt;
}

/// <summary>
/// Get Patient Data - Sample Data creation
/// </summary>
/// <returns></returns>
private DataTable GetPatientData()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable("Patients");
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
    return table;
}

/// <summary>
/// Get Student Data - Sample Data creation
/// </summary>
/// <returns></returns>
private DataTable GetStudentData()
{
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable("Students");
    table.Columns.Add("SrNo", typeof(int));
    table.Columns.Add("FirstName", typeof(string));
    table.Columns.Add("LastName", typeof(string));
    table.Columns.Add("Age", typeof(int));

    // Here we add five DataRows.
    table.Rows.Add(1, "Sandeep", "Ramani", 29);
    table.Rows.Add(2, "Kapil", "Bhaai", 28);
    table.Rows.Add(3, "Vinit", "Shah", 28);
    table.Rows.Add(4, "Samir", "Bhaai", 30);
    table.Rows.Add(5, "Umang", "Samani", 29);
    return table;
}

Step 4: Here comes the main part which we are waiting for. Export button click event logic along with methods required to create workbook and worksheets.

/// <summary>
/// Export button click event
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExportBoth_Click(object sender, EventArgs e)
{
    object[] myGridViews = new object[2];
    myGridViews[0] = PatientData;
    myGridViews[1] = StudentData;
    CreateWorkBook(myGridViews, "ExportToExcel", 80);
}

/// <summary>
/// Method to create workbook
/// </summary>
/// <param name="cList"></param>
/// <param name="wbName"></param>
/// <param name="CellWidth"></param>
private void CreateWorkBook(object[] cList, string wbName, int CellWidth)
{
   string attachment = "attachment; filename=\"" + wbName + ".xml\"";
   HttpContext.Current.Response.ClearContent();
   HttpContext.Current.Response.AddHeader("content-disposition", attachment);
   HttpContext.Current.Response.ContentType = "application/ms-excel";
   System.IO.StringWriter sw = new System.IO.StringWriter();
   sw.WriteLine("<?xml version=\"1.0\"?>");
   sw.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
   sw.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
   sw.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
   sw.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
   sw.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
   sw.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
   sw.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
   sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>");
   sw.WriteLine("<Created>2013-01-09T19:14:19Z</Created>");
   sw.WriteLine("<Version>11.9999</Version>");
   sw.WriteLine("</DocumentProperties>");
   sw.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
   sw.WriteLine("<WindowHeight>9210</WindowHeight>");
   sw.WriteLine("<WindowWidth>19035</WindowWidth>");
   sw.WriteLine("<WindowTopX>0</WindowTopX>");
   sw.WriteLine("<WindowTopY>90</WindowTopY>");
   sw.WriteLine("<ProtectStructure>False</ProtectStructure>");
   sw.WriteLine("<ProtectWindows>False</ProtectWindows>");
   sw.WriteLine("</ExcelWorkbook>");
   sw.WriteLine("<Styles>");
   sw.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
   sw.WriteLine("<Alignment ss:Vertical=\"Bottom\"/>");
   sw.WriteLine("<Borders/>");
   sw.WriteLine("<Font/>");
   sw.WriteLine("<Interior/>");
   sw.WriteLine("<NumberFormat/>");
   sw.WriteLine("<Protection/>");
   sw.WriteLine("</Style>");
   sw.WriteLine("<Style ss:ID=\"s22\">");
   sw.WriteLine("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
   sw.WriteLine("<Borders>");
   sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("</Borders>");
   sw.WriteLine("<Font ss:Bold=\"1\"/>");
   sw.WriteLine("</Style>");
   sw.WriteLine("<Style ss:ID=\"s23\">");
   sw.WriteLine("<Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
   sw.WriteLine("<Borders>");
   sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("</Borders>");
   sw.WriteLine("</Style>");
   sw.WriteLine("<Style ss:ID=\"s24\">");
   sw.WriteLine("<Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
   sw.WriteLine("<Borders>");
   sw.WriteLine("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"");
   sw.WriteLine("ss:Color=\"#000000\"/>");
   sw.WriteLine("</Borders>");
   sw.WriteLine("<Font ss:Color=\"#FFFFFF\"/>");
   sw.WriteLine("<Interior ss:Color=\"#FF6A6A\" ss:Pattern=\"Solid\"/>");
   //set header colour here
   sw.WriteLine("</Style>");
   sw.WriteLine("</Styles>");
   foreach (DataTable myTable in cList)
   {
      CreateWorkSheet(myTable.TableName, sw, myTable, CellWidth);
   }
   sw.WriteLine("</Workbook>");
   HttpContext.Current.Response.Write(sw.ToString());
   HttpContext.Current.Response.End();
}

/// <summary>
/// Method to create worksheet
/// </summary>
/// <param name="wsName"></param>
/// <param name="sw"></param>
/// <param name="dt"></param>
/// <param name="cellwidth"></param>
private void CreateWorkSheet(string wsName, System.IO.StringWriter sw, DataTable dt, int cellwidth)
{
    if (dt.Columns.Count > 0)
    {
       sw.WriteLine("<Worksheet ss:Name=\"" + wsName + "\">");
       int cCount = dt.Columns.Count;
       long rCount = dt.Rows.Count + 1;
       sw.WriteLine("<Table ss:ExpandedColumnCount=\"" + cCount + 
         "\" ss:ExpandedRowCount=\"" + rCount + "\"x:FullColumns=\"1\"");
       sw.WriteLine("x:FullRows=\"1\">");
       for (int i = (cCount - cCount); i <= (cCount - 1); i++)
       {
          sw.WriteLine("<Column ss:AutoFitWidth=\"1\" ss:Width=\"" + cellwidth + "\"/>");
       }
       DataTableRowIteration(dt, sw);
       sw.WriteLine("</Table>");
       sw.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
       sw.WriteLine("<Selected/>");
       sw.WriteLine("<DoNotDisplayGridlines/>");
       sw.WriteLine("<ProtectObjects>False</ProtectObjects>");
       sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
       sw.WriteLine("</WorksheetOptions>");
       sw.WriteLine("</Worksheet>");
   }
}

/// <summary>
/// Method to create rows by iterating thru datatable rows
/// </summary>
/// <param name="dt"></param>
/// <param name="sw"></param>
private void DataTableRowIteration(DataTable dt, System.IO.StringWriter sw)
{
     sw.WriteLine("");
     foreach (DataColumn dc in dt.Columns)
     {
        string tcText = dc.ColumnName;
        sw.WriteLine("<data>" + tcText + "</data>");
     }
     sw.WriteLine("");
     foreach (DataRow dr in dt.Rows)
     {
        sw.WriteLine("");
        foreach (DataColumn tc in dt.Columns)
       {
          string gcText = dr[tc].ToString();
          sw.WriteLine("<data>" + gcText + "</data>");
       }
       sw.WriteLine("");
     }
}

The above code helps us to create worksheets as per requirements. You just need to pass the object list [DataTable objects]. So it will iterate thru each one and create worksheets for each of them.

Please make sure you create Datatable with TableName so that we can create a workbook with different worksheet names. I have used the datatable name as the worksheet name.

Hope this will help!!!