不会VBA？用JavaScript玩转WPS自动化：15个实战场景全解析
第一部分：基础篇
1.1 环境搭建与第一个宏
开启宏功能
- 打开 WPS → 开发工具 → JS 宏
- 首次使用需启用宏（文件 → 选项 → 信任中心 → 启用所有宏）
Hello World 示例
function HelloWorld() {
// 弹出消息框
Application.MsgBox("Hello, WPS JS宏！");
// 在单元格写入内容
var sheet = Application.ActiveSheet;
sheet.Range("A1").Value = "Hello World";
sheet.Range("A1").Font.Bold = true;
sheet.Range("A1").Font.Size = 14;
sheet.Range("A1").Interior.Color = 65535; // 黄色背景
}
宏的基本结构
// 函数定义
function 宏名称() {
// 变量声明
var 变量名;
// 获取活动工作表
var sheet = Application.ActiveSheet;
// 获取活动工作簿
var workbook = Application.ActiveWorkbook;
// 操作代码...
// 返回值（可选）
return 结果;
}
1.2 JavaScript 基础语法（WPS环境）
function BasicSyntax() {
// 1. 变量与数据类型
var str = "字符串"; // 字符串
var num = 100; // 数字
var bool = true; // 布尔值
var date = new Date(); // 日期对象
var arr = [1, 2, 3, "a"]; // 数组
var obj = {name: "张三", age: 25}; // 对象
// 2. 条件语句
var score = 85;
if (score >= 90) {
Application.MsgBox("优秀");
} else if (score >= 80) {
Application.MsgBox("良好");
} else {
Application.MsgBox("需努力");
}
// 3. 循环语句
// For 循环
for (var i = 1; i <= 10; i++) {
Application.Cells(i, 1).Value = i;
}
// ForEach 遍历数组
var data = ["苹果", "香蕉", "橙子"];
for (var index in data) {
Application.Cells(index + 1, 2).Value = data[index];
}
// While 循环
var count = 1;
while (count <= 5) {
Application.Cells(count, 3).Value = "第" + count + "行";
count++;
}
// 4. 函数定义与调用
function add(a, b) {
return a + b;
}
var result = add(10, 20);
Application.MsgBox("10 + 20 = " + result);
// 5. 异常处理
try {
var risky = 1 / 0;
Application.MsgBox("结果：" + risky);
} catch (e) {
Application.MsgBox("错误：" + e.message);
}
}
1.3 核心对象模型
function ObjectModel() {
// Application 对象：WPS应用程序本身
var app = Application;
// Workbooks 集合：所有打开的工作簿
var wbs = app.Workbooks;
Application.MsgBox("当前打开的工作簿数量：" + wbs.Count);
// ActiveWorkbook：当前活动工作簿
var wb = app.ActiveWorkbook;
Application.MsgBox("当前工作簿名称：" + wb.Name);
// Worksheets 集合：工作簿中的所有工作表
var sheets = wb.Worksheets;
// ActiveSheet：当前活动工作表
var sheet = app.ActiveSheet;
// Range 对象：单元格或单元格区域
var range = sheet.Range("A1:D10");
// Selection：当前选中的区域
var sel = app.Selection;
}
第二部分：核心篇
2.1 单元格操作大全
// ==================== 单元格基础操作 ====================
function CellOperations() {
var sheet = Application.ActiveSheet;
// 1. 写入数据
sheet.Range("A1").Value = "姓名";
sheet.Range("B1").Value = "年龄";
sheet.Range("C1").Value = "部门";
// 批量写入数组（高效）
var data = [
["张三", 28, "技术部"],
["李四", 32, "销售部"],
["王五", 25, "人事部"]
];
sheet.Range("A2:C4").Value = data;
// 2. 读取数据
var name = sheet.Range("A2").Value;
var age = sheet.Range("B2").Value;
Application.MsgBox("姓名：" + name + "，年龄：" + age);
// 3. 单元格格式设置
var cell = sheet.Range("A1:C1");
// 字体设置
cell.Font.Name = "微软雅黑"; // 字体名称
cell.Font.Size = 12; // 字号
cell.Font.Bold = true; // 粗体
cell.Font.Italic = false; // 斜体
cell.Font.Color = 255; // 字体颜色（红色）
cell.Font.Underline = 2; // 下划线样式
// 对齐方式
cell.HorizontalAlignment = 3; // 水平居中（-4108为居中，3为靠右）
cell.VerticalAlignment = 2; // 垂直居中
// 背景填充
cell.Interior.Color = 49407; // 背景颜色（橙色）
cell.Interior.Pattern = 1; // 填充图案
// 边框设置
cell.Borders.LineStyle = 1; // 实线边框
cell.Borders.Weight = 2; // 边框粗细
cell.Borders.Color = 0; // 黑色边框
// 4. 行高列宽
sheet.Range("A1").RowHeight = 30; // 设置行高为30磅
sheet.Range("A1").ColumnWidth = 20; // 设置列宽为20字符
sheet.Columns("A:A").AutoFit(); // 自动调整列宽
sheet.Rows("1:1").AutoFit(); // 自动调整行高
// 5. 单元格合并与拆分
sheet.Range("A5:C5").Merge(); // 合并单元格
sheet.Range("A5").UnMerge(); // 取消合并
// 6. 公式设置
sheet.Range("D2").Formula = "=B2*100"; // 设置公式
sheet.Range("D2").FormulaR1C1 = "=RC[-1]*100"; // R1C1样式公式
// 7. 数字格式
sheet.Range("E2").NumberFormat = "0.00"; // 保留两位小数
sheet.Range("E3").NumberFormat = "yyyy-mm-dd"; // 日期格式
sheet.Range("E4").NumberFormat = "¥#,##0.00"; // 货币格式
sheet.Range("E5").NumberFormat = "0%"; // 百分比
// 8. 数据验证（下拉列表）
var validation = sheet.Range("F2:F10").Validation;
validation.Add(3, 1, 1, "男,女"); // 允许序列，来源为"男,女"
validation.IgnoreBlank = true;
validation.InCellDropdown = true;
// 9. 条件格式（简单示例）
var format = sheet.Range("B2:B10").FormatConditions;
format.Add(2, 0, "=B2>30"); // 单元格值大于30
format.Item(1).Interior.Color = 5296274; // 绿色背景
}
// ==================== 批量单元格操作（高性能） ====================
function BatchCellOperations() {
var sheet = Application.ActiveSheet;
var lastRow = sheet.UsedRange.Rows.Count;
// 方法1：使用数组批量写入（推荐，速度最快）
var data = [];
for (var i = 1; i <= 1000; i++) {
data.push(["数据" + i, i, new Date()]);
}
sheet.Range("A1:C1000").Value = data;
// 方法2：逐行写入（较慢，仅适用于小数据量）
for (var i = 1; i <= 100; i++) {
sheet.Cells(i, 1).Value = "行" + i;
}
// 方法3：使用FillDown批量复制
sheet.Range("A1").Value = "模板";
sheet.Range("A1:A100").FillDown();
// 清除操作
sheet.Range("A1:C100").ClearContents(); // 仅清除内容
sheet.Range("A1:C100").Clear(); // 清除内容和格式
sheet.Range("A1:C100").Delete(); // 删除单元格
}
2.2 工作表操作大全
function WorksheetOperations() {
var wb = Application.ActiveWorkbook;
// 1. 创建工作表
var newSheet = wb.Worksheets.Add();
newSheet.Name = "数据汇总";
// 在指定位置插入（第2个位置）
var sheet2 = wb.Worksheets.Add(null, wb.Worksheets(1));
sheet2.Name = "插入在中间";
// 2. 复制工作表
wb.Worksheets("数据汇总").Copy(null, wb.Worksheets(wb.Worksheets.Count));
wb.ActiveSheet.Name = "数据汇总-副本";
// 3. 移动工作表
wb.Worksheets("插入在中间").Move(wb.Worksheets(1)); // 移到最前
// 4. 重命名
wb.Worksheets("Sheet1").Name = "原始数据";
// 5. 删除工作表（需关闭提示）
Application.DisplayAlerts = false;
wb.Worksheets("插入在中间").Delete();
Application.DisplayAlerts = true;
// 6. 隐藏/显示工作表
wb.Worksheets("原始数据").Visible = 2; // 0=显示, 2=隐藏, 3=深度隐藏（VBA属性，JS中可能不同）
// 7. 保护/取消保护工作表
var sheet = wb.Worksheets("数据汇总");
sheet.Protect("password123"); // 设置保护密码
sheet.Unprotect("password123"); // 取消保护
// 8. 工作表属性设置
sheet.Tab.Color = 255; // 设置标签颜色为红色
sheet.EnableSelection = 0; // 限制选择范围
// 9. 遍历所有工作表
var msg = "工作表列表：\n";
for (var i = 1; i <= wb.Worksheets.Count; i++) {
var s = wb.Worksheets(i);
msg += i + ". " + s.Name + " (使用范围: " + s.UsedRange.Address + ")\n";
}
Application.MsgBox(msg);
// 10. 判断工作表是否存在
function SheetExists(name) {
try {
wb.Worksheets(name);
return true;
} catch(e) {
return false;
}
}
Application.MsgBox("是否存在'原始数据'：" + SheetExists("原始数据"));
}
2.3 工作簿操作大全
function WorkbookOperations() {
// 1. 新建工作簿
var newWb = Application.Workbooks.Add();
// 2. 打开工作簿
// var openWb = Application.Workbooks.Open("D:\\数据\\报表.xlsx");
// 3. 保存工作簿
newWb.SaveAs("D:\\新建文档.xlsx");
// 4. 另存为不同格式
// 51 = xlsx, 56 = xls, 6 = csv, 32 = html
newWb.SaveAs("D:\\备份.xls", 56);
// 5. 关闭工作簿
newWb.Close(false); // false=不保存更改, true=保存
// 6. 获取工作簿信息
var wb = Application.ActiveWorkbook;
var info = "名称：" + wb.Name + "\n" +
"路径：" + wb.Path + "\n" +
"完整路径：" + wb.FullName + "\n" +
"是否保存：" + wb.Saved;
Application.MsgBox(info);
// 7. 遍历所有打开的工作簿
var msg = "已打开的工作簿：\n";
for (var i = 1; i <= Application.Workbooks.Count; i++) {
msg += i + ". " + Application.Workbooks(i).Name + "\n";
}
Application.MsgBox(msg);
// 8. 激活工作簿
Application.Workbooks("报表.xlsx").Activate();
// 9. 工作簿保护
wb.Protect("密码", true); // 结构保护
// 10. 刷新链接
wb.UpdateLink(Name: wb.LinkSources(1));
}
2.4 数据查找与筛选
// ==================== 数据查找 ====================
function FindData() {
var sheet = Application.ActiveSheet;
// 1. Find 方法查找
var found = sheet.Range("A:A").Find("张三");
if (found != null) {
Application.MsgBox("找到'张三'在：" + found.Address);
found.Interior.Color = 65535; // 高亮显示
}
// 2. 查找下一个
var firstAddress = found.Address;
var nextCell = found;
do {
nextCell.Interior.Color = 65535;
nextCell = sheet.Range("A:A").FindNext(nextCell);
} while (nextCell != null && nextCell.Address != firstAddress);
// 3. 使用 Find 参数
var result = sheet.Cells.Find({
What: "关键词",
LookIn: -4163, // xlValues
LookAt: 2, // xlPart (1=xlWhole)
SearchOrder: 1, // xlByRows (2=xlByColumns)
SearchDirection: 1, // xlNext
MatchCase: false
});
// 4. Match 函数查找位置
var position = Application.Match("李四", sheet.Range("A1:A100"), 0);
Application.MsgBox("李四在第" + position + "行");
// 5. VLookup 模拟
function VLookup(lookupValue, tableArray, colIndexNum) {
var result = Application.VLookup(lookupValue, tableArray, colIndexNum, false);
return result;
}
}
// ==================== 数据筛选 ====================
function FilterData() {
var sheet = Application.ActiveSheet;
var range = sheet.Range("A1:D100"); // 包含标题的数据区域
// 1. 自动筛选
range.AutoFilter(2, ">25"); // 第2列大于25
// 2. 多条件筛选
range.AutoFilter(3, "技术部"); // 第3列等于"技术部"
// 3. 清除筛选
sheet.AutoFilterMode = false;
// 4. 高级筛选（复制到别处）
var criteriaRange = sheet.Range("F1:F2");
criteriaRange.Cells(1, 1).Value = "年龄";
criteriaRange.Cells(2, 1).Value = ">30";
range.AdvancedFilter(2, criteriaRange, sheet.Range("H1"), true);
// 参数：2=复制到新位置, 条件区域, 目标区域, 是否去重
// 5. 获取可见单元格（筛选后的数据）
var visibleCells = range.SpecialCells(12); // 12 = xlCellTypeVisible
Application.MsgBox("可见行数：" + visibleCells.Rows.Count);
}
2.5 排序与分类汇总
function SortAndSubtotal() {
var sheet = Application.ActiveSheet;
var range = sheet.Range("A1:D100");
// 1. 单列排序
range.Sort(sheet.Range("B2"), 1); // 按第2列升序排序
// 2. 多列排序
range.Sort({
Key1: sheet.Range("C2"), // 第一排序键（部门）
Order1: 1, // 升序
Key2: sheet.Range("B2"), // 第二排序键（年龄）
Order2: 2, // 降序
Header: 1 // 有标题行
});
// 3. 自定义排序（按颜色）
var sort = sheet.Sort;
sort.SortFields.Add(sheet.Range("A2:A100"), 0, 1, null, 0);
// 参数：范围, 排序类型(0=值), 顺序, 自定义序列, 区分大小写
sort.SetRange(range);
sort.Header = 1;
sort.Apply();
// 4. 分类汇总
range.Subtotal({
GroupBy: 3, // 按第3列（部门）分组
Function: 9, // 9=求和 (1=平均, 2=计数, 3=计数, 9=求和)
TotalList: [2, 4], // 对第2、4列求和
Replace: true,
PageBreaks: false,
SummaryBelowData: true // 汇总显示在数据下方
});
// 5. 移除分类汇总
sheet.RemoveSubtotal();
}
第三部分：进阶篇
3.1 图表自动化
function ChartAutomation() {
var sheet = Application.ActiveSheet;
var dataRange = sheet.Range("A1:C10");
// 1. 创建柱状图
var chart1 = sheet.ChartObjects.Add(100, 100, 400, 300).Chart;
chart1.SetSourceData(dataRange);
chart1.ChartType = 51; // 51 = xlColumnClustered (簇状柱形图)
chart1.HasTitle = true;
chart1.ChartTitle.Text = "销售数据对比";
// 2. 创建折线图
var chart2 = sheet.ChartObjects.Add(550, 100, 400, 300).Chart;
chart2.SetSourceData(dataRange);
chart2.ChartType = 65; // 65 = xlLineMarkers (带数据标记的折线图)
// 3. 创建饼图
var chart3 = sheet.ChartObjects.Add(100, 450, 400, 300).Chart;
chart3.SetSourceData(sheet.Range("A1:B5"));
chart3.ChartType = 5; // 5 = xlPie (饼图)
chart3.ApplyDataLabels(2); // 显示百分比
// 4. 图表样式设置
var cht = sheet.ChartObjects(1).Chart;
// 设置图表区格式
cht.ChartArea.Format.Fill.ForeColor.RGB = 16777215; // 白色背景
cht.ChartArea.Format.Line.Visible = 0; // 无边框
// 设置绘图区
cht.PlotArea.Format.Fill.ForeColor.RGB = 15921906; // 浅灰色
// 设置图例
cht.HasLegend = true;
cht.Legend.Position = -4107; // xlLegendPositionBottom
// 设置坐标轴
cht.Axes(1).HasTitle = true; // X轴
cht.Axes(1).AxisTitle.Text = "月份";
cht.Axes(2).HasTitle = true; // Y轴
cht.Axes(2).AxisTitle.Text = "销售额（万元）";
// 5. 导出图表为图片
cht.Export("D:\\图表.png", "PNG");
// 6. 删除图表
sheet.ChartObjects(2).Delete();
}
3.2 透视表自动化
function PivotTableAutomation() {
var wb = Application.ActiveWorkbook;
var sourceSheet = wb.Worksheets("数据源");
var sourceRange = sourceSheet.Range("A1:D1000");
// 1. 创建透视表
var pivotCache = wb.PivotCaches.Create(1, sourceRange, 1);
// 参数：SourceType(1=数据库), SourceData, Version
var pivotSheet = wb.Worksheets.Add();
pivotSheet.Name = "透视表分析";
var pivotTable = pivotCache.CreatePivotTable(
pivotSheet.Range("A3"), // 放置位置
"透视表1" // 透视表名称
);
// 2. 设置透视表字段
var pt = pivotTable;
// 行字段（部门）
pt.PivotFields("部门").Orientation = 1; // 1=行字段
// 列字段（月份）
pt.PivotFields("月份").Orientation = 2; // 2=列字段
// 数据字段（销售额）
pt.PivotFields("销售额").Orientation = 4; // 4=数据字段
pt.PivotFields("销售额").Function = -4157; // -4157=求和
// 3. 添加计算字段
pt.CalculatedFields.Add("利润率", "=利润/销售额*100");
pt.PivotFields("利润率").Orientation = 4;
// 4. 透视表样式
pt.TableStyle2 = "PivotStyleMedium9"; // 设置样式
// 5. 刷新透视表
pt.RefreshTable();
// 6. 透视表筛选
pt.PivotFields("部门").CurrentPage = "技术部"; // 筛选特定部门
// 7. 获取透视表数据
var dataBodyRange = pt.DataBodyRange;
Application.MsgBox("数据区域：" + dataBodyRange.Address);
}
3.3 形状与图片操作
function ShapesAndPictures() {
var sheet = Application.ActiveSheet;
// 1. 插入形状
var shape1 = sheet.Shapes.AddShape(1, 100, 100, 200, 100); // 1=矩形
shape1.TextFrame.Characters().Text = "重要提示";
shape1.TextFrame.Characters().Font.Size = 14;
shape1.TextFrame.Characters().Font.Bold = true;
shape1.Fill.ForeColor.RGB = 49407; // 填充颜色
shape1.Line.ForeColor.RGB = 255; // 线条颜色
// 常用形状类型：
// 1 = msoShapeRectangle, 2 = msoShapeParallelogram
// 9 = msoShapeOval, 13 = msoShapeHexagon
// 25 = msoShapeRoundedRectangle
// 2. 插入文本框
var textbox = sheet.Shapes.AddTextbox(1, 100, 250, 300, 50);
textbox.TextFrame.Characters().Text = "这是文本框内容";
textbox.TextFrame.Characters().Font.Color.RGB = 16711680; // 蓝色字体
// 3. 插入图片
var pic = sheet.Shapes.AddPicture(
"D:\\logo.png",
0, // LinkToFile (0=不链接)
1, // SaveWithDocument (1=随文档保存)
400, // Left
100, // Top
200, // Width
100 // Height
);
// 4. 图片设置
pic.LockAspectRatio = 0; // 允许改变长宽比
pic.Width = 300;
pic.Height = 150;
pic.Left = 500;
pic.Top = 200;
// 5. 插入艺术字
var wordArt = sheet.Shapes.AddTextEffect(
15, // PresetTextEffect (15=艺术字样式)
"WPS宏开发", // 文本
"微软雅黑", // 字体
36, // 字号
0, // 粗体
0, // 斜体
100, 400 // 位置
);
wordArt.Fill.ForeColor.RGB = 255; // 红色
// 6. 组合形状
var shapes = sheet.Shapes;
var range = shapes.Range([1, 2, 3]); // 选择多个形状
range.Group(); // 组合
// 7. 遍历所有形状
for (var i = 1; i <= sheet.Shapes.Count; i++) {
var shp = sheet.Shapes(i);
Application.MsgBox("形状" + i + "：" + shp.Name + "，类型：" + shp.Type);
}
}
3.4 事件处理与自动触发
// ==================== 工作表事件（需添加到 ThisWorkbook 或 Sheet 对象）====================
// 打开工作簿时触发
function Workbook_Open() {
Application.MsgBox("欢迎使用本工作簿！");
// 可以在这里设置初始化环境
}
// 工作表激活时触发
function Worksheet_Activate() {
var sheet = Application.ActiveSheet;
sheet.Range("A1").Value = "最后访问时间：" + new Date();
}
// 单元格变更时触发
function Worksheet_Change(target) {
// target 参数表示被修改的单元格范围
if (target.Column == 1) { // 如果修改的是A列
target.Offset(0, 1).Value = new Date(); // B列自动记录时间
}
}
// 选区变更时触发
function Worksheet_SelectionChange(target) {
// 可以在这里实现选中高亮等功能
Application.StatusBar = "当前选中：" + target.Address;
}
// 双击单元格时触发
function Worksheet_BeforeDoubleClick(target, cancel) {
if (target.Column == 1) {
Application.MsgBox("您双击了：" + target.Value);
cancel.Value = true; // 取消默认编辑行为
}
}
// 右键点击时触发
function Worksheet_BeforeRightClick(target, cancel) {
// 可以自定义右键菜单
}
// ==================== 定时器模拟 ====================
function SimulateTimer() {
// WPS JS宏不支持原生setInterval，但可以用循环+等待模拟
var i = 0;
while (i < 10) {
Application.ActiveSheet.Range("A1").Value = "计数：" + i;
Application.Wait(new Date(new Date().getTime() + 1000)); // 等待1秒
i++;
}
}
3.5 对话框与用户交互
function UserInteraction() {
// 1. 消息框
Application.MsgBox("操作完成！", 64, "提示"); // 64=信息图标
// 消息框按钮类型：
// 0 = 确定, 1 = 确定+取消, 2 = 终止+重试+忽略
// 3 = 是+否+取消, 4 = 是+否, 5 = 重试+取消
// 2. 输入框
var userName = Application.InputBox("请输入您的姓名：", "信息录入", "默认值");
if (userName != "False") { // 用户点击取消返回"False"
Application.MsgBox("您好，" + userName);
}
// 3. 选择区域对话框
var selectedRange = Application.InputBox("请选择数据区域：", "区域选择", Type: 8);
// Type 8 表示返回Range对象
Application.MsgBox("您选择了：" + selectedRange.Address);
// 4. 文件选择对话框（使用Application.FileDialog）
var fd = Application.FileDialog(3); // 3 = msoFileDialogFilePicker
fd.AllowMultiSelect = false;
fd.Filters.Clear();
fd.Filters.Add("Excel文件", "*.xlsx; *.xls");
fd.Filters.Add("所有文件", "*.*");
if (fd.Show() == -1) { // -1 表示点击了确定
var filePath = fd.SelectedItems(1);
Application.MsgBox("选择的文件：" + filePath);
}
// 5. 文件夹选择对话框
var folderDialog = Application.FileDialog(4); // 4 = msoFileDialogFolderPicker
if (folderDialog.Show() == -1) {
var folderPath = folderDialog.SelectedItems(1);
Application.MsgBox("选择的文件夹：" + folderPath);
}
// 6. 自定义表单（UserForm）
// 注意：WPS JS宏对UserForm支持有限，建议使用HTML对话框或外部表单
// 7. 进度显示（使用状态栏）
Application.StatusBar = "正在处理...";
for (var i = 1; i <= 100; i++) {
Application.StatusBar = "处理进度：" + i + "%";
// 模拟耗时操作
}
Application.StatusBar = false; // 恢复默认状态栏
}
第四部分：实战篇
4.1 数据清洗与标准化工具
// ==================== 完整的数据清洗工具 ====================
function DataCleaningTool() {
var sheet = Application.ActiveSheet;
var dataRange = sheet.UsedRange;
var lastRow = dataRange.Rows.Count;
Application.ScreenUpdating = false; // 关闭屏幕刷新，提升速度
Application.Calculation = 2; // 手动计算模式
try {
// 1. 去除前后空格
for (var i = 1; i <= lastRow; i++) {
for (var j = 1; j <= dataRange.Columns.Count; j++) {
var cell = sheet.Cells(i, j);
if (cell.Value != null && typeof cell.Value === 'string') {
cell.Value = cell.Value.trim();
}
}
}
// 2. 删除空行
var deleteRows = [];
for (var i = lastRow; i >= 1; i--) {
var isEmpty = true;
for (var j = 1; j <= dataRange.Columns.Count; j++) {
if (sheet.Cells(i, j).Value != null && sheet.Cells(i, j).Value != "") {
isEmpty = false;
break;
}
}
if (isEmpty) {
sheet.Rows(i).Delete();
}
}
// 3. 统一日期格式
for (var i = 2; i <= sheet.UsedRange.Rows.Count; i++) {
var dateCell = sheet.Cells(i, 3); // 假设C列是日期
if (dateCell.Value != null) {
var date = new Date(dateCell.Value);
if (!isNaN(date.getTime())) {
dateCell.Value = date;
dateCell.NumberFormat = "yyyy-mm-dd";
}
}
}
// 4. 标准化文本（全角转半角、大写转小写等）
for (var i = 2; i <= sheet.UsedRange.Rows.Count; i++) {
var nameCell = sheet.Cells(i, 1);
if (nameCell.Value != null) {
// 去除多余空格
var cleaned = nameCell.Value.toString().replace(/\s+/g, " ");
// 去除特殊字符
cleaned = cleaned.replace(/[^\w\s\u4e00-\u9fa5]/g, "");
nameCell.Value = cleaned;
}
}
// 5. 数据验证标记异常
for (var i = 2; i <= sheet.UsedRange.Rows.Count; i++) {
var ageCell = sheet.Cells(i, 2);
var age = parseInt(ageCell.Value);
if (isNaN(age) || age < 0 || age > 150) {
ageCell.Interior.Color = 255; // 红色标记异常
ageCell.AddComment("年龄数据异常");
}
}
Application.MsgBox("数据清洗完成！\n处理行数：" + (lastRow - 1), 64, "完成");
} catch (e) {
Application.MsgBox("错误：" + e.message, 16, "错误");
} finally {
Application.ScreenUpdating = true;
Application.Calculation = -4105; // 自动计算
}
}
4.2 财务报表自动生成系统
// ==================== 财务报表自动生成 ====================
function GenerateFinancialReport() {
var wb = Application.ActiveWorkbook;
var sourceSheet = wb.Worksheets("原始数据");
var reportSheet;
// 检查报表工作表是否存在
try {
reportSheet = wb.Worksheets("财务报表");
reportSheet.Cells.Clear();
} catch(e) {
reportSheet = wb.Worksheets.Add();
reportSheet.Name = "财务报表";
}
// 设置报表标题
reportSheet.Range("A1").Value = "2024年度财务分析报表";
reportSheet.Range("A1").Font.Size = 18;
reportSheet.Range("A1").Font.Bold = true;
reportSheet.Range("A1:F1").Merge();
reportSheet.Range("A1").HorizontalAlignment = 3; // 居中
// 生成汇总数据
var data = sourceSheet.UsedRange.Value;
var summary = {};
// 按部门汇总（假设B列是部门，D列是金额）
for (var i = 2; i <= data.length; i++) {
var dept = data[i-1][1]; // B列索引为1
var amount = parseFloat(data[i-1][3]) || 0; // D列索引为3
if (!summary[dept]) {
summary[dept] = {income: 0, expense: 0};
}
if (amount > 0) {
summary[dept].income += amount;
} else {
summary[dept].expense += Math.abs(amount);
}
}
// 写入报表
var row = 3;
reportSheet.Range("A" + row).Value = "部门";
reportSheet.Range("B" + row).Value = "收入";
reportSheet.Range("C" + row).Value = "支出";
reportSheet.Range("D" + row).Value = "净利润";
reportSheet.Range("E" + row).Value = "利润率";
// 标题行格式
var titleRange = reportSheet.Range("A" + row + ":E" + row);
titleRange.Font.Bold = true;
titleRange.Interior.Color = 4473924; // 深蓝色
titleRange.Font.Color = 16777215; // 白色
row++;
var totalIncome = 0, totalExpense = 0;
for (var dept in summary) {
var income = summary[dept].income;
var expense = summary[dept].expense;
var profit = income - expense;
var rate = income > 0 ? (profit / income * 100).toFixed(2) + "%" : "0%";
reportSheet.Range("A" + row).Value = dept;
reportSheet.Range("B" + row).Value = income;
reportSheet.Range("C" + row).Value = expense;
reportSheet.Range("D" + row).Value = profit;
reportSheet.Range("E" + row).Value = rate;
// 数字格式
reportSheet.Range("B" + row + ":D" + row).NumberFormat = "¥#,##0.00";
totalIncome += income;
totalExpense += expense;
row++;
}
// 添加合计行
reportSheet.Range("A" + row).Value = "合计";
reportSheet.Range("A" + row).Font.Bold = true;
reportSheet.Range("B" + row).Value = totalIncome;
reportSheet.Range("C" + row).Value = totalExpense;
reportSheet.Range("D" + row).Value = totalIncome - totalExpense;
reportSheet.Range("E" + row).Value = ((totalIncome - totalExpense) / totalIncome * 100).toFixed(2) + "%";
reportSheet.Range("A" + row + ":E" + row).Interior.Color = 49407; // 橙色背景
// 自动调整列宽
reportSheet.Columns("A:E").AutoFit();
// 添加图表
var chartRange = reportSheet.Range("A3:C" + (row - 1));
var chart = reportSheet.ChartObjects.Add(300, 50, 400, 250).Chart;
chart.SetSourceData(chartRange);
chart.ChartType = 51; // 簇状柱形图
chart.HasTitle = true;
chart.ChartTitle.Text = "各部门收支对比";
Application.MsgBox("财务报表生成完成！", 64, "成功");
}
4.3 批量文件处理器
// ==================== 批量处理多个Excel文件 ====================
function BatchFileProcessor() {
var fd = Application.FileDialog(4); // 选择文件夹
fd.Title = "选择包含Excel文件的文件夹";
if (fd.Show() != -1) {
Application.MsgBox("未选择文件夹", 48, "取消");
return;
}
var folderPath = fd.SelectedItems(1);
var fso = new ActiveXObject("Scripting.FileSystemObject");
var folder = fso.GetFolder(folderPath);
var files = folder.Files;
var processedCount = 0;
var errorFiles = [];
Application.ScreenUpdating = false;
Application.DisplayAlerts = false;
// 创建汇总工作簿
var summaryWb = Application.Workbooks.Add();
var summarySheet = summaryWb.Worksheets(1);
summarySheet.Name = "处理汇总";
summarySheet.Range("A1:F1").Value = ["文件名", "处理时间", "数据行数", "处理状态", "错误信息", "文件路径"];
summarySheet.Range("A1:F1").Font.Bold = true;
summarySheet.Range("A1:F1").Interior.Color = 49407;
var summaryRow = 2;
// 遍历所有Excel文件
var enumerator = new Enumerator(files);
for (; !enumerator.atEnd(); enumerator.moveNext()) {
var file = enumerator.item();
var ext = fso.GetExtensionName(file.Name).toLowerCase();
if (ext == "xlsx" || ext == "xls") {
try {
var wb = Application.Workbooks.Open(file.Path);
var sheet = wb.Worksheets(1);
var rowCount = sheet.UsedRange.Rows.Count;
// 在这里执行具体的处理逻辑
// 示例：添加处理标记
sheet.Range("Z1").Value = "已处理";
sheet.Range("Z2").Value = new Date();
// 保存并关闭
wb.Save();
wb.Close();
// 记录到汇总表
summarySheet.Cells(summaryRow, 1).Value = file.Name;
summarySheet.Cells(summaryRow, 2).Value = new Date();
summarySheet.Cells(summaryRow, 3).Value = rowCount;
summarySheet.Cells(summaryRow, 4).Value = "成功";
summarySheet.Cells(summaryRow, 6).Value = file.Path;
processedCount++;
} catch (e) {
errorFiles.push(file.Name + ": " + e.message);
summarySheet.Cells(summaryRow, 1).Value = file.Name;
summarySheet.Cells(summaryRow, 2).Value = new Date();
summarySheet.Cells(summaryRow, 4).Value = "失败";
summarySheet.Cells(summaryRow, 5).Value = e.message;
summarySheet.Cells(summaryRow, 6).Value = file.Path;
}
summaryRow++;
}
}
Application.ScreenUpdating = true;
Application.DisplayAlerts = true;
// 调整汇总表格式
summarySheet.Columns("A:F").AutoFit();
// 保存汇总文件
var summaryPath = folderPath + "\\处理汇总_" + FormatDate(new Date()) + ".xlsx";
summaryWb.SaveAs(summaryPath);
// 显示结果
var msg = "处理完成！\n成功处理：" + processedCount + "个文件\n";
if (errorFiles.length > 0) {
msg += "失败：" + errorFiles.length + "个文件\n";
msg += "失败列表：\n" + errorFiles.join("\n");
}
Application.MsgBox(msg, 64, "批处理完成");
}
// 辅助函数：格式化日期为文件名
function FormatDate(date) {
var y = date.getFullYear();
var m = (date.getMonth() + 1).toString().padStart(2, '0');
var d = date.getDate().toString().padStart(2, '0');
var h = date.getHours().toString().padStart(2, '0');
var min = date.getMinutes().toString().padStart(2, '0');
return y + m + d + "_" + h + min;
}
4.4 邮件自动发送系统（结合Outlook）
// ==================== 自动发送邮件（需安装Outlook）====================
function SendEmailWithAttachment() {
try {
// 创建Outlook应用对象
var outlookApp = new ActiveXObject("Outlook.Application");
var mailItem = outlookApp.CreateItem(0); // 0 = olMailItem
// 设置邮件属性
mailItem.To = "recipient@example.com";
mailItem.CC = "cc@example.com";
mailItem.Subject = "【自动发送】月度销售报表 - " + FormatDate(new Date());
// 设置邮件正文（HTML格式）
var htmlBody = "<html><body>";
htmlBody += "<h2>月度销售报表</h2>";
htmlBody += "<p>您好，</p>";
htmlBody += "<p>请查收本月的销售数据报表，详情如下：</p>";
htmlBody += "<table border='1'><tr><th>区域</th><th>销售额</th><th>增长率</th></tr>";
// 从Excel读取数据插入邮件
var sheet = Application.ActiveSheet;
var data = sheet.Range("A2:C10").Value;
for (var i = 0; i < data.length; i++) {
htmlBody += "<tr>";
htmlBody += "<td>" + data[i][0] + "</td>";
htmlBody += "<td>" + data[i][1] + "</td>";
htmlBody += "<td>" + data[i][2] + "</td>";
htmlBody += "</tr>";
}
htmlBody += "</table>";
htmlBody += "<p>此邮件由WPS宏自动生成，请勿直接回复。</p>";
htmlBody += "</body></html>";
mailItem.HTMLBody = htmlBody;
// 添加附件
var wb = Application.ActiveWorkbook;
var tempPath = "C:\\Temp\\报表_" + FormatDate(new Date()) + ".xlsx";
wb.SaveCopyAs(tempPath);
mailItem.Attachments.Add(tempPath);
// 发送邮件（或显示预览）
// mailItem.Send(); // 直接发送
mailItem.Display(); // 显示预览，让用户确认后发送
Application.MsgBox("邮件已准备就绪！", 64, "成功");
} catch (e) {
Application.MsgBox("发送邮件失败：" + e.message + "\n请确保已安装Outlook", 16, "错误");
}
}
4.5 数据库连接与数据同步
// ==================== 连接Access数据库 ====================
function ConnectToAccess() {
try {
// 创建ADO连接
var conn = new ActiveXObject("ADODB.Connection");
var connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\数据库\\data.accdb;Persist Security Info=False;";
conn.Open(connStr);
// 执行查询
var rs = new ActiveXObject("ADODB.Recordset");
var sql = "SELECT * FROM 员工表 WHERE 部门='技术部'";
rs.Open(sql, conn, 1, 3); // 1=adOpenKeyset, 3=adLockOptimistic
// 将数据写入Excel
var sheet = Application.ActiveSheet;
sheet.Cells.Clear();
// 写入标题
for (var i = 0; i < rs.Fields.Count; i++) {
sheet.Cells(1, i + 1).Value = rs.Fields(i).Name;
}
// 写入数据
var row = 2;
while (!rs.EOF) {
for (var i = 0; i < rs.Fields.Count; i++) {
sheet.Cells(row, i + 1).Value = rs.Fields(i).Value;
}
rs.MoveNext();
row++;
}
// 关闭连接
rs.Close();
conn.Close();
sheet.Columns.AutoFit();
Application.MsgBox("数据同步完成，共导入" + (row - 2) + "条记录", 64, "成功");
} catch (e) {
Application.MsgBox("数据库连接失败：" + e.message, 16, "错误");
}
}
// ==================== 连接SQL Server ====================
function ConnectToSQLServer() {
try {
var conn = new ActiveXObject("ADODB.Connection");
var connStr = "Provider=SQLOLEDB;Data Source=服务器地址;Initial Catalog=数据库名;User ID=用户名;Password=密码;";
conn.Open(connStr);
// 执行存储过程或SQL语句
var cmd = new ActiveXObject("ADODB.Command");
cmd.ActiveConnection = conn;
cmd.CommandText = "SELECT * FROM 销售数据 WHERE 日期 BETWEEN ? AND ?";
cmd.CommandType = 1; // adCmdText
// 添加参数
var param1 = cmd.CreateParameter("开始日期", 7, 1, -1, "2024-01-01"); // 7=adDate
var param2 = cmd.CreateParameter("结束日期", 7, 1, -1, "2024-12-31");
cmd.Parameters.Append(param1);
cmd.Parameters.Append(param2);
var rs = cmd.Execute();
// 处理结果集...
rs.Close();
conn.Close();
} catch (e) {
Application.MsgBox("SQL Server连接失败：" + e.message, 16, "错误");
}
}
第五部分：优化篇
5.1 性能优化技巧
// ==================== 高性能数据处理模式 ====================
function HighPerformanceProcessing() {
var sheet = Application.ActiveSheet;
// 1. 关闭不必要的功能
Application.ScreenUpdating = false; // 关闭屏幕刷新
Application.Calculation = 2; // 手动计算（-4105=自动, 2=手动）
Application.EnableEvents = false; // 禁用事件
Application.DisplayStatusBar = false; // 关闭状态栏
// 2. 使用数组处理大数据（避免频繁读写单元格）
var startTime = new Date().getTime();
var dataRange = sheet.Range("A1:J10000");
var data = dataRange.Value; // 一次性读取到数组
// 在内存中处理数据
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
if (typeof data[i][j] === 'number') {
data[i][j] = data[i][j] * 1.1; // 批量计算
}
}
}
// 一次性写回
dataRange.Value = data;
var endTime = new Date().getTime();
Application.ScreenUpdating = true;
Application.MsgBox("处理10,000行数据耗时：" + (endTime - startTime) + "毫秒");
// 3. 使用UsedRange优化
var usedRange = sheet.UsedRange;
var lastRow = usedRange.Rows.Count;
var lastCol = usedRange.Columns.Count;
// 4. 批量删除（从下往上删）
for (var i = lastRow; i >= 1; i--) {
if (sheet.Cells(i, 1).Value == "删除标记") {
sheet.Rows(i).Delete();
}
}
// 5. 恢复设置
Application.ScreenUpdating = true;
Application.Calculation = -4105; // 恢复自动计算
Application.EnableEvents = true;
Application.DisplayStatusBar = true;
}
// ==================== 内存管理 ====================
function MemoryManagement() {
// 及时释放COM对象（在WPS JS中尤为重要）
var wb = Application.ActiveWorkbook;
var sheet = wb.Worksheets(1);
var range = sheet.Range("A1");
// 使用完后释放引用（虽然JS有垃圾回收，但显式释放更好）
range = null;
sheet = null;
wb = null;
// 强制垃圾回收（在某些环境中可用）
// CollectGarbage(); // IE环境
}
5.2 错误处理与调试
// ==================== 完善的错误处理机制 ====================
function RobustErrorHandling() {
var errorLog = [];
var sheet = Application.ActiveSheet;
// 1. 基本Try-Catch
try {
// 可能出错的代码
var result = 1 / 0;
} catch (e) {
errorLog.push("计算错误：" + e.message);
}
// 2. 结构化错误处理
function SafeExecute(func, context) {
try {
return func.call(context);
} catch (e) {
errorLog.push({
function: func.name,
message: e.message,
line: e.lineNumber,
time: new Date()
});
return null;
}
}
// 3. 输入验证
function ValidateInput(value, type, required) {
if (required && (value == null || value == "")) {
throw new Error("必填项不能为空");
}
if (type === "number" && isNaN(parseFloat(value))) {
throw new Error("必须是数字");
}
if (type === "date" && isNaN(new Date(value).getTime())) {
throw new Error("必须是有效日期");
}
return true;
}
// 4. 断言函数
function Assert(condition, message) {
if (!condition) {
throw new Error("断言失败：" + message);
}
}
// 使用示例
try {
ValidateInput(sheet.Range("A1").Value, "number", true);
Assert(sheet.Range("B1").Value > 0, "B1必须大于0");
// 执行业务逻辑
ProcessData();
} catch (e) {
Application.MsgBox("业务逻辑错误：" + e.message, 16, "错误");
// 记录到单元格
sheet.Range("Z1").Value = "错误：" + e.message + " 时间：" + new Date();
}
// 5. 批量操作中的错误恢复
var data = sheet.Range("A1:A100").Value;
var successCount = 0;
for (var i = 0; i < data.length; i++) {
try {
// 处理每一行数据
ProcessRow(data[i]);
successCount++;
} catch (rowError) {
errorLog.push("第" + (i+1) + "行错误：" + rowError.message);
// 继续处理下一行，不中断
}
}
// 输出错误日志
if (errorLog.length > 0) {
var logSheet = GetOrCreateSheet("错误日志");
logSheet.Range("A1").Resize(errorLog.length, 1).Value = errorLog.map(function(e) {
return typeof e === 'object' ? JSON.stringify(e) : e;
});
}
Application.MsgBox("处理完成：成功" + successCount + "条，失败" + errorLog.length + "条");
}
// 辅助函数：获取或创建工作表
function GetOrCreateSheet(name) {
var wb = Application.ActiveWorkbook;
try {
return wb.Worksheets(name);
} catch(e) {
var sheet = wb.Worksheets.Add();
sheet.Name = name;
return sheet;
}
}
5.3 代码模块化与重用
// ==================== 工具类模块 ====================
var ExcelUtils = {
// 获取已用范围（带缓存）
GetUsedRange: function(sheet) {
if (!sheet) sheet = Application.ActiveSheet;
return sheet.UsedRange;
},
// 安全读取单元格值
SafeGetValue: function(cell, defaultValue) {
try {
var val = cell.Value;
return val == null ? defaultValue : val;
} catch(e) {
return defaultValue;
}
},
// 批量写入（高性能）
BatchWrite: function(sheet, startCell, dataArray) {
var rows = dataArray.length;
var cols = dataArray[0].length;
var range = sheet.Range(startCell).Resize(rows, cols);
range.Value = dataArray;
},
// 查找最后一行
GetLastRow: function(sheet, column) {
if (!column) column = 1;
return sheet.Cells(sheet.Rows.Count, column).End(-4162).Row; // -4162 = xlUp
},
// 格式化表格
FormatAsTable: function(range, styleName) {
if (!styleName) styleName = "TableStyleMedium2";
range.Worksheet.ListObjects.Add(1, range, null, 1).TableStyle = styleName;
// 1=有标题, 1=显示汇总
},
// 导出为PDF
ExportToPDF: function(workbook, filePath) {
workbook.ExportAsFixedFormat(0, filePath, 0, 1, 0, 1, 1, 0, 0);
// 0=PDF格式, 0=标准质量, 1=包含属性, 0=不缩小, 1=从第1页开始
},
// 发送HTTP请求（简化版）
HttpGet: function(url) {
var xhr = new XMLHttpRequest();
xhr.open("GET", url, false); // 同步请求
xhr.send();
if (xhr.status == 200) {
return xhr.responseText;
}
throw new Error("HTTP错误：" + xhr.status);
}
};
// ==================== 使用工具类 ====================
function UseUtilities() {
var sheet = Application.ActiveSheet;
// 使用工具方法
var lastRow = ExcelUtils.GetLastRow(sheet, "A");
Application.MsgBox("最后一行：" + lastRow);
// 批量写入示例
var data = [["姓名", "年龄"], ["张三", 25], ["李四", 30]];
ExcelUtils.BatchWrite(sheet, "A1", data);
// 格式化表格
ExcelUtils.FormatAsTable(sheet.Range("A1:B3"));
}
第六部分：扩展篇
6.1 调用Windows API与Shell
// ==================== 系统级操作 ====================
function SystemOperations() {
var shell = new ActiveXObject("WScript.Shell");
var fso = new ActiveXObject("Scripting.FileSystemObject");
// 1. 执行Shell命令
// shell.Run("notepad.exe", 1, true); // 打开记事本，等待关闭
// 2. 创建文件夹
var folderPath = "D:\\WPS宏输出\\" + FormatDate(new Date());
if (!fso.FolderExists(folderPath)) {
fso.CreateFolder(folderPath);
}
// 3. 复制文件
var sourceFile = "D:\\模板.xlsx";
var destFile = folderPath + "\\副本.xlsx";
if (fso.FileExists(sourceFile)) {
fso.CopyFile(sourceFile, destFile);
}
// 4. 读取文本文件
if (fso.FileExists("D:\\config.txt")) {
var ts = fso.OpenTextFile("D:\\config.txt", 1); // 1=ForReading
var content = ts.ReadAll();
ts.Close();
Application.MsgBox("配置文件内容：" + content);
}
// 5. 写入日志文件
var logFile = folderPath + "\\操作日志.txt";
var logStream = fso.OpenTextFile(logFile, 8, true); // 8=ForAppending, true=创建如果不存在
logStream.WriteLine("[" + new Date() + "] 宏执行成功");
logStream.Close();
// 6. 环境变量
var userName = shell.ExpandEnvironmentStrings("%USERNAME%");
var computerName = shell.ExpandEnvironmentStrings("%COMPUTERNAME%");
Application.MsgBox("当前用户：" + userName + "\n计算机名：" + computerName);
}
// ==================== 注册表操作 ====================
function RegistryOperations() {
var shell = new ActiveXObject("WScript.Shell");
// 读取注册表
try {
var value = shell.RegRead("HKEY_CURRENT_USER\\Software\\Microsoft\\Windows\\CurrentVersion\\Explorer\\Shell Folders\\Desktop");
Application.MsgBox("桌面路径：" + value);
} catch(e) {
Application.MsgBox("读取注册表失败");
}
// 写入注册表（谨慎使用）
// shell.RegWrite("HKEY_CURRENT_USER\\Software\\MyApp\\Setting", "Value", "REG_SZ");
}
6.2 JSON数据处理与Web API
// ==================== JSON解析与生成 ====================
function JSONOperations() {
// 1. 解析JSON字符串
var jsonStr = '{"name": "张三", "age": 28, "skills": ["Excel", "JS宏", "数据分析"]}';
// WPS JS使用JavaScript引擎，原生支持JSON
var obj = JSON.parse(jsonStr);
Application.MsgBox("姓名：" + obj.name + "，技能：" + obj.skills.join(", "));
// 2. 生成JSON
var data = {
reportName: "月度销售报表",
generatedAt: new Date().toISOString(),
data: [
{region: "华北", sales: 150000},
{region: "华南", sales: 230000},
{region: "华东", sales: 180000}
],
total: 560000
};
var jsonOutput = JSON.stringify(data, null, 2); // 格式化输出
Application.MsgBox(jsonOutput);
// 3. 将Excel数据转为JSON
var sheet = Application.ActiveSheet;
var range = sheet.Range("A1:C10");
var values = range.Value;
var jsonArray = [];
var headers = values[0]; // 第一行作为标题
for (var i = 1; i < values.length; i++) {
var row = {};
for (var j = 0; j < headers.length; j++) {
row[headers[j]] = values[i][j];
}
jsonArray.push(row);
}
var finalJson = JSON.stringify(jsonArray, null, 2);
// 保存到文件
var fso = new ActiveXObject("Scripting.FileSystemObject");
var file = fso.CreateTextFile("D:\\output.json", true);
file.Write(finalJson);
file.Close();
Application.MsgBox("JSON文件已保存");
}
// ==================== 调用Web API ====================
function CallWebAPI() {
try {
// 创建XMLHTTP对象
var xhr = new XMLHttpRequest();
// GET请求示例
xhr.open("GET", "https://api.exchangerate-api.com/v4/latest/USD", false);
xhr.setRequestHeader("Accept", "application/json");
xhr.send();
if (xhr.status == 200) {
var response = JSON.parse(xhr.responseText);
var rates = response.rates;
// 写入Excel
var sheet = Application.ActiveSheet;
sheet.Range("A1").Value = "货币";
sheet.Range("B1").Value = "汇率";
var row = 2;
for (var currency in rates) {
sheet.Cells(row, 1).Value = currency;
sheet.Cells(row, 2).Value = rates[currency];
row++;
}
sheet.Columns("A:B").AutoFit();
Application.MsgBox("汇率数据已更新");
}
// POST请求示例（发送数据）
/*
var postData = JSON.stringify({
key: "value",
data: "test"
});
xhr.open("POST", "https://api.example.com/submit", false);
xhr.setRequestHeader("Content-Type", "application/json");
xhr.send(postData);
*/
} catch (e) {
Application.MsgBox("API调用失败：" + e.message, 16, "错误");
}
}
6.3 自定义功能区与按钮
// ==================== 自定义快速访问工具栏（通过XML）====================
// 注意：此功能在WPS中可能需要通过插件或特定方式实现
// 以下是在WPS中可用的替代方案
// ==================== 创建自定义菜单（通过VBA/JS混合）====================
function CreateCustomUI() {
// WPS JS宏可以通过修改文档属性来存储配置
// 实际UI自定义通常需要通过WPS的插件开发接口
// 替代方案：创建"宏按钮"工作表
var wb = Application.ActiveWorkbook;
var btnSheet;
try {
btnSheet = wb.Worksheets("快捷操作");
} catch(e) {
btnSheet = wb.Worksheets.Add();
btnSheet.Name = "快捷操作";
}
// 创建按钮形状并绑定宏
var shapes = [
{name: "数据清洗", macro: "DataCleaningTool", color: 49407, top: 50},
{name: "生成报表", macro: "GenerateFinancialReport", color: 5287936, top: 120},
{name: "导出PDF", macro: "ExportToPDF", color: 255, top: 190},
{name: "发送邮件", macro: "SendEmailWithAttachment", color: 10092441, top: 260}
];
// 清除旧按钮
for (var i = btnSheet.Shapes.Count; i >= 1; i--) {
btnSheet.Shapes(i).Delete();
}
// 添加新按钮
for (var i = 0; i < shapes.length; i++) {
var btn = btnSheet.Shapes.AddShape(1, 50, shapes[i].top, 150, 50);
btn.TextFrame.Characters().Text = shapes[i].name;
btn.TextFrame.Characters().Font.Size = 12;
btn.TextFrame.Characters().Font.Bold = true;
btn.TextFrame.Characters().Font.Color.RGB = 16777215; // 白色文字
btn.Fill.ForeColor.RGB = shapes[i].color;
btn.Line.Visible = 0;
// 分配宏（在WPS中需要手动或通过其他方式绑定）
btn.OnAction = shapes[i].macro;
}
// 添加说明文字
btnSheet.Range("B2").Value = "点击上方按钮执行对应操作";
btnSheet.Range("B2").Font.Size = 14;
btnSheet.Range("B2").Font.Bold = true;
Application.MsgBox("快捷操作面板已创建！请在'快捷操作'工作表中使用", 64, "完成");
}
6.4 完整项目：进销存管理系统
// ==================== 进销存管理系统（完整示例）====================
var InventorySystem = {
// 初始化系统
Init: function() {
var wb = Application.ActiveWorkbook;
// 创建必要的工作表
this.CreateSheetIfNotExists(wb, "商品信息");
this.CreateSheetIfNotExists(wb, "入库记录");
this.CreateSheetIfNotExists(wb, "出库记录");
this.CreateSheetIfNotExists(wb, "库存查询");
this.CreateSheetIfNotExists(wb, "统计报表");
// 初始化表结构
this.InitProductSheet(wb.Worksheets("商品信息"));
this.InitInboundSheet(wb.Worksheets("入库记录"));
this.InitOutboundSheet(wb.Worksheets("出库记录"));
Application.MsgBox("进销存系统初始化完成！", 64, "成功");
},
// 创建表（如果不存在）
CreateSheetIfNotExists: function(wb, name) {
try {
wb.Worksheets(name);
} catch(e) {
var sheet = wb.Worksheets.Add();
sheet.Name = name;
}
},
// 初始化商品信息表
InitProductSheet: function(sheet) {
var headers = ["商品编码", "商品名称", "规格型号", "单位", "分类", "单价", "最低库存", "备注"];
this.SetHeaders(sheet, headers);
},
// 初始化入库记录表
InitInboundSheet: function(sheet) {
var headers = ["入库单号", "日期", "商品编码", "商品名称", "数量", "单价", "金额", "供应商", "操作员", "备注"];
this.SetHeaders(sheet, headers);
},
// 初始化出库记录表
InitOutboundSheet: function(sheet) {
var headers = ["出库单号", "日期", "商品编码", "商品名称", "数量", "单价", "金额", "客户", "操作员", "备注"];
this.SetHeaders(sheet, headers);
},
// 设置表头
SetHeaders: function(sheet, headers) {
sheet.Cells.Clear();
for (var i = 0; i < headers.length; i++) {
sheet.Cells(1, i + 1).Value = headers[i];
sheet.Cells(1, i + 1).Font.Bold = true;
sheet.Cells(1, i + 1).Interior.Color = 49407;
}
sheet.Columns.AutoFit();
},
// 添加入库记录
AddInbound: function() {
var sheet = Application.ActiveWorkbook.Worksheets("入库记录");
var lastRow = ExcelUtils.GetLastRow(sheet, "A") + 1;
// 生成入库单号
var orderNo = "RK" + new Date().getTime();
// 获取输入（简化版，实际应使用InputBox或表单）
var code = Application.InputBox("请输入商品编码：");
var name = Application.InputBox("请输入商品名称：");
var qty = parseInt(Application.InputBox("请输入数量："));
var price = parseFloat(Application.InputBox("请输入单价："));
// 写入记录
sheet.Cells(lastRow, 1).Value = orderNo;
sheet.Cells(lastRow, 2).Value = new Date();
sheet.Cells(lastRow, 3).Value = code;
sheet.Cells(lastRow, 4).Value = name;
sheet.Cells(lastRow, 5).Value = qty;
sheet.Cells(lastRow, 6).Value = price;
sheet.Cells(lastRow, 7).Value = qty * price;
sheet.Cells(lastRow, 8).Value = Application.InputBox("请输入供应商：");
sheet.Cells(lastRow, 9).Value = Application.UserName;
Application.MsgBox("入库记录已添加，单号：" + orderNo);
},
// 库存查询
QueryInventory: function() {
var productSheet = Application.ActiveWorkbook.Worksheets("商品信息");
var inboundSheet = Application.ActiveWorkbook.Worksheets("入库记录");
var outboundSheet = Application.ActiveWorkbook.Worksheets("出库记录");
var querySheet = Application.ActiveWorkbook.Worksheets("库存查询");
querySheet.Cells.Clear();
// 构建库存汇总
var inventory = {};
// 统计入库
var inboundData = inboundSheet.UsedRange.Value;
for (var i = 1; i < inboundData.length; i++) {
var code = inboundData[i][2];
var qty = parseFloat(inboundData[i][4]) || 0;
if (!inventory[code]) inventory[code] = {in: 0, out: 0};
inventory[code].in += qty;
}
// 统计出库
var outboundData = outboundSheet.UsedRange.Value;
for (var i = 1; i < outboundData.length; i++) {
var code = outboundData[i][2];
var qty = parseFloat(outboundData[i][4]) || 0;
if (!inventory[code]) inventory[code] = {in: 0, out: 0};
inventory[code].out += qty;
}
// 输出查询结果
querySheet.Range("A1:F1").Value = ["商品编码", "商品名称", "入库总量", "出库总量", "当前库存", "状态"];
querySheet.Range("A1:F1").Font.Bold = true;
var row = 2;
var productData = productSheet.UsedRange.Value;
for (var i = 1; i < productData.length; i++) {
var code = productData[i][0];
var name = productData[i][1];
var minStock = parseFloat(productData[i][6]) || 0;
var inv = inventory[code] || {in: 0, out: 0};
var current = inv.in - inv.out;
querySheet.Cells(row, 1).Value = code;
querySheet.Cells(row, 2).Value = name;
querySheet.Cells(row, 3).Value = inv.in;
querySheet.Cells(row, 4).Value = inv.out;
querySheet.Cells(row, 5).Value = current;
// 库存状态判断
if (current <= 0) {
querySheet.Cells(row, 6).Value = "缺货";
querySheet.Cells(row, 6).Interior.Color = 255; // 红色
} else if (current < minStock) {
querySheet.Cells(row, 6).Value = "库存不足";
querySheet.Cells(row, 6).Interior.Color = 49407; // 橙色
} else {
querySheet.Cells(row, 6).Value = "正常";
querySheet.Cells(row, 6).Interior.Color = 5296274; // 绿色
}
row++;
}
querySheet.Columns.AutoFit();
},
// 生成统计报表
GenerateReport: function() {
var reportSheet = Application.ActiveWorkbook.Worksheets("统计报表");
this.QueryInventory(); // 先更新库存
// 统计汇总数据
var querySheet = Application.ActiveWorkbook.Worksheets("库存查询");
var data = querySheet.UsedRange.Value;
var totalProducts = 0;
var lowStock = 0;
var outOfStock = 0;
var totalValue = 0;
for (var i = 1; i < data.length; i++) {
totalProducts++;
var status = data[i][5];
var stock = parseFloat(data[i][4]) || 0;
if (status == "缺货") outOfStock++;
else if (status == "库存不足") lowStock++;
// 计算库存价值（需要关联商品单价）
totalValue += stock * 100; // 简化计算
}
// 生成报表
reportSheet.Cells.Clear();
reportSheet.Range("A1").Value = "进销存统计报表";
reportSheet.Range("A1").Font.Size = 16;
reportSheet.Range("A1").Font.Bold = true;
reportSheet.Range("A3").Value = "统计时间：";
reportSheet.Range("B3").Value = new Date();
reportSheet.Range("A5").Value = "商品总数：";
reportSheet.Range("B5").Value = totalProducts;
reportSheet.Range("A6").Value = "缺货商品：";
reportSheet.Range("B6").Value = outOfStock;
reportSheet.Range("B6").Font.Color = 255;
reportSheet.Range("A7").Value = "库存不足：";
reportSheet.Range("B7").Value = lowStock;
reportSheet.Range("B7").Font.Color = 49407;
reportSheet.Range("A8").Value = "库存总值：";
reportSheet.Range("B8").Value = totalValue;
reportSheet.Range("B8").NumberFormat = "¥#,##0.00";
reportSheet.Columns.AutoFit();
// 创建图表
if (reportSheet.ChartObjects.Count > 0) {
reportSheet.ChartObjects(1).Delete();
}
var chart = reportSheet.ChartObjects.Add(250, 50, 400, 250).Chart;
chart.ChartType = 51; // 柱形图
// 使用报表数据创建图表
var chartData = [
["指标", "数量"],
["正常", totalProducts - lowStock - outOfStock],
["不足", lowStock],
["缺货", outOfStock]
];
reportSheet.Range("D1:E4").Value = chartData;
chart.SetSourceData(reportSheet.Range("D1:E4"));
chart.HasTitle = true;
chart.ChartTitle.Text = "库存状态分布";
}
};
// 快捷入口函数
function InitInventorySystem() { InventorySystem.Init(); }
function AddInboundRecord() { InventorySystem.AddInbound(); }
function QueryStock() { InventorySystem.QueryInventory(); }
function GenerateInventoryReport() { InventorySyste
本文系作者 @
雨落祈星台
原创发布在 雨落Droplogue ，未经许可，禁止转载。
评论