export multiple gridviews to excel clientside.

Question

I want to export data from my webpage using and export button. the data is displayed in 3 different gridviews that i want separated onto 3 different worksheets "top 1, top 2, top3" i want to do this clientside im using c# and asp.net.. how can i do this? could i use java script?

here is my code

using System;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;



public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
    DataSet dataSet = new DataSet();

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ISALog1ConnectionString"].ToString());

    SqlCommand cmd = new SqlCommand("exec ProxyReport", conn);
    cmd.CommandTimeout = 200;

    SqlDataAdapter ad = new SqlDataAdapter(cmd);
    ad.Fill(dataSet);

    GridView1.DataSource = dataSet.Tables[0];
    GridView1.DataBind();
    GridView2.DataSource = dataSet.Tables[1];
    GridView2.DataBind();
    GridView3.DataSource = dataSet.Tables[2];
    GridView3.DataBind();

}
protected void Button1_Click(object sender, EventArgs e)
{
    string attachment = "attachment; filename=Top 1.xls";
    Response.ClearContent();
    Response.AddHeader("content-disposition", attachment);
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    GridView1.RenderControl(htw);
    GridView2.RenderControl(htw);
    GridView3.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}
protected void GetExcel(object sender, EventArgs e)
{
    //3 gridviews each in 3 different worksheets
}

public override void VerifyRenderingInServerForm(Control control)
{

}


}

Would this work? can someone kindly implement that into my code? http://stackoverflow.com/questions/7632782/export-gridview-to-multiple-excel-sheet — Cloud, Jul 10 '12 at 15:07
That solution is not client side and requires excel on the server. Excel is not meant to be used on the server or in batch. You have to use third party options unless you want to roll your own, which i'm going to go ahead and guess, won't get you far.. — banging, Jul 10 '12 at 16:14
What would you suggest for this to work easily? Should I change the code to clientside? i tried epplus but im not understanding how that works. — Cloud, Jul 10 '12 at 16:19
epplus is actually what i recommend if you don't mind getting an xlsx (excel 2007+) file.. otherwise npoi like speti43 suggested below. — banging, Jul 10 '12 at 16:22
Okay, epplus seems like the best way to go. But i still don't know how to implement the code into mine for it to work. — Cloud, Jul 10 '12 at 16:34

speti43 · Accepted Answer · 2012-07-10T15:42:07.570

I suggest to use NPOI component for this, its free, and easy to use.

Usage:

    public static void GetExcel (DataTable sourceTable1,DataTable sourceTable2, DataTable sourceTable3, string fileName)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream memoryStream = new MemoryStream();
        HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet;
        HSSFSheet sheet2 = workbook.CreateSheet("Sheet2") as HSSFSheet;
        HSSFSheet sheet3 = workbook.CreateSheet("Sheet3") as HSSFSheet;
        HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;

        sheet.SetColumnWidth(0, 7000);
        sheet.SetColumnWidth(1, 7000);
        sheet.SetColumnWidth(2, 7000);
        sheet.SetColumnWidth(3, 10000);
        sheet.SetColumnWidth(4, 7000);

        sheet.DefaultRowHeight = 3000;


        HSSFPalette palette = workbook.GetCustomPalette();
        palette.SetColorAtIndex(HSSFColor.BLUE.index, 41, 113, 153);
        palette.SetColorAtIndex(HSSFColor.GREEN.index, 129, 179, 78);
        palette.SetColorAtIndex(HSSFColor.CORAL.index, 235, 235, 235);
        palette.SetColorAtIndex(HSSFColor.LIGHT_BLUE.index, 233, 241, 245);

        IFont headerFont = workbook.CreateFont();
        headerFont.FontHeightInPoints = 16;
        headerFont.FontName = "Palatino Linotype";
        headerFont.Boldweight = 5;
        headerFont.Color = HSSFColor.WHITE.index;

        IFont font = workbook.CreateFont();
        font.FontHeightInPoints = 14;
        font.FontName = "Palatino Linotype";
        font.Boldweight = 5;
        font.Color = HSSFColor.BLUE.index;

        HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        headerStyle.FillForegroundColor = HSSFColor.GREEN.index; ;
        headerStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
        headerStyle.BorderBottom = BorderStyle.THIN;
        headerStyle.BottomBorderColor = HSSFColor.WHITE.index;
        headerStyle.BorderRight = BorderStyle.THIN;
        headerStyle.RightBorderColor = HSSFColor.WHITE.index;
        headerStyle.BorderLeft = BorderStyle.THIN;
        headerStyle.LeftBorderColor = HSSFColor.WHITE.index;
        headerStyle.BorderTop = BorderStyle.THIN;
        headerStyle.TopBorderColor = HSSFColor.WHITE.index;
        headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
        headerStyle.Alignment = HorizontalAlignment.CENTER;
        headerStyle.SetFont(headerFont);

        HSSFCellStyle oddStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        oddStyle.FillForegroundColor = HSSFColor.CORAL.index;
        oddStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
        oddStyle.BorderBottom = BorderStyle.THIN;
        oddStyle.BottomBorderColor = HSSFColor.WHITE.index;
        oddStyle.BorderRight = BorderStyle.THIN;
        oddStyle.RightBorderColor = HSSFColor.WHITE.index;
        oddStyle.BorderLeft = BorderStyle.THIN;
        oddStyle.LeftBorderColor = HSSFColor.WHITE.index;
        oddStyle.BorderTop = BorderStyle.THIN;
        oddStyle.TopBorderColor = HSSFColor.WHITE.index;
        oddStyle.VerticalAlignment = VerticalAlignment.CENTER;
        oddStyle.Alignment = HorizontalAlignment.CENTER;
        oddStyle.SetFont(font);

        HSSFCellStyle evenStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        evenStyle.FillForegroundColor = HSSFColor.LIGHT_BLUE.index;
        evenStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
        evenStyle.BorderBottom = BorderStyle.THIN;
        evenStyle.BottomBorderColor = HSSFColor.WHITE.index;
        evenStyle.BorderRight = BorderStyle.THIN;
        evenStyle.RightBorderColor = HSSFColor.WHITE.index;
        evenStyle.BorderLeft = BorderStyle.THIN;
        evenStyle.LeftBorderColor = HSSFColor.WHITE.index;
        evenStyle.BorderTop = BorderStyle.THIN;
        evenStyle.TopBorderColor = HSSFColor.WHITE.index;
        evenStyle.VerticalAlignment = VerticalAlignment.CENTER;
        evenStyle.Alignment = HorizontalAlignment.CENTER;
        evenStyle.SetFont(font);

        // handling header.
        foreach (DataColumn column in sourceTable.Columns)
        {
            var headercell = headerRow.CreateCell(column.Ordinal);
            headercell.SetCellValue(column.ColumnName);
            headercell.CellStyle = headerStyle;
            headerRow.Height = 600;
        }

        // handling value.
        int rowIndex = 1;

        //Create 3 sheet, and fill them
        foreach (DataRow row in sourceTable.Rows)
        {
            HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
            dataRow.Height = 600;
            foreach (DataColumn column in sourceTable.Columns)
            {
                var cell = dataRow.CreateCell(column.Ordinal);
                cell.SetCellValue(row[column].ToString());

                if (sourceTable.Rows.IndexOf(row) % 2 == 0)
                {
                    cell.CellStyle = evenStyle;
                }
                else
                {
                    cell.CellStyle = oddStyle;
                }
            }

            rowIndex++;
        }

        workbook.Write(memoryStream);
        memoryStream.Flush();

        HttpResponse response = HttpContext.Current.Response;
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
        response.Clear();

        response.BinaryWrite(memoryStream.GetBuffer());
        response.End();
    }

I would prefer not to use third party – Cloud Jul 10 '12 at 15:46 — Cloud, Jul 10 '12 at 15:46

export multiple gridviews to excel clientside.

1 Answers1