Powered by SpreadsheetGear
<%@ Page Language="C#" EnableViewState="false" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>DataSet to Excel Workbook with Formats and Formulas using ASP.NET and C# - SpreadsheetGear</title>
<meta content="DataSet to Excel Workbook with Formats and Formulas using ASP.NET, C# and SpreadsheetGear, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft .NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft .NET, ASP.NET, C#, VB.NET and Microsoft Office solutions. Integrates with Microsoft Visual Studio .NET, including IntelliSense and Dynamic Help." name="description" />
<script language="C#" runat="server">

    void Page_Load(Object sender, EventArgs e)
    {
        // Create a DataSet from an XML file.
        String xmlFileName = Server.MapPath("files/nfl.xml");
        System.Data.DataSet dataset = new System.Data.DataSet();
        dataset.ReadXml(xmlFileName);

        // Create a workbook set to hold the template workbook and the report workbook.
        SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
        
        // Open the template workbook which contains formats, borders and formulas.
        String filename = Server.MapPath("files/nfltemplate.xls");
        SpreadsheetGear.IWorkbook templateWorkbook = workbookSet.Workbooks.Open(filename);

        // Get the template range from a defined name in the template workbook.
        SpreadsheetGear.IRange templateRange =
            templateWorkbook.Names["NFLDivisionFormat"].RefersToRange;

        // Get the number of rows and columns in the template range.
        int templateRangeRowCount = templateRange.RowCount;
        int templateRangeColCount = templateRange.ColumnCount;

        // Create a new workbook with one blank worksheet to hold the new Excel Report.
        SpreadsheetGear.IWorkbook reportWorkbook = workbookSet.Workbooks.Add();
        SpreadsheetGear.IWorksheet reportWorksheet = reportWorkbook.Worksheets[0];
        reportWorksheet.WindowInfo.DisplayGridlines = false;
        reportWorksheet.Name = dataset.DataSetName;

        // Start at cell B2
        int row = 1;
        int col = 1;

        // Insert each DataTable from the DataSet...
        foreach (System.Data.DataTable datatable in dataset.Tables)
        {
            // Get the destination range in the report worksheet.
            SpreadsheetGear.IRange dstRange = reportWorksheet.Cells[row, col, 
                row + templateRangeRowCount - 1, col + templateRangeColCount - 1];
            
            // Copy the template range formats and formulas to the report worksheet.
            templateRange.Copy(dstRange, SpreadsheetGear.PasteType.All, 
                SpreadsheetGear.PasteOperation.None, false, false);

            if (row == 1)
            {
                // Copy the template range column widths to the report worksheet once.
                templateRange.Copy(dstRange, SpreadsheetGear.PasteType.ColumnWidths,
                    SpreadsheetGear.PasteOperation.None, false, false);
            }

            // Use the TableName for the title of the range - this is a merged
            // cell centered across the top of the destination range.
            reportWorksheet.Cells[row, col].Formula = datatable.TableName;

            // Add a defined name for the new destination range. This defined
            // name will be adjusted by IRange.CopyFromDataTable, allowing us
            // to skip over the newly inserted range and any summary rows
            // added by the template.
            SpreadsheetGear.IName dstRangeName = reportWorkbook.Names.Add(
                datatable.TableName.Replace(" ", ""), "=" + dstRange.Address);
            
            // Insert the DataTable into the worksheet. This will adjust the defined name,
            // as well as the formats, cell borders and formulas which were copied from
            // the template workbook.
            reportWorksheet.Cells[row + 1, col, row + 3, col].CopyFromDataTable(datatable,
                SpreadsheetGear.Data.SetDataFlags.InsertCells);

            // Update the row counter to the end of the inserted table
            SpreadsheetGear.IRange range = dstRangeName.RefersToRange;
            row = range.Row + range.RowCount + 1;
        }
  
        // Stream the Excel workbook to the client in the Excel 97-2003 (xls) 
        // file format compatible with Excel 97-2019 and Excel for Office 365.
        Response.Clear();
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls");
        reportWorkbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
        Response.End();
    }
    
</script>
</head>
<body>
</body>
</html>