依赖:
<dependencies>
<!-- 引入poi,解析workbook视图 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
</dependencies>```
代码
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class Test {
public static Boolean addExcel(String absolutePath, int sheetIndex, String data) throws IOException {
FileInputStream fs = new FileInputStream(absolutePath);
XSSFWorkbook wb = new XSSFWorkbook(fs);
XSSFSheet sheet = wb.getSheetAt(sheetIndex);
XSSFRow row;
FileOutputStream out = new FileOutputStream(absolutePath);
row = sheet.createRow(4);
row.createCell(4).setCellValue(data);
wb.write(out);
out.flush();
out.close();
return true;
}
public static void main(String[] args) throws IOException {
String path = "F:\\代码资料\\ExcelTest\\text.xlsx";
addExcel(path,0,"456456");
}
}
![请添加图片描述](https://i-blog.csdnimg.cn/blog_migrate/59016d8d20a639276f8e757b734fed5a.png)
附加:
1:先生成一个excel文件,并设置sheet的名称,设置excel头;而后,以分页的方式,向文件中追加数据
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.*;
public class ExcelUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
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;
}
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;
}
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;
public class DataForExcel {
private Integer row;
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;
}
}
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/41147088682671874e8f29e9323aeadc.png)