List view to Excel
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim Array(,) As String
xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\TEST.xlsx")
xlWorkSheet = xlWorkBook.Sheets("sheet2")
ReDim Array(ListView1.Items.Count - 1, 8) ''row/column
xlWorkSheet.Cells(3, 2) = Date.Now
xlWorkSheet.Range("B2").Value = "xxx"
For i = 0 To ListView1.Items.Count - 1
Array(i, 0) = ListView1.Items(i).SubItems(0).Text
Array(i, 1) = ListView1.Items(i).SubItems(1).Text
Array(i, 2) = ListView1.Items(i).SubItems(2).Text
Array(i, 3) = ListView1.Items(i).SubItems(3).Text
Array(i, 4) = ListView1.Items(i).SubItems(4).Text
Array(i, 5) = ListView1.Items(i).SubItems(5).Text
Array(i, 6) = ListView1.Items(i).SubItems(6).Text
Array(i, 7) = ListView1.Items(i).SubItems(7).Text
Next
MessageBox.Show(UBound(Array))
xlWorkSheet.Range("A6:I" & (ListView1.Items.Count + 5).ToString).Value = Array
xlWorkSheet.Range("A" & (ListView1.Items.Count + 6).ToString & ":A500").EntireRow.Hidden = True
xlWorkSheet.SaveAs("C:\TEST.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Dim res As MsgBoxResult
res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then
Process.Start("C:\TEST.xlsx")
End If
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim Array(,) As String
xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\TEST.xlsx")
xlWorkSheet = xlWorkBook.Sheets("sheet2")
ReDim Array(ListView1.Items.Count - 1, 8) ''row/column
xlWorkSheet.Cells(3, 2) = Date.Now
xlWorkSheet.Range("B2").Value = "xxx"
For i = 0 To ListView1.Items.Count - 1
Array(i, 0) = ListView1.Items(i).SubItems(0).Text
Array(i, 1) = ListView1.Items(i).SubItems(1).Text
Array(i, 2) = ListView1.Items(i).SubItems(2).Text
Array(i, 3) = ListView1.Items(i).SubItems(3).Text
Array(i, 4) = ListView1.Items(i).SubItems(4).Text
Array(i, 5) = ListView1.Items(i).SubItems(5).Text
Array(i, 6) = ListView1.Items(i).SubItems(6).Text
Array(i, 7) = ListView1.Items(i).SubItems(7).Text
Next
MessageBox.Show(UBound(Array))
xlWorkSheet.Range("A6:I" & (ListView1.Items.Count + 5).ToString).Value = Array
xlWorkSheet.Range("A" & (ListView1.Items.Count + 6).ToString & ":A500").EntireRow.Hidden = True
xlWorkSheet.SaveAs("C:\TEST.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
Dim res As MsgBoxResult
res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then
Process.Start("C:\TEST.xlsx")
End If
ไม่มีความคิดเห็น