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

PAGE\*MERGEFORMAT#/10PAGE\*MERGEFORMAT#/10PrivateSubCommand4_Click()OnErrorResumeNextI!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!CreateExcelTable''''''''''''''''''''''''''''''''''''''''''DimxlAppAsExcel.ApplicationDimxlBookAsExcel.WorkbookDimxlSheetAsExcel.WorksheetDimxlSheet1AsExcel.WorksheetDimiAsInteger,tmHourAsStringI!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!OnErrorResumeNextI!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!SetxlApp=Excel.ApplicationSetxlBook=xlApp.Workbooks.Add'xlBook.ActivateSetxlSheet=xlBook.Worksheets(1)'''''''''''''''''''''''''''''''''引用第1张工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf xlApp.ActiveSheet.Rows.VerticalAlignment=xlVAlignCenter'''垂直方向居中''xlApp.ActiveSheet.Rows.HorizontalAlignment=xlVAlignCenter'水平方向居中xlSheet.Name="实测值"SetxlSheet1=xlBook.Worksheets(2)xlSheet1.Name="Chart"WithxlSheetFori=2To11.Range(Cells(1,1),Cells(1,i)).Merge合并A-K单元格Next'.Cells(1,1).ForeColor=RGB(100,150,255).Cells(1,1).Font.Size=25设置行高'设置列宽Fori=1To22.Rows(i).RowHeight=25NextFori=1To11.Columns(i).ColumnWidth=15Next合并单元格Fori=3To22Ifi<8Then单元格单元格框色.Range(Cells(3,1),Cells(i,1)).Merge”“皿於并A3-A7.Range(Cells(3,8),Cells(i,8)).Merge'''''''''''合并H3-H7ElseIfi<13Then.Range(Cells(8,1),Cells(i,1)).Merge.Range(Cells(8,8),Cells(i,8)).MergeElseIfi<18Then.Range(Cells(13,1),Cells(i,1)).Merge.Range(Cells(13,8),Cells(i,8)).MergeElseIfi<23Then.Range(Cells(18,1),Cells(i,1)).Merge.Range(Cells(18,8),Cells(i,8)).MergeEndIfNext''''''''''''''''''''''''''''''''''''''''''''.Range("A1","K22").Borders.LineStyle=xlContinuous单元格边.Range("A1","K22").Borders.Color=vbBlue'''''''''''''''''边框颜色.Range("A1","K22").Interior.Color=RGB(100,180,0)'''''区域背景'''''''''''''''''''''''''''''''.Range("A1").Value="iWatt项目”.Range("A1").Font.Color=vbRed''''''''''''''设置字体颜色.Range("A1").Font.Name="楷书"''''''''''''''设置字体字型.Range("A1").Font.Size=30''''''''''''''''''设置字体字号'''''''''''''''''''''''''''''''''''''''.Range("A2").Value="输入电压(VAC)"TOC\o"1-5"\h\z.Range("B2").Value="输入功率(W)".Range("C2").Value=输出电压"(V)".Range("D2").Value="输出电流mA)".Range("E2").Value=输出功率"(W)".Range("F2").Value="纹波电压(A)".Range("G2").Value="效率(%)".Range("H2").Value="过流点(A)".Range("I2").Value="初级到次级功率损耗(W)".Range("J2").Value=平均功率"%".Range("K2").Value=需符合CECfe准”'''''''''''''''''''''''''''''''''''电压值.Range("A3").Value="90".Range("A8").Value="115".Range("A13").Value="230".Range("A18").Value="264"I!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!负载值.Range("D3").Value="0".Range("D4").Value="1/4Load".Range("D5").Value="2/4Load".Range("D6").Value="3/4Load".Range("D7").Value="FullLoad".Range("D8").Value="0".Range("D9").Value="1/4Load".Range("D10").Value="2/4Load.Range("D11").Value="3/4Load.Range("D12").Value="FullLoad".Range("D13").Vaue="0".Range("D14").Vaue="1/4Load".Range("D15").Vaue="2/4Load".Range("D16").Vaue="3/4Load".Range("D17").Vaue="FullLoad".Range("D18").Vaue="0".Range("D19").Vaue="1/4Load".Range("D20").Vaue="2/4Load".Range("D21").Vaue="3/4Load".Range("D22").Vaue="FullLoad"EndWithtmHour="-"&Hour(Time)tmHour=tmHour&"-"&Minute(Time)tmHour=tmHour&"-"&Second(Time)xlApp.ActiveWorkbook.SaveAsApp.Path&"\"&Format(Date,dddd,mmmm,yyyy)&tmHour+".xls"xlApp.Workbooks.ClosexlApp.QuitSetxlApp=Nothing释放引用'I!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!写入数据'''''''''''''''''''''''''''''''''''''''''''''Dimj,LengthTXT,k,Num,NEXCELAsIntegerDimStrTxtAsStringOnErrorResumeNextI!!!!!!!!!!!!!!!!!!!!!!!!!!计算数组的围数NUMLengthTXT=Len(Text1.Text)StrTxt=Text1.TextNum=1Fori=1ToLengthTXTIfMid(Text1.Text,i,1)=","ThenNum=Num+1EndIfNextReDimStrDataArray(Num)重定义围数I!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!''''''''''''''''''''''''''''''赋值给数组StrDataArrayIfNum=1ThenStrDataArray(Num)=StrTxtElseFori=1ToLengthTXTStrData=StrData&Mid(StrTxt,i,1)k=k+1IfMid(StrTxt,i,1)=","ThenStrDataArray(j)=Left(StrData,k-1)StrData=""k=0EndIfStrDataArray(Num)=StrDataNextEndIf''''''''''''''''''''''''''''''checkStrDataArray(i)'Fori=1ToNum'MsgBoxStrDataArray(i)&""&i'NextI!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!数值分段存储到数组,每组为一个实测值DimTowArray()AsStringDimWS,NAsIntegerWS=Num\4'''''''''''''''''围数ReDimTowArray(WS,4)Fori=1ToNum-2N=i\4Forj=1To4'Ifi\4=0ThenTowArray(N+1,j)=StrDataArray(j+4*N)'EndIfNextNext'''''''''''''''''''''''''''''''''checkTowArray(N+1,j)'Fori=1ToWS'MsgBoxTowArray(i,1)&TowArray(i,2)&TowArray(i,3)&TowArray(i,4)'Next'''''''''''''''''''''''''''''''''''数值转换''''第4个字节转换为2进制ReDimByteDataString(WS)Fori=1ToNum\4'MsgBoxTowArray(i,4)MsgBoxCStr(TowArray(i,4))ByteDataString(i)=HexToByte(CStr(TowArray(i,4)))''转换为'''2进制,8位'MsgBoxByteDataString(i)&""&iNextI!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!打开Excel文件!DimfilenameAsStringWithCommonDialog1.DialogTitle='打开Excel文件”.Filter="(Excel)*.xls|*.xls".ShowOpenfilename=.filename'MsgBoxfilenameEndWithI!!!!!!!!!!!!!!!!!!!!!!!!DimxllAppAsExcel.ApplicationDimxllBookAsExcel.WorkbookDimxllSheetAsExcel.WorksheetDimxllSheet1AsExcel.WorksheetDimStrRowAsStringDimiAsIntegerSetxllApp=CreateObject("Excel.Application")SetxllBook=xllApp.Workbooks.Open(filename)SetxllSheet=xllBook.Worksheets(l)弓I用第1张工作表SetxllSheet1=xllBook.Worksheets(2)I!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!将数据写入到Excel单元格中WithxllSheetFori=1ToWSNEXCEL=iStrRow="B"&CStr(i+2)'MsgBoxByteDataString(i)'&StrRow.Range(StrRow).Value=ValueOfData(ByteDataString(i),NEXCEL设置一个返回函数NextEndWithSetct=xllApp.Worksheets("Chart").ChartObjects.Add(100,40,300,350)插入'图形''位置(10，40)为图形位置，(220，120)为图形的大小ct.Chart.ChartType=xlLineStacked'xlColumnClustered测值'块状图'xl3DColumnStacked立I!直修圈'xl3DPie'图形类型为饼图ct.Chart.SetSourceDataPlotBy:=xlColumnsWithct.Chart.HasTitle=True.ChartTitle.Characters.Font.Size=20.ChartTitle.Characters.Text=折线图""'图表标 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 为饼图.ChartTitle.Shadow=True''''''标题添加边框EndWithct.Chart.ApplyDataLabels2,True标志旁附图例项标志***标志数值xllBook.SavexllApp.ActiveWorkbook.SavexllApp.Application.QuitSetxllApp=Nothing'表忘释放引用EndSubSource尸Sheets供”).Range("B3:B6"),