wps js宏实战案例：批量汇总订单数据、识别黑名单、自动统计业绩、每笔订单生成带条形码的发货单并输出为pdf文件用于打印

Excel效率人

已于 2023-02-16 02:07:34 修改

阅读量3.8k

点赞数 7

分类专栏： Excel JS 文章标签： wps pdf excel javascript Powered by 金山文档

于 2023-02-13 22:03:21 首次发布

本文链接：https://blog.csdn.net/huaermeier/article/details/129014860

版权

Excel JS 专栏收录该内容

11 篇文章 41 订阅

订阅专栏

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

Excel效率人

关注

7
点赞
踩
39

收藏

觉得还不错? 一键收藏
打赏
0
评论
wps js宏实战案例：批量汇总订单数据、识别黑名单、自动统计业绩、每笔订单生成带条形码的发货单并输出为pdf文件用于打印

wps js支持ES6语法，对比vba能节省非常多的代码量，对于经常用Excel处理数据非常方便
复制链接

扫一扫