# 通過npm安裝
npm install exceljs

# 瀏覽器直接引入
<script src="https://cdn.jsdelivr.net/npm/exceljs@4.3.0/dist/exceljs.min.js"></script>

// 1. 創(chuàng)建工作簿 - 好比新建一個(gè)Excel文件
const workbook = new ExcelJS.Workbook();

/*
Workbook 對(duì)象結(jié)構(gòu)：
- creator: 創(chuàng)建者信息
- lastModifiedBy: 最后修改者
- created: 創(chuàng)建時(shí)間
- modified: 修改時(shí)間
- worksheets: [] 工作表集合
*/

// 2. 添加工作表 - 添加一個(gè)新的工作表頁
const worksheet = workbook.addWorksheet('銷售數(shù)據(jù)');

/*
addWorksheet 參數(shù)選項(xiàng)：
- name: 工作表名稱
- properties: {tabColor, pageSetup等}
- views: 視圖設(shè)置
*/

// 3. 設(shè)置列定義 - 類似數(shù)據(jù)庫表結(jié)構(gòu)
worksheet.columns = [
  { header: '訂單號(hào)', key: 'id', width: 20 },
  { header: '金額', key: 'amount', width: 15, style: { numFmt: '￥#,##0.00' } },
  { header: '日期', key: 'date', width: 15, style: { numFmt: 'yyyy-mm-dd' } }
];

/*
column 配置項(xiàng)：
- header: 列標(biāo)題
- key: 數(shù)據(jù)鍵名
- width: 列寬(字符數(shù))
- style: 列默認(rèn)樣式
*/

// 4. 添加數(shù)據(jù)行
worksheet.addRow({ id: 'ORD-20230001', amount: 1999.9, date: new Date() });

/*
addRow 方法特性：
- 接受對(duì)象/數(shù)組格式數(shù)據(jù)
- 自動(dòng)匹配column定義的key
- 返回Row對(duì)象可繼續(xù)操作
*/

// 5. 導(dǎo)出Excel文件
workbook.xlsx.writeBuffer().then(buffer => {
  const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
  saveAs(blob, '銷售報(bào)表.xlsx');
});

/*
writeBuffer 選項(xiàng)：
- 返回Promise<Buffer>
- 支持配置壓縮等參數(shù)
*/

// 獲取工作表
const firstSheet = workbook.getWorksheet(1); // 通過索引
const targetSheet = workbook.getWorksheet('Sheet1'); // 通過名稱

// 遍歷工作表
workbook.eachSheet((worksheet, sheetId) => {
  console.log(`工作表 ${sheetId}: ${worksheet.name}`);
});

// 刪除工作表
workbook.removeWorksheet('Sheet2');

// 工作表屬性設(shè)置
worksheet.properties = {
  tabColor: { argb: 'FFFF0000' }, // 標(biāo)簽頁顏色
  defaultRowHeight: 20, // 默認(rèn)行高
  defaultColWidth: 10, // 默認(rèn)列寬
  pageSetup: { // 頁面設(shè)置
    orientation: 'landscape', // 橫向
    margins: { left: 0.7, right: 0.7, top: 0.75, bottom: 0.75 }
  }
};

// 批量添加數(shù)據(jù)
const data = [
  { id: 'ORD-001', amount: 1000 },
  { id: 'ORD-002', amount: 2000 }
];
worksheet.addRows(data);

// 獲取行數(shù)據(jù)
const row = worksheet.getRow(5); // 獲取第5行
console.log(row.getCell(1).value); // 獲取第1列單元格值

// 遍歷數(shù)據(jù)
worksheet.eachRow((row, rowNumber) => {
  console.log(`Row ${rowNumber}:`, row.values);
});

// 合并單元格
worksheet.mergeCells('A1:C1'); // 合并A1到C1
worksheet.mergeCells(2, 1, 2, 3); // 行2列1到行2列3

// 數(shù)據(jù)驗(yàn)證
worksheet.getCell('B2').dataValidation = {
  type: 'list',
  formulae: ['"選項(xiàng)1,選項(xiàng)2,選項(xiàng)3"'],
  allowBlank: true
};

// 創(chuàng)建樣式
const headerStyle = {
  font: {
    name: '微軟雅黑',
    size: 12,
    bold: true,
    color: { argb: 'FFFFFFFF' } // 白色
  },
  fill: {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF4472C4' } // 藍(lán)色背景
  },
  alignment: {
    vertical: 'middle',
    horizontal: 'center'
  },
  border: {
    top: { style: 'thin', color: { argb: 'FF000000' } },
    left: { style: 'thin', color: { argb: 'FF000000' } },
    bottom: { style: 'thin', color: { argb: 'FF000000' } },
    right: { style: 'thin', color: { argb: 'FF000000' } }
  }
};

// 應(yīng)用樣式
worksheet.getRow(1).eachCell(cell => {
  cell.style = headerStyle;
});

// 條件格式
worksheet.eachRow((row, rowNumber) => {
  if(rowNumber > 1) { // 跳過表頭
    const amountCell = row.getCell('B');
    if(amountCell.value > 1500) {
      amountCell.font = { color: { argb: 'FFFF0000' }, bold: true };
    }
  }
});

// 基本公式
worksheet.getCell('D2').value = { 
  formula: 'B2*C2', 
  result: 0 // 初始值
};

// 數(shù)組公式
worksheet.getCell('E2').value = { 
  formula: 'SUM(B2:D2)', 
  result: 0 
};

// 公式計(jì)算（需在Excel中重新計(jì)算）
workbook.calcProperties.fullCalcOnLoad = true;

// 獲取公式值
console.log(worksheet.getCell('D2').result); // 計(jì)算結(jié)果

async function addImageToSheet() {
  // 1. 讀取圖片文件
  const imageFile = await fetch('logo.png');
  const imageBuffer = await imageFile.arrayBuffer();
  
  // 2. 添加圖片到工作表
  const imageId = workbook.addImage({
    buffer: imageBuffer,
    extension: 'png'
  });
  
  // 3. 定位圖片位置
  worksheet.addImage(imageId, {
    tl: { col: 1, row: 1 }, // 左上角位置(列1行1)
    br: { col: 3, row: 5 }, // 右下角位置(列3行5)
    editAs: 'oneCell' // 定位方式
  });
}

// 注意：ExcelJS圖表功能需要Pro版本或配合其他庫實(shí)現(xiàn)
// 以下是概念性示例：

// 1. 準(zhǔn)備圖表數(shù)據(jù)
worksheet.addRows([
  ['產(chǎn)品', '銷量'],
  ['手機(jī)', 1200],
  ['電腦', 800],
  ['平板', 600]
]);

// 2. 創(chuàng)建圖表（偽代碼）
const chart = {
  type: 'bar', // 柱狀圖
  data: {
    categories: 'A2:A4', // X軸數(shù)據(jù)
    values: 'B2:B4'      // Y軸數(shù)據(jù)
  },
  title: '產(chǎn)品銷量統(tǒng)計(jì)'
};

// 3. 添加圖表到工作表
worksheet.addChart(chart);

// Node.js 環(huán)境流式寫入
const fs = require('fs');
const { Workbook } = require('exceljs');

async function streamWrite() {
  // 1. 創(chuàng)建可寫流
  const stream = fs.createWriteStream('large-data.xlsx');
  
  // 2. 初始化流式工作簿
  const workbook = new Workbook.stream.xlsx.WorkbookWriter({
    stream: stream,
    useStyles: false, // 禁用樣式提升性能
    useSharedStrings: false
  });
  
  // 3. 添加工作表
  const worksheet = workbook.addWorksheet('大數(shù)據(jù)');
  
  // 4. 模擬大數(shù)據(jù)源
  for(let i = 1; i <= 100000; i++) {
    worksheet.addRow({
      id: `ID-${i}`,
      value: Math.random() * 1000,
      date: new Date()
    }).commit(); // 必須調(diào)用commit
    
    // 每1000行輸出進(jìn)度
    if(i % 1000 === 0) {
      console.log(`已處理 ${i} 行`);
      await new Promise(resolve => setImmediate(resolve));
    }
  }
  
  // 5. 完成寫入
  worksheet.commit();
  await workbook.commit();
  console.log('導(dǎo)出完成');
}

async function chunkedExport(data, fileName = 'export.xlsx') {
  // 1. 初始化工作簿
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('分塊數(shù)據(jù)');
  
  // 2. 添加表頭
  if(data.length > 0) {
    worksheet.columns = Object.keys(data[0]).map(key => ({
      header: key,
      key,
      width: 15
    }));
  }
  
  // 3. 分塊處理
  const CHUNK_SIZE = 5000;
  let processed = 0;
  
  while(processed < data.length) {
    const chunk = data.slice(processed, processed + CHUNK_SIZE);
    worksheet.addRows(chunk);
    processed += CHUNK_SIZE;
    
    // 更新UI
    updateProgress(processed / data.length * 100);
    
    // 釋放主線程
    await new Promise(resolve => setTimeout(resolve, 0));
  }
  
  // 4. 導(dǎo)出文件
  const buffer = await workbook.xlsx.writeBuffer();
  saveAs(new Blob([buffer]), fileName);
}

<template>
  <div class="excel-export">
    <button 
      @click="handleExport"
      :disabled="isExporting"
      :class="{ 'exporting': isExporting }"
    >
      <span v-if="!isExporting">導(dǎo)出Excel</span>
      <span v-else>
        <span class="spinner"></span>
        導(dǎo)出中 ({{ progress }}%)
      </span>
    </button>
    
    <div v-if="error" class="error-message">
      {{ error }}
      <button v-if="showRetry" @click="handleExport">重試</button>
    </div>
  </div>
</template>

<script>
import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';

export default {
  props: {
    data: {
      type: Array,
      required: true,
      validator: value => Array.isArray(value) && 
        (value.length === 0 || typeof value[0] === 'object')
    },
    columns: {
      type: Array,
      default: null
    },
    fileName: {
      type: String,
      default: `export_${new Date().toISOString().slice(0, 10)}.xlsx`
    },
    chunkSize: {
      type: Number,
      default: 5000
    },
    styles: {
      type: Object,
      default: () => ({
        header: {
          font: { bold: true, color: { argb: 'FFFFFFFF' } },
          fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF4472C4' } }
        },
        zebra: {
          even: { fill: { fgColor: { argb: 'FFF2F2F2' } } },
          odd: { fill: { fgColor: { argb: 'FFFFFFFF' } } }
        }
      })
    }
  },
  
  data() {
    return {
      isExporting: false,
      progress: 0,
      error: null,
      showRetry: false
    };
  },
  
  methods: {
    async handleExport() {
      this.isExporting = true;
      this.error = null;
      this.showRetry = false;
      
      try {
        // 1. 創(chuàng)建工作簿
        const workbook = new ExcelJS.Workbook();
        workbook.creator = this.$store.state.user.name || '系統(tǒng)';
        workbook.created = new Date();
        
        // 2. 添加工作表
        const worksheet = workbook.addWorksheet('導(dǎo)出數(shù)據(jù)');
        
        // 3. 設(shè)置列
        if(this.columns) {
          worksheet.columns = this.columns;
        } else if(this.data.length > 0) {
          worksheet.columns = Object.keys(this.data[0]).map(key => ({
            header: key,
            key,
            width: 15
          }));
        }
        
        // 4. 添加表頭樣式
        if(this.styles.header) {
          const headerRow = worksheet.getRow(1);
          headerRow.eachCell(cell => {
            cell.style = this.styles.header;
          });
        }
        
        // 5. 分塊添加數(shù)據(jù)
        for(let i = 0; i < this.data.length; i += this.chunkSize) {
          const chunk = this.data.slice(i, i + this.chunkSize);
          const addedRows = worksheet.addRows(chunk);
          
          // 應(yīng)用斑馬紋樣式
          if(this.styles.zebra) {
            addedRows.forEach((row, rowIndex) => {
              const isEven = (i + rowIndex) % 2 === 0;
              row.eachCell(cell => {
                cell.style = { 
                  ...cell.style, 
                  ...(isEven ? this.styles.zebra.even : this.styles.zebra.odd)
                };
              });
            });
          }
          
          // 更新進(jìn)度
          this.progress = Math.min(100, (i / this.data.length * 100));
          await new Promise(resolve => setTimeout(resolve, 0));
        }
        
        // 6. 自動(dòng)調(diào)整列寬
        worksheet.columns.forEach(column => {
          if(!column.width) {
            column.width = column.header.length + 5;
          }
        });
        
        // 7. 導(dǎo)出文件
        const buffer = await workbook.xlsx.writeBuffer();
        saveAs(new Blob([buffer]), this.fileName);
        
        // 8. 觸發(fā)完成事件
        this.$emit('export-complete', {
          fileName: this.fileName,
          rowCount: this.data.length
        });
        
      } catch (err) {
        console.error('導(dǎo)出失敗:', err);
        this.error = this.getErrorMessage(err);
        this.showRetry = true;
        this.$emit('export-error', err);
        
      } finally {
        this.isExporting = false;
      }
    },
    
    getErrorMessage(err) {
      if(err.message.includes('memory')) {
        return '數(shù)據(jù)量過大導(dǎo)致內(nèi)存不足，建議分批導(dǎo)出或聯(lián)系管理員';
      }
      return `導(dǎo)出失敗: ${err.message}`;
    }
  }
};
</script>

<style scoped>
.excel-export button {
  padding: 8px 16px;
  background: #4CAF50;
  color: white;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}

.excel-export button.exporting {
  background: #FFC107;
}

.excel-export button:disabled {
  background: #cccccc;
  cursor: not-allowed;
}

.error-message {
  color: #f44336;
  margin-top: 8px;
}

.spinner {
  display: inline-block;
  width: 12px;
  height: 12px;
  border: 2px solid rgba(255,255,255,0.3);
  border-radius: 50%;
  border-top-color: white;
  animation: spin 1s ease-in-out infinite;
  margin-right: 8px;
}

@keyframes spin {
  to { transform: rotate(360deg); }
}
</style>

function createMultiLangExport(data, lang = 'zh-CN') {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('數(shù)據(jù)');
  
  // 根據(jù)語言選擇表頭
  const headers = {
    'zh-CN': { id: '訂單號(hào)', amount: '金額', date: '日期' },
    'en-US': { id: 'Order ID', amount: 'Amount', date: 'Date' }
  };
  
  worksheet.columns = [
    { header: headers[lang].id, key: 'id', width: 20 },
    { header: headers[lang].amount, key: 'amount', width: 15 },
    { header: headers[lang].date, key: 'date', width: 15 }
  ];
  
  worksheet.addRows(data);
  return workbook;
}

// 使用示例
const workbook = createMultiLangExport(data, 'en-US');
workbook.xlsx.writeBuffer().then(/* ... */);

async function generateFromTemplate(templatePath, data) {
  // 1. 讀取模板
  const templateWorkbook = new ExcelJS.Workbook();
  await templateWorkbook.xlsx.readFile(templatePath);
  
  // 2. 獲取模板工作表
  const templateSheet = templateWorkbook.getWorksheet('Template');
  
  // 3. 創(chuàng)建新工作簿
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Report');
  
  // 4. 復(fù)制模板樣式和結(jié)構(gòu)
  worksheet.model = JSON.parse(JSON.stringify(templateSheet.model));
  
  // 5. 填充數(shù)據(jù)
  data.forEach((item, index) => {
    worksheet.getCell(`A${index + 2}`).value = item.name;
    worksheet.getCell(`B${index + 2}`).value = item.value;
  });
  
  // 6. 導(dǎo)出
  return workbook.xlsx.writeBuffer();
}

worksheet.getCell('A1').value = { 
  text: '00123456', // 文本內(nèi)容
  type: ExcelJS.ValueType.String // 明確指定類型
};

// 或者批量設(shè)置
worksheet.getColumn('A').eachCell(cell => {
  cell.numFmt = '@'; // @表示文本格式
});

worksheet.getCell('A1').value = {
  text: '公司官網(wǎng)',
  hyperlink: 'https://example.com',
  tooltip: '點(diǎn)擊訪問官網(wǎng)'
};

// 或者使用純文本方式
worksheet.getCell('A2').value = '=HYPERLINK("https://example.com", "官網(wǎng)")';

worksheet.views = [
  {
    state: 'frozen',
    xSplit: 1, // 凍結(jié)第1列右側(cè)
    ySplit: 1, // 凍結(jié)第1行下方
    topLeftCell: 'B2', // 活動(dòng)單元格
    activeCell: 'B2'
  }
];