LoadFromDataTable

This method loads a System.Data.DataTable into a spreadshet.

Basic usage

A DataTable can be filled with data from various sources, often a database. In this simple example we will create it and fill it ourselves.

var table = new DataTable("Astronauts");
table.Columns.Add("Id", typeof(int));
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
// add some data
table.Rows.Add(1, "Bob", "Behnken");
table.Rows.Add(2, "Doug", "Hurley");

//create a workbook with a spreadsheet and load the data table
using(var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("Astronauts");
    var filledRange = sheet.Cells["A1"].LoadFromDataTable(table);
}

This will set the value of cell A1 to 1, B1 to "Bob" and C1 to "Behnken". A2 to 2, B2 to "Doug" and C2 to "Hurley". The return value is the resulting range, containing the added data.

Headers

By supplying the parameter PrintHeaders to the LoadFromDataSet method you can add headers to the first row above the data.

sheet.Cells["A1"].LoadFromDataTable(table, true);
// alternatively from version 5.2.1 or higher
sheet.Cells["A1"].LoadFromDataTable(table, c => c.PrintHeaders = true);

EPPlus uses the Caption property of the DataColumn's of the DataTable to set the headers. Per default these will be the column names set when the DataTable was created, but you can change the caption to any value, see below (using the example above):

table.Columns["FirstName"].Caption = "First name";
table.Columns["LastName"].Caption = "Last name";

TableStyle

If you supply the parameter TableStyle EPPlus will create a table for the data in the worksheet. The TableStyles enum contains over 60 different table styles to choose from.

sheet.Cells["A1"].LoadFromDataTable(table, true, TableStyles.Dark1);
// alternatively from version 5.2.1 or higher
sheet.Cells["A1"].LoadFromDataTable(table, c =>
            {
                c.PrintHeaders = true;
                c.TableStyle = TableStyles.Dark1;
            });

Import XML with System.Data.DataSet

You can use this method to import XML data to a spreadsheet.

var dataSet = new DataSet();
var xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
            "<Items>" +
            "<Id>1</Id>" +
            "<Name>Test name</Name>" +
            "</Items>";
var reader = XmlReader.Create(new StringReader(xml));
dataSet.ReadXml(reader);
using(var package = new ExcelPackage())
{
    var sheet = package.Workbook.Worksheets.Add("test");
    sheet.Cells["A1"].LoadFromDataTable(dataSet.Tables["Items"], true, TableStyles.Dark1);
}