Read excel table data using c#

Question

I have excel file with several excel tables. I want to read all the data in excel tables for a given excel sheet and copy those to a different excel table. One excel sheet may have several tables

I have done that through VBA. I want to find a C# code to achieve it. This is the VBA code I used.

Dim tableObject As ListObject
Dim oLastRow As ListRow
Dim srcRow As Range

Set sheet = ThisWorkbook.Worksheets("Sheet1")
For Each tableObject In sheet.ListObjects
Set srcRow = tableObject.DataBodyRange
Set oLastRow = Worksheets("Sheet2").ListObjects("table1").ListRows.Add
srcRow.Copy
oLastRow.Range.PasteSpecial xlPasteValues
Next

Answer 1

I think you'll find that Excel's COM interface is very similar to VBA in that the class libraries are all identical. The only challenge is in grabbing the Excel instance and then managing the syntactical differences (ie VBA uses parentheses for indexers, and C# uses square brackets).

Assuming your VBA works, this should be the identical code in C# to grab an open instance of Excel and do what you have done. If you want to open a new instance of Excel, that's actually even easier (and very easy to Google):

Excel.Application excel;
Excel.Workbook wb;

try
{
    excel = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
    excel.Visible = true;
    wb = (Excel.Workbook)excel.ActiveWorkbook;
}
catch (Exception ex)
{
    ErrorMessage = "Trouble Locating Open Excel Instance";
    return;
}

Excel.Worksheet sheet = wb.Worksheets["Sheet1"];

foreach (Excel.ListObject lo in sheet.ListObjects)
{
    Excel.Range srcRow = lo.DataBodyRange;
    Excel.ListRow oLastRow = wb.Worksheets["Sheet2"].ListObjects["table1"].ListRows.Add();
    srcRow.Copy();
    oLastRow.Range.PasteSpecial(Excel.XlPasteType.xlPasteValues);
}

This all presupposes you have referenced Excel COM and set your using:

using Excel = Microsoft.Office.Interop.Excel;

Answer 2

Try to follow this:

public static DataTable exceldata(string filePath)
    {     
        DataTable dtexcel = new DataTable();
           bool hasHeaders = false;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            //Looping Total Sheet of Xl File
            /*foreach (DataRow schemaRow in schemaTable.Rows)
            {
            }*/
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [" + sheet3 + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtexcel);
            }

        conn.Close();
        return dtexcel;

    }

You can also use the following part of code to import data from tables inside your excel sheet

static void Main(string[] args)
{
try
{
    ReadInData readInData = new ReadInData(@"C:\SC.xlsx", "sc_2014");
    IEnumerable<Recipient> recipients = readInData.GetData();
}
catch (Exception ex)
{
    if(!(ex is FileNotFoundException || ex is ArgumentException || ex is FileToBeProcessedIsNotInTheCorrectFormatException))
        throw;
    Console.WriteLine(ex.Message);
}
Console.Write(Press any key to continue...);
Console.ReadKey(true);
}

public static class ReadInData
{
public static IEnumerable<Recipient> GetData(string path, string worksheetName, bool isFirstRowAsColumnNames = true)
{
    return new ExcelData(path).GetData(worksheetName, isFirstRowAsColumnNames)
        .Select(dataRow => new Recipient()
        {
            Municipality = dataRow["Municipality"].ToString(),
            Sexe = dataRow["Sexe"].ToString(),
            LivingArea = dataRow["LivingArea"].ToString()
        });
}
}

private static IExcelDataReader GetExcelDataReader(string path, bool isFirstRowAsColumnNames)
{
using (FileStream fileStream = File.Open(path, FileMode.Open, FileAccess.Read))
{
    IExcelDataReader dataReader;

    if (path.EndsWith(".xls"))
        dataReader = ExcelReaderFactory.CreateBinaryReader(fileStream);
    else if (path.EndsWith(".xlsx"))
        dataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
    else
        throw new FileToBeProcessedIsNotInTheCorrectFormatException("The file to be processed is not an Excel file");

    dataReader.IsFirstRowAsColumnNames = isFirstRowAsColumnNames;
    return dataReader;
}
}

private static DataSet GetExcelDataAsDataSet(string path, bool isFirstRowAsColumnNames)
{
return GetExcelDataReader(path, isFirstRowAsColumnNames).AsDataSet();
}

private static DataTable GetExcelWorkSheet(string path, string workSheetName, bool isFirstRowAsColumnNames)
{
DataTable workSheet = GetExcelDataAsDataSet(path, isFirstRowAsColumnNames).Tables[workSheetName];
if (workSheet == null)
    throw new WorksheetDoesNotExistException(string.Format("The worksheet {0} does not exist, has an incorrect name, or does not have any data in the worksheet", workSheetName));
return workSheet;
    }

private static IEnumerable<DataRow> GetData(string path, string workSheetName, bool isFirstRowAsColumnNames = true)
{
return from DataRow row in GetExcelWorkSheet(path, workSheetName, isFirstRowAsColumnNames).Rows select row;
}