React-ts页面处理Excel， 环境React+Ant Design
1.引用xlsx和file-saver
import * as XLSX from 'xlsx';
import { saveAs } from 'file-saver';2.具体导入逻辑
export const readExcelFile = (file: File): Promise<any[]> => {
return new Promise((resolve, reject) => {
const reader = new FileReader();
// 根据文件扩展名选择处理方式
const isCSV = file.name.toLowerCase().endsWith('.csv') ||
file.name.toLowerCase().endsWith('.txt');
if (isCSV) {
// 处理 CSV/TXT 文件
reader.onload = (e) => {
try {
const text = e.target?.result as string;
// 检测分隔符
const delimiter = detectDelimiter(text);
console.log(`检测到的分隔符: "${delimiter}" (ASCII: ${delimiter.charCodeAt(0)})`);
// 解析 CSV
const rawData = parseCSV(text, delimiter);
if (rawData.length === 0) {
resolve([]);
return;
}
// 提取标题行
const headers = rawData[0].map((cell: any, index: number) => {
if (cell === null || cell === undefined || cell === '') {
return `Column_${index + 1}`;
}
return cell.toString().trim();
});
// 处理可能的重复标题
const uniqueHeaders = makeUniqueHeaders(headers);
// 提取数据行（跳过标题行）
let dataRows = rawData.slice(1);
// 过滤空行和无效行
dataRows = filterValidRows(dataRows);
// 转换为 JSON 对象数组
const jsonData = dataRows.map(row => {
const obj: Record<string, any> = {};
uniqueHeaders.forEach((header, index) => {
// 只处理存在的列
if (index < row.length) {
const value = row[index];
// 处理空值
obj[header] = (value === null || value === undefined || value === '')
? null
: value;
} else {
obj[header] = null;
}
});
return obj;
});
resolve(jsonData);
} catch (error) {
reject(error);
}
};
// 以文本形式读取 CSV
reader.readAsText(file, 'UTF-8');
} else {
// 处理 Excel 文件（xlsx, xls）
reader.onload = (e) => {
try {
const arrayBuffer = e.target?.result as ArrayBuffer;
const workbook = XLSX.read(arrayBuffer, {
type: 'array',
cellDates: true,
cellStyles: true
});
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
// 获取原始行数据（二维数组格式）
const rawData = XLSX.utils.sheet_to_json(worksheet, {
header: 1,
defval: null,
raw: false
}) as any[][];
// 提取标题行
const headers = rawData[0].map((cell: any) =>
cell?.toString().trim() || `Column_${Math.random().toString(36).slice(2, 7)}`
);
// 处理可能的重复标题
const uniqueHeaders = makeUniqueHeaders(headers);
// 提取数据行（跳过标题行）
let dataRows = rawData.slice(1);
// 过滤空行和无效行
dataRows = filterValidRows(dataRows);
// 转换为 JSON 对象数组
const jsonData = dataRows.map(row => {
const obj: Record<string, any> = {};
uniqueHeaders.forEach((header, index) => {
// 只处理存在的列
if (index < row.length) {
const value = row[index];
// 处理空值
obj[header] = (value === null || value === undefined || value === '')
? null
: value;
} else {
obj[header] = null;
}
});
return obj;
});
resolve(jsonData);
} catch (error) {
reject(error);
}
};
reader.onerror = reject;
reader.readAsArrayBuffer(file);
}
});
};
// 检测文件分隔符的函数
function detectDelimiter(text: string): string {
const delimiters = [',', '\t', ';', '|', ' '];
const lines = text.split('\n').slice(0, 10); // 检查前10行
let bestDelimiter = ',';
let maxScore = 0;
for (const delimiter of delimiters) {
let score = 0;
for (const line of lines) {
if (line.trim() === '') continue;
const parts = line.split(delimiter);
// 忽略空行和只有空格的列
const nonEmptyParts = parts.filter(part => part.trim() !== '');
if (nonEmptyParts.length > 1) {
// 分数基于列数和非空列的比例
score += nonEmptyParts.length;
}
}
if (score > maxScore) {
maxScore = score;
bestDelimiter = delimiter;
}
}
return bestDelimiter;
}
// 解析 CSV 字符串
function parseCSV(text: string, delimiter: string): any[][] {
const lines = text.split('\n');
const result: any[][] = [];
for (let i = 0; i < lines.length; i++) {
const line = lines[i].trim();
if (line === '') continue;
// 处理引号内的分隔符
const row: any[] = [];
let inQuotes = false;
let currentCell = '';
for (let j = 0; j < line.length; j++) {
const char = line[j];
const nextChar = line[j + 1];
if (char === '"' && !inQuotes) {
inQuotes = true;
} else if (char === '"' && inQuotes && nextChar === '"') {
// 双引号转义
currentCell += '"';
j++; // 跳过下一个引号
} else if (char === '"' && inQuotes) {
inQuotes = false;
} else if (char === delimiter && !inQuotes) {
row.push(currentCell);
currentCell = '';
} else {
currentCell += char;
}
}
// 添加最后一个单元格
row.push(currentCell);
// 清理单元格：去除首尾空格
const cleanedRow = row.map(cell => {
if (cell === null || cell === undefined) return '';
const str = String(cell).trim();
// 尝试转换为数字
if (str !== '' && !isNaN(Number(str))) {
return Number(str);
}
return str;
});
result.push(cleanedRow);
}
return result;
}
// 处理重复标题
function makeUniqueHeaders(headers: string[]): string[] {
const seen = new Map<string, number>();
return headers.map(header => {
if (!header || header.trim() === '') {
return `Unnamed_Column_${Math.random().toString(36).slice(2, 7)}`;
}
let finalHeader = header.trim();
let count = seen.get(finalHeader) || 0;
while (seen.has(finalHeader) && seen.get(finalHeader)! > 0) {
count++;
finalHeader = `${header.trim()}_${count}`;
}
seen.set(header.trim(), count + 1);
seen.set(finalHeader, 1);
return finalHeader;
});
}
// 过滤空行和无效行
function filterValidRows(rows: any[][]): any[][] {
return rows.filter(row => {
// 检查行是否有效（至少有一个非空单元格）
return row.some(cell =>
cell !== null &&
cell !== undefined &&
cell !== '' &&
!(typeof cell === 'string' && cell.trim() === '')
);
});
}
// 检查单元格是否有效
function isValidCell(cell: any): boolean {
return cell !== null &&
cell !== undefined &&
cell !== '' &&
!(typeof cell === 'string' && cell.trim() === '');
}包含了csv、txt、xlsx, xls等格式的处理
3.具体导出逻辑
导出就比较简单了
export const exportToExcel = (data: any[], filename: string) => {
const worksheet = XLSX.utils.json_to_sheet(data);
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
const excelBuffer = XLSX.write(workbook, {
bookType: 'xlsx',
type: 'array'
});
const blob = new Blob([excelBuffer], {
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
saveAs(blob, `${filename}.xlsx`);
};