share
Stack OverflowHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?
[+1643] [41] mistrmark
[2008-09-29 22:30:28]
[ c# .net excel file-io ]
[ https://stackoverflow.com/questions/151005/how-to-create-excel-xls-and-xlsx-file-in-c-without-installing-ms-office ]

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

(3) 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
(3) This can be done with just .NET Framework, like described here, or much easier with some library like GemBox.Spreadsheet. - NixonUposseen
(1) Detailed blog with explanation: sforsuresh.in/… - Suresh Kamrushi
[+914] [2010-04-08 21:36:03] Mike Webb [ACCEPTED]

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

ExcelLibrary [1]

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 [2], 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 [3])

Here are a couple links for quick reference:
ExcelLibrary [4] - GNU Lesser GPL [5]
EPPlus [6] - GNU Lesser General Public License (LGPL) [7]

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] https://code.google.com/archive/p/excellibrary/
[2] https://github.com/JanKallman/EPPlus
[3] https://code.google.com/archive/p/excellibrary/issues/98
[4] https://code.google.com/archive/p/excellibrary/
[5] https://www.gnu.org/licenses/lgpl.html
[6] https://github.com/JanKallman/EPPlus
[7] https://github.com/JanKallman/EPPlus/blob/master/LICENSE

(1) +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! - Steven
(212) 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
(19) No. Use EppPlus - epplus.codeplex.com. It's the latest greatest. Supports formatting. I use it. - Bill Paetzke
(2) EPPlus is awesome! Don't need to install excel and works so easily - JumpingJezza
(50) It seems that ExcelLibrary only supports .xls and EPPlus only .xlsx so they complement each other. - jmster
(3) 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) Tried to create xls file with ExcelLibrary, but it doesn't work - can't open it with Excel 2010 plus :( - Dzmitry
(1) 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
EPPlus seems far less buggy than ExcelLibrary, BUT it is GPL and therefore only a solution for open source projects. - Seth
Does any of them support PivotTable creation ? - Cannon
(1) @АртёмЦарионов: 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
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
Open a blank file for me... can it be because I have ms office 2010? - ParPar
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
(10) 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) 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
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
(3) What about ClosedXML? I may prove to be useful in your projects. - Amadeus Sánchez
(1) NPOI has moved from Codeplex to Github. - jerhewet
@ChrisRogers Has the OP actually specified that? - wizzwizz4
@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
1
[+514] [2010-03-29 12:25:54] Jan Källman

If you are happy with the xlsx format, try my codeplex GitHub project. EPPlus [1]. 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.

[1] https://github.com/JanKallman/EPPlus

(8) 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
(67) License is now LGPL, release notes here: epplus.codeplex.com/releases/view/79802 - Simon D
(9) 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
@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) 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
(1) @Paul Chernoch: We populate large Excel sheets with interop very quickly. The secret is to do a bulk update. Create a object [,] block, populate that, then write that matrix to Excel at one time: excelWorksheet.get_Range(range).Value2 = block; - Marc Meketon
2
[+155] [2009-06-20 20:48:26] Leniel Maccaferri

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 [1] (Alpha) also supports XLSX.

Take a look at my blog posts:

Creating Excel spreadsheets .XLS and .XLSX in C# [2]

NPOI with Excel Table and dynamic Chart [3]

[1] https://github.com/tonyqus/npoi
[2] https://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html
[3] https://www.leniel.net/2009/10/npoi-with-excel-table-and-dynamic-chart.html

(5) A note on NPOI - Row and Column references are zero-based. Does work well for populating an existing template. - John M
(3) NPOI 2 is now in Beta, and I have used it in my projects for simple exports without problems. - Travis
(2) 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) 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
(4) NPOI has moved from Codeplex to Github. - jerhewet
3
[+150] [2011-08-16 09:25:58] Pellared

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:

[1] https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=5124
[2] https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
[3] https://docs.microsoft.com/en-us/office/open-xml/how-do-i
[4] https://blogs.msdn.microsoft.com/brian_jones/2010/03/12/announcing-the-release-of-the-open-xml-sdk-2-0/
[5] https://blogs.msdn.microsoft.com/brian_jones/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk/

(2) 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) Just a heads up that v2.5 is out and can be downloaded here. - Snuffleupagus
(8) 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) 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 stackoverflow.com/questions/11370672/… - Greg
(3) 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
Please note that Open XML SDK are now open source and hosted on github. Also you don't need to install the SDK, just fire-up nuget and DocumentFormat.OpenXml is all you need. It works with .net standard 1.3. - horeaper
4
[+98] [2008-09-29 22:41:09] Panos

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

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:

[1] https://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB
[2] http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept08/hey0911.mspx
[3] http://support.microsoft.com/kb/316934
[4] http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

(3) 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) 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) 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) 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
(6) 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) Microsoft has upgraded Jet, try this link stackoverflow.com/questions/14401729/… - Justin
(1) 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
5
[+75] [2009-01-24 18:33:17] Joe Erickson

The commercial solution, SpreadsheetGear for .NET [1] will do it.

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

Disclaimer: I own SpreadsheetGear LLC

[1] https://www.spreadsheetgear.com/
[2] https://www.spreadsheetgear.com/support/samples/
[3] https://www.spreadsheetgear.com/downloads/register.aspx

(6) You have a great product but I think a lot of people here are expecting free solutions. That might explain the down votes. - md1337
6
[+59] [2009-06-01 15:45:06] Nate

A few options I have used:

If XLSX is a must: ExcelPackage [1] 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 [2] 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 [3], the .NET port of Apache POI [4]. NPOI 2.0 (Alpha) [5] also supports XLSX.

[1] https://archive.codeplex.com/?p=ExcelPackage
[2] https://archive.codeplex.com/?p=exml
[3] https://github.com/tonyqus/npoi
[4] https://poi.apache.org/
[5] https://github.com/tonyqus/npoi

Be careful with ExcelPackage if you need to support XLS. I had a hard time with it and eventually switched to ExcelLibrary. - Jeremy
Definitely true. ExcelPackage/ExML is only a good option if you need the XLSX support. - Nate
(3) 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
7
[+57] [2008-09-29 22:37:59] Forgotten Semicolon

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 [1] 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.

[1] http://jasonhaley.com/blog/archive/2004/03/20/9583.aspx

(5) 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) This solution worked fine for me, just note you cannot use .xlsx extension - Jill
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
8
[+44] [2009-02-12 15:04:46] Petr Snobelt

You can use ExcelXmlWriter [1].

It works fine.

[1] https://www.carlosag.net/Tools/ExcelXmlWriter/

9
[+42] [2008-09-29 22:34:23] GEOCHET

You actually might want to check out the interop classes [1]. 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 [2] 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.

[1] http://msdn.microsoft.com/en-us/library/ms173186(VS.80).aspx
[2] https://support.microsoft.com/en-in/help/257757/considerations-for-server-side-automation-of-office

(5) But you have to make sure that you dispose of everything manually, otherwise you will leak memory - MagicKat
(6) @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) 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
(10) @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) 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
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
10
[+41] [2010-11-23 16:33:00] Manuel

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

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");

(9) 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) @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
This was the most suitable library. Since using it, I have been thinking how silly I was to use the Excel Interop COM! It's blazingly fast and the devs are active and responsive. - Amir No-Family
11
[+30] [2011-12-05 12:08:36] Mike Gledhill

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.


This seems a bit misleading, as you are asking for a donation to get all of the features. - UrbanEsc
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
12
[+23] [2008-09-30 01:16:40] Sam Warwick

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

[1] https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example
[2] https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140066(v=office.10)#odc_xmlss_ss:Workbook

13
[+19] [2016-10-07 18:03:31] Davis Jebaraj

Syncfusion Essential XlsIO [1] 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 [6] program if you qualify (less than 1 million USD in revenue). Note: I work for Syncfusion.

[1] https://www.syncfusion.com/products/file-formats/xlsio
[2] https://help.syncfusion.com/file-formats/xlsio/asp-net
[3] https://help.syncfusion.com/file-formats/xlsio/asp-net-mvc
[4] https://help.syncfusion.com/file-formats/xlsio/uwp
[5] https://help.syncfusion.com/file-formats/xlsio/xamarin
[6] https://www.syncfusion.com/products/communitylicense

14
[+17] [2008-09-29 22:48:00] ManiacZX

You may want to take a look at GemBox.Spreadsheet [1].

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.

[1] https://www.gemboxsoftware.com/spreadsheet/free-version

15
[+14] [2009-11-10 05:05:23] Dimi Takis

Well,

you can also use a third party library like Aspose [1].

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

[1] http://aspose.com

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) 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
16
[+13] [2008-09-30 03:53:10] Aaron Powell

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


17
[+13] [2008-11-24 08:22:41] biozinc

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.

[1] https://www.gemboxsoftware.com/
[2] http://www.componentone.com/SuperProducts/ExcelNET/

This one works on both .net and java,and is not expensive. SmartXLS smartxls.com - liya
18
[+13] [2011-08-23 16:52:27] Eisbaer

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)


19
[+13] [2015-12-02 13:30:28] Sachin Dhir

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 [1]

[1] http://technowide.net/2015/11/03/openxml-alternative-helps-avoid-ms-excel-server/

20
[+11] [2008-09-29 22:39:50] MagicKat

IKVM [1] + POI [2]

Or, you could use the Interop ...

[1] http://www.ikvm.net/
[2] http://poi.apache.org/

21
[+11] [2008-09-30 01:39:05] Ryan Lundy

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 [1]

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#.)

[1] http://blogs.msdn.com/bethmassi/archive/2007/10/30/quickly-import-and-export-excel-data-with-linq-to-xml.aspx

22
[+11] [2009-02-15 08:12:37] EMP

I've just recently used FlexCel.NET [1] 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.

[1] https://www.tmssoftware.com/site/flexcelnet.asp

23
[+11] [2010-12-03 19:53:12] user529824

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};
    }
}

24
[+11] [2011-02-16 11:47:30] Simen S

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.


25
[+11] [2011-04-28 10:40:05] Gaurav
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


(7) 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
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
26
[+9] [2009-11-10 05:02:18] user35711

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

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

bolton.


27
[+9] [2017-02-27 23:30:08] Taterhead

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 [1] 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

[1] https://github.com/thomasbtatum/GenerateAnyExcelFileWithCSharp

I haven't tried this Open XML SDK solution yet but Wow, I will definitely check it out. I've worked with tools like this for many years and didn't know about this one. I've published my own simple FOSS for converting files to XLSX with .NET: github.com/TonyGravagno/NebulaXConvert - TonyG
28
[+8] [2011-04-12 07:04:36] Bonnie Cornell

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 [1]

[1] http://www.devtriogroup.com/ExcelJetcell/Samples/Default.aspx

29
[+8] [2015-07-23 07:55:17] Harsha.Vaswani

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 this article [1].

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();
    }
[1] https://www.codeproject.com/Articles/9380/Export-a-DataSet-to-Microsoft-Excel-without-the-us

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
Supports xlsx ? OpenXML ? - Kiquenet
30
[+7] [2008-11-04 20:15:11] Stefan Koelle

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.


31
[+4] [2008-09-30 00:53:38] Nick

The Java open source solution is Apache POI [1]. 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.

[1] https://poi.apache.org/

32
[+4] [2010-11-22 10:42:03] Jens

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/


Link appears to be dead - Portland Runner
33
[+3] [2009-10-22 15:21:25] community_owned

I also vote for GemBox.Spreadsheet [1].

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.

[1] https://www.gemboxsoftware.com/spreadsheet

34
[+3] [2011-02-09 10:39:33] Craig Mc

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


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
(6) Microsoft does not recommend or support Office Automation from non-interactive applications such as ASP.NET. See support.microsoft.com/kb/257757 - TrueWill
Excel is quite unreliable & slow when it comes to Automation. - Leslie Godwin
35
[+2] [2010-07-09 19:21:26] ScaleOvenStove

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

There is an example XML from Wikipedia [1].

[1] https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

(2) This is cool except it doesn't support charts or images. - Francois Botha
36
[+2] [2017-12-07 20:40:09] AlexDev

One really easy option which is often overlooked is to create a .rdlc report using Microsoft Reporting [1] 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.

[1] https://msdn.microsoft.com/en-us/library/bb885185.aspx?f=255&MSPPError=-2147217396

37
[+1] [2017-04-05 09:00:59] Gayan Chinthaka Dharmarathna

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!");
   }

38
[+1] [2017-09-27 15:18:35] Vladimir Venegas

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 [1].

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

[1] https://github.com/vvenegasv/exportable

39
[0] [2018-11-05 07:46:39] Vijay Dodamani

To save xls into xlsx format, we just need to call SaveAs method from Microsoft.Office.Interop.Excel library. This method will take around 16 parameters and one of them is file format as well.

Microsoft document: Here SaveAs Method Arguments [1]

The object we need to pass is like

wb.SaveAs(filename, 51, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, false, false, 1,1, true, 
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value)

Here, 51 is is enumeration value for XLSX

For SaveAs in different file formats you can refer the xlFileFormat [2]

[1] https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._workbook.saveas?view=excel-pia
[2] https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlfileformat?view=excel-pia

40
[-6] [2014-12-20 06:37:43] saurabh27

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) 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) 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) 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
41