VBnet操做Excel的代码

代码示例 Private Sub writeToExcel(strTmp1() As String, colTmp1 As Collection) Dim tmp1 Dim i1 As Integer, intCol As Integer, intRow As Integer Dim xlApp As New Excel.Application Dim xlBook As New Excel.Workbook Dim xlSheet As New Excel.Worksheet Dim strName As String, strArray1() As String Dim strS1 As String Dim strD1 As String strS1 = CurrentProject.Path + “\template.xls“ strD1 = CurrentProject.Path + “\“ + CStr(at(Now, “YYYYMMDDHHMMSS“)) + “aaa1.xls“ For i1 = 0 To UBound(strTmp1) - 1 Debug.Print strTmp1(i1) + “ “ + CStr(i1) Next i1 strName = CurrentProject.Path + “\aaa1.xls“ FileCopy strS1, strD1 Set xlApp = CreateObject(“Excel.Application“) xlApp.Visible = False Set xlBook = xlApp.Workbooks.Open(strName) Set xlBook = xlApp.Workbooks.Open(strD1) Set xlSheet = xlBook.Worksheets(1) With xlSheet .Range(“F6“).Value = strTmp1(1) .Range(“H6“).Value = strTmp1(2) .Range(“F7“).Value = CStr(Date) .Range(“E10“).Value = strTmp1(9) .Range(“A15“).Value = “To: “ + strTmp1(8) .Range(“B26“).Value = strTmp1(4) + “PACKAGES“ .Range(“B27“).Value = strTmp1(5) + “KGS“ .Range(“B28“).Value = strTmp1(6) + “KGS“ .Range(“B29“).Value = strTmp1(7) + “M3“ End With intCol = 1 intRow = 21 1 1 / 3939 代码示例 For i1 = 1 To colTmp1.Count strArray1 = colTmp1.Item(i1) With xlSheet .Cells(intRow, 1).Value = strArray1(2) .Cells(intRow, 2).Value = strArray1(5) .Cells(intRow, 4).Value = strArray1(6) .Cells(intRow, 5).Value = strArray1(1) .Cells(intRow, 6).Value = strArray1(3) .Cells(intRow, 7).Value = strArray1(4) .Cells(intRow, 8).Value = strArray1(7) .Cells(intRow, 9).Value = strArray1(9) intRow = intRow + 1 xlApp.ActiveSheet.Rows(intRow).Insert .Cells(intRow, 1).Value = strArray1(8) intRow = intRow + 1 xlApp.ActiveSheet.Rows(intRow).Insert End With intRow = intRow + 1 xlApp.ActiveSheet.Rows(intRow).Insert Next i1 xlApp.Visible = True xlBook.Save xlBook.Close Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit tmp1 = Shell(strName, 1) hWndDesk = GetDesktopWindow() r = Shellcute(hWndDesk, “Open“, strName, vbNullString, 0 “ Set rs1 = CurrentDb.OpenRecordset(strSQL) rs1.MoveLast Debug.Print rs1.RecordCount lngCount = rs1.RecordCount intFieldLength = rs1.Fields.Count Debug.Print intFieldLength Debug.Print intFieldLength strA2() = Split(splitTable(“HEADCOST1“), “,“) Debug.Print UBound(strA2) With xlSheet For i1 = 0 To intFieldLength - 1 Debug.Print i1 Debug.Print strA1(i1) 3 3 / 3939 代码示例 .Range(strA1(i1)).Value = getZValue(strA2(i1)) Next i1 End With If rs1.RecordCount 0 Then rs1.MoveFirst For i1 = 1 To lngCount For i2 = 1 To rs1.Fields.Count If IsNull(rs1(i2 - 1)) Then strValue = “ “ Else strValue = rs1(i2 - 1).Value End If xlSheet.Cells(i1 + 1, i2) = strValue Next i2 rs1.MoveNext Next i1 rs1.MoveFirst Else MsgBox “未读取到数据“, vbCritical, “错误“ End If xlBook.Save xlBook.Close Set xlSheet = Nothing Set xlBook = Nothing xlApp.Quit Set xlApp = Nothing rs1.Close Set rs1 = Nothing End Sub 4 4 / 3939 代码示例 Private Sub Command1_Click() Set xlApp = CreateObject(“Excel.Application“)