The best way to read an Excel file (.xls / .xlsx)

Question

I know that there are different ways to read an Excel file:

• Iterop
• Oledb
• Open Xml SDK

Compatibility is not an issue because the program will run in a controlled environment.

My requirements:
Read the file in the DataTable / CUstom Entitie (I don’t know how to make dynamic properties / fields for the object [column names will change in the Excel file])

Use DataTable/Custom Entities to perform some operations using your data.

Refresh DataTable with Operation Results

Write it back to the excel file .

Which would be easier.

Also, if possible, advise me on custom objects (dynamically adding properties / fields to the object)

Answer 1

Take a look at Linq-to-Excel . This is pretty neat.

 var book = new LinqToExcel.ExcelQueryFactory(@"File.xlsx"); var query = from row in book.Worksheet("Stock Entry") let item = new { Code = row["Code"].Cast<string>(), Supplier = row["Supplier"].Cast<string>(), Ref = row["Ref"].Cast<string>(), } where item.Supplier == "Walmart" select item; 

It also allows the use of strongly typed string access.

Answer 2

Using OLE Query, this is pretty simple (e.g. sheetName is Sheet1):

 DataTable LoadWorksheetInDataTable(string fileName, string sheetName) { DataTable sheetData = new DataTable(); using (OleDbConnection conn = this.returnConnection(fileName)) { conn.Open(); // retrieve the data using data adapter OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "$]", conn); sheetAdapter.Fill(sheetData); conn.Close(); } return sheetData; } private OleDbConnection returnConnection(string fileName) { return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\""); } 

For newer versions of Excel:

 return new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=Excel 12.0;"); 

You can also use the open source Excel Data Reader in CodePlex. It works very well for exporting data from Excel worksheets.

Sample code is given at the specified link:

 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(); 

Help: How to import from Excel to a dataset using Microsoft.Office.Interop.Excel?

Answer 3

I understand that this question was asked almost 7 years ago, but it is still the best Google search result for certain keywords regarding importing Excel data from C #, so I wanted to provide an alternative based on some recent technical developments.

Importing Excel data has become so common in my day-to-day responsibilities that I simplified the process and documented the method on my blog: the best way to read an Excel file in c # .

I use NPOI because it can read / write Excel files without Microsoft Office installed and does not use COM + or any operations. This means that it can work in the cloud!

But the real magic is pairing with Donny Tian's NPOI Mapper, because it allows me to map Excel columns to properties in my C # classes without writing any code. It is beautiful.

Here is the basic idea:

I create a .net class that matches / maps the Excel columns that interest me:

  class CustomExcelFormat { [Column("District")] public int District { get; set; } [Column("DM")] public string FullName { get; set; } [Column("Email Address")] public string EmailAddress { get; set; } [Column("Username")] public string Username { get; set; } public string FirstName { get { return Username.Split('.')[0]; } } public string LastName { get { return Username.Split('.')[1]; } } } 

Please note this allows me to display based on the column name if I want!

Then, when I process the Excel file, all I need to do is something like this:

  public void Execute(string localPath, int sheetIndex) { IWorkbook workbook; using (FileStream file = new FileStream(localPath, FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); } var importer = new Mapper(workbook); var items = importer.Take<CustomExcelFormat>(sheetIndex); foreach(var item in items) { var row = item.Value; if (string.IsNullOrEmpty(row.EmailAddress)) continue; UpdateUser(row); } DataContext.SaveChanges(); } 

Now, admittedly, my code does not modify the excel file itself. Instead, I save the data in the database using the Entity Framework (which is why you see "UpdateUser" and "SaveChanges" in my example). But SO already has a good discussion on how to save / modify the file using NPOI .

Answer 4

Try this free method, https://freenetexcel.codeplex.com

  Workbook workbook = new Workbook(); workbook.LoadFromFile(@"..\..\parts.xls",ExcelVersion.Version97to2003); //Initialize worksheet Worksheet sheet = workbook.Worksheets[0]; DataTable dataTable = sheet.ExportDataTable(); 

Answer 5

If you can restrict it simply (Open Office XML format) * .xlsx, then EPPLus is probably the most popular library.

There is a bonus, there are no other dependencies. Just install using nuget:

 Install-Package EPPlus 

Answer 6

Personally, I found open source & free ExcelMapper the easiest way to work.

It provides a much shorter (i.e. readable) way to read Excel files compared to regular Microsoft.Interop & OLE queries.

1. There is an Excel file :

2. Create a C # Person object:

 public class Person { public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } } 

3. Read it using ExcelMapper

  var fileName = @"C:\Temp\Names.xlsx"; // your excel file List<Person> people = new ExcelMapper(fileName).Fetch<Person>(); 

You can also read from other tables simply by passing an extra sheet argument:

  var fileName = @"C:\Temp\Names.xlsx"; // your excel file List<Person> people = new ExcelMapper(fileName).Fetch<Person>("Sheet2"); 

You can install it using NuGet

 Install-Package ExcelMapper 

Disclaimer: I am not affiliated with ExcelMapper, but having tried various libraries, I found that it is easiest to work with this library.

Here is a short video demonstrating the above.

Answer 7

Try using the Aspose.cells library, this is not bad.

Install-package Aspose.cells

Code example:

 using Aspose.Cells; using System; namespace ExcelReader { class Program { static void Main(string[] args) { // Replace path for your file readXLS(@"C:\MyExcelFile.xls"); // or "*.xlsx" Console.ReadKey(); } public static void readXLS(string PathToMyExcel) { //Open your template file. Workbook wb = new Workbook(PathToMyExcel); //Get the first worksheet. Worksheet worksheet = wb.Worksheets[0]; //Get cells Cells cells = worksheet.Cells; // Get row and column count int rowCount = cells.MaxDataRow; int columnCount = cells.MaxDataColumn; // Current cell value string strCell = ""; Console.WriteLine(String.Format("rowCount={0}, columnCount={1}", rowCount, columnCount)); for (int row = 0; row <= rowCount; row++) // Numeration starts from 0 to MaxDataRow { for (int column = 0; column <= columnCount; column++) // Numeration starts from 0 to MaxDataColumn { strCell = ""; strCell = Convert.ToString(cells[row, column].Value); if (String.IsNullOrEmpty(strCell)) { continue; } else { // Do your staff here Console.WriteLine(strCell); } } } } } }