Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
|
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 31st, 2005, 11:58 AM
Authorized User
 
Join Date: Feb 2005
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Writing to Excel from a dataset with VB.Net

Hi,
Can anyone give me a quick sample bit of code that shows :

a) how to open a connection to a new excel spreadsheet
b) how to set up column names in the spreadsheet to match a dataset
c) how to write fields from the dataset to the spreadsheet
d) how to name the spreadsheet and name & save the xls file

thanks in advance,

Ru
 
Old June 3rd, 2005, 06:42 AM
Authorized User
 
Join Date: May 2005
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi:

Microsoft has a good set of example code that shows how to move the contents of a datagrid into an excel spreadsheet.

Here is the link: You can download this and load it into Visual Studio and check out the code:

http://www.microsoft.com/downloads/d...displaylang=en

Their approach was somewhat simple: Basically, you create an object of an excel application instance, with a workbook object, and a sheet object, and you traverse your data row by row and column by column and place it in the appropriate cells, referenced by their names (ie "A1", "B2" etc )

I modified this to use the column headings I had defined in my grid table styles, and the widths I had established as well. (Divide the width in your grid by 4 when specifying it for excel or the columns will be way too big.) I also dynamically determine the cell references so that they are created automatically for any size datagrid (I limited mine to 26 columns, but you could extend it further.)

As for naming the file and saving it, once the user is presented with the Excel dialog, they can do that from there, and name it and save it where they like.

Here is my modest code for exporting (via a button) my grid to excel. It requires that you add certain referenced dlls and that the user actually HAS excel.

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        ' display wait cursor
        Me.Cursor = Cursors.WaitCursor
        ' various variables
        Dim row As Integer
        Dim col As Integer
        Dim rowCount As Integer
        Dim colCount As Integer
        Dim cell As String
        Dim rowcell As Integer

        ' Excel Variables
        Dim excelApp As New Excel.Application
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
        Dim excelWorksheet As Excel.Worksheet = _
            CType(excelBook.Worksheets(1), Excel.Worksheet)

        ' get count of rows and count of columns
        rowCount = objDataSet.Tables(0).Rows.Count()
        colCount = objDataSet.Tables(0).Columns.Count()

        ' add the column headings
        For col = 0 To colCount - 1
            row = 1
            cell = GetExcelColumn(col) & row.ToString
            excelWorksheet.Range(cell).Value = grdFieldnetData.TableStyles(0).GridColumnStyles(co l).HeaderText
            excelWorksheet.Range(cell).ColumnWidth = grdFieldnetData.TableStyles(0).GridColumnStyles(co l).Width / 4
        Next

        ' now add the data elements
        For row = 0 To rowCount - 1
            rowcell = row + 2
            For col = 0 To colCount - 1
                cell = GetExcelColumn(col) & rowcell.ToString
                excelWorksheet.Range(cell).Value = grdFieldnetData.Item(row, col).ToString()
            Next
        Next
        ' turn off wait cursor
        Me.Cursor = Cursors.Default

        ' view the spread sheet
        excelApp.Visible = True
    End Sub
    ' Function GetExcelColumn - returns the column reference
    ' from an integer representing a column in a datagrid or dataset
    Function GetExcelColumn(ByVal col As Integer) As String
        Dim result As String
        Select Case col
            Case 0 ' first column
                result = "A"
            Case 1
                result = "B"
            Case 2
                result = "C"
            Case 3
                result = "D"
            Case 4
                result = "E"
            Case 5
                result = "F"
            Case 6
                result = "G"
            Case 7
                result = "H"
            Case 8
                result = "I"
            Case 9
                result = "J"
            Case 10
                result = "K"
            Case 11
                result = "L"
            Case 12
                result = "M"
            Case 13
                result = "N"
            Case 14
                result = "O"
            Case 15
                result = "P"
            Case 16
                result = "Q"
            Case 17
                result = "R"
            Case 18
                result = "S"
            Case 19
                result = "T"
            Case 20
                result = "U"
            Case 21
                result = "V"
            Case 22
                result = "W"
            Case 23
                result = "X"
            Case 24
                result = "Y"
            Case 25
                result = "Z"
        End Select
        Return result
    End Function
 
Old October 27th, 2005, 08:15 AM
Authorized User
 
Join Date: Feb 2005
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the help...
Appologies for not replying before now...
Ru.
 
Old July 19th, 2006, 12:43 AM
Registered User
 
Join Date: Jul 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to bahraman
Default

hi
    Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
error old type libraiy


 
Old June 1st, 2008, 05:17 AM
Authorized User
 
Join Date: Jun 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

you can do it with the help of OLEDB.

http://vb.net-informations.com/excel...xcel_oledb.htm

thanks
lee

 
Old July 27th, 2009, 12:07 PM
Registered User
 
Join Date: Jul 2009
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

nice code cliff but Excel Automation has many issues. Best way is to use 3rd party component like GemBox .NET Excel component. Here is a list of reasons why it's better to use this component then Excel Automation.

Filip
GemBox.Spreadsheet - easiest way to read and write Excel files in .NET





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dataset in vb.net thas123 .NET Framework 2.0 1 April 17th, 2007 06:46 AM
Dataset in vb.net thas123 .NET Framework 2.0 0 January 15th, 2007 04:35 AM
Reading and Writing data from Excel using VB? honey123 VB How-To 0 March 25th, 2006 08:48 AM
Writing a dataset into an access file pankaj_daga ADO.NET 10 March 16th, 2004 10:37 AM
Writing a dataset into an access file pankaj_daga Access 0 March 12th, 2004 10:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.