WPS JSA 宏编程（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) { 41 if (!g.IsType(prompt, 'String') || 42 !g.IsType(title, 'String') || 43 !g.IsType(must, 'Boolean')) 44 throw new TypeError('参数 prompt/title/must 分别必' + 45 '须是 String/String/Boolean 对象'); 46 47 if (must) title += '[必须]'; 48 49 while(true) { 50 var rng = Application.InputBox(prompt, title, undefined, 51 undefined, undefined, undefined, undefined, 8); 52 if (!must) break; 53 if (must && (typeof rng == 'object')) break; 54 } 55 56 return rng; 57 }, 58 59 /*获取指定列的列字母 60 columnIndex : 列序数，Number 类型 61 */ 62 GetColumnLetter : function(columnIndex) { 63 if (!g.IsType(columnIndex, 'Number')) 64 throw new TypeError('参数 columnIndex 必须是一个数字'); 65 66 if (columnIndex <= 0 || columnIndex > 16384) 67 throw new Error('Please make sure 1 <= columnIndex <= 16384.'); 68 69 let address = ActiveSheet.Columns.Item(columnIndex).Address(); 70 return address.substr(1, address.indexOf(':') - 1); 71 }, 72 73 /* 74 功能：为单元格区域的每个单元格值上面加（数字）或追加（文本）指定数据 75 value : 要加/追加的值 76 rng : 目标单元格区域 77 */ 78 AddOrAppend : function(value, rng) { 79 if (!(typeof value == 'string' || 80 typeof value == 'number')) 81 throw new Error('Parameter "value" must be a number/string object.'); 82 83 if (typeof rng == 'undefined') 84 rng = XLS.GetRange(undefined, undefined, true); 85 86 if (rng.constructor.name != 'Range') 87 throw new Error('Parameter "rng" must be a Range object.'); 88 89 for (let iArea = 1; iArea <= rng.Areas.Count; iArea++) { 90 let area = rng.Areas.Item(iArea); 91 for (let iRow = 1; iRow <= area.Rows.Count; iRow++) { 92 for (let iColumn = 1; iColumn <= area.Columns.Count; iColumn++) { 93 let cell = area.Cells.Item(iRow, iColumn); 94 if (typeof cell.Value2 == 'undefined') 95 cell.Value2 = value; 96 else 97 cell.Value2 += value; 98 } 99 } 100 } 101 }, 102 103 /*获取单元格区域的所有数据，如果有多个子区域，将返回一个多维数组， 104 否则，返回一维数组*/ 105 GetValues : function(rng) { 106 if (typeof rng == 'undefined') 107 rng = XLS.GetRange(undefined, undefined, true); 108 109 if (rng.constructor.name != 'Range') 110 throw new Error('Parameter "rng" must be a Range object.'); 111 112 let result = []; 113 for (let iArea = 1; iArea <= rng.Areas.Count; iArea++) { 114 let values = []; 115 let area = rng.Areas.Item(iArea); 116 for (let iRow = 1; iRow <= area.Rows.Count; iRow++) { 117 for (let iColumn = 1; iColumn <= area.Columns.Count; iColumn++) { 118 values.push(area.Cells.Item(iRow, iColumn).Value()); 119 } 120 } 121 result.push(values); 122 } 123 if (result.length == 1) 124 return result[0]; 125 else 126 return result; 127 }, 128 129 /*获取单元格区域的完全引用的地址*/ 130 GetRangeFullAddress : function(rng) { 131 if (typeof rng == 'undefined') 132 rng = XLS.GetRange(undefined, undefined, true); 133 134 if (rng.constructor.name != 'Range') 135 throw new Error('Parameter "rng" must be a Range object.'); 136 137 return "'" + 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) { 144 if (!XLS.IsRange(rng)) 145 throw new TypeError('参数 rng 必须是一个单元格区域对象'); 146 147 if (typeof color == 'undefined') 148 color = 0; //黑色 149 if (typeof color != 'number' || 150 Math.ceil(color) != color) 151 throw new TypeError('参数 color 必须是一个整数'); 152 if (typeof weight == 'undefined') 153 weight = xlThin; //细 154 if (typeof weight != 'number' || 155 Math.ceil(weight) != weight) 156 throw new TypeError('参数 weight 必须是一个整数'); 157 if (typeof lineStyle == 'undefined') 158 lineStyle = xlContinuous; 159 if (typeof lineStyle != 'number' || 160 Math.ceil(lineStyle) != lineStyle) 161 throw new TypeError('参数 lineStyle 必须是一个整数'); 162 163 let indices = [xlEdgeLeft, xlEdgeTop, 164 xlEdgeBottom, xlEdgeRight, 165 xlInsideHorizontal, xlInsideVertical]; 166 for (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) { 177 return g.IsType(rng, 'Range'); 178 }, 179 180 SetFormatConditionByExampleRange : function() { 181 {//与用户交互，取得操作需要的输入 182 //指定数据表所在的单元格区域 183 let title = '选取数据表'; 184 let prompt = '请用鼠标框选你要按值表样例加设条件格式的工作' + 185 '表数据所在的单元格区域(请不要连带选中表头行)'; 186 var rngMain = XLS.GetRange(prompt, title, true); 187 188 //指定值表样例所在的单元格区域 189 title = '选取值表样例'; 190 prompt = '请用鼠标框选你要设置条件格式参照的值表样例所在的' + 191 '单元格区域（请确保设置了格式）'; 192 var rngExample = XLS.GetRange(prompt, title, true); 193 194 //指定条件格式的基准列 195 title = '选取条件格式基准列'; 196 prompt = '请用鼠标选取为数据表设置条件格式时的基准列'; 197 var rngBaseColumn; 198 while(true) { 199 rngBaseColumn = XLS.GetRange(prompt, title, true); 200 if (rngBaseColumn.Columns.Count > 1) 201 alert('此类型条件的基准列只能是一列，请只选择一个列'); 202 else { 203 if (Application.Intersect(rngBaseColumn, rngMain) == undefined) 204 alert('你指定的基准列与之前选取的数据表之间没有交集，所以' + 205 '此列不能作为基准列，请重新选取'); 206 else 207 break; 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('$')); 217 var tempFormula = '=INDIRECT("Column" & ROW()) = "Value"'; 218 tempFormula = tempFormula.replace('Column', columnAddress); 219 } 220 221 //从值表样例单元格区域创建可迭代对象，以迭代每个单元格 222 let ociCells = new OfficeCollectionIterator(rngExample); 223 //按值表样例增加条件格式 224 for (let cell of ociCells) { 225 let info = { 226 Value : cell.Value(), 227 BackColor : cell.Interior.Color, 228 }; 229 //因为是要写在公式中，双写可能存在的引号 230 if (typeof info.Value === 'string') 231 info.Value = info.Value.replace('"', '""'); 232 let fcFormula = tempFormula.replace('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) { 265 if (rng == null || 266 rng == undefined || 267 rng.constructor.name != 'Range') 268 throw new TypeError('要求传入的 rng 参数是一个单元格区域对象'); 269 270 this.TopLeftCell = rng.Cells.Item(1); 271 this.RowCount = XLSExample.Data.length; 272 this.ColumnCount = XLSExample.Headers.length; 273 //标题区域 274 this.TitleRange = this.TopLeftCell.Resize(1, this.ColumnCount); 275 //表头区域 276 this.HeadersRange = this.TopLeftCell.Offset(1, 0) 277 .Resize(1, this.ColumnCount); 278 //主数据区域 279 this.MainRange = this.TopLeftCell.Offset(1, 0) 280 .Resize(XLSExample.Data.length + 1, this.ColumnCount); 281 this.TableRange = this.TopLeftCell.Resize( 282 XLSExample.Data.length + 2, XLSExample.Headers.length); 283 this.IsTableCreated = false; 284 this.Comment = null; 285 this.Borders = null; 286 this.Validation = null; 287 this.FormatCondition = null; 288 this.Sort = null; 289 this.WriteData(); 290 } 291 292 //表格样例的标题 293 static get Title() { 294 if (XLSExample._Title == undefined) 295 XLSExample._Title = '古名人成绩单'; 296 return XLSExample._Title; 297 } 298 299 //表格样例的表头 300 static get Headers() { 301 if (XLSExample._Headers == undefined) 302 XLSExample._Headers = ['姓名' , '性别', 303 '年龄', '语文', '数学', '外语']; 304 return XLSExample._Headers; 305 } 306 307 //表格样例的数据 308 static get Data() { 309 if (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 ]; 322 return XLSExample._Data; 323 } 324 325 //将数据写到初始化时的单元格位置 326 WriteData() { 327 //写标题数据 328 this.TitleRange.Merge(); 329 this.TitleRange.Cells.Item(1).Value2 = XLSExample.Title; 330 //写表头 331 this.HeadersRange.Value2 = XLSExample.Headers; 332 //写表内容 333 for (let i = 0; i < XLSExample.Data.length; i++) { 334 this.TopLeftCell.Offset(i + 2).Resize(1, 335 this.ColumnCount).Value2 = XLSExample.Data[i]; 336 } 337 } 338 339 //添加批注，并保存创建的批注对象的引用，以备更多探索访问 340 AddComment() { 341 this.TitleRange.ClearComments(); 342 let comment = this.TopLeftCell.AddComment(); 343 comment.Visible = true; 344 let now = new Date(Date.now()); 345 comment.Text('批注添加于 ：\n' + now.toLocaleString()); 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; 353 this.Comment = comment; 354 } 355 356 //给单元格区域添加边框 357 AddBorders() { 358 let borders = this.MainRange.Borders; 359 //1.外边框 360 for (let iBorder of [ 361 xlEdgeBottom, 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.内边框 372 for (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 //最后，留存边框对象的引用，以备更多探索访问 389 this.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 //将表头行加粗 403 this.HeadersRange.Font.Bold = true; 404 //设置主区域的第一列为楷体，水平分散对齐 405 let rngFirstColumn = this.MainRange.Columns.Item(1); 406 rngFirstColumn.HorizontalAlignment = xlHAlignDistributed; 407 rngFirstColumn.Font.Name = '楷体'; 408 //设置主区域除第一列以外的区域，水平居中对齐 409 for (let iColumn = 2; iColumn <= this.ColumnCount; iColumn++) 410 this.MainRange.Columns.Item(iColumn) 411 .HorizontalAlignment = xlHAlignCenter; 412 } 413 414 //给单元格区域添加数据有效性验证 415 AddValidation() { 416 let iColumn = XLSExample.Headers.indexOf('性别') + 1; 417 let rngValidation = this.MainRange.Columns.Item(iColumn); 418 rngValidation.Validation.Delete(); 419 rngValidation.Validation.Add( 420 /*JSA不支持 XlDVType 枚举，在此用全局变量，模拟自定义了一个 Enum，为使 JSA 能编译 421 通过，请确保【工具】》【选项】》【编译】》【禁止全局作用域表达式】处于未被勾选状态*/ 422 XLS.XlDVType.xlValidateList, 423 xlValidAlertStop, xlBetween, "男,女,阉,僧", undefined); 424 (obj => { 425 obj.InputTitle = "性别"; 426 obj.InputMessage = "允许的性别是：男,女,阉,僧"; 427 obj.ErrorTitle = "数据非法"; 428 obj.ErrorMessage = "只接受“男,女,阉,僧”四种性别"; 429 obj.InCellDropdown = true; 430 })(rngValidation.Validation); 431 432 //留存数据有效对象的引用，以备更多探索访问 433 this.Validation = rngValidation.Validation; 434 } 435 436 //添加条件格式：当各科都及格时，把行单元格的字段设置为绿色 437 AddFormatCondition() { 438 this.MainRange.FormatConditions.Delete(); 439 //如果“语文、数学、英语”都及格了，将字体颜色改为绿色 440 let formatCondition = this.MainRange.FormatConditions 441 .Add(xlExpression, -1, '=AND(' + 442 'ISNUMBER(INDIRECT("$E" & ROW())),' + 443 'INDIRECT("$E" & ROW())>=60,' + 444 'INDIRECT("$F" & ROW())>=60,' + 445 'INDIRECT("$G" & ROW())>=60)', 446 "", undefined, undefined, undefined, undefined); 447 formatCondition.SetFirstPriority(); 448 formatCondition.Font.Color = XLS.Colors.Green;//绿色 449 formatCondition.Font.TintAndShade = 0; 450 formatCondition.StopIfTrue = false; 451 452 //留存创建的条件格式对象，以备更多探索 453 this.FormatCondition = formatCondition; 454 } 455 456 //添加自动筛选 457 AddAutoFilter() { 458 if (this.TopLeftCell.Worksheet.AutoFilter != undefined) { 459 alert('数据自动筛选已经开启，无法再次开启'); 460 return; 461 } 462 this.MainRange.AutoFilter(undefined, 463 undefined, xlAnd, undefined, undefined); 464 let refersTo = "='" + this.MainRange.Worksheet.Name + 465 "'!" + this.MainRange.Address(); 466 this.MainRange.Worksheet.Names.Add(XLSExample.name, 467 refersTo, false, undefined, undefined, undefined, 468 undefined, undefined, undefined, undefined, undefined); 469 } 470 471 //添加排序规则：按年龄升序排列 472 AddSortRule() { 473 //获取排序基准列内容区域 474 let iColumn = XLSExample.Headers.indexOf('年龄') + 1; 475 let rngSortBy = this.HeadersRange.Cells 476 .Item(iColumn).Offset(1, 0) 477 .Resize(XLSExample.Data.length, 1); 478 //添加排序规则 479 (obj=>{ 480 (obj=>{ 481 obj.Clear(); 482 obj.Add(rngSortBy, xlSortOnValues, 483 xlAscending, "", undefined); 484 })(obj.SortFields); 485 obj.Header = xlYes; 486 obj.Orientation = xlSortColumns; 487 obj.MatchCase = false; 488 obj.SortMethod = xlPinYin; 489 obj.SetRange(this.MainRange); 490 obj.Apply(); 491 })(this.MainRange.Worksheet.Sort); 492 493 //留存排序规则对象，以备更多探索 494 this.Sort = this.MainRange.AutoFilter.Sort; 495 } 496 497 //为数据表创建条形图 498 AddChart() { 499 let rngMain = this.MainRange; 500 let sht = rngMain.Worksheet; 501 //取得给定数据表底部单元格，以定位新建的图表 502 let rngLocation = rngMain.Cells.Item(1) 503 .Offset(rngMain.Rows.Count + 2, 0); 504 //在给定位置按给定大小创建【簇状条形图】 505 let shape = sht.Shapes.AddChart(xlBarClustered, 506 rngLocation.Left, rngLocation.Top, 507 rngMain.Width, rngMain.Height * 2); 508 let chart = shape.Chart; 509 //将条形图的样式修改为 209 510 chart.ChartStyle = 209; 511 //设置引用的数据表 512 chart.SetSourceData(rngMain, xlColumns); 513 //Axes 方法返回坐标轴对象，无参时返回它们的集合 514 //设置横坐标的最大刻度 515 chart.Axes(xlValue).MaximumScale = 100; 516 //设置纵坐标（分类轴）的分类，可以是 Range 对象 517 let rngCategory = rngMain.Cells.Item(1) 518 .Offset(1, 0).Resize(rngMain.Rows.Count - 1, 1); 519 chart.Axes(xlCategory).CategoryNames = rngCategory; 520 //删除图表名为【年龄】的系列；SeriesCollection 方法无参调用 521 //会返回当前图表对象的所有系列的集合 522 chart.SeriesCollection('年龄').Delete(); 523 //设置图表的标题 524 let rngTitle = this.TitleRange.Cells.Item(1); 525 //必须先设置 HasTitle 属性为 true，才能设置标题文本 526 chart.HasTitle = true; 527 chart.ChartTitle.Text = rngTitle.Value2; 528 } 529 530 //为数据表创建透视表 531 AddPivotTable() { 532 let rngMain = this.MainRange; 533 let sht = rngMain.Worksheet; 534 //Create() 方法：为创建数据透视表创建缓冲对象 535 let address = `=${sht.Name}!${rngMain.Address(true, true, xlR1C1)}`; 536 let pivotCache = sht.Parent.PivotCaches() 537 .Create(xlDatabase, address, xlPivotTableVersion15) 538 //在缓冲对象上创建数据透视表 539 let rngLocation = rngMain.Cells.Item(1) 540 .Offset(rngMain.Rows.Count + 25, 0); 541 address = `${sht.Name}!${rngLocation.Address(true, true, xlR1C1)}`; 542 //这个调用必须使用 xlR1C1 样式的地址 543 let pivotTable = pivotCache.CreatePivotTable( 544 address, undefined, false, xlPivotTableVersion15); 545 546 (obj=>{ 547 obj.Orientation = xlRowField; 548 obj.Position = 1; 549 })(pivotTable.PivotFields("性别")); 550 (obj=>{ 551 obj.Orientation = xlRowField; 552 obj.Position = 2; 553 })(pivotTable.PivotFields("姓名")); 554 pivotTable.AddDataField( 555 pivotTable.PivotFields("语文"), undefined, undefined); 556 pivotTable.AddDataField( 557 pivotTable.PivotFields("数学"), undefined, undefined); 558 pivotTable.AddDataField( 559 pivotTable.PivotFields("外语"), undefined, undefined); 560 //设置字段 561 pivotTable.PivotFields("求和项:语文").Function = xlMax; 562 pivotTable.PivotFields("最大值项:语文").Caption = "最大值项:语文"; 563 pivotTable.PivotFields("求和项:数学").Function = xlMax; 564 pivotTable.PivotFields("最大值项:数学").Caption = "最大值项:数学"; 565 pivotTable.PivotFields("求和项:外语").Function = xlMax; 566 pivotTable.PivotFields("最大值项:外语").Caption = "最大值项:外语"; 567 //创建计算型字段 568 pivotTable.CalculatedFields() 569 .Add("总分", "= 语文+ 数学+ 外语", true); 570 pivotTable.PivotFields("总分").Orientation = xlDataField; 571 //将【姓名】字段按【总分】字段降序排序 572 pivotTable.PivotFields("姓名").AutoSort(xlDescending, '求和项:总分'); 573 //添加切片器 ：JSA 调用 API 创建切片器，代码可以无误执行，但无任何效果 574 //新创建的 slicer 对象，仍然是初始状态，Name/Caption = default， 575 //Top/Left/Width/Height = 1，赋值语句也可执行，但仍然无效 576 rngLocation = rngLocation.Offset(0, pivotTable.PivotFields().Count - 2); 577 let slicerCache = sht.Parent.SlicerCaches.Add(pivotTable, "性别"); 578 let slicer = slicerCache.Slicers.Add(sht, undefined, "性别", 579 "性别", rngLocation.Left, rngLocation.Top, 80, 100); 580 //Console.WriteAll(slicer.Name, slicer.Caption); 581 } 582 583 //创建超链接 584 AddHyperlinks() { 585 let rngTable = this.TableRange; 586 let sht = rngTable.Worksheet; 587 //创建内部超链接 588 let rngHeaders = rngTable.Rows.Item(2); 589 let rngInnerLink = rngTable.Cells.Item(1) 590 .Offset(rngTable.Rows.Count, 0); 591 let address = `'${sht.Name}'!${rngHeaders.Address(false, false)}` 592 sht.Hyperlinks.Add(rngInnerLink, "", 593 address, "内部位置超链接", "表头"); 594 //创建外部文件的超链接 595 address = 'C:\\Windows\\System32\\cmd.exe'; 596 let rngFile = rngInnerLink.Offset(0, 1); 597 sht.Hyperlinks.Add(rngFile, address, '', 598 '外部文件超链接', '文件'); 599 //创建网页超链接 600 address = 'http://www.baidu.com'; 601 let rngUrl = rngFile.Offset(0, 1); 602 sht.Hyperlinks.Add(rngUrl, address, '', 603 '外部网页超链接', '百度'); 604 //创建邮件超链接 605 address = 'mailto:123456789@qq.com?subject=还好吗'; 606 let rngMail = rngUrl.Offset(0, 1); 607 let lnk = sht.Hyperlinks.Add(rngMail, 608 address, '', '邮件超链接', address); 609 } 610 611 //创建文本框 612 AddTextBox() { 613 let rngMain = this.MainRange; 614 let rngLocation = rngMain.Cells.Item(1) 615 .Offset(0, rngMain.Columns.Count); 616 let shape = ActiveSheet.Shapes.AddTextbox( 617 msoTextOrientationVertical, rngLocation.Left, 618 rngLocation.Top, rngLocation.Width, rngMain.Height); 619 shape.Placement = xlMoveAndSize; 620 let characters = shape.TextFrame.Characters(); 621 characters.Text = "文本框试验"; 622 characters.Font.Bold = true; 623 characters.Font.Size = 15; 624 625 } 626 627 //添加标签 628 AddLabel() { 629 let rngMain = this.MainRange; 630 let rngLocation = rngMain.Cells.Item(1) 631 .Offset(0, rngMain.Columns.Count + 1); 632 let label = this.TopLeftCell.Worksheet.Shapes 633 .AddLabel(msoTextOrientationVertical, 634 rngLocation.Left, rngLocation.Top, 635 rngLocation.Width, this.MainRange.Height); 636 label.Placement = xlMoveAndSize; 637 label.TextFrame.Characters().Text = "标签试验" 638 } 639 640 //添加直线 641 AddLine() { 642 let rngLocation = this.MainRange.Cells.Item(1) 643 .Offset(0, this.MainRange.Columns.Count + 2); 644 let shape = this.TopLeftCell.Worksheet.Shapes 645 .AddLine(rngLocation.Left, rngLocation.Top, 646 rngLocation.Left + rngLocation.Width, 647 rngLocation.Top + rngLocation.Height); 648 shape.Placement = xlMoveAndSize; 649 let line = shape.Line; 650 line.DashStyle = msoLineDashDotDot; 651 } 652 653 static RunAll() { 654 let rng = new Range('B1'); 655 let eg = new XLSExample(rng); 656 eg.AddComment(); 657 eg.AddBorders(); 658 eg.SetFontAndAlignment(); 659 eg.AddValidation(); 660 eg.AddFormatCondition(); 661 eg.AddAutoFilter(); 662 eg.AddSortRule(); 663 eg.AddChart(); 664 eg.AddPivotTable(); 665 eg.AddHyperlinks(); 666 eg.AddTextBox(); 667 eg.AddLabel(); 668 eg.AddLine(); 669 } 670 }
