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