|
|
PB中操作Excel的技巧集一
2007-09-12 12:011．创建Excel对象
eole=CREATEOBJECT("Excel.application")
2．添加新工作簿
eole.Workbooks.add
3．设置第3个工作表为激活工作表
eole.Worksheets("sheet3").Activate
4．打开指定工作簿
eole.Workbooks.Open("c:\temp\ll.xls")
5．显示Excel窗口
eole.visible=True
6．更改Excel标题栏
eole.Caption=″VFP应用程序调用Microsoft Excel″
7．给单元格赋值
eole.cells(1,4).value=XM(XM为数据库字段名)
8．设置指定列的宽度(单位：字符个数)
eole.ActiveSheet.Columns(1).ColumnWidth=5
9．设置指定行的高度(单位：磅)
eole.ActiveSheet.Rows(1).RowHeight=1/0.035 (设定行高为1厘米，1磅=0.035厘米)
10．在第18行之前插入分页符
eole.Worksheets(″Sheet1″).Rows(18).PageBreak=1
11．在第4列之前删除分页符
eole.ActiveSheet.Columns(4).PageBreak=0
12．指定边框线宽度(Borders参数如下)
ole.ActiveSheet.Range(″b3:d3″).Borders(2).Weight=3
13．设置四个边框线条的类型
eole.ActiveSheet.Range(″b3:d3″).Borders(2).LineStyle=1
(其中Borders参数：1－左、2－右、3－顶、4－底、5－斜、6－斜/；LineStyle值：1与7－细实、2－细虚、4－点虚、9－双细实线)
14．设置页眉
eole.ActiveSheet.PageSetup.CenterHeader=″报表1″
15．设置页脚
eole.ActiveSheet.PageSetup.CenterFooter=″第＆P页″
16．设置页眉到顶端边距为2厘米
eole.ActiveSheet.PageSetup.HeaderMargin=2/0.035
17．设置页脚到底边距为3厘米
eole.ActiveSheet.PageSetup.FooterMargin=3/0.035
18．设置顶边距为2厘米
eole.ActiveSheet.PageSetup.TopMargin=2/0.035
19．设置底边距为4厘米
eole.ActiveSheet.PageSetup.BottomMargin=4/0.035
20．设置左边距为2厘米
eole.ActiveSheet.PageSetup.LeftMargin=2/0.035
21．设置右边距为2厘米
eole.ActiveSheet.PageSetup.RightMargin=2/0.035
22．设置页面水平居中
eole.ActiveSheet.PageSetup.CenterHorizontally=True
23．设置页面垂直居中
eole.ActiveSheet.PageSetup.CenterVertically=True
24．设置页面纸张大小(1－窄行8 5 11 39－宽行14 11)
eole.ActiveSheet.PageSetup.PaperSize=1
25．打印单元格网线
eole.ActiveSheet.PageSetup.PrintGridlines=True
26．拷贝整个工作表
eole.ActiveSheet.UsedRange.Copy
27．拷贝指定区域
eole.ActiveSheet.Range(″A1:E2″).Copy
28．粘贴
eole.WorkSheet(″Sheet2″).Range(″A1″).PasteSpecial
29．在第2行之前插入一行
eole.ActiveSheet.Rows(2).Insert
30．在第2列之前插入一列
eole.ActiveSheet.Columns(2).Insert
31．设置字体
eole.ActiveSheet.Cells(2,1).Font.Name=″黑体″
32．设置字体大小
eole.ActiveSheet.Cells(1,1).Font.Size=25
33．设置字体为斜体
eole.ActiveSheet.Cells(1,1).Font.Italic=True
34．设置整列字体为粗体
eole.ActiveSheet.Columns(1).Font.Bold=True
35．清除单元格公式
eole.ActiveSheet.Cells(1,4).ClearContents
36．打印预览工作表
方法一：eole.ActiveSheet.PrintPreview
方法二：eole.ExecuteExcel4Macro("PRINT(1,,,1,,TRUE,,,,,,1,,,TRUE,,FALSE)")
37．打印输出工作表
方法一：eole.ActiveSheet.PrintOut
方法二：eole.ExecuteExcel4Macro("PRINT(1,,,1,,,,,,,,1,,,TRUE,,FALSE)")
38．工作表另为
eole.ActiveWorkbook.SaveAs(″c:\temp\22.xls″)
39．放弃存盘
eole.ActiveWorkbook.saved=True
40．关闭工作簿
eole.Workbooks.close
41．退出Excel
eole.quit
42. 合并
string worksheet, beginRowcol, EndRowCol
MyOLE.ActiveWorkBook.Sheets(worksheet).Range( BeginRowCol+":"+EndRowCol).Select
MyOLE.ActiveWorkBook.Sheets(worksheet).Range(BeginRowCol+":"+EndRowCol).Merge
43. sheet改名
MyOLE.ActiveWorkBook.Sheets(olename).select
MyOLE.ActiveWorkBook.Sheets(olename).name=newname
44. 举例
MyOLE=Create OLEObject
ConnectErr = MyOLE.ConnectToNewObject ("excel.Application")
MyOLE.visible=false
//打开指定的XLS文件激活workbooks
MyOLE.application.workbooks.Open (FilePath)
//对XLS文件进行了改动以后，在关闭该文件时是否需要向用户提出警告。
MyOLE.Application.DisplayAlerts = False
if isnull(MyOLE) then return -1 ;
if ConnectErr <0 then
choose case ConnectErr
case -1
messagebox('错误提示','无效的调用')
case -2
messagebox('错误提示','类名没发现')
case -3
messagebox('错误提示','对象不能创建')
case -4
messagebox('错误提示','文件不能连接')
case -5
messagebox('错误提示','不能连接现在的对象')
case -6
messagebox('错误提示','文件无效')
case -7
messagebox("错误提示","文件不存在或已经打开")
case -8
messagebox("错误提示","服务器不能装载选择的文件")
case -9
messagebox("错误提示","其他错误")
end choose
return -1
end if
PB中操作Excel的技巧集二2007-09-12 13:11ole_object.Columns(c).Select
//设置对齐方式
ole_object.Selection.HorizontalAlignment = -4131 //居左
ole_object.Selection.HorizontalAlignment = -4152 //居中
ole_object.Selection.HorizontalAlignment = -4108 //居右
//设置居中对齐
ole_object.Rows("1:1").HorizontalAlignment = 3
//设置字体
ole_object.Rows("1:1").Font.Name = "宋体"
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.FontStyle = "常规"
ole_object.Rows("1:1").Font.Size = 20
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Bold = false
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Strikethrough = False
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Superscript = False
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Subscript = False
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.OutlineFont = False
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Shadow = False
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.Underline = 1
//ole_object.Rows("1:" + string(ll_rowcnt)).Font.ColorIndex = 1
//ole_object.Rows("1:" + string(ll_rowcnt)).RowHeight = UnitsToPixels( dec ( dw_1.describe ( "DataWindow.Detail.Height" ) ) , yUnitsToPixels! )
//如何将数据导出到Excel并中合并单元格?
//这是我现在的代码，如何讲部门那一列内容一样的单元格合并？
Dim uExcel As Excel.Application
Dim uExcelBook As Excel.Workbook
Dim intI As Integer
Dim intFgRow As Integer
If fgEmployee.Rows > 1 Then
Set uExcel = New Excel.Application
uExcel.Visible = False
uExcel.SheetsInNewWorkbook = 1
Set uExcelBook = uExcel.Workbooks.Add
With uExcel.Rows(1).Font
.Name = "宋体"
.Size = 9
.ColorIndex = 3
End With
uExcel.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter
uExcel.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter
uExcel.Columns(1).ColumnWidth = 9
uExcel.ActiveSheet.Cells(1, 1).Value = "序号"
For intFgRow = 1 To intColsCount
uExcel.Columns(intFgRow + 1).ColumnWidth = 11
uExcel.ActiveSheet.Cells(1, intFgRow + 1).Value = "部门"
Next
uExcel.Columns(intColsCount + 2).ColumnWidth = 40
uExcel.ActiveSheet.Cells(1, intColsCount + 2).Value = "员工"
uExcel.Columns(intColsCount + 3).ColumnWidth = 40
uExcel.ActiveSheet.Cells(1, intColsCount + 3).Value = "合计"
End If
For intI = 1 To fgEmployee.Rows - 1
With uExcel.Rows(intI + 1).Font
.Name = "宋体"
.Size = 9
.ColorIndex = 1
End With
For intFgRow = 1 To fgEmployee.Cols - 1
uExcel.ActiveSheet.Cells(intI + 1, intFgRow).Value = Trim(fgEmployee.TextMatrix(intI, intFgRow))
Next
Next
uExcelBook.SaveAs (strFileName)
uExcel.Quit
uExcel.DisplayAlerts = True
Set uExcel = Nothing
Set uExcelBook = Nothing
先计算要合并从哪到哪。然后用代码实现，以下是我录的一段excel的宏，作用是将C2到C7的单元格合并。
Range("C2:C7").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
放在vb中使用的时候为：
with uExcelBook.Worksheets(1).Range("C2:C7")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
//PB中查找Excel程序，并将其设置为最新显示
Function Long SetWindowPos(Long l_hwnd,Long l_ord,Long l_x,Long l_y,Long l_dx,Long l_dy,Long uflag) Library "user32"
Function ULong FindWindow (Long ClassName, String windowname) Library "user32.dll" Alias For FindWindowA
String ls_bookname
UnsignedLong ll_excel_hwnd
ls_ bookname = ole_object.ActiveWorkbook.Name
ls_ bookname = "Microsoft Excel - "+ls_ bookname
ll_excel_hwnd = FindWindow(0, ls_ bookname)
If ll_excel_hwnd > 0 Then
SetWindowPos(ll_excel_hwnd, -2, 0, 0, 0, 0, 3) //Top
//SetWindowPos(hwnd, -1, 0, 0, 0, 0, 3) //TopMost
//SetWindowPos(Handle(g_parent_window), 1,0, 0, 0, 0, 3) //Bottom
End If |
|
|Archiver|PowerBuilder(PB)BBS社区
( 鲁ICP备2021027222号-1 )
