ExpertXls Excel Library ASP.NET Web Forms demo · C#

DataTable Import and Export

Reads the worksheet of Data\awemployees.xls into a .NET DataTable (shown below) and loads the DataTable into a new workbook.

Select the format and press the button: the workbook is created on the server and downloaded by the browser.

Workbook format   

The DataTable read from Data\awemployees.xls (first 15 rows)

EmployeeIDNationalIDNumberContactIDLoginIDManagerIDTitleBirthDateMaritalStatusGenderHireDateSalariedFlagVacationHoursSickLeaveHoursModifiedDate
1144178071209adventure-works\guy116Production Technician - WC605/15/1972MM7/31/1996021307/31/2004
22530228761030adventure-works\kevin06Marketing Assistant6/3/1977SM2/26/1997042417/31/2004
35096471741002adventure-works\roberto012Engineering Manager12/13/1964MM12/12/1997-12217/31/2004
41124578911290adventure-works\rob03Senior Tool Designer1/23/1965SM1/5/1998048807/31/2004
54801685281009adventure-works\thierry0263Tool Designer8/29/1949MM1/11/199809247/31/2004
6247566241028adventure-works\david0109Marketing Manager4/19/1965SM1/20/1998-140407/31/2004
73097387521070adventure-works\jolynn021Production Supervisor - WC602/16/1946SF1/26/1998082617/31/2004
86906278181071adventure-works\ruth0185Production Technician - WC107/6/1946MF2/6/1998083617/31/2004
96952569081005adventure-works\gail03Design Engineer10/29/1942MF2/6/1998-15227/31/2004
109122658251076adventure-works\barry0185Production Technician - WC104/27/1946SM2/7/1998088647/31/2004
119983206921006adventure-works\jossef03Design Engineer4/11/1949MM2/24/1998-16237/31/2004
122457979671001adventure-works\terri0109Vice President of Engineering9/1/1961SF3/3/1998-11207/31/2004
138449736251072adventure-works\sidney0185Production Technician - WC1010/1/1946MM3/5/1998084627/31/2004
142330693021067adventure-works\taylor021Production Supervisor - WC505/3/1946MM3/11/1998079597/31/2004
151326748231073adventure-works\jeffrey0185Production Technician - WC108/12/1946SM3/23/1998085627/31/2004

Create, edit and read Excel workbooks in ASP.NET: the workbooks are created on the server and sent to the browser. The code of each workbook is in App_Code.

C# code of this sample

LoadGetDataTable.aspx.cs
using System;
using System.Data;
using System.IO;
using System.Web.UI;

using ExpertXls.ExcelLib;
using ExcelLibraryDemos;

/// <summary>
/// DataTable Import and Export: the workbook code is in App_Code\LoadGetDataTableDemo.cs.
/// </summary>
public partial class LoadGetDataTablePage : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // GetDataTable reads a worksheet range into a DataTable that any data control can show
            DataTable employees = LoadGetDataTableDemo.GetEmployees(Server.MapPath("~"));
            DataTable firstRows = employees.Clone();
            for (int i = 0; i < 15 && i < employees.Rows.Count; i++)
                firstRows.ImportRow(employees.Rows[i]);
            gridEmployees.DataSource = firstRows;
            gridEmployees.DataBind();
        }
    }

    protected void buttonCreate_Click(object sender, EventArgs e)
    {
        ExcelWorkbookFormat format = radioXlsx.Checked ? ExcelWorkbookFormat.Xlsx_2007 : ExcelWorkbookFormat.Xls_2003;
        byte[] workbookBytes;
        try
        {
            // create the workbook in memory; the input files are in the folders of the web site
            using (MemoryStream stream = new MemoryStream())
            {
                LoadGetDataTableDemo.CreateWorkbook(format, Server.MapPath("~"), stream);
                workbookBytes = stream.ToArray();
            }
        }
        catch (Exception ex)
        {
            labelError.Text = "The workbook could not be created: " + Server.HtmlEncode(ex.Message);
            return;
        }
        WorkbookDownload.Send(Response, workbookBytes, LoadGetDataTableDemo.OutputName + DemoLayout.GetExtension(format), format);
    }
}