How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?

3 upvote
  flag
Not sure if this is the right place, but have a look at ClosedXML. It does put the > 2003 limit on your code, but so far we have used it with great success. – Carl
1 upvote
  flag
This can be done with just .NET Framework, like described here, or much easier with some library like GemBox.Spreadsheet. – NixonUposseen

40 Answers 11

You actually might want to check out the interop classes. You say no OLE (which this isn't), but the interop classes are very easy to use.

You might be impressed if you haven't tried them.

Please be warned of Microsoft's stance on this:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

4 upvote
  flag
But you have to make sure that you dispose of everything manually, otherwise you will leak memory – MagicKat
6 upvote
  flag
@Ricky B: Also, in my experience with the interop is that it does use excel. Every time we used it, if Excel wasn't installed on the machine, we would get COM exceptions. – MagicKat
1 upvote
  flag
With the OLE, even with very careful disposals, it eventually leaks memory or crashes. This is argueably OK for attended applications/ workstations, but for servers is not recommended (MS has a KB stating this). For our server, we just reboot it nightly. Again, that works OK. – Jennifer Zouak
9 upvote
  flag
@Geoffrey: ah OK you are going to make me work for it :) --> support.microsoft.com/kb/257757 Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application... – Jennifer Zouak
3 upvote
  flag
I'm coming to this discussion after struggling more than a week on interop, and unless your needs are very simple, this is not gonna work. The support for formatting your spreadsheet is abysmal, which is arguably the reason for generating an .xls file and not just a flat .csv file. For example, have you tried outputting more than 911 characters in a cell, or have you tried setting the width of merged cells in a consistent manner? I have, and I can't tell you how much I hate this crap now... Do yourself a favor and go with one of the free libraries mentioned on this discussion. – md1337
upvote
  flag
I haven't changed Interop for EPPlus yet (but already on the half way) so I don't really know how much better life is with it, but dealing with Interop I got so much pain in very surprising cases nearly every time I needed something more complex than just create .xls/.xlsx file with simple table inside. And mentioned above 'twice-check-to-dispose-everything-magic' is one of that everyday pain. But yeah, it works, and most of the time it's enough. – pkuderov

An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.

I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.

5 upvote
  flag
It's so ad hoc but it works (not to mention excel issuing a warning on opening) and is so simple, it deserves to have a place as a solution. Though only for showing that you can export an excel file :)) – Luka Ramishvili
1 upvote
  flag
This solution worked fine for me, just note you cannot use .xlsx extension – Jill
upvote
  flag
Some people at my organization can't open excel files made this way in Office 2010 and above. Don't know what the problem is, but I had to roll my own OpenXML implementation. (see Sogger's answer) – Kristen Hammack

IKVM + POI

Or, you could use the Interop ...

You can use OLEDB to create and manipulate Excel files. Check this: Reading and Writing Excel using OLEDB.

Typical example:

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp\\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
  conn.Open();
  OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] string, [Column2] string)", conn);
  cmd.ExecuteNonQuery();
}

EDIT - Some more links:

3 upvote
  flag
Can someone confirm if this works when running in x64? I am pretty sure Jet only works if your app is compiled or running in 32-bit mode. – Lamar
1 upvote
  flag
I've just tested this connection and it failed on a Windows Server 2008 R2 x64 RC, seems like one have to install the 2007 Office System Driver: Data Connectivity Components [microsoft.com/downloads/… – Chris Richner
23 upvote
  flag
Be very careful with this -- it's a big ugly cludge (for example, sometimes it guesses a column type and discards all the data that does not fit). – dbkk
7 upvote
  flag
One should be very careful if using this method. I've found it very flaky for data that isn't in a perfect format. – Kenny Mann
5 upvote
  flag
As a person who had to use OleDb in a big project, I say STAY AWAY FROM IT! It sometimes is not able to retrieve a cell value just because it couldn't understand the format. It doesn't have a delete operation. It works totally different and unpredictable even with a slightest provider change. I'd say go for a proven commercial solution. – Caner Öncü
1 upvote
  flag
Microsoft has upgraded Jet, try this link //allinonescript.com/questions/14401729/… – Justin
upvote
  flag
At a previous job, we used Microsoft Access Database Engine 2010 Redistributable. It took the form of an OLEDB driver that allowed reading from and writing to Excel files, as well as Access format files. Note that this download does not require you to install the entire Office suite. Note also that it comes in both 32-bit and 64-bit flavors. It is very important that you match the 32-bit or 64-bit version to the architecture of the host process that will access the file(s). In our case, the host process was SSIS. – Stephen G Tuggy

You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs.

I haven't had need to use it myself yet, but does look interesting.

The Java open source solution is Apache POI. Maybe there is a way to setup interop here, but I don't know enough about Java to answer that.

When I explored this problem I ended up using the Interop assemblies.

You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.

Here's a way to do it with LINQ to XML, complete with sample code:

Quickly Import and Export Excel Data with LINQ to XML

It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.

(Also, of course, it's VB .NET, not C#, but you can always isolate the VB .NET stuff in its own project to use XML Literals, and do everything else in C#.)

I agree about generating XML Spreadsheets, here's an example on how to do it for C# 3 (everyone just blogs about it in VB 9 :P) http://www.aaron-powell.com/linq-to-xml-to-excel

Have you ever tried sylk?

We used to generate excelsheets in classic asp as sylk and right now we're searching for an excelgenerater too.

The advantages for sylk are, you can format the cells.

The various Office 2003 XML libraries avaliable work pretty well for smaller excel files. However, I find the sheer size of a large workbook saved in the XML format to be a problem. For example, a workbook I work with that would be 40MB in the new (and admittedly more tightly packed) XLSX format becomes a 360MB XML file.

As far as my research has taken me, there are two commercial packages that allow output to the older binary file formats. They are:

Neither are cheap (500USD and 800USD respectively, I think). but both work independant of Excel itself.

What I would be curious about is the Excel output module for the likes of OpenOffice.org. I wonder if they can be ported from Java to .Net.

upvote
  flag
This one works on both .net and java,and is not expensive. SmartXLS smartxls.com – liya

The commercial solution, SpreadsheetGear for .NET will do it.

You can see live ASP.NET (C# and VB) samples here and download an evaluation version here.

Disclaimer: I own SpreadsheetGear LLC

3 upvote
  flag
Hmmm...please help me understand why this is voted down. Too much hype? Too long? We hear things like "I can honestly say that of all the components my company has ever used, SpreadsheetGear is the best that I have encountered" from our customers on a regular basis...feedback appreciated! – Joe Erickson
upvote
  flag
Joe, this down vote does indeed looks unfair to me. You have my +1 to balance ;-) – Serge Wautier
8 upvote
  flag
It was likely voted down because it reads like an advertisement. – Gavin Miller
upvote
  flag
This was still receiving down votes 5 months after my answer so I replaced the objectionable long answer with a short answer - my apologies. – Joe Erickson
5 upvote
  flag
You have a great product but I think a lot of people here are expecting free solutions. That might explain the down votes. – md1337
5 upvote
  flag
I think you should have kept in the source code; there's nothing quite like seeing exactly what a tool is like to work with like the source... – sarnold

You can use ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/

It works fine.

I've just recently used FlexCel.NET and found it to be an excellent library! I don't say that about too many software products. No point in giving the whole sales pitch here, you can read all the features on their website.

It is a commercial product, but you get the full source if you buy it. So I suppose you could compile it into your assembly if you really wanted to. Otherwise it's just one extra assembly to xcopy - no configuration or installation or anything like that.

I don't think you'll find any way to do this without third-party libraries as .NET framework, obviously, does not have built in support for it and OLE Automation is just a whole world of pain.

A few options I have used:

If XLSX is a must: ExcelPackage is a good start but died off when the developer quit working on it. ExML picked up from there and added a few features. ExML isn't a bad option, I'm still using it in a couple of production websites.

For all of my new projects, though, I'm using NPOI, the .NET port of Apache POI. NPOI 2.0 (Alpha) also supports XLSX.

upvote
  flag
Best answer yet, I wish I could vote it all the way to the top – Michael La Voie
upvote
  flag
Be careful with ExcelPackage if you need to support XLS. I had a hard time with it and eventually switched to ExcelLibrary. – Jeremy
upvote
  flag
Definitely true. ExcelPackage/ExML is only a good option if you need the XLSX support. – Nate
2 upvote
  flag
Note that ExcelPackage has a successor: EPPlus (epplus.codeplex.com) which supports XLSX. My only concern, compared to NPOI for example, is performance, e.g. when there is a lot of columns. – Pragmateek

I've used with success the following open source projects:

  • ExcelPackage for OOXML formats (Office 2007)

  • NPOI for .XLS format (Office 2003). NPOI 2.0 (Alpha) also supports XLSX.

Take a look at my blog posts:

Creating Excel spreadsheets .XLS and .XLSX in C#

NPOI with Excel Table and dynamic Chart

5 upvote
  flag
A note on NPOI - Row and Column references are zero-based. Does work well for populating an existing template. – John M
3 upvote
  flag
NPOI 2 is now in Beta, and I have used it in my projects for simple exports without problems. – Travis
2 upvote
  flag
It works. It also has some of the most atrocious class and namespace names ever invented - mimicking the underlying data format atrociousness instead of mocking it. – Roman Starkov
8 upvote
  flag
This question and its answers are really old. NPOI is now the way to go, and I wish this answer was at the top before I wasted my time with EPPlus and ExcelLibrary trying to get a solution that worked for both .xls and .xlsx Get the C# downloads from npoi.codeplex.com. And the best documentation I've found, even though it is the Java version is at poi.apache.org – SurfingSanta
2 upvote
  flag
NPOI has moved from Codeplex to Github. – jerhewet

I also vote for GemBox.Spreadsheet.

Very fast and easy to use, with tons of examples on their site.

Took my reporting tasks on a whole new level of execution speed.

Some useful Excel automation in C# , u can find from the following link.

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

bolton.

Well,

you can also use a third party library like Aspose.

This library has the benefit that it does not require Excel to be installed on your machine which would be ideal in your case.

upvote
  flag
To be more precise, you can use Aspose.Cells for .NET in order to create Excel (XLS, XLSX) files in your .NET application. – Shahzad Latif
6 upvote
  flag
Yes you can, if you don't mind paying a minimum license fee of $999. Try the MikesKnowledgeBase library... which is $999 cheaper than this !! – Mike Gledhill

If you are happy with the xlsx format, try my codeplex GitHub project. EPPlus. Started it with the source from ExcelPackage, but today it's a total rewrite. Supports ranges, cell styling, charts, shapes, pictures, namesranges, autofilter and a lot of other stuff.

upvote
  flag
@Jan - You need to market this more. Get the word out to popular .NET blogs. Your project didn't come up in Google searches for me. You need the Google SEO juice! – Bill Paetzke
5 upvote
  flag
Yes, I guess i should. My "marketing" so far is a few posts here. I'll try to do something after I have released the new version in a few weeks (which will include encryption and table support and a few other things). Feel free to help me out if you want to :) – Jan Källman
upvote
  flag
Hi people, EPPlus is great in working at cell level and even works on server. (normal Excel PIA COM call raises access denied exception on server). But can i write HTML to worksheet somehow ? need to write multiple gridviews (1 per sheet) ! – Munish Goyal
33 upvote
  flag
One drawback of EPPlus is its licensing (currently GPL). Because of this, we cannot use this project. – dana
upvote
  flag
@dana - Why is the GPL license unusable in your case? – Dann
upvote
  flag
Ah, never mind. I just found this to explain it all programmers.stackexchange.com/questions/47032/… – Dann
5 upvote
  flag
My completely-free library also lets you export any DataSet, DataTable or List<> directly into an Excel 2007 .xlsx file, using Open XML. Full source code, and demo, available here: mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm – Mike Gledhill
64 upvote
  flag
License is now LGPL, release notes here: epplus.codeplex.com/releases/view/79802 – Simon D
9 upvote
  flag
The examples were helpful. I was able to change my code from using Microsoft interop library (horribly slow) to this library (version 4.x) in a couple hours. My benchmark writes a file with two tabs and about 750,000 cells. Using MS interop it took 13 minutes. Using EPPlus it took 10 seconds, a roughly 80x speedup. Very happy! – Paul Chernoch
upvote
  flag
@JanKällman You should update your CodePlex page to show you've got these methods available: LoadFromCollection<T>, LoadFromDataTable etc. (found via here) – PeterX
1 upvote
  flag
For clarity in this thread, the LGPL allows the software to be linked to without the infective part of the GPL occuring. You only need to open source changes you make to ClosedXml or if you directly put the source code (as opposed to referencing the ClosedXml assemblies) inside of your application then you need to open source your application. – Chris Marisic
upvote
  flag
Is this only available on codeplex (shutting down soon)? – Joe Phillips
upvote
  flag
For anyone else who stumbles across this, EPPlus is available on Github: github.com/JanKallman/EPPlus – tnk479
up vote 808 down vote accepted

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 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 Lesser General Public License (LGPL)

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.

1 upvote
  flag
+1. Agreed with the above. Tried fooling around with all of the COM bs, installing excel on app server. Too much work in the end. Found this and now problem solved! – Stevus
188 upvote
  flag
ExcelLibrary has been superseded by the exceptional EPPlus - epplus.codeplex.com. Jan updates it regularly. Have been using it and it is one of the finest open source projects we've worked with. – Mark A
17 upvote
  flag
No. Use EppPlus - epplus.codeplex.com. It's the latest greatest. Supports formatting. I use it. – Bill Paetzke
2 upvote
  flag
EPPlus is awesome! Don't need to install excel and works so easily – JumpingJezza
42 upvote
  flag
It seems that ExcelLibrary only supports .xls and EPPlus only .xlsx so they complement each other. – jmster
3 upvote
  flag
I tried using ExcelLibrary, unfortunately if the generated files are below a certain side they can't be opened in Windows 7. This seems to be a known bug, with no fix as yet: code.google.com/p/excellibrary/issues/detail?id=54 – Luke Girvin
1 upvote
  flag
Tried to create xls file with ExcelLibrary, but it doesn't work - can't open it with Excel 2010 plus :( – Dzmitry
1 upvote
  flag
It should be noted that ExcelLibrary has a lot of performance issues when dealing with large datasets(larger than 5000 rows with lots of columns). Currently doing a heavy modification of the code base at work so we can use it in a project. – rossisdead
upvote
  flag
EPPlus seems far less buggy than ExcelLibrary, BUT it is GPL and therefore only a solution for open source projects. – Seth
upvote
  flag
Does any of them support PivotTable creation ? – Cannon
1 upvote
  flag
@АртёмЦарионов: I'm no better placed than anyone else do do so, but according to the homepage epplus.codeplex.com, EPPlus does support pivot tables, and according to this issue (code.google.com/p/excellibrary/issues/…) ExcelLibrary also has at least some level of support for pivot tables. hth. – Tao
upvote
  flag
For reason unknown to me EPPlus does not work for me. But this Excel Library works just fine. I wonder is there a way to make the header row bold in excel? – Arbaaz
4 upvote
  flag
upvote
  flag
Open a blank file for me... can it be because I have ms office 2010? – ParPar
upvote
  flag
EPPlus is actually still technically GPL because it is a derived work (i.e. the code base is still based off of ExcelLibrary, and since that is GPL so is EPPlus ... you can't make some changes to GPL code and slap a LGPL license on it). Does anyone know of a way to write to Excel using either commercial code or true LGPL (or similarly licensed code). – Beep beep
7 upvote
  flag
Please stop putting forward EPPlus as an alternative to ExcelLibrary until it is capable of the same functionality (one handles XLS, the other XLSX). As much as I like EPPlus, it simply is not an answer to the OP's needs (XLS). – Chris Rogers
1 upvote
  flag
This question and its answers are really old. NPOI is now the way to go, and I wish I'd realised that before I wasted my time with EPPlus and ExcelLibrary trying to get a solution that worked for both .xls and .xlsx Get the C# downloads from npoi.codeplex.com. And the best documentation I've found, even though it is the Java version is at poi.apache.org – SurfingSanta
upvote
  flag
ExcelLibrary doesn't work anymore. If you want to write Excel 2003 files (.xls) this library is working great: CSharpJExcel sourceforge.net/projects/jexcelapi Make sure to download the C# port. – Chris
2 upvote
  flag
What about ClosedXML? I may prove to be useful in your projects. – Amadeus Sánchez
upvote
  flag
1 upvote
  flag
NPOI has moved from Codeplex to Github. – jerhewet
upvote
  flag
@ChrisRogers Has the OP actually specified that? – wizzwizz4
upvote
  flag
@wizzwizz4 - Yes the OP did ask for both XLS and XLSX formats (in the title). That comment (2 years ago) was directed at the many comments which put EPPlus forward as superseding ExcelLibrary - which it clearly doesn't until it can handle both formats. – Chris Rogers

you can just write it out to XML using the Excel XML format and name it .XLS and it will open with excel. You can control all the formatting (bold, widths, etc) in your XML file heading

http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

2 upvote
  flag
This is cool except it doesn't support charts or images. – Francois Botha

Look for ExtremeML. It's a pretty cool library which enables you to use the OpenXML format for generating OpenXML files.

It's also an OpenSource project.

http://www.extrememl.com/

If you're creating Excel 2007/2010 files give this open source project a try: http://closedxml.codeplex.com

It provides an object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).

ClosedXML allows you to create Excel 2007/2010 files without the Excel application. The typical example is creating Excel reports on a web server:

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("HelloWorld.xlsx");
8 upvote
  flag
I tried using this in a project that builds pretty large Excel sheets. Excellent library, but extremely poor in performance. I just did a comparison for the project I'm working on: ClosedXML (v 0.53.3) took 92,489 ms whereas EPPlus (v 2.9.03, for testing - we can't use because it's GPL) took 16,500 ms. – Druid
1 upvote
  flag
@Druid the license is LGPL assuming you don't modify the source code to ClosedXML it is free to use epplus.codeplex.com/license – Chris Marisic

You can create nicely formatted Excel files using this library: http://officehelper.codeplex.com/documentation
See below sample:

using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
    helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
    helper.CurrentSheetName = "Sheet1";
    helper.CurrentPosition = new CellRef("C3");

    //the template xlsx should contains the named range "header"; use the command "insert"/"name".
    helper.InsertRange("header");

    //the template xlsx should contains the named range "sample1";
    //inside this range you should have cells with these values:
    //<name> , <value> and <comment>, which will be replaced by the values from the getSample()
    CellRangeTemplate sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> {"name", "value", "comment"}); 
    helper.InsertRange(sample1, getSample());

    //you could use here other named ranges to insert new cells and call InsertRange as many times you want, 
    //it will be copied one after another;
    //even you can change direction or the current cell/sheet before you insert

    //typically you put all your "template ranges" (the names) on the same sheet and then you just delete it
    helper.DeleteSheet("Sheet3");
}        

where sample look like this:

private IEnumerable<List<object>> getSample()
{
    var random = new Random();

    for (int loop = 0; loop < 3000; loop++)
    {
        yield return new List<object> {"test", DateTime.Now.AddDays(random.NextDouble()*100 - 50), loop};
    }
}

http://www.codeproject.com/KB/cs/Excel_and_C_.aspx <= why not just use the built in power of windows, just install office on the server, any application that you install can be automated.

So much easier just use the native methods.

If it installed you can use it, this is the most awesome and under used feature in windows it was Dubbed COM back in the good old days, and it saves you tons of time and pain.

Or even easier just use the ref lib MS supplies - http://csharp.net-informations.com/excel/csharp-create-excel.htm

upvote
  flag
Why is the second way easier? Isn't it the same (adding the native object library to your project)? Do you need to have Excel installed to get this object library working? – Slauma
5 upvote
  flag
Microsoft does not recommend or support Office Automation from non-interactive applications such as ASP.NET. See support.microsoft.com/kb/257757 – TrueWill
upvote
  flag
Excel is quite unreliable & slow when it comes to Automation. – Leslie Godwin

Some 3rd party component vendors like Infragistics or Syncfusion provide very good Excel export capabilities that do not require Microsoft Excel to be installed.

Since these vendors also provide advanced UI grid components, these components are particularly handy if you want the style and layout of an excel export to mimic the current state of a grid in the user interface of your application.

If your export is intended to be executed server side with emphasis on the data to be exported and with no link to the UI, then I would go for one of the free open source options (e.g. ExcelLibrary).

I have previously been involved with projects that attempted to use server side automation on the Microsoft Office suite. Based on this experience I would strongly recommend against that approach.

Look at samples how to create Excel files.

There are examples in C# and VB.NET

It manages XSL XSLX and CSV Excel files.

http://www.devtriogroup.com/ExcelJetcell/Samples

public class GridViewExportUtil
{
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }
}

Hi this solution is to export your grid view to your excel file it might help you out

5 upvote
  flag
No, this generates HTML marked as an Excel file rather than a true Excel file. Yes, Excel itself will open that OK but other programs that consume spreadsheets - including Microsoft's free Excel viewer, for example - won't accept it. You'd do better to create a real Excel file using one of the libraries here. – Rup
upvote
  flag
You should also use System.Net.Mime.ContentDisposition to generate the content-disposition header text rather than a string append - that'll cope with filenames that contains spaces etc. correctly. – Rup

And what about using Open XML SDK 2.0 for Microsoft Office?

A few benefits:

  • Doesn't require Office installed
  • Made by Microsoft = decent MSDN documentation
  • Just one .Net dll to use in project
  • SDK comes with many tools like diff, validator, etc

Links:

2 upvote
  flag
Important to note that the DLL for this is just over 5 MB and limited to Office 2007 formats. But certainly the easiest and fastest solution which works for me. – Josh Brown
11 upvote
  flag
Just a heads up that v2.5 is out and can be downloaded here. – Snuffleupagus
8 upvote
  flag
The SDK models the XML into classes, so that each XML tag is mapped to a tag, and then you have to build the class hierarchy (each instance has a collection of child instances/tags) correctly. This means you have to know the XML structure of an Excel file, which is very complicated. It's much easier to use a wrapper such as EPPlus, mentioned above, which simplifies things. – Tsahi Asher
1 upvote
  flag
A great sample of Microsoft Open XML SDK - Open XML Writer can be found at polymathprogrammer.com/2012/08/06/… Or see Stack Overflow solution //allinonescript.com/questions/11370672/… – Greg
3 upvote
  flag
I found Microsoft Open XML SDK's Open XML Writer to be great. Using the solutions above, (Especially Vincent Tom's sample (Poly Math)), it's easy to build a writer that streams through big sets of data, and writes records in a manner similiar and not too much more complex to what you'd do for CSV; but that you're instead writing xml. Open XML is the mindset that Microsoft considers it's new Office formats in. And you can always rename them from .xslx to .zip files if you feel like poking at their XML contents. – Greg

Just want to add another reference to a third party solution that directly addresses your issue: http://www.officewriter.com

(Disclaimer: I work for SoftArtisans, the company that makes OfficeWriter)

Here's a completely free C# library, which lets you export from a DataSet, DataTable or List<> into a genuine Excel 2007 .xlsx file, using the OpenXML libraries:

http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Full source code is provided - free of charge - along with instructions, and a demo application.

After adding this class to your application, you can export your DataSet to Excel in just one line of code:

CreateExcelFile.CreateExcelDocument(myDataSet, "C:\\Sample.xlsx");

It doesn't get much simpler than that...

And it doesn't even require Excel to be present on your server.

upvote
  flag
This seems a bit misleading, as you are asking for a donation to get all of the features. – UrbanEsc
upvote
  flag
That's partly true: The completely free version will generate a perfect .xlsx file for you, and all source code is provided. If you donate $10 or more to one of those two charities (of which I receive absolutely nothing), then you get a "better" version showing how to do formatting, dates, etc. Given the cost of third-party products, I reckon donating $10 to a good cause instead is well worth it ! – Mike Gledhill

I am using following code for create excel 2007 file which create the file and write in that file but when i open the file but it give me error that exel cannot open the file bcz file might be coruupted or extension of the file is not compatible. but if i used .xls for file it work fines

for (int i = 0; i < TotalFile; i++)
{
    Contact.Clear();
    if (innerloop == SplitSize)
    {
        for (int j = 0; j < SplitSize; j++)
        {
            string strContact = DSt.Tables[0].Rows[i * SplitSize + j][0].ToString();
            Contact.Add(strContact);
        }
        string strExcel = strFileName + "_" + i.ToString() + ".xlsx";
                         File.WriteAllLines(strExcel, Contact.ToArray());
    }
}

also refer link

http://dotnet-magic.blogspot.in/2011/10/createformat-excel-file-from-cnet.html

1 upvote
  flag
That all relies on your Contact class, and you haven't told us what that is. If it works for xls then chances are you're actually writing out HTML which isn't a real Excel file. And your link is using interop, which as mentioned above shouldn't be used server-side and can be slow filling large tables. – Rup
1 upvote
  flag
Contact is linkedlist not a class.declare a linkledist and used it because i haven't know the size of data so i used linkedlist. – saurabh27
1 upvote
  flag
Oh, so you're producing a plain text file with one item per line? So Excel is treating it as a CSV without the commas? – Rup

I have written a simple code to export dataset to excel without using excel object by using System.IO.StreamWriter.

Below is the code which will read all tables from dataset and write them to sheets one by one. I took help from http://www.codeproject.com/Articles/9380/Export-a-DataSet-to-Microsoft-Excel-without-the-us article.

public static void exportToExcel(DataSet source, string fileName)
{
        const string endExcelXML = "</Workbook>";
        const string startExcelXML = "<xml version>\r\n<Workbook " +
                 "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
                 " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
                 "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
                 "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
                 "office:spreadsheet\">\r\n <Styles>\r\n " +
                 "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
                 "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
                 "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
                 "\r\n <Protection/>\r\n </Style>\r\n " +
                 "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
                 "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
                 "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
                 " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
                 "ss:ID=\"Decimal\">\r\n <NumberFormat " +
                 "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
                 "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
                 "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
                 "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
                 "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
                 "</Styles>\r\n ";
        System.IO.StreamWriter excelDoc = null;
        excelDoc = new System.IO.StreamWriter(fileName);

        int sheetCount = 1;
        excelDoc.Write(startExcelXML);
        foreach (DataTable table in source.Tables)
        {
            int rowCount = 0;
            excelDoc.Write("<Worksheet ss:Name=\"" + table.TableName + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int x = 0; x < table.Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(table.Columns[x].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            foreach (DataRow x in table.Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"" + table.TableName + "\">");
                    excelDoc.Write("<Table>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y < table.Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
            sheetCount++;
        }


        excelDoc.Write(endExcelXML);
        excelDoc.Close();
    }
upvote
  flag
Like the article says though, that's XML that Excel will read rather than actually being an XLS file, which means that it might only work in Excel and not other programs that read spreadsheets. But it's probably better than the equivalent HTML table answers here! – Rup
upvote
  flag
Supports xlsx ? OpenXML ? – Kiquenet

OpenXML is also a good alternative that helps avoid installing MS Excel on Server.The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.

Check this out OpenXML: Alternative that helps avoid installing MS Excel on Server

Syncfusion Essential XlsIO can do this. It has no dependency on Microsoft office and also has specific support for different platforms.

Code sample:

//Creates a new instance for ExcelEngine.
ExcelEngine excelEngine = new ExcelEngine();
//Loads or open an existing workbook through Open method of IWorkbooks
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileName);
//To-Do some manipulation|
//To-Do some manipulation
//Set the version of the workbook.
workbook.Version = ExcelVersion.Excel2013;
//Save the workbook in file system as xlsx format
workbook.SaveAs(outputFileName);

The whole suite of controls is available for free through the community license program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.

The simplest and fastest way to create an Excel file from C# is to use the Open XML Productivity Tool. The Open XML Productivity Tool comes with the Open XML SDK installation. The tool reverse engineers any Excel file into C# code. The C# code can then be used to re-generate that file.

An overview of the process involved is:

  1. Install the Open XML SDK with the tool.
  2. Create an Excel file using the latest Excel client with desired look. Name it DesiredLook.xlsx.
  3. With the tool open DesiredLook.xlsx and click the Reflect Code button near the top. enter image description here
  4. The C# code for your file will be generated in the right pane of the tool. Add this to your C# solution and generate files with that desired look.

As a bonus, this method works for any Word and PowerPoint files. As the C# developer, you will then make changes to the code to fit your needs.

I have developed a simple WPF app on github which will run on Windows for this purpose. There is a placeholder class called GeneratedClass where you can paste the generated code. If you go back one version of the file, it will generate an excel file like this:

enter image description here

If you make data table or datagridview from the code you can save all data using this simple method.this method not recomended but its working 100%, even you are not install MS Excel in your computer.

try
 {
  SaveFileDialog saveFileDialog1 = new SaveFileDialog();
  saveFileDialog1.Filter = "Excel Documents (*.xls)|*.xls";
  saveFileDialog1.FileName = "Employee Details.xls";
  if (saveFileDialog1.ShowDialog() == DialogResult.OK)
  {
  string fname = saveFileDialog1.FileName;
  StreamWriter wr = new StreamWriter(fname);
  for (int i = 0; i <DataTable.Columns.Count; i++)
  {
  wr.Write(DataTable.Columns[i].ToString().ToUpper() + "\t");
  }
  wr.WriteLine();

  //write rows to excel file
  for (int i = 0; i < (DataTable.Rows.Count); i++)
  {
  for (int j = 0; j < DataTable.Columns.Count; j++)
  {
  if (DataTable.Rows[i][j] != null)
  {
  wr.Write(Convert.ToString(getallData.Rows[i][j]) + "\t");
  }
   else
   {
   wr.Write("\t");
   }
   }
   //go to next line
   wr.WriteLine();
   }
   //close file
   wr.Close();
   }
   }
   catch (Exception)
   {
    MessageBox.Show("Error Create Excel Sheet!");
   }

Some time ago, I created a DLL on top of NPOI. It's very simple to use it:

IList<DummyPerson> dummyPeople = new List<DummyPerson>();
//Add data to dummyPeople...
IExportEngine engine = new ExcelExportEngine();
engine.AddData(dummyPeople); 
MemoryStream memory = engine.Export();

You could read more about it on here.

By the way, is 100% open source. Feel free to use, edit and share ;)

One really easy option which is often overlooked is to create a .rdlc report using Microsoft Reporting and export it to excel format. You can design it in visual studio and generate the file using:

localReport.Render("EXCELOPENXML", null, ((name, ext, encoding, mimeType, willSeek) => stream = new FileStream(name, FileMode.CreateNew)), out warnings);

You can also export it do .doc or .pdf, using "WORDOPENXML" and "PDF" respectively, and it's supported on many different platforms such as ASP.NET and SSRS.

It's much easier to make changes in a visual designer where you can see the results, and trust me, once you start grouping data, formatting group headers, adding new sections, you don't want to mess with dozens of XML nodes.

Not the answer you're looking for? Browse other questions tagged or ask your own question.