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.

C#VB.NET

 // 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);
         } 
     } 
 }

' lets connect to the sample database for some data
Using sqlConn As New SQLiteConnection(connectionString)
    sqlConn.Open()
    Using sqlCmd As 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 sqlReader = sqlCmd.ExecuteReader()
            ' Create a workbook and load the data from the IDataReader
            Using package As New ExcelPackage()
                Dim 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)
            End Using
        End Using
    End Using
End Using

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.