By mistrmark


2008-09-29 22:30:28 8 Comments

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

30 comments

@Leniel Maccaferri 2009-06-20 20:48:26

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 (Beta) 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

@John M 2010-04-30 13:45:52

A note on NPOI - Row and Column references are zero-based. Does work well for populating an existing template.

@Mike Webb 2010-04-08 21:36:03

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

ExcelLibrary

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

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

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

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

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

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

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU Lesser General Public License (LGPL)
NPOI - Apache License

Here some example code for ExcelLibrary:

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

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

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

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

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

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

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

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

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

@Mark A 2010-11-04 00:11:19

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.

@rossisdead 2011-10-18 23:17:34

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.

@Seth 2012-01-26 23:21:17

EPPlus seems far less buggy than ExcelLibrary, BUT it is GPL and therefore only a solution for open source projects.

@Beep beep 2015-01-27 20:48:09

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

@Chris 2015-10-28 15:06:41

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.

@Amadeus Sánchez 2015-11-30 18:10:23

What about ClosedXML? I may prove to be useful in your projects.

@Shubham 2019-01-04 05:17:40

check this out no need for third party libraries you can simply export datatable data to excel file using this

var dt = "your code for getting data into datatable";
            Response.ClearContent();
            Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd")));
            Response.ContentType = "application/vnd.ms-excel";
            string tab = "";
            foreach (DataColumn dataColumn in dt.Columns)
            {
                Response.Write(tab + dataColumn.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");
            int i;
            foreach (DataRow dataRow in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Response.Write(tab + dataRow[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            Response.End();

@Rup 2019-01-04 09:44:10

That generates a tab-separated file and saves it with an .XLS extension so that it gets opened by Excel. It's not a real Excel file, and you can't include formatting etc. There are similar answers here which try the same trick with HTML and the wrong extension.

@Vijay Dodamani 2018-11-05 07:46:39

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

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

@Rup 2018-11-05 14:45:29

@AlexDev 2017-12-07 20:40:09

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.

@ScaleOvenStove 2010-07-09 19:21:26

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.

@Francois Botha 2010-09-22 16:03:52

This is cool except it doesn't support charts or images.

@ 2009-10-22 15:21:25

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.

@Nick 2008-09-30 00:53:38

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.

@Harsha.Vaswani 2015-07-23 07:55:17

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.

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

@Rup 2015-07-23 09:39:24

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!

@Kiquenet 2017-02-16 14:52:13

Supports xlsx ? OpenXML ?

@EMP 2009-02-15 08:12:37

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.

@biozinc 2008-11-24 08:22:41

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.

@liya 2009-12-03 08:08:22

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

@ManiacZX 2008-09-29 22:48:00

You may want to take a look at GemBox.Spreadsheet.

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.

@Davis Jebaraj 2016-10-07 18:03:31

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.

@Sam Warwick 2008-09-30 01:16:40

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

@Manuel 2010-11-23 16:33:00

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

@Druid 2011-06-08 12:40:24

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.

@Chris Marisic 2015-08-12 16:08:27

@Druid the license is LGPL assuming you don't modify the source code to ClosedXML it is free to use epplus.codeplex.com/license

@GEOCHET 2008-09-29 22:34:23

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.

@MagicKat 2008-09-29 22:40:28

But you have to make sure that you dispose of everything manually, otherwise you will leak memory

@MagicKat 2008-09-29 22:42:57

@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.

@Jennifer Zouak 2010-03-09 21:54:00

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 2010-03-11 17:49:53

@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...

@md1337 2011-02-03 18:52:15

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.

@pkuderov 2013-05-13 15:11:30

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.

@Petr Snobelt 2009-02-12 15:04:46

You can use ExcelXmlWriter.

It works fine.

@Nate 2009-06-01 15:45:06

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.

@Jeremy 2010-09-17 13:55:24

Be careful with ExcelPackage if you need to support XLS. I had a hard time with it and eventually switched to ExcelLibrary.

@Nate 2010-09-21 15:16:28

Definitely true. ExcelPackage/ExML is only a good option if you need the XLSX support.

@Pragmateek 2013-11-03 19:00:31

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.

@Joe Erickson 2009-01-24 18:33:17

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

@md1337 2011-02-03 18:43:19

You have a great product but I think a lot of people here are expecting free solutions. That might explain the down votes.

@Panos 2008-09-29 22:41:09

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:

@Lamar 2008-09-30 01:45:11

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.

@Chris Richner 2009-06-16 07:31:19

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/…

@dbkk 2009-09-29 09:02:22

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

@Kenny Mann 2010-06-09 16:03:29

One should be very careful if using this method. I've found it very flaky for data that isn't in a perfect format.

@Caner Öncü 2014-09-13 18:32:16

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.

@Justin 2016-05-05 21:36:31

Microsoft has upgraded Jet, try this link stackoverflow.com/questions/14401729/…

@Stephen G Tuggy 2016-07-09 05:33:12

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.

@Pellared 2011-08-16 09:25:58

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:

@Josh Brown 2011-09-20 13:03:06

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.

@Snuffleupagus 2013-01-04 16:47:11

Just a heads up that v2.5 is out and can be downloaded here.

@Tsahi Asher 2014-12-24 16:27:57

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.

@Greg 2017-02-17 17:51:38

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 2017-02-17 17:54:49

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.

@horeaper 2018-05-30 23:37:44

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.

@Jan Källman 2010-03-29 12:25:54

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.

@Mike Gledhill 2011-11-23 12:04:00

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

@Simon D 2012-02-05 12:30:14

License is now LGPL, release notes here: epplus.codeplex.com/releases/view/79802

@Paul Chernoch 2015-02-10 18:55:49

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!

@PeterX 2015-02-24 03:39:45

@JanKällman You should update your CodePlex page to show you've got these methods available: LoadFromCollection<T>, LoadFromDataTable etc. (found via here)

@Chris Marisic 2015-08-12 16:10:30

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.

@Marc Meketon 2018-02-15 22:53:00

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

@Vladimir Venegas 2017-09-27 15:18:35

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

@Taterhead 2017-02-27 23:30:08

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

@TonyG 2018-07-26 16:34:27

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

@Gayan Chinthaka Dharmarathna 2017-04-05 09:00:59

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

@Mike Gledhill 2011-12-05 12:08:36

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.

@UrbanEsc 2017-01-23 15:33:19

This seems a bit misleading, as you are asking for a donation to get all of the features.

@Mike Gledhill 2017-05-02 14:15:29

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 !

@mcalex 2019-02-01 04:47:19

NET::ERR_CERT_COMMON_NAME_INVALID as at January 2019

@Sachin Dhir 2015-12-02 13:30:28

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

@saurabh27 2014-12-20 06:37:43

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

@Rup 2014-12-22 20:48:41

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.

@saurabh27 2014-12-23 09:53:12

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.

@Rup 2014-12-23 09:55:02

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?

@Dimi Takis 2009-11-10 05:05:23

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.

@Shahzad Latif 2011-08-29 11:55:55

To be more precise, you can use Aspose.Cells for .NET in order to create Excel (XLS, XLSX) files in your .NET application.

@Mike Gledhill 2012-01-05 13:10:00

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 !!

@user529824 2010-12-03 19:53:12

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

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] How do I enumerate an enum in C#?

63 Answered Questions

[SOLVED] How do I calculate someone's age in C#?

  • 2008-07-31 23:40:59
  • Jeff Atwood
  • 537048 View
  • 1742 Score
  • 63 Answer
  • Tags:   c# .net datetime

30 Answered Questions

[SOLVED] How do I create a file and write to it in Java?

  • 2010-05-21 19:58:55
  • Drew Johnson
  • 2670132 View
  • 1269 Score
  • 30 Answer
  • Tags:   java file-io

39 Answered Questions

[SOLVED] How do I properly clean up Excel interop objects?

17 Answered Questions

[SOLVED] How do I tell if a regular file does not exist in Bash?

  • 2009-03-12 14:48:43
  • Bill the Lizard
  • 2307230 View
  • 2920 Score
  • 17 Answer
  • Tags:   bash file-io scripting

24 Answered Questions

[SOLVED] Cast int to enum in C#

  • 2008-08-27 03:58:21
  • lomaxx
  • 1174141 View
  • 2849 Score
  • 24 Answer
  • Tags:   c# enums casting

38 Answered Questions

12 Answered Questions

41 Answered Questions

[SOLVED] Deep cloning objects

  • 2008-09-17 00:06:27
  • NakedBrunch
  • 740194 View
  • 2033 Score
  • 41 Answer
  • Tags:   c# .net clone

10 Answered Questions

[SOLVED] Should 'using' directives be inside or outside the namespace?

Sponsored Content