hi! can some one give me a code for exporting listview data to excel? thanks
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"
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.
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
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
thanks so much jim! but one question though. the column name was not added
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.
ok jim. thanks so much! :cool:
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
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?
This thread has been marked as solved. Please start a new thread.