LoadFromDataReader

This method loads data from a System.Data.IDataReader into a spreadsheet.

Basic usage

The IDataReader can be used for reading data from many different types of databases, in this sample we are using an SQLite database.

 // lets connect to the sample database for some data``
 using (var sqlConn = new SQLiteConnection(connectionString))
 { 
     sqlConn.Open(); 
     using (var sqlCmd = new SQLiteCommand("select CompanyName, [Name], Email, c.Country, o.OrderId, orderdate, ordervalue, currency from Customer c inner join Orders o on c.CustomerId=o.CustomerId inner join SalesPerson s on o.salesPersonId = s.salesPersonId ORDER BY 1,2 desc", sqlConn)) 
     { 
         using (var sqlReader = sqlCmd.ExecuteReader())
         { 
             // Create a workbook and load the data from the IDataReader
             using(var package = new ExcelPackage())
             {
                 var sheet = package.Workbook.Worksheets.Add("TestSheet");
                 // The second argument specifies if we should print headers on the first row or not
                 sheet.Cells["A1"].LoadFromDataReader(sqlReader, true);
         } 
     } 
 }

Arguments

PrintHeaders

Indicates if the column names from the data reader should be used as headers in the spreadsheet.

TableName

Name of the table in the spreadsheet.

Transpose

Set to true to transpose the data.

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.

var filledRange = sheet.Cells["A1"].LoadFromDataReader(sqlReader, true, TableStyles.Dark1);
// if you want to get access to the created table:
var table = sheet.Tables.GetFromRange(filledRange);

See also

• LoadFromDataReaderAsync
• Import data