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

Possible duplicate of [Reading Excel files from C#](http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp) — Steve, Nov 30 '15 at 08:23
For what it's worth, when I talk about Excel *tables* a fair percentage of the population doesn't always recognize that I mean an actual TABLE, as in a ListObject, new as of Excel 2007, etc. "Table" is often viewed as the same thing as a worksheet or range, which you clearly understand it is not. Just be mindful that when you use that word, it's not always going to click with people, even people familiar with Excel — Hambone, Dec 02 '15 at 19:34

Marlis Havroc · Answer 1 · 2015-12-02T19:12:23.777

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

In my code I am accessing the tables in excel. One sheet may have several tables — udaya726, Dec 01 '15 at 09:22

score 1 · Accepted Answer · answered Dec 02 '15 at 19:30

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;

Read excel table data using c#

2 Answers2