Export DataTable to Excel File

Question

I have a DataTable with 30  columns and 6500  rows I need to dump the whole DataTable values into an Excel file Can anyone please help with the C  code I need each column value to be in a cell To be precise I need the exact looking copy of DataTable in an Excel File Please help   Thanks  Vix

User · Accepted Answer

use this code         dt   city GetAllCity     your datatable     string attachment    attachment  filename city xls       Response ClearContent        Response AddHeader  content-disposition   attachment       Response ContentType    application vnd ms-excel       string tab           foreach  DataColumn dc in dt Columns                Response Write tab   dc ColumnName           tab     t             Response Write   n        int i      foreach  DataRow dr in dt Rows                tab               for  i   0  i  lt  dt Columns Count  i                          Response Write tab   dr i  ToString                 tab     t                     Response Write   n              Response End

User · Answer

Most answers are actually producing the CSV which I don t always have good experience with  when opening in Excel   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 do have it  if you have Excel installed  but this is something you have to consider when deploying  e g  on web server     In below helper class code you d have to call something like ExportHelper CreateXlsFromDataTable dataset Tables 0     C  tmp export xls     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

var lines   new List lt string gt                  string   columnNames   dt Columns Cast lt DataColumn gt                                                   Select column   gt  column ColumnName                                                 ToArray                 var header   string Join      columnNames               lines Add header               var valueLines   dt AsEnumerable                                   Select row   gt  string Join      row ItemArray                lines AddRange valueLines               File WriteAllLines  excel csv   lines     Here dt refers to your DataTable pass as a paramter

User · Answer

I had a project that I also needed an exact replica of a DataTable in Excel format  Using ClosedXML  the following code did exactly that for me  using ClosedXML Excel           private void DumpDataTableToExcel DataTable dt  string SavePathAndFilename                        try                               using  XLWorkbook excelWorkbook   new XLWorkbook                                          excelWorkbook AddWorksheet dt                       excelWorkbook SaveAs SavePathAndFilename                                               catch                                 Insert error handling here                             This method will use your DataTable s column names as the column names in Excel and the DataTable name itself will be the sheet name  Hard to accomplish all of this with fewer lines of code  Adding ClosedXML to your project is equally simple  There are instructions in the link above as well as directly on nuget org

User · Answer

Please try this  this will export your data table data s faster to excel    Note  Range  FW   that I have hard coded is because I had 179 columns   public void UpdateExcelApplication SqlDataTable dataTable                var objects   new string dataTable Rows Count  dataTable Columns Count            var rowIndex   0           foreach  DataRow row in dataTable Rows                        var colIndex   0               foreach  DataColumn column in dataTable Columns                                objects rowIndex  colIndex      Convert ToString row column                               rowIndex                       var range   this workSheet Range   A3 FW dataTable Rows Count   2             range Value   objects           this workSheet Columns AutoFit            this workSheet Rows AutoFit

User · Answer

While not a  NET implementation  you may find that the plug-in TableTools may be highly effective depending on your audience  It relies upon flash which shouldn t be a problem for most cases of needing to actually work in-depth and then want to record tabular information   The latest version appears to support copying to clipboard  into a CSV    XLS   really just a tab-delimited file named  xls   to a PDF  or create a printer friendly page version with all rows displayed and the rest of your page s contents hidden   I found the extension on the DataTables site here  http   datatables net extras tabletools   The download is available in the plug-ins  extras  page here  http   datatables net extras   It supposedly is downloaded as a part of DataTables  hence the phrase  Extras included in the DataTables package   but I didn t find it in the download I have been using  Seems to work wonderfully

User · Answer

I use This in page     public void DTToExcel DataTable dt           dosya isimleri ileride ayni anda birden fazla kullanici ayni dosya   zerinde islem yapmak ister d  s  ncesiyle guid yapildi       string FileName   Guid NewGuid   ToString         FileInfo f   new FileInfo Server MapPath  Downloads     string Format     0  xlsx   FileName        if  f Exists          f Delete       delete the file if it already exist       HttpResponse response   HttpContext Current Response      response Clear        response ClearHeaders        response ClearContent        response Charset   Encoding UTF8 WebName      response AddHeader  content-disposition    attachment  filename     FileName     xls        response AddHeader  Content-Type    application Excel        response ContentType    application vnd xlsx         response AddHeader  Content-Length   file Length ToString              create a string writer     using  StringWriter sw   new StringWriter                  using  HtmlTextWriter htw   new HtmlTextWriter sw     datatable a aldigimiz sorguyu bir datagrid e atayip html e   evir                           instantiate a datagrid             DataGrid dg   new DataGrid                dg DataSource   dt              dg DataBind                dg RenderControl htw               response Write sw ToString                 dg Dispose                dt Dispose                response End

User · Answer

Try this to export the data to Excel file same as in DataTable and could customize also   dtDataTable1   ds Tables 0       try               Microsoft Office Interop Excel Application ExcelApp   new Microsoft Office Interop Excel Application            Workbook xlWorkBook   ExcelApp Workbooks Add Microsoft Office Interop Excel XlWBATemplate xlWBATWorksheet            for  int i   1  i  gt  0  i--                        Sheets xlSheets   null              Worksheet xlWorksheet   null                Create Excel sheet             xlSheets   ExcelApp Sheets              xlWorksheet    Worksheet xlSheets Add xlSheets 1   Type Missing  Type Missing  Type Missing               xlWorksheet Name    MY FIRST EXCEL FILE               for  int j   1  j  lt  dtDataTable1 Columns Count   1  j                                  ExcelApp Cells i  j    dtDataTable1 Columns j - 1  ColumnName                  ExcelApp Cells 1  j  Interior Color   System Drawing ColorTranslator ToOle System Drawing Color Green                   ExcelApp Cells i  j  Font Color   System Drawing ColorTranslator ToOle System Drawing Color WhiteSmoke                                for the data of the excel             for  int k   0  k  lt  dtDataTable1 Rows Count  k                                  for  int l   0  l  lt  dtDataTable1 Columns Count  l                                          ExcelApp Cells k   2  l   1    dtDataTable1 Rows k  ItemArray l  ToString                                                ExcelApp Columns AutoFit                        Worksheet ExcelApp ActiveWorkbook Sheets ExcelApp ActiveWorkbook Sheets Count   Delete            ExcelApp Visible   true            catch  Exception ex                MessageBox Show ex Message

User · Answer

If you to export datatable to excel with formatted header text try like this   public void ExportFullDetails                 Int16 id   Convert ToInt16 Session  id             DataTable registeredpeople   new DataTable            registeredpeople   this dataAccess ExportDetails eventid            string attachment    attachment  filename Details xls           Response ClearContent            Response AddHeader  content-disposition   attachment           Response ContentType    application vnd ms-excel           string tab                 registeredpeople Columns  Reg id   ColumnName    Reg  ID           registeredpeople Columns  Name   ColumnName    Name           registeredpeople Columns  Reg country   ColumnName    Country           registeredpeople Columns  Reg city   ColumnName    City           registeredpeople Columns  Reg email   ColumnName    Email           registeredpeople Columns  Reg business phone   ColumnName    Business Phone           registeredpeople Columns  Reg mobile   ColumnName    Mobile           registeredpeople Columns  PositionRole   ColumnName    Position           registeredpeople Columns  Reg work type   ColumnName    Work Type            foreach  DataColumn dc in registeredpeople Columns                        Response Write tab   dc ColumnName               tab     t                      Response Write   n            int i          foreach  DataRow dr in registeredpeople Rows                        tab                   for  i   0  i  lt  registeredpeople Columns Count  i                                  Response Write tab   dr i  ToString                     tab     t                             Response Write   n                      Response End

User · Answer

Working code for Excel Export   try                       DataTable dt   DS Tables 0               string attachment    attachment  filename log xls               Response ClearContent                Response AddHeader  content-disposition   attachment               Response ContentType    application vnd ms-excel               string tab                   foreach  DataColumn dc in dt Columns                                Response Write tab   dc ColumnName                   tab     t                             Response Write   n                int i              foreach  DataRow dr in dt Rows                                tab                       for  i   0  i  lt  dt Columns Count  i                                          Response Write tab   dr i  ToString                         tab     t                                     Response Write   n                              Response End                      catch  Exception Ex

User · Answer

This snippet could be faster to implement      Example data DataTable table   new DataTable    table Columns AddRange new    new DataColumn  Key    new DataColumn  Value       foreach  string name in Request ServerVariables      table Rows Add name  Request ServerVariables name        This actually makes your HTML output to be downloaded as  xls file Response Clear    Response ClearContent    Response ContentType    application octet-stream   Response AddHeader  Content-Disposition    attachment  filename ExcelFile xls        Create a dynamic control  populate and render it GridView excel   new GridView    excel DataSource   table  excel DataBind    excel RenderControl new HtmlTextWriter Response Output     Response Flush    Response End

User · Answer

Below link is used to export datatable to excel in C  Code   http   royalarun blogspot in 2012 01 export-datatable-to-excel-in-c-windows html    using System           using System Data       using System IO       using System Windows Forms         namespace ExportExcel                       public partial class ExportDatatabletoExcel   Form                           public ExportDatatabletoExcel                                     InitializeComponent                                   private void Form1 Load object sender  EventArgs e                                 DataTable dt   new DataTable                       Add Datacolumn                 DataColumn workCol   dt Columns Add  FirstName   typeof String                     dt Columns Add  LastName   typeof String                    dt Columns Add  Blog   typeof String                    dt Columns Add  City   typeof String                    dt Columns Add  Country   typeof String                       Add in the datarow                 DataRow newRow   dt NewRow                     newRow  firstname      Arun                   newRow  lastname      Prakash                   newRow  Blog      http   royalarun blogspot com                    newRow  city      Coimbatore                   newRow  country      India                    dt Rows Add newRow                      open file                 StreamWriter wr   new StreamWriter   D   Book1 xls                     try                                        for  int i   0  i  lt  dt Columns Count  i                                                  wr Write dt Columns i  ToString   ToUpper       t                                               wr WriteLine                           write rows to excel file                     for  int i   0  i  lt   dt Rows Count   i                                                  for  int j   0  j  lt  dt Columns Count  j                                                          if  dt Rows i  j     null                                                                wr Write Convert ToString dt Rows i  j       t                                                              else                                                               wr Write   t                                                                                      go to next line                         wr WriteLine                                                close file                     wr Close                                      catch  Exception ex                                        throw ex

User · Answer

I have done the DataTable to Excel conversion with the following code  Hope it s very easy no need to change more just copy  amp  pest the code replace your variable with your variable  and it will work properly   First create a folder in your solution Document  and create an Excel file MyTemplate xlsx  you can change those name according to your requirement  But remember for that you have to change the name in code also   Please find the following code         protected void GetExcel Click object sender  EventArgs e                             ManageTicketBS objManageTicket   new ManageTicketBS            DataTable DT   objManageTicket GetAlldataByDate        this function will bring the data in DataTable format  you can use your function instate of that             string DownloadFileName          string FolderPath          string FileName    MyTemplate xlsx           DownloadFileName   Path GetFileNameWithoutExtension FileName    new Random   Next 10000  99999    Path GetExtension FileName           FolderPath           DownloadFileName           GetParents Server MapPath    Document     FileName   Server MapPath    Document     DownloadFileName   DT            string path   Server MapPath    Document     FolderPath           FileInfo file   new FileInfo path           if  file Exists                        try                               HttpResponse response   HttpContext Current Response                  response Clear                    response ClearContent                    response ClearHeaders                    response Buffer   true                  response ContentType   MimeType Path GetExtension FolderPath                    response AddHeader  Content-Disposition    attachment filename     DownloadFileName                   byte   data   File ReadAllBytes path                   response BinaryWrite data                   HttpContext Current ApplicationInstance CompleteRequest                    response End                               catch  Exception ex                                ex ToString                              finally                               DeleteOrganisationtoSupplierTemplate path                                     public string GetParents string FilePath  string TempFilePath  DataTable DTTBL                File Copy Path Combine FilePath   Path Combine TempFilePath   true           FileInfo file   new FileInfo TempFilePath           try                       DatatableToExcel DTTBL  TempFilePath  0                return TempFilePath                      catch  Exception ex                                        return                            public static string MimeType string Extension                string mime    application octetstream           if  string IsNullOrEmpty Extension               return mime          string ext   Extension ToLower            Microsoft Win32 RegistryKey rk   Microsoft Win32 Registry ClassesRoot OpenSubKey ext           if  rk    null  amp  amp  rk GetValue  Content Type      null              mime   rk GetValue  Content Type   ToString            return mime              static bool DeleteOrganisationtoSupplierTemplate string filePath                try                                       File Delete filePath               return true                    catch  IOException                                       return false                        public void DatatableToExcel DataTable dtable  string pFilePath  int excelSheetIndex 1                 try                       if  dtable    null  amp  amp  dtable Rows Count  gt  0                                IWorkbook workbook   null                  ISheet worksheet   null                   using  FileStream stream   new FileStream pFilePath  FileMode Open  FileAccess ReadWrite                                          workbook   WorkbookFactory Create stream                       worksheet   workbook GetSheetAt excelSheetIndex                        int iRow   1                         foreach  DataRow row in dtable Rows                                                IRow file   worksheet CreateRow iRow                           int iCol   0                          foreach  DataColumn column in dtable Columns                                                        ICell cell   null                              object cellValue   row iCol                                switch  column DataType ToString                                                                  case  System Boolean                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType Boolean                                            if  Convert ToBoolean cellValue     cell SetCellFormula  TRUE                                                else   cell SetCellFormula  FALSE                                                   cell CellStyle    boolCellStyle                                                                            break                                   case  System String                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType String                                           cell SetCellValue Convert ToString cellValue                                                                              break                                   case  System Int32                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType Numeric                                           cell SetCellValue Convert ToInt32 cellValue                                              cell CellStyle    intCellStyle                                                                            break                                  case  System Int64                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType Numeric                                           cell SetCellValue Convert ToInt64 cellValue                                              cell CellStyle    intCellStyle                                                                            break                                  case  System Decimal                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType Numeric                                           cell SetCellValue Convert ToDouble cellValue                                              cell CellStyle    doubleCellStyle                                                                            break                                  case  System Double                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType Numeric                                           cell SetCellValue Convert ToDouble cellValue                                              cell CellStyle    doubleCellStyle                                                                            break                                   case  System DateTime                                       if  cellValue    DBNull Value                                                                                cell   file CreateCell iCol  CellType String                                           DateTime dateTime   Convert ToDateTime cellValue                                           cell SetCellValue dateTime ToString  dd MM yyyy                                              DateTime cDate   Convert ToDateTime cellValue                                           if  cDate    null  amp  amp  cDate Hour  gt  0                                                                                          cell CellStyle    dateTimeCellStyle                                                                                     else                                                                                          cell CellStyle    dateCellStyle                                                                                                                       break                                  default                                      break                                                            iCol                                                      iRow                                              using  var WritetoExcelfile   new FileStream pFilePath  FileMode Create  FileAccess ReadWrite                                                 workbook Write WritetoExcelfile                           WritetoExcelfile Close                              workbook Write stream                           stream Close                                                                            catch  Exception ex                        throw ex                    This code you just need to copy  amp  pest in your script and add the Namespace as following  Also change the excel file name as previously discussed   using NPOI HSSF UserModel  using NPOI SS UserModel  using NPOI XSSF UserModel  using NPOI SS Util

User · Answer

The most rank answer in this post work  however its is CSV file  It is not actual Excel file  Therefore  you will get a warning when you are opening a file    The best solution I found on the web is using CloseXML https   github com closedxml closedxml You need to  Open XML as well    dt   city GetAllCity     your datatable  using  XLWorkbook wb   new XLWorkbook                  wb Worksheets Add dt            Response Clear            Response Buffer   true          Response Charset               Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet           Response AddHeader  content-disposition    attachment filename GridView xlsx            using  MemoryStream MyMemoryStream   new MemoryStream                          wb SaveAs MyMemoryStream               MyMemoryStream WriteTo Response OutputStream               Response Flush                Response End                      Credit  http   www aspsnippets com Articles Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension aspx

[c#] Export DataTable to Excel File

Examples related to c#

Examples related to asp.net

Examples related to excel

Examples related to datatable

Examples related to export