数据与分析
xlsx-official
Generating Excel files with xlsx/exceljs in Node.js. Use when generating .xlsx reports, data exports, dashboards, or spreadsheets from database data.
sabahattink · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add sabahattink/antigravity-fullstack-hq --skill xlsx-official技能说明
name: xlsx-official description: Generating Excel files with xlsx/exceljs in Node.js. Use when generating .xlsx reports, data exports, dashboards, or spreadsheets from database data.
Excel Generation
Option A — ExcelJS (Feature-Rich)
npm install exceljs
npm install -D @types/node
// lib/excel/report.excel.ts
import ExcelJS from 'exceljs'
export async function generateExcelReport(data: ReportData): Promise<Buffer> {
const workbook = new ExcelJS.Workbook()
workbook.creator = 'Full Stack HQ'
workbook.lastModifiedBy = 'system'
workbook.created = new Date()
workbook.modified = new Date()
const sheet = workbook.addWorksheet('Report', {
pageSetup: {
paperSize: 9, // A4
orientation: 'landscape',
fitToPage: true,
},
views: [{ state: 'frozen', xSplit: 0, ySplit: 1 }], // freeze header row
})
// ─── Column Definitions ───────────────────────────────────
sheet.columns = data.columns.map(col => ({
header: col.header,
key: col.key,
width: col.width ?? 20,
style: {
alignment: { horizontal: col.align ?? 'left', wrapText: false },
numFmt: col.format, // e.g. '#,##0.00', 'yyyy-mm-dd'
},
}))
// ─── Style the Header Row ─────────────────────────────────
const headerRow = sheet.getRow(1)
headerRow.eachCell(cell => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FF2563EB' },
}
cell.font = {
name: 'Calibri',
size: 11,
bold: true,
color: { argb: 'FFFFFFFF' },
}
cell.border = {
bottom: { style: 'medium', color: { argb: 'FF1D4ED8' } },
}
})
headerRow.height = 24
// ─── Add Data Rows ────────────────────────────────────────
data.rows.forEach((row, index) => {
const excelRow = sheet.addRow(row)
excelRow.height = 18
// Alternating row colors
if (index % 2 === 0) {
excelRow.eachCell(cell => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFF8FAFC' },
}
})
}
excelRow.eachCell(cell => {
cell.font = { name: 'Calibri', size: 10 }
cell.border = {
bottom: { style: 'thin', color: { argb: 'FFE5E7EB' } },
}
})
})
// ─── Summary Row ──────────────────────────────────────────
const summaryRow = sheet.addRow(data.summary)
summaryRow.eachCell(cell => {
cell.font = { name: 'Calibri', size: 10, bold: true }
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'FFEFF6FF' },
}
cell.border = {
top: { style: 'medium', color: { argb: 'FF2563EB' } },
bottom: { style: 'medium', color: { argb: 'FF2563EB' } },
}
})
// ─── Auto-filter ──────────────────────────────────────────
sheet.autoFilter = {
from: { row: 1, column: 1 },
to: { row: 1, column: data.columns.length },
}
// ─── Return Buffer ────────────────────────────────────────
return workbook.xlsx.writeBuffer() as Promise<Buffer>
}
Multiple Sheets
async function generateMultiSheetReport(report: MultiSheetReport): Promise<Buffer> {
const workbook = new ExcelJS.Workbook()
// Summary sheet
const summarySheet = workbook.addWorksheet('Summary')
addSummarySheet(summarySheet, report.summary)
// Data sheets
for (const section of report.sections) {
const sheet = workbook.addWorksheet(section.name.substring(0, 31)) // Excel 31-char limit
addDataSheet(sheet, section.columns, section.rows)
}
// Charts sheet (using data from other sheets)
const chartSheet = workbook.addWorksheet('Charts')
addChart(workbook, chartSheet, report)
return workbook.xlsx.writeBuffer() as Promise<Buffer>
}
Charts in ExcelJS
function addChart(workbook: ExcelJS.Workbook, sheet: ExcelJS.Worksheet, report: Report) {
// ExcelJS doesn't support charts directly — use chartjs-to-image or
// embed a pre-generated chart image
// Generate chart as PNG buffer (using chartjs-node-canvas)
const chartImage = generateChartPng(report.chartData)
const imageId = workbook.addImage({
buffer: chartImage,
extension: 'png',
})
sheet.addImage(imageId, {
tl: { col: 0, row: 0 },
ext: { width: 800, height: 400 },
editAs: 'oneCell',
})
}
Option B — SheetJS (xlsx) — Simpler
npm install xlsx
npm install -D @types/xlsx
import * as XLSX from 'xlsx'
export function generateSimpleXlsx(data: {
headers: string[]
rows: (string | number | Date)[][]
sheetName?: string
}): Buffer {
const wb = XLSX.utils.book_new()
// Convert to worksheet
const wsData = [data.headers, ...data.rows]
const ws = XLSX.utils.aoa_to_sheet(wsData)
// Set column widths
ws['!cols'] = data.headers.map(() => ({ wch: 20 }))
// Freeze top row
ws['!freeze'] = { xSplit: 0, ySplit: 1 }
XLSX.utils.book_append_sheet(wb, ws, data.sheetName ?? 'Sheet1')
return Buffer.from(XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' }))
}
// From an array of objects
export function objectsToXlsx<T extends Record<string, unknown>>(
rows: T[],
sheetName = 'Data'
): Buffer {
const wb = XLSX.utils.book_new()
const ws = XLSX.utils.json_to_sheet(rows)
XLSX.utils.book_append_sheet(wb, ws, sheetName)
return Buffer.from(XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' }))
}
NestJS Integration
// exports/exports.service.ts
@Injectable()
export class ExportsService {
constructor(private readonly ordersRepo: OrdersRepository) {}
async exportOrders(filter: OrderFilterDto): Promise<Buffer> {
const orders = await this.ordersRepo.findMany(filter)
return generateExcelReport({
columns: [
{ header: 'Order ID', key: 'id', width: 12 },
{ header: 'Customer', key: 'customer', width: 25 },
{ header: 'Date', key: 'date',
阅读完整来源 ↗