import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.*;
import java.util.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
public class ExcelProcessor {
private String inputFile;
private String outputFile;
private String targetColumnName; // 目标列名
private Workbook workbook;
private Sheet sheet;
public ExcelProcessor(String inputFile, String outputFile, String targetColumnName) {
this.inputFile = inputFile;
this.outputFile = outputFile != null ? outputFile : inputFile;
this.targetColumnName = targetColumnName;
}
/**
* 加载Excel文件
*/
public boolean loadExcel() {
try {
FileInputStream fileInputStream = new FileInputStream(inputFile);
// 根据文件扩展名选择适当的Workbook类型
if (inputFile.toLowerCase().endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fileInputStream);
} else if (inputFile.toLowerCase().endsWith(".xls")) {
workbook = new HSSFWorkbook(fileInputStream);
} else {
System.out.println("不支持的文件格式，请使用.xls或.xlsx文件");
return false;
}
// 默认使用第一个工作表
sheet = workbook.getSheetAt(0);
System.out.println("成功加载Excel文件: " + inputFile);
System.out.println("数据行数: " + (sheet.getLastRowNum() + 1));
// 显示列名（如果有标题行）
Row headerRow = sheet.getRow(0);
if (headerRow != null) {
System.out.print("列名: [");
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell cell = headerRow.getCell(i);
String columnName = getCellValueAsString(cell);
System.out.print(columnName);
if (i < headerRow.getLastCellNum() - 1) {
System.out.print(", ");
}
}
System.out.println("]");
}
// 检查目标列是否存在
int targetColumnIndex = getColumnIndexByName(targetColumnName);
if (targetColumnIndex == -1) {
System.out.println("错误: 找不到目标列 '" + targetColumnName + "'");
return false;
}
System.out.println("目标列 '" + targetColumnName + "' 索引为: " + targetColumnIndex);
fileInputStream.close();
return true;
} catch (IOException e) {
System.out.println("加载Excel文件失败: " + e.getMessage());
return false;
}
}
/**
* 根据列名获取列索引
*/
private int getColumnIndexByName(String columnName) {
Row headerRow = sheet.getRow(0);
if (headerRow == null) return -1;
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell cell = headerRow.getCell(i);
if (cell != null && getCellValueAsString(cell).equals(columnName)) {
return i;
}
}
return -1;
}
/**
* 执行业务操作 - 根据行内容计算结果
*/
public String businessOperation(Row row) {
// 获取行数据
String name = getCellValueAsString(getCellByColumnName(row, "姓名"));
String ageStr = getCellValueAsString(getCellByColumnName(row, "年龄"));
String department = getCellValueAsString(getCellByColumnName(row, "部门"));
// 如果没有找到对应的列，则使用默认值
if (name == null || name.isEmpty()) {
name = "用户" + (row.getRowNum() + 1);
}
int age = 0;
try {
age = ageStr != null && !ageStr.isEmpty() ? Integer.parseInt(ageStr) :
new Random().nextInt(41) + 20; // 随机20-60岁
} catch (NumberFormatException e) {
age = new Random().nextInt(41) + 20;
}
if (department == null || department.isEmpty()) {
String[] depts = {"技术部", "销售部", "人事部", "财务部", "市场部", "运营部"};
department = depts[new Random().nextInt(depts.length)];
}
// 示例业务逻辑：根据年龄和部门计算评分
double baseScore = 50;
double ageFactor = Math.min(age * 0.5, 30); // 年龄加分，最高30分
double deptBonus = Arrays.asList("技术部", "研发部", "销售部").contains(department) ? 10 : 0; // 部门奖励
// 生成随机评分变动
double randomFactor = (new Random().nextDouble() * 15) - 5; // -5到10的随机数
double score = Math.round((baseScore + ageFactor + deptBonus + randomFactor) * 100.0) / 100.0;
// 生成业务处理结果
String grade = getGrade(score);
return department + "的" + name + "，综合评分:" + score + "，等级:" + grade;
}
/**
* 根据评分获取等级
*/
private String getGrade(double score) {
if (score >= 90) return "优秀";
else if (score >= 80) return "良好";
else if (score >= 70) return "中等";
else if (score >= 60) return "及格";
else return "不及格";
}
/**
* 根据列名获取单元格
*/
private Cell getCellByColumnName(Row row, String columnName) {
if (row == null || row.getRowNum() == 0) return null; // 跳过标题行
Row headerRow = sheet.getRow(0);
if (headerRow == null) return null;
int columnIndex = -1;
for (int i = 0; i < headerRow.getLastCellNum(); i++) {
Cell cell = headerRow.getCell(i);
if (cell != null && getCellValueAsString(cell).equals(columnName)) {
columnIndex = i;
break;
}
}
if (columnIndex != -1) {
return row.getCell(columnIndex);
}
return null;
}
/**
* 将单元格值转换为字符串
*/
private String getCellValueAsString(Cell cell) {
if (cell == null) return "";
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue().toString();
} else {
// 检查是否为整数
double numericValue = cell.getNumericCellValue();
if (numericValue == Math.floor(numericValue)) {
return String.valueOf((int) numericValue);
} else {
return String.valueOf(numericValue);
}
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case FORMULA:
return cell.getCellFormula();
default:
return "";
}
}
/**
* 处理Excel中的每一行，并将结果写入指定列
*/
public boolean processRows() {
if (workbook == null) {
System.out.println("请先加载Excel文件");
return false;
}
// 获取目标列索引
int targetColumnIndex = getColumnIndexByName(targetColumnName);
if (targetColumnIndex == -1) {
System.out.println("错误: 找不到目标列 '" + targetColumnName + "'");
return false;
}
System.out.println("将结果写入列: " + targetColumnName + " (索引: " + targetColumnIndex + ")");
System.out.println("开始处理数据...");
// 从第1行开始（跳过标题行）
int totalRows = sheet.getLastRowNum();
for (int i = 1; i <= totalRows; i++) {
try {
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
// 执行业务操作
String result = businessOperation(row);
// 将结果写入目标列
Cell targetCell = row.getCell(targetColumnIndex);
if (targetCell == null) {
targetCell = row.createCell(targetColumnIndex);
}
// 根据结果类型设置单元格值
try {
// 尝试解析为数字
double numValue = Double.parseDouble(result);
targetCell.setCellValue(numValue);
} catch (NumberFormatException e) {
// 如果不是数字，则作为字符串处理
targetCell.setCellValue(result);
}
// 显示进度
if ((i) % 10 == 0 || i == totalRows) {
System.out.printf("已处理 %d/%d 行\n", i, totalRows);
}
} catch (Exception e) {
System.out.println("处理第 " + (i + 1) + " 行时出错: " + e.getMessage());
e.printStackTrace();
continue;
}
}
System.out.println("数据处理完成");
return true;
}
/**
* 保存处理后的Excel文件
*/
public boolean saveExcel() {
try {
// 确保输出目录存在
File outputFileObj = new File(outputFile);
File parentDir = outputFileObj.getParentFile();
if (parentDir != null && !parentDir.exists()) {
parentDir.mkdirs();
}
FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("文件已保存至: " + outputFile);
return true;
} catch (IOException e) {
System.out.println("保存文件失败: " + e.getMessage());
return false;
}
}
/**
* 关闭工作簿
*/
public void close() {
try {
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
System.out.println("关闭工作簿时出错: " + e.getMessage());
}
}
/**
* 预览处理后的数据
*/
public void previewData(int n) {
if (sheet == null) {
System.out.println("没有可预览的数据");
return;
}
System.out.println("\n处理后的数据预览:");
int rowsToShow = Math.min(n + 1, sheet.getLastRowNum() + 1); // 包含标题行
for (int i = 0; i < rowsToShow; i++) {
Row row = sheet.getRow(i);
if (row != null) {
System.out.print("第" + (i + 1) + "行: [");
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
System.out.print(getCellValueAsString(cell));
if (j < row.getLastCellNum() - 1) {
System.out.print(", ");
}
}
System.out.println("]");
}
}
}
/**
* 创建示例Excel文件用于测试（包含指定的目标列）
*/
public static void createSampleExcel(String filename) {
try {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("示例数据");
// 创建标题行
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("姓名");
headerRow.createCell(1).setCellValue("年龄");
headerRow.createCell(2).setCellValue("部门");
headerRow.createCell(3).setCellValue("入职时间");
headerRow.createCell(4).setCellValue("结果"); // 这是我们要写入的目标列
// 创建示例数据行
String[] names = {"张三", "李四", "王五", "赵六", "钱七", "孙八", "周九", "吴十"};
String[] departments = {"技术部", "销售部", "人事部", "财务部", "技术部", "市场部", "技术部", "人事部"};
Random random = new Random();
for (int i = 0; i < names.length; i++) {
Row row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(names[i]);
row.createCell(1).setCellValue(25 + random.nextInt(20)); // 25-44岁
row.createCell(2).setCellValue(departments[i % departments.length]);
row.createCell(3).setCellValue(LocalDate.now().minusDays(random.nextInt(1000)).toString());
// 第4列（"结果"列）保持空白，将被写入业务处理结果
}
// 自动调整列宽
for (int i = 0; i < 5; i++) {
sheet.autoSizeColumn(i);
}
FileOutputStream fileOut = new FileOutputStream(filename);
workbook.write(fileOut);
fileOut.close();
workbook.close();
System.out.println("示例Excel文件已创建: " + filename);
} catch (IOException e) {
System.out.println("创建示例文件失败: " + e.getMessage());
}
}
// 主方法 - 使用示例
public static void main(String[] args) {
// 创建示例Excel文件
String sampleFile = "sample_data.xlsx";
createSampleExcel(sampleFile);
// 创建处理器实例，指定目标列名为"结果"
ExcelProcessor processor = new ExcelProcessor(
sampleFile,
"processed_data.xlsx",
"结果" // 指定要写入的列名
);
// 加载Excel文件
if (processor.loadExcel()) {
// 处理数据（将结果写入指定列）
boolean success = processor.processRows();
if (success) {
// 预览处理后的数据
processor.previewData(5);
// 保存处理后的文件
processor.saveExcel();
} else {
System.out.println("数据处理失败");
}
} else {
System.out.println("文件加载失败");
}
// 关闭资源
processor.close();
}
}