Export list data to excel asp net using open XML
Export data form List<T> to excel workbook is very easy and fast using open XML as compare to other techniques.
Step 1:
Include references of DocumentFormat.OpenXml and WindowsBase
Step 2:
Add CreateExcelFileUtility.cs file in your project's Utility folder.
public class CreateExcelFileUtility
{
public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list));
return CreateExcelDocument(ds, xlsxFilePath);
}
#region HELPER_FUNCTIONS
public static DataTable ListToDataTable<T>(List<T> list)
{
DataTable dt = new DataTable();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
}
foreach (T t in list)
{
DataRow row = dt.NewRow();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
if (!IsNullableType(info.PropertyType))
row[info.Name] = info.GetValue(t, null);
else
row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
}
dt.Rows.Add(row);
}
return dt;
}
private static Type GetNullableType(Type t)
{
Type returnType = t;
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
returnType = Nullable.GetUnderlyingType(t);
}
return returnType;
}
private static bool IsNullableType(Type type)
{
return (type == typeof(string) ||
type.IsArray ||
(type.IsGenericType &&
type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
}
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
bool result = CreateExcelDocument(ds, xlsxFilePath);
ds.Tables.Remove(dt);
return result;
}
public static bool DeleteFileFromDirectory(string DirectoryPath)
{
bool response = true;
try
{
System.IO.DirectoryInfo downloadedMessageInfo = new DirectoryInfo(DirectoryPath);
foreach (FileInfo file in downloadedMessageInfo.GetFiles())
{
file.Delete();
}
foreach (DirectoryInfo dir in downloadedMessageInfo.GetDirectories())
{
dir.Delete(true);
}
}
catch (Exception ex)
{
response = false;
}
return response;
}
#endregion
/// <summary>
/// Create an Excel file, and write it to a file.
/// </summary>
/// <param name="ds">DataSet containing the data to be written to the Excel.</param>
/// <param name="excelFilename">Name of file to be written.</param>
/// <returns>True if successful, false if something went wrong.</returns>
public static bool CreateExcelDocument(DataSet ds, string excelFilename)
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
{
WriteExcelFile(ds, document);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = dt.TableName
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
excelColumnNames[n] = GetExcelColumnName(n);
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
var headerRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
}
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
var newExcelRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
}
}
private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static string GetExcelColumnName(int columnIndex)
{
// Convert a zero-based column index into an Excel column reference (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
//
// eg GetExcelColumnName(0) should return "A"
// GetExcelColumnName(1) should return "B"
// GetExcelColumnName(25) should return "Z"
// GetExcelColumnName(26) should return "AA"
// GetExcelColumnName(27) should return "AB"
// ..etc..
//
if (columnIndex < 26)
return ((char)('A' + columnIndex)).ToString();
char firstChar = (char)('A' + (columnIndex / 26) - 1);
char secondChar = (char)('A' + (columnIndex % 26));
return string.Format("{0}{1}", firstChar, secondChar);
}
}
Step 3:
Below given method in CreateExcelFileUtility.cs file will call to generate the excel document.
public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list));
return CreateExcelDocument(ds, xlsxFilePath);
}
We can call this method as given below by passing a list<T> and file path to save. This method return true, if file created and false, if file did not created
bool fileCreated = false;
var SampleList = reportsDataList.Select(x => new { Year = x.Year, Make = x.Make, Model = x.Model }).ToList();
fileCreated = CreateExcelFileUtility.CreateExcelDocument(disabledModelsList, filePath);
Export data form List<T> to excel workbook is very easy and fast using open XML as compare to other techniques.
Step 1:
Include references of DocumentFormat.OpenXml and WindowsBase
Step 2:
Add CreateExcelFileUtility.cs file in your project's Utility folder.
public class CreateExcelFileUtility
{
public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list));
return CreateExcelDocument(ds, xlsxFilePath);
}
#region HELPER_FUNCTIONS
public static DataTable ListToDataTable<T>(List<T> list)
{
DataTable dt = new DataTable();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
dt.Columns.Add(new DataColumn(info.Name, GetNullableType(info.PropertyType)));
}
foreach (T t in list)
{
DataRow row = dt.NewRow();
foreach (PropertyInfo info in typeof(T).GetProperties())
{
if (!IsNullableType(info.PropertyType))
row[info.Name] = info.GetValue(t, null);
else
row[info.Name] = (info.GetValue(t, null) ?? DBNull.Value);
}
dt.Rows.Add(row);
}
return dt;
}
private static Type GetNullableType(Type t)
{
Type returnType = t;
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
returnType = Nullable.GetUnderlyingType(t);
}
return returnType;
}
private static bool IsNullableType(Type type)
{
return (type == typeof(string) ||
type.IsArray ||
(type.IsGenericType &&
type.GetGenericTypeDefinition().Equals(typeof(Nullable<>))));
}
public static bool CreateExcelDocument(DataTable dt, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(dt);
bool result = CreateExcelDocument(ds, xlsxFilePath);
ds.Tables.Remove(dt);
return result;
}
public static bool DeleteFileFromDirectory(string DirectoryPath)
{
bool response = true;
try
{
System.IO.DirectoryInfo downloadedMessageInfo = new DirectoryInfo(DirectoryPath);
foreach (FileInfo file in downloadedMessageInfo.GetFiles())
{
file.Delete();
}
foreach (DirectoryInfo dir in downloadedMessageInfo.GetDirectories())
{
dir.Delete(true);
}
}
catch (Exception ex)
{
response = false;
}
return response;
}
#endregion
/// <summary>
/// Create an Excel file, and write it to a file.
/// </summary>
/// <param name="ds">DataSet containing the data to be written to the Excel.</param>
/// <param name="excelFilename">Name of file to be written.</param>
/// <returns>True if successful, false if something went wrong.</returns>
public static bool CreateExcelDocument(DataSet ds, string excelFilename)
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
{
WriteExcelFile(ds, document);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
spreadsheet.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = dt.TableName
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
excelColumnNames[n] = GetExcelColumnName(n);
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
var headerRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32");
}
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
var newExcelRow = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
}
}
private static void AppendTextCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference, DataType = CellValues.String };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static void AppendNumericCell(string cellReference, string cellStringValue, Row excelRow)
{
// Add a new Excel Cell to our Row
Cell cell = new Cell() { CellReference = cellReference };
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
}
private static string GetExcelColumnName(int columnIndex)
{
// Convert a zero-based column index into an Excel column reference (A, B, C.. Y, Y, AA, AB, AC... AY, AZ, B1, B2..)
//
// eg GetExcelColumnName(0) should return "A"
// GetExcelColumnName(1) should return "B"
// GetExcelColumnName(25) should return "Z"
// GetExcelColumnName(26) should return "AA"
// GetExcelColumnName(27) should return "AB"
// ..etc..
//
if (columnIndex < 26)
return ((char)('A' + columnIndex)).ToString();
char firstChar = (char)('A' + (columnIndex / 26) - 1);
char secondChar = (char)('A' + (columnIndex % 26));
return string.Format("{0}{1}", firstChar, secondChar);
}
}
Step 3:
Below given method in CreateExcelFileUtility.cs file will call to generate the excel document.
public static bool CreateExcelDocument<T>(List<T> list, string xlsxFilePath)
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list));
return CreateExcelDocument(ds, xlsxFilePath);
}
We can call this method as given below by passing a list<T> and file path to save. This method return true, if file created and false, if file did not created
bool fileCreated = false;
var SampleList = reportsDataList.Select(x => new { Year = x.Year, Make = x.Make, Model = x.Model }).ToList();
fileCreated = CreateExcelFileUtility.CreateExcelDocument(disabledModelsList, filePath);
