有时候我们可能遇到相当复杂的excel，比如表头的合并等操作，一种简单的方式就是直接代码合并(浪费时间)，另一种就是写好模板，动态的向模板中增加行和修改指定单元格数据。
1.一个简单的根据模板sheet动态修改
原来的excel模板内容如下:
现在的需求是动态的生成生成时间和生成人。并且在第五行开始的数据列表增加5列:
package cn.xm.exam.test; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class DynamicOperateExcelUtils { public static void main(String[] args) throws IOException { // 读取源文件 FileInputStream fis = new FileInputStream("G:/test.xlsx"); XSSFWorkbook workBook = new XSSFWorkbook(fis); // 进行模板的克隆(接下来的操作都是针对克隆后的sheet) XSSFSheet sheet = workBook.cloneSheet(0); workBook.setSheetName(0, "sheet-0"); // 给sheet命名 // 读取指定cell的内容 XSSFCell nameCell = sheet.getRow(1).getCell(0); XSSFCell nameCell2 = sheet.getRow(1).getCell(1); System.out.println(nameCell.getStringCellValue()); System.out.println(nameCell2.getStringCellValue()); // 替换单元格内容(注意获取的cell的下标是合并之前的下标) replaceCellValue(sheet.getRow(1).getCell(2), "xxxxx时间"); replaceCellValue(sheet.getRow(2).getCell(2), "xxxxx人"); // 动态插入数据-增加行 List<Map<String, Object>> datas = new ArrayList<>(); for (int i = 0; i < 5; i++) { Map data = new HashMap<>(); data.put("name", "name" + i); data.put("age", "age" + i); data.put("sex", "sex" + i); datas.add(data); } // 插入行 sheet.shiftRows(4, 4 + datas.size(), datas.size(), true, false);// 第1个参数是指要开始插入的行，第2个参数是结尾行数,第三个参数表示动态添加的行数 for (int i = 0; i < datas.size(); i++) { XSSFRow creRow = sheet.createRow(4 + i); creRow.setRowStyle(sheet.getRow(4).getRowStyle()); creRow.createCell(0).setCellValue(datas.get(i).get("name").toString()); creRow.createCell(1).setCellValue(datas.get(i).get("age").toString()); creRow.createCell(2).setCellValue(datas.get(i).get("sex").toString()); } // 输出为一个新的Excel，也就是动态修改完之后的excel String fileName = "test" + System.currentTimeMillis() + ".xlsx"; OutputStream out = new FileOutputStream("G:" + "/" + fileName); workBook.removeSheetAt(0); // 移除workbook中的模板sheet workBook.write(out); fis.close(); out.flush(); out.close(); } /** * 替换单元格的内容，单元格的获取位置是合并单元格之前的位置，也就是下标都是合并之前的下表 * * @param cell * 单元格 * @param value * 需要设置的值 */ public static void replaceCellValue(Cell cell, Object value) { String val = value != null ? String.valueOf(value) : ""; cell.setCellValue(val); } }
结果:
上面需要注意的是:在替换的时候获取cell的时候获取的是合并单元格之前的cell位置，在动态增加行的时候行的其实和结束都是包含在内的。
2. 封装的一个完整的工具类:
此工具类支持xls和xlsx格式(这也是一种常用的思想，用父类引用接受子类对象)，完美的支持excel的操作。而且是单个sheet的模板替换以及追加内容、读取和设置单个cell的值。
代码中依赖的工具包:Slf4j日志包，IOUtils工具包，commons-collections操作集合包。
package cn.xm.exam.utils; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.List; import java.util.Map; import org.apache.commons.collections.MapUtils; import org.apache.commons.io.IOUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.streaming.SXSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; public class DynamicOperateExcelUtils { private static final Logger LOGGER = LoggerFactory.getLogger(DynamicOperateExcelUtils.class); private Workbook workBook; private Sheet sheet; public DynamicOperateExcelUtils(String fileFullPath) { this(fileFullPath, null); } public DynamicOperateExcelUtils(String fileFullPath, String sheetName) { // 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx try { workBook = new XSSFWorkbook(new FileInputStream(fileFullPath)); } catch (Exception e) { try { workBook = new HSSFWorkbook(new FileInputStream(fileFullPath)); } catch (Exception e1) { LOGGER.error("Excel格式不正确", e1); throw new RuntimeException(e1); } } // 进行模板的克隆(接下来的操作都是针对克隆后的sheet) sheet = workBook.cloneSheet(0); // 移除workbook中的模板sheet workBook.removeSheetAt(0); // 重命名克隆后的sheet workBook.setSheetName(0, sheetName != null ? sheetName : "sheet1"); } public String getCellValue(int rowNum, int colNum) { return getCellValue(rowNum, colNum, ""); } /** * 根据行号列号获取值 * * @param rowNum * 行号 * @param colNum * 列号 * @param defaultValue * 默认值 * @return */ public String getCellValue(int rowNum, int colNum, String defaultValue) { Row row = sheet.getRow(rowNum); if (row == null) { return defaultValue; } Cell cell = row.getCell(colNum); if (cell == null) { return defaultValue; } return getCellValue(cell, defaultValue); } public String getCellValue(Cell cell) { return getCellValue(cell, ""); } /** * 读取cell的值 * * @param cell * 需要读取的cell * @param defaultValue * 默认值 * @return */ public String getCellValue(Cell cell, String defaultValue) { if (cell != null) { cell.setCellType(cell.CELL_TYPE_STRING); return cell.getStringCellValue(); } return defaultValue; } /** * 替换单元格的内容，单元格的获取位置是合并单元格之前的位置，也就是下标都是合并之前的下表 * * @param cell * 单元格 * @param value * 需要设置的值 */ public void replaceCellValue(Cell cell, Object value) { String val = value != null ? String.valueOf(value) : ""; cell.setCellValue(val); } /** * 根据行号，列号进行替换 * * @param rowNum * 行号 * @param colNum * 列号 * @param value * 值 */ public void replaceCellValue(int rowNum, int colNum, Object value) { Row row = sheet.getRow(rowNum); if (row == null) { return; } Cell cell = row.getCell(colNum); if (cell == null) { return; } replaceCellValue(cell, value); } /** * 向sheet中添加行，后面的行会向后自动移动 * * @param startRowIndex * 起始行 * @param datas * 数据 * @param keys * 数据中Map对应的key */ public void appendRows(int startRowIndex, List<Map<String, Object>> datas, String[] keys) { // 插入行 sheet.shiftRows(startRowIndex, startRowIndex + datas.size(), datas.size(), true, false);// 第1个参数是指要开始插入的行，第2个参数是结尾行数,第三个参数表示动态添加的行数 // 向插入的行中动态的填充数据 for (int i = 0; i < datas.size(); i++) { Map<String, Object> data = datas.get(i); // 创建行 Row row = sheet.createRow(startRowIndex + i); // 添加单元格 Cell cell = null; for (int j = 0, length_2 = keys.length; j < length_2; j++) { String key = keys[j]; String value = MapUtils.getString(data, key, ""); cell = row.createCell(j); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(value); } } // 调整列宽 autoResizeColumn(keys.length); } public void exportExcel(File file) { exportExcel(file.getAbsolutePath()); } public void exportExcel(String fileFullPath) { OutputStream outputStream = null; try { outputStream = new FileOutputStream(fileFullPath); workBook.write(outputStream); } catch (IOException e) { LOGGER.error(" exportExcel error", e); } finally { IOUtils.closeQuietly(outputStream); } } private void autoResizeColumn(int colNumber) { // 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次 if (sheet instanceof SXSSFSheet) { SXSSFSheet tmpSheet = (SXSSFSheet) sheet; tmpSheet.trackAllColumnsForAutoSizing(); } for (int i = 0; i < colNumber; i++) { sheet.autoSizeColumn(i, true); } } public Sheet getSheet() { return sheet; } }
测试:
原来excel: myExcel.xlsx
代码:
public static void main(String[] args) throws IOException { DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils("F:/myExcel.xlsx"); // 读取内容 String cellValue = dynamicOperateExcelUtils.getCellValue(1, 1); System.out.println(cellValue); // 替换单元格内容(注意获取的cell的下标是合并之前的下标) dynamicOperateExcelUtils.replaceCellValue(1, 1, "updated"); // 动态插入数据-增加行 List<Map<String, Object>> datas = new ArrayList<>(); for (int i = 0; i < 5; i++) { Map data = new HashMap<>(); data.put("name", "name" + i); data.put("age", "age" + i); data.put("sex", "sex" + i); datas.add(data); } dynamicOperateExcelUtils.appendRows(4, datas, new String[] { "name", "age", "sex" }); dynamicOperateExcelUtils.exportExcel(new File("F:/myExcel2.xlsx")); }
结果:
什么鬼
myExcel2.xlsx
补充:有的POI版本如果第index行没有任何数据，直接getRow(index)的时候会报错，所以有可能需要先创建行。(这个问题对不同的POI版本情况不一样)
补充:有时候遇到合并的单元格采用上面自动调整列宽不生效，解决办法:
sheet.autoSizeColumn(i, true);