有時候我們可能遇到相當複雜的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);