-
Notifications
You must be signed in to change notification settings - Fork 270
LoadFromDataTable
This method loads a System.Data.DataTable into a spreadshet.
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("Astronaut");
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.
The order of the columns on the worksheet will be the same as in the supplied DataTable. You can re-order the columns by using the DataColumn.SetOrdinal method:
table.Columns["LastName"].SetOrdinal(0);
table.Columns["FirstName"].SetOrdinal(1);
table.Columns["Id"].SetOrdinal(2);
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";
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.
var filledRange = sheet.Cells["A1"].LoadFromDataTable(table, true, TableStyles.Dark1);
// if you want access to the created table in the sheet
var excelTable = sheet.Tables.GetFromRange(filledRange);
// alternatively from version 5.2.1 or higher
sheet.Cells["A1"].LoadFromDataTable(table, c =>
{
c.PrintHeaders = true;
c.TableStyle = TableStyles.Dark1;
});
You can use this method to import XML data to a spreadsheet.
Content of astronauts.xml:
<?xml version="1.0" encoding="utf-8"?>
<Astronauts>
<Astronaut Id="1">
<FirstName>Bob</FirstName>
<LastName>Behnken</LastName>
</Astronaut>
<Astronaut Id="2">
<FirstName>Doug</FirstName>
<LastName>Hurley</LastName>
</Astronaut>
</Astronauts>
var dataSet = new DataSet();
var xml = File.ReadAllText(@"c:\Temp\astronauts.xml");
var reader = XmlReader.Create(new StringReader(xml));
dataSet.ReadXml(reader);
using(var package = new ExcelPackage())
{
var sheet = package.Workbook.Worksheets.Add("test");
var table = dataSet.Tables["Astronaut"];
// default the Id ends up last in the column order. This moves it to the first position.
table.Columns["Id"].SetOrdinal(0);
// Set caption for the headers
table.Columns["FirstName"].Caption = "First name";
table.Columns["LastName"].Caption = "Last name";
// call LoadFromDataTable, print headers and use the Dark1 table style
sheet.Cells["A1"].LoadFromDataTable(table, true, TableStyles.Dark1);
// AutoFit column with for the entire range
sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Row].AutoFitColumns();
package.SaveAs(new FileInfo(@"c:\temp\astronauts.xlsx"));
}
This results in the following worksheet:
EPPlus Software AB - https://epplussoftware.com
- Getting Started
- The Sample Project
- EPPlus and Docker
- EPPlus and Blazor
- EPPlus and Linux
- Security
- Configuration
- Releases/versioning
- What is new in EPPlus 5+
- Breaking Changes in EPPlus 5
- Breaking Changes in EPPlus 6
- Breaking Changes in EPPlus 7
- Addressing a worksheet
- Dimension/Used range
- Copying ranges/sheets
- Insert/Delete
- Filling ranges
- Sorting ranges
- Taking and skipping columns/rows
- Data validation
- Comments
- Freeze and Split Panes
- Header and Footer
- Autofit columns
- Grouping and Ungrouping Rows and Columns
- Formatting and styling
- Conditional formatting
- Using Themes
- Working with custom named table- or slicer- styles