XLSX 技能

ExcelInspect { action: "summary", filePath: "…" }

import ExcelJS from 'exceljs'
const wb = new ExcelJS.Workbook()
const ws = wb.addWorksheet('Q1 销售')

// 表头
ws.columns = [
  { header: '产品名称', key: 'name',  width: 20 },
  { header: '区域',     key: 'area',  width: 10 },
  { header: '销量',     key: 'qty',   width: 10 },
  { header: '单价（元）', key: 'price', width: 14, style: { numFmt: '¥#,##0.00' } },
  { header: '收入（元）', key: 'total', width: 14, style: { numFmt: '¥#,##0.00' } },
]
const rows = [
  { name: '智能音箱',  area: '华东', qty: 1240, price: 300 },
  { name: '扫地机器人', area: '华南', qty: 410,  price: 3000 },
  { name: '无线耳机',   area: '华北', qty: 860,  price: 150 },
]
rows.forEach((r, i) => {
  const excelRow = ws.addRow({ ...r, total: null })
  // F 列公式：total = qty * price
  const rowNum = excelRow.number
  ws.getCell(`E${rowNum}`).value = { formula: `C${rowNum}*D${rowNum}` }
})

// 合计行
const totalRow = ws.addRow({
  name: '合计', area: '',
  qty: { formula: `SUM(C2:C${rows.length + 1})` },
  price: null,
  total: { formula: `SUM(E2:E${rows.length + 1})` },
})
totalRow.font = { bold: true }

// 冻结首行 + 表头加粗 + 筛选
ws.views = [{ state: 'frozen', ySplit: 1 }]
ws.getRow(1).font = { bold: true }
ws.autoFilter = { from: 'A1', to: `E${rows.length + 1}` }

await wb.xlsx.writeFile('sales_q1.xlsx')
console.log('sales_q1.xlsx written')

import ExcelJS from 'exceljs'
const wb = new ExcelJS.Workbook()
await wb.xlsx.readFile('sales_q1.xlsx')
const ws = wb.getWorksheet('Q1 销售')

const last = ws.rowCount
ws.getCell(`F1`).value = 'YoY 增长率'
for (let r = 2; r < last; r++) {
  ws.getCell(`F${r}`).value = { formula: `(E${r}-E${r}*0.8)/(E${r}*0.8)` }
  ws.getCell(`F${r}`).numFmt = '0.0%'
}

// 条件格式：F 列 < 0 标红（exceljs 条件格式语法）
ws.addConditionalFormatting({
  ref: `F2:F${last - 1}`,
  rules: [{
    type: 'cellIs',
    operator: 'lessThan',
    formulae: ['0'],
    style: { font: { color: { argb: 'FFFF0000' } } },
    priority: 1,
  }],
})

await wb.xlsx.writeFile('sales_q1.xlsx')
console.log('YoY + conditional format added')

import ExcelJS from 'exceljs'
const wb = new ExcelJS.Workbook()
await wb.xlsx.readFile('sales_q1.xlsx')
const ws = wb.getWorksheet('Q1 销售')

// exceljs 原生不支持 addChart —— 通常用扩展包 office-chart；
// 若需求只是"让人看个图"，更实用的办法：用 sharp/chartjs-node 画 PNG 再 addImage。
// 下面演示 addImage 方案：
import fs from 'node:fs/promises'

// 假设你已经用 chartjs-node-canvas 生成了 bar.png
const imgId = wb.addImage({ buffer: await fs.readFile('bar.png'), extension: 'png' })
ws.addImage(imgId, { tl: { col: 6, row: 1 }, ext: { width: 480, height: 280 } })
await wb.xlsx.writeFile('sales_q1.xlsx')
console.log('chart image embedded')

import fs from 'node:fs/promises'
import ExcelJS from 'exceljs'

const csv = await fs.readFile(process.argv[2] ?? 'in.csv', 'utf-8')
const [headerLine, ...dataLines] = csv.trim().split(/\r?\n/)
const headers = headerLine.split(',')

const wb = new ExcelJS.Workbook()
const ws = wb.addWorksheet('Sheet1')
ws.addRow(headers).font = { bold: true }
for (const line of dataLines) {
  ws.addRow(line.split(',').map(v => (isNaN(+v) || v === '' ? v : Number(v))))
}
ws.columns.forEach(c => { c.width = 18 })
ws.views = [{ state: 'frozen', ySplit: 1 }]

await wb.xlsx.writeFile('out.xlsx')
console.log('csv → xlsx done')

import ExcelJS from 'exceljs'
const wb = new ExcelJS.Workbook()
await wb.xlsx.readFile('in.xlsx')
const errs = []
for (const ws of wb.worksheets) {
  ws.eachRow((row, rn) => {
    row.eachCell((cell, cn) => {
      // exceljs 读出 cell.value 若为 { error: '#REF!' } 形式
      if (cell.value && typeof cell.value === 'object' && cell.value.error) {
        errs.push({ sheet: ws.name, cell: cell.address, error: cell.value.error, formula: cell.formula })
      }
    })
  })
}
console.log(JSON.stringify(errs, null, 2))

DocConvert(from="csv",  to="xlsx", sourcePath="…")
DocConvert(from="xlsx", to="csv",  sourcePath="…")
DocConvert(from="xlsx", to="html", sourcePath="…")