下面我们通过一个例子，来了解一下日常宏编程中最常面临的任务（因为使用了全局表达式，请将【工具】》【选项】》【编译】》【禁止全局作用域表达式】取消勾选）：

    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.     },
    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, //白色 
    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 对象');
    47.         if (must) title += '[必须]';
    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.         }
    56.         return rng;
    57.     },
    59.     /*获取指定列的列字母
    60.     columnIndex : 列序数，Number 类型
    61.     */
    62.     GetColumnLetter : function(columnIndex) {
    63.         if (!g.IsType(columnIndex, 'Number'))
    64.             throw new TypeError('参数 columnIndex 必须是一个数字');
    66.         if (columnIndex <= 0 || columnIndex > 16384)
    67.             throw new Error('Please make sure 1 <= columnIndex <= 16384.');
    69.         let address = ActiveSheet.Columns.Item(columnIndex).Address();
    70.         return address.substr(1, address.indexOf(':') - 1);
    71.     },
    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.');
    83.         if (typeof rng == 'undefined')
    84.             rng = XLS.GetRange(undefined, undefined, true);
    86.         if (rng.constructor.name != 'Range')
    87.             throw new Error('Parameter "rng" must be a Range object.');
    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.     },
    103.     /*获取单元格区域的所有数据，如果有多个子区域，将返回一个多维数组，
    104.     否则，返回一维数组*/
    105.     GetValues : function(rng) {
    106.         if (typeof rng == 'undefined')
    107.             rng = XLS.GetRange(undefined, undefined, true);
    109.         if (rng.constructor.name != 'Range')
    110.             throw new Error('Parameter "rng" must be a Range object.');
    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.     },
    129.     /*获取单元格区域的完全引用的地址*/
    130.     GetRangeFullAddress : function(rng) {
    131.         if (typeof rng == 'undefined')
    132.             rng = XLS.GetRange(undefined, undefined, true);
    134.         if (rng.constructor.name != 'Range')
    135.             throw new Error('Parameter "rng" must be a Range object.');
    137.         return "'" + rng.Worksheet.Parent.Path + "\\[" +
    138.             rng.Worksheet.Parent.Name + "]" + rng.Worksheet.Name +
    139.             "'!" + rng.Address();
    140.     },
    142.     /*为单元格区域创建简单的边框*/
    143.     MakeSimpleBorders : function(rng, color, weight, lineStyle) {
    144.         if (!XLS.IsRange(rng))
    145.             throw new TypeError('参数 rng 必须是一个单元格区域对象');
    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 必须是一个整数');
    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.     },
    175.     /*判断一个对象是否是 Range 类型的对象*/
    176.     IsRange : function(rng) {
    177.         return g.IsType(rng, 'Range');
    178.     },
    180.     SetFormatConditionByExampleRange : function() {
    181.         {//与用户交互，取得操作需要的输入
    182.             //指定数据表所在的单元格区域
    183.             let title = '选取数据表';
    184.             let prompt = '请用鼠标框选你要按值表样例加设条件格式的工作' +
    185.                 '表数据所在的单元格区域(请不要连带选中表头行)';
    186.             var rngMain = XLS.GetRange(prompt, title, true);
    188.             //指定值表样例所在的单元格区域
    189.             title = '选取值表样例';
    190.             prompt = '请用鼠标框选你要设置条件格式参照的值表样例所在的' +
    191.                 '单元格区域（请确保设置了格式）';
    192.             var rngExample = XLS.GetRange(prompt, title, true);
    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.         }
    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.         }
    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.     },
    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.     },
    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 参数是一个单元格区域对象');
    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.     }
    292.     //表格样例的标题
    293.     static get Title() {
    294.         if (XLSExample._Title == undefined)
    295.             XLSExample._Title = '古名人成绩单';
    296.         return XLSExample._Title;
    297.     }
    299.     //表格样例的表头
    300.     static get Headers() {
    301.         if (XLSExample._Headers == undefined)
    302.             XLSExample._Headers = ['姓名'  , '性别', 
    303.                 '年龄', '语文', '数学', '外语'];
    304.         return XLSExample._Headers;
    305.     }
    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.     }
    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.     }
    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.     }
    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.         */    
    388.         //最后，留存边框对象的引用，以备更多探索访问
    389.         this.Borders = borders;
    390.     }
    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.     }
    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);    
    432.         //留存数据有效对象的引用，以备更多探索访问
    433.         this.Validation = rngValidation.Validation;
    434.     }
    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;
    452.         //留存创建的条件格式对象，以备更多探索
    453.         this.FormatCondition = formatCondition;
    454.     }
    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.     }
    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);    
    493.         //留存排序规则对象，以备更多探索
    494.         this.Sort = this.MainRange.AutoFilter.Sort;
    495.     }
    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.     }
    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);
    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.     }
    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.     }
    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;
    625.     }    
    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.     }
    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.     }
    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. }