首页 / 脚本样式 / JavaScript / JavaScript三方包将Table导出为Excel

JavaScript三方包将Table导出为Excel

https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js

创建一个简单的 Excel 文件

// 创建一个工作簿
const workbook = XLSX.utils.book_new();
// 创建一个工作表
const data = [
  ["Name", "Age", "City"],
  ["Alice", 30, "New York"],
  ["Bob", 25, "Los Angeles"],
  ["Charlie", 35, "Chicago"]
];
const worksheet = XLSX.utils.aoa_to_sheet(data);
// 将工作表添加到工作簿
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
// 导出 Excel 文件
XLSX.writeFile(workbook, "example.xlsx");

读取 Excel 文件

// 假设我们有一个 input 元素用于选择文件
const input = document.getElementById('file-input');
input.addEventListener('change', function(e) {
  const file = e.target.files[0];
  const reader = new FileReader();
  reader.onload = function(e) {
    const data = new Uint8Array(e.target.result);
    const workbook = XLSX.read(data, { type: 'array' });
    // 获取第一个工作表
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    // 将工作表转换为 JSON 数据
    const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
    console.log(jsonData);
  };
  reader.readAsArrayBuffer(file);
});

处理复杂数据结构

const data = [
  { Name: "Alice", Age: 30, City: "New York" },
  { Name: "Bob", Age: 25, City: "Los Angeles" },
  { Name: "Charlie", Age: 35, City: "Chicago" }
];
const worksheet = XLSX.utils.json_to_sheet(data);

将 HTML 表格转换为工作表

	
	

		
		

			
			

				Name
			

			

				Age
			

			

				City
			

		

		

			
			

				Alice
			

			

				30
			

			

				New York
			

		

		

			
			

				Bob
			

			

				25
			

			

				Los Angeles
			

		

		

			
			

				Charlie
			

			

				35
			

			

				Chicago
			

		

	


const table = document.getElementById('my-table');
const worksheet = XLSX.utils.table_to_sheet(table);

设置单元格样式

const worksheet = XLSX.utils.aoa_to_sheet([
  ["Name", "Age", "City"],
  ["Alice", 30, "New York"],
  ["Bob", 25, "Los Angeles"],
  ["Charlie", 35, "Chicago"]
]);
// 设置第一行的样式
worksheet['A1'].s = {
  font: { name: 'Arial', sz: 14, bold: true, color: { rgb: "FF0000" } },
  fill: { fgColor: { rgb: "FFFF00" } },
  border: { top: { style: 'thin', color: { rgb: "000000" } }, bottom: { style: 'medium', color: { rgb: "FF0000" } } },
  alignment: { horizontal: 'center', vertical: 'center' }
};
worksheet['B1'].s = {
  font: { name: 'Calibri', sz: 12, italic: true, color: { rgb: "00FF00" } },
  fill: { fgColor: { rgb: "00FFFF" } },
  border: { left: { style: 'thick', color: { rgb: "00FF00" } }, right: { style: 'dashed', color: { rgb: "0000FF" } } },
  alignment: { horizontal: 'left', vertical: 'top', wrapText: true }
};
worksheet['C1'].s = {
  font: { name: 'Times New Roman', sz: 16, underline: true, color: { rgb: "0000FF" } },
  fill: { fgColor: { rgb: "FF00FF" } },
  border: { top: { style: 'dotted', color: { rgb: "FF00FF" } }, bottom: { style: 'double', color: { rgb: "00FFFF" } } },
  alignment: { horizontal: 'right', vertical: 'bottom' }
};
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
XLSX.writeFile(workbook, "combined-styles.xlsx");

合并单元格

const worksheet = XLSX.utils.aoa_to_sheet([
  ["Name", "Age", "City"],
  ["Alice", 30, "New York"],
  ["Bob", 25, "Los Angeles"],
  ["Charlie", 35, "Chicago"]
]);
// 合并 A1 和 B1 单元格
worksheet['!merges'] = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 1 } }];
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
XLSX.writeFile(workbook, "merged-cells.xlsx");

处理大数据量

const data = [];
for (let i = 0; i < 100000; i++) { data.push([`Name${i}`, i, `City${i}`]); } const worksheet = XLSX.utils.aoa_to_sheet(data); const workbook = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1"); // 使用 writeFile 方法导出文件 XLSX.writeFile(workbook, "large-data-example.xlsx"); 

处理多工作表

const workbook = XLSX.utils.book_new();
// 创建第一个工作表
const data1 = [
  ["Name", "Age", "City"],
  ["Alice", 30, "New York"],
  ["Bob", 25, "Los Angeles"],
  ["Charlie", 35, "Chicago"]
];
const worksheet1 = XLSX.utils.aoa_to_sheet(data1);
XLSX.utils.book_append_sheet(workbook, worksheet1, "Sheet1");
// 创建第二个工作表
const data2 = [
  ["Product", "Price", "Quantity"],
  ["Apple", 1.2, 10],
  ["Banana", 0.8, 15],
  ["Orange", 1.5, 20]
];
const worksheet2 = XLSX.utils.aoa_to_sheet(data2);
XLSX.utils.book_append_sheet(workbook, worksheet2, "Sheet2");
// 导出 Excel 文件
XLSX.writeFile(workbook, "multiple-sheets.xlsx");

处理公式

const worksheet = XLSX.utils.aoa_to_sheet([
  ["Product", "Price", "Quantity", "Total"],
  ["Apple", 1.2, 10],
  ["Banana", 0.8, 15],
  ["Orange", 1.5, 20]
]);
// 设置 Total 列的公式
worksheet['D2'].f = 'B2*C2';
worksheet['D3'].f = 'B3*C3';
worksheet['D4'].f = 'B4*C4';
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
XLSX.writeFile(workbook, "formulas.xlsx");

导出表格数据

Export Table to Excel

	
	

		
		

			
			

				Name
			

			

				Age
			

			

				City
			

		

		

			
			

				Alice
			

			

				30
			

			

				New York
			

		

		

			
			

				Bob
			

			

				25
			

			

				Los Angeles
			

		

		

			
			

				Charlie
			

			

				35
			

			

				Chicago
			

		

	


function exportTableToExcel() {
  const table = document.getElementById('my-table');
  const worksheet = XLSX.utils.table_to_sheet(table);
  const workbook = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
  XLSX.writeFile(workbook, "table-export.xlsx");
}

导出 JSON 数据

const data = [
      { Name: "Alice", Age: 30, City: "New York" },
      { Name: "Bob", Age: 25, City: "Los Angeles" },
      { Name: "Charlie", Age: 35, City: "Chicago" }
    ];

    function exportJsonToExcel() {
      const worksheet = XLSX.utils.json_to_sheet(data);
      const workbook = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
      XLSX.writeFile(workbook, "json-export.xlsx");
    }