package jnpf.tmsUtil;
|
|
import jnpf.exception.DataException;
|
import jnpf.util.StringUtil;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.w3c.dom.Document;
|
import org.w3c.dom.Element;
|
import org.w3c.dom.Node;
|
import org.w3c.dom.NodeList;
|
|
import javax.xml.parsers.DocumentBuilderFactory;
|
import java.io.BufferedReader;
|
import java.io.ByteArrayInputStream;
|
import java.io.InputStream;
|
import java.io.InputStreamReader;
|
import java.nio.charset.Charset;
|
import java.nio.charset.StandardCharsets;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
/**
|
* TMS Excel 导入：支持 SpreadsheetML(.xls 本系统导出)、POI 二进制/xlsx、UTF-8 CSV。
|
*/
|
public final class TmsExcelImportHelper {
|
|
private static final DataFormatter FORMATTER = new DataFormatter();
|
|
private TmsExcelImportHelper() {
|
}
|
|
/**
|
* 读取首个工作表全部行（含表头），单元格统一转字符串。
|
*/
|
public static List<List<String>> readRows(byte[] bytes, String fileName) {
|
if (bytes == null || bytes.length == 0) {
|
throw new DataException("上传文件为空");
|
}
|
String name = fileName == null ? "" : fileName.toLowerCase();
|
try {
|
if (name.endsWith(".csv") || looksLikeCsv(bytes)) {
|
return readCsv(bytes);
|
}
|
if (looksLikeSpreadsheetMl(bytes)) {
|
return readSpreadsheetMl(bytes);
|
}
|
return readWithPoi(bytes);
|
} catch (DataException e) {
|
throw e;
|
} catch (Exception e) {
|
throw new DataException("无法解析导入文件，请使用模板填写后上传（xls/xlsx/csv）");
|
}
|
}
|
|
private static boolean looksLikeSpreadsheetMl(byte[] bytes) {
|
String head = new String(bytes, 0, Math.min(bytes.length, 200), StandardCharsets.UTF_8).trim();
|
return head.startsWith("<?xml") || head.contains("urn:schemas-microsoft-com:office:spreadsheet");
|
}
|
|
private static boolean looksLikeCsv(byte[] bytes) {
|
// 无扩展名时的兜底：首行含逗号且非 XML/ZIP
|
if (bytes.length >= 2 && bytes[0] == 'P' && bytes[1] == 'K') {
|
return false;
|
}
|
if (looksLikeSpreadsheetMl(bytes)) {
|
return false;
|
}
|
String head = new String(bytes, 0, Math.min(bytes.length, 120), StandardCharsets.UTF_8);
|
return head.contains(",") && !head.contains("<Workbook");
|
}
|
|
private static List<List<String>> readWithPoi(byte[] bytes) throws Exception {
|
try (InputStream in = new ByteArrayInputStream(bytes);
|
Workbook workbook = WorkbookFactory.create(in)) {
|
Sheet sheet = workbook.getSheetAt(0);
|
List<List<String>> rows = new ArrayList<>();
|
int last = sheet.getLastRowNum();
|
for (int i = 0; i <= last; i++) {
|
Row row = sheet.getRow(i);
|
if (row == null) {
|
continue;
|
}
|
int lastCell = Math.max(row.getLastCellNum(), 0);
|
List<String> cells = new ArrayList<>(lastCell);
|
boolean any = false;
|
for (int c = 0; c < lastCell; c++) {
|
String v = cellText(row.getCell(c));
|
if (StringUtil.isNotEmpty(v)) {
|
any = true;
|
}
|
cells.add(v);
|
}
|
if (any) {
|
rows.add(cells);
|
}
|
}
|
return rows;
|
}
|
}
|
|
private static String cellText(Cell cell) {
|
if (cell == null) {
|
return "";
|
}
|
if (cell.getCellType() == CellType.FORMULA) {
|
try {
|
return FORMATTER.formatCellValue(cell).trim();
|
} catch (Exception ignored) {
|
return "";
|
}
|
}
|
return FORMATTER.formatCellValue(cell).trim();
|
}
|
|
private static List<List<String>> readSpreadsheetMl(byte[] bytes) throws Exception {
|
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
|
factory.setNamespaceAware(true);
|
factory.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
|
Document doc = factory.newDocumentBuilder().parse(new ByteArrayInputStream(bytes));
|
NodeList rowNodes = doc.getElementsByTagNameNS("urn:schemas-microsoft-com:office:spreadsheet", "Row");
|
if (rowNodes.getLength() == 0) {
|
rowNodes = doc.getElementsByTagName("Row");
|
}
|
List<List<String>> rows = new ArrayList<>(rowNodes.getLength());
|
for (int i = 0; i < rowNodes.getLength(); i++) {
|
Element rowEl = (Element) rowNodes.item(i);
|
NodeList children = rowEl.getChildNodes();
|
List<String> cells = new ArrayList<>();
|
boolean any = false;
|
int col = 0;
|
for (int j = 0; j < children.getLength(); j++) {
|
Node n = children.item(j);
|
if (!(n instanceof Element)) {
|
continue;
|
}
|
Element cell = (Element) n;
|
if (!"Cell".equals(cell.getLocalName()) && !"Cell".equals(cell.getTagName())) {
|
continue;
|
}
|
String idx = cell.getAttributeNS("urn:schemas-microsoft-com:office:spreadsheet", "Index");
|
if (StringUtil.isEmpty(idx)) {
|
idx = cell.getAttribute("ss:Index");
|
}
|
if (StringUtil.isNotEmpty(idx)) {
|
int target = Integer.parseInt(idx) - 1;
|
while (col < target) {
|
cells.add("");
|
col++;
|
}
|
}
|
String text = dataText(cell);
|
if (StringUtil.isNotEmpty(text)) {
|
any = true;
|
}
|
cells.add(text);
|
col++;
|
}
|
if (any) {
|
rows.add(cells);
|
}
|
}
|
return rows;
|
}
|
|
private static String dataText(Element cell) {
|
NodeList dataNodes = cell.getElementsByTagNameNS("urn:schemas-microsoft-com:office:spreadsheet", "Data");
|
if (dataNodes.getLength() == 0) {
|
dataNodes = cell.getElementsByTagName("Data");
|
}
|
if (dataNodes.getLength() == 0) {
|
return "";
|
}
|
return dataNodes.item(0).getTextContent() == null ? "" : dataNodes.item(0).getTextContent().trim();
|
}
|
|
private static List<List<String>> readCsv(byte[] bytes) throws Exception {
|
Charset charset = detectCharset(bytes);
|
try (BufferedReader reader = new BufferedReader(
|
new InputStreamReader(new ByteArrayInputStream(bytes), charset))) {
|
List<List<String>> rows = new ArrayList<>();
|
String line;
|
while ((line = reader.readLine()) != null) {
|
if (StringUtil.isEmpty(line.trim())) {
|
continue;
|
}
|
if (rows.isEmpty() && line.startsWith("\uFEFF")) {
|
line = line.substring(1);
|
}
|
rows.add(parseCsvLine(line));
|
}
|
return rows;
|
}
|
}
|
|
private static Charset detectCharset(byte[] bytes) {
|
if (bytes.length >= 3
|
&& (bytes[0] & 0xFF) == 0xEF
|
&& (bytes[1] & 0xFF) == 0xBB
|
&& (bytes[2] & 0xFF) == 0xBF) {
|
return StandardCharsets.UTF_8;
|
}
|
// Excel 中文环境常存 GBK
|
String asUtf8 = new String(bytes, 0, Math.min(bytes.length, 200), StandardCharsets.UTF_8);
|
if (asUtf8.contains("题库") || asUtf8.contains("题型") || asUtf8.contains("题干")) {
|
return StandardCharsets.UTF_8;
|
}
|
return Charset.forName("GBK");
|
}
|
|
private static List<String> parseCsvLine(String line) {
|
List<String> cells = new ArrayList<>();
|
StringBuilder cur = new StringBuilder();
|
boolean inQuote = false;
|
for (int i = 0; i < line.length(); i++) {
|
char ch = line.charAt(i);
|
if (inQuote) {
|
if (ch == '"') {
|
if (i + 1 < line.length() && line.charAt(i + 1) == '"') {
|
cur.append('"');
|
i++;
|
} else {
|
inQuote = false;
|
}
|
} else {
|
cur.append(ch);
|
}
|
} else if (ch == '"') {
|
inQuote = true;
|
} else if (ch == ',') {
|
cells.add(cur.toString().trim());
|
cur.setLength(0);
|
} else {
|
cur.append(ch);
|
}
|
}
|
cells.add(cur.toString().trim());
|
return cells;
|
}
|
}
|
