Click here to Skip to main content
15,035,600 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Comments
Janna Dela Cruz 14-Jan-13 22:12pm
   
thanks! i'll keep that in mind.

VB
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
   
v2
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
   
VB
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
   
:) 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 :)


[edit]unnecesary Code block removed[/edit]
   
v2
Go to this link,it may be help you
http://www.setha.info/ict-vb2008.html?start=15[^]
   
v2
This can be achieve by many ways. See my below simple code, i wrote for my old project :
VB
''' <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. :)
   
v4
Comments
Member 10534668 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 12-May-14 1:24am
   
Can you tell me the exact line on which you are getting the error?
Member 10534668 13-May-14 0:22am
   
The exception was reported in this line in nested FOR Loop:
_mFileStream.WriteLine(String.Format(" <ss:data ss:type="" string""="">{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 21-May-14 9:01am
   
Once you fill the blank values with any values eg. 0, the problem will be solved.
BIBASWAN 27-Apr-18 14:51pm
   
Thanx Manoj,you code is excellent and working fine,but i am facing one problem that is...if there are 4 column in grid this code export 3 column....please tell me how to solve this problem
XML
If DataGrid.Item(intCol, intRow).Value Is Nothing Then
    _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", ""))
Else
    _mFileStream.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", DataGrid.Item(intCol, intRow).Value.ToString))
End If


Or Like above use a defaul value for a field

Save the output as ExcelDocument.xml not xls or xlsx
well for solution 7 that is

Well Made I love it nice and Quick!!
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900