/// <summary> /// Convert DataTable to Excel File /// </summary> /// <param name="filePath">Excel FilePath</param> /// <param name="table">DataTable</param> /// <param name="fileName">Excel Filename</param> /// <returns></returns> public bool Write(string filePath, DataTable table, string fileName) { try { bool success = false; var today = Convert.ToDateTime(DateTime.Now).ToString("yyyyMMddHHmmss"); //delete existing files // Util_Directory.DeleteFiles(filePath); var newDir = filePath + "\\" + today; //if dir not exist create Util_Directory.CreateDirectory(newDir); FileInfo file = new FileInfo(Path.Combine(newDir, fileName)); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (var package = new ExcelPackage(file)) { var workSheet = package.Workbook.Worksheets.Add("Sheet1"); //add column headers var columns = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList(); int i = 1; foreach (var column in columns) { workSheet.Cells[1, i].Value = column; i++; } // add row values int startRow = 2; foreach (DataRow row in table.Rows) { int startColumn = 1; foreach (var item in row.ItemArray) { workSheet.Cells[startRow, startColumn].Value = item; startColumn++; } startRow++; } workSheet.Cells.AutoFitColumns(); package.Save(); success = true; } return success; } catch (Exception) { throw; } }

 readonly DataTable dt = new DataTable(); /// <summary> /// Convert excel file to DataTable by xml configuration /// </summary> /// <param name="xmllFilePath">xml filepath</param> /// <param name="excelFilePath">excel filePath</param> /// <returns>DataTable</returns> public DataTable Read(string xmllFilePath, string excelFilePath) { try { List<string> xmlColumns = MiscUtil.ExtractXMLToList(xmllFilePath); List<string> xcelColumnNames = new List<string>(); FileInfo excelFile = new FileInfo(excelFilePath); ; ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage package = new ExcelPackage(excelFile)) { //get colmuns and store to list ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; { for (int i = 1; i <= workSheet.Dimension.End.Column; i++) { xcelColumnNames.Add(workSheet.Cells[1, i].Value.ToString()); } } //get indexes to compare xml and excel columns Dictionary<string, int> indexes = MiscUtil.GetDictionaryOfListArray(xmlColumns, xcelColumnNames.ToArray()); //get the match columns and add to datatable columns foreach (var header in indexes) { if (header.Value >= 0) { dt.Columns.Add(header.Key); } } // add rows foreach (ExcelWorksheet workSheetR in package.Workbook.Worksheets) { // int columnCnt = workSheetR.Dimension.End.Column; int rowCnt = workSheetR.Dimension.End.Row; var dicValue = indexes.Select(x => x.Value).ToList(); List<int> lstOfMatchColumn = dicValue.FindAll(x => x >= 0); List<string> columns = new List<string>(); for (int row = 2; row <= rowCnt; row++) { foreach (var col in lstOfMatchColumn) { columns.Add(workSheetR.Cells[row, col + 1].Value?.ToString().Trim()); } string[] columnsArray = MiscUtil.ConvertListToArray(columns); dt.Rows.Add(columnsArray); //clear so it will be new list of columns columns.Clear(); } } } return dt; } catch (Exception ex) { throw new Exception(ex.Message); }