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).

Answer 1

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.