1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| Imports Microsoft.Office.Interop.Excel
Public Class Form2
Property ExcelApp As Application
Property ExcelWorkBook As Workbook
Sub New(ByRef App As Application)
InitializeComponent()
ExcelApp = App
End Sub
Private Sub BBack_Click(sender As Object, e As EventArgs) Handles BBack.Click
ExcelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
ExcelApp = Nothing
Form1.Show()
Me.Close()
End Sub
Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable
Dim sheets As ExcelApp.Sheets
Dim oMissiong As Object = System.Reflection.Missing.Value
Dim workbook As ExcelWorkbook = Nothing
Dim dt As DataTable = New DataTable()
Try
If ExcelApp Is Nothing Then Return Nothing
workbook = ExcelApp.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
sheets = workbook.Worksheets
Dim worksheet As ExcelApp.Worksheet = sheets(1)
Dim ji As Integer = CType(12, Integer)
If worksheet Is Nothing Then Return Nothing
Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
Dim iColCount As Integer = worksheet.UsedRange.Columns.Count
For i As Integer = 0 To iColCount - 1
Dim name = "column" & i
If hasTitle Then
Dim txt = (CType(worksheet.Cells(1, i + 1), ExcelApp.Range)).Text.ToString()
If Not String.IsNullOrWhiteSpace(txt) Then name = txt
End If
While dt.Columns.Contains(name)
name = name & "_1"
End While
dt.Columns.Add(New DataColumn(name, GetType(String)))
Next
Dim range As ExcelApp.Range
Dim rowIdx As Integer = If(hasTitle, 2, 1)
For iRow As Integer = rowIdx To iRowCount
Dim dr As DataRow = dt.NewRow()
For iCol As Integer = 1 To iColCount
range = CType(worksheet.Cells(iRow, iCol), ExcelApp.Range)
dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
Next
dt.Rows.Add(dr)
Next
Return dt
Catch
Return Nothing
Finally
workbook.Close(False, oMissiong, oMissiong)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
workbook = Nothing
ExcelWorkBook.Close()
ExcelWorkBook = Nothing
End Try
End Function
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
End Sub
Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged
End Sub
Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged
End Sub
Private Sub ComboBox4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox4.SelectedIndexChanged
End Sub
Private Sub ComboBox5_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox5.SelectedIndexChanged
End Sub
Private Sub ComboBox6_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox6.SelectedIndexChanged
End Sub
End Class |
Partager