How to Read an Excel File in C# Without Using Microsoft.Office.Interop.Excel Libraries

How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

var fileName = @"C:\ExcelFile.xlsx";
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
    conn.Open();

    var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";

        var adapter = new OleDbDataAdapter(cmd);
        var ds = new DataSet();
        adapter.Fill(ds);
    }
}

Search For a code in Excel File without Excel Installed

There are many excel libraries in C#, most used, fast and easy one is EPPLUS.

Here is sample of reading a file (source: https://github.com/JanKallman/EPPlus/wiki/Getting-Started)

//Open the workbook (or create it if it doesn't exist)
    var fi=new FileInfo(@"c:\workbooks\myworkbook.xlsx")
    using (var p = new ExcelPackage(fi))
        {
           //Get the Worksheet created in the previous codesample. 
           var ws=p.Workbook.Worksheets["MySheet"];
           Set the cell value using row and column.
           ws.Cells[2, 1].Value = "This is cell A2. It is set to bolds";
           //The style object is used to access most cells formatting and styles.
           ws.Cells[2, 1].Style.Font.Bold=true;
           //Save and close the package.
           p.Save();
       }

How to read an excel and do the mapping

there are some good answers here:

How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries

It is always a good idea to read an excel file without being required to have office installed and you will see there an answer related to this, there are libraries that can do that for you easily.

Next, try to apply a little SOC ( separation of concerns ) in your code. This means you build a layer responsible for reading your excel files and that one returns some data in a format you can use in the UI. That's the layer you call and then you apply the results of that call to your DataGrid or whatever other UI thing you want to display it in.

unable to loop through open Excel workbooks in C#

I looked further into it and found the following solution that works for me.

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

private void PrintListOfOpenWorkbooks()
{
    Excel.Application xlApp;
 
    xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

    foreach(Excel.Workbook xlWorkBook in xlApp.Workbooks)
    {
        Console.WriteLine(xlWorkBook.Name);
    }
}

Maybe this will help other people in the future as well.

---

Related Topics