sheetJs / xlsx-js-style 纯前端实现导出 excel 表格及自定义单元格样式

文章目录

• 一、安装
• 二、创建基础工作表
• 三、设置单元格宽度/高度/隐藏单元格
• 四、分配数字格式
• 五、超链接
• 六、单元格注释
• 七、公式
• 八、合并单元格
• 九、自定义单元格样式
• 十、项目地址

  ---

  一、安装

  • xlsx 地址：https://www.npmjs.com/package/xlsx
  • SheetJs 地址：https://docs.sheetjs.com/docs/
  • xlsx-js-style 地址：https://www.npmjs.com/package/xlsx-js-style

    npm install xlsx-js-style
    

    二、创建基础工作表

    import { utils, writeFileXLSX } from 'xlsx'
    const list = [
        { Name: 'Bill Clinton', Index: 42 },
        { Name: 'GeorgeW Bush', Index: 43 },
        { Name: 'Barack Obama', Index: 44 },
        { Name: 'Donald Trump', Index: 45 },
        { Name: 'Joseph Biden', Index: 46 },
    ]
    const exportFile = () => {
        // 创建一个工作簿 workbook
        const workBook = utils.book_new()
        // 创建工作表 worksheet
        // json_to_sheet 	是将【由对象组成的数组】转化成sheet
        // aoa_to_sheet  	是将【一个二维数组】转化成 sheet
        // table_to_sheet  	是将【table的dom】直接转成sheet
        // 这里我们使用 json_to_sheet
        const workSheet = utils.json_to_sheet(list)
        // 将工作表放入工作簿中
        // utils.book_append_sheet(workbook, worksheet, name, true);
        utils.book_append_sheet(workBook, workSheet, 'Data')
        // 生成数据保存
        writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
            bookType: 'xlsx',
        })
    }
    
    
        
    
            创建工作表
        
    
    

    

    三、设置单元格宽度/高度/隐藏单元格

    • !cols 设置列宽

      cols 为一个对象数组，依次表示每一列的宽度

      wpx 字段表示以像素为单位，wch 字段表示以字符为单位

      hidden 如果为真，则隐藏该列

    • !rows 设置行高

      rows 为一个对象数组，依次表示每一行的高度

      import { utils, writeFileXLSX } from 'xlsx-js-style'
      const list = [
          {
              Name: 'Bill Clinton',
              Date: '2023-01-01',
              'Source category name': 'Excise Taxes',
              'Source subcategory name': 'Corporation Income Taxes',
          },
          {
              Name: 'GeorgeW Bush',
              Date: '2023-01-01',
              'Source category name': 'Excise Taxes',
              'Source subcategory name': 'Corporation Income Taxes',
          },
          {
              Name: 'Barack Obama',
              Date: '2023-01-01',
              'Source category name': 'Excise Taxes',
              'Source subcategory name': 'Corporation Income Taxes',
          },
          {
              Name: 'Donald Trump',
              Date: '2023-01-01',
              'Source category name': 'Excise Taxes',
              'Source subcategory name': 'Corporation Income Taxes',
          },
          {
              Name: 'Joseph Biden',
              Date: '2023-01-01',
              'Source category name': 'Excise Taxes',
              'Source subcategory name': 'Corporation Income Taxes',
          },
      ]
      const exportFile = () => {
          // 创建一个工作簿 workbook
          const workBook = utils.book_new()
          // 创建工作表 worksheet
          const workSheet = utils.json_to_sheet(list)
          // 设置列宽
          // cols 为一个对象数组，依次表示每一列的宽度
          // wpx 字段表示以像素为单位，wch 字段表示以字符为单位
          // hidden 如果为真，则隐藏该列
          workSheet['!cols'] = [
              { wpx: 100 },
              { wch: 50 },
              { width: 30 },
              { hidden: true },
          ]
          // 设置行高
          // rows 为一个对象数组，依次表示每一行的高度
          workSheet['!rows'] = [{ hpx: 30 }, { hpt: 50 }, { hidden: true }]
          // 将工作表放入工作簿中
          // utils.book_append_sheet(workbook, worksheet, name, true);
          utils.book_append_sheet(workBook, workSheet, 'Data')
          // 生成数据保存
          writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
              bookType: 'xlsx',
          })
      }
      
      
          
      
              设置单元格宽度/高度/隐藏
          
      
      

      

      四、分配数字格式

      import { utils, writeFileXLSX } from 'xlsx-js-style'
      const list = [
          { Name: 'Barack Obama', Taxes: 726223 },
          { Name: 'GeorgeW Bush', Taxes: 3.5 },
          { Name: 'Bill Clinton', Taxes: 45571 },
          { Name: 'Donald Trump', Taxes: 0.0219 },
          { Name: 'Donald Trump', Taxes: new Date() },
          { Name: 'Joseph Biden', Taxes: 666666 },
      ]
      const exportFile = () => {
          // 创建一个工作簿 workbook
          const workBook = utils.book_new()
          // 创建工作表 worksheet
          const workSheet = utils.json_to_sheet(list)
          // 分配数字格式
          workSheet['B3'].z = '"$"#,##0.00_);\\("$"#,##0.00\\)'
          workSheet['B4'].z = '#,##0'
          workSheet['B5'].z = '0.00%'
          workSheet['B6'].z = 'yyyy-mm-dd hh:mm:ss AM/PM'
          workSheet['B7'].z = '[Red](#,##0)'
          // 将工作表放入工作簿中
          // utils.book_append_sheet(workbook, worksheet, name, true);
          utils.book_append_sheet(workBook, workSheet, 'Data')
          // 生成数据保存
          writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
              bookType: 'xlsx',
          })
      }
      
      
          
      
              分配数字格式
          
      
      

      

      五、超链接

      import { utils, writeFileXLSX } from 'xlsx-js-style'
      const list = [
          { Name: 'https://sheetjs.com' },
          { Name: '电子邮箱' },
          { Name: '访问 C 盘文件' },
          { Name: '选中指定单元格' },
          { Name: '跳转指定 Sheet' },
          { Name: 'Joseph Biden' },
      ]
      const exportFile = () => {
          // 创建一个工作簿 workbook
          const workBook = utils.book_new()
          // 创建工作表 worksheet
          const workSheet = utils.json_to_sheet(list)
          // 链接 https://sheetjs.com
          workSheet['A2'].l = {
              Target: 'https://sheetjs.com',
              Tooltip: 'https://sheetjs.com',
          }
          // 链接电子邮箱
          workSheet['A3'].l = { Target: 'mailto:ignored@dev.null' }
          // 访问本地 C 盘文件
          workSheet['A4'].l = { Target: 'file:///C:/Users/pc/Downloads/receipts.xls' }
          // 选中指定单元格 A1:C5
          workSheet['A5'].l = { Target: '#A1:C5', Tooltip: '选中 A1:C5 ' }
          // 跳转指定 Sheet
          workSheet['A6'].l = { Target: '#Data2!A1:C6', Tooltip: 'Data2' }
          workSheet['A7'].l = { Target: '#SheetJSDN', Tooltip: 'Defined Name' }
          // 将工作表放入工作簿中
          // utils.book_append_sheet(workbook, worksheet, name, true);
          utils.book_append_sheet(workBook, workSheet, 'Data')
          // 创建工作表2 worksheet
          var worksheet2 = utils.aoa_to_sheet([['Same', 'Cross', 'Name']])
          utils.book_append_sheet(workBook, worksheet2, 'Data2')
          // 定义的名称, ref 选中的是当前超链接所在单元格位置
          workBook.Workbook = {
              Names: [{ Name: 'SheetJSDN', Ref: 'Data2!A1:A1' }],
          }
          // 生成数据保存
          writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
              bookType: 'xlsx',
          })
      }
      
      
          
      
              超链接
          
      
      

      • 鼠标移上去看到小手标识及提示语，点击即可看到对应的超链接效果；

        

        六、单元格注释

        import { utils, writeFileXLSX } from 'xlsx-js-style'
        const list = [{ Name: 'Vue' }, { Name: 'React' }, { Name: 'Angular' }]
        const exportFile = () => {
            // 创建一个工作簿 workbook
            const workBook = utils.book_new()
            // 创建工作表 worksheet
            const workSheet = utils.json_to_sheet(list)
            // 添加注释
            if (!workSheet.A2.c) workSheet.A2.c = []
            workSheet.A2.c.push({
                a: 'Vue',
                t: 'Vue 是一款用于构建用户界面的 JavaScript 框架',
            })
            if (!workSheet.A3.c) workSheet.A3.c = []
            // 如果设置为 true，则只有当用户将鼠标悬停在注释上时，注释才可见；
            workSheet.A3.c.hidden = true
            workSheet.A3.c.push({
                a: 'React',
                t: 'React 用于构建 Web 和原生交互界面的库',
            })
            if (!workSheet.A4.c) workSheet.A4.c = []
            // 如果设置为 true，则只有当用户将鼠标悬停在注释上时，注释才可见；
            workSheet.A4.c.hidden = true
            workSheet.A4.c.push({
                a: 'Angular',
                t: 'Angular 是一个应用设计框架与开发平台，旨在创建高效而精致的单页面应用',
            })
            // 将工作表放入工作簿中
            utils.book_append_sheet(workBook, workSheet, 'Data')
            // 生成数据保存
            writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
                bookType: 'xlsx',
            })
        }
        
        
            
        
                单元格注释
            
        
        

        

        七、公式

        import { utils, writeFileXLSX, writeFile } from 'xlsx-js-style'
        const list = [
            ['姓名', '语文', '数学', '英语', '总数', '最大值', '姓名去重'],
            ['张三', 80, 100, 100],
            ['李四', 90, 100, 80],
            ['李四', 85, 80, 100],
            ['王五', 100, 85, 90],
            ['张三', 90, 70, 90],
            ['赵六', 95, 90, 80],
            ['张三', 100, 80, 90],
        ]
        const exportSimpleFormula = () => {
            var ws = utils.aoa_to_sheet([
                [6], // A1
                [8], // A2
                [{ t: 'n', v: 3, f: 'SUM(A1,A2)' }], // SUM 函数
                [{ t: 'n', v: 3, f: 'CONCAT("concat：",A1,A2)' }], // CONCAT 函数
            ])
            var wb = utils.book_new()
            utils.book_append_sheet(wb, ws, 'Sheet1')
            writeFile(wb, 'SheetJSFormula.xlsx')
        }
        const exportFile = () => {
            // 创建一个工作簿 workbook
            const workBook = utils.book_new()
            // 创建工作表 worksheet
            const workSheet = utils.aoa_to_sheet(list)
            utils.sheet_set_array_formula(workSheet, 'E2:E8', 'B2:B8+C2:C8+D2:D8', true)
            list.forEach((e: (string | number)[], index: number) => {
                if (index > 0) {
                    utils.sheet_set_array_formula(
                        workSheet,
                        `F${index + 1}`,
                        `MAX(B${index + 1},C${index + 1},D${index + 1})`,
                        true
                    )
                }
            })
            utils.sheet_set_array_formula(
                workSheet,
                'G2:G8',
                '_xlfn.UNIQUE(A2:A8)',
                true
            )
            // 将工作表放入工作簿中
            utils.book_append_sheet(workBook, workSheet, 'Data')
            // 生成数据保存
            writeFileXLSX(workBook, `SheetJSVueAoO.xlsx`, {
                bookType: 'xlsx',
            })
        }
        
        
            
        
                简单公式
                      
                数组公式
            
        
        

        • 给单元格增加公式，比如下面：求指定单元格中的最大值(MAX函数)；
        • 在 “结果” 双击即可看到对应的公式，按 " ESC" 即可退出；

          

          八、合并单元格

          • !merges 设置单元格合并
          • merges 为一个对象数组，每个对象设定了单元格合并的规则
          • 方法一：通过 decode_range 设置范围合并单元格
          • 方法二：手动设置 A1-C1 的单元格合并（s:开始位置, e:结束位置, r:行, c:列）

            import { utils, writeFile } from 'xlsx-js-style'
            const exportFile = () => {
                var ws = utils.aoa_to_sheet([
                    // 特别注意合并的地方后面预留 2 个 null， 否则后面的内容（本例中是第四列其它信息）会被覆盖
                    ['主要信息', null, null, '其它信息'],
                    ['姓名', '性别', '年龄', '注册时间'],
                    ['张三', '男', 18, new Date()],
                    ['李四', '女', 22, new Date()],
                ])
                if (!ws['!merges']) ws['!merges'] = []
                // 方法一：通过 decode_range 设置范围合并单元格
                ws['!merges'].push(utils.decode_range('A1:C1'))
                // 方法二：手动设置 A1-C1 的单元格合并
                // merges 为一个对象数组，每个对象设定了单元格合并的规则
                // s:开始位置, e:结束位置, r:行, c:列
                // ws['!merges'] = [
                //     { s: { r: 0, c: 0 }, e: { r: 0, c: 2 } },
                // ]
                var wb = utils.book_new()
                utils.book_append_sheet(wb, ws, 'Sheet1')
                writeFile(wb, 'SheetJSVueAoO.xlsx')
            }
            
            
                
            
                    合并单元格
                
            
            

            

            九、自定义单元格样式

            import { utils, writeFile } from 'xlsx-js-style'
            const header = [
                [[]], // 占位
                [
                    {}, // 占位
                    {
                        v: `工厂统计表 ${'\n'}`,
                        t: 's',
                        s: {
                            font: {
                                sz: 15, //设置标题的字号
                                bold: true, //设置标题是否加粗
                                name: '宋体',
                            },
                            //设置标题水平竖直方向居中，并自动换行展示
                            alignment: {
                                horizontal: 'center',
                                vertical: 'center',
                                wrapText: true,
                            },
                            fill: {
                                fgColor: { rgb: '9FE3FF' },
                            },
                        },
                    },
                ],
            ]
            const info = [
                [
                    null,
                    {
                        v: '            统计时间：2023/01/01 00:00',
                        t: 's',
                        s: {
                            fill: {
                                fgColor: { rgb: '9FE3FF' },
                            },
                        },
                    },
                ],
                [
                    null,
                    {
                        v: '            统计维度：按月',
                        t: 's',
                        s: {
                            fill: {
                                fgColor: { rgb: '9FE3FF' },
                            },
                        },
                    },
                ],
                [
                    null,
                    {
                        v: '            统计周期：2023/01/01 至 2023/01/01',
                        t: 's',
                        s: {
                            alignment: {
                                vertical: 'top',
                            },
                            fill: {
                                fgColor: { rgb: '9FE3FF' },
                            },
                        },
                    },
                ],
            ]
            const risk = [
                '序号',
                '险别',
                '企财险',
                '家财险',
                '机动车',
                '责任险',
                '意外险',
                '货运险',
                '保证险',
                '其他险',
            ]
            const data = risk.map((e) => {
                return {
                    v: e,
                    t: 's',
                    s: {
                        font: {
                            bold: true, //设置标题是否加粗
                            name: '宋体',
                        },
                        //设置标题水平竖直方向居中，并自动换行展示
                        alignment: {
                            horizontal: 'center',
                            vertical: 'center',
                            wrapText: true,
                        },
                        border: {
                            top: { style: 'thin', color: { rgb: '000000' } },
                            bottom: { style: 'thin', color: { rgb: '000000' } },
                            left: { style: 'thin', color: { rgb: '000000' } },
                            right: { style: 'thin', color: { rgb: '000000' } },
                        },
                        fill: {
                            fgColor: { rgb: '9FE3FF' },
                        },
                    },
                }
            })
            const random = (min: number, max: number): number => {
                return Math.floor(Math.random() * (max - min)) + min
            }
            const dataArr = () => {
                const items: (Object | null)[][] = []
                Array.apply(null, { length: 18 } as any).map((e, index: number) => {
                    const item: (Object | null)[] = [null]
                    Array.apply(null, { length: 10 } as any).map((ele, idx: number) => {
                        item.push({
                            v: idx === 0 ? index + 1 : random(1, 100000),
                            t: 's',
                            s: {
                                font: {
                                    name: '宋体',
                                },
                                alignment: {
                                    horizontal: 'center',
                                    vertical: 'center',
                                },
                                border: {
                                    top: { style: 'thin', color: { rgb: '000000' } },
                                    bottom: { style: 'thin', color: { rgb: '000000' } },
                                    left: { style: 'thin', color: { rgb: '000000' } },
                                    right: { style: 'thin', color: { rgb: '000000' } },
                                },
                            },
                        })
                    })
                    items.push(item)
                })
                return items
            }
            const exportFile = () => {
                var ws = utils.aoa_to_sheet([
                    ...header,
                    ...info,
                    [null, ...data],
                    ...dataArr(),
                ])
                // 合并单元格
                if (!ws['!merges']) ws['!merges'] = []
                ws['!merges'].push(utils.decode_range('B2:K2'))
                ws['!merges'].push(utils.decode_range('B3:K3'))
                ws['!merges'].push(utils.decode_range('B4:K4'))
                ws['!merges'].push(utils.decode_range('B5:K5'))
                ws['!merges'].push(utils.decode_range('A2:A5'))
                ws['!merges'].push(utils.decode_range('L2:L5'))
                // 设置列宽
                // cols 为一个对象数组，依次表示每一列的宽度
                if (!ws['!cols']) ws['!cols'] = []
                ws['!cols'] = [
                    { wpx: 70 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 118 },
                    { wpx: 200 },
                ]
                // 设置行高
                // rows 为一个对象数组，依次表示每一行的高度
                if (!ws['!rows']) ws['!rows'] = []
                ws['!rows'] = [
                    { hpx: 0 },
                    { hpx: 40 },
                    { hpx: 15 },
                    { hpx: 15 },
                    { hpx: 20 },
                    { hpx: 20 },
                    ...Array.apply(null, { length: dataArr().length } as any).map(() => {
                        return { hpx: 20 }
                    }),
                ]
                var wb = utils.book_new()
                utils.book_append_sheet(wb, ws, 'Sheet1')
                writeFile(wb, 'SheetJSVueAoO.xlsx')
            }
            
            
                
            
                    单元格样式
                
            
            

            

            十、项目地址

            项目地址：https://github.com/aibujin/xlsx-js-style