How to export datagridview to excel using vb net

Question

I have a datagridview in vb net that is filled up from the database  I ve researched and I found out that there is no built in support to print directly from datagridview  I don t want to use crystal report because I m not familiar with it   I m planning to export it to excel to enable me to generate report from the datagridview   Can you provide me ways to do this

User · Answer

Excel Method    This method is different than many you will see  Others use a loop to write each cell and write the cells with text data type     This method creates an object array from a DataTable or DataGridView and then writes the array to Excel  This means I can write to Excel without a loop and retain data types     I extracted this from my library and I think I changed it enough to work with this code only  but more minor tweaking might be necessary  If you get errors just let me know and I ll correct them for you  Normally  I create an instance of my class and call these methods  If you would like to use my library then use this link to download it and if you need help just let me know  https   zomp co Files aspx ID zExcel    After copying the code to your solution you will use it like this     In your button code add this and change the names to your controls     WriteDataGrid  Sheet1   grid   To open your file after exporting use this line    System Diagnostics Process Start  The location and filename of your file    In the WriteArray method you ll want to change the line that saves the workbook to where you want to save it  Probably makes sense to add this as a parameter     wb SaveAs  C  MyWorkbook xlsx      Public Function WriteArray Sheet As String  ByRef ObjectArray As Object     As String     Try         Dim xl As Excel Application   New Excel Application         Dim wb As Excel Workbook   xl Workbooks Add           Dim ws As Excel Worksheet   wb Worksheets Add           ws Name   Sheet         Dim range As Excel Range   ws Range  A1   Resize ObjectArray GetLength 0   ObjectArray GetLength 1           range Value   ObjectArray          range   ws Range  A1   Resize 1  ObjectArray GetLength 1  - 1           range Interior Color   RGB 0  70  132    Con-way Blue         range Font Color   RGB Drawing Color White R  Drawing Color White G  Drawing Color White B          range Font Bold   True         range WrapText   True          range HorizontalAlignment   Excel XlHAlign xlHAlignCenter         range VerticalAlignment   Excel XlVAlign xlVAlignCenter          range Application ActiveWindow SplitColumn   0         range Application ActiveWindow SplitRow   1         range Application ActiveWindow FreezePanes   True          wb SaveAs  C  MyWorkbook xlsx           wb CLose           xl Quit           xl   Nothing         wb   Nothing         ws    Nothing         range   Nothing         ReleaseComObject xl          ReleaseComObject wb          ReleaseComObject ws          ReleaseComObject range           Return        Catch ex As Exception         Return  WriteArray     amp  Environment NewLine  amp  Environment NewLine  amp  ex Message     End Try End Function  Public Function WriteDataGrid SheetName As String  ByRef dt As DataGridView  As String         Try             Dim l dt Rows Count   1  dt Columns Count  As Object             For c As Integer   0 To dt Columns Count - 1                 l 0  c    dt Columns c  HeaderText             Next              For r As Integer   1 To dt Rows Count                 For c As Integer   0 To dt Columns Count - 1                     l r  c    dt Rows r - 1  Cells c                  Next             Next              Dim errors As String   WriteArray SheetName  l              If errors  lt  gt     Then                 Return errors             End If              Return            Catch ex As Exception             Return  WriteDataGrid     amp  Environment NewLine  amp  Environment NewLine  amp  ex Message         End Try     End Function    Public Function WriteDataTable SheetName As String  ByRef dt As DataTable  As String         Try             Dim l dt Rows Count   1  dt Columns Count  As Object             For c As Integer   0 To dt Columns Count - 1                 l 0  c    dt Columns c  ColumnName             Next              For r As Integer   1 To dt Rows Count                 For c As Integer   0 To dt Columns Count - 1                     l r  c    dt Rows r - 1  Item c                  Next             Next              Dim errors As String   WriteArray SheetName  l              If errors  lt  gt     Then                 Return errors             End If              Return            Catch ex As Exception             Return  WriteDataTable     amp  Environment NewLine  amp  Environment NewLine  amp  ex Message         End Try     End Function     I actually don t use this method in my Database program because it s a slow method when you have a lot of rows columns  I instead create a CSV from the DataGridView  Writing to Excel with Excel Automation is only useful if you need to format the data and cells otherwise you should use CSV  You can use the code after the image for CSV export       CSV Method    Private Sub DataGridToCSV ByRef dt As DataGridView  Qualifier As String            Dim TempDirectory As String    A temp Directory            System IO Directory CreateDirectory TempDirectory          Dim oWrite As System IO StreamWriter         Dim file As String   System IO Path GetRandomFileName  amp    csv          oWrite   IO File CreateText TempDirectory  amp       amp  file           Dim CSV As StringBuilder   New StringBuilder            Dim i As Integer   1         Dim CSVHeader As StringBuilder   New StringBuilder           For Each c As DataGridViewColumn In dt Columns             If i   1 Then                 CSVHeader Append Qualifier  amp  c HeaderText ToString    amp  Qualifier              Else                 CSVHeader Append      amp  Qualifier  amp  c HeaderText ToString    amp  Qualifier              End If             i    1         Next           CSV AppendLine CSVHeader ToString            oWrite WriteLine CSVHeader ToString            oWrite Flush            For r As Integer   0 To dt Rows Count - 1              Dim CSVLine As StringBuilder   New StringBuilder               Dim s As String                  For c As Integer   0 To dt Columns Count - 1                 If c   0 Then                      CSVLine Append Qualifier  amp  gridResults Rows r  Cells c  Value ToString    amp  Qualifier                      s   s  amp  Qualifier  amp  gridResults Rows r  Cells c  Value ToString    amp  Qualifier                 Else                      CSVLine Append      amp  Qualifier  amp  gridResults Rows r  Cells c  Value ToString    amp  Qualifier                      s   s  amp       amp  Qualifier  amp  gridResults Rows r  Cells c  Value ToString    amp  Qualifier                 End If              Next             oWrite WriteLine s              oWrite Flush                CSV AppendLine CSVLine ToString                 CSVLine Clear           Next           oWrite Write CSV ToString             oWrite Close           oWrite   Nothing              System Diagnostics Process Start TempDirectory  amp       amp  file              GC Collect        End Sub

User · Answer

Private Sub Button1 Click ByVal sender As System Object  ByVal e As System EventArgs  Handles Button1 Click     DATAGRIDVIEW TO EXCEL  DataGridView1     PARAMETER  YOUR DATAGRIDVIEW End Sub  Private Sub DATAGRIDVIEW TO EXCEL ByVal DGV As DataGridView      Try         Dim DTB   New DataTable  RWS As Integer  CLS As Integer          For CLS   0 To DGV ColumnCount - 1   COLUMNS OF DTB             DTB Columns Add DGV Columns CLS  Name ToString          Next          Dim DRW As DataRow          For RWS   0 To DGV Rows Count - 1   FILL DTB WITH DATAGRIDVIEW             DRW   DTB NewRow              For CLS   0 To DGV ColumnCount - 1                 Try                     DRW DTB Columns CLS  ColumnName ToString    DGV Rows RWS  Cells CLS  Value ToString                 Catch ex As Exception                  End Try             Next              DTB Rows Add DRW          Next          DTB AcceptChanges            Dim DST As New DataSet         DST Tables Add DTB          Dim FLE As String        PATH AND FILE NAME WHERE THE XML WIL BE CREATED  EXEMPLE  C  REPS XML xml          DTB WriteXml FLE          Dim EXL As String        PATH OF  EXCEL EXE IN YOUR MICROSOFT OFFICE         Shell Chr 34   amp  EXL  amp  Chr 34   amp       amp  Chr 34   amp  FLE  amp  Chr 34   vbNormalFocus    OPEN XML WITH EXCEL      Catch ex As Exception         MsgBox ex ToString      End Try  End Sub

User · Answer

Code below creates Excel File and saves it in D  drive It uses Microsoft office 2007   FIRST ADD REFERRANCE  Microsoft office 12 0 object library   to your project  Then Add code given bellow to the Export button click event-  Private Sub Export Button Click ByVal sender As System Object  ByVal e As  System EventArgs  Handles VIEW Button Click      Dim xlApp As Microsoft Office Interop Excel Application     Dim xlWorkBook As Microsoft Office Interop Excel Workbook     Dim xlWorkSheet As Microsoft Office Interop Excel Worksheet     Dim misValue As Object   System Reflection Missing Value     Dim i As Integer     Dim j As Integer      xlApp   New Microsoft Office Interop Excel ApplicationClass     xlWorkBook   xlApp Workbooks Add misValue      xlWorkSheet   xlWorkBook Sheets  sheet1         For i   0 To DataGridView1 RowCount - 2         For j   0 To DataGridView1 ColumnCount - 1             For k As Integer   1 To DataGridView1 Columns Count                 xlWorkSheet Cells 1  k    DataGridView1 Columns k - 1  HeaderText                 xlWorkSheet Cells i   2  j   1    DataGridView1 j  i  Value ToString               Next         Next     Next      xlWorkSheet SaveAs  D  vbexcel xlsx       xlWorkBook Close       xlApp Quit        releaseObject xlApp      releaseObject xlWorkBook      releaseObject xlWorkSheet       MsgBox  You can find the file D  vbexcel xlsx   End Sub  Private Sub releaseObject ByVal obj As Object      Try         System Runtime InteropServices Marshal ReleaseComObject obj          obj   Nothing     Catch ex As Exception         obj   Nothing     Finally         GC Collect       End Try End Sub

User · Answer

another easy way and more flexible   after loading data into Datagrid Private Sub Button Export Click sender As Object  e As EventArgs  Handles Button Export Click     Dim file As System IO StreamWriter     file   My Computer FileSystem OpenTextFileWriter  quot c  1 Myfile csv quot   True      If DataGridView1 Rows Count   0 Then GoTo loopend        collect the header s names      Dim Headerline As String     For k   0 To DataGridView1 Columns Count - 1         If k   DataGridView1 Columns Count - 1 Then   last column dont put   separate              Headerline   Headerline  amp  DataGridView1 Columns k  HeaderText         Else             Headerline   Headerline  amp  DataGridView1 Columns k  HeaderText  amp   quot   quot          End If     Next     file WriteLine Headerline     this will write header names at the first line        collect the data       For i   0 To DataGridView1 Rows Count - 1         Dim DataRow As String         For k   0 To DataGridView1 Columns Count - 1             If k   DataGridView1 Columns Count - 1 Then                 DataRow   DataRow  amp  DataGridView1 Rows i  Cells k  Value   last column dont put   separate              End If             DataRow   DataRow  amp  DataGridView1 Rows i  Cells k  Value  amp   quot   quot          Next          file WriteLine DataRow          DataRow    quot  quot      Next    loopend          file Close   End Sub

User · Answer

Regarding your need to  print directly from datagridview   check out this article on CodeProject   The DataGridViewPrinter Class  There are a number of similar articles but I ve had luck with the one I linked

User · Answer

In design mode  Set DataGridView1 ClipboardCopyMode properties to EnableAlwaysIncludeHeaderText  or on the program code  DataGridView1 ClipboardCopyMode   DataGridViewClipboardCopyMode EnableAlwaysIncludeHeaderText   In the run time select all cells content  Ctrl A  and copy  Ctrl C  and paste to the Excel Program  Let the Excel do the rest  Sorry for the inconvenient  I have been searching the method to print data directly from the datagridvew  create report from vb net VB2012  and have not found the satisfaction result   Above code just my though  wondering if my applications user can rely on above simple step it will be nice and  I could go ahead to next step on my program progress

User · Answer

Dim rowNo1 As Short         Dim numrow As Short         Dim colNo1 As Short         Dim colNo2 As Short      rowNo1   1     colNo1   1     colNo2   1     numrow   1      ObjEXCEL   CType CreateObject  Excel Application    Microsoft Office Interop Excel Application      objEXCELBook   CType ObjEXCEL Workbooks Add  Microsoft Office Interop Excel Workbook      objEXCELSheet   CType objEXCELBook Worksheets 1   Microsoft Office Interop Excel Worksheet       ObjEXCEL Visible   True      For numCounter   0 To grdName Columns Count - 1           MsgBox grdName Columns numCounter  HeaderText            If grdName Columns numCounter  Width  gt  0 Then             ObjEXCEL Cells 1  numCounter   1    grdName Columns numCounter  HeaderText           End If           ObjEXCEL Cells 1  numCounter   1    grdName Columns GetFirstColumn DataGridViewElementStates Displayed      Next numCounter      ObjEXCEL Range  A A   ColumnWidth   10     ObjEXCEL Range  B B   ColumnWidth   25     ObjEXCEL Range  C C   ColumnWidth   20     ObjEXCEL Range  D D   ColumnWidth   20     ObjEXCEL Range  E E   ColumnWidth   20     ObjEXCEL Range  F F   ColumnWidth   25      For rowNo1   0 To grdName RowCount - 1         For colNo1   0 To grdName ColumnCount - 1             If grdName Columns colNo1  Width  gt  0 Then                 If Trim grdName Item colNo1  rowNo1  Value   lt  gt     Then                      If IsDate grdName Item colNo1  rowNo1  Value    True Then                          ObjEXCEL Cells numrow   1  colNo2    Format CDate grdName Item colNo1  rowNo1  Value    dd MMM yyyy                        Else                     ObjEXCEL Cells numrow   1  colNo2    grdName Item colNo1  rowNo1  Value                      End If                 End If                 If colNo2  gt   grdName ColumnCount Then                     colNo2   1                 Else                     colNo2   colNo2   1                 End If             End If         Next colNo1         numrow   numrow   1     Next rowNo1

User · Answer

The following code works fine for me      Protected Sub ExportToExcel sender As Object  e As EventArgs  Handles ExportExcel Click         Try             Response Clear               Response Buffer   True             Response AddHeader  content-disposition    attachment filename ExportEthias xls               Response Charset                  Response ContentType    application vnd ms-excel              Using sw As New StringWriter                   Dim hw As New HtmlTextWriter sw                  GvActifs RenderControl hw                   Le format de base est le texte pour   viter les probl  mes d arrondis des nombres                 Dim style As String     lt style gt   textmode      lt  style gt                   Response Write Style                  Response Output Write sw ToString                    Response Flush                   Response End               End Using         Catch ex As Exception             lblMessage Text    Erreur export Excel      amp  ex Message         End Try     End Sub     Public Overrides Sub VerifyRenderingInServerForm control As Control            Verifies that the control is rendered     End Sub   Hopes this help you

User · Answer

A simple way of generating a printable report from a Datagridview is to place the datagridview on a Panel object  It is possible to draw a bitmap of the panel   Here s how I do it    create the bitmap with the dimentions of the Panel Dim bmp As New Bitmap Panel1 Width  Panel1 Height    draw the Panel to the bitmap  bmp  Panel1 DrawToBitmap bmp  Panel1 ClientRectangle   I create a multi page tiff by  breaking my datagridview items into pages  this is how i detect the start of a new page    i add the rows to my datagrid one at a time and then check if the scrollbar is active   if the scrollbar is active i save the row to a variable and then i remove it from the  datagridview and roll back my counter integer by one thus the next run will include this  row   Private Function VScrollBarVisible   As Boolean     Dim ctrl As New Control     For Each ctrl In DataGridView Results Controls         If ctrl GetType   Is GetType VScrollBar  Then             If ctrl Visible   True Then                 Return True             Else                 Return False             End If         End If     Next     Return Nothing End Function   I hope this helps

[vb.net] How to export datagridview to excel using vb.net?

Examples related to vb.net

Examples related to excel

Examples related to datagridview

Examples related to printing