| wps js宏实战案例：批量汇总订单数据、识别黑名单、自动统计业绩、每笔订单生成带条形码的发货单并输出为pdf文件用于打印 | 您所在的位置：网站首页 › wps表格分类 › wps js宏实战案例：批量汇总订单数据、识别黑名单、自动统计业绩、每笔订单生成带条形码的发货单并输出为pdf文件用于打印 |
wps js宏实战案例：批量汇总订单数据、识别黑名单、自动统计业绩、每笔订单生成带条形码的发货单并输出为pdf文件用于打印
|
wps js支持ES6语法，对比vba能节省非常多的代码量，不过缺点也很明显，wps js环境暂时不支持调用win系统组件和第三方控件。小声比比：可能有调用的方法，只是我没发现。 基本需求：可以选择订单文件，支持多选 生成订单汇总表，识别每笔订单的黑名单、外岛地区、边远地区的状态，并标用颜色标记 通过复选框决定是否生成发货单，要求除了黑名单外，每笔订单生成一个条形码，并将数据写入预先设定的发货单模板样式，相同交易序号的需要合并在同一个发货单里面，然后将所有发货单输出为一个pdf文件用于打印 通过复选框决定是否生成业绩表，除了黑名单之外，统计每个店铺的业绩 黑名单、外岛地区、边远地区，可以随时修改 加上客服名字 成品效果图：
订单汇总表、业绩表、发货单pdf的样式
原始订单表格数据
动图演示效果
实现思路：
用FileDialog对象弹出可供用户选择文件的对话框选择文件，以获取选中文件的完整路径 遍历所选文件，循环提取订单数据 获取用户选项，如获取（黑名单、外岛地区、边远地区）名单、模板、客服名字，处理订单数据 合并相同交易序号的订单，将数据写入模板 步骤过程： 一、创建一个按钮添加点击事件处理函数来执行代码；创建两个复选框，根据是否选中来生成发货单pdf、生成业绩表
模板存放位置 
二、根据实现思路，先写好代码框架，功能再一个一个写，通过模块化开发，比较容易理清代码逻辑
//主框架，通过模块化开发，比较容易理清代码逻辑
function CommandButton1_Click() {
let wb = ThisWorkbook //获取当前工作簿
, sht1 = wb.Sheets(1) //获取Sheet1工作表
, sht2 = wb.Sheets(2) //获取汇总表模板
, sht3 = wb.Sheets(3) //获取发货单模板
, sht4 = wb.Sheets(4) //获取业绩表模板
//调用文件对话框，如果用户未选择文件则退出函数执行
let pth = getFilePath()
if (!pth) return sht1.Range('B3').Value2 = ''
//提取订单数据
let data = getFileData(pth)
sht1.Range('B3').Value2 = pth.join('\n')
//提取Sheet1工作表的日期、客服名称、指定文件路径；处理数据，根据订单收件人、电话、地址，识别黑名单、外岛地区、边远地区
let options = getOptions(sht1, data)
//生成订单汇总表
createSummary(sht2, options)
//判断Sheet1工作表的复选框是否选中来生成发货单和业绩表
if (CheckBox1.Value) createDelivery(sht3, options)
if (CheckBox2.Value) createAchievement(sht4, options)
}
三、模块化函数，以下都是模块化封装的函数，可以重复利用，要用的时候直接复制到项目里，提升效率，节省时间
//弹出文件选择对话框，提取已选中文件的完整路径
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
}
//提取Excel文件的活动工作表数据
function getFileData(pth) {
let data = []
for (let i = 0; i < pth.length; i++) {
let wb = Workbooks.Open(pth[i])
, arr = wb.ActiveSheet.Range('A1').CurrentRegion.Value2
wb.Close(false)
if (i > 0) arr.shift() //因为保留一行表头，所以循环到第二个工作簿开始要去掉表头
data = data.concat(arr) //合并数组
}
return data
}
//日期格式化yyyy-mm-dd HH:MM:SS
function dateFormat(fmt, 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
}
四、获取基本选项，生成汇总表、发货单pdf、业绩表的功能代码
//提取Sheet1工作表的当前日期、客服名称、黑名单、外岛地区、边远地区名单，指定文件路径
function getOptions(sht, data) {
const options = {}
, pth = ThisWorkbook.Path + '\\'
, today = dateFormat('yyyy-m-d', new Date)
options['日期'] = today
options['客服'] = sht.Range('B2').Value2
options['汇总表路径'] = pth + today + options['客服'] + '订单汇总表.xlsx'
options['发货单路径'] = pth + today + options['客服'] + '发货单.pdf'
options['业绩表路径'] = pth + today + options['客服'] + '业绩表.xlsx'
let i = 0
, arr = []
, hmd, wd, by //黑名单、外岛地区、边远地区
i = sht.Range('A8').End(xlDown).Row
arr = sht.Range('A8:A' + i).Value2
hmd = new RegExp(arr.join('|')) //黑名单正则
i = sht.Range('B8').End(xlDown).Row
arr = sht.Range('B8:B' + i).Value2
wd = new RegExp(arr.join('|')) //外岛地区正则
i = sht.Range('C8').End(xlDown).Row
arr = sht.Range('C8:C' + i).Value2
by = new RegExp(arr.join('|')) //边远地区正则
//识别黑名单、外岛地区、边远地区
options['data'] = data.map(function (item) {
let str = item[5] + item[6] + item[8]
item.unshift('正常')
if (by.test(str)) item[0] = '边远地区'
if (wd.test(str)) item[0] = '外岛地区'
if (hmd.test(str)) item[0] = '黑名单'
return item
})
return options
}
//生成订单汇总表
function createSummary(sht, options) {
//复制订单汇总表模板到新工作簿
let wb = Workbooks.Add()
sht.Copy(wb.ActiveSheet)
let mysht = wb.ActiveSheet
, data = options['data']
, ros = data.length
//订单汇总表预处理
mysht.Range('B2, C2:R2, U2:AC2, AF2, AH2:AJ2').Value2 = ''
mysht.Range('A2:AM' + ros).FillDown()
mysht.Rows('2:' + ros).RowHeight = 36
//写入数据
mysht.Range('A1:A' + ros).Value2 = data.map(function (item) {
return item.slice(0, 1)
})
mysht.Range('A1').Value2 = '区分类'
mysht.Range('B2:B' + ros).Value2 = options['客服']
mysht.Range('C1:F' + ros).Value2 = data.map(function (item) {
return item.slice(1, 5)
})
mysht.Range('L1:P' + ros).Value2 = data.map(function (item) {
return item.slice(5, 10)
})
mysht.Range('U1:U' + ros).Value2 = data.map(function (item) {
return item.slice(10, 11)
})
mysht.Range('V1:AB' + ros).Value2 = data.map(function (item) {
return item.slice(11)
})
//标记颜色
data.forEach(function (item, i) {
let j = i + 1
if (item[0] == '边远地区') mysht.Range(`A${j}:AM${j}`).Interior.ColorIndex = 33
if (item[0] == '外岛地区') mysht.Range(`A${j}:AM${j}`).Interior.ColorIndex = 43
if (item[0] == '黑名单') mysht.Range(`A${j}:AM${j}`).Interior.ColorIndex = 15
})
//删除Sheet1工作表
Application.DisplayAlerts = false //禁止弹出警告提示
wb.Sheets('Sheet1').Delete()
Application.DisplayAlerts = true
//保存工作簿
wb.SaveAs(options['汇总表路径'])
}
//生成发货单
function createDelivery(sht, options) {
let wb = Workbooks.Add()
sht.Copy(wb.ActiveSheet)
let mysht = wb.ActiveSheet
, data = options['data']
//利用对象方法查重订单
const order = {}
data.forEach(function (item, i) {
if (i == 0) return
if (item[0] == '黑名单') return
let num = item[5]
if (order[num]) {
let con = order[num]
, ros = mysht.Cells(65536, con + 2).End(xlUp).Row + 1
mysht.Range(mysht.Cells(ros, con), mysht.Cells(ros, con + 6)).Value2 = item.slice(11)
mysht.Cells(5, con + 5).Value2 += Number(item[16]) + Number(item[17])
} else {
mysht.Range('A3:I3, A5:I5, A7:I7').Value2 = ''
mysht.Range('A3:E3').Value2 = [options['客服'], item[1], item[2], item[3], item[4]]
mysht.Range('A5:E5').Value2 = item.slice(5, 10)
mysht.Range('F5').Value2 = Number(item[16]) + Number(item[17])
mysht.Range('G5').Value2 = item[10]
mysht.Range('A7:G7').Value2 = item.slice(11)
let mod = mysht.Range('A2:I12')
, arr = [12, 12, 12, 12, 10, 9, 9, 9, 9] //列宽
, con = mysht.Cells(6, Columns.Count).End(xlToLeft).Column + 1 //列号
, link = `https://bwipjs-api.metafloor.com/?bcid=code128&text=${num}&height=9`
mod.Copy(mysht.Cells(2, con))
for (let i in arr) {
mysht.Columns(Number(i) + con).ColumnWidth = arr[i] //设置列宽
}
let lef = mysht.Cells(6, con).Left + 220
mysht.Shapes.AddPicture(link, true, true, lef, 12, 151.5, 40) //插入条形码图片
order[num] = con //记录列号
}
})
//删除A:I列
mysht.Columns('A:I').Delete()
wb.Sheets('Sheet1').Visible = false //隐藏Sheet1工作表
wb.SaveAs(options['发货单路径'], 103) //导出pdf
wb.Close(false) //关闭工作簿
Shell('wps.exe ' + options['发货单路径'], jsNormalNoFocus) //打开pdf文件
}
//生成业绩表
function createAchievement(sht, options) {
let wb = Workbooks.Add()
sht.Copy(wb.ActiveSheet)
let mysht = wb.ActiveSheet
, data = options['data']
//利用对象查重
const order = {}
data.forEach(function (item, i) {
if (i == 0) return
if (item[0] == '黑名单') return
let shop = item[3] //店名
if (order[shop]) { //如果存在店名
order[shop][0] += 1
order[shop][1] += Number(item[16]) + Number(item[17])
} else { //如果不存在店名
order[shop] = [1, Number(item[16]) + Number(item[17])]
}
})
//遍历对象写入数据
let ros = 2
for (let item in order) {
mysht.Range(`C${ros}:E${ros}`).Value2 = [item, order[item][0], order[item][1]]
ros++
}
//设置样式
ros = ros - 1
mysht.Rows('2:' + ros).RowHeight = 33 //设置行高
mysht.Range('A1:G' + ros).Borders.LineStyle = xlContinuous //设置边框
mysht.Range(`A2:A${ros}, B2:B${ros}, F2:F${ros}, G2:G${ros}`).Merge() //合并单元格
mysht.Range('F2').Formula = `=SUM(D2:D${ros})` //设置单元格公式
mysht.Range('G2').Formula = `=SUM(E2:E${ros})`
mysht.Range('A2:B2').Value2 = [options['日期'], options['客服']]
Application.DisplayAlerts = false
wb.Sheets('Sheet1').Delete() //生成Sheet1工作表
Application.DisplayAlerts = true
wb.SaveAs(options['业绩表路径'])
}
源码地址（含4个原始订单数据文件）：
https://download.csdn.net/download/huaermeier/87453744 |
【本文地址】
公司简介
联系我们