【已解决】EXCEL打印页间距的问题-已解决问题区-AUTOIT CN

koflion 发表于 2013-1-16 11:45:37

【已解决】EXCEL打印页间距的问题

本帖最后由 koflion 于 2013-1-17 04:04 编辑

请问如何设置excel打印页间距？？？？

现在情况是不同机器都是A4纸，但有的间距宽。。要打2页。。。没法固定。。。

顺便问问。。怎么设置页眉、页脚以及打印标题，先感谢啦！！

解决方式：$oEXCEL.ActiveSheet.pagesetup.TopMargin=1.8*2.85
非常感谢kevinch

代码如下：其中数据来源为2个数组，$tempC 为表头，$tempD为明细$EXCEL = _ExcelBookNew(0)
If @error = 1 Then
MsgBox(262144, "错误！", "本机未安装Office EXCEL,请安装后再试!")
Else
$Rows = 1
$EXCEL.DisplayAlerts = False ;不提示操作信息
$EXCEL.Worksheets("Sheet2").Delete ;删除Sheet2
$EXCEL.Worksheets("Sheet3").Delete ;删除Sheet3
$EXCEL.DisplayAlerts = True
$EXCEL.Worksheets("Sheet1").Activate
$EXCEL.Cells.VerticalAlignment = -4108 ;设置垂直对齐方式为居中
$EXCEL.Cells.HorizontalAlignment = -4108 ;设置水平对齐方式为居中
$EXCEL.ActiveSheet.Columns(1).ColumnWidth = 10 ;设置列宽
$EXCEL.ActiveSheet.Columns(2).ColumnWidth = 18
$EXCEL.ActiveSheet.Columns(3).ColumnWidth = 8
$EXCEL.ActiveSheet.Columns(4).ColumnWidth = 14
$EXCEL.ActiveSheet.Columns(5).ColumnWidth = 8
$EXCEL.ActiveSheet.Columns(6).ColumnWidth = 8.88
$EXCEL.ActiveSheet.Columns(7).ColumnWidth = 8
$EXCEL.ActiveSheet.Columns(8).ColumnWidth = 9
;$EXCEL.Windows(1).DisplayGridlines=False ;是否显示网格
;$EXCEL.Columns.AutoFit ;自动调整列以获得更佳视图
;$EXCEL.Rows.AutoFit ;自动调整行以获得更佳视图

$EXCEL.Rows($Rows).RowHeight = 20
_range($EXCEL, "A" & $Rows & ":H" & $Rows);合并单元格
_ExcelFontSetProperties($EXCEL, 1, 1, 1, 1, True, False, False)

If $tempC = 0 Then
_ExcelWriteCell($EXCEL, "<" & $QLKS & "> " & "汇总单", $Rows, 1)
Else
_ExcelWriteCell($EXCEL, "<" & $QLKS & "> " & "汇总单 (重打)", $Rows, 1)
EndIf
$Rows += 1
$EXCEL.Rows($Rows).RowHeight = 15
$EXCEL.workbooks(1).activesheet.range("A" & $Rows).numberformatlocal = "@" ;设置为文本格式
_ExcelWriteCell($EXCEL, "汇总单号", $Rows, 1)
_ExcelWriteCell($EXCEL, $tempC, $Rows, 2)
_ExcelWriteCell($EXCEL, "日期", $Rows, 3)
_ExcelWriteCell($EXCEL, $tempC, $Rows, 4)
_ExcelWriteCell($EXCEL, "时间", $Rows, 5)
_ExcelWriteCell($EXCEL, $tempC, $Rows, 6)
_ExcelWriteCell($EXCEL, "人员", $Rows, 7)
_ExcelWriteCell($EXCEL, $tempC, $Rows, 8)
$Rows += 1
$EXCEL.Rows($Rows).RowHeight = 15
_ExcelWriteCell($EXCEL, "编码", $Rows, 1)
_ExcelWriteCell($EXCEL, "名称", $Rows, 2)
_ExcelWriteCell($EXCEL, "单价", $Rows, 3)
_ExcelWriteCell($EXCEL, "规格", $Rows, 4)
_ExcelWriteCell($EXCEL, "申请数量", $Rows, 5)
_ExcelWriteCell($EXCEL, "冲减数量", $Rows, 6)
_ExcelWriteCell($EXCEL, "实发数量", $Rows, 7)
_ExcelWriteCell($EXCEL, "金额", $Rows, 8)
$Rows += 1
$KSRows = $Rows
$JE = 0
For $i = 1 To $tempD
$EXCEL.workbooks(1).activesheet.range("A" & $Rows).numberformatlocal = "@" ;设置为文本格式
For $A = 1 To 8
_ExcelWriteCell($EXCEL, $tempD[$i][$A], $Rows, $A)
If $A <= 4 And $A <> 3 Then _ExcelHorizontalAlignSet($EXCEL, $Rows, $A, $Rows, $A)
Next
$JE += $tempD[$i]
$Rows += 1
Next
_ExcelWriteCell($EXCEL, "合计", $Rows, 1)
_ExcelWriteCell($EXCEL, $tempD & " 种", $Rows, 2)
_ExcelWriteCell($EXCEL, "合计", $Rows, 7)
_ExcelWriteCell($EXCEL, $JE, $Rows, 8)
$Rows += 1
$EXCEL.Rows($Rows).RowHeight = 20
_range($EXCEL, "A" & $Rows & ":C" & $Rows)
_range($EXCEL, "E" & $Rows & ":H" & $Rows)
_ExcelWriteCell($EXCEL, "开始日期:" & $tempC, $Rows, 1)
_ExcelWriteCell($EXCEL, $tempC, $Rows, 4)
_ExcelWriteCell($EXCEL, "结束日期:" & $tempC, $Rows, 5)

If $HZDDY = 0 Then

If $tempC <> "" Then
$Rows += 1
$FYRows = $Rows
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
_ExcelWriteCell($EXCEL, "申请单", $Rows, 1)
$FYARR = StringSplit($tempC, ",")
$FYTYD = ""
$E = 1
For $D = 1 To $FYARR
If $E <= 11 Then
If $D = $FYARR Or $E = 11 Then
$FYTYD = $FYTYD & $FYARR[$D]
Else
$FYTYD = $FYTYD & $FYARR[$D] & ","
EndIf
Else
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
$E = 1
$FYTYD = ""
$FYTYD = $FYTYD & $FYARR[$D] & ","
If $D <> $FYARR Then
$Rows += 1
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
EndIf

EndIf
$E += 1
Next
If $Rows = $FYRows And $FYTYD <> "" Then
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
ElseIf $Rows <> $FYRows And $FYTYD <> "" Then
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
EndIf
$Rows += 1
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
_ExcelWriteCell($EXCEL, "申请单共计: " & $FYARR & " 张", $Rows, 2)
EndIf
If $tempC <> "" Then
$Rows += 1
$FYRows = $Rows
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
_ExcelWriteCell($EXCEL, "冲减单号", $Rows, 1)
$FYARR = StringSplit($tempC, ",")
$FYTYD = ""
$E = 1
For $D = 1 To $FYARR
If $E <= 11 Then
If $D = $FYARR Or $E = 11 Then
$FYTYD = $FYTYD & $FYARR[$D]
Else
$FYTYD = $FYTYD & $FYARR[$D] & ","
EndIf
Else
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
$E = 1
$FYTYD = ""
$FYTYD = $FYTYD & $FYARR[$D] & ","
If $D <> $FYARR Then
$Rows += 1
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
EndIf

EndIf
$E += 1
Next
If $Rows = $FYRows And $FYTYD <> "" Then
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
ElseIf $Rows <> $FYRows And $FYTYD <> "" Then
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
$EXCEL.workbooks(1).activesheet.range("B" & $Rows).numberformatlocal = "@" ;设置为文本格式
_ExcelWriteCell($EXCEL, $FYTYD, $Rows, 2)
EndIf
$Rows += 1
_range($EXCEL, "A" & $FYRows & ":A" & $Rows)
_range($EXCEL, "B" & $Rows & ":H" & $Rows)
_ExcelWriteCell($EXCEL, "冲减单共计: " & $FYARR & " 张", $Rows, 2)
EndIf
EndIf
$Rows += 1
$EXCEL.Rows($Rows).RowHeight = 20
_range($EXCEL, "E" & $Rows & ":F" & $Rows)
_range($EXCEL, "G" & $Rows & ":H" & $Rows)
_ExcelWriteCell($EXCEL, "打印时间", $Rows, 1)
_ExcelWriteCell($EXCEL, _NowCalc(), $Rows, 2)
_ExcelWriteCell($EXCEL, "打印", $Rows, 3)
_ExcelWriteCell($EXCEL, $LoginName, $Rows, 4)
_ExcelWriteCell($EXCEL, "签收", $Rows, 5)
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(2).LineStyle = 1
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(3).LineStyle = 1
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(7).LineStyle = 1
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(8).LineStyle = 1
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(9).LineStyle = 1
$EXCEL.Range("A" & $KSRows - 1 & ":H" & $Rows).Borders(10).LineStyle = 1

_GUICtrlListView_SetItemText($YPHZListView, $CKIndex, "已打", 4)
_ExcelBookSave($EXCEL)
$EXCEL.ActiveWorkbook.PrintOut()
_ExcelBookClose($EXCEL)
EndIf请问是不是从$EXCEL.ActiveWorkbook.PrintOut()这个函数上下手啊。PS：真心求教。。。希望能指点一下

kevinch 发表于 2013-1-16 12:58:27

你的代码呢？

koflion 发表于 2013-1-16 14:11:42

代码？我就问问有没有相关函数来操作而已。。

kevinch 发表于 2013-1-16 14:23:43

那我也只能随便答答：有

koflion 发表于 2013-1-16 15:46:59

搜索了下。。发现打印函数里面并没有定义页边距的参数:
一、打印当前工作簿 oExcel.ActiveWorkBook.PrintOut()　默认直接打印整个工作簿：
oExcel.ActiveWorkBook.PrintOut(1,oExcel.WorkSheets.count,1,.T.)　带参数的打印
参数说明：共四个参数
参数1-数值：当前工作簿中进行打印的起始工作表号(默认1)
参数2-数值：当前工作簿中进行打印的结束工作表号(默认最末)
参数3-数值：打印份数(默认1)
参数4-逻辑值：是预览打印还是直接打印：.T.-预览打印，.F.-直接打印(默认.F.)
二、打印当前工作表
oExcel.ActiveSheet.PrintOut()
参数说明：共八个参数
参数1-数值：起始页号，省略则默认为开始位置
参数2-数值：终止页号，省略则默认为最后一页
参数3-数值：打印份数，省略则默认为1份
参数4-逻辑值：是否预览，省略则默认为直接打印(.F.)
参数5-字符值：设置活动打印机名称，省略则为默认打印机
参数6-逻辑值：是否输出到文件，省略则默认为否(.F.)，若选.T.且参数8为空，则Excel提示输入要输出的文件名
参数7-逻辑值：输出类型，省略则默认为(.T.)逐份打印，否则逐页打印
参数8-字符值：当参数6为.T.时，设置要打印到的文件名
如：oExcel.ActiveSheet.PrintOut(1,3,6,.F.,"MyPrinter",.F.,.T.,,)　从1页打印到3页，打印6份，通过MyPrinter打印机来打印，并且，要逐份打印。

koflion 发表于 2013-1-16 15:53:41

找到修改页边距的宏,但是怎么把宏转换成函数执行呢？？

sheetA.OPG("PageSetup").OPS("LeftMargin", 3 * 28.5); // 左边距3
sheetA.OPG("PageSetup").OPS("RightMargin", 4 * 28.5); // 右边距4
sheetA.OPG("PageSetup").OPS("TopMargin", 1 * 28.5); // 上边距1
sheetA.OPG("PageSetup").OPS("BottomMargin", 2 * 28.5);// 下边距2
sheetA.OPG("PageSetup").OPS("HeaderMargin", 1.1 * 28.5); // 页眉1.1
sheetA.OPG("PageSetup").OPS("FooterMargin", 1.2 * 28.5); // 页脚1.2

kevinch 发表于 2013-1-16 19:07:03

$sheet(1).pagesetup.leftmargin=3*2.85

koflion 发表于 2013-1-17 04:04:57

$sheet(1).pagesetup.leftmargin=3*2.85
kevinch 发表于 2013-1-16 19:07 http://www.autoitx.com/images/common/back.gif

谢谢了

vv3509 发表于 2013-1-24 12:12:03

这个不错谢谢分享

曼菲士 发表于 2013-4-20 14:57:22

谢谢分享，学习了。

种菜砍柴 发表于 2016-1-20 15:14:57

学习，thank you！！！{:face (356):}

页: [1]

AUTOIT CN's Archiver

【已解决】EXCEL打印页间距的问题