The Gridview to Excel Export Tool 3.0

How to export from ASP.NET to a Multiple Worksheets Excel Workbook

The tool you need to export data from ASP.NET to Excel. You will obtain multiple sheets workbook; you'll export to Excel 2003, Excel 2007 and Excel 2010; you'll format columns, add computed columns with your own formulas.

How to create Single/Multiple Worksheets Workbooks from an ASPX page.

This sample illustrates how to export data from an ASP.NET page.Click here to run the demo

Click here to download the full demo. The Access Northwind database is included in the package.

This tutorial assumes you have already installed the GridviewToExcelExportTool.

The full process is made up of following steps:

First step is the Web Site creation - choose C# as a language
Second step is to add a reference to the GEETMultisheet30.dll inside the C:\Program Files (x86)\Cefalà Ing. Ettore\GridviewExcelExportTool\3.0.0\GEETBin folder for 64bits systems or C:\Program Files\Cefalà Ing. Ettore\GridviewExcelExportTool\3.0.0\GEETBin for 32 bits systems.
Replace xhtml code of your default.aspx page with next one:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Title="Export from ASP.NET to Excel" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>From ASP.NET to Excel</title> <meta name="keywords" content="ASP.NET, excel, openoffice" /> <meta name="description" content="How to export data from ASP.NET into single/multisheet Excel workbooks." /> </head> <body> <form id="form1" runat="server"> <div> <h3>GridviewToExcel 3.0 - How to export data from ASP.NET to both Excel and OpenOffice.</h3> <p>This sample demonstrates</p> <ol> <li>how easy is to export data from a gridview to a single sheet Excel workbook</li> <li>how you can export data from asp.net to multisheet Excel/OpenOffice workbooks </li> </ol> <p>Note: Colors' choice was not the main objective of this demo.</p> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" Font-Names="Arial" Font-Size="Small" AllowPaging="True" PageSize="15" EnableModelValidation="True"> <Columns> <asp:BoundField DataField="CategoryName" HeaderText="Category Name" SortExpression="CategoryName" > <ItemStyle BackColor="Silver" Font-Bold="True" ForeColor="White" /> </asp:BoundField> <asp:BoundField DataField="ProductName" HeaderText="Product Name" SortExpression="ProductName" > <ItemStyle BackColor="#009999" ForeColor="White" /> </asp:BoundField> <asp:BoundField DataField="ProductSales" DataFormatString="{0:#,##0.00}" HeaderText="Product Sales" SortExpression="Product Sales"> <ItemStyle HorizontalAlign="Right" BackColor="#666699" Font-Bold="True" ForeColor="White" /> </asp:BoundField> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="" ProviderName="" SelectCommand="SELECT * FROM [Product Sales for 1995]" oninit="SqlDataSource1_Init"></asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceSummary" runat="server" ConnectionString="" ProviderName="" SelectCommand="SELECT CategoryName, SUM(ProductSales) AS ProductSales FROM [Product Sales for 1995] GROUP BY CategoryName ORDER BY CategoryName" oninit="SqlDataSourceSummary_Init"> </asp:SqlDataSource> <br /><p style="font-weight:bold"> 1) Select either Excel XML or XLSX output format: <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" onselectedindexchanged="DropDownList1_SelectedIndexChanged"> <asp:ListItem>XML</asp:ListItem> <asp:ListItem>XLSX</asp:ListItem> <asp:ListItem Value="ZIPPED XML">ZIPPED XML</asp:ListItem> </asp:DropDownList></p> <p style="font-weight:bold"> 2) Choose action: <asp:DropDownList ID="DropDownListResponse" runat="server" AutoPostBack="True" onselectedindexchanged="DropDownListResponse_SelectedIndexChanged"> <asp:ListItem>RESPONSE</asp:ListItem> <asp:ListItem>WRITE TO FILE</asp:ListItem> </asp:DropDownList> <asp:Label ID="LabelFileName" runat="server" Text="Label"></asp:Label> </p> <b>3) Then push a button to run the export to Excel/OpenOffice </b> <asp:Button ID="ButtonEasyExcel" runat="server" OnClick="ButtonEasyExcel_Click" Text="To Excel - Very Easy" /> <asp:Button ID="ButtonMultisheetExcel" runat="server" OnClick="RunMultiSheetExportToExcel" Text="To Excel - Multisheet" /> <br /> <br /> <strong>4) Click next button to open the Excel Export just written to disk <asp:LinkButton ID="LinkButton1" runat="server" onclick="LinkButton1_Click">Open Excel Export File</asp:LinkButton> <br /> </strong> <br /> </div> </form> </body> </html>
Replace c# code of default.aspx.cs file with next one:
using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using GEETMultiSheet30; using System.IO; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) ComputeFileName(); } public override void VerifyRenderingInServerForm(Control control) { //base.VerifyRenderingInServerForm(control); } protected void ButtonEasyExcel_Click(object sender, EventArgs e) { // check if a valid Excel format is selected if (DropDownList1.SelectedValue.Equals("?")) return; switch (DropDownList1.SelectedValue) { case "?": break; case "XML": // run export into XML format RunEasyExportToExcel(ExcelOutputFormat.xml,0); break; case "ZIPPED XML": // run export into XML format RunEasyExportToExcel(ExcelOutputFormat.xml,10000); break; case "XLSX": // run export into XLSX format RunEasyExportToExcel(ExcelOutputFormat.xlsx,0); break; default: break; } } protected void RunEasyExportToExcel(ExcelOutputFormat excelOUTPUTFORMAT,long ZipLimit) { object sds = SqlDataSource1; HttpResponse R = Page.Response; GEETStandard gEasy = new GEETStandard(excelOUTPUTFORMAT); gEasy.CreateAndAddExcelSheetFromGridview("Orders Report of " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString(), ref sds, ref GridView1, true, true); SetBottomLines(gEasy.GEETExcelWorkbookDefinition.ExcelSheets[0]); if (DropDownListResponse.SelectedValue.Equals("RESPONSE")) gEasy.GEETOutputResponse(ref R, "My Orders Excel Export", true, HttpCacheability.NoCache, ZipLimit); else gEasy.GEETOutputToFile(Server.MapPath("./output/your_file_name." + excelOUTPUTFORMAT.ToString()), ZipLimit); } protected void SqlDataSource1_Init(object sender, EventArgs e) { if (Server.MachineName.Equals("MAINSERVER")) { // enable next lines to connect to SQL Server Northwind database SqlDataSource1.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"; SqlDataSource1.ProviderName = "System.Data.SqlClient"; SqlDataSource1.SelectCommand = "SELECT * FROM [Product Sales for 1997]"; } else { // this is to set the SqlDataSource1 connectionstring SqlDataSource1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("./App_data/nwind.mdb") + "'"; SqlDataSource1.ProviderName = "System.Data.OleDb"; SqlDataSource1.SelectCommand = "SELECT * FROM [Product Sales for 1995]"; } } protected void SqlDataSourceSummary_Init(object sender, EventArgs e) { if (Server.MachineName.Equals("MAINSERVER")) { // next lines to connect to SQL Server Northwind database SqlDataSourceSummary.ConnectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True"; SqlDataSourceSummary.ProviderName = "System.Data.SqlClient"; SqlDataSourceSummary.SelectCommand = "SELECT CategoryName, SUM(ProductSales) AS ProductSales FROM [Product Sales for 1997] GROUP BY CategoryName ORDER BY CategoryName"; } else { // this is to set the SqlDataSourceSummary connectionstring SqlDataSourceSummary.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("./App_data/nwind.mdb") + "'"; SqlDataSourceSummary.ProviderName = "System.Data.OleDb"; SqlDataSourceSummary.SelectCommand = "SELECT CategoryName, SUM(ProductSales) AS ProductSales FROM [Product Sales for 1995] GROUP BY CategoryName ORDER BY CategoryName"; } } protected void RunMultiSheetExportToExcel(object sender, EventArgs e) { // check if a valid Excel format is selected if (DropDownList1.SelectedValue.Equals("?")) return; // instanciate a new ExcelSheet specifying both the Excel Sheet Name and the Criteria (the Titee) which will be display in the top row ExcelSheet es = new ExcelSheet("Sales Summary by Category", "Sales by Category (see following sheets)"); // set the ExcelSheet datasource - here a sqldatasource is used es.ExcelDataSource = SqlDataSourceSummary; // next step is Columns Definition - even though you can manually define each single columns // it's much easier to automatically generate them by means of the LoadColumnsListFromSQLDatasource function. // This function will generate a column for each sqldatasource column. // After running LoadColumnsListFromSQLDatasource you can all the same add, delete, move columns as well as define each column properties. es.ColumnsList = GEETFunctions.LoadColumnsListFromSQLDatasource(ref SqlDataSourceSummary, true, true); // Header row style definition es.Header1Style.size = 14; es.Header1Style.bgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Cyan); // Columns' Headers style definition es.Header2Style.size = 12; es.Header2Style.bold = true; es.Header2Style.bgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Yellow); es.Header2Style.fgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Blue); // setting the Summary line position - if can either set it to "up" or "down". // if you choose "up" then the summary line will be put between the first row - the header - and the Colums' Headers row. // if you choose "down" the summary line will be put at the bottom of the sheet es.SummaryLinePosition = SummaryLinePosition.Down; es.ColumnsList[0].ColumnHeader = "Category"; es.ColumnsList[0].ColumnStyle.bgColor = "828282"; es.ColumnsList[0].ColumnStyle.fgColor = "FFFFFF"; es.ColumnsList[0].ColumnStyle.size = 13; //es.ColumnsList[0].ColumnStyle.bold = true; es.ColumnsList[1].ColumnHeader = "Total Category Sales"; es.ColumnsList[1].ColumnStyle.bgColor = "8A9AAA"; es.ColumnsList[1].ColumnStyle.fgColor = "FFFFFF"; es.ColumnsList[1].ColumnStyle.size = 13; //es.ColumnsList[1].ColumnStyle.bold = true; // here a new computed column is defined - a percentage will be represented that will be calculated by dividing the value of the second column by its sum. ExcelColumn PERCcolumn = new ExcelColumn("% on Total", RowFunctions.PercOnTotal, 2, 2); PERCcolumn.ColumnStyle.size = 15; // the background color is set here by means of the FromColorToHex6 function which convert System colors to Hex6 format PERCcolumn.ColumnStyle.bgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Red); PERCcolumn.ColumnStyle.fgColor = "FFFFFF"; PERCcolumn.ColumnStyle.font = "Courier"; // next line will add the PercColumn definitio the the Excel Sheet definition es.ColumnsList.AddLast(PERCcolumn); // set the summary line style es.TotalLineStyle.size = 13; es.TotalLineStyle.bold = true; ExcelWorkbookDefinition exdef = new ExcelWorkbookDefinition(); exdef.ExcelSheets.Add(es); // here we instantiate a new Excel Sheet setting both name and title es = new ExcelSheet("All sales", "All sales"); // set the ExcelSheet sqldatasource es.ExcelDataSource = SqlDataSource1; // here we generate a column list starting from the Gridview object - after the we can manipulate the excel sheet according to our needs // this CommonColumnsList will be used as a template for following Excel Sheets' definitions ExcelColumns CommonColumnsList = GEETFunctions.LoadColumnsListFromGridview(ref GridView1); // now we set the Excel Sheet "column list" to that we just obtained es.ColumnsList = CommonColumnsList; // set summary line position es.SummaryLinePosition = SummaryLinePosition.Up; // set first row style es.Header1Style.size = 14; es.Header1Style.bgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Green); es.Header1Style.fgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.White); ExcelColumn c; // next line to search for a column whose header is "Product Sales" c = es.ColumnsList.FindByHeader("Product Sales"); // in case it exists we set some properties if (c != null) { //// here we set both the ThousandSeparator and the number of decimals digits we want to display //c.ThousandSeparator = true; //c.DecimalDigits = 2; // here we set Excel data format for the new column c.ColumnType = ColumnTypes.Numeric; c.ExcelDataFormat = "#,##0.00"; c.ColumnStyle.fgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Black); c.ColumnStyle.bgColor = "CCCCCC"; c.ColumnStyle.size = 10; c.ColumnStyle.bold = true; } // this is another way to search for a column //foreach (GEETMultiSheet.ExcelColumn c in es.ColumnsList) //{ // if (c.ColumnHeader == "Product Sales") // { // c.ThousandSeparator = true; // c.DecimalDigits = 2; // c.ColumnStyle.fgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Black); // c.ColumnStyle.bgColor = "CCCCCC"; // //c.ColumnStyle = es.Header1Style; // c.ColumnStyle.size = 10; // c.ColumnStyle.bold = true; // } //} // set the style for the first row es.Header1Style.bold = true; es.Header1Style.size = 16; // set the first bottom line es.BottomLineText = "Category Details on following sheets."; // we want the first bottom line to be an href link es.BottomLineHref = "http://www.gridviewtoexcel.com"; // here I define a new Percentage Column ExcelColumn PCol = new ExcelColumn("%", RowFunctions.PercOnTotal, 3, 3); PCol.ColumnStyle.bgColor = GEETFunctions.FromColorToHex6(System.Drawing.Color.Red); PCol.ColumnStyle.fgColor = "FFFFFF"; CommonColumnsList.AddLast(PCol); // since we want to generate one Excel Sheet for each Category we use SqlDataSource1 to provide us // a dataview sorted by CategoryName and ProductName System.Data.DataView xdvDetails; xdvDetails = (DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty); System.Data.DataView xdvSorted = xdvDetails.Table.DefaultView; xdvSorted.Sort = "CategoryName,ProductName"; System.Data.DataView xdv2; String cCategoryName = ""; int i = 1; // add a worksheet definition for each category foreach (System.Data.DataRowView r in xdvSorted) { if (r["CategoryName"].ToString() != cCategoryName) { i = i + 1; cCategoryName = r["CategoryName"].ToString(); // for each CategoryName we define a new Excel Sheet setting both SheetName and Header to the Category Name ExcelSheet ExcelSheetDef = new ExcelSheet(cCategoryName, "Category: " + cCategoryName); // this is a trick - we instantiate a new dataview from the original one and we filter it using the current Category Name xdv2 = GEETFunctions.CloneDataView(xdvDetails, string.Format("CategoryName='{0}'", cCategoryName), "ProductName"); // set some style ExcelSheetDef.Header1Style.size = 12; ExcelSheetDef.Header1Style.bold = true; // set the excelsheet columnlist (which won't change for next sheets) ExcelSheetDef.ColumnsList = CommonColumnsList; // tell the third column we want it to be SUMed ExcelSheetDef.ColumnsList[2].ColumnFunction = ColumnFunctions.SUM; // eventually set the ExcelSheet datasource ExcelSheetDef.ExcelDataSource = xdv2; // set the summary line position ExcelSheetDef.SummaryLinePosition = SummaryLinePosition.Up; // next to set bottom lines content SetBottomLines(ExcelSheetDef); // add the current excelsheet definition to the Excel Workbook definition exdef.ExcelSheets.Add(ExcelSheetDef); } } GEETStandard geet; // retrieve the Excel Output format from the dropdownlist if (DropDownList1.SelectedValue.IndexOf("XML") >= 0) // instansiating the GEETStandard class passing parameters geet = new GEETStandard(ExcelOutputFormat.xml, exdef); else // instansiating the GEETStandard class passing parameters geet = new GEETStandard(ExcelOutputFormat.xlsx, exdef); // add the Excel Sheet definition to the Excel Workbook definition int ZipLimit = 0; if (DropDownList1.SelectedValue.StartsWith("ZIPPED")) // any XML file over 1000 bytes will be zipped -zippingworks only for XML files. XLSX are already zipped. ZipLimit = 1000; else // set ZipLimit = 0 to disable zipping ZipLimit = 0; if (DropDownListResponse.SelectedValue.Equals("RESPONSE")) { HttpResponse R = Page.Response; geet.GEETOutputResponse(ref R, "your_file_name", true, HttpCacheability.NoCache, ZipLimit); } else { if (DropDownList1.SelectedValue.Equals("XML")) { geet.GEETOutputToFile(Server.MapPath(LabelFileName.Text),0); } else if (DropDownList1.SelectedValue.Equals("ZIPPED XML")) { // any xml file larger than 1 byte will be zipped - .zip extension will be added to filename geet.GEETOutputToFile(Server.MapPath(LabelFileName.Text), 1); } else { geet.GEETOutputToFile(Server.MapPath(LabelFileName.Text), 0); } } } protected void SetBottomLines(ExcelSheet es) { es.BottomLineText = "This Excel Sheet has been created using the Gridview to Excel Export Tool 2.0 by Ettore Cefal� - http://www.gridviewtoexcel.com"; es.BottomLineHref = "http://www.gridviewtoexcel.com"; es.BottomLine2Text = "For info write to info@gridviewtoexcel.com"; es.BottomLine2Href = "mailto://info@gridviewtoexcel.com"; } protected void LinkButton1_Click(object sender, EventArgs e) { if (File.Exists(Server.MapPath(LabelFileName.Text))) { HttpResponse R = this.Page.Response; GEETFunctions.GeneralResponseFile(Server.MapPath(LabelFileName.Text),ref R); } } protected void DropDownListResponse_SelectedIndexChanged(object sender, EventArgs e) { ComputeFileName(); } protected void ComputeFileName() { if (DropDownListResponse.SelectedValue.Equals("RESPONSE")) LabelFileName.Text = ""; else { String ctime = DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss"); if (DropDownList1.SelectedValue.Equals("XML")) // write an XML file - LabelFileName.Text = "./output/your_file_name" + ctime + ".xml"; else if (DropDownList1.SelectedValue.Equals("ZIPPED XML")) LabelFileName.Text = "./output/your_file_name" + ctime + ".xml.zip"; else LabelFileName.Text = "./output/your_file_name" + ctime + ".xlsx"; } } protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { ComputeFileName(); } }
Press F5 to run the web page. Click here to run the sample from this server

For any info please write to info@gridviewtoexcel.com