CSHARP

c# read excel file into datatable

string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);

sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Test.xls;Extended Properties="Excel 12.0;HDR=No;IMEX=1"";

oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();

dtTablesList = oleExcelConnection.GetSchema("Tables");

if (dtTablesList.Rows.Count > 0) 
{
    sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}

dtTablesList.Clear();
dtTablesList.Dispose();


if (!string.IsNullOrEmpty(sSheetName)) {
    oleExcelCommand = oleExcelConnection.CreateCommand();
    oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
    oleExcelCommand.CommandType = CommandType.Text;
    oleExcelReader = oleExcelCommand.ExecuteReader();
    nOutputRow = 0;

    while (oleExcelReader.Read())
    {
    }
    oleExcelReader.Close();
}
oleExcelConnection.Close();

excel to datatable in c#

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