以下的main函數，先生成一個excel檔案，并設定sheet的名稱，設定excel頭；而後，以分頁的方式，向檔案中追加資料
maven依賴
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency> 代碼示例
package com.**.**.**.common.utils;
import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.util.CellRangeAddress;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
/**
* @author zyydd
* @date 2019/3/15 15:00
*/
public class ExcelUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 以下測試方法，先生成一個excel檔案，并設定sheet的名稱，設定excel頭
* 之後，以分頁的方式，向檔案中增加資料
*
* @param args
*/
public static void main(String[] args) throws IOException {
String fileAbsolutePath = "D:\\test.xlsx";
Map<String, List<DataForExcel>> dataMap = initTestDataHead();
ExcelUtils.generateExcelWithManySheets(fileAbsolutePath, dataMap);
for (int i = 0; i < 3; i++) {
List<String[]> testData = new ArrayList<>();
for (int k = 1; k < 11; k++) {
String[] oneRow = new String[6];
oneRow[0] = (i * 10 + k) + "";
oneRow[1] = "張三" + oneRow[0];
oneRow[2] = "男";
oneRow[3] = "北京市朝陽區";
oneRow[4] = "北京市大興區";
oneRow[5] = (System.currentTimeMillis() % 10000000000L) + "";
testData.add(oneRow);
}
ExcelUtils.addExcel(fileAbsolutePath, 0, testData);
}
}
private static Map<String, List<DataForExcel>> initTestDataHead() {
Map<String, List<DataForExcel>> dataMap = new HashMap<String, List<DataForExcel>>();
List<DataForExcel> dataForExcelList1 = new ArrayList<DataForExcel>();
dataForExcelList1.add(new DataForExcel(0, 0, "序号"));
dataForExcelList1.add(new DataForExcel(0, 1, "姓名"));
dataForExcelList1.add(new DataForExcel(0, 2, "性别"));
dataForExcelList1.add(new DataForExcel(0, 3, "家庭住址"));
dataForExcelList1.add(new DataForExcel(0, 4, "通信位址"));
dataForExcelList1.add(new DataForExcel(0, 5, "手機号"));
dataMap.put("人員明細", dataForExcelList1);
return dataMap;
}
/**
* 傳入資料，在指定路徑下生成Excel檔案 支援生成多個sheet，并為sheet命名
*
* @param absolutePath 生成檔案的絕對路徑，例如"C:\\Users\\...\\out.xlsx"
* @param dataForExcelMap key：sheet名； value：傳入的資料 名字相同時會覆寫之前的檔案
* @return
*/
public static boolean generateExcelWithManySheets(String absolutePath, Map<String, List<DataForExcel>> dataForExcelMap) {
boolean flag = false;
try {
XSSFWorkbook workbook = new XSSFWorkbook();
for (Map.Entry<String, List<DataForExcel>> entry : dataForExcelMap.entrySet()) {
XSSFSheet sheet = workbook.createSheet(entry.getKey());
List<DataForExcel> dataForExcel = entry.getValue();
Collections.sort(dataForExcel, (arg0, arg1) -> arg0.getRow().compareTo(arg1.getRow()));
XSSFRow nrow = null;
for (DataForExcel data : dataForExcel) {
if (dataForExcel.indexOf(data) == 0 || !data.getRow().equals(dataForExcel.get(dataForExcel.indexOf(data) - 1).getRow())) {
nrow = sheet.createRow(data.getRow());
}
XSSFCell ncell = nrow.createCell(data.getColumn());
ncell.setCellValue(data.getValue());
}
}
File file = new File(absolutePath);
file.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(file);
workbook.write(stream);
stream.close();
flag = true;
} catch (IOException ie) {
LOGGER.error(ie.getMessage());
} catch (Exception e) {
LOGGER.error(e.getMessage());
}
return flag;
}
/**
* 向已存在的excel中追加資料
*
* @param absolutePath 已存在的excel絕對路徑
* @param sheetIndex sheet的序号，從0開始
* @param dataList cell資料
* @return
* @throws IOException
*/
public static Boolean addExcel(String absolutePath, int sheetIndex, List<String[]> dataList) throws IOException {
int columnsNum = dataList.get(0).length;
FileInputStream fs = new FileInputStream(absolutePath);
XSSFWorkbook wb = new XSSFWorkbook(fs);
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
XSSFRow row;
int lastRowNum = sheet.getLastRowNum();
FileOutputStream out = new FileOutputStream(absolutePath);
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(++lastRowNum);
String[] addOneRowData = dataList.get(i);
for (int j = 0; j < addOneRowData.length; j++) {
String str = addOneRowData[j];
row.createCell(j).setCellValue(str);
}
}
setSheetStyle(sheet, columnsNum - 1);
wb.write(out);
out.flush();
out.close();
return true;
}
private static XSSFSheet setSheetStyle(XSSFSheet sheet, int columnsNum) {
sheet.createFreezePane(0, 1, 0, 1);
String columnRange = "A1:" + (char) (65 + columnsNum) + "1";
sheet.setAutoFilter(CellRangeAddress.valueOf(columnRange));
for (int i = 0; i <= columnsNum; i++) {
sheet.autoSizeColumn(i);
}
return sheet;
}
} package com.**.**.**.common.utils;
/**
* @author zyydd
* @date 2019/3/15 15:00
*/
public class DataForExcel {
/**
* excel的行号 從0開始 例如excel一個表格行号為0，列号也為0
*/
private Integer row;
/**
* excel的列号 從0開始 例如excel一個表格行号為0，列号也為0
*/
private Integer column;
/**
* 插入的值
*/
private String value;
public DataForExcel() {
}
public DataForExcel(Integer row, Integer column, String value) {
this.row = row;
this.column = column;
this.value = value;
}
public Integer getRow() {
return row;
}
public void setRow(Integer row) {
this.row = row;
}
public Integer getColumn() {
return column;
}
public void setColumn(Integer column) {
this.column = column;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
} 執行截圖