How to export dataGridView data Instantly to Excel on button click

Question

I have 10k rows and 15 column in my data grid view  I want to export this data to an excel sheet o button click  I have already tried with the below code   private void btExport Click object sender  EventArgs e                Microsoft Office Interop Excel  Application app    new Microsoft Office Interop Excel Application            Microsoft Office Interop Excel  Workbook workbook    app Workbooks Add Type Missing                   Microsoft Office Interop Excel  Worksheet worksheet   null                             app Visible   true          worksheet   workbook Sheets  Sheet1            worksheet   workbook ActiveSheet                            for int i 1 i lt dataGridView1 Columns Count 1 i                           worksheet Cells 1  i    dataGridView1 Columns i-1  HeaderText                        for  int i 0  i  lt  dataGridView1 Rows Count-1   i                          for int j 0 j lt dataGridView1 Columns Count j                                  if  dataGridView1 Rows i  Cells j  Value    null                                        worksheet Cells i   2  j   1    dataGridView1 Rows i  Cells j  Value ToString                                      else                                       worksheet Cells i   2  j   1                                                          This is working for me but it is taking lots of time to complete exporting process   Is it possible to export from dataGridView  with 10k rows to excel instantly on a button click   Other than this  when I tried copy all dataGridview contents to clip board and then paste it to excel sheet manually  it happen almost instantly    So is there a way to copy all dataGridView cells to clip board and paste it to excel sheet with cell formatting  on a button click   I have code for copy to clipboard as below  but I don t know how to paste it in to a new excel sheet by opening it           private void copyAllToolStripMenuItem Click object sender  EventArgs e                dataGridView1 SelectAll            DataObject dataObj   dataGridView1 GetClipboardContent            if  dataObj    null              Clipboard SetDataObject dataObj           Please help with an example  I am new to C

User · Answer

In my opinion this is the easiest and instantly working method of exporting datagridview    try                       SaveFileDialog sfd   new SaveFileDialog                sfd Filter    Excel Documents    xlsx    xlsx               sfd FileName    ProfitLoss xlsx               if  sfd ShowDialog      DialogResult OK                                DataTable dts   new DataTable                    for  int i   0  i  lt  grdProfitAndLoss Columns Count  i                                          dts Columns Add grdProfitAndLoss Columns i  Name                                     for  int j   0  j  lt  grdProfitAndLoss Rows Count  j                                          DataRow toInsert   dts NewRow                        int k   0                      int inc   0                      for  k   0  k  lt  grdProfitAndLoss Columns Count  k                                                  if  grdProfitAndLoss Columns k  Visible    false    continue                            toInsert inc    grdProfitAndLoss Rows j  Cells k  Value                          inc                                              dts Rows Add toInsert                                     dts AcceptChanges                    ExcelUtlity obj   new ExcelUtlity                    obj WriteDataTableToExcel dts   Profit And Loss   sfd FileName   Profit And Loss                    MessageBox Show  Exported Successfully                                    catch  Exception ex

User · Answer

I like Jake s solution  The problem with no header is resolved by doing the following  xlWorkSheet Cells 1  1     Header 1   xlWorkSheet Cells 1  2     Header 2   xlWorkSheet Cells 1  3     Header 3     of course this only works is you know what the headers should be ahead of time

User · Answer

Interop is slow and has other issues  using the the clipboard seems non extensible   Here are two other ways to do this   Work with Excel 2007  files directly instead of working with Excel  it ll be much  much  faster  You can use OpenXML  http   openxmldeveloper org   which is Microsoft s SDK  The best way to learn OpenXML is to download the Productivity tool  http   www microsoft com en-us download details aspx id 5124   ittakes an existing file and generates the code required to create it  Another  perhaps simpler  option is to use ClosedXML  http   closedxml codeplex com    It seems a lot easier to use  look at the example http   closedxml codeplex com wikipage title Showcase amp referringTitle Home   but I have no experience with it  I m sure there are other libraries that wrap work with Excel  Work with excel via OLEDB  This allows you to work with Excel as if it s a dababase  See http   www codeproject com Articles 8500 Reading-and-Writing-Excel-using-OLEDB or Performance of OLEDB to read Excel for examples and more details    I d start with ClosedXML

User · Answer

This line works only for the DataGridView Control on Windows Forms   DataObject dataObj   dataGridView1 GetClipboardContent      This one addresses the same issue  but for the DataGrid control for the WPF Framework       private void copyDataGridContentToClipboard                 datagridGrupeProductie SelectAll            datagridGrupeProductie ClipboardCopyMode   DataGridClipboardCopyMode IncludeHeader           ApplicationCommands Copy Execute null  datagridGrupeProductie           datagridGrupeProductie UnselectAll                private void rightClickGrupeProductie Click object sender  RoutedEventArgs e                copyDataGridContentToClipboard            Microsoft Office Interop Excel Application excelApp          Microsoft Office Interop Excel Workbook excelWkbk          Microsoft Office Interop Excel Worksheet excelWksht          object misValue   System Reflection Missing Value          excelApp   new Microsoft Office Interop Excel Application            excelApp Visible   true          excelWkbk   excelApp Workbooks Add misValue           excelWksht    Microsoft Office Interop Excel Worksheet excelWkbk Worksheets get Item 1           Microsoft Office Interop Excel Range CR    Microsoft Office Interop Excel Range excelWksht Cells 1  1           CR Select            excelWksht PasteSpecial CR  Type Missing  Type Missing  Type Missing  Type Missing  Type Missing  true

User · Answer

using Excel   Microsoft Office Interop Excel    private void btnExportExcel Click object sender  EventArgs e        try               Microsoft Office Interop Excel Application excel   new Microsoft Office Interop Excel Application            excel Visible   true          Microsoft Office Interop Excel Workbook workbook   excel Workbooks Add System Reflection Missing Value           Microsoft Office Interop Excel Worksheet sheet1    Microsoft Office Interop Excel Worksheet workbook Sheets 1           int StartCol   1          int StartRow   1          int j   0  i   0             Write Headers         for  j   0  j  lt  dgvSource Columns Count  j                          Microsoft Office Interop Excel Range myRange    Microsoft Office Interop Excel Range sheet1 Cells StartRow  StartCol   j               myRange Value2   dgvSource Columns j  HeaderText                     StartRow               Write datagridview content         for  i   0  i  lt  dgvSource Rows Count  i                          for  j   0  j  lt  dgvSource Columns Count  j                                  try                                       Microsoft Office Interop Excel Range myRange    Microsoft Office Interop Excel Range sheet1 Cells StartRow   i  StartCol   j                       myRange Value2   dgvSource j  i  Value    null        dgvSource j  i  Value                                    catch                                                                                             catch  Exception ex                MessageBox Show ex ToString

User · Answer

alternatively you can perform a fast export without using Office dll  as Excel can parse csv files without problems   Doing something like this  for less than 65 536 rows with titles      Try              If  p oGrid RowCount   0  Then                 MsgBox  No data   MsgBoxStyle Information   App                   Exit Sub             End If              Cursor Current   Cursors WaitCursor              Dim sText As New System Text StringBuilder             Dim sTmp As String             Dim aVisibleData As New List Of String               For iAuxRow As Integer   0 To p oGrid Columns Count - 1                 If p oGrid Columns iAuxRow  Visible Then                     aVisibleData Add p oGrid Columns iAuxRow  Name                      sText Append p oGrid Columns iAuxRow  HeaderText ToUpper                      sText Append                      End If             Next             sText AppendLine                For iAuxRow As Integer   0 To p oGrid RowCount - 1                 Dim oRow As DataGridViewRow   p oGrid Rows iAuxRow                  For Each sCol As String In aVisibleData                     Dim sVal As String                     sVal   oRow Cells sCol  Value ToString                       sText Append sVal Replace           Replace vbCrLf                              sText Append                      Next                 sText AppendLine               Next              sTmp   IO Path GetTempFileName  amp    csv              IO File WriteAllText sTmp  sText ToString  System Text Encoding UTF8              sText   Nothing              Process Start sTmp           Catch ex As Exception             process error ex          Finally             Cursor Current   Cursors Default         End Try

User · Answer

This answer is for the first question  why it takes so much time and it offers an alternative solution for exporting the DataGridView to Excel    MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side and cannot be use to export large Excel files  For more details see why not to use OLE Automation from Microsoft point of view   Interop saves Excel files in XLS file format  old Excel 97-2003 file format  and the support for Office 2003 has ended  Microsoft Excel released XLSX file format with Office 2007 and recommends the usage of OpenXML SDK instead of Interop  But XLSX files are not really so fast and doesn   t handle very well large Excel files because they are based on XML file format  This is why Microsoft also released XLSB file format with Office 2007  file format that is recommended for large Excel files  It is a binary format  So the best and fastest solution is to save XLSB files   You can use this C  Excel library to save XLSB files  but it also supports XLS and XLSX file formats    See the following code sample as alternative of exporting DataGridView to Excel      Create a DataSet and add the DataTable of DataGridView  DataSet dataSet   new DataSet    dataSet Tables Add  DataTable dataGridView     or   DataTable dataGridView DataSource  Copy   to create a copy     Export Excel file  ExcelDocument workbook   new ExcelDocument    workbook easy WriteXLSBFile FromDataSet filePath  dataSet        new EasyXLS ExcelAutoFormat EasyXLS Constants Styles AUTOFORMAT EASYXLS1          Sheet1      If you also need to export the formatting of the DataGridView check this code sample on how to export datagridview to Excel in C

User · Answer

This is a great question and I was surprised at how difficult it was to find a clear and complete answer  most of the answers I found were either sudo-code or not 100  complete   I was able to create a complete solution to copy and save the data from my DataGridView to an excel file based on Jake s answer so I m posting my complete solution in the hopes that it can help other new comers to c  like myself     First off  you will need the Microsoft Office Interop Excel reference in your project  See MSDN on how to add it   My Code   using Excel   Microsoft Office Interop Excel   private void btnExportToExcel Click object sender  EventArgs e        SaveFileDialog sfd   new SaveFileDialog        sfd Filter    Excel Documents    xls    xls       sfd FileName    Inventory Adjustment Export xls       if  sfd ShowDialog      DialogResult OK                   Copy DataGridView results to clipboard         copyAlltoClipboard             object misValue   System Reflection Missing Value          Excel Application xlexcel   new Excel Application             xlexcel DisplayAlerts   false     Without this you will get two confirm overwrite prompts         Excel Workbook xlWorkBook   xlexcel Workbooks Add misValue           Excel Worksheet xlWorkSheet    Excel Worksheet xlWorkBook Worksheets get Item 1               Format column D as text before pasting results  this was required for my data         Excel Range rng   xlWorkSheet get Range  D D   Cells          rng NumberFormat                    Paste clipboard results to worksheet range         Excel Range CR    Excel Range xlWorkSheet Cells 1  1           CR Select            xlWorkSheet PasteSpecial CR  Type Missing  Type Missing  Type Missing  Type Missing  Type Missing  true               For some reason column A is always blank in the worksheet                         Delete blank column A and select cell A1         Excel Range delRng   xlWorkSheet get Range  A A   Cells          delRng Delete Type Missing           xlWorkSheet get Range  A1   Select                Save the excel file under the captured location from the SaveFileDialog         xlWorkBook SaveAs sfd FileName  Excel XlFileFormat xlWorkbookNormal  misValue  misValue  misValue  misValue  Excel XlSaveAsAccessMode xlExclusive  misValue  misValue  misValue  misValue  misValue           xlexcel DisplayAlerts   true          xlWorkBook Close true  misValue  misValue           xlexcel Quit             releaseObject xlWorkSheet           releaseObject xlWorkBook           releaseObject xlexcel               Clear Clipboard and DataGridView selection         Clipboard Clear            dgvItems ClearSelection                Open the newly saved excel file         if  File Exists sfd FileName               System Diagnostics Process Start sfd FileName            private void copyAlltoClipboard         dgvItems SelectAll        DataObject dataObj   dgvItems GetClipboardContent        if  dataObj    null          Clipboard SetDataObject dataObj      private void releaseObject object obj        try               System Runtime InteropServices Marshal ReleaseComObject obj           obj   null            catch  Exception ex                obj   null          MessageBox Show  Exception Occurred while releasing object     ex ToString               finally               GC Collect

User · Answer

I solved this by simple copy and paste method  I don t know it is the best way to do this but for me it works good and almost instantaneously  Here is my code       private void copyAlltoClipboard                 dataGridView1 SelectAll            DataObject dataObj   dataGridView1 GetClipboardContent            if  dataObj    null              Clipboard SetDataObject dataObj             private void button3 Click 1 object sender  EventArgs e                copyAlltoClipboard            Microsoft Office Interop Excel Application xlexcel          Microsoft Office Interop Excel Workbook xlWorkBook          Microsoft Office Interop Excel Worksheet xlWorkSheet          object misValue   System Reflection Missing Value          xlexcel   new Excel Application            xlexcel Visible   true          xlWorkBook   xlexcel Workbooks Add misValue           xlWorkSheet    Excel Worksheet xlWorkBook Worksheets get Item 1           Excel Range CR    Excel Range xlWorkSheet Cells 1  1           CR Select            xlWorkSheet PasteSpecial CR  Type Missing  Type Missing  Type Missing  Type Missing  Type Missing  true                     Thanks

User · Answer

I m adding this answer because none of the other methods use OpenXMLWriter  despite the fact that it is faster than OpenXML DOM and both faster and more reliable than COM Interop  and because some of the other methods use the clipboard  which I would caution against  as it s output is unreliable   The details can be found in my answer in the link below  however that example is for a DataTable  but you can adapt it to using a DataGridView by just changing the row column loops to reference a dgv instead of a dt   How to export DataTable to Excel

User · Answer

The Best is use use closedxml codeplex com Library Refer it  https   closedxml codeplex com wikipage title Adding 20DataTable 20as 20Worksheet amp referringTitle Documentation  var wb   new ClosedXML Excel XLWorkbook    DataTable dt   GetTheDataTable     Refer documentation   wb Worksheets Add dt    Response Clear    Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet   Response AddHeader  content-disposition    attachment filename   FileName xlsx       using  var ms   new System IO MemoryStream          wb SaveAs ms       ms WriteTo Response OutputStream       ms Close       Response End

User · Answer

I did not intend to steal  Jake and  Cornelius s answer  so i tried editing it  but it was rejected   Anyways  the only improvement I have to point out is about avoiding extra blank column in excel after paste  Adding one line dataGridView1 RowHeadersVisible   false  hides so called  Row Header  which appears on the left most part of DataGridView  and so it is not selected and copied to clipboard when you do dataGridView1 SelectAll     private void copyAlltoClipboard                   to remove the first blank column from datagridview         dataGridView1 RowHeadersVisible   false          dataGridView1 SelectAll            DataObject dataObj   dataGridView1 GetClipboardContent            if  dataObj    null              Clipboard SetDataObject dataObj             private void button3 Click 1 object sender  EventArgs e                copyAlltoClipboard            Microsoft Office Interop Excel Application xlexcel          Microsoft Office Interop Excel Workbook xlWorkBook          Microsoft Office Interop Excel Worksheet xlWorkSheet          object misValue   System Reflection Missing Value          xlexcel   new Excel Application            xlexcel Visible   true          xlWorkBook   xlexcel Workbooks Add misValue           xlWorkSheet    Excel Worksheet xlWorkBook Worksheets get Item 1           Excel Range CR    Excel Range xlWorkSheet Cells 1  1           CR Select            xlWorkSheet PasteSpecial CR  Type Missing  Type Missing  Type Missing  Type Missing  Type Missing  true

User · Answer

that s what i use for my gridview   try to use it for yr data   it works perfectly              GridView1 AllowPaging   false          GridView1 DataBind             StringBuilder sb   new StringBuilder             for  int k   0  k  lt  GridView1 Columns Count  k                            add separator             sb Append GridView1 Columns k  HeaderText                              append new line         sb Append   r n            for  int i   0  i  lt  GridView1 Rows Count  i                          for  int k   0  k  lt  GridView1 Columns Count  k                                  sb Append GridView1 Rows i  Cells k  Text                                 sb AppendLine

User · Answer

If your DataGridView s RightToLeft set to Yes then your data copy reversely  So you should use the below code to copy the data correctly     private void copyAlltoClipboard         dgvItems RightToLeft   RightToLeft No      dgvItems SelectAll        DataObject dataObj   dgvItems GetClipboardContent        if  dataObj    null          Clipboard SetDataObject dataObj       dgvItems RightToLeft   RightToLeft Yes

[c#] How to export dataGridView data Instantly to Excel on button click?

Examples related to c#

Examples related to datagridview

Examples related to copy

Examples related to export-to-excel

Examples related to export-to-csv