POI实现Excel读取和导出的工具类:
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 java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtils {
//读取Excel表格存成List<List<String>>的数据结构
public static List<List<String>> readXlsxByStream(InputStream inputStream)
throws IOException {
List<List<String>> resultList = new ArrayList<>();
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);// 获取当前页
if (xssfSheet != null) {
// 处理当前页,并处理每一行
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);// 获取行数据
int minColIX = xssfRow.getFirstCellNum();// 第一行数据
int maxColIX = xssfRow.getLastCellNum();// 总行数
List<String> rowList = new ArrayList<>();
// 遍历该行,处理该行数据
for (int colIX = minColIX; colIX < maxColIX; colIX++) {
XSSFCell cell = xssfRow.getCell(colIX);// 获取单元格
if (cell != null) {
rowList.add(cell.toString());
}
}
resultList.add(rowList);
}
}
return resultList;
}
//把List<List<String>>输出成一个Excel表格
public static XSSFWorkbook createExcel(List<List<String>> content){
// 创建HSSFWorkbook,一个HSSFWorkbook对应一个Excel文件
XSSFWorkbook wb = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet("Sheet1");
// 设置表头,即每个列的列名
List<String> tableHead = content.get(0);
content.remove(0);
// 创建第一行
XSSFRow row = sheet.createRow(0);
// 将列名写入
for (int i = 0; i < tableHead.size(); i++) {
// 给列写入数据,创建单元格,写入数据
row.createCell(i).setCellValue(tableHead.get(i));
}
// 写入正式数据
for (int i = 0; i < content.size(); i++) {
// 创建行
row = sheet.createRow(i+1);
for (int j=0;j<tableHead.size();j++){
row.createCell(j).setCellValue(content.get(i).get(j));
}
}
return wb;
}
}
需要导入poi的包:
<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>