/// <summary>
/// Reads the excel to a two dimension array.
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public static object[,] As2DArray(this ExcelParameter parm)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
using (var excel = new ExcelPackage(new FileInfo(parm.FilePath)))
{
var sheets = excel.Workbook.Worksheets;
ExcelWorksheet sheet;
if (parm.SheetName is null)
{
sheet = sheets.FirstOrDefault();
}
else
{
sheet = sheets[parm.SheetName] ?? sheets.FirstOrDefault();
}
if (!(sheet?.Cells.Value is object[, ]))
{
return(new object[0, 0]);
}
return((object[, ])sheet.Cells.Value);
}
}
/// <summary>
/// Reads the excel to the cross array.
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static object[][] AsCrossArray(this ExcelParameter param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
return(param.As2DArray().Convert());
}
/// <summary>
/// Reads the excel to a DataTable.
/// </summary>
/// <param name="parm"></param>
/// <returns></returns>
public static DataTable AsDataTable(this ExcelParameter parm)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
DataTable results;
using (var excel = new ExcelPackage(new FileInfo(parm.FilePath)))
{
var sheets = excel.Workbook.Worksheets;
ExcelWorksheet sheet;
if (parm.SheetName is null)
{
sheet = sheets.FirstOrDefault();
}
else
{
sheet = sheets[parm.SheetName] ?? sheets.FirstOrDefault();
}
results = new DataTable(sheet?.Name);
if (!(sheet?.Cells.Value is object[,] cells))
{
return(results);
}
for (var j = parm.ColumnIndex; j < sheet.Dimension.Columns; j++)
{
results.Columns.Add(new DataColumn(cells[0, j]?.ToString()));
}
for (var i = parm.RowIndex; i < sheet.Dimension.Rows; i++)
{
var row = results.NewRow();
for (var j = parm.ColumnIndex; j < sheet.Dimension.Columns; j++)
{
row[j - parm.ColumnIndex] = cells[i - parm.RowIndex, j];
}
results.Rows.Add(row);
}
}
return(results);
}
/// <summary>
/// Writes the list to the excel.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="objs"></param>
/// <param name="param"></param>
/// <param name="createHeader"></param>
public static void ToExcel <T>(this ExcelParameter param, IEnumerable <T> objs, bool createHeader = true)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
if (objs == null)
{
throw new ArgumentNullException(nameof(objs));
}
// If exists, auto width setting will throw exception.
if (param.FilePath.Exists)
{
File.Delete(param.FilePath.FullName);
}
File.Copy(param.TemplatePath.FullName, param.FilePath.FullName);
// Epplus dll write excel file that column index from 1 to end column index and row index from 0 to end row index.
param.ColumnIndex += 1;
var excel = param.GetExcelPackage(out var sheet);
var ps = typeof(T).GetProperties();
// The titlt row.
if (createHeader)
{
for (var i = 0; i < ps.Length; i++)
{
sheet.Cells[param.RowIndex, i + param.ColumnIndex].Value = ps[i].GetDcrp();
}
}
var tmpObjs = objs.ToList();
// The content row.
for (var i = 0; i < tmpObjs.Count; i++)
{
for (var j = 0; j < ps.Length; j++)
{
sheet.Cells[i + param.RowIndex + 1, j + param.ColumnIndex].Value = ps[j].GetValue(tmpObjs[i]);
}
}
sheet.SetExcelStyle();
excel.Save();
}
/// <summary>
/// Reads the excel to the list.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="param"></param>
/// <returns></returns>
public static List <T> AsList <T>(this ExcelParameter param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
var results = new List <T>();
var ps = typeof(T).GetProperties();
using (var excel = new ExcelPackage(param.FilePath))
{
var sheets = excel.Workbook.Worksheets;
var sheet = (param.SheetName == null
? sheets.FirstOrDefault()
: sheets[param.SheetName]) ?? sheets.FirstOrDefault();
if (!(sheet?.Cells.Value is object[,] cells))
{
return(new List <T>());
}
for (var i = param.RowIndex; i < sheet.Dimension.Rows; i++)
{
var obj = (T)Activator.CreateInstance(typeof(T));
for (var j = param.ColumnIndex; j < typeof(T).GetProperties().Length + param.ColumnIndex; j++)
{
var columnName = cells[0, j]?.ToString();
var pls = ps.Where(w =>
w.GetDcrp().Equals(columnName) || w.Name.Equals(cells[param.RowIndex, j]));
foreach (var p in pls)
{
var val = p.PropertyType.IsEnum
? Enum.Parse(p.PropertyType, cells[i, j].ToString())
: Convert.ChangeType(cells[i, j], p.PropertyType);
p.SetValue(obj, cells[i, j] != DBNull.Value ? val : null, null);
break;
}
}
results.Add(obj);
}
}
return(results);
}
/// <summary>
/// Writes the DataTable to a excel.
/// </summary>
/// <param name="table"></param>
/// <param name="parm"></param>
/// <param name="createHeader"></param>
public static ExcelPackage ToExcel(this ExcelParameter parm, DataTable table, bool createHeader = true)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
if (table is null)
{
throw new ArgumentNullException(nameof(table));
}
// If exists, auto width setting will throw exception.
if (File.Exists(parm.FilePath))
{
File.Delete(parm.FilePath);
}
File.Copy(parm.TemplatePath, parm.FilePath);
// Epplus dll write excel file that column index from 1 to end, row index from 1 to end.
parm.ColumnIndex += 1;
parm.RowIndex += 1;
var excel = parm.GetExcelPackage(out var sheet);
var columns = table.Columns.Cast <DataColumn>().ToList();
// The titlt row.
for (var i = 0; createHeader && i < columns.Count; i++)
{
sheet.Cells[parm.RowIndex, i + parm.ColumnIndex].Value = columns[i].ColumnName;
}
// The cotent row.
for (var i = 0; i < table.Rows.Count; i++)
{
for (var j = 0; j < columns.Count; j++)
{
sheet.Cells[i + parm.RowIndex + 1, j + parm.ColumnIndex].Value = table.Rows[i][columns[j].ColumnName];
}
}
sheet.SetExcelStyle();
excel.Save();
return(excel);
}
/// <summary>
/// Gets the excel object.
/// </summary>
/// <param name="param"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static ExcelPackage GetExcelPackage(this ExcelParameter param, out ExcelWorksheet sheet)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
var result = new ExcelPackage(param.FilePath);
var sheets = result.Workbook.Worksheets;
sheet = sheets.FirstOrDefault(f => f.Name.ToLower() == param.SheetName.ToLower()) != null
? sheets[param.SheetName]
: sheets.Add(param.SheetName);
return(result);
}
/// <summary>
/// Gets the excel object.
/// </summary>
/// <param name="parm"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static ExcelPackage GetExcelPackage(this ExcelParameter parm, out ExcelWorksheet sheet)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
var result = new ExcelPackage(new FileInfo(parm.FilePath));
var sheets = result.Workbook.Worksheets;
sheet = sheets.FirstOrDefault(f => string.Equals(f.Name.ToLower(), parm.SheetName.ToLower()));
sheet = sheet != null ? sheets[parm.SheetName] : sheets.Add(parm.SheetName);
return(result);
}
/// <summary>
/// Writes the two dimension array to a excel.
/// </summary>
/// <param name="parm"></param>
/// <param name="table"></param>
/// <returns></returns>
public static ExcelPackage ToExcel(this ExcelParameter parm, object[,] table)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
if (table is null)
{
throw new ArgumentNullException(nameof(table));
}
// If exists, auto width setting will throw exception.
if (File.Exists(parm.FilePath))
{
File.Delete(parm.FilePath);
}
File.Copy(parm.TemplatePath, parm.FilePath);
// Epplus dll write excel file that column index from 1 to end, row index from 1 to end.
parm.ColumnIndex += 1;
parm.RowIndex += 1;
var excel = parm.GetExcelPackage(out var sheet);
for (var i = 0; i < table.GetLength(0); i++)
{
for (var j = 0; j < table.GetLength(1); j++)
{
sheet.Cells[i + parm.RowIndex, j + parm.ColumnIndex].Value = table[i, j];
}
}
sheet.SetExcelStyle();
excel.Save();
return(excel);
}
/// <summary>
/// Reads the excel to the two dimension array.
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static object[,] As2DArray(this ExcelParameter param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
using (var excel = new ExcelPackage(param.FilePath))
{
var sheets = excel.Workbook.Worksheets;
var sheet = (param.SheetName == null
? sheets.FirstOrDefault()
: sheets[param.SheetName]) ?? sheets.FirstOrDefault();
if (!(sheet?.Cells.Value is object[, ]))
{
return(new object[0, 0]);
}
return((object[, ])sheet.Cells.Value);
}
}
/// <summary>
/// Reads the excel to the data table.
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable AsDataTable(this ExcelParameter param)
{
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
var results = new DataTable();
using (var excel = new ExcelPackage(param.FilePath))
{
var sheets = excel.Workbook.Worksheets;
var sheet = (param.SheetName == null
? sheets.FirstOrDefault()
: sheets[param.SheetName]) ?? sheets.FirstOrDefault();
if (!(sheet?.Cells.Value is object[,] cells))
{
return(new DataTable());
}
for (var j = param.ColumnIndex; j < sheet.Dimension.Columns; j++)
{
results.Columns.Add(new DataColumn(cells[0, j]?.ToString()));
}
for (var i = param.RowIndex; i < sheet.Dimension.Rows; i++)
{
for (var j = param.ColumnIndex; j < sheet.Dimension.Columns; j++)
{
results.Rows[i - param.RowIndex][j] = cells[i + param.RowIndex, j];
}
}
}
return(results);
}
/// <summary>
/// Sets custom style.
/// </summary>
/// <param name="excel"></param>
/// <param name="action"></param>
/// <param name="param"></param>
public static void SetExcelStyle(this ExcelPackage excel, Action <ExcelWorksheet> action, ExcelParameter param)
{
if (excel == null)
{
throw new ArgumentNullException(nameof(excel));
}
if (action == null)
{
throw new ArgumentNullException(nameof(action));
}
if (param == null)
{
throw new ArgumentNullException(nameof(param));
}
action(excel.Workbook.Worksheets[param.SheetName]);
excel.Save();
}
/// <summary>
/// Reads the excel to a List.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="parm"></param>
/// <returns></returns>
public static List <T> AsList <T>(this ExcelParameter parm)
{
if (parm is null)
{
throw new ArgumentNullException(nameof(parm));
}
var results = new List <T>();
var ps = typeof(T).GetProperties();
using (var excel = new ExcelPackage(new FileInfo(parm.FilePath)))
{
var sheets = excel.Workbook.Worksheets;
ExcelWorksheet sheet;
if (parm.SheetName is null)
{
sheet = sheets.FirstOrDefault();
}
else
{
sheet = sheets[parm.SheetName] ?? sheets.FirstOrDefault();
}
if (!(sheet?.Cells.Value is object[,] cells))
{
return(new List <T>());
}
// To skip the titlt row, so add 1.
for (var i = parm.RowIndex + 1; i < sheet.Dimension.Rows; i++)
{
var obj = (T)Activator.CreateInstance(typeof(T));
for (var j = parm.ColumnIndex; j < typeof(T).GetProperties().Length; j++)
{
var columnName = cells[parm.RowIndex, j]?.ToString();
var parmInfo = ps.FirstOrDefault(w => w.GetDcrp().Equals(columnName) || w.Name.Equals(columnName));
if (parmInfo == null)
{
continue;
}
var cellVal = cells[i, j];
object val;
if (parmInfo.PropertyType.IsEnum)
{
val = Enum.Parse(parmInfo.PropertyType, cellVal.ToString());
}
else
{
val = Convert.ChangeType(cellVal, parmInfo.PropertyType);
}
parmInfo.SetValue(obj, cellVal != DBNull.Value ? val : null, null);
}
results.Add(obj);
}
}
return(results);
}
