C# – Read password protected excel file using OLEDB in C#

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

SpreadsheetGear for .NET gives you an API for working with xls and xlsx workbooks from .NET. It is easier to use and faster than OleDB or the Excel COM object model. You can see the live samples or try it for yourself with the free trial.

Disclaimer: I own SpreadsheetGear LLC

EDIT:

StingyJack commented "Faster than OleDb? Better back that claim up".

This is a reasonable request. I see claims all the time which I know for a fact to be false, so I cannot blame anyone for being skeptical.

Below is the code to create a 50,000 row by 10 column workbook with SpreadsheetGear, save it to disk, and then sum the numbers using OleDb and SpreadsheetGear. SpreadsheetGear reads the 500K cells in 0.31 seconds compared to 0.63 seconds with OleDB - just over twice as fast. SpreadsheetGear actually creates and reads the workbook in less time than it takes to read the workbook with OleDB.

The code is below. You can try it yourself with the SpreadsheetGear free trial.

using System;
using System.Data; 
using System.Data.OleDb; 
using SpreadsheetGear;
using SpreadsheetGear.Advanced.Cells;
using System.Diagnostics;

namespace SpreadsheetGearAndOleDBBenchmark
{
    class Program
    {
        static void Main(string[] args)
        {
            // Warm up (get the code JITed).
            BM(10, 10);

            // Do it for real.
            BM(50000, 10);
        }

        static void BM(int rows, int cols)
        {
            // Compare the performance of OleDB to SpreadsheetGear for reading
            // workbooks. We sum numbers just to have something to do.
            //
            // Run on Windows Vista 32 bit, Visual Studio 2008, Release Build,
            // Run Without Debugger:
            //  Create time: 0.25 seconds
            //  OleDb Time: 0.63 seconds
            //  SpreadsheetGear Time: 0.31 seconds
            //
            // SpreadsheetGear is more than twice as fast at reading. Furthermore,
            // SpreadsheetGear can create the file and read it faster than OleDB
            // can just read it.
            string filename = @"C:\tmp\SpreadsheetGearOleDbBenchmark.xls";
            Console.WriteLine("\nCreating {0} rows x {1} columns", rows, cols);
            Stopwatch timer = Stopwatch.StartNew();
            double createSum = CreateWorkbook(filename, rows, cols);
            double createTime = timer.Elapsed.TotalSeconds;
            Console.WriteLine("Create sum of {0} took {1} seconds.", createSum, createTime);
            timer = Stopwatch.StartNew();
            double oleDbSum = ReadWithOleDB(filename);
            double oleDbTime = timer.Elapsed.TotalSeconds;
            Console.WriteLine("OleDb sum of {0} took {1} seconds.", oleDbSum, oleDbTime);
            timer = Stopwatch.StartNew();
            double spreadsheetGearSum = ReadWithSpreadsheetGear(filename);
            double spreadsheetGearTime = timer.Elapsed.TotalSeconds;
            Console.WriteLine("SpreadsheetGear sum of {0} took {1} seconds.", spreadsheetGearSum, spreadsheetGearTime);
        }

        static double CreateWorkbook(string filename, int rows, int cols)
        {
            IWorkbook workbook = Factory.GetWorkbook();
            IWorksheet worksheet = workbook.Worksheets[0];
            IValues values = (IValues)worksheet;
            double sum = 0.0;
            Random rand = new Random();
            // Put labels in the first row.
            foreach (IRange cell in worksheet.Cells[0, 0, 0, cols - 1])
                cell.Value = "Cell-" + cell.Address;
            // Using IRange and foreach be less code, 
            // but we'll do it the fast way.
            for (int row = 1; row <= rows; row++)
            {
                for (int col = 0; col < cols; col++)
                {
                    double number = rand.NextDouble();
                    sum += number;
                    values.SetNumber(row, col, number);
                }
            }
            workbook.SaveAs(filename, FileFormat.Excel8);
            return sum;
        }

        static double ReadWithSpreadsheetGear(string filename)
        {
            IWorkbook workbook = Factory.GetWorkbook(filename);
            IWorksheet worksheet = workbook.Worksheets[0];
            IValues values = (IValues)worksheet;
            IRange usedRahge = worksheet.UsedRange;
            int rowCount = usedRahge.RowCount;
            int colCount = usedRahge.ColumnCount;
            double sum = 0.0;
            // We could use foreach (IRange cell in usedRange) for cleaner 
            // code, but this is faster.
            for (int row = 1; row <= rowCount; row++)
            {
                for (int col = 0; col < colCount; col++)
                {
                    IValue value = values[row, col];
                    if (value != null && value.Type == SpreadsheetGear.Advanced.Cells.ValueType.Number)
                        sum += value.Number;
                }
            }
            return sum;
        }

        static double ReadWithOleDB(string filename)
        {
            String connectionString =  
                "Provider=Microsoft.Jet.OLEDB.4.0;" + 
                "Data Source=" + filename + ";" + 
                "Extended Properties=Excel 8.0;"; 
            OleDbConnection connection = new OleDbConnection(connectionString); 
            connection.Open(); 
            OleDbCommand selectCommand =new OleDbCommand("SELECT * FROM [Sheet1$]", connection); 
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); 
            dataAdapter.SelectCommand = selectCommand; 
            DataSet dataSet = new DataSet(); 
            dataAdapter.Fill(dataSet); 
            connection.Close(); 
            double sum = 0.0;
            // We'll make some assumptions for brevity of the code.
            DataTable dataTable = dataSet.Tables[0];
            int cols = dataTable.Columns.Count;
            foreach (DataRow row in dataTable.Rows)
            {
                for (int i = 0; i < cols; i++)
                {
                    object val = row[i];
                    if (val is double)
                        sum += (double)val;
                }
            }
            return sum;
        }
    }
}