Powered by SpreadsheetGear
<%@ 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>Insert DataTable into Excel Workbook with Formats and Formulas - SpreadsheetGear</title>
<meta content="Insert DataTable into Excel Workbook with Formats and Formulas using SpreadsheetGear." name="description" />
</head>
<script language="VB" runat="server">

    Sub Page_Load(sender As Object, e As EventArgs)
    
        ' Create a DataSet from an XML file and retrieve an order table.
        Dim xmlfile As String = Server.MapPath("files/spiceorder.xml")
        Dim dataset As New System.Data.DataSet()
        dataset.ReadXml(xmlfile)
        Dim datatable As System.Data.DataTable = dataset.Tables("OrderItems")
   
        ' Open the template workbook, which contains number formats and 
        ' formulas, and get an IRange from a defined name
        Dim filename As String = Server.MapPath("files/spiceordertemplate.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(filename)
        Dim range As SpreadsheetGear.IRange = workbook.Names("SetDataRange").RefersToRange
   
        ' Insert the DataTable into the template worksheet range. The InsertCells
        ' flag will cause the formatted range to be adjusted for the inserted data.
        range.CopyFromDataTable(datatable, SpreadsheetGear.Data.SetDataFlags.InsertCells)
   
        ' Retrieve a DataSet from a defined name which includes the formatted range.
        Dim datasetOutput As System.Data.DataSet = workbook.GetDataSet("GetDataRange", SpreadsheetGear.Data.GetDataFlags.FormattedText)
   
        ' Bind a DataGrid to the formatted DataSet
        DataGrid1.DataSource = datasetOutput
        DataGrid1.DataBind()
        
    End Sub 'Page_Load
    
</script>

<body style="font-family: Verdana;">
<h5>Insert DataTable into Excel Workbook with Formats and Formulas<br />Using VB.NET and <a href="https://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">SpreadsheetGear</a></h5>
<form id="form1" runat="server">
  <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>
</form>
<h6>Copyright 2005 © SpreadsheetGear LLC. All Rights Reserved.<br /><a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>
</body>

</html>