文章目录
- 写在前面
- POI
- 导入依赖
- 写入
- 读取
- 计算公式
- EasyExcel
- 导入依赖
- 写入
- 读取
- 实战
写在前面
在开发中经常会涉及到excel的处理,比如导出用户信息为excel表格、打印报表、月销售情况、成绩单等等(导出数据),还有将excel的信息录入到网站数据库等(导入数据),我们不可能手动操作,所以介绍Apache的POI和阿里巴巴的EasyExcel。
POI
Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 官网https://poi.apache.org/ xls和xlsx 首先注意下xls(2003版)和xlsx(2007版)的区别,都是excel的格式,后缀名不同。关键xls只有65536行,而xlsx没有行数限制,后续都以xlsx为例,如果要用xls格式,操作都是类似的,导入对应依赖,修改后缀名和注意数据溢出(65536)。 对象说明 java是面向对象编程,那么excel的操作也是对应到具体对象的,即工作簿,工作表,行和列。
其中需要注意的是工作簿(Workbook)接口下实现类的区别,HSSF是03版的,XSSF是07版,SXSSF是07优化(提升速度)
其余如word等导入依赖照葫芦画瓢
导入依赖
不会Maven参考这里
<!--xls(03)-->
<!--<dependency>-->
<!--<groupId>org.apache.poi</groupId>-->
<!--<artifactId>poi</artifactId>-->
<!--<version>3.17</version>-->
<!--</dependency>-->
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
写入
package com.wzl;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class Poi {
//当前项目路径
static String PATH="C:\\Users\\Administrator\\IdeaProjects\\excel\\";
@Test
public void testWrite07() throws IOException {
//创建一个工作簿
Workbook workbook=new XSSFWorkbook();
//创建一个工作表
Sheet sheet=workbook.createSheet();
//创建一行
Row row1=sheet.createRow(0);//第一行,下标从0开始
//创建一列,行和列构成一个单元格cell
Cell cell11=row1.createCell(0);//(1,1)
//写入
cell11.setCellValue("单元格(1,1)");
Row row2=sheet.createRow(1);
Cell cell25=row2.createCell(4);//(2,5)
cell25.setCellValue("测试");
//保存(IO流)
FileOutputStream fileOutputStream=new FileOutputStream(PATH+"poi写入测试.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();//关闭流
System.out.println("finished");
}
}
读取
@Test
public void testRead07() throws IOException {
//获取文件流
FileInputStream fileInputStream=new FileInputStream(PATH+"poi写入测试.xlsx");
//读取工作簿
Workbook workbook=new XSSFWorkbook(fileInputStream);
fileInputStream.close();//关闭文件流
//get第一个工作表
Sheet sheet=workbook.getSheetAt(0);
//get第一行
Row row1=sheet.getRow(0);
//get第一列
Cell cell11=row1.getCell(0);
//进行写入数据库等操作,这里直接输出
System.out.println(cell11.getStringCellValue());
//获取有多少行
int rowCnt=sheet.getPhysicalNumberOfRows();
int colCnt=0; //列数
if(row1!=null) //通过第一行获取有多少列
colCnt=row1.getPhysicalNumberOfCells();
//遍历表格
// for(int i=0;i<rowCnt;i++)
// for(int j=0;j<colCnt;j++)
}
计算公式
excel还有很多公式,POI同样也可以处理。
@Test
public void testFormula() throws IOException{
FileInputStream fileInputStream=new FileInputStream(PATH+"poi写入测试.xlsx");
Workbook workbook=new XSSFWorkbook(fileInputStream);
fileInputStream.close();
Sheet sheet=workbook.getSheetAt(0);
Row row6=sheet.getRow(5);
Cell cell62=row6.getCell(1);//(6,2)
//输出单元格内容(计算公式)
System.out.println(cell62.getCellFormula());
//拿到计算公式 eval
FormulaEvaluator formulaEvaluator=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//计算
CellValue evaluate=formulaEvaluator.evaluate(cell62);
//输出计算结果
String cellValue=evaluate.formatAsString();
System.out.println(cellValue);
}
EasyExcel
EasyExcel是阿里巴巴开源的excel处理框架,因为没有一次全部加载进内存,是从磁盘上一行行解析,所以节省内存,同时操作十分简便,一行代码,可以根据实体类自动生成表。 官网https://github.com/alibaba/easyexcel 文档https://www.yuque.com/easyexcel/doc/easyexcel
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
EasyExcel有用到POI,,删掉前面的依赖,注意重复冲突。
写入
格式类
package com.wzl.easy;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
@Data
public class DemoData {
@ExcelProperty("自定义标题")
private String string;
@ExcelProperty("时间")
private Date date;
private Double doubleData;
@ExcelIgnore //忽略
private int ignore;
public void setString(String string) {
this.string = string;
}
public void setDate(Date date) {
this.date = date;
}
public void setDoubleData(Double doubleData) {
this.doubleData = doubleData;
}
}
写测试
package com.wzl.easy;
import com.alibaba.excel.EasyExcel;
import com.wzl.easy.DemoData;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Easy {
static String PATH="C:\\Users\\Administrator\\IdeaProjects\\excel\\";
//生成DemoData类随机数据
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 15; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
@Test
public void simpleWrite() {
String fileName = PATH+"EasyExcelWrite.xlsx";
// 文件名 格式类 表名 数据
EasyExcel.write(fileName, DemoData.class).sheet("工作表名").doWrite(data());
System.out.println("finished");
}
}
读取
Demo把持久层逻辑都写好了,这里直接演示输出(偷懒 )
持久层
package com.wzl.easy;
import java.util.List;
/**
* 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。
**/
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
}
}
监听器
package com.wzl.easy;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
public class DemoDataListener extends AnalysisEventListener<DemoData> {
private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
private static final int BATCH_COUNT = 5;
List<DemoData> list = new ArrayList<DemoData>();
private DemoDAO demoDAO;
public DemoDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
demoDAO = new DemoDAO();
}
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
@Override
public void invoke(DemoData data, AnalysisContext context) {
System.out.println(JSON.toJSONString(data));
//LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData(); //持久化逻辑
// 存储完成清理 list
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
demoDAO.save(list);
LOGGER.info("存储数据库成功!");
}
}
测试
@Test
public void simpleRead() {
String fileName = PATH+"EasyExcelWrite.xlsx";
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
System.out.println("finished");
}
更多其他操作可以查看文档,调用API即可,十分详细。
实战
数据来源是爬虫(因为我没有数据库和数据 ),Jsoup爬虫可参考我另一篇博客,用数据库也是一样的,怎么拿到数据都行,重点演示下POI。
以CSDN搜索java为例,爬取博客相关信息后存入excel。
@Test
public void jsoupPoiDemo()throws IOException{
//创建一个工作簿,使用07优化
Workbook workbook=new SXSSFWorkbook();
//创建一个工作表
Sheet sheet=workbook.createSheet();
//创建第一行(表头)
Row row1=sheet.createRow(0);
row1.createCell(0).setCellValue("标题");
row1.createCell(1).setCellValue("浏览量");
row1.createCell(2).setCellValue("作者");
row1.createCell(3).setCellValue("时间");
row1.createCell(4).setCellValue("地址");
int rowCnt=1;//记录行数
//获取数据
String url="https://so.csdn.net/so/search/s.do?q=java&p=";
for(int page=1;page<=20;page++){ //遍历20页
//解析网页
Document document=Jsoup.parse(new URL(url+Integer.toString(page)),60000);//60s超时
Elements elements=document.getElementsByClass("search-list J_search");
for (Element el:elements) { //遍历该页每组数据
//数据筛选
String title=el.getElementsByTag("a").eq(0).text();//标题
String countStr=el.getElementsByClass("mr16").html();//浏览量
if(countStr==null||"".equals(countStr))continue; //搜索结果含少量course,我们只要blog
int count=Integer.parseInt(countStr);
String writer=el.getElementsByTag("a").eq(1).text();//作者
String time=el.getElementsByClass("date").html();//时间
String hrefAll=el.getElementsByTag("a").eq(0).attr("href");//博客地址
String href=hrefAll.substring(0, hrefAll.indexOf("?"));//截取?之前,避免过长
//写入数据
Row row=sheet.createRow(rowCnt++); //另起一行
row.createCell(0).setCellValue(title);
row.createCell(1).setCellValue(count);
row.createCell(2).setCellValue(writer);
row.createCell(3).setCellValue(time);
row.createCell(4).setCellValue(href);
}
}
//保存(IO流)
FileOutputStream fileOutputStream=new FileOutputStream(PATH+"CSDN博客数据分析.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();//关闭流
System.out.println("finished");
}
原创不易,请勿转载(
本不富裕的访问量雪上加霜) 博主首页:https://blog.csdn.net/qq_45034708