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