VB创建Excel表格,合并单元格,生成图形等操作

1、Private Sub Command4_Click()On Error Resume Next''''''''''''''''''''''''''''''''''''''''''''''''''&#

2、39;'''''Create Excel Table''''''''''''''''''''''''''''''''''''''''''Dim xlApp As Excel.ApplicationDim xlBook As

3、Excel.WorkbookDim xlSheet As Excel.WorksheetDim xlSheet1 As Excel.WorksheetDim i As Integer, tmHour As StringII11IIIIIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIII11II11On Error Resume NextII11IIIIIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IISet xlApp = Excel.ApplicationSet xlBook = xlApp.Workb

4、ooks.Add'xlBook.ActivateSet xlSheet = xlBook.Worksheets(1) ''''''''''''''''''''''''''''''''' 引用第 1张工作表 xlApp.ActiveSheet.Rows.VerticalAlignment =xlVAlignCenter '

5、;''垂'' 直方向居中xIA pp .ActiveSheet. Rows.Horizo ntalAlig nmen t=xlVAIig nCe nter'水 平方向居中xlSheet.Name = "实测值"Set xlSheet1 = xlBook.Worksheets(2)xlSheet1.Name = "Chart"With xlSheetFor i = 2 To 11只ange(Cells(1, 1), Cells(1, i).Merge合并A-K单元格 Next' .Cells(1, 1).Fo

6、reColor = RGB(100, 150, 255).Cells(1, 1).Font.Size = 25'''''''''''''''''''''''''' 设置行高 '设置列宽For i = 1 To 22.Rows(i).RowHeight = 25NextFor i = 1 To 11.Columns(i).ColumnWidth = 15Next''&

7、#39;'''''''''''''''''''''''' 合并单元格For i = 3 To 22If i < 8 Then单元格单元格色只ange(Cells(3, 1), Cells(i, 1).Merge "”'合并 A3-A7.Range(Cells(3, 8), Cells(i, 8).Merge '''''''&#

8、39;'''合并 H3-H7ElseIf i < 13 Then.Range(Cells(8, 1), Cells(i, 1).Merge.Range(Cells(8, 8), Cells(i, 8).Merge3 / 10E_se_f i A 18 ThenRange(ce_s(13 D ce_s(L l)MegeRange(ce-_SU3 8)7 Ce-_S(L 8)MegeE_se_f i A 23 Then Range(ce-_SU0 1)7 Ce-_S(L l)Mege Range(ce_su0 8)7 ce_s(L 8)MegeNexf Range(=

9、AVK22=)BOde0Linesfy_eHX_c§finuous三三力ange(=A二 夭22)BOde0co_oH vbB_ue§§力岁9殳71夭22)_2望000_0HRGB(1O0 180 0) m冈 Range(=A1).va_ue H 二wa#昌四= Range(=AV)Fonf.co_oH vbRed Range(=AV).FOnf.Name H .s Range(=A1)Fonf.size"30 Range(=A2).va_ue H (VAC) Range(B2).va_ue H 1 Range(c2).va_ue H鸯圧曲朗(V) Ra

10、nge(D2).va_ue H骞圧曲酉 1= Range(币2).va_ue H 鸯圧5擾 1 Range(F2).va_ue H (Ar Range(G2).va_ue H S3= Range(H2)va_ue H 聲的(A) Rangeu2)va_ue H 1 Range(J2).va_ue H is % Range(夭2).va_ue "酹苹吟 omow = Range(=A3).va_ue H =90 Range(=A8).va_ue!115 Range(=A13).va_ue t23cr Range(=A18)va_ue :264二 Range(D3).vaue H =0=

11、 Range(D4)va Range(D5).va Range(D6=).va Range(D7).va Range(D8).va Range(D9).vaue"w4L0adue H .24 LoacTue H FU= Loadue H =0=ue tw4L0acr Range(D1cr).va_ue H .24 Load Range(D1r).va_ue H =3、4 LoacT.Range("D12").Value = "Full Load".Range("D13").Value = "0".Rang

12、e("D14").Value = "1/4 Load".Range("D15").Value = "2/4 Load".Range("D16").Value = "3/4 Load".Range("D17").Value = "Full Load".Range("D18").Value = "0".Range("D19").Value = "1/4 Load"

13、;.Range("D20").Value = "2/4 Load".Range("D21").Value = "3/4 Load".Range("D22").Value = "Full Load"End WithtmHour = "-" & Hour(Time)tmHour = tmHour & "-" & Minute(Time)tmHour = tmHour & "-" &

14、 Second(Time)xlApp.ActiveWorkbook.SaveAsApp.Path&""&Format(Date,dddd,mmmm, yyyy)& tmHour + ".xls"xlApp.Workbooks.ClosexlApp.QuitSet xlApp = Nothing 释' 放引用II11IIIIIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIII11IIIIII写入数据 ''''''

15、;'''''''''''''''''''''''''''''''''''''''Dim j, LengthTXT, k, Num, NEXCEL As IntegerDim StrTxt As StringOn Error Resume Next''''&

16、#39;'''''''''''''''''''''' 计算数组的围数 NUMLengthTXT = Len(Text1.Text)StrTxt = Text1.TextNum = 1For i = 1 To LengthTXTIf Mid(Text1.Text, i, 1) = "," ThenNum = Num + 1End IfNextReDim StrDataArray(Num)重定义围数

17、9;''''''''''''''''''''''''''''' 赋值给数组 StrDataArrayIf Num = 1 ThenStrDataArray(Num) = StrTxtElseFor i = 1 To LengthTXTStrData = StrData & Mid(StrTxt, i, 1)If Mid(StrTxt, i, 1) = &qu

18、ot;," ThenStrDataArray(j) = Left(StrData, k - 1)StrData = ""End IfStrDataArray(Num) = StrDataNextEnd If''''''''''''''''''''''''''''''check StrDataArray(i)'For i

19、= 1 To Num'MsgBox StrDataArray(i) & "" & i'Next数值分段存储到数组 ,每组为一个实测值Dim TowArray() As StringDim WS, N As IntegerWS = Num 4 ''''''''''''''''' 围数ReDim TowArray(WS, 4)For i = 1 To Num - 2For j = 1 To 4'If i

20、4 = 0 ThenTowArray(N + 1, j) = StrDataArray(j + 4 * N)' End IfNextNext'''''''''''''''''''''''''''''''''checkTowArray(N + 1, j)'For i = 1 To WS'MsgBoxTowArra

21、y(i, 1) &TowArray(i, 2) & TowArray(i, 3) & TowArray(i,4)'Next''''''''''''''''''''''''''''''''''' 数值转换''''第 4个字节转换为 2进制ReDim Byte

22、DataString(WS)For i = 1 To Num 4'MsgBox TowArray(i, 4) MsgBox CStr(TowArray(i, 4)ByteDataString(i) = HexToByte(CStr(TowArray(i, 4) ''转'''换为 2进制,8位 'MsgBox ByteDataString(i) & " " & iNextII11IIIIIIII11IIIII11IIIII11IIIII11IIIII11IIIII打开Excel文件!Dim filenam

23、e As StringWith CommonDialog1.DialogTitle ='打开 Excel文件".Filter = "(Excel)*.xls| *.xls".ShowOpenfilename = .filename' MsgBox filenameEnd WithDim xllApp As Excel.ApplicationDim xllBook As Excel.WorkbookDim xllSheet As Excel.WorksheetDim xllSheet1 As Excel.WorksheetDim StrRow As

24、String'Dim i As IntegerSet xllApp = CreateObject("Excel.Application")Set xllBook = xllApp.Workbooks.Open(filename)Set xllSheet = xllBook.Worksheets(l)引用第 1 张工作表Set xllSheet1 = xllBook.Worksheets(2)将数据写入到Excel单元格中With xllSheetFor i = 1 To WSNEXCEL = iStrRow = "B" & CStr(i + 2)'