package util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import utils.FileUtil;
public class ExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 创建excel文件 如果excel文件存在并且有与之对应sheet则会在对应的sheet后面
* 追加数据否则新建一个文件
* @param filePath 文件路径
* @param dataList 数据列表
* @param sheetName excel sheet名称
*/
@SuppressWarnings("resource")
public static void createExcel(String filePath, List<String> dataList, String sheetName){
if (dataList == null || dataList.size() == 0){
LOGGER.error("=======dataList为空错误=====");
return;
}
LOGGER.info("此次写入sheetName=====" + sheetName);
//如果文件地址为空则返回
if (filePath == null || "".equals(filePath)){
LOGGER.error("=======filePath为空错误=====");
return;
}
String suffix = filePath.substring(filePath.lastIndexOf(".") +1 );
//强制规定excel的后缀
if (!"xlsx".equals(suffix)){
filePath += ".xlsx";
}
LOGGER.info("excel文件路径:" + filePath);
//第一步创建workbook
XSSFWorkbook wrokBook = null;
XSSFSheet sheet = null;
File file = new File(filePath);
try {
if (FileUtil.fileIsExists(file)){
LOGGER.info("excel文件存在直接读取文件");
wrokBook = new XSSFWorkbook(new FileInputStream(file));
sheet = wrokBook.getSheet(sheetName);
}else{
LOGGER.info("excel文件不存在创建文件");
file = FileUtil.createFile(file);
wrokBook = new XSSFWorkbook();
sheet = wrokBook.createSheet(sheetName);
}
} catch (Exception e) {
LOGGER.error(e.getMessage());
return;
}
//第二步创建sheet
if (sheet == null){
sheet = wrokBook.createSheet(sheetName);
}
//第三步创建行row:添加表头0行
int lastRowNumber = sheet.getLastRowNum();
//lastRowNumber ==0 有两种情况 1-只有一条数据 2-一条数据都没有
if(lastRowNumber == 0){
Row fristRow = sheet.getRow(0);
if (fristRow == null){
lastRowNumber --;
}
}
LOGGER.info("lastRowNumber ====== " + lastRowNumber);
LOGGER.info("此次插入excel共" + dataList.size() + "条数据");
//第四步插入数据
for (int i = 0; i < dataList.size(); i++) {
//创建行
Row row = sheet.createRow(lastRowNumber + i + 1);
//创建单元格并且添加数据
row.createCell(0).setCellValue(dataList.get(i));
}
//第五步将生成excel文件保存到指定路径下
try {
FileOutputStream fout = new FileOutputStream(filePath);
wrokBook.write(fout);
fout.close();
LOGGER.info("此次写入excel结束" );
} catch (IOException e) {
LOGGER.error(e.getMessage());
}
}
public static void main(String[] args) {
// System.out.println(Integer.MAX_VALUE);
List<String> dataList = new ArrayList<String>();
dataList.add("测试测试");
dataList.add("测试测试1");
createExcel("D://test//test1.xlsx", dataList, "hotel");
}
}
maven 引用
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>