日常开发中报表导出、数据批量规整、模板修改等场景经常需要动态对 Excel 表格插入、删除行和列。本文将介绍如何使用 Java 操作 Excel 文件的行和列包括对.xls、.xlsx格式表格的行、列新增与删除附带完整可运行示例。安装依赖本文示例使用的是Spire.XLS for Java这是一个专门处理Excel文件的Java库。你可以在pom.xml文件从添加以下代码从Maven安装该库Maven依赖repositoriesrepositoryidcom.e-iceblue/idurlhttps://repo.e-iceblue.cn/repository/maven-public//url/repository/repositoriesdependenciesdependencygroupIde-iceblue/groupIdartifactIdspire.xls/artifactIdversion14.12.0/version/dependency/dependencies一、插入行和列最基础的操作就是插入行和列了。比如要在第2行前面插入一个新行importcom.spire.xls.*;publicclassInsertRowsColumns{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetworksheetworkbook.getWorksheets().get(0);// 在第2行位置插入一行worksheet.insertRow(2);// 在第2列位置插入一列worksheet.insertColumn(2);workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}批量插入也很简单第二个参数指定插入的数量// 从第5行开始连续插入2行worksheet.insertRow(5,2);// 从第5列开始连续插入2列worksheet.insertColumn(5,2);实际场景我经常在生成报表时根据数据量动态插入行。比如有10条记录就在表头后面插入10行。二、删除行和列删除操作和插入类似但要注意删除后后面的行/列会自动前移。importcom.spire.xls.*;publicclassDeleteRowsColumns{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetworksheetworkbook.getWorksheets().get(0);// 从第5行开始删除4行删除第5、6、7、8行worksheet.deleteRow(5,4);// 从第2列开始删除2列删除第2、3列worksheet.deleteColumn(2,2);workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}踩过的坑如果循环删除多行记得从后往前删否则索引会乱。三、删除空白行和列这个功能特别实用经常遇到导出的Excel里有很多空白行手动删太麻烦。importcom.spire.xls.*;publicclassDeleteBlankRows{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(messy_data.xlsx);Worksheetsheetworkbook.getWorksheets().get(0);// 删除空白行从后往前遍历for(intisheet.getRows().length-1;i0;i--){if(sheet.getRows()[i].isBlank()){sheet.deleteRow(i1);// 注意行号从1开始}}// 删除空白列从后往前遍历for(intjsheet.getColumns().length-1;j0;j--){if(sheet.getColumns()[j].isBlank()){sheet.deleteColumn(j1);// 注意列号从1开始}}workbook.saveToFile(cleaned.xlsx,ExcelVersion.Version2013);workbook.dispose();}}为什么要从后往前删因为删除一行后后面的行号会变化。如果从前往后删可能会跳过某些行或者删错位置。应用场景清理从数据库导出的Excel、去除模板中的占位行等。四、复制行和列有时候需要在同一个Sheet内复制行或者跨Sheet复制数据。同一Sheet内复制importcom.spire.xls.*;publicclassCopyRows{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetsheetworkbook.getWorksheets().get(0);// 把第1行复制到第3行// 参数源行、目标行、是否复制值、是否复制格式、是否复制公式sheet.copy(sheet.getRows()[0],sheet.getRows()[2],true,true,true);workbook.saveToFile(result.xlsx,ExcelVersion.Version2010);workbook.dispose();}}跨Sheet复制Worksheetsheet1workbook.getWorksheets().get(0);Worksheetsheet2workbook.getWorksheets().get(1);// 把Sheet1的第1行复制到Sheet2的第2行sheet1.copy(sheet1.getRows()[0],sheet2.getRows()[1],true,true,true);三个布尔参数说明第一个是否复制单元格的值第二个是否复制格式字体、颜色等第三个是否复制公式实际用途我经常用它来复制表头到多个Sheet或者根据模板行批量生成数据行。五、隐藏和显示行列有些敏感数据不想直接显示可以隐藏起来importcom.spire.xls.*;publicclassHideRowsColumns{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetworksheetworkbook.getWorksheets().get(0);// 隐藏第2列worksheet.hideColumn(2);// 隐藏第4行worksheet.hideRow(4);workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}取消隐藏// 显示第2列worksheet.showColumn(2);// 显示第4行worksheet.showRow(4);使用场景财务报表中隐藏中间计算过程、保护敏感信息等。六、设置行高和列宽默认的行列尺寸可能不合适需要自定义importcom.spire.xls.*;publicclassSetHeightWidth{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetworksheetworkbook.getWorksheets().get(0);// 设置第4列宽度为30worksheet.setColumnWidth(4,30);// 设置第4行高度为30worksheet.setRowHeight(4,30);workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}批量设置默认值// 设置所有行的默认高度worksheet.setDefaultRowHeight(20);// 设置所有列的默认宽度worksheet.setDefaultColumnWidth(15);经验之谈中文内容通常需要更宽的列英文可以窄一些。我一般会根据内容类型预设不同的列宽。七、自动调整列宽如果不确定列宽设多少合适可以让Excel自动调整importcom.spire.xls.*;publicclassAutoFitColumns{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetworksheetworkbook.getWorksheets().get(0);// 自动调整指定区域的列宽worksheet.getAllocatedRange().autoFitColumns();// 也可以只调整某几列// worksheet.getCellRange(A1:C10).autoFitColumns();workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}注意自动调整会遍历所有单元格数据量大时会比较慢。如果性能重要建议手动设置固定宽度。八、分组行列大纲功能Excel的大纲功能可以把相关行/列折叠起来适合层级数据importcom.spire.xls.*;publicclassGroupRowsColumns{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetsheetworkbook.getWorksheets().get(0);// 对第1到5行进行分组可以折叠// 第三个参数是否在下方显示汇总行sheet.groupByRows(1,5,false);// 对第1到3列进行分组sheet.groupByColumns(1,3,false);workbook.saveToFile(result.xlsx,ExcelVersion.Version2013);workbook.dispose();}}实际场景财务报表按月分组、项目计划按阶段分组等。用户可以选择展开或折叠查看不同粒度的数据。九、根据条件删除行有时候需要根据内容筛选并删除某些行比如删除包含特定关键词的行importcom.spire.xls.*;publicclassRemoveRowByKeyword{publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();workbook.loadFromFile(data.xlsx);Worksheetsheetworkbook.getWorksheets().get(0);Stringkeyword测试;// 从后往前遍历删除包含关键词的行for(intisheet.getRows().length-1;i0;i--){booleanshouldDeletefalse;// 检查该行所有单元格for(Objectcell:sheet.getRows()[i]){if(cell!nullcell.toString().contains(keyword)){shouldDeletetrue;break;}}if(shouldDelete){sheet.deleteRow(i1);}}workbook.saveToFile(filtered.xlsx,ExcelVersion.Version2013);workbook.dispose();}}应用场景清理测试数据、过滤无效记录、删除标记为废弃的行等。十、综合实战动态生成报表最后分享一个完整的例子根据数据动态生成带格式的报表。importcom.spire.xls.*;importjava.util.List;importjava.util.ArrayList;publicclassDynamicReport{// 模拟数据类staticclassProduct{Stringname;doubleprice;intquantity;Product(Stringname,doubleprice,intquantity){this.namename;this.priceprice;this.quantityquantity;}}publicstaticvoidmain(String[]args){WorkbookworkbooknewWorkbook();Worksheetsheetworkbook.getWorksheets().get(0);// 准备数据ListProductproductsnewArrayList();products.add(newProduct(产品A,99.9,100));products.add(newProduct(产品B,199.9,50));products.add(newProduct(产品C,299.9,30));// 1. 写入表头String[]headers{产品名称,单价,数量,总价};for(inti0;iheaders.length;i){sheet.getCellRange(1,i1).setValue(headers[i]);}// 格式化表头sheet.getCellRange(1,1,1,headers.length).getStyle().getFont().isBold(true);sheet.getCellRange(1,1,1,headers.length).getStyle().setKnownColor(ExcelColors.LightBlue);// 2. 动态插入数据行for(inti0;iproducts.size();i){introwi2;// 从第2行开始第1行是表头Productpproducts.get(i);sheet.getCellRange(row,1).setValue(p.name);sheet.getCellRange(row,2).setNumberValue(p.price);sheet.getCellRange(row,3).setNumberValue(p.quantity);// 总价 单价 * 数量使用公式sheet.getCellRange(row,4).setFormula(Brow*Crow);sheet.getCellRange(row,4).getStyle().setNumberFormat(¥#,##0.00);}// 3. 添加汇总行intsummaryRowproducts.size()2;sheet.getCellRange(summaryRow,1).setValue(合计);sheet.getCellRange(summaryRow,1).getStyle().getFont().isBold(true);// 汇总公式sheet.getCellRange(summaryRow,3).setFormula(SUM(C2:C(summaryRow-1)));sheet.getCellRange(summaryRow,4).setFormula(SUM(D2:D(summaryRow-1)));sheet.getCellRange(summaryRow,4).getStyle().setNumberFormat(¥#,##0.00);// 4. 设置列宽sheet.setColumnWidth(1,20);// 产品名称sheet.setColumnWidth(2,15);// 单价sheet.setColumnWidth(3,15);// 数量sheet.setColumnWidth(4,20);// 总价// 5. 自动调整可选// sheet.getAllocatedRange().autoFitColumns();// 6. 添加边框sheet.getCellRange(1,1,summaryRow,4).getBorders().setLineStyle(LineStyleType.Thin);workbook.saveToFile(product_report.xlsx,ExcelVersion.Version2013);workbook.dispose();System.out.println(报表生成完成);}}这个例子展示了动态插入数据行根据数据量使用公式自动计算设置格式加粗、背景色、数字格式调整列宽添加边框小结总结一下几个关键点插入行列用insertRow()和insertColumn()支持批量插入删除行列用deleteRow()和deleteColumn()循环删除要从后往前删除空白用isBlank()判断清理脏数据很实用复制行列用copy()方法可以控制是否复制值、格式、公式隐藏显示用hideRow/Column()和showRow/Column()设置尺寸用setRowHeight()和setColumnWidth()自动调整用autoFitColumns()但大数据量时注意性能分组折叠用groupByRows/Columns()适合层级数据实际使用中最重要的是理解业务需求。不是所有场景都需要复杂的操作有时候简单的插入和删除就能解决问题。
