'这是我的做法,提供给您做参考。 Private Sub cmdBatchExport_Click() Call DataExportToExcel(1, "PallletNumber:", MSHFlexGrid1) End SubPublic Function DataExportToExcel(ByVal ExportTypeInt As Integer, ByVal ExportTypeString As String, ByVal Mshflex As MSHFlexGrid)Dim i As Integer, j As Integer Dim tmpexcel As New Excel.Application Dim tmpsheet As New Excel.Worksheet
On Error GoTo eNext: tmpexcel.Workbooks.Add (1) Set tmpsheet = tmpexcel.ActiveWorkbook.ActiveSheet
For i = 0 To Mshflex.Rows - 1 For j = 1 To Mshflex.Cols - 1 If j < 3 Then tmpsheet.Cells(i + 1, j) = "'" + Mshflex.TextMatrix(i, j) Else tmpsheet.Cells(i + 1, j) = Mshflex.TextMatrix(i, j) End If Next j Next i
Private Function GridToExcel() As Boolean Dim DataArray() As String Dim r As Integer, c As Integer GridToExcel = False On Error GoTo exportErr ReDim DataArray(MSHFlexGrid1.Rows - 1, MSHFlexGrid1.Cols - 1) For r = 1 To MSHFlexGrid1.Rows - 1 For c = 1 To MSHFlexGrid1.Cols - 1 DataArray(r - 1, c - 1) = MSHFlexGrid1.TextMatrix(r, c) Next c Next r
Dim oExcel As Object Dim oBook As Object Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application") Set oBook = oExcel.Workbooks.Add Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Resize(MSHFlexGrid1.Rows - 1, MSHFlexGrid1.Cols - 1).value = DataArray oExcel.Visible = True Set oSheet = Nothing Set oBook = Nothing oExcel.Quit Set oExcel = Nothing GridToExcel = True Exit Function exportErr: GridToExcel = False End Function
你可以参考我资源中的一个范例来操作 EXCEL
http://download.csdn.net/source/639058
Private Sub cmdBatchExport_Click()
Call DataExportToExcel(1, "PallletNumber:", MSHFlexGrid1)
End SubPublic Function DataExportToExcel(ByVal ExportTypeInt As Integer, ByVal ExportTypeString As String, ByVal Mshflex As MSHFlexGrid)Dim i As Integer, j As Integer
Dim tmpexcel As New Excel.Application
Dim tmpsheet As New Excel.Worksheet
On Error GoTo eNext: tmpexcel.Workbooks.Add (1)
Set tmpsheet = tmpexcel.ActiveWorkbook.ActiveSheet
For i = 0 To Mshflex.Rows - 1
For j = 1 To Mshflex.Cols - 1
If j < 3 Then
tmpsheet.Cells(i + 1, j) = "'" + Mshflex.TextMatrix(i, j)
Else
tmpsheet.Cells(i + 1, j) = Mshflex.TextMatrix(i, j)
End If
Next j
Next i
tmpsheet.Cells(Mshflex.Rows + 1, 8) = "Power Sum"
tmpsheet.Cells(Mshflex.Rows + 1, 9) = "=SUM(I2:I" + CStr(Mshflex.Rows) + ")"
tmpsheet.Cells(Mshflex.Rows + 1, 10) = "=SUM(J2:J" + CStr(Mshflex.Rows) + ")"
tmpsheet.Cells(Mshflex.Rows + 2, 8) = "Average"
tmpsheet.Cells(Mshflex.Rows + 2, 9) = "=AVERAGE(I2:I" + CStr(Mshflex.Rows) + ")"
tmpsheet.Cells(Mshflex.Rows + 2, 10) = "=AVERAGE(J2:J" + CStr(Mshflex.Rows) + ")" tmpsheet.Cells(Mshflex.Rows + 3, 8) = ExportTypeString
tmpsheet.Cells(Mshflex.Rows + 3, 9) = "'" + Trim(lblPalletNumber.Caption)
'Call AddFooterInfo(tmpSheet)
Dim tempStr As String
CD1.Filter = "Excel (*.xls)|*.xls"
CD1.Filename = App.Path & "\" + Trim(ExportTypeString) + Trim(lblPalletNumber.Caption) + ".xls"
CD1.ShowSave
tmpsheet.SaveAs CD1.Filename
Set tmpsheet = Nothing
tmpexcel.Quit
Set tmpexcel = Nothing
Exit FunctioneNext:
MsgBox Err.Description, vbCritical, Err.Number
Exit Function
End Function
Dim DataArray() As String
Dim r As Integer, c As Integer
GridToExcel = False
On Error GoTo exportErr
ReDim DataArray(MSHFlexGrid1.Rows - 1, MSHFlexGrid1.Cols - 1)
For r = 1 To MSHFlexGrid1.Rows - 1
For c = 1 To MSHFlexGrid1.Cols - 1
DataArray(r - 1, c - 1) = MSHFlexGrid1.TextMatrix(r, c)
Next c
Next r
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Resize(MSHFlexGrid1.Rows - 1, MSHFlexGrid1.Cols - 1).value = DataArray
oExcel.Visible = True Set oSheet = Nothing
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing
GridToExcel = True
Exit Function
exportErr:
GridToExcel = False
End Function