'How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
How can I create an Excel spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?
Solution 1:[1]
If you are happy with the xlsx format, try my library, EPPlus. It started with the source from ExcelPackage, but since became a total rewrite.
It supports ranges, cell styling, charts, shapes, pictures, named ranges, AutoFilter, and a lot of other stuff.
You have two options:
EPPlus 4, licensed under LGPL (original branch, developed until 2020)
EPPlus 5, licensed under Polyform Noncommercial 1.0.0 (since 2020).
From the EPPlus 5 readme.md:
With the new license EPPlus is still free to use in some cases, but will require a commercial license to be used in a commercial business.
EPPlus website: https://www.epplussoftware.com/
Solution 2:[2]
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:
- Github
- Main MSDN Landing
- "How Do I..." starter page
- blogs.MSDN brian_jones announcing SDK
- blogs.MSDN brian_jones describing SDK handling large files without crashing (unlike DOM method)
Solution 3:[3]
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:
Solution 4:[4]
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:
Solution 5:[5]
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
Solution 6:[6]
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.
Solution 7:[7]
An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.
I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.
Solution 8:[8]
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");
Solution 9:[9]
You actually might want to check out the interop classes available in C# (e.g. Microsoft.Office.Interop.Excel. You say no OLE (which this isn't), but the interop classes are very easy to use. Check out the C# Documentation here (Interop for Excel starts on page 1072 of the C# PDF).
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.
Solution 10:[10]
You can use ExcelXmlWriter.
It works fine.
Solution 11:[11]
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.
Solution 12:[12]
You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.
Solution 13:[13]
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.
Solution 14:[14]
Syncfusion Essential XlsIO can do this. It has no dependency on Microsoft office and also has specific support for different platforms.
- ASP.NET
- ASP.NET MVC
- UWP
- Xamarin
- WPF and Windows Forms
- Windows Service and batch based operations
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.
Solution 15:[15]
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.
Solution 16:[16]
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.
Solution 17:[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();
}
Solution 18:[18]
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
Solution 19:[19]
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.
Solution 20:[20]
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
Solution 21:[21]
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)
Solution 22:[22]
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
Solution 23:[23]
Solution 24:[24]
Here's a way to do it with LINQ to XML, complete with sample code:
Quickly Import and Export Excel Data with LINQ to XML
It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.
(Also, of course, it's VB .NET, not C#, but you can always isolate the VB .NET stuff in its own project to use XML Literals, and do everything else in C#.)
Solution 25:[25]
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.
Solution 26:[26]
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};
}
}
Solution 27:[27]
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:
- Install the Open XML SDK with the tool.
- Create an Excel file using the latest Excel client with desired look. Name it
DesiredLook.xlsx. - With the tool open
DesiredLook.xlsxand click the Reflect Code button near the top.
- 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:
Solution 28:[28]
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.
Solution 29:[29]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow