WPSJSA宏编程（JS）：4.最常见的宏编程任务
下⾯我们通过⼀个例⼦，来了解⼀下⽇常宏编程中最常⾯临的任务（因为使⽤了全局表达式，请将【⼯具】》【选项】》【编译】》【禁⽌全局作⽤域表达式】取消勾选）：
1/*提供⼀些表格相关的⼯具函数与常数*/
2 const XLS = {
3//数据有效性类型枚举 Range.Validation.Add(...)⽅法的第⼀个参数
4 XlDVType : {
5 xlValidateInputOnly : 0,//仅在⽤户更改值时进⾏验证。
6 xlValidateWholeNumber : 1,//全部数值。
7 xlValidateDecimal : 2,//数值。
8 xlValidateList : 3,//值必须存在于指定列表中。
9 xlValidateDate : 4,//⽇期值。
10 xlValidateTime : 5,//时间值。
11 xlValidateTextLength : 6,//⽂本长度。
12 xlValidateCustom : 7//使⽤任意公式验证数据有效性。
13 },
14
15//常⽤颜⾊常数
16 Colors : {
17 Black : 0, //⿊⾊
18 DarkRed : 192, //深红
19 Red : 255, //红⾊
20 Orange : 49407, //橙⾊
21 Yellow : 65535, //黄⾊
22 LightGreen : 5296274, //浅绿
23 Green : 5287936, //绿⾊
24 LightBlue : 15773696, //浅蓝
25 Blue : 12611584, //蓝⾊
26 DarkBlue : 6299648, //深蓝
27 Purpose : 10498160, //紫⾊
28 Magenta : 0xFF00FF, //紫红⾊
29 Cyan : 0xFFFF00, //青⾊
30 White : 0xFFFFFF, //⽩⾊
31
32 },
33/*
34获取⿏标选取的单元格区域对象
35 prompt : 对话框提⽰信息
36 title : 对话框标题
37 must : 是否强制返回⼀个单元格区域对象
38*/
39 GetRange : function(prompt = '请⽤⿏标框选单元格区域',
40 title = '选取单元格区域', must = false) {
41if (!g.IsType(prompt, 'String') ||
42 !g.IsType(title, 'String') ||
43 !g.IsType(must, 'Boolean'))
44throw new TypeError('参数 prompt/title/must 分别必' +
45 '须是 String/String/Boolean 对象');
46
47if (must) title += '[必须]';
48
49while(true) {
50var rng = Application.InputBox(prompt, title, undefined,
51 undefined, undefined, undefined, undefined, 8);
52if (!must) break;
53if (must && (typeof rng == 'object')) break;
54 }
55
56return rng;
57 },
58
59/*获取指定列的列字母
60 columnIndex : 列序数，Number 类型
61*/
62 GetColumnLetter : function(columnIndex) {
63if (!g.IsType(columnIndex, 'Number'))
64throw new TypeError('参数 columnIndex 必须是⼀个数字');
65
66if (columnIndex <= 0 || columnIndex > 16384)
67throw new Error('Please make sure 1 <= columnIndex <= 16384.');
68
69 let address = ActiveSheet.Columns.Item(columnIndex).Address();
70return address.substr(1, address.indexOf(':') - 1);
71 },
72
73/*
74功能：为单元格区域的每个单元格值上⾯加（数字）或追加（⽂本）指定数据
75 value : 要加/追加的值
76 rng : ⽬标单元格区域
77*/
78 AddOrAppend : function(value, rng) {
79if (!(typeof value == 'string' ||
80typeof value == 'number'))
81throw new Error('Parameter "value" must be a number/string object.'); 82
83if (typeof rng == 'undefined')
84 rng = XLS.GetRange(undefined, undefined, true);
85
86if (structor.name != 'Range')
87throw new Error('Parameter "rng" must be a Range object.');
88
89for (let iArea = 1; iArea <= rng.Areas.Count; iArea++) {
90 let area = rng.Areas.Item(iArea);
91for (let iRow = 1; iRow <= area.Rows.Count; iRow++) {
92for (let iColumn = 1; iColumn <= area.Columns.Count; iColumn++) { 93 let cell = area.Cells.Item(iRow, iColumn);
94if (typeof cell.Value2 == 'undefined')
95 cell.Value2 = value;
96else
97 cell.Value2 += value;
98 }
99 }
100 }
101 },
102
103/*获取单元格区域的所有数据，如果有多个⼦区域，将返回⼀个多维数组，104否则，返回⼀维数组*/
105 GetValues : function(rng) {
106if (typeof rng == 'undefined')
107 rng = XLS.GetRange(undefined, undefined, true);
108
109if (structor.name != 'Range')
110throw new Error('Parameter "rng" must be a Range object.');
111
112 let result = [];
113for (let iArea = 1; iArea <= rng.Areas.Count; iArea++) {
114 let values = [];
115 let area = rng.Areas.Item(iArea);
116for (let iRow = 1; iRow <= area.Rows.Count; iRow++) {
117for (let iColumn = 1; iColumn <= area.Columns.Count; iColumn++) { 118 values.push(area.Cells.Item(iRow, iColumn).Value());
119 }
120 }
121 result.push(values);
122 }
123if (result.length == 1)
124return result[0];
125else
126return result;
127 },
128
129/*获取单元格区域的完全引⽤的地址*/
130 GetRangeFullAddress : function(rng) {
131if (typeof rng == 'undefined')
132 rng = XLS.GetRange(undefined, undefined, true);
133
134if (structor.name != 'Range')
135throw new Error('Parameter "rng" must be a Range object.');
136
137return "'" + rng.Worksheet.Parent.Path + "\\[" +
138 rng.Worksheet.Parent.Name + "]" + rng.Worksheet.Name +
139 "'!" + rng.Address();
140 },
141
142/*为单元格区域创建简单的边框*/
143 MakeSimpleBorders : function(rng, color, weight, lineStyle) {
144if (!XLS.IsRange(rng))
145throw new TypeError('参数 rng 必须是⼀个单元格区域对象');
146
147if (typeof color == 'undefined')
148 color = 0; //⿊⾊
149if (typeof color != 'number' ||
150 il(color) != color)
151throw new TypeError('参数 color 必须是⼀个整数');
152if (typeof weight == 'undefined')
153 weight = xlThin; //细
154if (typeof weight != 'number' ||
155 il(weight) != weight)
156throw new TypeError('参数 weight 必须是⼀个整数');
157if (typeof lineStyle == 'undefined')
158 lineStyle = xlContinuous;
159if (typeof lineStyle != 'number' ||
160 il(lineStyle) != lineStyle)
161throw new TypeError('参数 lineStyle 必须是⼀个整数');
162
163 let indices = [xlEdgeLeft, xlEdgeTop,
164 xlEdgeBottom, xlEdgeRight,
165 xlInsideHorizontal, xlInsideVertical];
166for (let index of indices) {
167 (obj=>{
168 obj.Weight = weight;
169 obj.LineStyle = lineStyle;
170 obj.Color = color;
171 })(rng.Borders.Item(index));
172 }
173 },
174
175/*判断⼀个对象是否是 Range 类型的对象*/
176 IsRange : function(rng) {
177return g.IsType(rng, 'Range');
178 },
179
180 SetFormatConditionByExampleRange : function() {
181 {//与⽤户交互，取得操作需要的输⼊
182//指定数据表所在的单元格区域
183 let title = '选取数据表';
184 let prompt = '请⽤⿏标框选你要按值表样例加设条件格式的⼯作' +
185 '表数据所在的单元格区域(请不要连带选中表头⾏)';
186var rngMain = XLS.GetRange(prompt, title, true);
187
188//指定值表样例所在的单元格区域
189 title = '选取值表样例';
190 prompt = '请⽤⿏标框选你要设置条件格式参照的值表样例所在的' +
191 '单元格区域（请确保设置了格式）';
192var rngExample = XLS.GetRange(prompt, title, true);
193
194//指定条件格式的基准列
195 title = '选取条件格式基准列';
196 prompt = '请⽤⿏标选取为数据表设置条件格式时的基准列';
197var rngBaseColumn;
198while(true) {
199 rngBaseColumn = XLS.GetRange(prompt, title, true);
200if (rngBaseColumn.Columns.Count > 1)
201 alert('此类型条件的基准列只能是⼀列，请只选择⼀个列');
202else {
203if (Application.Intersect(rngBaseColumn, rngMain) == undefined) 204 alert('你指定的基准列与之前选取的数据表之间没有交集，所以' + 205 '此列不能作为基准列，请重新选取');
206else
207break;
208 }
209 }
210 }
211
212 {//为条件格式准备需要的公式
213 let rngIntersect = Application.Intersect(rngBaseColumn, rngMain);
214 let addrFirstCell = rngIntersect.Cells.Item(1).Address();
215 let columnAddress = addrFirstCell.substr(
216 0, addrFirstCell.lastIndexOf('$'));
217var tempFormula = '=INDIRECT("Column" & ROW()) = "Value"';
218 tempFormula = place('Column', columnAddress);
219 }
220
221//从值表样例单元格区域创建可迭代对象，以迭代每个单元格
222 let ociCells = new OfficeCollectionIterator(rngExample);
223//按值表样例增加条件格式
224for (let cell of ociCells) {
225 let info = {
226 Value : cell.Value(),
227 BackColor : cell.Interior.Color,
228 };
229//因为是要写在公式中，双写可能存在的引号
230if (typeof info.Value === 'string')
231 info.Value = place('"', '""');
232 let fcFormula = place('Value', info.Value);
233 let formatCondition = rngMain.FormatConditions
234 .Add(xlExpression, -1, fcFormula, "",
235 undefined, undefined, undefined, undefined);
236//formatCondition.SetFirstPriority();
237 formatCondition.Interior.Color = info.BackColor
238 formatCondition.StopIfTrue = false;
239 }
240 },
241
242/*列出菜单栏清单*/
243 ListAllCommandBarsInTable : function() {
244 let cbs = new OfficeCollectionIterator(
245 Application.CommandBars);
246 let data = Enumerable.from(cbs)
247 .select((cb, i) => [i, cb.Name,
248 cb.NameLocal, cb.Type, cb.BuiltIn])
249 .toArray();
250//写数据到表
251 let writter = new XLSTableWritter(
252 '序号，名称，友好名，类型，内建?'.split(','), data, '菜单栏清单',
253 '类型有：\n0 => 默认菜单栏；\n1 => 菜单栏；\n2 => 快捷菜单'); 254 writter.WriteTo(new Range('B40'));
255 },
256
257
258 }
259/*⼀个数据表测试
260它向外静态提供了⼀个数据表的完整数据
261并以实例的⽅式围绕⼀个数据表，向⽤户初步测试各类相关对象与功能262*/
263 class XLSExample {
264 constructor(rng) {
265if (rng == null ||
266 rng == undefined ||
267 structor.name != 'Range')
268throw new TypeError('要求传⼊的 rng 参数是⼀个单元格区域对象'); 269
270this.TopLeftCell = rng.Cells.Item(1);
271this.RowCount = XLSExample.Data.length;
272this.ColumnCount = XLSExample.Headers.length;
273//标题区域
274this.TitleRange = this.TopLeftCell.Resize(1, this.ColumnCount); 275//表头区域
276this.HeadersRange = this.TopLeftCell.Offset(1, 0)
277 .Resize(1, this.ColumnCount);
278//主数据区域
279this.MainRange = this.TopLeftCell.Offset(1, 0)
280 .Resize(XLSExample.Data.length + 1, this.ColumnCount);
281this.TableRange = this.TopLeftCell.Resize(
282 XLSExample.Data.length + 2, XLSExample.Headers.length); 283this.IsTableCreated = false;
284this.Comment = null;
285this.Borders = null;
286this.Validation = null;
287this.FormatCondition = null;
288this.Sort = null;
289this.WriteData();
290 }
291
292//表格样例的标题
293 static get Title() {
294if (XLSExample._Title == undefined)
295 XLSExample._Title = '古名⼈成绩单';
296return XLSExample._Title;
297 }
298
299//表格样例的表头
300 static get Headers() {
301if (XLSExample._Headers == undefined)
302 XLSExample._Headers = ['姓名' , '性别',
303 '年龄', '语⽂', '数学', '外语'];
304return XLSExample._Headers;
305 }
306
307//表格样例的数据
308 static get Data() {
309if (XLSExample._Data == undefined)
310 XLSExample._Data = [
311 ['李⽩' , '男', 23, 99, 57, 80],
312 ['赵云' , '男', 32, 77, 63, 55],
313 ['貂蝉' , '⼥', 18, 80, 80, 80],
314 ['李清照', '⼥', 25, 98, 66, 90],
315 ['赵佶' , '男', 54, 96, 33, 82],
316 ['武曌' , '⼥', 78, 65, 66, 63],
317 ['⼒⼠' , '阉', 55, 79, 67, 77],
318 ['赵⾼' , '阉', 43, 82, 88, 83],
319 ['⽞奘' , '僧', 56, 78, 54, 98],
320 ['罗麽' , '僧', 42, 88, 77, 66]
321 ];
322return XLSExample._Data;
323 }
324
325//将数据写到初始化时的单元格位置
326 WriteData() {
327//写标题数据
328this.TitleRange.Merge();
329this.TitleRange.Cells.Item(1).Value2 = XLSExample.Title;
330//写表头
331this.HeadersRange.Value2 = XLSExample.Headers;
332//写表内容
333for (let i = 0; i < XLSExample.Data.length; i++) {
334this.TopLeftCell.Offset(i + 2).Resize(1,
335this.ColumnCount).Value2 = XLSExample.Data[i];
336 }
337 }
338
339//添加批注，并保存创建的批注对象的引⽤，以备更多探索访问340 AddComment() {
341this.TitleRange.ClearComments();
342 let comment = this.TopLeftCell.AddComment();
343 comment.Visible = true;
344 let now = new w());
345 comment.Text('批注添加于：\n' + LocaleString());
346 comment.Shape.Width = 136;
347 comment.Shape.Height = 30;
348//这⾥只右移了 2 列，可见单元格区域是否被合并，会影响
349//Range.Offset() ⽅法的功能
350 let rngLocation = this.TopLeftCell.Offset(8, 2);
351 comment.Shape.Left = rngLocation.Left;
352 comment.Shape.Top = rngLocation.Top;
353this.Comment = comment;
354 }
355
356//给单元格区域添加边框
357 AddBorders() {
358 let borders = this.MainRange.Borders;
359//1.外边框
360for (let iBorder of [
361 xlEdgeBottom,
javascript程序设计软件
362 xlEdgeLeft,
363 xlEdgeRight,
364 xlEdgeTop
365 ]) {
366 let border = borders.Item(iBorder);
367 border.Color = XLS.Colors.Blue/*color：蓝⾊*/;
368 border.LineStyle = xlDouble/*lineStyle：双实线*/;
369 border.Weight = xlMedium/*weight：中等粗细*/;
370 }
371//2.内边框
372for (let iBorder of [xlInsideHorizontal, xlInsideVertical]) {
373 let border = borders.Item(iBorder);
374 border.Color = XLS.Colors.Red/*color：红⾊*/;
375 border.LineStyle = xlDot/*lineStyle：点线*/;
376 border.Weight = xlThin/*weight：细线*/;
377 }
378/*
379 //3.斜边框
380 for (let iBorder of [xlDiagonalDown, xlDiagonalUp]){
381 let border = borders.Item(iBorder);
382 border.Color = XLS.Colors.Blue; //color：蓝⾊
383 border.LineStyle = xlContinuous;//lineStyle：实线
384 border.Weight = xlThin;//weight：细线
385 }
386*/
387
388//最后，留存边框对象的引⽤，以备更多探索访问
389this.Borders = borders;
390 }
391
392//设置字体与对齐⽅式
393 SetFontAndAlignment() {
394//将表标题加粗，并⽔平分散缩进5对齐
395 (obj=>{
396 obj.Font.Bold = true;
397 obj.HorizontalAlignment = xlHAlignDistributed;
398 obj.VerticalAlignment = xlVAlignCenter;
399 obj.AddIndent = false;
400 obj.IndentLevel = 5;
401 })(this.TitleRange);
402//将表头⾏加粗
403this.HeadersRange.Font.Bold = true;
404//设置主区域的第⼀列为楷体，⽔平分散对齐
405 let rngFirstColumn = this.MainRange.Columns.Item(1);
406 rngFirstColumn.HorizontalAlignment = xlHAlignDistributed; 407 rngFirstColumn.Font.Name = '楷体';
408//设置主区域除第⼀列以外的区域，⽔平居中对齐
409for (let iColumn = 2; iColumn <= this.ColumnCount; iColumn++) 410this.MainRange.Columns.Item(iColumn)
411 .HorizontalAlignment = xlHAlignCenter;
412 }
413
发表评论