@Override
public Map<String, Object> uploadData(InputStream is, boolean isExcel2003, String userId, String importType) {
Map<String, Object> result = new HashMap<>();
List<ChargingStationOrigUpload> dataList = new ArrayList<>();
// 定义允许的列名（去除了空字符串）
final Set<String> ALLOWED_COLUMNS = new HashSet<>(Arrays.asList(
"采集批次", "充电桩APP", "充电桩名称", "地址", "充电桩类型",
"可用电池数量", "原始省份", "原始城市", "原始区县", "快充数量", "慢充数量",
"其它桩数量", "营业性质", "营业时间", "充电价格", "最低充电价格（元/度）",
"最高充电价格（元/度）", "最低服务费（元/度）", "最高服务费（元/度）",
"快充功率（KW）", "慢充功率（KW）", "充电桩位置", "停车费用",
"电池类型（度）", "其它描述", "经度", "纬度", "采集时间",
"采集原始ID", "充电桩原始ID"
));
Workbook wb = null;
try {
wb = isExcel2003 ? new HSSFWorkbook(is) : new XSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
// 获取列名映射（列名 -> 列索引）
Map<String, Integer> columnIndexMap = validateHeader(sheet.getRow(0), ALLOWED_COLUMNS);
// 读取数据行
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
// 如果当前行为空，则终止读取
if (isNullOrEmptyRow(row)) {
log.info("检测到空行，停止读取。当前行号: {}", rowIndex + 1);
break;
}
ChargingStationOrigUpload entity = new ChargingStationOrigUpload();
try {
mapRowToEntity(row, entity, columnIndexMap);
dataList.add(entity); // 只有非空有效行才会加入列表
} catch (DataValidationException e) {
log.error("第{}行数据验证失败: {}", rowIndex + 1, e.getMessage());
throw new ReadDataExection("第" + (rowIndex + 1) + "行数据错误: " + e.getMessage());
}
}
// 统一设置importType
if (!dataList.isEmpty()) {
for (ChargingStationOrigUpload item : dataList) {
item.setImportType(importType);
}
processImportData(dataList, userId, result);
} else {
result.put("success", false);
result.put("rsMsg", "没有有效数据可导入");
}
} catch (ReadDataExection e) {
result.put("success", false);
result.put("rsMsg", "数据验证失败: " + e.getMessage());
log.error("数据验证异常", e);
} catch (Exception e) {
result.put("success", false);
result.put("rsMsg", "系统异常: " + (e.getMessage() != null ? e.getMessage() : "未知错误"));
log.error("文件解析异常", e);
} finally {
closeQuietly(is); // 关闭输入流
}
return result;
}
// 关闭输入流的方法
private void closeQuietly(InputStream is) {
if (is != null) {
try {
is.close();
} catch (IOException e) {
log.warn("输入流关闭失败", e);
}
}
}
/**
* 验证表头并创建列名-索引映射
*/
private Map<String, Integer> validateHeader(Row headerRow, Set<String> allowedColumns) throws ReadDataExection {
if (headerRow == null) throw new ReadDataExection("文件缺少表头");
Map<String, Integer> columnIndexMap = new HashMap<>();
Set<String> foundColumns = new HashSet<>();
// 检查必须存在的列
String[] requiredColumns = {"采集批次", "充电桩APP", "充电桩名称"};
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell cell = headerRow.getCell(i);
if (cell == null) continue;
String colName = getCellValue(cell).trim();
// 跳过空白列名
if (StringUtils.isBlank(colName)) continue;
// 检查列名是否合法
if (!allowedColumns.contains(colName)) {
throw new ReadDataExection("Excel包含非法列名: " + colName);
}
// 检查列名重复
if (foundColumns.contains(colName)) {
throw new ReadDataExection("Excel包含重复列名: " + colName);
}
columnIndexMap.put(colName, i);
foundColumns.add(colName);
}
// 确保所有必需的列都存在于 columnIndexMap 中
for (String requiredCol : requiredColumns) {
if (!columnIndexMap.containsKey(requiredCol)) {
throw new ReadDataExection("缺少必要列: " + requiredCol);
}
}
return columnIndexMap;
}
/**
* 将行数据映射到实体对象
*/
private void mapRowToEntity(Row row, ChargingStationOrigUpload entity, Map<String, Integer> columnIndexMap)
throws DataValidationException {
try {
// 使用传统方式获取单元格值
String batchStr = getCellValue(getCellByColumnName(row, columnIndexMap, "采集批次"));
if (StringUtils.isNotBlank(batchStr)) {
try {
double value = Double.parseDouble(batchStr.trim());
entity.setBatch((int) value);
} catch (NumberFormatException e) {
System.err.println("字段映射失败: For input string: \"" + batchStr + "\"");
}
}
entity.setSource(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩APP")).trim());
entity.setName(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩名称")).trim());
entity.setAddress(getCellValue(getCellByColumnName(row, columnIndexMap, "地址")).trim());
entity.setStationType(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩类型")));
entity.setChargingPositionType(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩位置")));
entity.setBatteryCounts(getCellValue(getCellByColumnName(row, columnIndexMap, "可用电池数量")));
entity.setCollectionTime(getCellValue(getCellByColumnName(row, columnIndexMap, "采集时间")));
entity.setLng(getCellValue(getCellByColumnName(row, columnIndexMap, "经度")));
entity.setLat(getCellValue(getCellByColumnName(row, columnIndexMap, "纬度")));
entity.setProvince(getCellValue(getCellByColumnName(row, columnIndexMap, "原始省份")));
entity.setCity(getCellValue(getCellByColumnName(row, columnIndexMap, "原始城市")));
entity.setArea(getCellValue(getCellByColumnName(row, columnIndexMap, "原始区县")));
entity.setFastCounts(removeDecimalIfPresent(getCellValue(getCellByColumnName(row, columnIndexMap, "快充数量"))));
entity.setSlowCounts(removeDecimalIfPresent(getCellValue(getCellByColumnName(row, columnIndexMap, "慢充数量"))));
entity.setOtherStation(removeDecimalIfPresent(getCellValue(getCellByColumnName(row, columnIndexMap, "其它桩数量"))));
entity.setTags(getCellValue(getCellByColumnName(row, columnIndexMap, "营业性质")));
entity.setBusinessHours(getCellValue(getCellByColumnName(row, columnIndexMap, "营业时间")));
entity.setPrice(getCellValue(getCellByColumnName(row, columnIndexMap, "充电价格")));
entity.setLowPrice(getCellValue(getCellByColumnName(row, columnIndexMap, "最低充电价格（元/度）")));
entity.setHightPrice(getCellValue(getCellByColumnName(row, columnIndexMap, "最高充电价格（元/度）")));
entity.setLowServicePrice(getCellValue(getCellByColumnName(row, columnIndexMap, "最低服务费（元/度）")));
entity.setHightServicePrice(getCellValue(getCellByColumnName(row, columnIndexMap, "最高服务费（元/度）")));
entity.setFastPower(removeDecimalIfPresent(getCellValue(getCellByColumnName(row, columnIndexMap, "快充功率（KW）"))));
entity.setSlowPower(removeDecimalIfPresent(getCellValue(getCellByColumnName(row, columnIndexMap, "慢充功率（KW）"))));
entity.setChargingPositionType(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩位置")));
entity.setParkingInfo(getCellValue(getCellByColumnName(row, columnIndexMap, "停车费用")));
entity.setBatteryType(getCellValue(getCellByColumnName(row, columnIndexMap, "电池类型（度）")));
entity.setOtherDesc(getCellValue(getCellByColumnName(row, columnIndexMap, "其它描述")));
entity.setStationId(getCellValue(getCellByColumnName(row, columnIndexMap, "充电桩原始ID")));
entity.setCollectionId(getCellValue(getCellByColumnName(row, columnIndexMap, "采集原始ID")));
} catch (Exception e) {
throw new DataValidationException("字段映射失败: " + e.getMessage());
}
}
/**
* 根据列名获取单元格
*/
private Cell getCellByColumnName(Row row, Map<String, Integer> columnIndexMap, String colName) {
Integer index = columnIndexMap.get(colName);
if (index != null && index < row.getLastCellNum()) {
return row.getCell(index);
}
return null;
}
/**
* 处理导入的数据
*/
private void processImportData(List<ChargingStationOrigUpload> dataList, String userId, Map<String, Object> result) {
if (dataList == null || dataList.isEmpty()) {
result.put("success", false);
result.put("rsMsg", "没有可处理的数据");
return;
}
try {
// 保存数据到中间表
boolean saveSuccess = charingStationDataDao.saveMidTable(dataList);
if (!saveSuccess) {
result.put("success", false);
result.put("rsMsg", "保存到中间表失败，存在数据格式错误");
return;
}
// 执行数据上传逻辑（例如调用存储过程）
Map<String, Object> vMap = charingStationDataDao.uploadData(userId);
String rsMsg = (String) vMap.get("rsMsg");
if ("success".equalsIgnoreCase(rsMsg)) {
result.put("success", true);
result.put("rsMsg", "导入成功!");
} else {
result.put("success", false);
result.put("rsMsg", rsMsg != null ? rsMsg : "未知错误");
}
} catch (Exception e) {
String errorMsg = "数据库操作失败: " + e.getMessage();
result.put("success", false);
result.put("rsMsg", errorMsg);
log.error("数据库操作异常", e);
}
}
/**
* 判断某一行是否为空行
*/
private boolean isNullOrEmptyRow(Row row) {
if (row == null) return true;
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
if (cell != null && StringUtils.isNotBlank(getCellValue(cell))) {
return false; // 存在非空单元格，不是空行
}
}
return true;
}
/**
* 获取单元格值
*/
private String getCellValue(Cell cell) {
if (cell == null) return "";
try {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue().trim();
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
}
return BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
case Cell.CELL_TYPE_BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
default:
return "";
}
} catch (Exception e) {
return "CELL_FORMAT_ERROR";
}
}
/**
* 去除字符串中 ".0" 小数部分（如 "123.0" -> "123"）
*/
private String removeDecimalIfPresent(String value) {
if (value != null && value.endsWith(".0")) {
return value.substring(0, value.length() - 2);
}
return value;
}
/**
* 自定义数据验证异常类
*/
class DataValidationException extends Exception {
public DataValidationException(String message) {
super(message);
}
}
}
.xsxl文件太大内存溢出，我应该怎么解决这个问题
最新发布
本文介绍了一种使用jQuery插件的方法来获取Datagrid当前页面的所有行数据。通过简单的代码示例展示了如何定位到特定行并获取其数据。
扫一扫
4441
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
