1.使用场景
1.用户信息导出为excel
2.excel表的信息录入数据库
2.目前流行的是 aphache的POI 和阿里的EasyExcel(都耗内存)
1.POI有很多工具底层用它,大量被使用
2.POI有几种格式
HSSF 读写Excel3 最多65536行 .xls
XSSF 读写Excel7 没有限制 .xlsx
HWPF 读写Word格式
HSLF 读写PPT
HDGF 读写Visio
3.EasyExcel 不会内存溢出,POI会OOM
3.几个概念 POI
工作薄>工作表(可以切换)>行列>单元格
//03版本一定要.xls命名 ,07 以.xlsx结尾
4.大文件怎么写 03超过65536
//07读写慢可以先用 SXSSF类来读写(内存使用少,超过100个数据写入临时文件,需要自己清除)
//写
@Test
public void aa1(){
// 3版的 Workbook workbook = new HSSFWorkbook();
//7版的
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("jams2");
for (int i = 0; i < 65536; i++) {
Row row = sheet.createRow(i);
row.setHeight((short) 2000);
CellStyle rowStyle = row.getRowStyle();
Cell cell = row.createCell(0);
cell.setCellValue("今日新增观众");
Cell cell1 = row.createCell(1);
cell1.setCellValue(999);
cell1.setCellStyle(rowStyle);
Row row1 = sheet.createRow(1);
Cell cell2 = row1.createCell(0);
cell2.setCellValue("统计时间");
Cell cell3 = row1.createCell(1);
String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell3.setCellValue(time);
}
try {
// 3版的 FileOutputStream fileOutputStream = new FileOutputStream(PATH + "jams2.xls");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "jams2.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成表完毕");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//读 注意要获取不同的类型(最麻烦)和判空 不能只读取字符串,
//简单的读
@Test
public void aa2() throws IOException {
// FileInputStream inputStream = new FileInputStream(PATH+"jams2.xlsx");
FileInputStream inputStream = new FileInputStream(PATH+"jams1.xls");
// Workbook workbook = new XSSFWorkbook(inputStream);
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(cell.getRichStringCellValue());
inputStream.close();
}
//根据不同类型的读
//可得到 获取的类型
@Test
public void testTypeCell() throws Exception{
//获取文件流
InputStream is = new FileInputStream(PATH+"jams1.xls");
//创建一个工作簿,使用 excel能操作的这边都可以操作
Workbook workbook = new HSSFWorkbook(is);
//得到表
Sheet sheet = workbook.getSheetAt(0);
//读取标题内容
Row rowTitle = sheet.getRow(0);
if (rowTitle!=null){
//读取列,得到一行有多少列有数据
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell!=null){
String cellValue = cell.getStringCellValue();
System.out.print(cellValue+"|");
}
}
System.out.println();
}
//获取表中的内容
//获取表中有多少行有数据
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData!=null){//如果行不为空
//读取列
int cellCount=rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
if (cell!=null){
//判断单元格类型
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case HSSFCell.CELL_TYPE_STRING://字符串
cellValue=String.valueOf(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN://布尔
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK://空
break;
case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {// 不是日期格式,则防止当数字过长时以科学计数法显示
cell.setCellType(HSSFCell.CELL_TYPE_STRING);//转换为字符串输出
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_ERROR:
System.out.print("【数据类型错误】");
break;
}
System.out.print(cellValue+" ");
}
}
}
System.out.println();
}
is.close();
}
//运行结果
姓名|学号|
jams 110
jams1 120
5.计算公式(excel已经有的公式和指定的单元格) excel求和 =sum() 点击括号里面然后选取区域
//我会excel公式…既读又写, 使用sum函数对 签到次数进行统计然后生成结果
//测试公式
@Test
public void testFormular() throws Exception{
//获取文件流
InputStream is = new FileInputStream(PATH+"jams1.xls");
//创建一个工作簿,使用 excel能操作的这边都可以操作
Workbook workbook = new HSSFWorkbook(is);
//得到表
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(3);
int cellCount = row.getPhysicalNumberOfCells();
//放计算结果的列
Row rowRes = sheet.createRow(cellCount);
Cell cell = rowRes.createCell(2);
FormulaEvaluator fFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
System.out.println(cell.getNumericCellValue());
cell.setCellFormula("SUM(C2:C"+cellCount+")");
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_FORMULA://2
//得到公式
String formula = cell.getCellFormula();
System.out.println(formula);
//计算
CellValue evaluate = fFormulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
sout(cellValue);
break;
}
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "jams1.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("生成表完毕");
}
//结果
姓名 学号 签到次数
jams 110 1
jams1 120 2
3
6 //这个是sum函数执行的结果哦
6.EasyExcel(底层使用poi 所以要注释掉之前的poi依赖)
//依赖
<!--fastjson-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version >1.2.62</version>
</dependency>
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version >2.2.0-beta2</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version >1.18.12</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
//写文件,写一个实体类加上注释就可以使用了
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private double doubleNumber;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
//创建list,给easyExcel写入数据
String PATH="D:\\";
private List data(){
ArrayList<DemoData> arrayList = new ArrayList();
for (int i = 0; i < 100; i++) {
DemoData demoData = new DemoData();
demoData.setString(Integer.toString(i));
demoData.setDate(new Date());
// demoData.setADouble(3.14); 名字不要包含关键字,不然写入失败
demoData.setDoubleNumber(3.14);
arrayList.add(demoData);
}
return arrayList;
}
@Test
public void aa(){
//直接写文件名就可以生成 03(超出65535行也是会报异常) 或者07版的拉
String fileName=PATH+"EasyExcel.xls";
EasyExcel.write(fileName,DemoData.class).sheet("jams1").doWrite(data());
}
//读数据和写入到数据库,需要创建监听器和调用dao写入
@Test
public void read(){
//直接写文件名就可以生成 03(超出65535行也是会报异常) 或者07版的拉
String fileName=PATH+"EasyExcel.xlsx";
EasyExcel.read(fileName,DemoData.class,new DemoDataListener()).sheet("jams1").doRead();
}
//创建监听器类,可以批量插入数据库 避免OOM错误
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) {
System.out.println("ok");
System.out.println(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() {
demoDAO.save(list);
}
}
//模拟spring里面放的Dao类
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
System.out.println(list);
}
}