Powered by SpreadsheetGear 2010
 
<%@ 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">
<head>
<title>Insert DataTable into Excel Workbook with Formats and Formulas - SpreadsheetGear 2010</title>
<meta content="Insert DataTable into Excel Workbook with Formats and Formulas using SpreadsheetGear for .NET." name="description" />
</head>
<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();
        dataset.ReadXml(xmlfile);
        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", 
            SpreadsheetGear.Data.GetDataFlags.FormattedText);
        
        // Bind a DataGrid to the formatted DataSet
        DataGrid1.DataSource = datasetOutput;
        DataGrid1.DataBind();
    }
    
</script>

<body style="font-family: Verdana;">
<h5>Insert DataTable into Excel Workbook with Formats and Formulas<br />Using C# and <a href="http://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">SpreadsheetGear 2010</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="http://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>
</body>

</html>