C# – How to create excel sheet data directly from the .net application

Here is a very good article regarding the Mutex solution. The approach described by the article is advantageous for two reasons.

First, it does not require a dependency on the Microsoft.VisualBasic assembly. If my project already had a dependency on that assembly, I would probably advocate using the approach shown in another answer. But as it is, I do not use the Microsoft.VisualBasic assembly, and I'd rather not add an unnecessary dependency to my project.

Second, the article shows how to bring the existing instance of the application to the foreground when the user tries to start another instance. That's a very nice touch that the other Mutex solutions described here do not address.

UPDATE

As of 8/1/2014, the article I linked to above is still active, but the blog hasn't been updated in a while. That makes me worry that eventually it might disappear, and with it, the advocated solution. I'm reproducing the content of the article here for posterity. The words belong solely to the blog owner at Sanity Free Coding.

Today I wanted to refactor some code that prohibited my application from running multiple instances of itself.

Previously I had use System.Diagnostics.Process to search for an instance of my myapp.exe in the process list. While this works, it brings on a lot of overhead, and I wanted something cleaner.

Knowing that I could use a mutex for this (but never having done it before) I set out to cut down my code and simplify my life.

In the class of my application main I created a static named Mutex:

static class Program
{
    static Mutex mutex = new Mutex(true, "{8F6F0AC4-B9A1-45fd-A8CF-72F04E6BDE8F}");
    [STAThread]
    ...
}

Having a named mutex allows us to stack synchronization across multiple threads and processes which is just the magic I'm looking for.

Mutex.WaitOne has an overload that specifies an amount of time for us to wait. Since we're not actually wanting to synchronizing our code (more just check if it is currently in use) we use the overload with two parameters: Mutex.WaitOne(Timespan timeout, bool exitContext). Wait one returns true if it is able to enter, and false if it wasn't. In this case, we don't want to wait at all; If our mutex is being used, skip it, and move on, so we pass in TimeSpan.Zero (wait 0 milliseconds), and set the exitContext to true so we can exit the synchronization context before we try to aquire a lock on it. Using this, we wrap our Application.Run code inside something like this:

static class Program
{
    static Mutex mutex = new Mutex(true, "{8F6F0AC4-B9A1-45fd-A8CF-72F04E6BDE8F}");
    [STAThread]
    static void Main() {
        if(mutex.WaitOne(TimeSpan.Zero, true)) {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
            mutex.ReleaseMutex();
        } else {
            MessageBox.Show("only one instance at a time");
        }
    }
}

So, if our app is running, WaitOne will return false, and we'll get a message box.

Instead of showing a message box, I opted to utilize a little Win32 to notify my running instance that someone forgot that it was already running (by bringing itself to the top of all the other windows). To achieve this I used PostMessage to broadcast a custom message to every window (the custom message was registered with RegisterWindowMessage by my running application, which means only my application knows what it is) then my second instance exits. The running application instance would receive that notification and process it. In order to do that, I overrode WndProc in my main form and listened for my custom notification. When I received that notification I set the form's TopMost property to true to bring it up on top.

Here is what I ended up with:

• Program.cs

static class Program
{
    static Mutex mutex = new Mutex(true, "{8F6F0AC4-B9A1-45fd-A8CF-72F04E6BDE8F}");
    [STAThread]
    static void Main() {
        if(mutex.WaitOne(TimeSpan.Zero, true)) {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
            mutex.ReleaseMutex();
        } else {
            // send our Win32 message to make the currently running instance
            // jump on top of all the other windows
            NativeMethods.PostMessage(
                (IntPtr)NativeMethods.HWND_BROADCAST,
                NativeMethods.WM_SHOWME,
                IntPtr.Zero,
                IntPtr.Zero);
        }
    }
}

• NativeMethods.cs

// this class just wraps some Win32 stuff that we're going to use
internal class NativeMethods
{
    public const int HWND_BROADCAST = 0xffff;
    public static readonly int WM_SHOWME = RegisterWindowMessage("WM_SHOWME");
    [DllImport("user32")]
    public static extern bool PostMessage(IntPtr hwnd, int msg, IntPtr wparam, IntPtr lparam);
    [DllImport("user32")]
    public static extern int RegisterWindowMessage(string message);
}

• Form1.cs (front side partial)

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
    protected override void WndProc(ref Message m)
    {
        if(m.Msg == NativeMethods.WM_SHOWME) {
            ShowMe();
        }
        base.WndProc(ref m);
    }
    private void ShowMe()
    {
        if(WindowState == FormWindowState.Minimized) {
            WindowState = FormWindowState.Normal;
        }
        // get our current "TopMost" value (ours will always be false though)
        bool top = TopMost;
        // make our form jump to the top of everything
        TopMost = true;
        // set it back to whatever it was
        TopMost = top;
    }
}

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.