Best method for exporting a DataGridView to Excel

Best method for exporting a DataGridView to Excel

• Murdoch

  Im going straight into excel with the following code

  of course, it doesn't seem to want to let me put the header text in. When it starts processing the rows it simply starts at row 1 (and i understand why)

  how do i tell it that even though i want to parse the datagrid from rowindex 0, i want to insert it on the spreadsheet starting at row 2

  Dim wapp As Microsoft.Office.Interop.Excel.Application

  Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet

  Dim wbook As Microsoft.Office.Interop.Excel.Workbook

  wapp = New Microsoft.Office.Interop.Excel.Application

  wapp.Visible = True

  wbook = wapp.Workbooks.Add()

  wsheet = wbook.ActiveSheet

  Dim iX As Integer

  Dim iY As Integer

  Dim iC As Integer

  For iC = 0 To DataGridView1.Columns.Count - 1

  wsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText

  wsheet.Cells(1, iC + 1).font.bold = True

  Next

  wsheet.Rows(2).select()

  For iX = 0 To DataGridView1.Rows.Count - 1

  For iY = 0 To DataGridView1.Columns.Count - 1

  wsheet.Cells(iX + 1, iY + 1).value = DataGridView1(iY, iX).Value.ToString

  Next

  Next

  wapp.Visible = True

  wapp.UserControl = True

  
  

• Jim Cogswell

  Hello,

  I assume u r using VB. If it is in .NET environment, make sure you installed Office application programming with DotNET.

  In your program add reference to Microsoft.Excel.

  As i have done similar programing using Excel without DotNET i'm giving you same idea. You harvest the same in DotNET.

  In VB6 with Excel 2000 TLB file referenced, CreateObject(Excel.Application). This will create Excel object in memory. Your datagrid has data. This is nothing but a dataset or in earlier form of ADO a recordset with UI. Create new Sheet in memory of the excel object and set the recordset property of the sheet to the recordset of the data you have. Before doing that set the Redraw and Refresh and Visible properties of the Excel object to false. After setting the recordset object, set those properties to true. Using VB6 and Excel97/2000 i have created complex excel files with very large data of above 120 columns and more than 100,000 rows in few seconds. Of course your PC performance counts here.

  This programming requires expertise in Office Automation.

  Good luck.

  
  

• Binju Paul

  Here's a tip on manually creating XL files that is frequently overlooked. If you create a simple tab delimited text file (as opposed to a comma delimited text file) and change the file extension to xls, Excel will open the file as if it's a full blown spreadsheet (it won't prompt you to do any conversions).

  
  

• antonioa - msft

  Is there any way to export with some code changes to word format - *.doc
  
  

  Dim wapp As Microsoft.Office.Interop.Excel.Application

  Dim wsheet As Microsoft.Office.Interop.Excel.Worksheet

  Dim wbook As Microsoft.Office.Interop.Excel.Workbook

  wapp = New Microsoft.Office.Interop.Excel.Application

  wapp.Visible = True

  wbook = wapp.Workbooks.Add()

  wsheet = wbook.ActiveSheet

  Dim iX As Integer

  Dim iY As Integer

  Dim iC As Integer

  For iC = 0 To DataGridView1.Columns.Count - 1

  wsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText

  wsheet.Cells(1, iC + 1).font.bold = True

  Next

  wsheet.Rows(2).select()

  For iX = 0 To DataGridView1.Rows.Count - 1

  For iY = 0 To DataGridView1.Columns.Count - 1

  wsheet.Cells(iX + 1, iY + 1).value = DataGridView1(iY, iX).Value.ToString

  Next

  Next

  And how to do it Can anyone of you guys help
  
  

• Johnny P

  Private Function CreateRecordsetFromDataTable(ByVal DT As DataTable) As ADODB.Recordset

  Duracellko

  Well done, that was some good thinking. Also with a little tinkering you can use it to export DataGridViews directly to excel.

  Thanks for that piece of code

  Ger

  
  
  

• Philski

  Heh,

   i actually did a test last night on a "basic" csv file and it went straight in. I must have stuffed up my previous test.

  Thanks for that Dave. Tis appreciated

  Does anyone know any ways of being able to format excel when using csv files i'm not concerned about row colouring and the like, just formatting columns to the right text/general/number type etc.

  Excel has a nasty habit of removing leading 0's and  general screwing up large numbers. I don't want the end user to have to fix it each time

   

  Just trying this out though... The DataGrid can have a varying number of columns.

  It order to parse through and write to a csv, i need to know how many columns i have..

  Im trying the below code, but it doesn't seem to work. Anyone have any thoughts

  Try

  FileOpen(9, "f:\csvdump.csv", OpenMode.Output)

  Dim iX As Integer

  Dim sY As String

  Dim CCM As Integer = DataGridView1.Columns.Count

  Dim CC As Integer = 0

  For iX = 0 To DataGridView1.Rows.Count - 1

  Do Until CC = CCM

  sY = DataGridView1(CC, iX).Value.ToString & ","

  Print(9, sY)

  CC = CC + 1

  Loop

  Next

  FileClose(9)

  Catch ex As Exception

  MsgBox(ex.Message)

  End Try

   

  Doh i've inserted my Brain and it seemed to help me !

  Try

  FileOpen(9, "f:\csvdump.csv", OpenMode.Output)

  Dim iX As Integer

  Dim iY As Integer

  Dim sY As String

  For iX = 0 To DataGridView1.Rows.Count - 1

  For iY = 0 To DataGridView1.Columns.Count - 1

  sY = DataGridView1(iY, iX).Value.ToString & ","

  Print(9, sY)

  Next

  Print(9, vbCr)

  Next

  FileClose(9)

  Catch ex As Exception

  MsgBox(ex.Message)

  End Try

   

  
  

• Davide Rinaldi

  And Very simply, you do it like the following:

  wsheet.Cells(iX + 2, iY + 1).value = DataGridView1(iY, iX).Value.ToString

  Next

  tell it to add 2 to datagridview row index and then sart from there..

  about 30 seconds between "how do i" and "oh i did it"

  
  

• Mordy

  Why i'm getting following error: Type 'Microsoft.Office.Interop.Excel.Application' is not defined. Must i import some library Please help!
  
  
  

• CSHALL

  Hmmmm. Maybe "load straight into" was a bit of an exageration then.

  I haven't used Excel for a couple of years and then it was only '97 which certainly loads a csv file without asking any questions. It seems a shame if that has been lost in more recent versions.

  I doubt if you had the format wrong. For the test I simply looped through the grid putting a comma after each cell entry and a new line at the end of each row, e.g.

  FileOpen(1, "csvtest.csv", OpenMode.Output)

  Dim iX As Integer

  Dim sY As String

  For iX = 0 To DG2.RowCount - 1

  sY = DG2(0, iX).Value.ToString & "," & _

  DG2(1, iX).Value.ToString & "," & _

  DG2(2, iX).Value.ToString & "," & _

  ........................etc

  Print(1, sY)

  Next

  FileClose(1)

  On my grid that produces a 500k, 10,000 line file in about a second.

  I can't help further with the formatting I'm afraid, although I'm sure there are plenty of people around here who will be able to.

  Dave

  
  

• Arto

  I'd thought about that also although i wanted to do some formatting prior to the end user actually seeing.

  I've actually had problems with opening files directly - excel seems to want to run through the "import external data" process. I imagine im not formatting the file correctly

  Do you have any suggestions or a brief code example for this

  Cheers

  Mc

  
  

• Limmer

  I noticed that using OLE Automation from VB.NET is slower than using it from VB6. I tried Office XP PIA (Primary Interop Assemblies) downloaded from http://www.microsoft.com/downloads/details.aspx FamilyID=c41bd61e-3060-4f71-a6b4-01feba508e52&DisplayLang=en

  I think it is so slow because of switching between managed (your application) and non-managed (MS Excel) code. I solved this problem by using Range.CopyFromRecordset. Office XP PIA includes PIA for ADODB. So I include reference to this assembly in my project. Then I created function that creates ADODB.Recordset object from DataTable.

  This function can look like this:

  Private Function CreateRecordsetFromDataTable(ByVal DT As DataTable) As ADODB.Recordset

  Dim rs As New ADODB.Recordset

  'Create columns in ADODB.Recordset

  Dim FieldAttr As ADODB.FieldAttributeEnum

  FieldAttr = ADODB.FieldAttributeEnum.adFldIsNullable Or ADODB.FieldAttributeEnum.adFldIsNullable Or ADODB.FieldAttributeEnum.adFldUpdatable

  For Each iColumn As DataColumn In DT.Columns

  Dim FieldType As ADODB.DataTypeEnum

  If iColumn.DataType Is GetType(System.Boolean) Then

  FieldType = ADODB.DataTypeEnum.adBoolean

  ElseIf iColumn.DataType Is GetType(System.Byte) Then

  FieldType = ADODB.DataTypeEnum.adTinyInt

  ElseIf iColumn.DataType Is GetType(System.Int16) Then

  FieldType = ADODB.DataTypeEnum.adSmallInt

  ElseIf iColumn.DataType Is GetType(System.Int32) Then

  FieldType = ADODB.DataTypeEnum.adInteger

  ElseIf iColumn.DataType Is GetType(System.Int64) Then

  FieldType = ADODB.DataTypeEnum.adBigInt

  ElseIf iColumn.DataType Is GetType(System.Single) Then

  FieldType = ADODB.DataTypeEnum.adSingle

  ElseIf iColumn.DataType Is GetType(System.Double) Then

  FieldType = ADODB.DataTypeEnum.adDouble

  ElseIf iColumn.DataType Is GetType(System.Decimal) Then

  FieldType = ADODB.DataTypeEnum.adCurrency

  ElseIf iColumn.DataType Is GetType(System.DateTime) Then

  FieldType = ADODB.DataTypeEnum.adDBDate

  ElseIf iColumn.DataType Is GetType(System.Char) Then

  FieldType = ADODB.DataTypeEnum.adChar

  ElseIf iColumn.DataType Is GetType(System.String) Then

  FieldType = ADODB.DataTypeEnum.adVarWChar

  End If

  If FieldType = ADODB.DataTypeEnum.adVarWChar Then

  rs.Fields.Append(iColumn.ColumnName, FieldType, 4000)

  Else

  rs.Fields.Append(iColumn.ColumnName, FieldType)

  End If

  rs.Fields(icolumn.ColumnName).Attributes = FieldAttr

  Next

  'Opens the ADODB.Recordset

  rs.Open()

  'Inserts rows into the recordset

  For Each iRow As DataRow In DT.Rows

  rs.AddNew()

  For Each iColumn As DataColumn In DT.Columns

  If iRow.IsNull(icolumn) Then

  If (rs(icolumn.ColumnName).Attributes And ADODB.FieldAttributeEnum.adFldIsNullable) <> 0 Then

  rs(icolumn.ColumnName).Value = DBNull.Value

  End If

  Else

  rs(icolumn.ColumnName).Value = iRow.Item(icolumn)

  End If

  Next

  Next

  'Moves to the first record in recordset

  If Not rs.BOF Then rs.MoveFirst()

  Return rs

  End Function

  Then it is possible to insert this recordset into Excel by usin Range.CopyFromRecordset method

  
  
  

• mookiebud

  Interest Tab delimiter tip there. I'll give that a try one day!

  RRV,

  Do you have any sample code for doing this Specifically the setting of the record set etc. I'd be very interested in getting that sort of export to work. I'm especially concerned about the formatting for the output (i want the end user to do as little as possible)

  The Next stage of my app will involved directly initiating a mail merge process for the data in the datagridview. This is a way off yet i imagine

  ta

  Mc

  
  

• Cha Chunchadatharn

  Download Office XP PIA library from http://www.microsoft.com/downloads/details.aspx FamilyID=c41bd61e-3060-4f71-a6b4-01feba508e52&DisplayLang=en

  Then add reference to the library by Add Reference dialog on Browse tab.

  Or install them by using .reg files included in the downloaded file.

  
  
  

• mikeisaac

  If you just want the data then don't overlook the simplicity of using a csv file which will load straight into any version of Excel.

  A brief test indicates speeds of about 10,000 lines per second which seems quite quick.

  
  
• Best method for exporting a DataGridView to Excel