Powered by SpreadsheetGear
<%@ Page Language="C#" EnableViewState="false" %>
<%@ Import Namespace="System.Data" %>
<!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>Excel Workbook Consolidation using ASP.NET, C# and Microsoft Excel Compatible Spreadsheet Component for Microsoft .NET, ASP.NET, C#, VB.NET, XLS and Microsoft Visual Studio .NET</title>
<meta content="Excel workbook consolidation 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)
    {
        // Determine the display type from query parameter
        String region = Request.QueryString["region"];
        if (region == null)
            DisplayAsDataGrid(ListRegion.SelectedItem.Value);
        else
            DisplayAsWorkbook(region);
    }
    
    void DisplayAsDataGrid(String region)
    {
        DataSet dataSet;
        if (region.Equals("All"))
        {
            // Get a new workbook with all regions consolidated
            SpreadsheetGear.IWorkbook workbook = GetWorkbookConsolidated();
            
            // Get a DataSet from the newly consolidated workbook
            dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText);
        }
        else
        {
            // Get a workbook for the specified region
            SpreadsheetGear.IWorkbook workbook = GetWorkbookForRegion(region);
        
            // Get a Dataset for the "YearSales" defined name
            dataSet = workbook.GetDataSet("YearSales", SpreadsheetGear.Data.GetDataFlags.FormattedText);
        }
        
        // Bind a DataGrid to the DataSet
        DataGrid1.DataSource = dataSet;
        DataGrid1.DataBind();
    }
    
    void DisplayAsWorkbook(String region)
    {
        SpreadsheetGear.IWorkbook workbook;
        if (region.Equals("All"))
        {
            // Get a new workbook with all regions consolidated
            workbook = GetWorkbookConsolidated();
        }
        else
        {
            // Get a workbook for the specified region
            workbook = GetWorkbookForRegion(region);
        }

        // 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=Sales_" + region + ".xls");
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
        Response.End();
    }
    
    SpreadsheetGear.IWorkbook GetWorkbookConsolidated()
    {
        // Create a new workbook and name the first sheet
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
        SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
        worksheet.Name = "Total Sales";
        
        // Copy all region templates to the new worksheet
        CopyRegion(worksheet, "North", SpreadsheetGear.PasteOperation.None);
        CopyRegion(worksheet, "South", SpreadsheetGear.PasteOperation.Add);
        CopyRegion(worksheet, "East", SpreadsheetGear.PasteOperation.Add);
        CopyRegion(worksheet, "West", SpreadsheetGear.PasteOperation.Add);
        
        // Auto size all worksheet columns which contain data
        worksheet.UsedRange.Columns.AutoFit();
        
        return workbook;
    }
    
    void CopyRegion(SpreadsheetGear.IWorksheet dstWorksheet, String region, SpreadsheetGear.PasteOperation pasteOperation)
    {
        // Open the specified region workbook and get the source range
        SpreadsheetGear.IWorkbook srcWorkbook = GetWorkbookForRegion(region);
        SpreadsheetGear.IRange srcRange = srcWorkbook.Names["YearSales"].RefersToRange;
        
        // Set up our destination range to match the size of the source range
        String address = srcRange.Address;
        SpreadsheetGear.IRange dstRange = dstWorksheet.Cells[address];
      
        // Copy the source range values and formats to the destination range
        // We have to call copy twice here since there is currently no PasteType
        // which does values and all formats together.  This is a limitation in
        // the Excel API, but probably should be added to the SpreadsheetGear API.
        srcRange.Copy(dstRange, 
            SpreadsheetGear.PasteType.Values, 
            pasteOperation, true, false);
        
        srcRange.Copy(dstRange, 
            SpreadsheetGear.PasteType.Formats, 
            pasteOperation, true, false);
    }
    
    SpreadsheetGear.IWorkbook GetWorkbookForRegion(String region)
    {
        // Get the filename from the region name
        String filename;
        switch (region)
        {
            case "North":
            default:
                filename = "spicenorth.xls";
                break;
            case "South":
                filename = "spicesouth.xls";
                break;
            case "East":
                filename = "spiceeast.xls";
                break;
            case "West":
                filename = "spicewest.xls";
                break;
        }
        
        // Return a workbook from the filename
        String ssFile = Server.MapPath("files/" + filename);
        return SpreadsheetGear.Factory.GetWorkbook(ssFile);
    }
     
</script>
</head>
<body style="font-family: Verdana;">
<h5>Excel Workbook Consolidation Sample<br />Using ASP.NET, C# and <a href="https://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">SpreadsheetGear</a></h5>
<form id="form1" runat="server">
  <table cellpadding="4">
    <tr>
      <td colspan="2"><ASP:Label Font-Size="10" Font-Bold="true" runat="server">2005 Sales by Region</ASP:Label></td>
    </tr>
    <tr>
      <td><ASP:RadioButtonList ID="ListRegion" Font-Size="10" AutoPostBack="true" runat="server">
            <ASP:ListItem Selected="true">North</ASP:ListItem>
            <ASP:ListItem>South</ASP:ListItem>
            <ASP:ListItem>East</ASP:ListItem>
            <ASP:ListItem>West</ASP:ListItem>
            <ASP:ListItem>All</ASP:ListItem>
          </ASP:RadioButtonList></td>
      <td align="right" valign="bottom" style="font-size:12px"><a href="workbookconsolidation.aspx?displaytype=xls®ion=<% =ListRegion.SelectedItem.Value %>">Display as Excel Workbook</a></td>
    </tr>
    <tr>
      <td colspan="2"><ASP:DataGrid ID="DataGrid1" BorderWidth="1px" BorderColor="#AAAAAA" CellPadding="4" runat="server">
            <HeaderStyle BackColor="#FF6600" ForeColor="#FFFFFF" Font-Size="8pt" Font-Bold="True" />
            <ItemStyle BackColor="#FFFFEE" ForeColor="#000000" Font-Size="8pt" /> 
            <AlternatingItemStyle BackColor="#FFFFFF" ForeColor="#000000" Font-Size="8pt"/>
          </ASP:DataGrid></td>
    </tr>
  </table>
</form>
<h6>Copyright 2005 © SpreadsheetGear LLC. All Rights Reserved.<br /><a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>
</body>
</html>