<%@ 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>