What is the best way to read an XLS file?

Question

I would like to know the best way to read an XLS file in C#.

I can handle it the same way I handle a database table?

Answer 1

What is the best way I can not say, because what is best for one case can be worse for another. I can give options.

In addition to the method already cited by Reiksiel, there are some other options:

There is the library Epplus which is one of those used for this function.

You can use the library Excel Data Reader which is a project open source who does the hard work for you. I’ve never used it but there are many people who use it.

There is also the excellibrary. I have no further information.

There is still the Free . NET Excel API that [and a "capped" version of commercial software. Just to test it and see if it’s worth buying.

If using LINQ is very important to you, you have the Linq to Excel. I don’t know anyone using.

There are a few ways to treat worksheets as XML. A example in the OS.

Some of these solutions only work with the XLSX. I don’t know if your case is strictly for the XLS.

As you did not give parameters, I can say that there are several commercial components of several well-known companies in the market. A simple search will lead you to them.

I won’t quote the more "nuts' solutions" :)

Answer 2

There is the option to use Oledb and read the Excel file as a table in a database...

An example.....

string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;Extended Properties='Excel 8.0;HDR=Yes;'"
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection) 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

This example uses the Microsoft.Jet.OleDb.4.0 provider to open and read an Excel file. However, if the file is of type xlsx (from Excel 2007 and later versions), then you will need to download the Microsoft Data Access components and install it on the machine. The provider will be Microsoft.ACE.OLEDB.12.0;. No need to have Office on the machine.

Source: Here

Answer 3

Using Microsoft.Office.Interop.Excel

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
// add reference - COM - Microsoft Excel 15.0
// http://csharp.net-informations.com/excel/csharp-read-excel.htm
namespace excelop
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void btlerxls_Click(object sender, EventArgs e)
    {
      Excel.Application xlApp;
      Excel.Workbook xlWorkBook;
      Excel.Worksheet xlWorkSheet;
      Excel.Range range;

      string str;
      int rCnt;
      int cCnt;
      int rw = 0;
      int cl = 0;

      xlApp = new Excel.Application();
      xlWorkBook = xlApp.Workbooks.Open(edarquivo.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

      range = xlWorkSheet.UsedRange;
      rw = range.Rows.Count;
      cl = range.Columns.Count;


      for (rCnt = 1; rCnt <= rw; rCnt++)
      {
        for (cCnt = 1; cCnt <= cl; cCnt++)
        {
          str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
          memo1.Text += rCnt.ToString() + " " + cCnt.ToString() + " " + str + "\r\n";
        }
      }
      xlWorkBook.Close(true, null, null);
      xlApp.Quit();
      Marshal.ReleaseComObject(xlWorkSheet);
      Marshal.ReleaseComObject(xlWorkBook);
      Marshal.ReleaseComObject(xlApp);
    }
  }
}

Reference