Powered by SpreadsheetGear
<%@ Page Language="C#" 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">
<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 for .NET." name="description" />
<script language="C#" runat="server">

    void Page_Load(Object sender, EventArgs e)
        // Create a DataSet from an XML file and retrieve an order table.
        String xmlfile = Server.MapPath("files/spiceorder.xml");
        System.Data.DataSet dataset = new System.Data.DataSet();
        System.Data.DataTable datatable = dataset.Tables["OrderItems"];
        // Open the template workbook, which contains number formats and 
        // formulas, and get an IRange from a defined name
        String filename = Server.MapPath("files/spiceordertemplate.xls");
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename);
        SpreadsheetGear.IRange range = 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.
        System.Data.DataSet datasetOutput = workbook.GetDataSet("GetDataRange", 
        // Bind a DataGrid to the formatted DataSet
        DataGrid1.DataSource = datasetOutput;

<body style="font-family: Verdana;">
<h5>Insert DataTable into Excel Workbook with Formats and Formulas<br />Using C# 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"/>
<h6>Copyright 2005 © SpreadsheetGear LLC. All Rights Reserved.<br /><a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>