// 第一步：使用 fetch 获取文件流，并使用XLSX解析文件流数据
async function previewExcel() {
try {
const response = await fetch(excelUrl, {
responseType: 'arraybuffer', // 关键：指定响应类型
});
const arrayBuffer = await response.arrayBuffer();
// 解析Excel
const workbook = XLSX.read(arrayBuffer, { type: 'array',cellStyles: true });
// 遍历所有 Sheet 页
workbook.SheetNames.forEach((sheetName) => {
const worksheet = workbook.Sheets[sheetName];
const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 'A', defval: '' ,blankrows:true,cellStyles: true });
const mergeCells = getMergeCells(worksheet); // 获取合并单元格信息
const cellStyles = getCellStyles(worksheet); // 获取单元格样式信息
sheetsData[sheetName] = {
data: jsonData,
mergeCells,
cellStyles,
};
});
// 默认渲染第一个 Sheet 页
currentSheetName = workbook.SheetNames[0];
const { data, mergeCells, cellStyles } = sheetsData[currentSheetName];
renderTable(data, mergeCells, cellStyles);
// 渲染 Sheet 页切换菜单
renderSheetTabs(workbook.SheetNames);
} catch (error) {
console.error('预览失败:', error);
}
}
//获取合并单元格的数据
function getMergeCells(worksheet) {
const merges = worksheet['!merges'] || [];
return merges.map(merge => ({
row: merge.s.r,
col: merge.s.c,
rowspan: merge.e.r - merge.s.r + 1,
colspan: merge.e.c - merge.s.c + 1,
}));
}
function convertHexToCssHex(hex) {
// 检查输入是否为有效的十六进制颜色值（6 位字符）
if (/^[0-9A-Fa-f]{6}$/.test(hex)) {
return "#" + hex;
} else {
return hex;
}
}
//获取单元格的样式
function getCellStyles(worksheet) {
const styles = [];
// 防止空sheet页整个excel都不渲染
if (!worksheet) {
return styles;
}
if (!worksheet['!ref']) {
return styles;
}
const range = XLSX.utils.decode_range(worksheet['!ref']); // 获取工作表范围
for (let row = range.s.r; row <= range.e.r; row++) {
for (let col = range.s.c; col <= range.e.c; col++) {
const cellAddress = XLSX.utils.encode_cell({ r: row, c: col });
const cell = worksheet[cellAddress];
if (cell && cell.s) {
// XLSX 返回的颜色格式为“FFFFFF”，需要在前面加"#"才能正常渲染颜色
// XLSX未能解析出字体样式
const fgColor = cell.s.fgColor?.rgb // "FFFF00"
const background = convertHexToCssHex(fgColor) ?? 'white'
styles.push({
row,
col,
style: {
background,
color: cell.s.font?.color?.rgb || 'black', // 字体颜色
fontWeight: cell.s.font?.bold ? 'bold' : 'normal', // 字体加粗
},
});
}
}
}
return styles;
}
// 自定义样式渲染器（通用）
const styleRenderer = (hotInstance, TD, row, col, prop, value, cellProperties) => {
// 调用默认文本渲染器
Handsontable.renderers.TextRenderer(hotInstance, TD, row, col, prop, value, cellProperties);
// 获取当前单元格的样式配置
const { styles } = hotInstance.getSettings();
const cell = styles.find(s => s.row === row && s.col === col);
// 应用样式
if (cell) {
TD.style.background = cell.style.background || '';
TD.style.color = cell.style.color || '';
TD.style.fontWeight = cell.style.fontWeight || '';
}
};
// 注册渲染器
Handsontable.renderers.registerRenderer('styleRenderer', styleRenderer);
// 渲染表格到页面
let hot;
function renderTable(data,mergeCells, cellStyles) {
const container = document.getElementById('excel-preview');
if (!container) {
console.error('容器元素未找到！');
return;
}
container.innerHTML = ''; // 清空容器
// 分页数据
const start = (currentPage - 1) * pageSize;
const end = start + pageSize;
const pageData = data.slice(start, end);
// 生成动态 cell 配置
const cellConfig = cellStyles.map(style => ({
row: style.row,
col: style.col,
renderer: 'styleRenderer', // 使用注册的自定义渲染器
}));
hot = new Handsontable(container, {
data: pageData, // 表格数据
rowHeaders: true, // 显示行号
colHeaders: true, // 显示列号
mergeCells: mergeCells, // 设置合并单元格
styles:cellStyles,
cell: cellConfig, // 动态样式配置
contextMenu: true, // 启用右键菜单
manualRowResize: true, // 允许调整行高
manualColumnResize: true, // 允许调整列宽
stretchH: 'all', // 拉伸列宽以适应容器
height: 'auto', // 自动高度
licenseKey: 'non-commercial-and-evaluation', // 免费版 License
});
// 渲染分页导航
renderPagination(data.length);
}
function renderPagination(totalRows) {
const paginationContainer = document.getElementById('pagination');
paginationContainer.innerHTML = '';
const totalPages = Math.ceil(totalRows / pageSize);
// 上一页按钮
const prevButton = document.createElement('button');
prevButton.textContent = '上一页';
prevButton.disabled = currentPage === 1;
prevButton.addEventListener('click', () => {
currentPage--;
const { data, mergeCells, cellStyles } = sheetsData[currentSheetName];
renderTable(data, mergeCells, cellStyles);
});
paginationContainer.appendChild(prevButton);
// 页码显示
const pageInfo = document.createElement('span');
pageInfo.textContent = `第 ${currentPage} 页 / 共 ${totalPages} 页`;
paginationContainer.appendChild(pageInfo);
// 下一页按钮
const nextButton = document.createElement('button');
nextButton.textContent = '下一页';
nextButton.disabled = currentPage === totalPages;
nextButton.addEventListener('click', () => {
currentPage++;
const { data, mergeCells, cellStyles } = sheetsData[currentSheetName];
renderTable(data, mergeCells, cellStyles);
});
paginationContainer.appendChild(nextButton);
}
function renderSheetTabs(sheetNames) {
const tabsContainer = document.getElementById('sheet-tabs');
tabsContainer.innerHTML = ''; // 清空旧内容
sheetNames.forEach((sheetName) => {
const tab = document.createElement('button');
tab.textContent = sheetName;
tab.className = sheetName === currentSheetName ? 'active' : ''; // 高亮当前 Sheet 页
tab.addEventListener('click', () => {
currentSheetName = sheetName;
currentPage = 1; // 切换 Sheet 页时重置页码
const { data, mergeCells, cellStyles } = sheetsData[currentSheetName];
renderTable(data, mergeCells, cellStyles);
updateActiveTab(tab); // 更新高亮状态
});
tabsContainer.appendChild(tab);
});
}
function updateActiveTab(activeTab) {
const tabs = document.querySelectorAll('#sheet-tabs button');
tabs.forEach((tab) => tab.classList.remove('active'));
activeTab.classList.add('active');
}
document.addEventListener('DOMContentLoaded', function () {
previewExcel()
});
400 186 1886
