.NET Spreadsheet Component Sample Source Code in C# and Visual Basic .NET

Powered by SpreadsheetGear

<%@ Page Language="VB" 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, VB.NET 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, Visual Basic .NET 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="VB" runat="server">

    Sub Page_Load(sender As Object, e As EventArgs)
        ' Determine the display type from query parameter
        Dim region As String = Request.QueryString("region")
        If region Is Nothing Then
            DisplayAsDataGrid(ListRegion.SelectedItem.Value)
        Else
            DisplayAsWorkbook(region)
        End If
    End Sub 'Page_Load

    Sub DisplayAsDataGrid(region As String)
        Dim dataSet As DataSet
        If region.Equals("All") Then
            ' Get a new workbook with all regions consolidated
            Dim workbook As SpreadsheetGear.IWorkbook = GetWorkbookConsolidated()

            ' Get a DataSet from the newly consolidated workbook
            dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText)
        Else
            ' Get a workbook for the specified region
            Dim workbook As SpreadsheetGear.IWorkbook = GetWorkbookForRegion(region)

            ' Get a Dataset for the "YearSales" defined name
            dataSet = workbook.GetDataSet("YearSales", SpreadsheetGear.Data.GetDataFlags.FormattedText)
        End If

        ' Bind a DataGrid to the DataSet
        DataGrid1.DataSource = dataSet
        DataGrid1.DataBind()
    End Sub 'DisplayAsDataGrid

    Sub DisplayAsWorkbook(region As String)
        Dim workbook As SpreadsheetGear.IWorkbook
        If region.Equals("All") Then
            ' Get a new workbook with all regions consolidated
            workbook = GetWorkbookConsolidated()
        Else
            ' Get a workbook for the specified region
            workbook = GetWorkbookForRegion(region)
        End If

        ' 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()
    End Sub 'DisplayAsWorkbook

    Function GetWorkbookConsolidated() As SpreadsheetGear.IWorkbook
        ' Create a new workbook and name the first sheet
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
        Dim worksheet As SpreadsheetGear.IWorksheet = 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
    End Function 'GetWorkbookConsolidated

    Sub CopyRegion(dstWorksheet As SpreadsheetGear.IWorksheet, region As String, pasteOperation As SpreadsheetGear.PasteOperation)
        ' Open the specified region workbook and get the source range
        Dim srcWorkbook As SpreadsheetGear.IWorkbook = GetWorkbookForRegion(region)
        Dim srcRange As SpreadsheetGear.IRange = srcWorkbook.Names("YearSales").RefersToRange

        ' Set up our destination range to match the size of the source range
        Dim address As String = srcRange.Address
        Dim dstRange As SpreadsheetGear.IRange = 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)
    End Sub 'CopyRegion

    Function GetWorkbookForRegion(region As String) As SpreadsheetGear.IWorkbook
        ' Get the filename from the region name
        Dim filename As String = ""
        Select Case region
            Case "North"
                filename = "spicenorth.xls"
            Case "South"
                filename = "spicesouth.xls"
            Case "East"
                filename = "spiceeast.xls"
            Case "West"
                filename = "spicewest.xls"
        End Select

        ' Return a workbook from the filename
        Dim ssFile As String = Server.MapPath(("files/" + filename))
        Return SpreadsheetGear.Factory.GetWorkbook(ssFile)
    End Function 'GetWorkbookForRegion

</script>
</head>
<body style="font-family: Verdana;">
<h5>Excel Workbook Consolidation Sample<br />Using ASP.NET, VB.NET 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>