POI及EasyExcel导出导入
POI基本读写操作
导入依赖
<!-- xls(03) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
两种版本的写入和读取
```java
//通过POI来操作excel
public class PoiTest {
//excel文件路径
static String path = "C:\\Users\\lenovo\\Desktop\\idea_workspace\\springboot_poi\\";
public static void main(String[] args) throws IOException {
//03,写入数据
write_03();
//07,写入数据
write_07();
//03,获取数据
read_03();
//07,获取数据
read_07();
}
//03版本 ,向excel中写入数据
public static void write_03() throws IOException {
//1.创建工作簿
Workbook workbook = new HSSFWorkbook();
//2.创建表名
Sheet sheet = workbook.createSheet("03版_第一张表");
//3.创建行
Row row0 = sheet.createRow(0);
//4.创建单元格
Cell cell = row0.createCell(0);
//5.写入数据
cell.setCellValue("这是第一行一列的格子");
//6.创建流用于输出
FileOutputStream fileOutputStream = new FileOutputStream(path + "03版本表.xls");
//7.输出
workbook.write(fileOutputStream);
System.out.println("03版本表已经生成");
}
//07版本 ,向excel中写入数据
public static void write_07() throws IOException {
//1.创建工作簿
Workbook workbook = new XSSFWorkbook();
//2.创建表名
Sheet sheet = workbook.createSheet("07版_第一张表");
//3.创建行
Row row0 = sheet.createRow(0);
//4.创建单元格
Cell cell = row0.createCell(0);
//5.写入数据
cell.setCellValue("这是第一行一列的格子");
//6.创建流用于输出
FileOutputStream fileOutputStream = new FileOutputStream(path + "07版本表.xlsx");
//7.输出
workbook.write(fileOutputStream);
System.out.println("07版本表已经生成");
}
//03版本 ,读excel中的数据
public static void read_03() throws IOException {
//1.创建流用于读取
FileInputStream fileInputStream = new FileInputStream(path + "03版本表.xls");
//2.工作簿用于接收
Workbook workbook = new HSSFWorkbook(fileInputStream);
//3.获取表
Sheet sheet = workbook.getSheetAt(0);
//4.获取行
Row row = sheet.getRow(0);
//5.获取单元格
Cell cell = row.getCell(0);
//6.获取单元格中的数据,并输出
System.out.println(cell.getStringCellValue());
System.out.println("获取到03版本中的数据");
}
//07版本 ,读excel中的数据
public static void read_07() throws IOException {
//1.创建流用于读取
FileInputStream fileInputStream = new FileInputStream(path + "07版本表.xlsx");
//2.工作簿用于接收
Workbook workbook = new XSSFWorkbook(fileInputStream);
//3.获取表
Sheet sheet = workbook.getSheetAt(0);
//4.获取行
Row row = sheet.getRow(0);
//5.获取单元格
Cell cell = row.getCell(0);
//6.获取单元格中的数据,并输出
System.out.println(cell.getStringCellValue());
System.out.println("获取到07版本中的数据");
}
}
大数据读写
- 03版本:过程中读写入缓存,不操作磁盘,最后一次读写入磁盘,速度快,但最多只能处理65536行
- 07版本:大量数据,速度慢,耗内存,可能发生内存溢出
@Test
void contextLoads() throws Exception {
//创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个工作表
Sheet sheet = workbook.createSheet("第一个工作表");
long begin = System.currentTimeMillis();
for (int rowNum = 0; rowNum < 65536; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
FileOutputStream fileOutputStream = new FileOutputStream(path + "excel03.xlsx");
workbook.write(fileOutputStream);
long end = System.currentTimeMillis();
System.out.println("生成完毕,所需时间为:" + (double) (end - begin) / 1000);
fileOutputStream.close();
}
读取不同的数据类型
@Test
void testRead() throws Exception {
//获取文件流
FileInputStream fileInputStream = new FileInputStream(path + "风云歌手排行榜07.xlsx");
//获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
//获取工作表
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
if (row != null){
int cellCount = row.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum <cellCount ; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue + "|");
}
}
System.out.println();
}
//获取表中的内容
//获取行数
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if(rowData != null){
//获取该行的列的数目
int cellCount = row.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("["+(rowNum+1)+","+(cellNum+1)+"]:");
Cell cell = rowData.getCell(cellNum);
//判断列的数据类型
if (cell!=null){
CellType cellType = cell.getCellType();
switch (cellType){
case STRING:
System.out.println("字符串:" + cell.getStringCellValue()); break;
case BOOLEAN:
System.out.println("布尔:" + cell.getBooleanCellValue()); break;
case BLANK:
System.out.println("空"); break;
case ERROR:
System.out.println("没有该数据类型"); break;
case NUMERIC:
//判断是否是日期?如果是日期就直接输出;否则就转换为String,然后输出
if(DateUtil.isCellDateFormatted(cell)){
System.out.print("日期:" );
Date date = cell.getDateCellValue();
System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date));
//System.out.println(new DateTime(date).toString("yyyy-MM-dd HH:mm:ss"));
}else{
System.out.print("转换为字符串输出:" );
cell.setCellType(STRING);
System.out.println(cell.toString());
}
break;
case FORMULA:
String formula = cell.getCellFormula();
System.out.println("公式:" + formula); break;
default:
System.out.println("该位置没有数据");break;
}
}
}
}
}
fileInputStream.close();
}
}
计算公式
@Test
public void formulaTest() throws IOException {
FileInputStream fileInputStream = new FileInputStream(path + "1.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
//拿到工作簿的计算公式
FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//输出单元格的内容
CellType cellType = cell.getCellType();
switch (cellType){
case FORMULA:
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
//计算
CellValue cellValue = formulaEvaluator.evaluate(cell);
System.out.println(cellValue.formatAsString());
break;
}
}
}
EasyExcel操作
导入依赖
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
通用数据生成
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
最简单的读:根据监听器规则读取
// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data
* one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(DemoData data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
demoDAO.save(list);
LOGGER.info("存储数据库成功!");
}
}
最简单的写:固定格式类写入
/**
* 最简单的写
* <p>1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>2. 直接写即可
*/
@Test
public void simpleWrite() {
// 写法1
String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个格式类class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
// 写法2
fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 这里 需要指定写用哪个class去写
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
excelWriter.write(data(), writeSheet);
} finally {
// 千万别忘记finish 会帮忙关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}