Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel
Xport vb.net to Excel Xport vb.net to Excel
Xport vb.net to Excel
Go Back  Xtreme Visual Basic Talk > > > Xport vb.net to Excel


Reply
 
Thread Tools Display Modes
  #1  
Old 01-17-2012, 12:20 PM
kimt kimt is offline
Centurion
 
Join Date: Mar 2003
Posts: 144
Default Xport vb.net to Excel


Trying to export fields to excel. Have set the references to Excel 12, But, the i, j loop cuts short the number of fields to export to 10 when there are about 16 to export.
Code:
Imports Excel = Microsoft.Office.Interop.Excel. Dim i As Integer Dim j As Integer Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim sFileName As String = "tmp.xls" xlApp = New Excel.Application xlWorkBook = xlApp.Workbooks.Add(misValue) conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application.StartupPath & "\temp.mdb") command = New OleDbCommand("SELECT * FROM Members", conn) da.SelectCommand = command da.Fill(ds, "Members") 'get active worksheet xlWorkSheet = DirectCast(xlWorkBook.ActiveSheet, Excel.Worksheet) xlWorkSheet.Name = "Members" For i = 0 To ds.Tables(0).Rows.Count - 1 For j = 0 To ds.Tables(0).Columns.Count - 1 xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j) Next Next
Any ideas on how to cure would be great. thanx.

After working on it the results are the code cycles through temp.mdb twice. the first time only 10 fields the second has all fields. I do not see why it cycles.

Also, I would like to add headers. Thanks.

Found the reason for the dupes. No on to adding headers. thanks.

Last edited by kimt; 01-18-2012 at 02:51 AM.
Reply With Quote
  #2  
Old 01-18-2012, 02:56 AM
cigarette cigarette is offline
Newcomer
 
Join Date: Mar 2010
Location: Pinas
Posts: 8
Default

Check this out. I've extracted my data to a listview then it will be exported to excel

Code:
Dim ExcelReport As Excel.ApplicationClass
        Const MAX_COLOURS As Int16 = 40
        Const MAX_COLUMS As Int16 = 254
        Dim i As Integer
        Dim New_Item As Windows.Forms.ListViewItem
        Dim TempColum As Int16
        Dim ColumLetter As String
        Dim TempRow As Int16
        Dim TempColum2 As Int16
        Dim AddedColours As Int16 = 1
        Dim MyColours As Hashtable = New Hashtable
        Dim AddNewBackColour As Boolean = True
        Dim AddNewFrontColour As Boolean = True
        Dim BackColour As String
        Dim FrontColour As String

        Dim workbook As Excel.Workbook
        ExcelReport = New Excel.Application
        ExcelReport.Visible = True
        workbook = ExcelReport.Workbooks.Open("\\your path goes here\ExcelFile.xls") 'CREATE YOUR MODIFIED EXCEL FILE AND CREATE YOUR HEADER THERE AS LONG YOU DECLARE ON HOW MANY ROW YOU WILL SKIPPED. AND CREATE A MACRO FOR YOUR EXCEL FILE THAT WILL DISABLED SAVE AND USE SAVE AS COZ IT WILL OVERWRITE THE EMPTY SHEET WITH YOUR EXTRACTED DATA IF THE END USER SAVES IT
        ExcelReport.Range("A1").Value = "MY EXAMPLE " + Datetimepicker1.Value.ToString("MMMM yyyy")
        i = 30 ''HERE WHERE YOU WILL DECLARE ON HOW MANY COLUMNS ARE THERE IN YOUR LISTVIEW (ASSUMING IT'S 30)
        Do Until i = ListView1.Columns.Count
            If i > MAX_COLUMS Then
                MsgBox("Too many Colums added")
                Exit Do
            End If
            TempColum = i
            TempColum2 = 30 ''DECLARE HERE ONCE AGAIN
            Do While TempColum > 25
                TempColum -= 26
                TempColum2 += 1
            Loop
            ColumLetter = Chr(97 + TempColum)
            If TempColum2 > 0 Then ColumLetter = Chr(96 + TempColum2) & ColumLetter
            ExcelReport.Range(ColumLetter & 1).Value = ListView1.Columns(i).Text
            ExcelReport.Range(ColumLetter & 1).Font.Name = ListView1.Font.Name
            ExcelReport.Range(ColumLetter & 1).Font.Size = ListView1.Font.Size
            i += 1
        Loop
        TempRow = 6 'DECLARATION ON HOW MANY ROWS YOU WILL SKIP BEFORE EXTRACTING YOUR DATA
        For Each New_Item In ListView1.Items
            i = 0
            Do Until i = New_Item.SubItems.Count
                If i > MAX_COLUMS Then
                    MsgBox("Too many Colums added")
                    Exit Do
                End If
                TempColum = i
                TempColum2 = 0
                Do While TempColum > 25
                    TempColum -= 26
                    TempColum2 += 1
                Loop
                ColumLetter = Chr(97 + TempColum)
                If TempColum2 > 0 Then ColumLetter = Chr(96 + TempColum2) & ColumLetter
                ExcelReport.Range(ColumLetter & TempRow).Value = New_Item.SubItems(i).Text
                ExcelReport.Range(ColumLetter & TempRow).Font.Name = New_Item.Font.Name
                ExcelReport.Range(ColumLetter & TempRow).Font.Size = New_Item.Font.Size
                AddNewFrontColour = False
                AddNewBackColour = False
                Try
                    BackColour = MyColours(New_Item.BackColor.ToString)
                    If BackColour = "" Then AddNewBackColour = True
                    FrontColour = MyColours(New_Item.ForeColor.ToString)
                    If FrontColour = "" Then AddNewFrontColour = True
                Catch ex As Exception
                    AddNewFrontColour = False
                    AddNewBackColour = False
                End Try
                If AddedColours < MAX_COLOURS And (AddNewFrontColour Or AddNewBackColour) And (New_Item.BackColor.ToArgb <> -1) Then
                    If AddNewBackColour Then
                        MyColours.Add(New_Item.BackColor.ToString, AddedColours)
                        ExcelReport.Workbooks.Item(1).Colors(AddedColours) = RGB(New_Item.BackColor.R, New_Item.BackColor.G, New_Item.BackColor.B)
                        AddedColours += 1
                    End If
                    If AddNewFrontColour Then
                        MyColours.Add(New_Item.ForeColor.ToString, AddedColours)
                        ExcelReport.Workbooks.Item(1).Colors(AddedColours) = RGB(New_Item.ForeColor.R, New_Item.ForeColor.G, New_Item.ForeColor.B)
                        AddedColours += 1
                    End If
                End If
                ExcelReport.Rows(TempRow & ":" & TempRow).select()
                ExcelReport.Selection.Interior.ColorIndex = MyColours(New_Item.BackColor.ToString)
                ExcelReport.Selection.Font.ColorIndex = MyColours(New_Item.ForeColor.ToString)
                i += 1
            Loop
            TempRow += 1
        Next
        ExcelReport.Cells.Select()
        ExcelReport.Cells.Range("A6").Select() 'HERE WHERE YOU WILL START TO EXTRACT YOUR DATA
BTW if you want to add some header, you can add it on your listview and automatically it will be adopt on excel file.

Hope it helps.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel Xport vb.net to Excel
Xport vb.net to Excel
Xport vb.net to Excel
 
Xport vb.net to Excel
Xport vb.net to Excel
 
-->