|
Public Sub ExcelToMshfgrd(FileName As String, ByRef RowCount As Integer, ByRef ColCount As Integer, mshg As MSHFlexGrid) '直接导入Excel内容到MshFlexGrid
If InStr(FileName, ".xls") = 0 Then
MsgBox "请选择 Excel 文件", vbInformation, "请选择正确文件格式"
Exit Sub
End If
Dim oExcel As Excel.Application '用前期绑定
Set oExcel = New Excel.Application
Dim wsBook As Workbook '定义工作簿
Dim wsSheet As Worksheet '定义工作表
With oExcel
.Visible = False
Set wsBook = .ActiveWorkbook
Set wsBook = .Workbooks.Open(FileName)
Set wsSheet = .ActiveWorkbook.Sheets(1) '读取 Sheet1的数据
End With
'读取的Excel,基本格式为 第一行： 主标题 ，第二行 列标题 ，第三行开始正式内容
With wsSheet
' If .Cells(1, 1) = "" Then '对第一行内容进行检测,也可忽略
' MsgBox "请从第一个单元格开始填写数据", vbOKOnly
' Exit Sub
' End If
.Cells(1, 1).Select
'可对列数进行限定,防止取回列数与所需不符
' If ColCount = .Cells.CurrentRegion.Columns.count Then
'
' End If
RowCount = .Cells.CurrentRegion.Rows.count
ColCount = .Cells.CurrentRegion.Columns.count
With mshg
.MergeCells = flexMergeRestrictRows '
.MergeRow(0) = True '合并主标题
.Rows = RowCount '+ 1
.Cols = ColCount + 1
For i = 1 To ColCount
.TextMatrix(0, i) = wsSheet.Cells(1, 1) '读取主标题
.TextMatrix(1, i) = wsSheet.Cells(2, i) '读取列标题
Next
For i = 3 To RowCount '读取选择的内容
.TextMatrix(i - 1, 0) = i - 2 '读取行数
For j = 1 To ColCount
.TextMatrix(i - 1, j) = wsSheet.Cells(i, j)
Next
Next
End With
End With
wsBook.Close False
oExcel.Quit '退出
'释放内存
If Not (wsBook Is Nothing) Then
Set wsBook = Nothing
End If
If Not (wsSheet Is Nothing) Then
Set wsSheet = Nothing
End If
If Not (oExcel Is Nothing) Then
Set oExcel = Nothing
End If
End Sub
|
