poi读取Excel模板并修改模板内容与动态的增加行

　　有时候我们可能遇到相当复杂的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> datas = new ArrayList<>(); for (int i = 0; i < 5; i++) {

Map data = new HashMap<>(); data.put(\"name\ data.put(\"age\ data.put(\"sex\ 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格式不正确\ 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> datas, String[] keys) { // 插⼊⾏

sheet.shiftRows(startRowIndex, startRowIndex + datas.size(), datas.size(), true, false);// 第1个参数是指要开始插⼊的⾏，第2个参数是结尾⾏数,第三个参数表⽰动态添加的⾏数 // 向插⼊的⾏中动态的填充数据 for (int i = 0; i < datas.size(); i++) {

Map 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\ } 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> datas = new ArrayList<>(); for (int i = 0; i < 5; i++) {

Map data = new HashMap<>(); data.put(\"name\ data.put(\"age\ data.put(\"sex\ datas.add(data); }

dynamicOperateExcelUtils.appendRows(4, datas, new String[] { \"name\ dynamicOperateExcelUtils.exportExcel(new File(\"F:/myExcel2.xlsx\")); }结果:什么⿁myExcel2.xlsx

补充:有的POI版本如果第index⾏没有任何数据，直接getRow(index)的时候会报错，所以有可能需要先创建⾏。(这个问题对不同的POI版本情况不⼀样)

补充:有时候遇到合并的单元格采⽤上⾯⾃动调整列宽不⽣效，解决办法:sheet.autoSizeColumn(i, true);