Skip to content

Import/export data

You can read and write data from and to your spreadsheet in a few different ways. The most obvious is to use the Cells property of the ExcelWorksheet class, shown on the Getting Started page. There are also a few other ways to import/export/read data from/into spreadsheets with EPPlus.

Load data into a spreadsheet

From the Cells property (ExcelRange) you can access these methods to load data from various sources:

  • LoadFromText and LoadFromTextAsync- Read a text file (csv, tsv, etc) and load the data into a range on a worksheet.
  • LoadFromDataReader and LoadFromDataReaderAsync - Loads data into a range from an IDataReader
  • LoadFromDataTable - Loads data into a range from a System.Data.DataTable. Can be used for importing data from a range of sources, like XML (example provided) and databases.
  • LoadFromCollection - Loads data into a range from an IEnumerable<T> using reflection.
  • LoadFromCollection using attributes - Loads data into a range/table from an IEnumerable<T> using reflection. Uses attributes that specifies styling, number formats, formulas, etc.
  • LoadFromDictionaries - Loads data into a range from an IEnumerable of ExpandoObject/dynamic objects (via their IDictionary<string, object> interface. Useful for importing json data, example provided.
  • LoadFromArrays - Loads data into a range from an IEnumerable of object[] where each object array becomes a row in the worksheet.

You can optionally specify a parameter to create an ExcelTable when you use these methods. For more detailed examples, have a look at sample 4 & 5 the sample project Sample-.NET Framework or Sample-.NET Framework.

Export data from a spreadsheet

From the Cells property (ExcelRange) you can access these methods to write:

  • ToText and ToTextAsync - Writes a range to a csv string.
  • SaveToText and SaveToTextAsync - Writes a range to a csv file.
  • ToDataTable - Exports data from a range to a System.Data.DataTable
  • ToCollection - Exports data from a range to an IEnumerable<T> where T is a class.
  • ToJson - Exports data from a range to Json.
  • CreateHtmlExporter - Exports data from a range to html/css.
  • GetValue<T> - Gets a value, with the option to specify a datatype
  • Value - Gets or sets the value of the range.

You can also use the GetValue and SetValue methods directly on the worksheet object. (This will give a little bit better performance than reading/writing via the range):

  • GetValue<T> - Gets a value of a single cell, with the option to specify a datatype
  • SetValue - Sets a value of a single cell

Since the Cells property implements the IEnumerable interface, you can use Linq to query data from a worksheet.

var query1= (from cell in sheet.Cells["d:d"] where cell.Value is double && (double)cell.Value >= 9990 && (double)cell.Value <= 10000 select cell);

Dim query1 = (From cell In sheet.Cells("d:d") Where TypeOf cell.Value Is Double AndAlso CDbl(cell.Value) >= 9990 AndAlso CDbl(cell.Value) <= 10000 Select cell)

Most of these methods are demonstrated in the sample project.