wps js宏开发表格数据归类拆分和excel文件合并的xlam加载宏插件 |
您所在的位置：网站首页 › excel中的宏自动修改数据 › wps js宏开发表格数据归类拆分和excel文件合并的xlam加载宏插件 |
wps js宏开发表格数据归类拆分和excel文件合并的xlam加载宏插件
|
前言
用wps js宏编辑器开发的表格数据拆分与合并的加载宏工具，写好代码把工作簿另存为xlam加载宏文件，然后添加到加载项即可使用。xlam文件你可以理解为没有工作表的工作簿，先看动画演示效果： 
主要功能
向功能区添加宏命令按钮来执行代码 能在不同的工作簿运行 数据拆分归类：用户可以指定某列字段进行归类；用户可以设置将归类好的数据分别写入工作表或者工作簿 Excel文件合并：弹出选项窗体保存用户的配置信息，根据用户设置的配置信息进行合并数据 准备文件老规矩先新建一个xlam文件，然后添加到加载项里再编辑代码 具体操作可以回顾我上一期写的文章：wps js自定义功能区，把写好的宏命令加在excel的功能区 为Excel文件合并_选项插入一个窗体，设计样式如下
js源码
一、主体代码框架
//工作簿打开事件，触发该事件的时候向功能区添加宏命令
function Workbook_Open() {
let arr = [
{ title: ['拆分工作表数据', '选项'], jsAction: ['splitSheet', 'splitSheet_option'] },
{ title: ['合并Excel文件', '选项'], jsAction: ['mergeExcelFile', 'mergeExcelFile_option'] }
]
//设置功能区
arr.forEach((obj) => {
let bar = Application.CommandBars.Add() //添加CommandBar对象
bar.Visible = true
for (let i in obj.title) {
let btn = bar.Controls.Add(msoControlButton) //添加Button控件
btn.Caption = obj.title[i] //指定Button名称
btn.OnAction = obj.jsAction[i] //指定要执行的函数
}
})
}
//UserForm1窗体初始化事件，展示用户之前设置好的配置信息
function UserForm1_Initialize() {
let text = GetSetting('addins', 'excelMergeTools', 'option') //提取注册表信息
if (text) {
const option = JSON.parse(text) //JSON字符串转化js对象
for (let item in option) {
UserForm1[item].Value = option[item] //设置Control的Value
}
} else {
let pth = Application.DefaultFilePath + '\\' //提取系统默认文档路径
UserForm1.TextEdit2.Value = pth
UserForm1.TextEdit1.Value = ''
UserForm1.OptionButton1.Value = true
}
}
//UserForm1_CommandButton1点击事件，用于保存用户配置信息
function UserForm1_CommandButton1_Click() {
//判断用户输入的文件夹路径是否合法
let prn = /^[a-zA-Z]:((\\)[\S].+\s?)*\\$/
, str = UserForm1.TextEdit2.Value
if (!prn.test(str)) return alert('默认保存路径不合法！\n正确范例：C:\\Users\\Administrator\\Desktop\\')
//读取配置
const option = {
TextEdit1: UserForm1.TextEdit1.Value,
TextEdit2: UserForm1.TextEdit2.Value,
OptionButton1: UserForm1.OptionButton1.Value,
OptionButton2: UserForm1.OptionButton2.Value,
CheckBox1: UserForm1.CheckBox1.Value
}
let text = JSON.stringify(option) //js对象转化JSON字符串
SaveSetting('addins', 'excelMergeTools', 'option', text) //保存到注册表信息
UserForm1.Close() //卸载窗体
}
//删除配置信息
function deleteSettingTest() {
DeleteSetting('addins', 'splitSheetTools', 'option')
DeleteSetting('addins', 'excelMergeTools', 'option')
}
二、功能代码
写好每个按钮对应的处理函数 //拆分工作表数据 function splitSheet() { //识别数据区域 let data = Range('A1').CurrentRegion.Value2 //获取数据 if (!data) return alert('无法识别数据区域！请确保从A1单元格起有连续非空白的数据区域。') //获取需要处理的列 let inf = Application.InputBox('请输入要拆分的列，大小写字母均可\n例如拆分A列数据则输入a', undefined, 'a') , prn = /^[a-zA-Z]+$/ , col = 0 if (inf == '') return if (!prn.test(inf)) return alert('只能输入字母！') col = Range(inf + 1).Column - 1 //在data二维数组的的第二维索引 //获取配置信息、数据表头字段 let splitMode = GetSetting('addins', 'splitSheetTools', 'option') , mode = splitMode ? splitMode : '工作表' //拆分模式 , th = data[0] //表头字段 //处理数据，按用户所选列归类 const obj = {} //利用对象方法归类 data.forEach((item, i) => { if (i == 0) return if (obj[item[col]]) return obj[item[col]] = obj[item[col]].concat([item]) //合并数组 obj[item[col]] = [th, item] }) //指定文件存放路径、获取当前文件名后缀、创建文件夹 let pth = ActiveWorkbook.Path //获取当前工作簿的路径 , wbName = ActiveWorkbook.Name , suffix = wbName.slice(wbName.lastIndexOf('.')) pth = pth ? pth + '\\split' + dateFormat('yyyymmddHHMMSS') + '\\' : Application.DefaultFilePath + '\\split' + dateFormat('yyyymmddHHMMSS') + '\\' if (mode != '工作表') MkDir(pth) //创建文件夹 //错误提示 let wbErr = '重命名工作簿失败，工作簿名称不能包含以下任意字符：\n\\ / : * ? " < > |' , shtErr = '重命名工作表失败，当前可能存在相同名称的工作表或者工作表名称无效，合法的工作表名称为：\n1.最多不超过31个字符 \n2.不能包含字符：\\ / ? * [ ] \n3.第一个字符或者最后一个字符不能是单引号 \n4.空字符串' //循环写入数据 for (let item in obj) { let data = obj[item] if (mode == '工作表') { //拆分为工作表的操作 let sht = Worksheets.Add(undefined, ActiveSheet) sht.Range('A1').Resize(data.length, data[0].length).Value2 = data //sht.Name = item let ifErr = trycatch(()=>{sht.Name = item}, ()=>{alert(shtErr)}) //错误处理回调函数 if (ifErr) return } else { //拆分为工作簿的操作 let wb = Workbooks.Add() , sht = wb.ActiveSheet , fileName = pth + item + suffix sht.Range('A1').Resize(data.length, data[0].length).Value2 = data //sht.Name = item let ifErr = trycatch(()=>{sht.Name = item}, ()=>{alert(shtErr)}) //错误处理回调函数 if (ifErr) return //wb.SaveAs(fileName) ifErr = trycatch(()=>{wb.SaveAs(fileName)}, ()=>{alert(wbErr)}) if (ifErr) return wb.Close(false) } } //执行完成后提示信息 if (mode != '工作表') { let tip = '数据拆分完成，是否打开文件夹？\n文件路径在：' + pth , msg = MsgBox(tip, jsYesNo) if (msg == 6) return Shell('cmd /c start ' + pth, jsHide) //调用cmd命令打开文件夹 } else { alert('完成') } } //拆分工作表数据_选项 function splitSheet_option() { let splitMode = GetSetting('addins', 'splitSheetTools', 'option') if (splitMode) { let mode = (splitMode == '工作表') ? '工作簿' : '工作表' , tip = '当前数据拆分模式为: ' + splitMode + '\n是否切换为' + mode + '?' , msg = MsgBox(tip, jsYesNo) if (msg == 6) SaveSetting('addins', 'splitSheetTools', 'option', mode) } else { let tip = '当前拆分模式为：工作表\n是否将数据拆分成工作簿？' , msg = MsgBox(tip, jsYesNo) if (msg == 6) return SaveSetting('addins', 'splitSheetTools', 'option', '工作簿') SaveSetting('addins', 'splitSheetTools', 'option', '工作表') } } //合并Excel文件 function mergeExcelFile() { let pth = getFilePath() if (!pth) return //用户未选择文件则退出执行 let text = GetSetting('addins', 'excelMergeTools', 'option') , option = text ? JSON.parse(text) : {} //将配置信息转化js对象 , data = option.OptionButton2 ? getFileData(pth, 1) : getFileData(pth) //判断文件合并模式 if (!data) return //没有读取到数据则退出执行 let wb = Workbooks.Add() //新建工作簿保存数据 , sht = wb.Sheets(1) , sPath = '' sht.Range('A1').Resize(data.length, data[0].length).Value2 = data //写入数据 if (option.CheckBox1) { sPath = Application.GetSaveAsFilename() //弹出文件保存对话框，手动指定文件路径和文件名 } else { //读取用户指定的文件路径、文件名 let filePath = option.TextEdit2 ? option.TextEdit2 : Application.DefaultFilePath + '\\' , fileName = option.TextEdit1 ? option.TextEdit1 + dateFormat('yyyymmddSS') : 'newfile' + dateFormat('yyyymmddHHMMSS') , suffix = pth[0].slice(pth[0].lastIndexOf('.')) sPath = filePath + fileName + suffix } wb.SaveAs(sPath) //保存工作簿 } //合并Excel文件_选项 function mergeExcelFile_option() { UserForm1.Show() } 三、可以多次复用的模块化函数实际开发过程大多都是用现成代码完成的，平时写代码的时候可以尝试封装可以多次复用的代码块，等下次需要用到类似功能的时候，直接复制粘贴改改就能用。 //以下均为模块化函数，具体案例参考我往期写的专栏，这里不多赘述 //需要的时候直接复制就能用，实际开发过程中，大部分都是用现成代码修改完成的 //对话框获取文件完整路径 function getFilePath() { let fd = Application.FileDialog(msoFileDialogFilePicker), pth = [] fd.Filters.Clear() fd.Filters.Add('Excel文件', '*.xls;*.xlsx;*.csv') fd.Title = '请选文件' if (fd.Show() != -1) { alert('你没选中文件！') return '' } for (let i = 0; i < fd.SelectedItems.Count; i++) { pth[i] = fd.SelectedItems.Item(i + 1) } return pth } //循环读取每个工作簿的工作表数据 function getFileData(pth, all) { let data = [], i = 0, th = '' for (let mypth of pth) { let wb = Workbooks.Open(mypth), noall = all ? 0 : wb.ActiveSheet.Index for (let sht of wb.Worksheets) { if (noall && sht.Index != noall) continue let arr = sht.Range('A1').CurrentRegion.Value2 if (!arr) { let tip = '无法识别数据区域！\n是否继续执行并忽略该工作簿/表', yesno = MsgBox(tip, jsYesNo) if (yesno != 6) return continue } if (i == 0) th = arr[0].join() if (th != arr[0].join()) { let tip = '表头字段不一致！\n是否继续执行并忽略该工作簿/表', yesno = MsgBox(tip, jsYesNo) if (yesno != 6) return continue } if (i > 0) arr.shift() data = data.concat(arr) i++ } wb.Close(false) } return data } //日期格式化 function dateFormat(fmt, date) { fmt = fmt || 'yyyy-mm-dd HH:MM:SS' date = date || new Date const opt = { 'y+': date.getFullYear().toString() , 'm+': (date.getMonth() + 1).toString() , 'd+': date.getDate().toString() , 'H+': date.getHours().toString() , 'M+': date.getMinutes().toString() , 'S+': date.getSeconds().toString() } for (let k in opt) { let ret = new RegExp('(' + k + ')').exec(fmt) if (ret) { fmt = fmt.replace(ret[1], (ret[1].length == 1) ? (opt[k]) : (opt[k].padStart(ret[1].length, '0'))) } } return fmt } //错误处理 function trycatch(f, e) { try { f() } catch { e() return '1' } } xlam加载宏源码文件地址https://download.csdn.net/download/huaermeier/87522018 |
【本文地址】
今日新闻 |
推荐新闻 |