How do I create an Excel XLS and XLSX file in C without installing Microsoft Office

Question

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

User · Answer

You can use ExcelXmlWriter   It works fine

User · Answer

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

User · Answer

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

User · Answer

Here s a completely free C  library  which lets you export from a DataSet  DataTable or List lt  gt  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

User · Answer

You can try my SwiftExcel library  This library writes directly to the file  so it is very efficient  For example you can write 100k rows in few seconds without any memory usage   Here is a simple example of usage   using  var ew   new ExcelWriter  C   temp  test xlsx          for  var row   1  row  lt   10  row                  for  var col   1  col  lt   5  col                          ew Write   row  row -col  col    col  row

User · Answer

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

User · Answer

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                                            lt summary gt          Replace any of the contained controls with literals          lt  summary gt           lt param name  control  gt  lt  param gt      private static void PrepareControlForExport Control control                for  int i   0  i  lt  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

User · Answer

How to create an Excel   xslx  file using C  on OneDrive without installing Microsoft Office  The Microsoft Graph API provides File and Excel APIs for creating and modifying Excel files stored in OneDrive for both enterprise and consumer accounts  The Microsoft Graph NuGet package provides many interfaces for working with the File and Excel APIs       Name    myExcelFile xslx     File   new Microsoft Graph File          Create an empty file in the user s OneDrive  var excelWorkbookDriveItem   await graphClient Me Drive Root Children Request   AddAsync excelWorkbook       Add the contents of a template Excel file  DriveItem excelDriveItem  using  Stream ms   ResourceHelper GetResourceAsStream ResourceHelper ExcelTestResource           Upload content to the file  ExcelTestResource is an empty template Excel file        https   graph microsoft io en-us docs api-reference v1 0 api item uploadcontent     excelDriveItem   await graphClient Me Drive Items excelWorkbookDriveItem Id  Content Request   PutAsync lt DriveItem gt  ms        At this point  you now have an Excel file created in the user  enterprise or consumer  or group s OneDrive  You can now use the Excel APIs to make changes to the Excel file without using Excel and without needing to understand the Excel XML format

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I recode again the code and now you can create an  xls file  later you can convert to Excel 2003 Open XML Format  private static void exportToExcel DataSet source  string fileName                   Documentacion en             https   en wikipedia org wiki Microsoft Office XML formats            https   answers microsoft com en-us msoffice forum all how-to-save-office-ms-xml-as-xlsx-file 4a77dae5-6855-457d-8359-e7b537beb1db            https   riptutorial com es openxml          const string startExcelXML    quot  lt  xml version   quot 1 0  quot  encoding   quot UTF-8  quot   gt  r n quot                     quot  lt  mso-application progid   quot Excel Sheet  quot   gt  r n quot                      quot  lt Workbook xmlns   quot urn schemas-microsoft-com office spreadsheet  quot  r n quot                      quot xmlns o   quot urn schemas-microsoft-com office office  quot  r n  quot                      quot xmlns x   quot urn schemas-microsoft-com office excel  quot  r n  quot                      quot xmlns ss   quot urn schemas-microsoft-com office spreadsheet  quot  r n  quot                      quot xmlns html   quot http   www w3 org TR REC-html40  quot  gt  r n  quot                      quot xmlns html   quot https   www w3 org TR html401   quot  gt  r n  quot                       quot  lt DocumentProperties xmlns   quot urn schemas-microsoft-com office office  quot  gt  r n  quot                      quot    lt Version gt 16 00 lt  Version gt  r n  quot                      quot  lt  DocumentProperties gt  r n  quot                      quot   lt OfficeDocumentSettings xmlns   quot urn schemas-microsoft-com office office  quot  gt  r n  quot                      quot    lt AllowPNG  gt  r n  quot                      quot   lt  OfficeDocumentSettings gt  r n  quot                       quot   lt ExcelWorkbook xmlns   quot urn schemas-microsoft-com office excel  quot  gt  r n  quot                      quot    lt WindowHeight gt 9750 lt  WindowHeight gt  r n  quot                      quot    lt WindowWidth gt 24000 lt  WindowWidth gt  r n  quot                      quot    lt WindowTopX gt 0 lt  WindowTopX gt  r n  quot                      quot    lt WindowTopY gt 0 lt  WindowTopY gt  r n  quot                      quot    lt RefModeR1C1  gt  r n  quot                      quot    lt ProtectStructure gt False lt  ProtectStructure gt  r n  quot                      quot    lt ProtectWindows gt False lt  ProtectWindows gt  r n  quot                      quot   lt  ExcelWorkbook gt  r n  quot                       quot  lt Styles gt  r n  quot                      quot  lt Style ss ID   quot Default  quot  ss Name   quot Normal  quot  gt  r n  quot                      quot  lt Alignment ss Vertical   quot Bottom  quot   gt  r n  lt Borders  gt  quot                      quot  r n  lt Font  gt  r n  lt Interior  gt  r n  lt NumberFormat  gt  quot                      quot  r n  lt Protection  gt  r n  lt  Style gt  r n  quot                      quot  lt Style ss ID   quot BoldColumn  quot  gt  r n  lt Font  quot                      quot x Family   quot Swiss  quot  ss Bold   quot 1  quot   gt  r n  lt  Style gt  r n  quot                      quot  lt Style ss ID   quot StringLiteral  quot  gt  r n  lt NumberFormat quot                      quot  ss Format   quot    quot   gt  r n  lt  Style gt  r n  lt Style  quot                      quot ss ID   quot Decimal  quot  gt  r n  lt NumberFormat  quot                      quot ss Format   quot 0 0000  quot   gt  r n  lt  Style gt  r n  quot                      quot  lt Style ss ID   quot Integer  quot  gt  r n  lt NumberFormat  gt  quot                      quot ss Format   quot 0  quot   gt  r n  lt  Style gt  r n  lt Style  quot                      quot ss ID   quot DateLiteral  quot  gt  r n  lt NumberFormat  quot                      quot ss Format   quot dd mm yyyy    quot   gt  r n  lt  Style gt  r n  quot                      quot  lt  Styles gt  r n  quot           System IO StreamWriter excelDoc   null          excelDoc   new System IO StreamWriter fileName false            int sheetCount   1          excelDoc Write startExcelXML           foreach  DataTable table in source Tables                        int rowCount   0              excelDoc Write  quot  lt Worksheet ss Name   quot  quot    table TableName    quot   quot  gt  quot                excelDoc Write  quot  lt Table gt  quot                excelDoc Write  quot  lt Row gt  quot                for  int x   0  x  lt  table Columns Count  x                                  excelDoc Write  quot  lt Cell ss StyleID   quot BoldColumn  quot  gt  lt Data ss Type   quot String  quot  gt  quot                    excelDoc Write table Columns x  ColumnName                   excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                              excelDoc Write  quot  lt  Row gt  quot                foreach  DataRow x in table Rows                                rowCount                      if the number of rows is  gt  64000 create a new page to continue output                 if  rowCount    1048576                                        rowCount   0                      sheetCount                        excelDoc Write  quot  lt  Table gt  quot                        excelDoc Write  quot   lt  Worksheet gt  quot                        excelDoc Write  quot  lt Worksheet ss Name   quot  quot    table TableName    quot   quot  gt  quot                        excelDoc Write  quot  lt Table gt  quot                                      excelDoc Write  quot  lt Row gt  quot      ID  quot    rowCount    quot                  for  int y   0  y  lt  table Columns Count  y                                          System Type rowType                      rowType   x y  GetType                        switch  rowType ToString                                                  case  quot System String quot                               string XMLstring   x y  ToString                                XMLstring   XMLstring Trim                                XMLstring   XMLstring Replace  quot  amp  quot    quot  amp  quot                                XMLstring   XMLstring Replace  quot  gt  quot    quot  gt  quot                                XMLstring   XMLstring Replace  quot  lt  quot    quot  lt  quot                                excelDoc Write  quot  lt Cell ss StyleID   quot StringLiteral  quot  gt  quot                                                quot  lt Data ss Type   quot String  quot  gt  quot                                excelDoc Write XMLstring                               excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          case  quot System DateTime quot                                 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    quot  quot     Excel Converted Date                             XMLDatetoString   XMLDate Year ToString                                       quot - quot                                      XMLDate Month  lt  10    quot 0 quot                                     XMLDate Month ToString     XMLDate Month ToString                                        quot - quot                                      XMLDate Day  lt  10    quot 0 quot                                     XMLDate Day ToString     XMLDate Day ToString                                        quot T quot                                      XMLDate Hour  lt  10    quot 0 quot                                     XMLDate Hour ToString     XMLDate Hour ToString                                        quot   quot                                      XMLDate Minute  lt  10    quot 0 quot                                     XMLDate Minute ToString     XMLDate Minute ToString                                        quot   quot                                      XMLDate Second  lt  10    quot 0 quot                                     XMLDate Second ToString     XMLDate Second ToString                                        quot  000 quot                               excelDoc Write  quot  lt Cell ss StyleID   quot DateLiteral  quot  gt  quot                                              quot  lt Data ss Type   quot DateTime  quot  gt  quot                                excelDoc Write XMLDatetoString                               excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          case  quot System Boolean quot                               excelDoc Write  quot  lt Cell ss StyleID   quot StringLiteral  quot  gt  quot                                             quot  lt Data ss Type   quot String  quot  gt  quot                                excelDoc Write x y  ToString                                 excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          case  quot System Int16 quot                           case  quot System Int32 quot                           case  quot System Int64 quot                           case  quot System Byte quot                               excelDoc Write  quot  lt Cell ss StyleID   quot Integer  quot  gt  quot                                         quot  lt Data ss Type   quot Number  quot  gt  quot                                excelDoc Write x y  ToString                                 excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          case  quot System Decimal quot                           case  quot System Double quot                               excelDoc Write  quot  lt Cell ss StyleID   quot Decimal  quot  gt  quot                                       quot  lt Data ss Type   quot Number  quot  gt  quot                                excelDoc Write x y  ToString                                 excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          case  quot System DBNull quot                               excelDoc Write  quot  lt Cell ss StyleID   quot StringLiteral  quot  gt  quot                                       quot  lt Data ss Type   quot String  quot  gt  quot                                excelDoc Write  quot  quot                                excelDoc Write  quot  lt  Data gt  lt  Cell gt  quot                                break                          default                              throw  new Exception rowType ToString      quot  not handled  quot                                                             excelDoc Write  quot  lt  Row gt  quot                              excelDoc Write  quot  lt  Table gt  quot                excelDoc Write  quot  lt  Worksheet gt  quot                               sheetCount                       const string endExcelOptions1    quot  r n lt WorksheetOptions xmlns   quot urn schemas-microsoft-com office excel  quot  gt  r n quot                 quot  lt Selected  gt  r n quot                 quot  lt ProtectObjects gt False lt  ProtectObjects gt  r n quot                 quot  lt ProtectScenarios gt False lt  ProtectScenarios gt  r n quot                 quot  lt  WorksheetOptions gt  r n quot            excelDoc Write endExcelOptions1           excelDoc Write  quot  lt  Workbook gt  quot            excelDoc Close

User · Answer

I wonder why nobody suggested PowerShell with the free ImportExcel Module  it creates XML-Excel files  xlsx  with ease  Especially easy when creating Excel-sheets coming from Databases like SQL Server

User · Answer

IKVM   POI  Or  you could use the Interop

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Hey  Scripting Guy  How Can I Read from Excel Without Using Excel  How To Use ADO NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic  NET Reading and Writing Excel Spreadsheets Using ADO NET C  DbProviderFactory

User · Answer

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

User · Answer

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  lt DataTable Columns Count  i          wr Write DataTable Columns i  ToString   ToUpper       t          wr WriteLine         write rows to excel file   for  int i   0  i  lt   DataTable Rows Count   i          for  int j   0  j  lt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Some useful Excel automation in C    u can find from the following link   http   csharp net-informations com excel csharp-excel-tutorial htm  bolton

User · Answer

You can use a library called ExcelLibrary  It s a free  open source library posted on Google Code   ExcelLibrary  This looks to be a port of the PHP ExcelWriter that you mentioned above  It will not write to the new  xlsx format yet  but they are working on adding that functionality in   It s very simple  small and easy to use  Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data   ExcelLibrary seems to still only work for the older Excel format   xls files   but may be adding support in the future for newer 2007 2010 formats    You can also use EPPlus  which works only for Excel 2007 2010 format files   xlsx files   There s also NPOI which works with both   There are a few known bugs with each library as noted in the comments  In all  EPPlus seems to be the best choice as time goes on  It seems to be more actively updated and documented as well   Also  as noted by                below  EPPlus has support for Pivot Tables and ExcelLibrary may have some support  Pivot table issue in ExcelLibrary   Here are a couple links for quick reference  ExcelLibrary - GNU Lesser GPL EPPlus - GNU  LGPL  - No longer maintained EPPlus 5 - Polyform Noncommercial - Starting May 2020 NPOI - Apache License  Here some example code for ExcelLibrary   Here is an example taking data from a database and creating a workbook from it  Note that the ExcelLibrary code is the single line at the bottom     Create the data set and table DataSet ds   new DataSet  New DataSet    DataTable dt   new DataTable  New DataTable       Set the locale for each ds Locale   System Threading Thread CurrentThread CurrentCulture  dt Locale   System Threading Thread CurrentThread CurrentCulture     Open a DB connection  in this example with OleDB  OleDbConnection con   new OleDbConnection dbConnectionString   con Open       Create a query and fill the data table with the data from the DB string sql    SELECT Whatever FROM MyDBTable    OleDbCommand cmd   new OleDbCommand sql  con   OleDbDataAdapter adptr   new OleDbDataAdapter     adptr SelectCommand   cmd  adptr Fill dt   con Close       Add the table to the data set ds Tables Add dt      Here s the easy part  Create the Excel worksheet from the data set ExcelLibrary DataSetHelper CreateWorkbook  MyExcelFile xls   ds     Creating the Excel file is as easy as that  You can also manually create Excel files  but the above functionality is what really impressed me

User · Answer

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     lt  Workbook gt            const string startExcelXML     lt xml version gt  r n lt 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   gt  r n  lt Styles gt  r n                        lt Style ss ID   Default   ss Name   Normal   gt  r n                        lt Alignment ss Vertical   Bottom    gt  r n  lt Borders  gt                        r n  lt Font  gt  r n  lt Interior  gt  r n  lt NumberFormat  gt                        r n  lt Protection  gt  r n  lt  Style gt  r n                        lt Style ss ID   BoldColumn   gt  r n  lt Font                       x Family   Swiss   ss Bold   1    gt  r n  lt  Style gt  r n                        lt Style     ss ID   StringLiteral   gt  r n  lt NumberFormat                       ss Format        gt  r n  lt  Style gt  r n  lt Style                       ss ID   Decimal   gt  r n  lt NumberFormat                       ss Format   0 0000    gt  r n  lt  Style gt  r n                        lt Style ss ID   Integer   gt  r n  lt NumberFormat                       ss Format   0    gt  r n  lt  Style gt  r n  lt Style                       ss ID   DateLiteral   gt  r n  lt NumberFormat                       ss Format   mm dd yyyy      gt  r n  lt  Style gt  r n                        lt  Styles gt  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   lt Worksheet ss Name       table TableName       gt                 excelDoc Write   lt Table gt                 excelDoc Write   lt Row gt                 for  int x   0  x  lt  table Columns Count  x                                  excelDoc Write   lt Cell ss StyleID   BoldColumn   gt  lt Data ss Type   String   gt                     excelDoc Write table Columns x  ColumnName                   excelDoc Write   lt  Data gt  lt  Cell gt                               excelDoc Write   lt  Row gt                 foreach  DataRow x in table Rows                                rowCount                      if the number of rows is  gt  64000 create a new page to continue output                 if  rowCount    64000                                        rowCount   0                      sheetCount                        excelDoc Write   lt  Table gt                         excelDoc Write    lt  Worksheet gt                         excelDoc Write   lt Worksheet ss Name       table TableName       gt                         excelDoc Write   lt Table gt                                       excelDoc Write   lt Row gt       ID     rowCount                     for  int y   0  y  lt  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   amp      amp                                 XMLstring   XMLstring Replace   gt      gt                                 XMLstring   XMLstring Replace   lt      lt                                 excelDoc Write   lt Cell ss StyleID   StringLiteral   gt                                                  lt Data ss Type   String   gt                                 excelDoc Write XMLstring                               excelDoc Write   lt  Data gt  lt  Cell gt                                 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  lt  10    0                                     XMLDate Month ToString     XMLDate Month ToString                                        -                                      XMLDate Day  lt  10    0                                     XMLDate Day ToString     XMLDate Day ToString                                        T                                      XMLDate Hour  lt  10    0                                     XMLDate Hour ToString     XMLDate Hour ToString                                                                               XMLDate Minute  lt  10    0                                     XMLDate Minute ToString     XMLDate Minute ToString                                                                               XMLDate Second  lt  10    0                                     XMLDate Second ToString     XMLDate Second ToString                                         000                               excelDoc Write   lt Cell ss StyleID   DateLiteral   gt                                                lt Data ss Type   DateTime   gt                                 excelDoc Write XMLDatetoString                               excelDoc Write   lt  Data gt  lt  Cell gt                                 break                          case  System Boolean                               excelDoc Write   lt Cell ss StyleID   StringLiteral   gt                                               lt Data ss Type   String   gt                                 excelDoc Write x y  ToString                                 excelDoc Write   lt  Data gt  lt  Cell gt                                 break                          case  System Int16                           case  System Int32                           case  System Int64                           case  System Byte                               excelDoc Write   lt Cell ss StyleID   Integer   gt                                           lt Data ss Type   Number   gt                                 excelDoc Write x y  ToString                                 excelDoc Write   lt  Data gt  lt  Cell gt                                 break                          case  System Decimal                           case  System Double                               excelDoc Write   lt Cell ss StyleID   Decimal   gt                                         lt Data ss Type   Number   gt                                 excelDoc Write x y  ToString                                 excelDoc Write   lt  Data gt  lt  Cell gt                                 break                          case  System DBNull                               excelDoc Write   lt Cell ss StyleID   StringLiteral   gt                                         lt Data ss Type   String   gt                                 excelDoc Write                                  excelDoc Write   lt  Data gt  lt  Cell gt                                 break                          default                              throw  new Exception rowType ToString       not handled                                                              excelDoc Write   lt  Row gt                               excelDoc Write   lt  Table gt                 excelDoc Write    lt  Worksheet gt                 sheetCount                        excelDoc Write endExcelXML           excelDoc Close

User · Answer

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

User · Answer

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    Gembox ComponentOne Excel   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  TotalFile  i          Contact Clear        if  innerloop    SplitSize                for  int j   0  j  lt  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

User · Answer

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         lt name gt     lt value gt  and  lt comment gt   which will be replaced by the values from the getSample       CellRangeTemplate sample1   helper CreateCellRangeTemplate  sample1   new List lt string gt    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 lt List lt object gt  gt  getSample         var random   new Random         for  int loop   0  loop  lt  3000  loop                  yield return new List lt object gt    test   DateTime Now AddDays random NextDouble   100 - 50   loop

User · Answer

If you are happy with the xlsx format  try my GitHub project  EPPlus  It started with the source from ExcelPackage  but today it s a total rewrite  It supports ranges  cell styling  charts  shapes  pictures  named ranges  AutoFilter and a lot of other stuff

User · Answer

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    Gembox ComponentOne Excel   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

User · Answer

http   www codeproject com KB cs Excel and C  aspx  lt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 xlsx and 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

User · Answer

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

User · Answer

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  lt  dt Columns Count  i                                          Response Write tab   dataRow i  ToString                         tab     t                                     Response Write   n                              Response End

User · Answer

You can install OpenXml nuget package on Visual Studio  Here is a bit of code to export a data table to an excel file  Imports DocumentFormat OpenXml Imports DocumentFormat OpenXml Packaging Imports DocumentFormat OpenXml Spreadsheet  Public Class ExportExcelClass     Public Sub New        End Sub      Public Sub ExportDataTable ByVal table As DataTable  ByVal exportFile As String            Create a spreadsheet document by supplying the filepath            By default  AutoSave   true  Editable   true  and Type   xlsx          Dim spreadsheetDocument As SpreadsheetDocument   spreadsheetDocument Create exportFile  SpreadsheetDocumentType Workbook             Add a WorkbookPart to the document          Dim workbook As WorkbookPart   spreadsheetDocument AddWorkbookPart         workbook Workbook   New Workbook            Add a WorksheetPart to the WorkbookPart          Dim Worksheet As WorksheetPart   workbook AddNewPart Of WorksheetPart            Worksheet Worksheet   New Worksheet New SheetData               Add Sheets to the Workbook          Dim sheets As Sheets   spreadsheetDocument WorkbookPart Workbook AppendChild Of Sheets  New Sheets             Dim data As SheetData   Worksheet Worksheet GetFirstChild Of SheetData            Dim Header As Row   New Row           Header RowIndex   CType 1  UInt32           For Each column As DataColumn In table Columns             Dim headerCell As Cell   createTextCell table Columns IndexOf column    1  1  column ColumnName              Header AppendChild headerCell          Next          data AppendChild Header           Dim contentRow As DataRow         For i As Integer   0 To table Rows Count - 1             contentRow   table Rows i              data AppendChild createContentRow contentRow  i   2           Next      End Sub      Private Function createTextCell ByVal columnIndex As Integer  ByVal rowIndex As Integer  ByVal cellValue As Object  As Cell         Dim cell As Cell   New Cell           cell DataType   CellValues InlineString          cell CellReference   getColumnName columnIndex    rowIndex ToString          Dim inlineString As InlineString   New InlineString           Dim t As Text   New Text           t Text   cellValue ToString           inlineString AppendChild t          cell AppendChild inlineString          Return cell     End Function      Private Function createContentRow ByVal dataRow As DataRow  ByVal rowIndex As Integer  As Row         Dim row As Row   New Row With                rowIndex   CType rowIndex  UInt32                     For i As Integer   0 To dataRow Table Columns Count - 1             Dim dataCell As Cell   createTextCell i   1  rowIndex  dataRow i               row AppendChild dataCell          Next          Return row     End Function      Private Function getColumnName ByVal columnIndex As Integer  As String         Dim dividend As Integer   columnIndex         Dim columnName As String   String Empty         Dim modifier As Integer          While dividend  gt  0             modifier    dividend - 1  Mod 26             columnName   Convert ToChar 65   modifier  ToString    amp  columnName             dividend   CInt   dividend - modifier    26           End While          Return columnName     End Function End Class

User · Answer

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

User · Answer

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

User · Answer

Some time ago  I created a DLL on top of NPOI  It s very simple to use it   IList lt DummyPerson gt  dummyPeople   new List lt DummyPerson gt       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

User · Answer

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

[c#] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to file-io