Read and Write Excel Files C#

Question

Is there any easy to implement library that can be used to read excel files and may be create them later on? is this my best bet?

http://support.microsoft.com/kb/302084

Answer 1

Try this: http://epplus.codeplex.com

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

Answer 2

If you are willing to commit yourself to a later version of Excel (2007+) you can also take a look at the OpenXML SDK. It's free, doesn't tie you to having MS Office installed on the machine it will be running on and there are quite a few resources available on how to use it online (including blogs from the OpenXML team).

Answer 3

There is excel package plus:

http://epplus.codeplex.com/

Only works on xlsx though, but Office 2003 is cycling out anyway.

Answer 4

You can use ExcelLibrary ,Although it works for .xls only which is 2003 format

The aim of this project is provide a native .NET solution to create, read and modify Excel files without using COM interop or OLEDB connection.

I had a chance of using EPPLUS ,it was wonderful :) ,It works for new excel format .xlsx which is used in 2007/2010

EPPlus is a .net library , you can read and write to excel files ,create charts ,pictures ,shapes... and Much more

Also take a look at this SO post

Answer 5

I've used oledb, interop and just started using Epplus. So far epplus is proving to be simplest. http://epplus.codeplex.com/

However, I just posted a problem I have with epplus, but I posted some code you could use as reference.

c# epplus error Removed Part: Drawing shape

Answer 6

I like to use ExcelDataReader for reading and the aforementioned EPPlus for writing. Here's an example.

Here's an example of reading with it:

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

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

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

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

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

        var cdm = new ValueSetRepository();

        for (int i = 0; i < result.Tables.Count; i++)
        {
            // CHECK if tableNames filtering is specified
            if (tableNames != null)
            {
                // CHECK if a table matches the specified tablenames
                var tablename = result.Tables[i].TableName;
                if (!tableNames.Contains(tablename))
                {
                    continue;
                }
            }

            var lookup = new ValueSetLookup();
            lookup.CmsId = result.Tables[i].Rows[2][0].ToString();
            lookup.NqfNumber = result.Tables[i].Rows[2][1].ToString();
            lookup.Data = new List<ValueSetAttribute>();

            int row_no = 2;
            while (row_no < result.Tables[i].Rows.Count) // i is the index of table
            // (sheet name) which you want to convert to csv
            {
                var currRow = result.Tables[i].Rows[row_no];
                var valueSetAttribute = new ValueSetAttribute()
                {
                    Id = currRow[0].ToString(),
                    Number = currRow[1].ToString(),
                    tName = currRow[2].ToString(),
                    Code = currRow[7].ToString(),
                    Description = currRow[8].ToString(),
                };

                lookup.Data.Add(valueSetAttribute);
                row_no++;
            }

            cdm.AddRecord(lookup);

Answer 7

A company I used to work for did a lot of research on this and decided a product by SoftArtisans was their best bet: OfficeWriter

I always found it strange how weak the support for Excel reading and writing was. I'm pretty sure that if you use Microsoft's libraries you have to have Excel installed anyway which is an extra expense just like OfficeWriter.

Answer 8

You could either go for VBA or use the free library from FileHelpers. If you are planning to buy some commerical solutions, I would recommend ASPOSE

Answer 9

According to this website you need to include a reference to the Microsoft Excel 12.0 Object library. From there, you need to do a few things to open up the file. There's a code sample on the website.

PS - Sorry it's not too detailed but I couldn't find the Microsoft Office developer reference with more details.

Answer 10

I used ExcelLibrary with very great results! (until now it support Excel 2003 or lower versions).

http://code.google.com/p/excellibrary/

Answer 11

Yes, multiple open-source libraries exist to help read and/or write Excel spreadsheets using C#.

Here is a shortlist of C# libraries:

1. Microsoft.Office.Interop.Excel

2. ExcelDataReader

3. NPOI

4. ExcelMapper - NPOI extension

5. EPPlus

An up-to-date curated list is maintained here.

Example: Reading Excel File using ExcelMapper

a. Install using NuGet, by running below command in NuGet Packet Manager:

Install-Package ExcelMapper

b. Sample C# Code for ExcelMapper

    public void ReadExcelUsingExcelMapperExtension()
    {
        string filePath = @"C:\Temp\ListOfPeople.xlsx";
        var people = new ExcelMapper(filePath).Fetch<Person>().ToList();
    }

    public class Person
    {
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public int Age { get; set; }
    }

Disclaimer: I like the conciseness of ExcelMapper, therefore included sample code for this package. To do the same using other libraries, requires a lot more code.