from listview to excel

[sigridish]

hi! can some one give me a code for exporting listview data to excel? thanks

[sigridish]

Public Sub saveExcelFile(ByVal FileName As String)

        Dim Excel As Object

        Excel = CreateObject("Excel.Application")
        Excel.screenupdating = True
        Excel.Visible = True

        Dim xlWorkSheet As Object = Excel.workbooks.add
        Excel.workbooks(1).worksheets(1).cells(1, 1).value = "Success"

        xlWorkSheet.SaveAs(FileName)

        Excel.quit()
        Excel = Nothing

    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim saveFileDialog1 As New SaveFileDialog
        saveFileDialog1.Filter = "Excel File|*.xls"
        saveFileDialog1.Title = "Save an Excel File"
        saveFileDialog1.ShowDialog()
        If saveFileDialog1.FileName <> "" Then
            saveExcelFile(saveFileDialog1.FileName)
        End If
    End Sub

that is my code. everytime i open the saved file, it says that it has a different file type and asks me if i want to continue opening it and i click yes. the data inside the excel file is only the word "Success"

[Reverend Jim]

Assuming you have an Excel file on D: named myfile.xls, you can access it as follows:

Dim xls As New Excel.Application
Dim sheet As Excel.Worksheet

xls.Workbooks.Open("D:\myfile.xls")
sheet = xls.Workbooks(1).Worksheets(1)

sheet.Cells(1,1) = "abc"
sheet.Cells(1,2) = "def"
sheet.Cells(1,3) = "ghi"

xls.SaveWorkspace()
xls.Quit

That will set the first three columns of the first row to the respective values, save the file then exit Excel. The actual Excel interface is hidden.

[sigridish]

hi jim! waht i want to happen is import the data in my listview to excel. i think this would do loop? but im weak when it comes to loop

[Reverend Jim]

Create a new form. Add a reference to "Microsoft Excel 11.0 Object Library". At least that's the name on my PC. The version number may be different on yours.

Add two buttons named btnPopulate and btnExport. Add a listview named lvwToExport and set it to details view. Make sure it has three columns. Add the following code to your form and run it (you may need to change the path of the Excel file). Click on "Populate" to add several items to the listview, then "Export" to create an Excel spreadsheet and copy the listview items. By default, the Excel.Application is created invisible. If you want to see it add the line

xls.Visible = True

and comment out the xls.Close and xls.Quit lines (or the spreadsheet will disappear almost immediately after it is created).

Imports Microsoft.Office.Interop
Imports System.Windows.Forms

Public Class Form1

    Private Sub btnPopulate_Click(sender As System.Object, e As System.EventArgs) Handles btnPopulate.Click

        Dim item As ListViewItem

        'add two rows (of three columns) to the listview

        item = New ListViewItem("one")
        item.SubItems.Add("two")
        item.SubItems.Add("three")
        lvwToExport.Items.Add(item)

        item = New ListViewItem("four")
        item.SubItems.Add("five")
        item.SubItems.Add("six")
        lvwToExport.Items.Add(item)

    End Sub

    Private Sub btnExport_Click(sender As System.Object, e As System.EventArgs) Handles btnExport.Click

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        'by default a new Excel Application has no workbooks. Add one. Also
        'by default, a new workbook has three worksheets.

        xls.Workbooks.Add()
        sheet = xls.ActiveWorkbook.ActiveSheet

        Dim row As Integer = 1
        Dim col As Integer = 1

        'copy the listview rows and columns to the worksheet cells

        For Each item As ListViewItem In lvwToExport.Items
            For i As Integer = 0 To item.SubItems.Count - 1
                sheet.Cells(row, col) = item.SubItems(i).Text
                col = col + 1
            Next
            row += 1
            col = 1
        Next

        'save the workbook, close it then exit Excel

        xls.ActiveWorkbook.SaveAs("d:\temp\myfile.xls")
        xls.Workbooks.Close()
        xls.Quit()

    End Sub

End Class

[sigridish]

thanks so much jim! but one question though. the column name was not added

[Reverend Jim]

I'll leave that as an exercise for the student. Read up on the ListView control and play around a bit and you'll find out how to get the column names from the control. Or, if you already know the column names you should be able to write the code to add them to the spreadsheet.

[sigridish]

ok jim. thanks so much! :cool:

[sigridish]

hi jim! i have another question

i have two combo box in my program

one has the subteam for example if you click the 1st combo box, the items would be
A1
A2
A3
A4
A5
what i want to happen is that if i chose A1 only the members of team A1 would appear in my combo box2. and by that, you need an sql statement. but how do i code that?

[Reverend Jim]

This thread has been marked as solved. Please start a new thread.

[sigridish]

http://www.daniweb.com/software-development/vbnet/threads/396290/1699997#post1699997

please check this jim