How to export DataTable to Excel

Question

How can I export a DataTable to Excel in C   I am using Windows Forms  The DataTable is associated with a DataGridView control  I have to export records of DataTable to Excel

User · Answer

I would recommend ClosedXML -

You can turn a DataTable into an Excel worksheet with some very readable code:

XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");

The developer is responsive and helpful. The project is actively developed, and the documentation is superb.

User · Answer

Purely sample code  in case it might help someone with some ideas   based on Tomasz Wisniewski answer from here  https   stackoverflow com a 21079709 2717521  MainWindow ExportButton   public int counter   public void SaveToExcel object sender  RoutedEventArgs e        counter   1      CountChecker       public void CountChecker         string filename   GlobalStrings building house address      string path   Environment GetFolderPath Environment SpecialFolder Desktop                if  CurrentID    0                if   File Exists path   filename     xlsx                          DataGridParts Export path   filename               MessageBoxEx Show this   Shranjeno na namizje                      else                       if   File Exists path          filename           counter            xlsx                                  DataGridParts Export path   filename           counter                          MessageBoxEx Show this   Shranjeno na namizje                              else                               counter                    CountChecker                                      else               MessageBoxEx Show this   Izbran ni noben naslov               ExportToExcel Class   using Microsoft Office Interop Excel  using System Runtime InteropServices   namespace CBUGIS       public static class ExportToExcel                    lt summary gt              Export DataTable to Excel file              lt  summary gt               lt param name  DataTable  gt Source DataTable lt  param gt               lt param name  ExcelFilePath  gt Path to result file name lt  param gt          public static void Export this System Data DataTable DataTable  string ExcelFilePath   null                        int ColumnsCount              int RowShift   5               ColumnsCount   DataTable Columns Count                  load excel  and create a new workbook             Application Excel   new Application                Excel Workbooks Add                    single worksheet              Worksheet Worksheet   Excel ActiveSheet              Excel Sheets 1  Name    CBUGIS                Worksheet Columns NumberFormat                    Worksheet Columns HorizontalAlignment   XlHAlign xlHAlignLeft               object    Title   new object 3  1      Array Size              if  GlobalStrings building alterantive addresses Length    0                                if  GlobalStrings building postcode Length    0                                        Title 0  0     NASLOV      GlobalStrings building house street         GlobalStrings building house number   GlobalStrings building house id          GlobalStrings building postcode         GlobalStrings building area                      Title 1  0     K O       GlobalStrings building cadastral community        T STAVBE      GlobalStrings building building number        T PARCELE      GlobalStrings building plot number                                    else                                       Title 0  0     NASLOV      GlobalStrings building house street         GlobalStrings building house number   GlobalStrings building house id                      Title 1  0     K O       GlobalStrings building cadastral community        T STAVBE      GlobalStrings building building number          GlobalStrings building plot number                                              else                               if  GlobalStrings building postcode Length    0                                        Title 0  0     NASLOV      GlobalStrings building house street         GlobalStrings building house number   GlobalStrings building house id          GlobalStrings building postcode         GlobalStrings building area                      Title 1  0     K O       GlobalStrings building cadastral community        T STAVBE      GlobalStrings building building number        T PARCELE      GlobalStrings building plot number                      Title 2  0     GLEJ TUDI      GlobalStrings building alterantive addresses                                    else                                       Title 0  0     NASLOV      GlobalStrings building house street         GlobalStrings building house number   GlobalStrings building house id                      Title 1  0     K O       GlobalStrings building cadastral community        T STAVBE      GlobalStrings building building number        T PARCELE      GlobalStrings building plot number                      Title 2  0     GLEJ TUDI      GlobalStrings building alterantive addresses                                               Range TitleRange   Worksheet get Range  Range  Worksheet Cells 3  1     Range  Worksheet Cells 1  1                 TitleRange Value   Title              TitleRange Font Bold   true              TitleRange Font Size   10                object   Header   new object 11      Number of Columns              Header 0     DEL               Header 1     DELE                 Header 2     CRP               Header 3     LASTNIK               Header 4     NASLOV               Header 5     P                  Header 6     OBMOCJE               Header 7     DR  AVA               Header 8     EM  O MAT    T                Header 9     OPIS               Header 10     OPOMBA                Range HeaderRange   Worksheet get Range  Range  Worksheet Cells RowShift  2     Range  Worksheet Cells RowShift  12                 HeaderRange Value   Header              HeaderRange Font Bold   true              HeaderRange Interior Color   System Drawing ColorTranslator ToOle System Drawing Color LightGray                   DataCells             int RowsCount   DataTable Rows Count               object    Cells   new object RowsCount  ColumnsCount                for  int j   0  j  lt  RowsCount  j                    for  int i   0  i  lt  ColumnsCount - 1  i                        if  i  gt  1                                                Cells j  i - 2    DataTable Rows j  i                                      Range CellRange   Worksheet get Range  Range  Worksheet Cells RowShift  1  2     Range  Worksheet Cells RowShift   RowsCount  12                 CellRange Value   Cells              CellRange Borders LineStyle   XlLineStyle xlContinuous               Worksheet Columns NumberFormat                    Worksheet Columns 1  ColumnWidth   0 1              for  int b   1  b  lt  12  b                                  if  b  gt  1                                        Worksheet Columns b  AutoFit                                                 Worksheet PageSetup Orientation   XlPageOrientation xlLandscape              Worksheet PageSetup TopMargin   0 5              Worksheet PageSetup BottomMargin   0 5              Worksheet PageSetup RightMargin   0 5              Worksheet PageSetup LeftMargin   0 5                  check fielpath             if  ExcelFilePath    null  amp  amp  ExcelFilePath                                      Worksheet SaveAs ExcelFilePath                   Excel Quit                    Marshal FinalReleaseComObject Worksheet                   Marshal FinalReleaseComObject TitleRange                   Marshal FinalReleaseComObject HeaderRange                   Marshal FinalReleaseComObject CellRange                   Marshal FinalReleaseComObject Excel                             else                 no filepath is given                               Excel Visible   true

User · Answer

Try this function pass the datatable and file path where you want to export      public void CreateCSVFile ref DataTable dt  string strFilePath                    try                  Create the CSV file to which grid data will be exported          StreamWriter sw   new StreamWriter strFilePath  false              First we will write the headers            DataTable dt   m dsProducts Tables 0           int iColCount   dt Columns Count          for  int i   0  i  lt  iColCount  i                          sw Write dt Columns i                if  i  lt  iColCount - 1                                sw Write                                       sw Write sw NewLine               Now write all the rows           foreach  DataRow dr in dt Rows                        for  int i   0  i  lt  iColCount  i                                  if   Convert IsDBNull dr i                                          sw Write dr i  ToString                                       if  i  lt  iColCount - 1                                        sw Write                                                    sw Write sw NewLine                     sw Close              catch  Exception ex                throw ex

User · Answer

I wanted to add this answer because I spent a great deal of time looking for a fast  reliable method to do this and no complete examples of using OpenXMLWriter for this purpose existed anywhere that I could find   First  COM Interop  which many of the other answers use  is OK for this purpose  but it suffers from some sensitivities  I ve used it for decades and it s mostly stable  but when implementing a data warehouse front-end for hundreds of users  I found it to be subject to too many issues depending on the machine and what the user did  so I switched to OpenXML  OpenXML DOM is fairly good for this purpose  but it s slower than using OpenXMLWriter  When you get into large datasets  100K   with lots of columns  DOM is much slower than OpenXMLWriter  so I use the latter  The method below writes 420K  rows with 30  fields in less than 30 seconds   I hope the comments are sufficient to guide anyone through what it s doing  It is simplified  in that it writes all values to the file as strings  but you can implement logic to write various datatypes  and use various cell formats  based on the content of your data  You can also adapt this for use on a DataGridView  instead of a DataTable  by changing just a few things  namely the loops through columns rows    A reference to DocumentFormat OpenXML  d l with the OpenXML SDK  and WindowsBase is required   Imports DocumentFormat OpenXml Imports DocumentFormat OpenXml Spreadsheet Imports DocumentFormat OpenXml Packaging  Public Sub ExportToExcelXML ByRef dt As DataTable  filename As String      Dim wbp As WorkbookPart  wsp As WorksheetPart      If this DataTable has more rows in it than can fit in Excel  throw an exception     If dt Rows Count  gt  1048575 Then Throw New Exception  The DataTable is too large to export to Excel         Delete any previous file of the same name that may exist      File Delete filename       Create an OpenXML SpreadsheetDocument        Using xls   SpreadsheetDocument Create filename  SpreadsheetDocumentType Workbook           Add a WorkbookPart to the Spreadsheet Doc  then add a WorksheetPart to the WorkbookPart          wbp   xls AddWorkbookPart           wsp   wbp AddNewPart Of WorksheetPart           Now we need to add the  StyleSheet  to the WorkbookPart  that we just added above   This will allow us to apply formatting to our Cells           Add the WbStylesPart and the StyleSheet          Dim stp As WorkbookStylesPart   wbp AddNewPart Of WorkbookStylesPart          Dim ss As New Stylesheet          Create the only two Fonts we re going to use  Regular and Bold           Dim fBold As New Font         fBold Append New Bold          Dim fnts As New Fonts         fnts Append New Font   This creates the default  unmodified  regular  Font  It s added first  so its index is 0          fnts Append fBold   This creates the Bold font  It s added second  so its index is 1           Create the default Fill Border settings  these have to be here  even though I don t set any custom fills borders           Dim flls As New Fills         Dim brdrs As New Borders         flls Append New Fill          brdrs Append New Border           Now I have to add formats  NumberFormat and CellFormat   First  you create a NumberFormat  This is basically the pattern of              the format  i e      for Text   For now  I only need a Text format  but I can add more patterns if needed              I give the format an ID of 164  since 163 is where the built-in Excel formats end          Dim nbrfmts As New NumberingFormats         nbrfmts Append New NumberingFormat With   NumberFormatId   164   FormatCode                  Create the first two CellFormats  Default  which will have an index of 0 and  Header   Bold Centered  with an index of 1          Dim cellfmts As New CellFormats           cellfmts Append New CellFormat   With   FontId   0   NumberFormatId   164   FillId   0   BorderId   0           cellfmts Append New CellFormat   With   FontId   1   NumberFormatId   164               Alignment   New Alignment   With   WrapText   True   Horizontal   HorizontalAlignmentValues Center             Add all of the Fonts Fills Borders etc to the StyleSheet and add it all to the WorkbookStylesPart          ss Append fnts          ss Append flls          ss Append brdrs          ss Append cellfmts          ss NumberingFormats   nbrfmts         stp Stylesheet   ss         stp Stylesheet Save            Now create an OpenXMLWriter using the WorksheetPart to write the cells to the worksheet          Using oxw As OpenXmlWriter   OpenXmlWriter Create wsp               Write the start element for the Worksheet and the Columns                oxw WriteStartElement New Worksheet              oxw WriteStartElement New Columns                 Now I m going to loop through the columns in the DataTable                For c As Integer   0 To dt Columns Count - 1                  Now we ll get the width for the column  To do this  we loop through all of the rows and measure the width of the text                      using the default Excel Font  currently Font  Calibri Size  11  and return the largest width  in pixels  to use below                      Why not do this loop below  when I loop through the rows to write the Cells   Because you can t  You have to                     write the Column XML first before writing the SheetData Row Cell XML  I confirmed this by trying it   so there s                     no way  that I m aware of  to avoid looping through all of the rows twice if you want to AutoFit                   Setup vars we ll use for getting the column widths  below                   Dim g   System Drawing Graphics FromHwnd IntPtr Zero                  Dim fnt   New System Drawing Font  Calibri   11                  Dim wid As Double   0                  Get the width of the header  because if this is wider than the widest value  we ll use the header text s width                       I found that adding 2 pixels to the width was necessary to get the column as wide as Excel would make it                  Dim tmp As Double   g MeasureString dt Columns c  ColumnName  New System Drawing Font fnt  System Drawing FontStyle Bold   Width   2                  Loop through the rows in the dt and get the width of the value in that row col  If it s wider than the widest                     width we ve encountered thus far  use the new wider width as our basis                  For Each row As DataRow In dt Rows                     If tmp  gt  wid Then wid   tmp                     tmp   g MeasureString row c  ToString  fnt  Width                 Next                  Set the column attributes and write it to the file  The Width is set using a formula that converts from pixels to Excel s column width values                  Dim oxa As New List Of OpenXmlAttribute  From  New OpenXmlAttribute  min   Nothing  c   1   New OpenXmlAttribute  max   Nothing  c   1                       New OpenXmlAttribute  width   Nothing  System Math Round  wid - 12   5    7D   1  2                    oxw WriteStartElement New Column    oxa                  oxw WriteEndElement               Next              CLose out the Columns collection              oxw WriteEndElement                Write the start element for the SheetData                oxw WriteStartElement New SheetData               Write the start element for the Header row              oxw WriteStartElement New Row               Loop through the Columns in the dt              For Each col As DataColumn In dt Columns                  Write a cell for this column s Header  All Header cells are written with a DataType of String   str                         I ALSO apply the  Header  CellFormat  StyleIndex 1  to all of the Header Cells  This makes them Bold and Centered                  WriteCell oxw  col ColumnName   str   1              Next              Close out the Header row              oxw WriteEndElement                Loop through all of the rows in the dt                For Each row As DataRow In dt Rows                  Write a StartElement for this row                    oxw WriteStartElement New Row                   Loop through all of the columns in the dt                    For c As Integer   0 To dt Columns Count - 1                      Write a value in this row column to the Excel file  I use the datatype of  String  and the default CellFormat StyleIndex                      WriteCell oxw  row c  ToString   str   0                  Next                  Close out this row                  oxw WriteEndElement               Next              Close out the Worksheet and SheetData elements                oxw WriteEndElement               oxw WriteEndElement           End Using          Now we re going to create an OpenXMLWriter using the WorkbookPart  that we created above             Using oxw As OpenXmlWriter   OpenXmlWriter Create wbp               Add starting elements for the Workbook and Sheets collection              oxw WriteStartElement New Workbook                oxw WriteStartElement New Sheets                 Add the Sheet  name the Sheet after the file name minus the extension               oxw WriteElement New Sheet   With   Name   Path GetFileNameWithoutExtension filename    SheetId   1   Id   xls WorkbookPart GetIdOfPart wsp                 Write End elements for the Workbook Sheets             oxw WriteEndElement               oxw WriteEndElement           End Using     End Using  End Sub   This Sub is used to write a value to a Cell using OpenXMLWriter  Private Sub WriteCell ByRef oxw As OpenXmlWriter  value As String  datatype As String  style As UInt32Value      Dim oxa As New List Of OpenXmlAttribute  From  New OpenXmlAttribute  t   Nothing  datatype   New OpenXmlAttribute  s   Nothing  style       oxw WriteStartElement New Cell    oxa      If value  lt  gt  Nothing Then oxw WriteElement New CellValue value       oxw WriteEndElement   End Sub

User · Answer

In regards tuncalik s answer  which is great  especially if you want to have a little play with the code    but it is putting my dates into Excel in American format i e  2nd March 2014 in the UK is 02 03 2014 but in the USA its 03 02 2014 with month 1st  then day of week after  I need to have it in UK format  any ideas please   I have checked it is stored in UK format in my DataTable and also my Excel is set to UK but for some reason when it makes the Excel document it thinks its USA  is this because Microsoft are a USA company     I ll try experimenting with culture codes but not sure where to put that yet  Tried but this had no effect   p s   I did have to change one line to get it to work by adding a  cast  as below     single worksheet Excel  Worksheet workSheet    Excel  Worksheet excelApp ActiveSheet    Update  I have achieved UK formatting of the dates by converting to LongDateTime format  its only a work around though   DateTime startDate   Convert ToDateTime myList 0  ToString     string strStartDate   startDate ToLongDateString    DateTime endDate   Convert ToDateTime myList myListTotalRows  ToString     string strEndDate   endDate ToLongDateString          cheers

User · Answer

Solution based on tuncalik  thanks for idea  article  but in case of big tables is working much more faster  and is a little less clear    public static class My DataTable Extensions            lt summary gt          Export DataTable to Excel file          lt  summary gt           lt param name  DataTable  gt Source DataTable lt  param gt           lt param name  ExcelFilePath  gt Path to result file name lt  param gt      public static void ExportToExcel this System Data DataTable DataTable  string ExcelFilePath   null                try                       int ColumnsCount               if  DataTable    null     ColumnsCount   DataTable Columns Count     0                  throw new Exception  ExportToExcel  Null or empty input table  n                    load excel  and create a new workbook             Microsoft Office Interop Excel Application Excel   new Microsoft Office Interop Excel Application                Excel Workbooks Add                    single worksheet             Microsoft Office Interop Excel  Worksheet Worksheet   Excel ActiveSheet               object   Header   new object ColumnsCount                   column headings                            for  int i   0  i  lt  ColumnsCount  i                    Header i    DataTable Columns i  ColumnName               Microsoft Office Interop Excel Range HeaderRange   Worksheet get Range  Microsoft Office Interop Excel Range  Worksheet Cells 1  1     Microsoft Office Interop Excel Range  Worksheet Cells 1  ColumnsCount                 HeaderRange Value   Header              HeaderRange Interior Color   System Drawing ColorTranslator ToOle System Drawing Color LightGray               HeaderRange Font Bold   true                  DataCells             int RowsCount   DataTable Rows Count              object    Cells   new object RowsCount  ColumnsCount                for  int j   0  j  lt  RowsCount  j                    for  int i   0  i  lt  ColumnsCount  i                        Cells j  i    DataTable Rows j  i                Worksheet get Range  Microsoft Office Interop Excel Range  Worksheet Cells 2  1     Microsoft Office Interop Excel Range  Worksheet Cells RowsCount   1  ColumnsCount    Value   Cells                  check fielpath             if  ExcelFilePath    null  amp  amp  ExcelFilePath                                      try                                       Worksheet SaveAs ExcelFilePath                       Excel Quit                        System Windows MessageBox Show  Excel file saved                                       catch  Exception ex                                        throw new Exception  ExportToExcel  Excel file could not be saved  Check filepath  n                            ex Message                                               else       no filepath is given                               Excel Visible   true                                  catch  Exception ex                        throw new Exception  ExportToExcel   n    ex Message

User · Answer

use the following class  using System  using System Collections Generic  using System Linq  using System Text  using System Data  using excel   Microsoft Office Interop Excel  using EL   ExcelLibrary SpreadSheet  using System Drawing  using System Collections  using System Runtime InteropServices  using System Windows Forms    namespace  basic   public class ExcelProcesser       public void WriteToExcel System Data DataTable dt                excel Application XlObj   new excel Application            XlObj Visible   false          excel  Workbook WbObj    excel Workbook  XlObj Workbooks Add               excel  Worksheet WsObj    excel Worksheet WbObj ActiveSheet          object misValue   System Reflection Missing Value            try                       int row   1  int col   1              foreach  DataColumn column in dt Columns                                  adding columns                 WsObj Cells row  col    column ColumnName                  col                                reset column and row variables             col   1              row                for  int i   0  i  lt  dt Rows Count  i                                    adding data                 foreach  var cell in dt Rows i  ItemArray                                        WsObj Cells row  col    cell                      col                                      col   1                  row                              WbObj SaveAs fileFullName  excel XlFileFormat xlWorkbookNormal  misValue  misValue  misValue  misValue  excel XlSaveAsAccessMode xlExclusive  misValue  misValue  misValue  misValue  misValue                     catch  Exception ex                        MessageBox Show ex Message                     finally                       WbObj Close true  misValue  misValue

User · Answer

This solution is basically pushing List lt Object gt  data to Excel  It uses DataTable to achieve this  I implemented an extension method  so basically there are two things   needed    1  An Extension Method    public static class ReportHelper       public static string ToExcel lt T gt  this IList lt T gt  data                PropertyDescriptorCollection properties               TypeDescriptor GetProperties typeof T            DataTable table   new DataTable            foreach  PropertyDescriptor prop in properties                          table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType               if  prop Attributes typeof FGMS Entity Extensions ReportHeaderAttribute      null                                table Columns Add GetColumnHeader prop   Nullable GetUnderlyingType prop PropertyType     prop PropertyType                                      So it seems like when there is only one row of data the headers do not appear           so adding a dummy blank row which fixed the issues           Add a blank Row - Issue   1471         DataRow blankRow   table NewRow            table Rows Add blankRow            foreach  T item in data                        DataRow row   table NewRow                foreach  PropertyDescriptor prop in properties                    row prop Name    prop GetValue item     DBNull Value                  if  prop Attributes typeof FGMS Entity Extensions ReportHeaderAttribute      null                                        row GetColumnHeader prop     prop GetValue item     DBNull Value                                table Rows Add row                     table TableName    Results           var filePath   System IO Path GetTempPath            System Guid NewGuid   ToString       xls           table WriteXml filePath            return filePath             private static string GetColumnHeader PropertyDescriptor prop                return   FGMS Entity Extensions ReportHeaderAttribute  prop Attributes typeof FGMS Entity Extensions ReportHeaderAttribute     ReportHeaderText                         Decorate your DTO classes with the Attribute  ReportHeaderAttribute  Column Name         public class UserDTO               public int Id   get  set            public int SourceId   get  set            public string SourceName   get  set              ReportHeaderAttribute  User Type            public string UsereType   get  set              ReportHeaderAttribute  Address            public string Address  get  set              ReportHeaderAttribute  Age            public int Age  get  set             public bool IsActive   get  set              ReportHeaderAttribute  Active            public string IsActiveString                       get                               return IsActive    Yes     No                              Everything that needs to be a column in the Excel has to be decorated with  ReportHeaderAttribute  Column Name       Then Simply   Var userList   Service GetUsers     Returns List of UserDTO  var excelFilePath   userList ToExcel     HttpResponseMessage result   new HttpResponseMessage HttpStatusCode OK               var stream   new FileStream excelFilePath  FileMode Open               result Content   new StreamContent stream               result Content Headers ContentType                   new MediaTypeHeaderValue  application vnd ms-excel                result Content Headers ContentDisposition   new ContentDispositionHeaderValue  attachment     FileName    UserList xls                  return result

User · Answer

You can use EasyXLS that is a library for exporting Excel files   Check this code   DataSet ds   new DataSet    ds Tables Add dataTable    ExcelDocument xls   new ExcelDocument    xls easy WriteXLSFile FromDataSet  datatable xls   ds              new ExcelAutoFormat Styles AUTOFORMAT EASYXLS1    DataTable      See also this sample about how to export datatable to excel in C

User · Answer

Old thread - but thought i would throw my code in here  I wrote a little function to write a data table to a new excel sheet at a specified path  location    Also you will need to add a reference to microsoft excel 14 0 library    I pulled from this thread on writing anything to excel - How to write some data to excel file  xlsx   i used that to extrapolate how to write a datatable   note in catch statements i have an errorhandler static class reference  you can ignore those    using excel   Microsoft Office Interop Excel   using System IO   using System Data   using System Runtime InteropServices      class and namespace wrapper is not shown in this example    private void WriteToExcel System Data DataTable dt  string location                  instantiate excel objects  application  workbook  worksheets          excel Application XlObj   new excel Application            XlObj Visible   false          excel  Workbook WbObj    excel Workbook  XlObj Workbooks Add               excel  Worksheet WsObj    excel Worksheet WbObj ActiveSheet             run through datatable and assign cells to values of datatable         try                       int row   1  int col   1              foreach  DataColumn column in dt Columns                                  adding columns                 WsObj Cells row  col    column ColumnName                  col                                reset column and row variables             col   1              row                for  int i   0  i  lt  dt Rows Count  i                                    adding data                 foreach  var cell in dt Rows i  ItemArray                                        WsObj Cells row  col    cell                      col                                      col   1                  row                              WbObj SaveAs location                     catch  COMException x                                        ErrorHandler Handle x                     catch  Exception ex                                       ErrorHandler Handle ex                     finally                       WbObj Close

User · Answer

Using DocumentFormat OpenXml nuget package  I have  created a singleton class which handles export to excel from either a DataTable or DataSet  Tables will be represented as separate sheets in a workbook  The main class public sealed class OfficeOpenXML               private static Lazy lt OfficeOpenXML gt   instance   new Lazy lt OfficeOpenXML gt       gt  new OfficeOpenXML             private OfficeOpenXML                                public static OfficeOpenXML GetInstance                         return  instance Value                     public MemoryStream GetExcelStream DataSet ds  bool firstRowAsHeader   false                        if  ds    null    ds Tables Count    0                                return null                             MemoryStream stream   new MemoryStream                using  var excel   SpreadsheetDocument Create stream  SpreadsheetDocumentType Workbook                                   create doc and workbook                 WorkbookPart workbookPart   excel AddWorkbookPart                    Workbook workbook   new Workbook                    Sheets sheets   new Sheets                      loop all tables in the dataset                 for  int iTable   0  iTable  lt  ds Tables Count  iTable                                          var table   ds Tables iTable                         create sheet part                     WorksheetPart worksheetPart   workbookPart AddNewPart lt WorksheetPart gt                         Worksheet worksheet   new Worksheet                        SheetData data   new SheetData                        List lt Row gt  allRows   new List lt Row gt                            setting header of the sheet                     Row headerRow   new Row     RowIndex   1                        for  int iColumn   0  iColumn  lt  table Columns Count  iColumn                                                  var col   table Columns iColumn                             if first row of table is not the header then set columns of table as header of sheet                         if   firstRowAsHeader                                                        headerRow Append new Cell                                                               DataType   CellValues String                                  CellValue   new CellValue col ColumnName                                                                                    else                                                       headerRow Append new Cell                                                               DataType   CellValues String                                  CellValue   new CellValue Convert ToString table Rows 0  col                                                                                                        allRows Add headerRow                          setting other data rows                     if  table Rows    null  amp  amp  table Rows Count    0                                                for  int iRow   firstRowAsHeader   1   0  iRow  lt  table Rows Count  iRow                                                          var row   table Rows iRow                               Row valueRow   new Row   RowIndex    uint  iRow    firstRowAsHeader   1   2                                   for  int iColumn   0  iColumn  lt  table Columns Count  iColumn                                                                  var col   table Columns iColumn                                   valueRow Append new Cell                                                                       DataType   Format col DataType                                       CellValue   new CellValue Convert ToString row col                                                                                                  allRows Add valueRow                                                                          add rows to the data                     data Append allRows                       worksheet Append data                       worksheetPart Worksheet   worksheet                      worksheetPart Worksheet Save                           add worksheet to main sheets                     sheets Append new Sheet                                               Name   string IsNullOrWhiteSpace table TableName     quot Sheet quot     iTable   1    table TableName                          Id   workbookPart GetIdOfPart worksheetPart                           SheetId    uint iTable   1                                            single table processing ends here                    add created sheets to workbook                 workbook Append sheets                   excel WorkbookPart Workbook   workbook                  excel WorkbookPart Workbook Save                      excel Close                              stream Seek 0  SeekOrigin Begin                   return stream                      public MemoryStream GetExcelStream DataTable dt  bool firstRowAsHeader   false                        DataSet ds   new DataSet                ds Tables Add dt               return GetExcelStream ds  firstRowAsHeader                         region Excel Helpers          CellValues Format Type t                         switch  t ToString                                   case  quot System String quot                       return CellValues String                  case  quot System DateTime quot                       return CellValues Date                  case  quot System Boolean quot                       return CellValues Boolean                  case  quot System Int16 quot                       return CellValues Number                  case  quot System Int32 quot                       return CellValues Number                  case  quot System Int64 quot                       return CellValues Number                  case  quot System UInt16 quot                       return CellValues Number                  case  quot System UInt32 quot                       return CellValues Number                  case  quot System UInt64 quot                       return CellValues Number                  case  quot System Decimal quot                       return CellValues Number                  case  quot System Double quot                       return CellValues Number                  case  quot System Single quot                       return CellValues Number                  default                      return CellValues String                                   endregion        Save to a file         var excelApp   OfficeOpenXML GetInstance            var dt   GetDataTableFromDB            using  var stream   excelApp GetExcelStream dt  false    use true to hide datatable columns from excel                        using  FileStream fs   new FileStream   quot C  Users Public myexcel xlsx quot   FileMode Create                                 stream CopyTo fs                   fs Flush                             Download in an MVC application public ActionResult DownloadReport         var ds   GetDataSetFromDB        var excelApp   OfficeOpenXML GetInstance        var file   excelApp GetExcelStream ds  false       return File file   quot application vnd openxmlformats-officedocument spreadsheetml sheet quot   Guid NewGuid   ToString      quot  xlsx quot

User · Answer

Private tmr As System Windows Forms Timer  Private Sub TestExcel   Handles Button1 Click          Initial data  SQL Server table with 6 columns and 293000 rows            Data table holding all data     Dim dt As New DataTable  F161            Create connection     Dim conn As New SqlConnection  Server MYSERVER Database Test Trusted Connection Yes        Dim fAdapter As New SqlDataAdapter With                SelectCommand   New SqlCommand   SELECT   FROM dbo MyTable   conn                 Fill DataTable     fAdapter Fill dt           Create Excel application     Dim xlApp As New Excel Application With   Visible   True           Temporarily disable screen updating     xlApp ScreenUpdating   False          Create brand new workbook     Dim xlBook As Excel Workbook   xlApp Workbooks Add       Dim xlSheet As Excel Worksheet   DirectCast xlBook Sheets 1   Excel Worksheet           Get number of rows     Dim rows count   dt Rows Count         Get number of columns     Dim cols count   dt Columns Count          Here  s the core idea  after receiving data         you need to create an array and transfer it to sheet          Why array          Because it s the fastest way to transfer data to Excel s sheet          So  we have two tasks          1  Create array         2  Transfer array to sheet                                                                            TASK 1  Create array                                                                           In order to create array  we need to know that         Excel s Range object expects 2-D array whose lower bounds         of both dimensions start from 1          This means you can t use C  array          You need to manually create such array          Since we already calculated number of rows and columns          we can use these numbers in creating array      Dim arr   Array CreateInstance GetType Object    rows count  cols count    1  1            Fill array     For r   0 To rows count - 1         For c   0 To cols count - 1             arr r   1  c   1    dt Rows r  c          Next     Next                                                                            TASK 2  Transfer array to sheet                                                                           Now we need to transfer array to sheet          So  how transfer array to sheet fast                   THE FASTEST WAY TO TRANSFER DATA TO SHEET IS TO ASSIGN ARRAY TO RANGE          We could  of course  hard-code values  but Resize property         makes this work a breeze      xlSheet Range  A1   Resize Resize rows count  cols count  Value   arr          If we decide to dump data by iterating over array          it will take LOTS of time          For r   1 To rows count             For c   1 To cols count                 xlSheet Cells r  c    arr r  c              Next         Next          Here are time results          1  Assigning array to Range  3 seconds         2  Iterating over array  45 minutes          Turn updating on     xlApp ScreenUpdating   True     xlApp   Nothing     xlBook   Nothing     xlSheet   Nothing          Here we have another problem          creating array took lots of memory  about 150 MB           Using  GC Collect     by unknown reason  doesn t help here          However  if you run GC Collect   AFTER this procedure is finished          say  by pressing another button and calling another procedure           then the memory is cleaned up          I was wondering how to avoid creating some extra button to just release memory          so I came up with the idea to use timer to call GC          After 2 seconds GC collects all generations          Do not forget to dispose timer since we need it only once       tmr   New Timer       AddHandler tmr Tick          Sub               GC Collect               GC WaitForPendingFinalizers               GC WaitForFullGCComplete               tmr Dispose           End Sub     tmr Interval   TimeSpan FromSeconds 2  TotalMilliseconds       tmr Start    End Sub

User · Answer

With the EPPlus NuGet package  it s very easy  public class TestObject       public int Col1   get  set        public int Col2   get  set        public string Col3   get  set        public DateTime Col4   get  set        TestMethod  public void LoadFromCollection MemberList Test           https   stackoverflow com questions 32587834 epplus-loadfromcollection-text-converted-to-number 32590626 32590626      var TestObjectList   new List lt TestObject gt         for  var i   0  i  lt  10  i            TestObjectList Add new TestObject  Col1   i  Col2   i 10  Col3    i 10     quot E4 quot            Create a test file     var fi   new FileInfo   quot c  temp LoadFromCollection MemberList Test xlsx quot        if  fi Exists          fi Delete         using  var pck   new ExcelPackage fi                   Do NOT include Col1         var mi   typeof  TestObject               GetProperties                Where pi   gt  pi Name     quot Col1 quot                Select pi   gt   MemberInfo pi               ToArray             var worksheet   pck Workbook Worksheets Add  quot Sheet1 quot            worksheet Cells LoadFromCollection              TestObjectList               true               TableStyles Dark1               BindingFlags Public  BindingFlags Instance               mi            pck Save             Notice that Col1 is NOT in the output

User · Answer

To export data to Excel  you can use the ClosedXML Report library  https   github com ClosedXML ClosedXML Report   Believe me  this is a wonderful library and easy for her to use  The library does not need Excel Interop  ClosedXML Report generates an Excel file based on a template that you can create in Excel using any formatting  For example       var template   new XLTemplate     Templates report xlsx         using  var db   new DbDemos                  var cust   db customers LoadWith c   gt  c Orders  First            template AddVariable cust           template Generate               template SaveAs outputFile

User · Answer

Some smart answers on here  Tomasz Wisniewski  cuongle   They seem to fall into two categories  either you need a wrapper plug-in library or the eventual writing of the data needs to loop writing cell by cell  line by line  With the latter  performance will be slow  and perhaps the first is not so convenient for what should be such a simple task as exporting a DataTable to Excel  Here s how I do it  utilising a copy paste which is much faster  3 milliseconds for 800 rows when I tested   but we have to cheat slightly  What you have to do is create a hidden Form with a DataGridView inside on the fly  Then when you dump your DataTable in their as the DataSource  you can call the  quot GetClipboardContent quot  method of the DataGridView class  and finally simply place it in a selected Range Cell on the Excel sheet  Example code  private bool FncCopyDataTableToWorksheet DataTable dt  ExcelUsing Worksheet destWsh                Form frm   new Form            frm Size   new Size 0  0                        DataGridView dgv   new DataGridView            DataObject obj          ExcelUsing Range rng          int j   0           frm Controls Add dgv            bool result   false           try                       dgv DataSource   dt              dgv RowHeadersVisible   false              frm Show                dgv SelectAll                obj   dgv GetClipboardContent                frm Hide                dgv ClearSelection                 if  obj    null                                Clipboard SetDataObject obj                      Write Headers                 foreach  DataGridViewColumn dgvc in dgv Columns                                        if  dgvc Visible    true                                                rng    ExcelUsing Range destWsh Cells 1  1   j                           rng Value2   dgvc Name ToString                            j                                                             rng    ExcelUsing Range destWsh Cells 2  1                   rng Select                    destWsh PasteSpecial rng   Missing   Missing   Missing   Missing   Missing  true                    result   true                             else   result   false                      catch  Exception ex                          Handle                    rng   null          obj   null          dgv   null          frm   null           return result

User · Answer

An elegant option is writing an extension method  see below  for the DataTable class of  net framework   This extention method can be called as follows   using System  using System Collections Generic  using System Linq  using Excel   Microsoft Office Interop Excel  using System Data  using System Data OleDb   DataTable dt     fill table data in dt here          export DataTable to excel    save excel file without ever making it visible if filepath is given    don t save excel file  just make it visible if no filepath is given dt ExportToExcel ExcelFilePath     Extension method for DataTable class   public static class My DataTable Extensions           Export DataTable into an excel file with field names in the header line        - Save excel file without ever making it visible if filepath is given        - Don t save excel file  just make it visible if no filepath is given     public static void ExportToExcel this DataTable tbl  string excelFilePath   null            try               if  tbl    null    tbl Columns Count    0                  throw new Exception  ExportToExcel  Null or empty input table  n                    load excel  and create a new workbook             var excelApp   new Excel Application                excelApp Workbooks Add                    single worksheet             Excel  Worksheet workSheet   excelApp ActiveSheet                  column headings             for  var i   0  i  lt  tbl Columns Count  i                      workSheet Cells 1  i   1    tbl Columns i  ColumnName                                rows             for  var i   0  i  lt  tbl Rows Count  i                         to do  format datetime values before printing                 for  var j   0  j  lt  tbl Columns Count  j                          workSheet Cells i   2  j   1    tbl Rows i  j                                                   check file path             if   string IsNullOrEmpty excelFilePath                     try                       workSheet SaveAs excelFilePath                       excelApp Quit                        MessageBox Show  Excel file saved                                       catch  Exception ex                        throw new Exception  ExportToExcel  Excel file could not be saved  Check filepath  n                                            ex Message                                   else      no file path is given                 excelApp Visible   true                                  catch  Exception ex                throw new Exception  ExportToExcel   n    ex Message

User · Answer

One way of doing it would be also with ACE OLEDB Provider  see also connection strings for Excel   Of course you d have to have the provider installed and registered  You should have it  if you have Excel installed  but this is something you have to consider when deploying the app    This is the example of calling the helper method from ExportHelper  ExportHelper CreateXlsFromDataTable myDataTable    C  tmp export xls     The helper for exporting to Excel file using ACE OLEDB   public class ExportHelper       private const string ExcelOleDbConnectionStringTemplate    Provider Microsoft ACE OLEDB 12 0 Data Source  0  Extended Properties   Excel 8 0 HDR YES                lt summary gt          Creates the Excel file from items in DataTable and writes them to specified output file           lt  summary gt      public static void CreateXlsFromDataTable DataTable dataTable  string fullFilePath                string createTableWithHeaderScript   GenerateCreateTableCommand dataTable            using  var conn   new OleDbConnection String Format ExcelOleDbConnectionStringTemplate  fullFilePath                          if  conn State    ConnectionState Open                                conn Open                               OleDbCommand cmd   new OleDbCommand createTableWithHeaderScript  conn               cmd ExecuteNonQuery                 foreach  DataRow dataExportRow in dataTable Rows                                AddNewRow conn  dataExportRow                                      private static void AddNewRow OleDbConnection conn  DataRow dataRow                string insertCmd   GenerateInsertRowCommand dataRow            using  OleDbCommand cmd   new OleDbCommand insertCmd  conn                         AddParametersWithValue cmd  dataRow               cmd ExecuteNonQuery                              lt summary gt          Generates the insert row command           lt  summary gt      private static string GenerateInsertRowCommand DataRow dataRow                var stringBuilder   new StringBuilder            var columns   dataRow Table Columns Cast lt DataColumn gt    ToList            var columnNamesCommaSeparated   string Join      columns Select x   gt  x Caption            var questionmarkCommaSeparated   string Join      columns Select x   gt                  stringBuilder AppendFormat  INSERT INTO   0       dataRow Table TableName           stringBuilder Append columnNamesCommaSeparated           stringBuilder Append    VALUES             stringBuilder Append questionmarkCommaSeparated           stringBuilder Append               return stringBuilder ToString                    lt summary gt          Adds the parameters with value           lt  summary gt      private static void AddParametersWithValue OleDbCommand cmd  DataRow dataRow                var paramNumber   1           for  int i   0  i  lt   dataRow Table Columns Count - 1  i                          if   ReferenceEquals dataRow Table Columns i  DataType  typeof int    amp  amp   ReferenceEquals dataRow Table Columns i  DataType  typeof decimal                                  cmd Parameters AddWithValue   p    paramNumber  dataRow i  ToString   Replace                                        else                               object value   GetParameterValue dataRow i                    OleDbParameter parameter   cmd Parameters AddWithValue   p    paramNumber  value                   if  value is decimal                                        parameter OleDbType   OleDbType Currency                                               paramNumber   paramNumber   1                            lt summary gt          Gets the formatted value for the OleDbParameter           lt  summary gt      private static object GetParameterValue object value                if  value is string                        return value ToString   Replace                               return value             private static string GenerateCreateTableCommand DataTable tableDefination                StringBuilder stringBuilder   new StringBuilder            bool firstcol   true           stringBuilder AppendFormat  CREATE TABLE   0       tableDefination TableName            foreach  DataColumn tableColumn in tableDefination Columns                        if   firstcol                                stringBuilder Append                                  firstcol   false               string columnDataType    CHAR 255                 switch  tableColumn DataType Name                                case  String                       columnDataType    CHAR 255                        break                  case  Int32                       columnDataType    INTEGER                       break                  case  Decimal                          Use currency instead of decimal because of bug described at                         http   social msdn microsoft com Forums vstudio en-US 5d6248a5-ef00-4f46-be9d-853207656bcc localization-trouble-with-oledbparameter-and-decimal forum csharpgeneral                     columnDataType    CURRENCY                       break                             stringBuilder AppendFormat   0   1    tableColumn ColumnName  columnDataType                     stringBuilder Append                return stringBuilder ToString

User · Answer

You can use my SwiftExcel library  It is especially good when performance and low memory usage come in place as it writes data directly to the file     using  var ew   new ExcelWriter  C   temp  test xlsx          for  var row   1  row  lt   100  row                  for  var col   1  col  lt   10  col                          ew Write   row  row -col  col    col  row                       Nuget command to install     Install-Package SwiftExcel

User · Answer

Just Make use of the CloseMXL Excel Library    It s easy and pretty fast too    Class  private DataTable getAllList                         string constr   ConfigurationManager ConnectionStrings  RConnection   ConnectionString              using  SqlConnection con   new SqlConnection constr                                 using  SqlCommand cmd   new SqlCommand  SELECT EmpId  gender  EmpName  pOnHold FROM Employee  WHERE EmpId      AnyVariable      ORDER BY EmpName                                          using  SqlDataAdapter da   new SqlDataAdapter                                                  DataTable dt   new DataTable                            cmd CommandType   CommandType Text                          cmd Connection   con                          da SelectCommand   cmd                          da Fill dt                           dt Columns 0  ColumnName    Employee Id                           dt Columns 1  ColumnName    Gender                           dt Columns 2  ColumnName    Employee Name                           dt Columns 3  ColumnName    On Hold                            return dt                                                                    Then another method which get the Dataset  public DataSet getDataSetExportToExcel                         DataSet ds   new DataSet                DataTable dtEmp   new DataTable  CLOT List                dtEmp   getAllList                 ds Tables Add dtEmp                ds Tables 0  TableName    Employee     If you which to use Mutliple Tabs              return ds                Now you Button Click Event  protected void btn Export Click object sender  EventArgs e                        DataSet ds   getDataSetExportToExcel                 using  XLWorkbook wb   new XLWorkbook                                  wb Worksheets Add ds                   wb Style Alignment Horizontal   XLAlignmentHorizontalValues Center                  wb Style Font Bold   true                   Response Clear                    Response Buffer   true                  Response Charset                       Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet                   Response AddHeader  content-disposition    attachment filename EmployeeonHoldList xlsx                     using  MemoryStream MyMemoryStream   new MemoryStream                                          wb SaveAs MyMemoryStream                       MyMemoryStream WriteTo Response OutputStream                        Response Flush                        Response End

User · Answer

Excel Interop      This method prevents Dates getting flipped from dd-mm-yyyy to   mm-dd-yyyy   public bool DataTableToExcelFile DataTable dt  string targetFile        const bool dontSave   false      bool success   true         Exit if there is no rows to export     if  dt Rows Count    0  return false       object misValue   System Reflection Missing Value      List lt int gt  dateColIndex   new List lt int gt         Excel Application excelApp   new Excel Application        Excel Workbook excelWorkBook   excelApp Workbooks Add misValue       Excel Worksheet excelWorkSheet   excelWorkBook Sheets  sheet1           Iterate through the DataTable and populate the Excel work sheet     try           for  int i   -1  i  lt   dt Rows Count - 1  i                  for  int j   0  j  lt   dt Columns Count - 1  j                      if  i  lt  0                          Take special care with Date columns                     if  dt Columns j  DataType is typeof DateTime                             excelWorkSheet Cells 1  j   1  EntireColumn NumberFormat    d-MMM-yyyy                             dateColIndex Add j                                                else if     Feel free to add more Formats                      else                             Otherwise Format the column as text                         excelWorkSheet Cells 1  j   1  EntireColumn NumberFormat                                                  excelWorkSheet Cells 1  j   1    dt Columns j  Caption                                     else if  dateColIndex IndexOf j   gt  -1                        excelWorkSheet Cells i   2  j   1    Convert ToDateTime dt Rows i  ItemArray j   ToString  d-MMM-yyyy                                       else                       excelWorkSheet Cells i   2  j   1    dt Rows i  ItemArray j  ToString                                                         Add Autofilters to the Excel work sheet           excelWorkSheet Cells AutoFilter 1  Type Missing  Excel XlAutoFilterOperator xlAnd  Type Missing  true             Autofit columns for neatness         excelWorkSheet Columns AutoFit            if  File Exists exportFile   File Delete exportFile           excelWorkSheet SaveAs exportFile         catch           success   false        finally             Do this irrespective of whether there was an exception or not           excelWorkBook Close dontSave           excelApp Quit            releaseObject excelWorkSheet           releaseObject excelWorkBook           releaseObject excelApp             return success         If you dont care about Dates being flipped  then use see link that shows how to populate all the cells in the Excel spreadsheet in one line of code    Excel Interop - Efficiency and performance  CSV   public string DataTableToCSV DataTable dt  bool includeHeader  string rowFilter  string sortFilter  bool useCommaDelimiter   false  bool truncateTimesFromDates   false        dt DefaultView RowFilter   rowFilter      dt DefaultView Sort   sortFilter      DataView dv   dt DefaultView      string csv   DataTableToCSV dv ToTable  includeHeader  useCommaDelimiter  truncateTimesFromDates         reset the Filtering     dt DefaultView RowFilter   string Empty      return csv     public string DataTableToCsv DataTable dt  bool includeHeader  bool useCommaDelimiter   false  bool truncateTimesFromDates   false        StringBuilder sb   new StringBuilder        string delimter   Constants vbTab      if  useCommaDelimiter          delimter             if  includeHeader            foreach  DataColumn dc in dt Columns                sb AppendFormat   0     Constants vbTab  dc ColumnName                        remove the last Tab         sb Remove sb ToString Length - 1  1           sb Append Environment NewLine              foreach  DataRow dr in dt Rows            foreach  DataColumn dc in dt Columns                if  Information IsDate dr dc ColumnName  ToString     amp  dr dc ColumnName  ToString   Contains         false  amp  truncateTimesFromDates                    sb AppendFormat   0     delimter  Convert ToDateTime dr dc ColumnName  ToString    Date ToShortDateString                   else                   sb AppendFormat   0     delimter  CheckDBNull dr dc ColumnName  ToString   Replace                                               remove the last Tab         sb Remove sb ToString Length - 1  1           sb Append Environment NewLine             return sb ToString     public enum enumObjectType       StrType   0      IntType   1      DblType   2    public object CheckDBNull object obj  enumObjectType ObjectType   enumObjectType StrType        object objReturn   null      objReturn   obj      if  ObjectType    enumObjectType StrType  amp  Information IsDBNull obj             objReturn             else if  ObjectType    enumObjectType IntType  amp  Information IsDBNull obj             objReturn   0        else if  ObjectType    enumObjectType DblType  amp  Information IsDBNull obj             objReturn   0 0            return objReturn

User · Answer

The best and easiest way  private void exportToExcel DataTable dt                   Set up work book  work sheets  and excel application           Microsoft Office Interop Excel Application oexcel   new Microsoft Office Interop Excel Application            try                       string path   AppDomain CurrentDomain BaseDirectory              object misValue   System Reflection Missing Value              Microsoft Office Interop Excel Workbook obook   oexcel Workbooks Add misValue               Microsoft Office Interop Excel Worksheet osheet   new Microsoft Office Interop Excel Worksheet                    obook Worksheets Add misValue                osheet    Microsoft Office Interop Excel Worksheet obook Sheets  Sheet1                int colIndex   0              int rowIndex   1               foreach  DataColumn dc in dt Columns                                colIndex                    osheet Cells 1  colIndex    dc ColumnName                            foreach  DataRow dr in dt Rows                                rowIndex                    colIndex   0                   foreach  DataColumn dc in dt Columns                                        colIndex                        osheet Cells rowIndex  colIndex    dr dc ColumnName                                                osheet Columns AutoFit                string filepath    C   Temp  Book1                  Release and terminate excel              obook SaveAs filepath               obook Close                oexcel Quit                releaseObject osheet                releaseObject obook                releaseObject oexcel               GC Collect                      catch  Exception ex                        oexcel Quit                log AddToErrorLog ex  this Name

User · Answer

Try simple code  to convert DataTable to excel file as csv   var lines   new List lt string gt      string   columnNames   dataTable Columns      Cast lt DataColumn gt         Select column   gt  column ColumnName       ToArray     var header   string Join      columnNames Select name   gt       name        lines Add header    var valueLines   dataTable AsEnumerable        Select row   gt  string Join      row ItemArray Select val   gt       val          lines AddRange valueLines    File WriteAllLines  excel csv   lines     This will write a new file excel csv into the current working directory which is generally either where the  exe is or where you launch it from

[c#] How to export DataTable to Excel

Examples related to c#

Examples related to excel

Examples related to datatable