how to read .xls and .xlsx and .xlsm files in c#

Question

I used openfiledialog and filter it to get: .xls and .xlsx and .xlsm files. but I don't know what to do next, I build a class of workers with firstName and lastName and I want to take the data from the excel file and put it in the varible.

This is my code of the openfiledialog:

 private void ExcelLoad_Click(object sender, EventArgs e)
    {
        int size = -1;
        openFileDialog1.Title = "Browse Excel file";
        openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm";
        DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
        if (result == DialogResult.OK) // Test result.
        {
                string file = openFileDialog1.FileName;
                try
                {
                    string text = File.ReadAllText(file);
                    size = text.Length;
                }
                catch (IOException)
                {
                }
        }
        Console.WriteLine(size); // <-- Shows file size in debugging mode.
        Console.WriteLine(result); // <-- For debugging use.
        Stream  excelOpenFile= openFileDialog1.OpenFile();
    }

so how can I read the data from this kind of files.(is succed to open it but i dont know how to use the file and get the data from it).

You need to do research before asking here. Google "open excel file in C#" — Jeremy Thompson, Jul 19 '15 at 14:39
possible duplicate of [Reading Excel files from C#](http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp) — Jeremy Thompson, Jul 19 '15 at 14:41
possible duplicate of [How to open an Excel file in C#?](http://stackoverflow.com/questions/464902/how-to-open-an-excel-file-in-c) — Behzad, Jul 19 '15 at 14:43

score 7 · Accepted Answer · edited May 23 '17 at 11:53

You need to use the Library for Reading XLS Files,

Refer Reading Excel files from C# & https://github.com/ExcelDataReader/ExcelDataReader

UPDATE 1: From Github, how to use it, Install the package as nuget package.

It is recommended to use Nuget

Install-Package ExcelDataReader

The current binaries are still on the codeplex site, but these will not be updated going forward. If there are enough requests for separate binary hosting other than nuget then we'll come up with some other solution.

UPDATE 2: Code for reading from Excel Data Reader

    FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();

//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

After step 3, you would be getting the Excel data in a DataSet

 DataSet result = excelReader.AsDataSet();

Next, you can iterate over the DataSet with following code

foreach (DataColumn col in result.Table[0].Columns)
{
     foreach (DataRow row in result.Table[0].Rows)
     {
          Console.WriteLine(row[col.ColumnName].ToString());           
     }
}

P.S : I have used Table[0] just to specify the first DataTable or data from first excel sheet. you can iterate over the various Data Table as well.

Updated the answer with information "How to install the package" — Abhinav Galodha, Jul 19 '15 at 15:20
thank you! and i have a quation, i wrote "excelReader.GetValue(0)" (after do all the staff before) and it work but i cant go to the other rows just to the culloms. what should i do — Nir, Jul 19 '15 at 15:59

user10053673 · Answer 2 · 2019-10-01T14:05:58.063

The latest version of ExcelDataReader (v3.6) supports the reading of .xlsm files. It can be installed directly using the NuGet package manager within Visual Studio. I've tested it in VS2019 Community edition and it works. Excel doesn't need to be installed, it doesn't use Iterop or Oledb. However, .NET framework v4.7 or higher is required on the target machines running the application.

More info... https://www.youtube.com/watch?v=_h_4-HxrMMc

Source code

using ExcelDataReader;

...

class ExcelReader {

    public static DataTable ExcelToDataTable(String fileName) {

        using(var stream = File.Open(fileName, FileMode.Open, FileAccess.Read)) {
            using (var reader = ExcelReaderFactory.CreateReader(stream)) {
                var result = reader.AsDataSet(new ExcelDataSetConfiguration() {
                    ConfigureDataTable = (data) => new ExcelDataTableConfiguration() {
                        UseHeaderRow = true
                    }
                });

                DataTableCollection table = result.Tables;
                DataTable resultTable = table["Blad1"];
                return resultTable;
            }
        }

    }
}

Note: Don't forget to install the "ExcelDataReader.Dataset" package as well.

how to read .xls and .xlsx and .xlsm files in c#

2 Answers2

Linked