VBnet操做Excel的代码.docx
- 文档编号:5223708
- 上传时间:2022-12-14
- 格式:DOCX
- 页数:55
- 大小:32.88KB
VBnet操做Excel的代码.docx
《VBnet操做Excel的代码.docx》由会员分享,可在线阅读,更多相关《VBnet操做Excel的代码.docx(55页珍藏版)》请在冰豆网上搜索。
VBnet操做Excel的代码
PrivateSubwriteToExcel(strTmp1()AsString,colTmp1AsCollection)
'
'Dimtmp1
Dimi1AsInteger,intColAsInteger,intRowAsInteger
DimxlAppAsNewExcel.Application
DimxlBookAsNewExcel.Workbook
DimxlSheetAsNewExcel.Worksheet
DimstrNameAsString,strArray1()AsString
DimstrS1AsString
DimstrD1AsString
strS1=CurrentProject.Path+"\template.xls"
strD1=CurrentProject.Path+"\"+CStr(Format(Now,"YYYYMMDDHHMMSS"))+"aaa1.xls"
'Fori1=0ToUBound(strTmp1)-1
'Debug.PrintstrTmp1(i1)+""+CStr(i1)
'Nexti1
'strName=CurrentProject.Path+"\aaa1.xls"
FileCopystrS1,strD1
SetxlApp=CreateObject("Excel.Application")
xlApp.Visible=False
'SetxlBook=xlApp.Workbooks.Open(strName)
SetxlBook=xlApp.Workbooks.Open(strD1)
SetxlSheet=xlBook.Worksheets
(1)
WithxlSheet
.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"
EndWith
intCol=1
intRow=21
Fori1=1TocolTmp1.Count
strArray1=colTmp1.Item(i1)
WithxlSheet
.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
EndWith
intRow=intRow+1
xlApp.ActiveSheet.Rows(intRow).Insert
Nexti1
xlApp.Visible=True
xlBook.Save
'xlBook.Close
SetxlSheet=Nothing
SetxlBook=Nothing
'xlApp.Quit
'tmp1=Shell(strName,1)
'hWndDesk=GetDesktopWindow()
'r=ShellExecute(hWndDesk,"Open",strName,vbNullString,0&,1)
EndSub
DimxlAppAsNewExcel.Application
DimxlBookAsNewExcel.Workbook
DimxlSheetAsNewExcel.Worksheet
PublicSubexportExcel()
'
DimstrA1()AsString,strA2()AsString,strTmp1AsString,strDATEAsString,strNameAsString,strValueAsString
DimintFieldLengthAsInteger,i1AsInteger,i2AsInteger,lngCountAsLong
Dimrs1AsDAO.Recordset
strTmp1="A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1,AA1,AB1,AC1,AD1,AE1,AF1,AG1,AH1,AI1,AJ1,AK1,AL1,AM1,AN1,AO1,AP1,AQ1,AR1,AS1,AT1,AU1,AV1,AW1,AX1,AY1,AZ1,BA1,BB1,BC1,BD1,BE1,BF1,BG1,BH1,BI1,BJ1,BK1,BL1,BM1,BN1,BO1,BP1,BQ1,BR1,BS1,BT1,BU1,BV1,BW1,BX1,BY1,BZ1,CA1,CB1,CC1,CD1,CE1,CF1,CG1,CH1,CI1,CJ1,CK1,CL1,CM1,CN1,CO1,CP1,CQ1,CR1,CS1,CT1,CU1,CV1,CW1,CX1,CY1,CZ1"
strA1=Split(strTmp1,",")
SetxlApp=CreateObject("Excel.Application")
xlApp.Visible=False
SetxlBook=xlApp.Workbooks.Add
strDATE=CStr(Format(Date,"YYYY-MM-DD"))
Me.CommonDialog1.DefaultExt="xls"
Me.CommonDialog1.Filename="帐单输出"+strDATE+".xls"
Me.CommonDialog1.Filter="EXCELFILE(*.xls)|*.xls"
Me.CommonDialog1.ShowSave
strName=Me.CommonDialog1.Filename
xlBook.SaveAsstrName
SetxlBook=xlApp.Workbooks.Open(strName)
SetxlSheet=xlBook.Worksheets
(1)
strSQL="SELECT*FROMHEADCOST1;"
Setrs1=CurrentDb.OpenRecordset(strSQL)
rs1.MoveLast
Debug.Printrs1.RecordCount
lngCount=rs1.RecordCount
intFieldLength=rs1.Fields.Count
'Debug.PrintintFieldLength
Debug.PrintintFieldLength
strA2()=Split(splitTable("HEADCOST1"),",")
Debug.PrintUBound(strA2)
WithxlSheet
Fori1=0TointFieldLength-1
Debug.Printi1
Debug.PrintstrA1(i1)
.Range(strA1(i1)).Value=getZValue(strA2(i1))
Nexti1
EndWith
Ifrs1.RecordCount<>0Then
rs1.MoveFirst
Fori1=1TolngCount
Fori2=1Tors1.Fields.Count
IfIsNull(rs1(i2-1))Then
strValue=""
Else
strValue=rs1(i2-1).Value
EndIf
xlSheet.Cells(i1+1,i2)=strValue
Nexti2
rs1.MoveNext
Nexti1
rs1.MoveFirst
Else
MsgBox"未读取到数据",vbCritical,"错误"
EndIf
xlBook.Save
xlBook.Close
SetxlSheet=Nothing
SetxlBook=Nothing
xlApp.Quit
SetxlApp=Nothing
rs1.Close
Setrs1=Nothing
EndSub
PrivateSubCommand1_Click()
SetxlApp=CreateObject("Excel.Application")
xlApp.Visible=False
SetxlBook=xlApp.Workbooks.Add
DimstrDateAsString,strNameAsString,strValueAsString
strDate=CStr(Format(Date,"yyyy-mm-dd"))
Me.CommonDialog1.DefaultExt="xls"
Me.CommonDialog1.FileName="SEND3B2"+strDate+".xls"
Me.CommonDialog1.Filter="EXCELFILE(*.xls)|*.xls"
Me.CommonDialog1.ShowSave
strName=Me.CommonDialog1.FileName
Debug.PrintstrName
xlBook.SaveAsstrName
SetxlBook=xlApp.Workbooks.Open(strName)
SetxlSheet=xlBook.Worksheets
(1)
'Fori1=0ToMe.DataGrid1.Columns.Count-1
'xlSheet.Cells(1,i1+1)=Me.DataGrid1.Columns.Item(j).Caption
'Nexti1
WithxlSheet
.Range("A1").Value="ORDERKEY"
.Range("B1").Value="EXTERNORDERKEY"
.Range("C1").Value="MM"
.Range("D1").Value="QTY"
.Range("E1").Value="PRODUCTDESP"
.Range("F1").Value="DIVISION"
.Range("G1").Value="MOQ"
.Range("H1").Value="OVERPACKQTY"
.Range("I1").Value="OVERPACK?
"
.Range("J1").Value="CTNQTY"
.Range("K1").Value="OPCTNQTY"
.Range("L1").Value="CTN_PALLET"
.Range("M1").Value="PALLETNO"
.Range("N1").Value="PALLETWEIGHT"
.Range("O1").Value="PALLETVOLUME"
.Range("P1").Value="PALLETLENGTH"
.Range("Q1").Value="PALLETWIDTH"
.Range("R1").Value="PALLETHIGH"
.Range("S1").Value="DELIVERYDATE"
.Range("T1").Value="CONSIGNEEKEY"
.Range("U1").Value="C_COUNTRY"
.Range("V1").Value="BILLTOKEY"
.Range("W1").Value="INCOTERM"
.Range("X1").Value="STATUS"
.Range("Y1").Value="INTERMODALVEHICLE"
.Range("Z1").Value="ORDERGROUP"
.Range("AA1").Value="HAWB"
.Range("AB1").Value="REQSHIPDATE"
.Range("AC1").Value="RELEASEDDATE"
.Range("AD1").Value="C_COMPANY"
EndWith
IfMe.Adodc1.Recordset.RecordCount<>0Then
Me.Adodc1.Recordset.MoveFirst
Fori1=1ToMe.Adodc1.Recordset.RecordCount
Fori2=1ToMe.Adodc1.Recordset.Fields.Count
IfIsNull(Me.Adodc1.Recordset.Fields(i2-1))Then
strValue=""
Else
strValue=Me.Adodc1.Recordset.Fields(i2-1).Value':
Debug.PrintstrValue
EndIf
xlSheet.Cells(i1+1,i2)=strValue
Nexti2
Me.Adodc1.Recordset.MoveNext
Nexti1
Me.Adodc1.Recordset.MoveFirst
Else
MsgBox"请先查询数据",vbCritical,"错误"
EndIf
xlBook.Save
xlBook.Close
SetxlSheet=Nothing
SetxlBook=Nothing
'xlApp.Visible=True
xlApp.Quit
SetxlApp=Nothing
EndSub
用VB操作Excel(VB6.0)(整理)
首先创建Excel对象,使用ComObj:
DimExcelIDasExcel.Application
SetExcelIDasnewExcel.Application
1)显示当前窗口:
ExcelID.Visible:
=True;
2)更改Excel标题栏:
ExcelID.Caption:
='应用程序调用MicrosoftExcel';
3)添加新工作簿:
ExcelID.WorkBooks.Add;
4)打开已存在的工作簿:
ExcelID.WorkBooks.Open('C:
\Excel\Demo.xls');
5)设置第2个工作表为活动工作表:
ExcelID.WorkSheets[2].Activate;
或ExcelID.WorkSheets['Sheet2'].Activate;
6)给单元格赋值:
ExcelID.Cells[1,4].Value:
='第一行第四列';
7)设置指定列的宽度(单位:
字符个数),以第一列为例:
ExcelID.ActiveSheet.Columns[1].ColumnsWidth:
=5;
8)设置指定行的高度(单位:
磅)(1磅=0.035厘米),以第二行为例:
ExcelID.ActiveSheet.Rows[2].RowHeight:
=1/0.035;//1厘米
9)在第8行之前插入分页符:
ExcelID.WorkSheets[1].Rows[8].PageBreak:
=1;
10)在第8列之前删除分页符:
ExcelID.ActiveSheet.Columns[4].PageBreak:
=0;
11)指定边框线宽度:
ExcelID.ActiveSheet.Range['B3:
D4'].Borders[2].Weight:
=3;
1-左2-右3-顶4-底5-斜(\)6-斜(/)
12)清除第一行第四列单元格公式:
ExcelID.ActiveSheet.Cells[1,4].ClearContents;
13)设置第一行字体属性:
ExcelID.ActiveSheet.Rows[1].Font.Name:
='隶书';
ExcelID.ActiveSheet.Rows[1].Font.Color:
=clBlue;
ExcelID.ActiveSheet.Rows[1].Font.Bold:
=True;
ExcelID.ActiveSheet.Rows[1].Font.UnderLine:
=True;
14)进行页面设置:
a.页眉:
ExcelID.ActiveSheet.PageSetup.CenterHeader:
='报表演示';
b.页脚:
ExcelID.ActiveSheet.PageSetup.CenterFooter:
='第&P页';
c.页眉到顶端边距2cm:
ExcelID.ActiveSheet.PageSetup.HeaderMargin:
=2/0.035;
d.页脚到底端边距3cm:
ExcelID.ActiveSheet.PageSetup.HeaderMargin:
=3/0.035;
e.顶边距2cm:
ExcelID.ActiveSheet.PageSetup.TopMargin:
=2/0.035;
f.底边距2cm:
ExcelID.ActiveSheet.PageSetup.BottomMargin:
=2/0.035;
g.左边距2cm:
ExcelID.ActiveSheet.PageSetup.LeftMargin:
=2/0.035;
h.右边距2cm:
ExcelID.ActiveSheet.PageSetup.RightMargin:
=2/0.035;
i.页面水平居中:
ExcelID.ActiveSheet.PageSetup.CenterHorizontally:
=2/0.035;
j.页面垂直居中:
ExcelID.ActiveSheet.PageSetup.CenterVertically:
=2/0.035;
k.打印单元格网线:
ExcelID.ActiveSheet.PageSetup.PrintGridLines:
=True;
15)拷贝操作:
a.拷贝整个工作表:
ExcelID.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域:
ExcelID.ActiveSheet.Range['A1:
E2'].Copy;
c.从A1位置开始粘贴:
ExcelID.ActiveSheet.Range.['A1'].PasteSpecial;
d.从文件尾部开始粘贴:
ExcelID.ActiveSheet.Range.PasteSpecial;
16)插入一行或一列:
a.ExcelID.ActiveSheet.Rows[2].Insert;
b.ExcelID.ActiveSheet.Columns[1].Insert;
17)删除一行或一列:
a.ExcelID.ActiveSheet.Rows[2].Delete;
b.ExcelID.ActiveSheet.Columns[1].Delete;
18)打印预览工作表:
ExcelID.ActiveSheet.PrintPreview;
19)打印输出工作表:
ExcelID.ActiveSheet.PrintOut;
20)工作表保存:
IfnotExcelID.ActiveWorkBook.Savedthen
ExcelID.ActiveSheet.PrintPreview
Endif
21)工作表另存为:
ExcelID.SaveAs('C:
\Excel\Demo1.xls');
22)放弃存盘:
ExcelID.ActiveWorkBook.Saved:
=True;
23)关闭工作簿:
ExcelID.WorkBooks.Close;
24)退出Excel:
ExcelID.Quit;
25)设置工作表密码:
ExcelID.ActiveSheet.Protect"123",DrawingObjects:
=True,Contents:
=True,Scenarios:
=True
26)EXCEL的显示方式为最大化
ExcelID
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- VBnet Excel 代码