Example
//create a new ExcelPackage
using (ExcelPackage excelPackage = new ExcelPackage())
{
//the query or stored procedure name for the database
string sqlQuery = "SELECT * FROM myTable";
//create a datatable
DataTable dataTable = loadExternalDataSet(sqlQuery);
//create a WorkSheet
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
//add all the content from the DataTable, starting at cell A1
worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
}
//method for retrieving data from the database and return it as a datatable
public static DataTable loadExternalDataSet(string sqlQuery)
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString))
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection))
{
try
{
adapter.Fill(dt);
}
catch
{
}
}
return dt;
}
