const workbook = new ExcelJS.Workbook();

const worksheet = workbook.addWorksheet("學(xué)生數(shù)據(jù)", {
  properties: {
    tabColor: { argb: 'FF00FF00' } // 綠色標(biāo)簽
  }
});

worksheet.pageSetup = {
  orientation: 'landscape' // 橫向
};

cell.font = {
  name: '微軟雅黑',
  size: 12
};

cell.border = {
  top: { style: 'double' }
};

const cell = worksheet.getCell('B3'); 
// 相當(dāng)于在Excel中點(diǎn)擊B3單元格

const cell = worksheet.getCell(3, 2); 
// 行號(hào)3，列號(hào)2（即B3）

const row = worksheet.getRow(3); // 獲取第3行
const cell = row.getCell(2);     // 獲取該行第2列的單元格

worksheet.addRow([10, '張三']).eachCell((cell, colNumber) => {
  if(colNumber === 2) { // 第二列
    cell.font = { name: '微軟雅黑', size: 12 };
  }
});

worksheet.addConditionalFormatting({
  ref: 'A1:A10',
  rules: [{
    type: 'cellIs',
    operator: 'greaterThan',
    formulae: [100],
    style: { fill: { type: 'solid', fgColor: { argb: 'FFFF0000' } } }
  }]
});

for(let i = 0; i < 100000; i++) {
  worksheet.addRow([i, `Name${i}`]).commit();
}

worksheet.mergeCells('A1:D1');

cell.border = {
  diagonal: { up: true }
};

worksheet.dataValidation.add('B2:B100', {
  type: 'list',
  formulae: ['"男,女"']
});

cell.value = { 
  formula: 'SUM(B2:C2)',
  result: 150 
};

saveAs(new Blob([buffer]), 'report.xlsx');

res.setHeader('Content-Type', 'application/vnd.ms-excel');

const transformedData = response.data.list.map((item, index) => ({
  序號(hào): index + 1,
  學(xué)生姓名: item.name,
  性別: item.gender === "male" ? "男" : "女", // 數(shù)據(jù)字典轉(zhuǎn)換
  // ...其他字段
  錄取時(shí)間: item.admission_time 
    ? dayjs(item.admission_time).format("YYYY-MM-DD HH:mm:ss") 
    : "--" // 處理空值
}));

const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet("學(xué)生報(bào)名信息");

// 合并單元格
worksheet.mergeCells("A1:N1"); 

// 設(shè)置標(biāo)題內(nèi)容
const titleCell = worksheet.getCell("A1");
titleCell.value = `學(xué)生報(bào)名信息（${dateRange}）`;

// 設(shè)置樣式
titleCell.font = {
  name: "宋體",      // 對(duì)應(yīng)Excel字體下拉框
  size: 16,         // 字號(hào)設(shè)置
  bold: true        // 加粗按鈕
};
titleCell.alignment = { 
  horizontal: "center" // 對(duì)應(yīng)Excel居中按鈕
};

const headers = ["序號(hào)", "學(xué)生姓名", ...];
const headerRow = worksheet.addRow(headers);

// 樣式設(shè)置
headerRow.eachCell(cell => {
  cell.border = {
    top: { style: "thin" }, // 對(duì)應(yīng)Excel邊框工具欄
    // ...其他邊框
  };
  cell.fill = {
    type: "pattern",
    pattern: "solid",      // 對(duì)應(yīng)Excel油漆桶工具
    fgColor: { argb: "FFD9D9D9" } 
  };
});

transformedData.forEach(item => {
  const row = worksheet.addRow(Object.values(item));
  
  row.eachCell((cell, colNumber) => {
    // 第六列（家庭住址）左對(duì)齊，其他居中
    cell.alignment = {
      horizontal: colNumber === 6 ? "left" : "center"
    };
    
    // 斑馬線效果
    if(row.number % 2 === 0) {
      cell.fill = { 
        type: "pattern",
        fgColor: { argb: "FFF9F9F9" } 
      };
    }
  });
});

worksheet.columns = [
  { width: 6 },   // 序號(hào)列
  { width: 10 },  // 姓名列
  // ...其他列
  { width: 30 }   // 地址列（最寬）
];

const buffer = await workbook.xlsx.writeBuffer();
const blob = new Blob([buffer], {
  type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" 
});

saveAs(blob, `學(xué)生報(bào)名信息_${dateRange}.xlsx`);

console.log(`當(dāng)前處理單元格：${cell.address}`);

// 查看最終應(yīng)用的樣式
console.log(cell.style);

worksheet.background = {
  imageId: 'watermark',
  type: 'picture'
};

worksheet.views = [{
  state: 'frozen',
  ySplit: 2 // 凍結(jié)前兩行（標(biāo)題+表頭）
}];

worksheet.dataValidation.add('C3:C100', {
  type: 'list',
  formulae: ['"男,女"']
});

worksheet.background = {
  imageId: 'watermark',
  type: 'picture'
};

worksheet.views = [{
  state: 'frozen',
  ySplit: 2 // 凍結(jié)前兩行（標(biāo)題+表頭）
}];

worksheet.dataValidation.add('C3:C100', {
  type: 'list',
  formulae: ['"男,女"']
});