CODEWITHSUNDEEP
c#excel
user7557432
user7557432

Reputation:

Read from Excel file — specific sheet

Simply put: I need to read from an xlsx file (rows), in the simplest way. That is, preferably without using third-party tools, or at least things that aren't available as nuget packages.

I've been trying for a while with IExcelDatareader, but I cannot figure out how to get data from a specific sheet.

This simple code snippet works, but it just reads the first worksheet:

FileStream stream = File.Open("C:\\test\\test.xlsx", FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;

while (excelReader.Read()) {
    Console.WriteLine(excelReader.GetString(0));
}

This prints the rows in the first worksheet, but ignores the others. Of course, there is nothing to suggest otherwise, but I cannot seem to find out how to specify the sheet name.

It strikes me that this should be quite easy?

Sorry for asking something which has been asked several times before, but the answer (here and elsewhere on the net) are a jungle of bad, plain wrong and outdated half-answers that's a nightmare to try and make sense of. Especially since almost everyone answering assumes that you know some specific details that are not always easy to find.

UPDATE: As per daniell89's suggestion below, I've tried this:

FileStream stream = File.Open("C:\\test\\test.xlsx", FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;

// Select the first or second sheet - this works:
DataTable specificWorkSheet = excelReader.AsDataSet().Tables[1]; 

// This works: Printing the first value in each column
foreach (var col in specificWorkSheet.Columns) 
    Console.WriteLine(col.ToString());

// This does NOT work: Printing the first value in each row
foreach (var row in specificWorkSheet.Rows)
    Console.WriteLine(row.ToString());

Printing each column heading with col.ToString() works fine.

Printing the first cell of each row with row.ToString() results in this output:

System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
...

One per row, so it's obviously getting the rows. But how to get the contents, and why does ToString() work for the columns and not for the rows?

Upvotes: 3

Views: 13753

Answers (3)

Tawanda Madzidzise
Tawanda Madzidzise

Reputation: 26

It is a late reply but i hope it will help someone The script will be aiming at retrieving data from the first sheet and also to get the data of the first row 

 if (upload != null && upload.ContentLength > 0)
           {
               // ExcelDataReader works with the binary Excel file, so it needs a FileStream
               // to get started. This is how we avoid dependencies on ACE or Interop:
               Stream stream = upload.InputStream;

               // We return the interface, so that
               IExcelDataReader reader = null;


               if (upload.FileName.EndsWith(".xls"))
               {
                   reader = ExcelReaderFactory.CreateBinaryReader(stream);
               }
               else if (upload.FileName.EndsWith(".xlsx"))
               {
                   reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
               }
               else
               {
                   ModelState.AddModelError("File", "This file format is not supported");
                   return View();
               }

                var result = reader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = true


                    }
                }).Tables[0];// get the first sheet data with index 0
                var tables = result.Rows[0].Table.Columns;//we have to get a list of table headers here "first row" from 1 row
               foreach(var rue in tables)// iterate through the header list and add it to variable 'Headers'
                {
                    Headers.Add(rue.ToString());//Headers has been treated as a global variable  "private List<string> Headers = new List<string>();"
                }
                var count = Headers.Count();// test if the headers have been added using count
                reader.Close();

               return View(result);
           }
           else
           {
               ModelState.AddModelError("File", "Please Upload Your file");
           }

Upvotes: 0

ASH
ASH

Reputation: 20302

You need to get the Worksheet for the sheet you want to read data from. To get range A1 from Cars, for example:

var app = new Application();
Workbooks workbooks = app.Workbooks;
Workbook workbook = workbooks.Open(@"C:\MSFT Site Account Updates_May 2015.xlsx");

Worksheet sheet = workbook.Sheets["Cars"];

Range range = sheet.Range["A1"];

Upvotes: 0

daniell89
daniell89

Reputation: 2272

Maybe look at this answer: https://stackoverflow.com/a/32522041/5358389

DataSet workSheets= reader.AsDataSet();

And then specific sheet:

DataTable specificWorkSheet = reader.AsDataSet().Tables[yourValue];

Enumerating rows:

foreach (var row in specificWorkSheet.Rows)
     Console.WriteLine(((DataRow)row)[0]); // column identifier in square brackets

Upvotes: 5

Related Questions