From ASP.NET to Excel Multisheets Workbook.

The Gridview to Excel Export Tool 2.0

How to export data from ASP.NET to Excel

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.

Welcome

9/3/2010

Walkthrough: How to create Single/Multisheets Workbooks from an ASPX page.

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

Before running this demo you should install the GridviewToExcelExportTool.

You can download full demo from here. The Access Northwind database is included in the package.

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 GEETMultisheet.dll inside the C:\Program Files (x86)\Cefalà Ing. Ettore\GridviewExcelExportTool\2.0.0\GEETBin folder for 64bits systems or C:\Program Files\Cefalà Ing. Ettore\GridviewExcelExportTool\2.0.0\GEETBin for 32 bits systems.
From Microsoft site download the Access Nothwind database from here and install it into the App_code folder of your new web site
Open the default.aspx page and modify it as described hereafter
Copy next xhtml code and paste it inside the form tag of the default.aspx page:
<%@ 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>How to export data from ASP.NET to both Excel and OpenOffice.</h3> <p>More precisely you'll see</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"> <asp:ListItem>XML</asp:ListItem> <asp:ListItem>XLSX</asp:ListItem> </asp:DropDownList></p> <b>2) 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 /> <a href="..">Home</a> </div> </form> </body> </html>
Then copy following code and paste it into the default.aspx.cs behind file
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 GEETMultiSheet; public partial class _Default : System.Web.UI.Page { 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 RunEasyExportToXmlExcelFormat(); break; case "XLSX": // run export into XLSX format RunEasyExportToXlsxExcelFormat(); break; default: break; } } protected void RunEasyExportToXmlExcelFormat() { //this is the easiest thing you can do - export your gridview to Excel/OpenOffice object sds = SqlDataSource1; Page P = Page; GEETEasyResponse2003 G2003 = new GEETEasyResponse2003("Ettore", ref P, "Orders", ref sds, ref GridView1, true); G2003.Execute(); } protected void RunEasyExportToXlsxExcelFormat() { // this is the easiest thing you can do - export your gridview to Excel/OpenOffice object sds = SqlDataSource1; Page P = Page; GEETEasyResponse2007 G2007 = new GEETEasyResponse2007("Ettore", ref P, "Orders", ref sds, ref GridView1, true); G2007.Execute(); } 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; Page P = Page; GEETStandard geet; // retrieve the Excel Output format from the dropdownlist if (DropDownList1.SelectedValue.Equals("XML")) // instansiating the GEETStandard class passing parameters geet = new GEETStandard(ExcelOutputFormat.xml, "YOUR NAME", ref P); else // instansiating the GEETStandard class passing parameters geet = new GEETStandard(ExcelOutputFormat.xlsx, "YOUR NAME", ref P); // instanciate a new ExcelSheet specifying both the Excel Sheet Name and the Criteria (the Titee) which will be display in the top row GEETMultiSheet.ExcelSheet es = new ExcelSheet("Sales Summary by Category", "Sales by Category (see following sheets)"); // set the ExcelSheet datasource - here a sqldatasource is used es.SDS = 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; // Columns' Headers style definition es.Header2Style.size = 12; es.Header2Style.bold = true; // 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; // add the Excel Sheet definition to the Excel Workbook definition geet.GEETExcelWorkbookDefinition.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.SDS = 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 = GEETMultiSheet.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); GEETMultiSheet.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"; // eventually add the new excel sheet definition to the excel sheet geet.GEETExcelWorkbookDefinition.ExcelSheets.Add(es); // 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 GEETMultiSheet.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.DV = xdv2; // set the summary line position ExcelSheetDef.SummaryLinePosition = SummaryLinePosition.Up; //ExcelSheetDef.BottomLineText = "This Excel Sheet has been created using the Gridview to Excel Export Tool 2.0 by Ettore Cefal� - http://www.gridviewtoexcel.com"; //ExcelSheetDef.BottomLineHref = "http://www.gridviewtoexcel.com"; //ExcelSheetDef.BottomLine2Text = "For info write to info@gridviewtoexcel.com"; //ExcelSheetDef.BottomLine2Href = "mailto://info@gridviewtoexcel.com"; // add the current excelsheet definition to the Excel Workbook definition geet.GEETExcelWorkbookDefinition.ExcelSheets.Add(ExcelSheetDef); } } // with next 2 statements you can write the exported file to a folder. ExportAction ExportToFileAction = new ExportAction(Server.MapPath(String.Format("output/provaexport.{0}", geet.ExcelOutputFormat.ToString()))); geet.ExcelExportActions.Add(ExportToFileAction); // next action allows you to send Excel export to some email addresses //System.Collections.Generic.List<String> _RecipientList = new System.Collections.Generic.List<String>(); //_RecipientList.Add("yourcollegue1@email.it"); //_RecipientList.Add("yourcollegue1@email.it"); //ExportAction emailExportAction = new ExportAction("sender@email.com", "smtp.server.it", _RecipientList, User.Identity.Name); // // or //ExportAction emailExportAction = new ExportAction("sender@email.com", "smtp.server.it", _RecipientList, User.Identity.Name, new System.Net.NetworkCredential("sender@email.com", "password")); //geet.ExcelExportActions.Add(emailExportAction); // run the export geet.Execute(); if (!geet.GEETOk) { Response.Write(geet.GEETMessage); Response.Write(geet.GEETLog); } } }
Press F5 to run the web page. Click here to run from this server

For any issue please write to support@gridviewtoexcel.com

RSS

To the Gridview to Excel Tool Forum

To the download page

Free Gridview to Excel Export Tool download
Download the Gridview to Excel Export Tool from the best download software source

Soft14.com - get free, shareware and quality commercial software about Software Development Components and Libraries VB

Member of Softpicks.Net - Software download network

Ing Ettore Cefalà - via Conca del Naviglio,5 - 20123 - Milano - P.IVA: 07902170153