/// <summary>
/// Reads the excel to the data table.
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable AsDataTable(this ExcelParam 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>
/// Writes the data table to the excel.
/// </summary>
/// <param name="table"></param>
/// <param name="param"></param>
/// <param name="createHeader"></param>
public static ExcelPackage ToExcel(this ExcelParam param, DataTable table, bool createHeader = true)
{
// If exists, auto width setting will throw exception.
if (File.Exists(param.FilePath))
{
File.Delete(param.FilePath);
}
File.Copy(param.TemplatePath, param.FilePath);
// 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 columns = table.Columns.Cast <DataColumn>().ToList();
// The titlt row.
for (var i = 0; createHeader && i < columns.Count; i++)
{
sheet.Cells[param.RowIndex, i + param.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 + param.RowIndex + 1, j + param.ColumnIndex].Value = table.Rows[i][columns[j].ColumnName];
}
}
sheet.SetExcelStyle();
excel.Save();
return(excel);
}
/// <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, ExcelParam 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 the cross array.
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public static object[][] AsCrossArray(this ExcelParam param)
{
return(param.As2DArray().Convert());
}
/// <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, ExcelParam param)
{
action?.Invoke(excel.Workbook.Worksheets[param.SheetName]);
excel.Save();
}
