SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillswps-core-api

wps-core-api

Office & productivity

View repository

Updated 10-15 20:12

About this skill

Global Variables

  • Application / app: WPS application object
  • ActiveWorkbook（wb): current workbook
  • ActiveSheet（ws): current active worksheet
  • Selection: current selection

Helper functions (must be defined at the top of all code)

function CL(c){var s="";while(c>0){c--;s=String.fromCharCode(65+(c%26))+s;c=Math.floor(c/26);}return s;}
function _n(v){return (v===null||v===undefined||isNaN(v))?0:Number(v);}
function toM(v){return _n(v)/1e6;}

Core API (synchronous execution, not Excel.run mode)

Read/Write Data

ws.Range("A1").Value2 = "文本";
ws.Range("A1:C3").Value2 = [["a","b","c"],["d","e","f"]];
var vals = ws.Range("A1:C3").Value2;
ws.Range(CL(col)+row).Value2 = 123;
ws.Range("A1").Formula = "=SUM(B1:B10)";
ws.Range("A1:Z100").ClearContents();
ws.Range("A1:Z100").ClearFormats();
ws.Range("A1:Z100").Clear();

Dynamic range location (avoid hardcoding row counts)

// End() 方向必须用数字：4=xlDown, 3=xlUp, 2=xlRight, 1=xlLeft
var lastRow = ws.Range("A1").End(4).Row;
var lastRow = ws.Range("A65536").End(3).Row;
var used = ws.UsedRange;
var lastRow = used.Row + used.Rows.Count - 1;

ws.Range("A1").Offset(1, 0).Value2 = "A2";
var r = ws.Range("A1").Resize(10, 5);
var dataRange = ws.Range("A1").CurrentRegion;

Formatting

range.Font.Bold = true;
range.Font.Size = 14;
range.Font.Color = 0xFFFFFF;        // BGR 格式
range.Font.Name = "微软雅黑";
range.Font.Italic = true;
range.Interior.Color = 0x8B4513;
range.HorizontalAlignment = -4108; // -4108=居中, -4131=左, -4152=右
range.VerticalAlignment   = -4108;
range.WrapText = true;
range.ShrinkToFit = true;
range.Orientation = 90;             // 文字旋转角度
range.IndentLevel = 2;
range.NumberFormat = "#,##0.00";
range.NumberFormat = "0.00%";
range.NumberFormat = "yyyy-mm-dd";
range.NumberFormat = "#,##0.0,,"M""; // 百万M
range.Locked = false;               // 配合工作表保护

Size and Merging

ws.Range("A:A").ColumnWidth = 15;
ws.Range("1:1").RowHeight = 30;
ws.Range("A1:P1").Merge();
ws.Range("A1:P1").UnMerge();

Row/Column Operations

ws.Range("5:5").Insert();
ws.Range("5:5").Delete();
ws.Range("3:5").Hidden = true;
ws.Range("A:C").Hidden = true;

Sorting (Range.Sort)

// 按 B 列升序（1=升序/xlAscending, 2=降序; Header 1=xlYes有表头）
ws.Range("A1:E100").Sort(ws.Range("B1"), 1, null, null, null, null, null, 1);

// 多列排序：B升序再C降序
ws.Range("A1:E100").Sort(ws.Range("B1"), 1, ws.Range("C1"), null, 2, null, null, 1);

Find and Replace

var found = ws.Range("A:A").Find("目标值");
if (found) { var row = found.Row; }

ws.Range("A1:Z100").Replace("旧值", "新值");

Copy and Fill

ws.Range("A1:C10").Copy(ws.Range("E1"));
ws.Range("A1:A2").AutoFill(ws.Range("A1:A20"));
ws.Range("A1:E1").FillDown();

Data Validation

ws.Range("J4:J100").Validation.Add(3, 1, 1, "选项A,选项B,选项C");

Conditional Formatting

range.FormatConditions.Delete();
var fc = range.FormatConditions.Add(1, 5, 0); // 1=xlCellValue, 5=xlGreater
fc.Interior.Color = 0x00AA00;
fc.Font.Color = 0xFFFFFF;

Row/Column Grouping (financial model collapsing)

ws.Range("5:10").Group();
ws.Range("B:D").Group();
ws.Range("5:10").Ungroup();
ws.Outline.ShowLevels(1);   // 折叠到第1级
ws.Outline.ShowLevels(2);

AutoFilter

ws.Range("A1:A10").AutoFilter();
ws.Range("A1").AutoFilter(2, ">1000");
ws.ShowAllData();

Deduplication

ws.Range("A1:E100").RemoveDuplicates([1, 2], 1);

Worksheet Operations

// 创建（WPS 特殊：Add() 返回 null，必须用 ActiveSheet）
wb.Sheets.Add();
var ws = wb.ActiveSheet;
ws.Name = "新工作表";

// 访问（必须用 .Item()）
var ws = wb.Sheets.Item("Sheet1");
var ws = wb.Sheets.Item(1);
var count = wb.Sheets.Count;

// 属性
ws.Visible = false;
ws.Tab.Color = 0x0055D9;   // 标签颜色 BGR
ws.Activate();

// 复制/移动/删除
ws.Copy(null, wb.Sheets.Item(wb.Sheets.Count));
Application.DisplayAlerts = false;
ws.Delete();
Application.DisplayAlerts = true;

// 保护
ws.Protect("密码");
ws.Unprotect("密码");

// 命名区域
wb.Names.Add("收入", "=财务!:");
ws.Names.Add("本表总计", ws.Range("E100"));

Chart Operations

// 必须用 Shapes.AddChart2（ChartObjects.Add 不可用）
// 类型：57=折线, 51=簇状柱形, 5=面积, 65=饼图
var shape = ws.Shapes.AddChart2(0, 57, left, top, width, height);
var chart = shape.Chart;
chart.HasTitle = true;
chart.ChartTitle.Text = "标题";
chart.SetSourceData(ws.Range("A1:C20"));

var s = chart.SeriesCollection.Item(1);
s.Name = "系列名";
s.XValues = ws.Range("A2:A20");
s.Values = ws.Range("B2:B20");

Application Level

// 大批量操作前必须关闭，操作后必须恢复
Application.ScreenUpdating = false;
Application.DisplayAlerts  = false;
// ... 操作 ...
Application.ScreenUpdating = true;
Application.DisplayAlerts  = true;

// 内置工作表函数
Application.WorksheetFunction.Sum(ws.Range("B2:B100"));
Application.WorksheetFunction.Average(ws.Range("B2:B100"));
Application.WorksheetFunction.VLookup("key", ws.Range("A:B"), 2, false);
Application.WorksheetFunction.Max(ws.Range("C:C"));
Application.WorksheetFunction.CountIf(ws.Range("A:A"), ">0");

// 合并不连续区域
var union = Application.Union(ws.Range("A1:A10"), ws.Range("C1:C10"));
union.Font.Bold = true;

BGR Common Color Quick Reference

Purpose BGR Value RGB Equivalent
Dark blue header/brand color 0x8B4513 #13458B
Vivid blue (chart primary color) 0xFF4500 #0045FF
Orange accent 0x0055D9 #D95500
Golden orange (chart bars) 0xE8A040 #40A0E8
Light gray background 0xF0F0F0 #F0F0F0
Light blue alternating rows 0xFFF0E0 #E0F0FF
White 0xFFFFFF #FFFFFF
Black 0x000000 #000000
Red (down/warning) 0x0000FF #FF0000
Green (up/completed) 0x00AA00 #00AA00
Orange-yellow (in progress) 0x00AAFF #FFAA00
Blue (information) 0xFF8800 #0088FF

❌ Unavailable APIs (strictly forbidden in WPS JS macros, will crash or throw errors)

Forbidden Syntax Reason Correct Alternative
ws = wb.Sheets.Add() Returns null wb.Sheets.Add(); ws = Application.ActiveSheet;
ws.Cells(row, col) VBA syntax unavailable ws.Cells.Item(row, col) or ws.Range(CL(col)+row)
ws.Rows(n) VBA syntax unavailable ws.Range("n:n")
ws.Columns("A") VBA syntax unavailable ws.Range("A:A")
ws.Columns("A").AutoFit() Unavailable Set manually ColumnWidth
ws.ListObjects Unavailable Simulate with regular Range
ws.ChartObjects.Add() Unavailable ws.Shapes.AddChart2()
.Borders / .BorderAround() Crashes directly Use background color to distinguish regions
wb.Sheets("名称") Bracket form unavailable wb.Sheets.Item("名称")
chart.SeriesCollection(n) Collection bracket access unavailable chart.SeriesCollection.Item(n)
Worksheets(1) Collection bracket access unavailable Worksheets.Item(1)
End(xlDown) literal Enum unavailable Use numbers: 4=down, 3=up, 2=right, 1=left
ActiveSheet(no prefix) Global variables unstable Application.ActiveSheet
ActiveWorkbook(no prefix) Global variables unstable Application.ActiveWorkbook
Range("A1").Value Write unavailable For writing use .Value2, for reading use .Value() or .Value2
[A1] = 5 JS parses as destructuring assignment Range("A1").Value2 = 5
AddChart2(-1, ...) Style -1 returns null AddChart2(0, ...)
.Cells.Clear() Unstable when operating on the entire sheet .UsedRange.Clear()

⚠️ Key differences between WPS JS macros and Excel VBA

  1. Colors are BGR not RGB：0xFF0000 = blue! Red is 0x0000FF
  2. Sheets.Add() returns null, must use Application.ActiveSheet to get the new sheet
  3. End() uses numbers：4=xlDown, 3=xlUp, 2=xlRight, 1=xlLeft
  4. Before large batch writesmust Application.ScreenUpdating = false, otherwise WPS crashes
  5. Borders completely unavailable: Use a dark background + white text instead of a visual separator
  6. Collection access must use .Item()：Sheets.Item(1)、SeriesCollection.Item(1) etc.
  7. Method calls must add parentheses：.Select()、.Activate()、.Delete() etc.
  8. JS is strictly case-sensitive：Value2 is not value2，Formula is not formula
  9. VBA syntax is not supported: cannot use Dim、Set、Sub/End Sub、&(for string concatenation use +）