Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi All,
 
Is it possible to export datagridview to excel together with its color formatting and its set header? I mean, i need to export exactly what's in the datagridview to excel. Got some idea or thoughts how to do this? Thanks!
Posted 14-Jan-13 15:44pm
Comments
Janna Mamer Dela Cruz at 14-Jan-13 22:12pm
   
thanks! i'll keep that in mind.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Yes, it's possible, with certain degree of approximation, and even pretty good. I have no idea why doing so, but to work with Excel, you will need to use Microsoft Office Interop assembly. If Excel is installed, you have appropriate assembly "Microsoft.Office.Interop.Excel" of appropriate version on GAC, so you can add it from the tab ".NET" of the "Add Reference" window.
 
Please see:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspx[^].
 
(Use the documentation of the version you want to use.)
 
As assume the code with DataGridView is yours, so you should know where did you define all column/cell styles to read them and create similar Excel styles.
 
Many ask if it's possible without Office installation, which is actual for deployment to arbitrary users, as you may or may not allow yourself demand that the users had appropriate Office installation as a prerequisite for using your product. I provided my answers here:
Convert Office-Documents to PDF without interop[^],
Hi how can i display word file in windows application using c#.net[^],
Read a word file without using Interop.word dll...Do not want to install word in IIS..[^].
 
Now, how about some not very pleasant note? All this is labor-taking and ineffective. I personally think that using such a proprietary product as Microsoft Office is a bad thing for a software team or a company, leading to vendor lock-in (http://en.wikipedia.org/wiki/Vendor_lock-in[^]). The desire to export things to Office reflect non a software developer point of view, but the views of a inexperienced user thinking that Office is a major part of computer functionality, due to very limited knowledge and experience. You might easier provide everything what the user wants using pure .NET and nothing else, without unwanted investments. However, I understand that I don't know anything about your product, so you should know better what you really need.
 
—SA
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel
 
sub export
Dim default_location As String = Application.StartupPath & "\Jadwal\" & periode.Text & ".xls"
'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To dg2.ColumnCount - 1
dset.Tables(0).Columns.Add(dg2.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To dg2.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To dg2.Columns.Count - 1
dr1(j) = dg2.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next
 
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
 
excel.Visible = True
excel.UserControl = True
      
wBook = excel.Workbooks.Add(System.Reflection.Missing.Value)
wSheet = wBook.Sheets("sheet1")
excel.Range("A50:I50").EntireColumn.AutoFit()
With wBook
.Sheets("Sheet1").Select()
.Sheets(1).Name = "NameYourSheet"
End With
 
Dim dt As System.Data.DataTable = dset.Tables(0)
wSheet.Cells(1).value = periode.Text
 
For i = 0 To dg2.RowCount - 1
For j = 0 To dg2.ColumnCount - 1
wSheet.Cells(i + 1, j + 1).value = dg2.Rows(i).Cells(j).Value.tosring
Next j
Next i
 
wSheet.Columns.AutoFit()
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(default_location)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
 
If System.IO.File.Exists(default_location) Then
System.IO.File.Delete(default_location)
End If
 
wBook.SaveAs(default_location)
excel.Workbooks.Open(default_location)
excel.Visible = True
end sub
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DATAGRIDVIEW_TO_EXCEL((DataGridView1)) ' THIS PARAMETER IS 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) & EXL & Chr(34) & " " & Chr(34) & FLE & Chr(34), vbNormalFocus) ' OPEN XML WITH EXCEL

    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
 
End Sub
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Go to this link,it may be help you
http://www.setha.info/ict-vb2008.html?start=15[^]
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

It is possible. as Sergey said you need Microsoft Office Interop. I think below article must be very helpful for you, it export customized data from database to excel, but it uses c#, not vb.net, you can give it a try:
How to export customized format data to Excel[^]
https://exportdata.codeplex.co[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Smile | :) 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 Smile | :)
 
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 Smile | :)
 

[edit]unnecesary Code block removed[/edit]
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

This can be achieve by many ways. See my below simple code, i wrote for my old project :
''' <summary>
    ''' To Export Data To Excel From DataGridView
    ''' </summary>
    ''' <param name="FilePath">Excel File Path As String</param>
    ''' <param name="DataGrid">DataGridControlName As DataGridView</param>
    Public Sub ExportDataToExcel(ByVal FilePath As String, ByVal DataGrid As DataGridView)
        Dim _mFileStream As New IO.StreamWriter(FilePath, False)
        Try
            _mFileStream.WriteLine("<?xml version=""1.0""?>")
            _mFileStream.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
            _mFileStream.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
            _mFileStream.WriteLine("    <ss:Styles>")
            _mFileStream.WriteLine("        <ss:Style ss:ID=""1"">")
            _mFileStream.WriteLine("           <ss:Font ss:Bold=""1""/>")
            _mFileStream.WriteLine("           <ss:FontName=""Courier New""/>")
            _mFileStream.WriteLine("        </ss:Style>")
            _mFileStream.WriteLine("    </ss:Styles>")
            _mFileStream.WriteLine("    <ss:Worksheet ss:Name=""Sheet1"">")
            _mFileStream.WriteLine("        <ss:Table>")
            For x As Integer = 0 To DataGrid.Columns.Count - 1
                _mFileStream.WriteLine("            <ss:Column ss:Width=""{0}""/>", DataGrid.Columns.Item(x).Width)
            Next
            _mFileStream.WriteLine("            <ss:Row ss:StyleID=""1"">")
            For i As Integer = 0 To DataGrid.Columns.Count - 1
                _mFileStream.WriteLine("                <ss:Cell>")
                _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Columns.Item(i).HeaderText))
                _mFileStream.WriteLine("</ss:Cell>")
            Next
            _mFileStream.WriteLine("            </ss:Row>")
            For intRow As Integer = 0 To DataGrid.RowCount - 2
                _mFileStream.WriteLine(String.Format("            <ss:Row ss:Height =""{0}"">", DataGrid.Rows(intRow).Height))
                For intCol As Integer = 0 To DataGrid.Columns.Count - 1
                    _mFileStream.WriteLine("                <ss:Cell>")
                    _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Item(intCol, intRow).Value.ToString))
                    _mFileStream.WriteLine("                </ss:Cell>")
                Next
                _mFileStream.WriteLine("            </ss:Row>")
            Next
            _mFileStream.WriteLine("        </ss:Table>")
            _mFileStream.WriteLine("    </ss:Worksheet>")
            _mFileStream.WriteLine("</ss:Workbook>")
            _mFileStream.Close()
            _mFileStream.Dispose()
            _mFileStream = Nothing
        Catch ex As Exception
            _mFileStream.Close()
            _mFileStream.Dispose()
            _mFileStream = Nothing
            MEssageBox.Show("Error While Exporting Data To Excel. Error : " & ex.Message)
        End Try
    End Sub
Add this code in your project and call this method where you want to Export data to Excel from DataGridView by passing below two parameters :
1 : Your Destination excel file path
2 : your DatagridView Control Name which contains Data
 
I hope this will help you. Smile | :)
  Permalink  
v4
Comments
Member 10534668 at 11-May-14 7:37am
   
Dear Manoj, This code works well when the datagridview is fully filled. If datagridview contains some blank spaces the code give rise to error "Object Reference not set to an instance of an object ", the error seems to be originated due to absence of data in some of datagridview cells. The error reported from nested For loop. I tried using Try..catch loop to suppress error and fill that cell with 0 and - but final excel file is corrupted. Can you suggest how I can get over this problem. Thanks very much.
Manoj K Bhoir at 12-May-14 1:24am
   
Can you tell me the exact line on which you are getting the error?
Member 10534668 at 13-May-14 0:22am
   
The exception was reported in this line in nested FOR Loop:
_mFileStream.WriteLine(String.Format(" {0}", DataGrid.Item(intCol, intRow).Value.ToString))
 
I tried to include a Try.. Catch loop to suppress this and passed a "0" for blank cells. The code runs but the final excel sheet is corrupted...Thanks for taking time for helping me out...
Member 10534668 at 21-May-14 9:01am
   
Once you fill the blank values with any values eg. 0, the problem will be solved.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 216
1 CPallini 195
2 BillWoodruff 180
3 Maciej Los 165
4 Kornfeld Eliyahu Peter 140


Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 6 Oct 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100