Ads by Google

Open Excel Files

Excel Tutorial

ASP Net Excel

Sap Excel Upload

Home

Ask a Question

Jobs

Projects

Forum

Submit Tutorial

Latest Tutorials

Subscribe in a reader Search

Android | ASP | C | C++ | C# | Cocoa | Java | JavaScript | JSP | J2ME | JSF | JDBC| Struts | HTML5 | PHP | Perl | Python | Ruby | VB.net | T .net rends | T estpapers

How to export from DataGridView to excel using VB.net

By: Issac Em ailed: 441 tim Printed: 584 tim es es
4
Like 5.6k

Jump T o
Program ing Language m

T utorial Archive: Android AJAX ASP C C++ C# .net By: Andrei - Where should I call the a Cocoa Cloud Com puting EJB Java Certification By: Amit Agarwal - this code is running succ Interview iPhone By: Chandra Sekhar Bodireddy - The coding which is givenJavascript JSF JSP Java Beans J2ME JDBC Linux By: avinash - It's good working very we Mac OS X MySQL Perl PHP Python Ruby SAP VB.net By: JUST_PREET - not bad EJB Struts T rends XML Office 365 Hibernate By: Alfa - Hi there, a nice tutorial Latest T utorials on: Android By: N - As C does not support Fun AJAX ASP C Cocoa C++ .net C# EJB Java Certification By: Rahul - too good post....very eas Interview iPhone Javascript JSF JSP Java By: Janani - very Good machi....... Beans J2ME JDBC Linux Mac OS X MySQL Perl PHP Python Ruby SAP VB.net For exporting data from Datagridview to Excel, connect the database and load data from the database EJB Struts Cloud to Datagridview and then create a new excel file and write the data from Datagridview to Excel file. Com puting XML Office 365 Hibernate

Latest comments

Related Links

First step is to Load the Product table data to DataGridView, and create new Excel file and write the data from Datagridview to Excel file.
Imports System.Data Imports System.Data.SqlClient Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As SqlConnection Dim connectionString As String Dim sql As String

converted by Web2PDFConvert.com

connectionString = "data source=servername;" & _ "initial catalog=databasename;user id=username;password=password;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "SELECT * FROM Product" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet dscmd.Fill(ds) DataGridView1.DataSource = ds.Tables(0) cnn.Close() End Sub
F acebook social plugin

Find us on Facebook Java-Samples.com

Like 5,673 people like Java-Samples.com.

Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click

Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer xlApp = New 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 xlWorkSheet.Cells(i + 1, j + 1) = _ DataGridView1(j, i).Value.ToString() Next Next xlWorkSheet.SaveAs("C:\vbexcel.xlsx") xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) MsgBox("You can find the file C:\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 End Class

Get tutorials direct to your email:

Subscribe

Excel Tutorials
www.lynda.com/Excel Online video tutorials for Excel. Free Trial.

VB.net Home | All VB.net Tutorials | Latest VB.net Tutorials

If this tutorial doesn't answer

converted by Web2PDFConvert.com

SAP to/from Excel

AutomationAnywhere.com/SAP-Excel Upload/download SAP to/from Excel No Programming. Free Trial!

Deploy Apps in the Cloud

www.ravellosystems.com Clone Your App, Deploy to the Cloud Develop & Test Your App. Start Now!

your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.

Download MS Excel 2010

www.SoftwareKing.com/Excel-2010 Download & Install Instantly Today! All Major Credit Cards Accepted.

Visual Basic Excel Course

udemy.com/VisualBasic The Beginner Visual Basic for Excel Tutorial. 8+ hrs of content.

Excel Consulting
www.ExcelCorporateSolutions.com Automation-Macros Add-Ins Custom Functions
Ads by Google

Bookkeeping Schools
Colleges.CampusCorner.com Search Affordable and Flexible Bookkeeping Degree Programs.

Microsoft Excel Course Excel Export MS Excel 2007

Ads by Google

SAP Excel Integration

www.guixt.com/wsoffice Simplify SAP with GuiXT. Automate Data Entry and Editing. Learn more!

How to Use Excel Excel Office Programming Tutorial

Comments(14)
1. View Comment
It's very helpful to me... Thanks...

2.

View Tutorial View Comment

Hello,

By: Nicks Patel at 2010-03-24 23:37:04

Is there anyway the datagridview can export the column name too? Douglas

3.

View Tutorial View Comment

By: Douglas at 2010-05-13 22:10:09

Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("Feuil1") For Each col As DataGridViewColumn In DG_ENCOURS.Columns 'noms des colonnes xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText For Each rowa As DataGridViewRow In DG_ENCOURS.Rows
converted by Web2PDFConvert.com

xlWorkSheet.Cells(rowa.Index + 2, col.Index + 1) = rowa.Cells(col.Index).Value Next Next xlApp.Visible = True

4.

View Tutorial View Comment

By: Zarno at 2010-12-24 07:11:19

It too good code

5.

View Tutorial View Comment

By: Srinivas at 2011-02-20 03:58:19

Thanks to OP and Zarno for finishing off this code

6.

View Tutorial View Comment

error :

By: Soapy Balls at 2011-05-27 05:26:13

Object reference not set to an instance of an object.

7.

View Tutorial View Comment

By: Ramnath at 2011-05-30 04:01:32

Hello all.. can anyone please help me.. i just got this error.. xlWorkBook = xlApp.Workbooks.Add(misValue) >> Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))

8.

View Tutorial View Comment

By: Christoven at 2011-06-15 05:55:16

I have use the above code, Am getting the below error Unable to cast COM Object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' of Interface type 'Microsoft.Office.Interop.Excel._Application'. This Operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000C000-000000000046}' failed due to the following error : could not be found. (Exception from HRESULT: 0x80030002(STG_E_FILENOTFOUND)). Kindly anyone give me the solution

converted by Web2PDFConvert.com

9.

View Tutorial View Comment

By: RameshKumar at 2011-07-25 05:02:12

add reference -> .net -> 'Microsoft.Office.Interop.Excel then import Microsoft.Office.Interop.

10.

View Tutorial View Comment

hi,

By: Ravi at 2011-08-27 12:17:36

while exporting data i am getting error on below statement saying "Null Reference Exception was unhandled" xlWorkSheet.Cells(i + 1, j + 1) =_ DataGridView1(j,i).Value.ToString() help me outta this.......

11.

View Tutorial View Comment

By: akash at 2011-12-22 07:28:05

Thanks for your code. Nice working but I have one problem. I have time in DataGridview , In that condition it is not working. Without time it is working fine. Plz suggest me. I have urgent required. Thanks

12.

View Tutorial View Comment

By: Ravinder Kumar at 2012-01-02 18:01:21

Thank you... I have the same problem , I have date and time in my datagridview, could not exported and display in excel. Only shows ########. tnx

13.

View Tutorial View Comment

By: makoy at 2012-01-16 09:06:34

I have a problem , i cant see the Microsoft.Office.Interop. in add reference -> .net -> .. i am using Visual Basic Express Edition (2008) .. there is no "Microsoft.Office.Interop.Excel" there , dont know what to do to be able to use that code for my project.. please help.. Thanks.

14.

View Tutorial View Comment

By: Japz at 2012-01-16 09:51:11

I am using VS 2010 Express and MS Excel 2007 wherein I am trying to export data from the DataGridView into an Excel file.
converted by Web2PDFConvert.com

xlWorkSheet.SaveAs( C:\ExportTagDetails.xlsx ) the above line of code works for me. But if I again try to create a file, it obviously shows the Message : A file named C:\ExportTagDetails.xlsx already exists in this location. Do you want to replace it? When I press Yes it creates no problem and calmly replaces itself in place of the old file. But if I click on No or Cancel then it throws an exception stating : System.Runtime.Interop.Services.COMException(0x800A03EC): Exception from HRESULT: 0x800A03EC Here is the entire block of code : Private Sub cmd_export_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd_export.Click Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer Dim row As Long = 1 xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets( Sheet1 ) xlWorkSheet.Cells(1, 1).Font.Bold = True xlWorkSheet.Cells(1, 1).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) xlWorkSheet.Cells(1, 2).Font.Bold = True xlWorkSheet.Cells(1, 2).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) xlWorkSheet.Columns(1).ColumnWidth = 45 xlWorkSheet.Columns(2).ColumnWidth = 30 xlWorkSheet.Range( A & row).Value = Tag Name xlWorkSheet.Range( B & row).Value = Start Value

row = row + 1 Try For i = 0 To DataGridView1.RowCount 2 For j = 0 To DataGridView1.ColumnCount 1 xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString() Next Next xlWorkSheet.SaveAs( C:\ExportTagDetails.xlsx ) Catch ex As Exception MsgBox(ex.ToString) End Try xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook)
converted by Web2PDFConvert.com

releaseObject(xlWorkSheet) MsgBox( You can find the file at C:\ExportTagDetails.xlsx ) End Sub Is there any way I can handle the issue. Or can you provide me with a solution where the user can himself enter the name of the file and also specify the location. Thanks in advance.

View Tutorial
Your name (required):

By: Alphin at 2012-07-06 10:57:07

Your email(required, will not be shown to the public):

Your sites URL (optional):

http://www .java-samples.com
Your comments:

Type the two words:

Add Y Comments our

More Tutorials by Issac

Save/Write/Read image file from/to a database using Java program How to modify the objects using java classes Java program for Cloning Java program for changeable wrapper class A tutorial on Chat Server and Chat Client in VB.net Chat Server in VB.net Chat client in VB.net How to open and read an XML file in VB.net How to create an XML file in VB.net XML and VB.net

More Tutorials in VB.net

Scope in VB.net Unstructured Exception Handling in VB.net Structured Exception Handling in VB.net Creating Sub Procedures in VB.net Creating Functions in VB.net Passing a Variable Number of Arguments to Procedures in VB.net Specifying Optional Arguments with default values in Procedures in VB.net Preserving a Variable's Values between Procedure Calls in VB.net Procedure Delegates in VB.net Properties in VB.net

converted by Web2PDFConvert.com

How to create an XML file in VB.NET using Dataset Multi Threaded Client Socket Programming in VB.net Multi Threaded Server Socket Programming in VB.net Multi threaded Socket Programming in VB.net Client Socket Program sample in VB.net

Understanding Scope in VB.net Using Resume Next and Resume Line in VB.net Using On Error GoTo 0 in VB.net Getting an Exception's Number and Description in VB.net Raising an Exception Intentionally in VB.net

More Latest News

Most Viewed Articles (in VB.net )

Your first VB.NET Crystal Reports - A step by step guide For Loop in VB.net Send SMS using VB code Sub Procedures and Functions in VB.net While Loop in VB.net Client Socket Program sample in VB.net How To Connect To A MySql Database in VB.net Arrays and Dynamic Arrays in VB.net Operators in VB.net IfElse Statements in VB.net How to send email using VB.NET code Using Select Case in VB.net How to export from DataGridView to excel using VB.net String Split function in VB.net To read the entire worksheet in an Excel workbook through VB.net Code

Most Emailed Articles (in VB.net)

Send SMS using VB code For Loop in VB.net IfElse Statements in VB.net For EachNext Loop in VB.net Your first VB.NET Crystal Reports - A step by step guide The Select Case statement in VB.net While Loop in VB.net Do Loop in VB.net What's New in VB .NET? A comparison of VB vs VB.net The Option and Imports Statements in VB .NET Arrays and Dynamic Arrays in VB.net Using Select Case in VB.net Operators in VB.net Using If with And and Comparing two integers using If Sub Procedures and Functions in VB.net

2013 Java-samples.com T utorialArchive: Android AJAX ASP C Cloud Com .net puting Cocoa C++ C# EJB Java Certification Interview iPhone Javascript JSF JSP Java Beans J2ME JDBC Linux Mac OS X MySQL Perl PHP Python Ruby VB.net Struts T Papers T est rends SAP XML Office 365 Hibernate Latest T utorials on: Android AJAX ASP C Cocoa Cloud Com .net puting C++ C# EJB Java Certification Interview iPhone JavaScript JSF JSP Java Beans J2ME JDBC Linux Mac OS X MySQL Perl PHP Python Ruby VB.net SAP Struts T rends XML Office 365 Hibernate

converted by Web2PDFConvert.com