Excel在储量地质报告附表排版中的应用

李光明

(山东省地质科学研究院，山东 济南 250013)

附表是储量地质报告的重要组成部分。生成格式统一、规范的附表，是储量地质报告编写中的一项重要工作。目前生成附表的方法，一是在Excel附表的基础上形成Word附表，然后进行排版；二是对Excel附表直接进行排版，并且排版过程都是人工完成的，过程繁琐，效率低下，难以做到规范、统一。为了解决以上问题，该文介绍一种在Excel 2010环境下，利用程序方式，快速高效地进行储量地质报告附表排版的方法。

1 排版要求

附表排版包括页面设置、表格格式设置和关键字栏目合并。

1.1 页面设置

页面布局包括纸张规格、纸张方向、页边距、居中方式、页眉页脚。

纸张规格为A4(297mm×210mm)，方向为横向。根据《党政机关公文格式》(GB/T 9704—2012)规定，标准页边距设置见图1，即左、右边界宽度为36mm，上、下边界宽度为27mm。居中方式为水平居中，页眉、页脚距上、下端距离为20mm。

图1 页边距设置/mm

1.2 表格格式

顶端标题行：1～3行，包括表题1行、栏目2行。表题行行高20磅，字号16磅；栏目行行高15.25磅，字号9磅。

数据区(表身)：行高15.25磅，字号9磅(表宽超过页宽时，字号自动缩小)，每页包括25行。

页码：包括2套页码，即在附表册中的总页码和所属附表的页次和页数。如图2中附表3，该表共5页，其第1页在附表册中的总页码为“- 3 -”(右下角)，自身页码为“第1页 共 5 页”(见右上角)。页码设于页脚和页眉中。

责任人、日期：设于页脚中。

单元格边框线型：默认的线型较粗，为此将其改为细线。

1.3 关键字栏目合并

为了便于阅读和使用，附表中勘查线编号、工程编号、矿体编号、品位等关键字栏目常需要进行合并。合并时不允许跨越上一级栏目和页。

图3为勘查线编号、工程编号、矿体编号栏目合并前、后对比图。

图2 表格格式示例

图3 关键字栏目合并前、后对比图

2 方法原理

(1)数据区行数=(第1个水平分页符的行号-1)-标题行行数，即(PageSetup.HPageBreaks(1).Location.Row-1)-标题行行数。图3中为(29-1)-3=25(行)。

(2)每个附表的页数：利用水平分页符(HPageBreaks.Count)计算页数。如果某个附表的最后1页满页(25行)，页数即等于HPageBreaks.Count，否则等于HpageBreaks.Count+1。

(3)每一个附表在全部附表中的起始页码，等于前继附表累计页数+1。如：该文中附表1为1页，附表2为1页，附表3为5页，则附表4的起始页码为(1+1+5)+1=8，即附表4的PageSetup.FirstPageNumber等于8。

在附表册中的总页码(&P)注记在右页脚中，因此，应将其赋值给PageSetup.RightFooter。

(4)每个附表自身页码“第 页 共 页”中，前者等于其页码(&P)-(PageSetup. FirstPageNumber-1)，后者等于该表的页数(&N)。其注记在右页眉中，因此，赋值给PageSetup.RightHeader即可。

(5)使表格不超宽：利用垂直分页符判断。若PageSetup.VPageBreaks.Count>0，表示表格超宽，这时缩小字号，直至表格压缩在1页中。如字号过小，可适当修改左、右边距。

3 排版程序

3.1 程序代码

包括页面和格式设置程序pPageSetup()、关键字栏目合并程序pColumnMerge()。

3.1.1 页面和格式设置程序

页面和格式设置程序如下：

SubpPageSetup()'参数设置nPageWidth=29.7'A4页面宽度(横向)nLeftMargin=3.6'左边距(cm)nRightMargin=3.6'右边距nTopMargin=2.7'上边距nBottomMargin=2.7'下边距nHeaderMargin=2'页眉nFooterMargin=2'页脚nCaptionRowHeight=20'标题行行高(磅,7.05mm)nCaptionFontSize=16'标题字号(磅,三号,5.64mm)nWordRowHeight=15.25'数据行行高cMaker="智标仁"'制表人(应根据情况修改制表人及日期)cExaminer="简姹"'检查人(应根据情况修改检查人及日期)cAuditor="沈河"'审核人(应根据情况修改审核人及日期)MakeDate="2017年7月29日"'制表日期ExaminationDate="2017年7月30日"'检查日期AuditDate="2017年7月31日"'审核日期Application.ScreenUpdating=False'屏幕刷新关闭Application.PrintCommunication=True'2010版启用与打印机通信,否则无法正常执行页眉、页脚的设置代码'Application.PrintCommunication=False'2003版关闭与打印机通信,提高代码运行效率'删除原目次页内容Worksheets("目次页").SelectWithSheets("目次页") nLines=.UsedRange.Rows.Count .Rows("4:"&nLines).DeleteShift:=xlUp .Rows("4:104").RowHeight=20 .Rows("4:104").Font.Size=10EndWithnSheets=Worksheets.Count'工作表个数Forsh=4TonSheets'前3个是封面、题名页和目次页,不是表 Worksheets(sh).Select WithActiveSheet nWordFontSize=9'数据字号(小五号,3.15mm) With.PageSetup .PrintTitleRows="$1:$3"'标题行 .Orientation=xlLandscape'横向打印模式(xlPortrait纵向打印模式) .PaperSize=xlPaperA4'A4纸 .LeftMargin=Application.CentimetersToPoints(nLeftMargin)'左边距 .RightMargin=Application.CentimetersToPoints(nRightMargin)'右边距 .TopMargin=Application.CentimetersToPoints(nTopMargin)'上边距 .BottomMargin=Application.CentimetersToPoints(nBottomMargin)'下边距 .HeaderMargin=Application.CentimetersToPoints(nHeaderMargin)'页眉 .FooterMargin=Application.CentimetersToPoints(nFooterMargin)'页脚 .CenterHorizontally=True'水平居中 .CenterVertically=False'垂直不居中 EndWith .Range("A4").Select ActiveWindow.FreezePanes=True'冻结1～3行 nLines=.UsedRange.Rows.Count'表的行数 nColumns=.UsedRange.Columns.Count'表的列数--数值 cColumns=IIf(nColumns<=26,Chr(64+nColumns),"A"&Chr(64+nColumns-26))'表的列数--字符,如:A、AA .Rows("1:1").RowHeight=nCaptionRowHeight'标题行行高 .Rows("1:1").Font.Size=nCaptionFontSize'标题行字号 .Rows("2:"&nLines).RowHeight=nWordRowHeight'栏目行、表身行高 .Rows("2:"&nLines).Font.Size=nWordFontSize .Columns("A:"&cColumns).EntireColumn.AutoFit'列自适应宽度 '若表格宽度超过1页,减小字号,使表格保持在1页中 wPage1=nPageWidth-nLeftMargin-nRightMargin'页面理论宽度(cm) wPage2=Application.CentimetersToPoints(wPage1)'页面理论宽度(磅) nTimes=8 DoWhile.VPageBreaks.Count>0'若表格宽度超过1页,则缩小 wSheet=.UsedRange.Columns.Width'表格实际宽度(磅) nWordFontSize=Round(nTimes*wPage2/wSheet,1)'计算字号(如字号过小,可适当修改左、右边距) .Rows("2:"&nLines).Font.Size=nWordFontSize'设置字号 .Columns("A:"&cColumns).EntireColumn.AutoFit'列自适应宽度 nTimes=nTimes-0.5'可酌情修改,如将0.5改为0.25 Loop

该程序同时生成目次页。

3.1.2 关键字栏目合并程序

关键字栏目合并程序如下：

SubpColumnMerge()'基本分析结果表关键字栏目合并'B列勘查线剖面编号、C列工程编号、D列矿体编号 nRows=ActiveSheet.UsedRange.Rows.Count'表的行数 i=4'表中数据行的首行号 Application.DisplayAlerts=False'信息警告关闭 nRowsPerPage=(ActiveSheet.HPageBreaks(1).Location.Row-1)-(i-1)'每页数据的行数 DoWhilei<=nRows'表 nPageRowNo=1'每页的初始行号 DoWhilenPageRowNo<=nRowsPerPageAndi<=nRows'页 pmRowNo1=i'剖面的起始行号 pmbh=Cells(i,"B")'剖面编号 DoWhileCells(i,"B")=pmbhAndnPageRowNo<=nRowsPerPageAndi<=nRows '剖面 gcRowNo1=i'工程的起始行号 gcbh=Cells(i,"C")'工程编号 DoWhilegcbh=Cells(i,"C")AndnPageRowNo<=nRowsPerPageAndi<=nRows'工程 ktRowNo1=i'矿体的起始行号 ktbh=Cells(i,"D")'矿体编号 DoWhilektbh=Cells(i,"D")Andgcbh=Cells(i,"C")And_ nPageRowNo<=nRowsPerPageAndi<=nRows'矿体 nPageRowNo=nPageRowNo+1'页的行号增1 i=i+1'表的行号增1 Loop ktRowNo2=i-1'矿体的终止行号 If""<>ktbhThen'非矿体不合并 Range(Cells(ktRowNo1,"D"),Cells(ktRowNo2,"D")).Merge'矿体编号合并 EndIf Loop Range(Cells(gcRowNo1,"C"),Cells(ktRowNo2,"C")).Merge'工程合并 Loop Range(Cells(pmRowNo1,"B"),Cells(ktRowNo2,"B")).Merge'剖面合并 LoopLoopApplication.DisplayAlerts=True'信息警告开启EndSub

该程序中，B列为勘查线编号、C列为工程编号、D列为矿体编号。若用户附表中的列号和程序中的不同，应对程序作相应修改。

3.2 程序操作

3.2.1 程序输入

分别将程序pPageSetup()、pColumnMerge()输入到【封面】、【基本分析】模块代码窗口中。以pPageSetup()为例，方法如下：

将鼠标置于【封面】标签之上，点击右键，弹出快捷菜单(图4)，单击【查看代码】，显示【封面】模块代码窗口(图5)，将程序代码输入其中。

图4 工作表标签快捷菜单

图5 代码窗口

3.2.2 程序运行

分别在【封面】、【基本分析】模块代码窗口中，将光标停留在代码范围内，按功能键【F5】或运行按钮(图5)即可运行。注意应先运行pPageSetup()，再运行pColumnMerge()。

页面和格式设置程序执行完成后，可通过【打印预览】观看效果。目次页和栏目合并结果，可直接浏览。

3.2.3 注意事项

(1)操作前，应将原始附表进行备份。

(2)程序执行前，应在3.1节程序中修改制表人、检查人和审核人及日期。若各附表的责任人不同，应在排版后修改。修改方法如下：

在图6【页面布局】选项卡【页面设置】功能区中单击【打印标题】，显示图7【页面设置】对话框，单击【页眉/页脚】/【页脚】，可进入修改。

图6 【打印标题】功能组

图7 【页面设置】对话框

(3)页面参数若需要调整，用户可在3.1节程序中进行修改，或在图7【页边距】中进行修改。

(4)【封面】【题名页】【目次页】应置于工作簿开始位置(图4)，附表的顺序应根据规范的规定进行排列。【目次页】标签名称不可以更改，其他标签名称可由用户自由命名。

(5)若排版后表格较窄，用户可人工进行调整，包括调整左、右页边距，以达到美观的效果。

(6)将pCellMerge()程序拷贝到其他工作表模块代码窗口中，对关键字栏目列号作相应修改(或增减)，即可实现对其他附表关键字栏目的合并操作。

4 结语

该程序在多个储量地质报告中进行了成功应用，实现了快速排版和版式的规范、统一。希望该程序能给地质同仁编制地质报告附表提供预期的帮助。

[1] 伍远高.Excel VBA开发技术大全[M].北京：清华大学出版社，2009.

[2] 韩家国等编著.Excel VBA从入门到精通[M].北京：化学工业出版社，2010.

[3] 伍远高等编著.Excel VBA编程实战宝典[M].北京：清华大学出版社，2014.

[4] 冯宇,蒋向东,于立红主编.Excel 2010电子表格制作项目教程[M]. 上海：上海科学普及出版社,2015.

[5] Excel Home编著.Excel 2013应用大全[M]. 北京：人民邮电出版社,2015.

[6] Excel Home编著.Excel 2010 VBA实战技巧精粹[M].北京：人民邮电出版社，2015.

[7] 刘玉红,王攀登.Excel 2016高效办公[M]. 北京：清华大学出版社,2017.

[8] 固体矿产勘查报告格式规定(DZ/T 0131-1994)[S].

[9] 固体矿产勘查地质资料综合整理综合研究技术要求(DZ/T 0079-2015)[S].

[10] 固体矿产勘查工作规范(GB/T 33444-2016)[S].