POI和EasyExcel实现Excel数据批量读取到数据库

POI和easyExcel讲解

目的：这一部分我主要是为了实现excel数据批量插入到数据库。
参考：狂神说java的上课笔记 视屏教学网址

常用信息

1，将用户信息导出为excel表格

2，讲Excel表中的信息录入到网站数据库，大大减小网站数据的录入量！

开发中经常会设计到excel的处理，如导出Excel到数据库中！

操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel

Apache POI

Apache POI官网：https://poi.apache.org/

easyExcel

easyExcel官网地址

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节约内存著称。

EasyExcel能大量减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从一个磁盘上一行行读取数据，逐个解析。

下图是EasyExcel和POI在解析Excel时的对比图(时间与空间的相互取舍)。

官网文档

为了大家快速使用，先讲解EasyExcel

EasyExcel操作

写入操作：

1,导入依赖

<!--导入依赖-->
<dependencies>
	<!--easyexcel-->
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>easyexcel</artifactId>
		<version >2.2.0-beta2</version>
	</dependency>
    <!--fastjson-->
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>fastjson</artifactId>
		<version >1.2.62</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>
<dependencies>

2,实体类

@Data
public class DemoData {
	@ExcelProperty("字符串标题")
	private String string;
	@ExcelProperty("日期标题")
	private Date date;
	@ExcelProperty("数字标题")
	private Double doubleData;
	//忽略这个字段
	@ExcelIgnore
	private String ignore;
}

3,测试类

public class TestWrite{
    //模拟写入数据
    pivate 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;
	}
    //根据ist写 excel
    @Test
	public void simplewrite(){
		String fileName = PATH +EasyTest. xlsx"；
		//这里需要指定写用哪个 class去写，然后写到第一个 sheet，名字为模板然后文件流会自动关闭
		//write(fileName,格式类)
        //sheet（表名）
        //doWrite（数据）
        EasyExcel.write(fileName，DemoData.class).sheet("模板").doWrite(data());
        
    }
}

读取操作：

excel示例

1,对象

@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

2,监听器

// 有个很重要的点 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>();
    
    //为了Spring注入，我们这里自己new DemoDAO();
    private DemoDAO demoDAO;
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    
    //核心方法，读取数据会执行invoke方法
    //DemoData 类型
    //AnalysisContext 分析上下文
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
       System.out.println(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();
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        demoDAO.save(list);
    }
}

3，持久层

// 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

4,测试类

 @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        String fileName = "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

固定套路：

1，写入，固定类格式进行写入

2，读取，根据监听器设置的规则进行读取！

POI-Excel操作

POI-Excel写

创建项目

1，创建一个普通的maven项目

2，引入pom依赖

<!--导入依赖-->
<dependencies>
	<!--xLs(03)-->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version >3.9</version>
	</dependency>
	<!--xLsx(07)-->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxm1</artifactId>
		<version>3.9</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>
<dependencies>

03|07版本的写，就是对象不同，方法是一样的

区别：03版最多65536行，07行数没有限制

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hm02jrQU-1588496283092)(C:\Users\duank\Desktop\Java学习路线\狂神说java\99,实用技能\Apache POI，EasyExcel\images\05.png)]

1，工作簿

2，工作表

3，行

4，单元格

03版

//设置输出路径
String PATH = "D:\\test";
public void writeExcel03() throws Exception{
    //1,创建一个工作簿
    Workbook workbook = new HSSFWorkbook();
    //2,创建一个工作表
    Sheet sheet = workbook.createSheet("java表");
    //3，创建一行（1，1）
    Row row1 = sheet.createRow(0);
    //4，创建一个单元格
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("这个坐标（1，1）");
    //（1，2）
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("这个坐标（1，2）");
    
    //第二行（2，1）
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("这个坐标（2，1）");
    //（1，2）
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);
    
    //生成一张表（IO流）03版本使用xls结尾！
    FileOutputStream fos = new FileOutputStream(PATH+"03版本Excel测试.xls");
    //输出
    workbook.write(fos);
    //关闭流
    fos.close();
    
    System.out.println("生成成功！");
}

07版(和03不同的就这两处)

 Workbook workbook = new XSSFWorkbook();

//生成一张表（IO流）07版本使用xlsx结尾！
FileOutputStream fos = new FileOutputStream(PATH+"07版本Excel测试.xlsx");

大文件写HSSF

缺点：最多只能处理65536行，否则会报异常

java.lang.IllegalArgumentException:Invalid row number (65536) outside allowable range (0.. 65535)

优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

//设置输出路径
String PATH = "D:\\test";
public void testwrite03BigData() throws IOException {
	//时间
	long begin = System.currentTimeMillis();
	//创建一个薄
	Workbook workbook = new HSSFWorkbook();
	//创建表
	Sheet sheet = workbook.createSheet();
	//写入数据
	for (int rowNum =0; rowNum < 65537: rowNum++){
        Row row = sheet.createRow(rowNum);
		for (int cellNum = 0; cellNum < 10; cellNum++){
			Cell cell = row.createCell(cellNum);
			cell.setCellValue(cellNum);
    	}
    }
	Fileoutputstream fos = new Fileoutputstream(PATH +"03版本Excel大量数据测试.xls");
	workbook. write(fos);
	fos.close();
	long end = System.currentTimeMiLlis();
	System. out. printin((double)(end-begin )/1000;
}                         

大文件写XSSF

缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条数据

优点：可以写较大的数据量，如20万条数据

Workbook workbook = new XSSFWorkbook();
Fileoutputstream ops = new Fileoutputstream(PATH +"07版本Excel大量数据测试.xlsx");

大文件写SXSSF

优点：可以写非常大量的数据库，如100万条甚至更多条，写数据速度快，占用更少的内存

注意：

过程中会产生临时文件，需要清理临时文件

默认由100条记录被保存在内存中，如果超出这数量，则最前面的数据被写入临时文件

如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）

Workbook workbook = new SXSSFWorkbook();
Fileoutputstream ops = new Fileoutputstream(PATH +"07版本Excel大量数据测试.xlsx");

SXSSWorkbook 来自官方解释：实现：BigGridDemo策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释……仍然只储存在内存中，因此如果广泛使用，可能需要大量内存。

POI-Excel读

03|07

03版本

String PATH = "D:\\test";
public void testReado7() throws Exception {
    //获取文件流
	FileInputStream fis = new FileInputStream(PATH +"03版本Excel测试.xls");
	//1、创建一个工作簿。使用 exceL能操作的这边他都可以操作！
	Workbook workbook = new HSSFWorkbook(fis);
	//2、得到表
	Sheet sheet = workbook.getsheetAt(0);
	//3、得到行
	Row row = sheet.getRow（0）;
	//4、得到列
	Cell cell = row.getCell(1);
    
    //读取值的时候，一定要注意类型！
    //getStringCellValue 字符串类型
    System.out.println(cell.getNumericCellValue());
    inputStream.close();
}

07版本

	FileInputStream fis = new FileInputStream(PATH +"07版本Excel测试.xlsx");
	//1、创建一个工作簿。使用 exceL能操作的这边他都可以操作！
	Workbook workbook = new XSSFWorkbook(fis);

注意获取值的类型即可

读取不同的数据类型

//获取文件流
FileInputStream fis = new FileInputStream("明细表.xls");
//调用方法
testCellType(fis);

//编写工具类
public void testCellType(FileInputStream fis)throws Exception{

	//创建一个工作簿。使用 excel能操作的这边他都可以操作
	Workbook workbook = new HSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
    
    //获取标题内容
	Row rowTitle = sheet.getRow(0);
	if (rowTitlel =null){
    	//得到一行有多少列有数据
		int cellCount = rowTitle.getPhysicalNumberofCells();
        for (int cellNum = 0; cellNum<cellCount; cellNum++)(
			Cell cell = rowTitle.getCell(cellNum);
			if(ce11!=nu11){
				int cellType = cell.getcellType();
                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(ce11 != nu11){
				int cellType = cell.getCellType();
				String cellValue = "";
                
                switch (cellType){
					case HSSFCell.CELL_TYPE_STRING://字符
						System.out.print("【 String】");
						cellValue = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN://布尔
						System.out.print("【 BOOLEAN】");
						cellValue = String.valueOf(cell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_BLANK://空
						System.out.print("【 BLANK】");
						break;
					case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
						System.out.print("【 NUMERIC】");
						if(HSSFDateUtil.isCellDateFormatted(cell)){// 日期
							System.out.print("--【日期】");
							Date date = cell.getDateCellValue();
							cellValue = new DateTime(date).toString("yyyy-MM-dd");
						}else{
                            //不是日期格式，防止数字过长！
							System.out.print("--【转换为字符串输出】");
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cellValue = cell.toString();
                        }
                        break;
                    case HSSFCell.CELL_TYPE_ERROR://错误
						System.out.print("【 数据类型错误】");
						break;
                }
                System.out.println(cellValue);
            }
        }
    }  
}
//关闭流            
fis.close();  

如果Excel是07版，将HSSFWorkbook改成XSSFWorkbook即可

计算公式

public void testFormula() throws Exception{
	
    FileInputStream fis = new FileInputStream("公式表.xls");
	//创建一个工作簿。使用 excel能操作的这边他都可以操作
	Workbook workbook = new HSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
    
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);
    
    //拿到计算公司 evaL
	FormulaEvaluator fFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);

	//输出单元格的内容
	int cellType = cell.getCellType();
	switch (cellType){
		case Cell.CELL_TYPE_FORMULA://公式
			String formula = cell.getCellFormula();
            System.out.println(formula);
            
        	//计算
			Cellvalue evaluate = FormulaEvaluator.evaluate(cell);
			String cellValue = evaluate.formatAsstring();
			System.out.println(cellValue);
        	break;
    }
}

w row = sheet.getRow(4);
Cell cell = row.getCell(0);

//拿到计算公司 evaL
FormulaEvaluator fFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);

//输出单元格的内容
int cellType = cell.getCellType();
switch (cellType){
	case Cell.CELL_TYPE_FORMULA://公式
		String formula = cell.getCellFormula();
        System.out.println(formula);
        
    	//计算
		Cellvalue evaluate = FormulaEvaluator.evaluate(cell);
		String cellValue = evaluate.formatAsstring();
		System.out.println(cellValue);
    	break;
}

}