// Pass to param the excel file location (path)
public DataTable Read(string filePath)
{
try
{
DataTable dt = new DataTable();
FileInfo excelFile = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(excelFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
//add column header
foreach (var firstRC in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
{
dt.Columns.Add(firstRC.Text);
}
// add rows
for (int rN = 2; rN <= workSheet.Dimension.End.Row; rN++)
{
ExcelRange row = workSheet.Cells[rN, 1, rN, workSheet.Dimension.End.Column];
DataRow newR = dt.NewRow();
foreach (var cell in row)
{
newR[cell.Start.Column - 1] = cell.Text;
}
dt.Rows.Add(newR);
}
}
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
} /// <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;
}
}// Using OleDb:
private static DataTable ExcelToDataTable(string fileFullPath)
{
try
{
DataTable dt = new DataTable();
string StartingColumn = "A";
string EndingColumn = "D";
string StartReadingFromRow = "1";
string HDR = "YES";
string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileFullPath + ";Extended Properties="Excel 12.0;HDR=" + HDR + ";IMEX=0"";
OleDbConnection cnn = new OleDbConnection(ConStr);
cnn.Open();
DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = "";
foreach (DataRow drSheet in dtSheet.Rows)
{
if (drSheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetname = drSheet["TABLE_NAME"].ToString();
OleDbCommand oconn = new OleDbCommand("select * from ["
+ sheetname + StartingColumn + StartReadingFromRow + ":" + EndingColumn + "]", cnn);
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
adp.Fill(dt);
cnn.Close();
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
// ------------------------------------------------------
// using openxmlSDK:
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Core_Excel.Utilities
{
static class ExcelUtility
{
public static DataTable Read(string path)
{
var dt = new DataTable();
using (var ssDoc = SpreadsheetDocument.Open(path, false))
{
var sheets = ssDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
var relationshipId = sheets.First().Id.Value;
var worksheetPart = (WorksheetPart) ssDoc.WorkbookPart.GetPartById(relationshipId);
var workSheet = worksheetPart.Worksheet;
var sheetData = workSheet.GetFirstChild<SheetData>();
var rows = sheetData.Descendants<Row>().ToList();
foreach (var row in rows) //this will also include your header row...
{
var tempRow = dt.NewRow();
var colCount = row.Descendants<Cell>().Count();
foreach (var cell in row.Descendants<Cell>())
{
var index = GetIndex(cell.CellReference);
// Add Columns
for (var i = dt.Columns.Count; i <= index; i++)
dt.Columns.Add();
tempRow[index] = GetCellValue(ssDoc, cell);
}
dt.Rows.Add(tempRow);
}
}
return dt;
}
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
var stringTablePart = document.WorkbookPart.SharedStringTablePart;
var value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
return stringTablePart.SharedStringTable.ChildElements[int.Parse(value)].InnerText;
return value;
}
public static int GetIndex(string name)
{
if (string.IsNullOrWhiteSpace(name))
return -1;
int index = 0;
foreach (var ch in name)
{
if (char.IsLetter(ch))
{
int value = ch - 'A' + 1;
index = value + index * 26;
}
else
break;
}
return index - 1;
}
}
} 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);
}