Powered by SpreadsheetGear 2010
 
<%@ Page Language="VB" 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>DataTable to Excel Workbook with Chart using ASP.NET and VB.NET - Returns XLS Workbook Using SpreadsheetGear 2010</title>
<meta content="DataTable to Excel Workbook with Chart with ASP.NET, VB.NET and SpreadsheetGear 2010, 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)
        ' Open a workbook template containing a chart
        Dim ssFile As String = Server.MapPath("files/chartsalesbyquarter.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(ssFile)
        Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("2005 Sales")
        
        ' Create a DataSet from an XML file.  Modify this code to use
        ' any DataSet such as one returned from a database query.
        Dim xmlfile As String = Server.MapPath("files/spicesalestotal.xml")
        Dim dataset As New System.Data.DataSet()
        dataset.ReadXml(xmlfile)
        Dim datatable As System.Data.DataTable = dataset.Tables("Products")
        
        ' Copy the DataTable to the worksheet starting at the top left cell
        Dim range As SpreadsheetGear.IRange = worksheet.Cells(0, 0, 2, 0)
        range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.InsertCells)
        
        ' Auto size all worksheet columns which contain data
        worksheet.UsedRange.Columns.AutoFit()
        
        ' Stream the Excel spreadsheet to the client in a format
        ' compatible with Excel 97/2000/XP/2003/2007/2010.
        Response.Clear()
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97)
        Response.End()
    End Sub 'Page_Load
    
</script>
</head>
</html>