一、前言
在日常开发中，Excel文件的读写操作是绑定业务系统的常见需求，比如数据导入导出、报表生成、批量数据处理等。Apache POI是Java领域处理Excel文件的主流框架，但其API设计较为底层，使用起来代码冗长且容易出错。
Hutool作为一款国产的Java工具类库，对POI进行了友好的封装。其中RowUtil是专门用于操作Excel行数据的工具类，提供了行的创建、读取、写入、插入、删除等常用操作，能够有效简化开发工作量。
本文将详细介绍RowUtil的核心API及其在实际项目中的应用场景。
二、环境准备
使用RowUtil前需要引入Hutool的POI模块依赖：
<!-- Maven依赖 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-poi</artifactId>
<version>5.8.43</version>
</dependency>
<!-- POI依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
RowUtil类位于cn.hutool.poi.excel.cell包下，完整类名为cn.hutool.poi.excel.cell.RowUtil。
三、核心API详解
3.1 getOrCreateRow - 获取或创建行
该方法用于获取指定索引位置的行，如果该行不存在则自动创建。这是操作Excel行数据的基础方法，避免了手动判断行是否存在的繁琐逻辑。
方法签名：
public static Row getOrCreateRow(Sheet sheet, int rowIndex)
参数说明：
返回值：返回指定位置的Row对象
基础使用示例：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class GetOrCreateRowDemo {
public static void main(String[] args) throws IOException {
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("员工信息");
// 获取或创建第一行（索引为0）作为标题行
Row headerRow = RowUtil.getOrCreateRow(sheet, 0);
headerRow.createCell(0).setCellValue("员工编号");
headerRow.createCell(1).setCellValue("姓名");
headerRow.createCell(2).setCellValue("部门");
// 获取或创建数据行
Row dataRow1 = RowUtil.getOrCreateRow(sheet, 1);
dataRow1.createCell(0).setCellValue("EMP001");
dataRow1.createCell(1).setCellValue("张三");
dataRow1.createCell(2).setCellValue("技术部");
Row dataRow2 = RowUtil.getOrCreateRow(sheet, 2);
dataRow2.createCell(0).setCellValue("EMP002");
dataRow2.createCell(1).setCellValue("李四");
dataRow2.createCell(2).setCellValue("市场部");
// 再次获取已存在的行，不会重复创建
Row existingRow = RowUtil.getOrCreateRow(sheet, 1);
System.out.println("第二行第一列的值：" + existingRow.getCell(0).getStringCellValue());
// 保存文件
try (FileOutputStream fos = new FileOutputStream("employee.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("文件创建成功");
}
}
3.2 readRow - 读取行数据
RowUtil提供了两个重载的readRow方法，用于将行数据读取为List集合。
方法签名一（读取整行）：
public static List<Object> readRow(Row row, CellEditor cellEditor)
方法签名二（读取指定范围的列）：
public static List<Object> readRow(Row row, int startCellNumInclude, int endCellNumInclude, CellEditor cellEditor)
参数说明：
- • startCellNumInclude：起始列索引（包含）
- • endCellNumInclude：结束列索引（包含）
- • cellEditor：单元格编辑器，可对读取的值进行转换处理，传null表示不做处理
返回值：返回包含行数据的List集合
基础使用示例：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
public class ReadRowDemo {
public static void main(String[] args) throws IOException {
// 读取已有的Excel文件
FileInputStream fis = new FileInputStream("employee.xlsx");
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
// 读取第一行的所有数据
Row headerRow = sheet.getRow(0);
List<Object> headerData = RowUtil.readRow(headerRow, null);
System.out.println("表头数据：" + headerData);
// 读取第二行的所有数据
Row dataRow = sheet.getRow(1);
List<Object> rowData = RowUtil.readRow(dataRow, null);
System.out.println("第一条数据：" + rowData);
// 只读取第1到第2列的数据（索引0和1）
List<Object> partialData = RowUtil.readRow(dataRow, 0, 1, null);
System.out.println("部分数据（编号和姓名）：" + partialData);
workbook.close();
fis.close();
}
}
使用CellEditor进行数据转换：
import cn.hutool.poi.excel.cell.CellEditor;
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
public class ReadRowWithEditorDemo {
public static void main(String[] args) throws IOException {
FileInputStream fis = new FileInputStream("employee.xlsx");
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Row dataRow = sheet.getRow(1);
// 定义单元格编辑器，对数据进行处理
CellEditor cellEditor = new CellEditor() {
@Override
public Object edit(Cell cell, Object value) {
if (value == null) {
return "";
}
// 将所有值转为字符串并去除首尾空格
String strValue = value.toString().trim();
// 对特定列进行脱敏处理
if (cell.getColumnIndex() == 1) {
// 姓名只显示姓氏
return strValue.substring(0, 1) + "**";
}
return strValue;
}
};
List<Object> processedData = RowUtil.readRow(dataRow, cellEditor);
System.out.println("处理后的数据：" + processedData);
workbook.close();
fis.close();
}
}
3.3 writeRow - 写入行数据
writeRow方法用于向指定行写入数据，提供了两个重载版本，支持设置样式。
方法签名一（无样式写入）：
public static void writeRow(Row row, Iterable<?> rowData)
方法签名二（带样式写入）：
public static void writeRow(Row row, Iterable<?> rowData, StyleSet styleSet, boolean isHeader)
参数说明：
- • isHeader：是否为标题行，影响样式的应用
基础使用示例：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class WriteRowDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("订单数据");
// 准备标题数据
List<String> headers = Arrays.asList("订单号", "商品名称", "数量", "单价", "金额");
// 创建并写入标题行
Row headerRow = RowUtil.getOrCreateRow(sheet, 0);
RowUtil.writeRow(headerRow, headers);
// 准备订单数据
List<Object> order1 = Arrays.asList("ORD20240101001", "笔记本电脑", 2, 5999.00, 11998.00);
List<Object> order2 = Arrays.asList("ORD20240101002", "无线鼠标", 5, 89.00, 445.00);
List<Object> order3 = Arrays.asList("ORD20240101003", "机械键盘", 3, 299.00, 897.00);
// 写入数据行
Row dataRow1 = RowUtil.getOrCreateRow(sheet, 1);
RowUtil.writeRow(dataRow1, order1);
Row dataRow2 = RowUtil.getOrCreateRow(sheet, 2);
RowUtil.writeRow(dataRow2, order2);
Row dataRow3 = RowUtil.getOrCreateRow(sheet, 3);
RowUtil.writeRow(dataRow3, order3);
// 保存文件
try (FileOutputStream fos = new FileOutputStream("orders.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("订单文件创建成功");
}
}
带样式写入示例：
import cn.hutool.poi.excel.StyleSet;
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class WriteRowWithStyleDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("销售报表");
// 创建样式集
StyleSet styleSet = new StyleSet(workbook);
// 设置标题行样式：加粗、居中、背景色
CellStyle headStyle = styleSet.getHeadCellStyle();
Font headFont = workbook.createFont();
headFont.setBold(true);
headFont.setFontHeightInPoints((short) 12);
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置数据行样式：居中
CellStyle cellStyle = styleSet.getCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 写入标题行
List<String> headers = Arrays.asList("月份", "销售额", "成本", "利润", "利润率");
Row headerRow = RowUtil.getOrCreateRow(sheet, 0);
RowUtil.writeRow(headerRow, headers, styleSet, true);
// 写入数据行
List<List<Object>> dataList = Arrays.asList(
Arrays.asList("1月", 150000, 90000, 60000, "40%"),
Arrays.asList("2月", 180000, 100000, 80000, "44%"),
Arrays.asList("3月", 220000, 120000, 100000, "45%")
);
for (int i = 0; i < dataList.size(); i++) {
Row dataRow = RowUtil.getOrCreateRow(sheet, i + 1);
RowUtil.writeRow(dataRow, dataList.get(i), styleSet, false);
}
// 设置列宽
for (int i = 0; i < headers.size(); i++) {
sheet.setColumnWidth(i, 15 * 256);
}
try (FileOutputStream fos = new FileOutputStream("sales_report.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("销售报表创建成功");
}
}
3.4 insertRow - 插入行
该方法用于在指定位置插入若干空行，原有行会向下移动。在需要动态添加数据或调整Excel结构时非常有用。
方法签名：
public static void insertRow(Sheet sheet, int startRow, int insertNumber)
参数说明：
基础使用示例：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
public class InsertRowDemo {
public static void main(String[] args) throws IOException {
// 创建初始数据
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("数据表");
// 写入初始数据
String[][] initialData = {
{"序号", "项目", "状态"},
{"1", "项目A", "进行中"},
{"2", "项目B", "已完成"},
{"3", "项目C", "待启动"}
};
for (int i = 0; i < initialData.length; i++) {
Row row = RowUtil.getOrCreateRow(sheet, i);
RowUtil.writeRow(row, Arrays.asList(initialData[i]));
}
System.out.println("插入前的数据：");
printSheetData(sheet);
// 在第2行（索引1）的位置插入2行
RowUtil.insertRow(sheet, 1, 2);
// 在插入的空行中填入新数据
Row newRow1 = sheet.getRow(1);
RowUtil.writeRow(newRow1, Arrays.asList("", "新增项目1", "规划中"));
Row newRow2 = sheet.getRow(2);
RowUtil.writeRow(newRow2, Arrays.asList("", "新增项目2", "评审中"));
System.out.println("\n插入后的数据：");
printSheetData(sheet);
try (FileOutputStream fos = new FileOutputStream("insert_demo.xlsx")) {
workbook.write(fos);
}
workbook.close();
}
private static void printSheetData(Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
StringBuilder sb = new StringBuilder();
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j) != null) {
sb.append(row.getCell(j).toString()).append("\t");
}
}
System.out.println("第" + (i + 1) + "行：" + sb);
}
}
}
}
3.5 removeRow - 删除行
该方法用于从工作表中删除指定的行。与直接使用POI的removeRow方法不同，RowUtil的removeRow方法修复了删除行时可能拆分合并单元格的问题，处理更加安全。
方法签名：
public static void removeRow(Row row)
参数说明：
基础使用示例：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
public class RemoveRowDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("待删除测试");
// 创建测试数据
String[][] testData = {
{"编号", "姓名", "状态"},
{"001", "张三", "在职"},
{"002", "李四", "离职"},
{"003", "王五", "在职"},
{"004", "赵六", "在职"}
};
for (int i = 0; i < testData.length; i++) {
Row row = RowUtil.getOrCreateRow(sheet, i);
RowUtil.writeRow(row, Arrays.asList(testData[i]));
}
System.out.println("删除前的数据：");
printSheetData(sheet);
// 删除第3行（索引2，即李四那行，状态为离职）
Row rowToDelete = sheet.getRow(2);
RowUtil.removeRow(rowToDelete);
System.out.println("\n删除后的数据：");
printSheetData(sheet);
try (FileOutputStream fos = new FileOutputStream("remove_demo.xlsx")) {
workbook.write(fos);
}
workbook.close();
}
private static void printSheetData(Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
StringBuilder sb = new StringBuilder();
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j) != null) {
sb.append(row.getCell(j).toString()).append("\t");
}
}
System.out.println("第" + (i + 1) + "行：" + sb);
}
}
}
}
批量删除满足条件的行：
import cn.hutool.poi.excel.cell.RowUtil;
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.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class BatchRemoveRowDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("批量删除测试");
// 创建测试数据
String[][] testData = {
{"订单号", "金额", "是否作废"},
{"ORD001", "1000", "否"},
{"ORD002", "500", "是"},
{"ORD003", "2000", "否"},
{"ORD004", "300", "是"},
{"ORD005", "1500", "否"}
};
for (int i = 0; i < testData.length; i++) {
Row row = RowUtil.getOrCreateRow(sheet, i);
RowUtil.writeRow(row, Arrays.asList(testData[i]));
}
// 收集需要删除的行（状态为"是"表示已作废）
List<Row> rowsToDelete = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null && row.getCell(2) != null) {
String status = row.getCell(2).getStringCellValue();
if ("是".equals(status)) {
rowsToDelete.add(row);
}
}
}
// 从后向前删除，避免行索引变化导致的问题
for (int i = rowsToDelete.size() - 1; i >= 0; i--) {
RowUtil.removeRow(rowsToDelete.get(i));
}
System.out.println("删除作废订单后的数据：");
printSheetData(sheet);
try (FileOutputStream fos = new FileOutputStream("batch_remove_demo.xlsx")) {
workbook.write(fos);
}
workbook.close();
}
private static void printSheetData(Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
StringBuilder sb = new StringBuilder();
for (int j = 0; j < row.getLastCellNum(); j++) {
if (row.getCell(j) != null) {
sb.append(row.getCell(j).toString()).append("\t");
}
}
System.out.println("第" + (i + 1) + "行：" + sb);
}
}
}
}
四、实战场景应用
4.1 场景一：员工信息批量导入与校验
企业HR系统中经常需要批量导入员工信息，需要读取Excel数据并进行校验处理。
import cn.hutool.poi.excel.cell.CellEditor;
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class EmployeeImportDemo {
public static void main(String[] args) throws IOException {
// 模拟创建待导入的员工Excel文件
createSourceFile();
// 读取并处理员工数据
FileInputStream fis = new FileInputStream("employee_import.xlsx");
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
// 定义数据清洗编辑器
CellEditor dataCleanEditor = new CellEditor() {
@Override
public Object edit(Cell cell, Object value) {
if (value == null) {
return "";
}
String strValue = value.toString().trim();
// 手机号脱敏处理
if (cell.getColumnIndex() == 2 && strValue.length() == 11) {
return strValue.substring(0, 3) + "****" + strValue.substring(7);
}
return strValue;
}
};
List<List<Object>> validEmployees = new ArrayList<>();
List<String> errorMessages = new ArrayList<>();
// 从第二行开始读取数据（跳过标题行）
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row == null) continue;
List<Object> rowData = RowUtil.readRow(row, dataCleanEditor);
// 校验数据完整性
if (rowData.size() < 4) {
errorMessages.add("第" + (i + 1) + "行数据不完整");
continue;
}
String name = rowData.get(0).toString();
String dept = rowData.get(1).toString();
if (name.isEmpty() || dept.isEmpty()) {
errorMessages.add("第" + (i + 1) + "行姓名或部门为空");
continue;
}
validEmployees.add(rowData);
}
// 输出处理结果
System.out.println("成功导入 " + validEmployees.size() + " 条员工数据：");
for (List<Object> emp : validEmployees) {
System.out.println(emp);
}
if (!errorMessages.isEmpty()) {
System.out.println("\n存在 " + errorMessages.size() + " 条错误：");
errorMessages.forEach(System.out::println);
}
workbook.close();
fis.close();
}
private static void createSourceFile() throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("员工列表");
String[][] data = {
{"姓名", "部门", "手机号", "入职日期"},
{"张三", "技术部", "13812345678", "2024-01-15"},
{"李四", "市场部", "13987654321", "2024-02-20"},
{"", "财务部", "13511112222", "2024-03-10"},
{"赵六", "人事部", "13633334444", "2024-03-25"}
};
for (int i = 0; i < data.length; i++) {
Row row = RowUtil.getOrCreateRow(sheet, i);
RowUtil.writeRow(row, Arrays.asList(data[i]));
}
try (FileOutputStream fos = new FileOutputStream("employee_import.xlsx")) {
workbook.write(fos);
}
workbook.close();
}
}
4.2 场景二：财务报表动态生成
财务系统需要根据业务数据动态生成报表，包含汇总行的插入和格式化。
import cn.hutool.poi.excel.StyleSet;
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
public class FinancialReportDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("季度财务报表");
// 创建样式
StyleSet styleSet = new StyleSet(workbook);
CellStyle headerStyle = styleSet.getHeadCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerStyle.setFont(headerFont);
// 写入报表标题
Row titleRow = RowUtil.getOrCreateRow(sheet, 0);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("2024年第一季度财务报表");
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
// 写入表头
List<String> headers = Arrays.asList("月份", "收入", "支出", "利润", "利润率");
Row headerRow = RowUtil.getOrCreateRow(sheet, 1);
RowUtil.writeRow(headerRow, headers, styleSet, true);
// 写入月度数据
double[][] monthlyData = {
{150000, 90000},
{180000, 105000},
{210000, 115000}
};
String[] months = {"1月", "2月", "3月"};
double totalIncome = 0;
double totalExpense = 0;
for (int i = 0; i < monthlyData.length; i++) {
double income = monthlyData[i][0];
double expense = monthlyData[i][1];
double profit = income - expense;
double profitRate = profit / income * 100;
totalIncome += income;
totalExpense += expense;
Row dataRow = RowUtil.getOrCreateRow(sheet, i + 2);
List<Object> rowData = Arrays.asList(
months[i],
income,
expense,
profit,
String.format("%.1f%%", profitRate)
);
RowUtil.writeRow(dataRow, rowData, styleSet, false);
}
// 在数据后插入一行作为汇总行
int summaryRowIndex = 2 + monthlyData.length;
RowUtil.insertRow(sheet, summaryRowIndex, 1);
// 写入汇总数据
Row summaryRow = RowUtil.getOrCreateRow(sheet, summaryRowIndex);
double totalProfit = totalIncome - totalExpense;
double totalProfitRate = totalProfit / totalIncome * 100;
List<Object> summaryData = Arrays.asList(
"合计",
totalIncome,
totalExpense,
totalProfit,
String.format("%.1f%%", totalProfitRate)
);
RowUtil.writeRow(summaryRow, summaryData, styleSet, true);
// 设置列宽
sheet.setColumnWidth(0, 12 * 256);
for (int i = 1; i < 5; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
try (FileOutputStream fos = new FileOutputStream("financial_report.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("财务报表生成成功");
}
}
4.3 场景三：库存盘点数据清洗
仓储系统中需要对盘点数据进行清洗，删除无效记录并更新库存状态。
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class InventoryCleanDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("库存盘点");
// 模拟盘点数据
Object[][] inventoryData = {
{"商品编码", "商品名称", "系统库存", "实际库存", "差异", "状态"},
{"SKU001", "办公桌", 50, 50, 0, "正常"},
{"SKU002", "办公椅", 100, 98, -2, "盘亏"},
{"SKU003", "文件柜", 30, 30, 0, "正常"},
{"SKU004", "打印机", 20, 22, 2, "盘盈"},
{"SKU005", "碎纸机", 15, 0, -15, "作废"},
{"SKU006", "投影仪", 10, 10, 0, "正常"},
{"SKU007", "扫描仪", 8, 0, -8, "作废"}
};
// 写入原始数据
for (int i = 0; i < inventoryData.length; i++) {
Row row = RowUtil.getOrCreateRow(sheet, i);
RowUtil.writeRow(row, Arrays.asList(inventoryData[i]));
}
System.out.println("清洗前的数据：");
printSheetData(sheet);
// 收集需要删除的行（状态为作废的记录）
List<Row> rowsToDelete = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
Cell statusCell = row.getCell(5);
if (statusCell != null && "作废".equals(statusCell.getStringCellValue())) {
rowsToDelete.add(row);
}
}
}
// 从后向前删除
for (int i = rowsToDelete.size() - 1; i >= 0; i--) {
RowUtil.removeRow(rowsToDelete.get(i));
}
// 重新编号并更新差异状态
int dataRowIndex = 1;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null && row.getCell(0) != null) {
// 读取差异值并更新状态描述
Cell diffCell = row.getCell(4);
Cell statusCell = row.getCell(5);
if (diffCell != null && diffCell.getCellType() == CellType.NUMERIC) {
double diff = diffCell.getNumericCellValue();
if (diff == 0) {
statusCell.setCellValue("库存一致");
} else if (diff > 0) {
statusCell.setCellValue("需核实盘盈");
} else {
statusCell.setCellValue("需核实盘亏");
}
}
dataRowIndex++;
}
}
System.out.println("\n清洗后的数据：");
printSheetData(sheet);
try (FileOutputStream fos = new FileOutputStream("inventory_clean.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("\n库存数据清洗完成，共删除 " + rowsToDelete.size() + " 条作废记录");
}
private static void printSheetData(Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if (row != null) {
StringBuilder sb = new StringBuilder();
for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
if (cell != null) {
sb.append(getCellValue(cell)).append("\t");
}
}
System.out.println(sb);
}
}
}
private static String getCellValue(Cell cell) {
if (cell.getCellType() == CellType.NUMERIC) {
return String.valueOf((int) cell.getNumericCellValue());
}
return cell.getStringCellValue();
}
}
4.4 场景四：考勤数据汇总与统计
HR系统需要处理员工考勤数据，进行汇总统计并生成月度报表。
import cn.hutool.poi.excel.StyleSet;
import cn.hutool.poi.excel.cell.RowUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class AttendanceReportDemo {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("考勤汇总");
StyleSet styleSet = new StyleSet(workbook);
// 表头
List<String> headers = Arrays.asList(
"员工姓名", "部门", "应出勤", "实际出勤", "迟到", "早退", "请假", "出勤率"
);
Row headerRow = RowUtil.getOrCreateRow(sheet, 0);
RowUtil.writeRow(headerRow, headers, styleSet, true);
// 模拟考勤数据
List<Map<String, Object>> attendanceList = new ArrayList<>();
attendanceList.add(createAttendance("张三", "技术部", 22, 22, 1, 0, 0));
attendanceList.add(createAttendance("李四", "技术部", 22, 20, 2, 1, 2));
attendanceList.add(createAttendance("王五", "市场部", 22, 21, 0, 0, 1));
attendanceList.add(createAttendance("赵六", "市场部", 22, 22, 0, 0, 0));
attendanceList.add(createAttendance("钱七", "财务部", 22, 19, 3, 2, 3));
// 写入考勤数据
int rowIndex = 1;
for (Map<String, Object> attendance : attendanceList) {
Row dataRow = RowUtil.getOrCreateRow(sheet, rowIndex++);
int shouldAttend = (int) attendance.get("shouldAttend");
int actualAttend = (int) attendance.get("actualAttend");
double attendRate = (double) actualAttend / shouldAttend * 100;
List<Object> rowData = Arrays.asList(
attendance.get("name"),
attendance.get("dept"),
shouldAttend,
actualAttend,
attendance.get("late"),
attendance.get("early"),
attendance.get("leave"),
String.format("%.1f%%", attendRate)
);
RowUtil.writeRow(dataRow, rowData, styleSet, false);
}
// 按部门分组统计
Map<String, int[]> deptStats = new LinkedHashMap<>();
for (Map<String, Object> att : attendanceList) {
String dept = (String) att.get("dept");
int[] stats = deptStats.computeIfAbsent(dept, k -> new int[4]);
stats[0] += (int) att.get("shouldAttend");
stats[1] += (int) att.get("actualAttend");
stats[2] += (int) att.get("late");
stats[3] += (int) att.get("leave");
}
// 插入空行分隔
RowUtil.insertRow(sheet, rowIndex, 1);
rowIndex++;
// 写入部门汇总
Row deptHeaderRow = RowUtil.getOrCreateRow(sheet, rowIndex++);
RowUtil.writeRow(deptHeaderRow,
Arrays.asList("部门汇总", "", "应出勤", "实际出勤", "迟到次数", "", "请假天数", "平均出勤率"),
styleSet, true);
for (Map.Entry<String, int[]> entry : deptStats.entrySet()) {
Row deptRow = RowUtil.getOrCreateRow(sheet, rowIndex++);
int[] stats = entry.getValue();
double avgRate = (double) stats[1] / stats[0] * 100;
List<Object> deptData = Arrays.asList(
entry.getKey(), "",
stats[0], stats[1], stats[2], "", stats[3],
String.format("%.1f%%", avgRate)
);
RowUtil.writeRow(deptRow, deptData, styleSet, false);
}
// 设置列宽
int[] columnWidths = {12, 10, 10, 10, 8, 8, 8, 10};
for (int i = 0; i < columnWidths.length; i++) {
sheet.setColumnWidth(i, columnWidths[i] * 256);
}
try (FileOutputStream fos = new FileOutputStream("attendance_report.xlsx")) {
workbook.write(fos);
}
workbook.close();
System.out.println("考勤报表生成成功");
}
private static Map<String, Object> createAttendance(String name, String dept,
int shouldAttend, int actualAttend, int late, int early, int leave) {
Map<String, Object> map = new HashMap<>();
map.put("name", name);
map.put("dept", dept);
map.put("shouldAttend", shouldAttend);
map.put("actualAttend", actualAttend);
map.put("late", late);
map.put("early", early);
map.put("leave", leave);
return map;
}
}
五、使用注意事项
在使用RowUtil工具类时，有几点需要特别注意：
第一，行索引从0开始计算，与Excel界面显示的行号差1。操作时要注意索引和实际行号的对应关系。
第二，删除多行时建议从后向前删除，避免因行索引变化导致删除错误的行。
第三，使用insertRow插入行后，原有行会自动下移，但单元格的公式引用不会自动更新，需要手动处理。
第四，removeRow方法相比POI原生方法更安全，能正确处理合并单元格的情况，推荐优先使用。
第五，读取数据时配合CellEditor可以实现数据清洗、格式转换、脱敏处理等功能，灵活性很高。
第六，写入大量数据时，建议使用SXSSFWorkbook替代XSSFWorkbook，以减少内存占用。
六、总结
Hutool的RowUtil工具类对Apache POI的行操作进行了很好的封装，提供了简洁易用的API。通过getOrCreateRow可以避免繁琐的空值判断，readRow和writeRow简化了数据的读写操作，insertRow和removeRow则让行的增删变得更加安全可靠。
在实际项目中，RowUtil特别适合用于数据导入导出、报表生成、批量数据处理等场景。配合Hutool的其他Excel工具类如ExcelWriter、ExcelReader一起使用，可以大幅提升开发效率，减少样板代码的编写。
建议在使用前先熟悉POI的基本概念，理解Workbook、Sheet、Row、Cell之间的层级关系，这样才能更好地发挥RowUtil的作用。
