.NET Excel Library that can read/write .xls files [closed]

Question

I'm looking for an Excel library which reads/writes .xls (not .xlsx) files.

I'm using excellibrary but it's very buggy and I can't seem to open the files I create. The issue has been known for almost a year and hasn't been fixed yet.

I've seen in another thread someone suggested EPPlus, but that only works with the 2007/2010 .xlsx format.

I've used Koogra in the past, and been quite happy with it, but I believe it can only read... not write.

If you know of a library, please let me know what it's called.

Edit: I'm quite happy to create my Excel file with the built-in Microsoft.Office.Interop.Excel if necessary, however my machine has Office 2007 and the target machines only have Office 2003. I noticed I have 2 file versions for that library: 12, and 14. I checked the Excel version on the target machines and it's 11.8169.8172 - I'm assuming that the built-in Excel interop will not work on the target?

Colangelo

Must you absolutely work directly with XLS files? Have you ruled out just using CSV files? – Medley

I absolutely must use XLS, and not CSV or XLSX. I'm uploading the result to a third-party web service and hence have zero control over the file format. – Colangelo

I know I sound stupid because I have never used C#, but can C# use late binding? If so, you can just do the CreateObject("Excel.Application") thingamuhjig (well, you can in VB/VBA/VB.NET anyway). No need for all the other libraries, or hell, even references. – Hellenistic

Answer 1

I'd recommend NPOI. NPOI is FREE and works exclusively with .XLS files. It has helped me a lot.

Detail: you don't need to have Microsoft Office installed on your machine to work with .XLS files if you use NPOI.

Check these blog posts:

Creating Excel spreadsheets .XLS and .XLSX in C#

NPOI with Excel Table and dynamic Chart

[UPDATE]

NPOI 2.0 added support for XLSX and DOCX.

You can read more about it here:

NPOI 2.0 series of posts scheduled

Lawtun

Thanks. Actually I already started using NPOI shortly after posting the question and am still using it. There is not a lot of doc and the namespaces all have very unfriendly abbreviations, but I managed to figure it out and it's working :) – Colangelo

that's good @Ozzah, but could you manipulate the xls template using Npoi? – Cherenkov

The codeplex link seems a bit out of date shouldn't this be pointing to github.com/tonyqus/npoi which shows that the project is still active? – Heredity

Also NPOI like POI doesn't support BIFF formats below BIFF8 - just get that annoying "The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)". For those looking for a solution to this issue this might help - https://mcmap.net/q/340843/-excel-biff5-to-biff8-conversion – Heredity

thanks ...was using epplus...i think epplus should remove this bug..hahhahah 2nd person moving from epplus to NPOI – Barnwell

Answer 2

Is there a reason why you can't use the Excel ODBC connection to read and write to Excel? For example, I've used the following code to read from an Excel file row by row like a database:

private DataTable LoadExcelData(string fileName)
{
  string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";

  OleDbConnection con = new OleDbConnection(Connection);

  OleDbCommand command = new OleDbCommand();

  DataTable dt = new DataTable(); OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$] WHERE LastName <> '' ORDER BY LastName, FirstName", con);

  myCommand.Fill(dt);

  Console.WriteLine(dt.Rows.Count);

  return dt;
}

You can write to the Excel "database" the same way. As you can see, you can select the version number to use so that you can downgrade Excel versions for the machine with Excel 2003. Actually, the same is true for using the Interop. You can use the lower version and it should work with Excel 2003 even though you only have the higher version on your development PC.

Complect

I know this is a rather old post, but I wanted to state as a notice that I get "out of memory" error when I try to read ~35k rows in a sheet with this method. – Humblebee

I am using this approach and faced the issue of having to install excel components on the deployment target. Any workarounds for that? installing access database component is not an option either. – Hanley

If you don't have Excel on the deployment target, you should look at another solution to do this. Look at the accepted solution above for working with Excel without Excel being installed. – Complect

Answer 3

You may consider 3rd party tool that called Excel Jetcell .NET component for read/write excel files:

C# sample

// Create New Excel Workbook
ExcelWorkbook Wbook = new ExcelWorkbook();
ExcelCellCollection Cells = Wbook.Worksheets.Add("Sheet1").Cells;

Cells["A1"].Value = "Excel writer example (C#)";
Cells["A1"].Style.Font.Bold = true;
Cells["B1"].Value = "=550 + 5";

// Write Excel XLS file
Wbook.WriteXLS("excel_net.xls");

VB.NET sample

' Create New Excel Workbook
Dim Wbook As ExcelWorkbook = New ExcelWorkbook()
Dim Cells As ExcelCellCollection = Wbook.Worksheets.Add("Sheet1").Cells

Cells("A1").Value = "Excel writer example (C#)"
Cells("A1").Style.Font.Bold = True
Cells("B1").Value = "=550 + 5"

' Write Excel XLS file
Wbook.WriteXLS("excel_net.xls")

Bobodioulasso

the free option only allows for unformatted export. otherways it's payed – Muzzleloader