POI和EasyExcel

使用场景

1. 用户信息导出为Excel表格（导出数据）
2. 将Excel表中的信息录入到网站数据库

Apache POI和阿里巴巴的EasyExcel！！！

Apache POI

操作Ecxel大概65535行数据。
消耗内存。(先加载到内存，后写入)

POI-Excel写

03(xls)

@Test
	public void test03() throws Exception {
		//1.创建一个工作簿
		Workbook workbook = new HSSFWorkbook();
		//2.创建一个工作表
		Sheet sheet = workbook.createSheet("test测试表");

		//(1,1)
		//3.创建行
		Row row1 = sheet.createRow(0);
		//4.创建列 (单元格)
		Cell cell1 = row1.createCell(0);
		cell1.setCellValue("测试");

		//(1,2)
		Cell cell12 = row1.createCell(1);
		cell12.setCellValue(1);

		//(2,1)
		Row row2 = sheet.createRow(1);
		Cell cell21 = row2.createCell(0);
		cell21.setCellValue("统计时间");

		//(2,2)
		Cell cell22 = row2.createCell(1);
		String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell22.setCellValue(time);

		//生成一张表 (IO流)
		FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试03.xls");

		workbook.write(fileOutputStream);

		//关闭流
		fileOutputStream.close();
		System.out.println("表格生成完毕");
	}

大文件写入HSSF

• 缺点：最多只能65536行，否则会抛出异常。
• 优点：过程中写入缓存，不操作磁盘，最后一次写入磁盘，速度快。

07(xlsx)

@Test
	public void test07() throws Exception {
		//1.创建一个工作簿
		Workbook workbook = new XSSFWorkbook();
		//2.创建一个工作表
		Sheet sheet = workbook.createSheet("test测试表");

		//(1,1)
		//3.创建行
		Row row1 = sheet.createRow(0);
		//4.创建列 (单元格)
		Cell cell1 = row1.createCell(0);
		cell1.setCellValue("测试");

		//(1,2)
		Cell cell12 = row1.createCell(1);
		cell12.setCellValue(1);

		//(2,1)
		Row row2 = sheet.createRow(1);
		Cell cell21 = row2.createCell(0);
		cell21.setCellValue("统计时间");

		//(2,2)
		Cell cell22 = row2.createCell(1);
		String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
		cell22.setCellValue(time);

		//生成一张表 (IO流)
		FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试07.xlsx");

		workbook.write(fileOutputStream);

		//关闭流
		fileOutputStream.close();
		System.out.println("表格生成完毕");
	}

大文件写XSSF

• 缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条。
• 优点：可以写较大数量，如20万条。

大文件写SXSSF

• 优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存。
• 注意：过程中会产生临时文件，需要清除临时文件((SXSSFWorkbook) workbook).dispose。默认是100条记录保存在内存中，如果超过，就会被写入临时文件中。如果想自定义内存数据量，可以使用new SXSSFWorkbook(数量)。但仍可能会消耗大量内存。

POI-Excel读

03

@Test
	public void testRead03() throws Exception{
		//获取文件流
		FileInputStream fileInputStream = new FileInputStream("C:\\Users\\OuTengTeng\\Desktop\\EasyExcel\\EasyExcel测试03.xls");
		//1.创建一个工作簿
		Workbook workbook = new HSSFWorkbook(fileInputStream);
		//2.get表
		Sheet sheet = workbook.getSheetAt(0);
		//3.get行
		Row row = sheet.getRow(0);
		//4.get列
		Cell cell = row.getCell(0);

		//读取String类型
		System.out.println(cell.getStringCellValue());

		//关闭流
		fileInputStream.close();
	}

07

换

读取不同类型的数据

@Test
	public void testCellType() throws Exception{
		//获取文件流
		FileInputStream fileInputStream = new FileInputStream(PATH + "\\EasyExcel测试03.xls");
		//创建工作簿
		Workbook workbook = new HSSFWorkbook(fileInputStream);
		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){
					//CellType cellType = cell.getCellType();
					String cellValue = cell.getStringCellValue();
					System.out.print(cellValue + "|");
				}
				
			}
		}

		//获取表中的内容
		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++) {
					System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
					Cell cell = rowData.getCell(cellNum);
					if (cell != null) {
						CellType cellType = cell.getCellType();
						//System.out.println(cellType);
						String cellValue = "";
						switch (cellType) {
							case STRING:
								System.out.print("【String】");
								cellValue = cell.getStringCellValue();
								break;
							case BOOLEAN:
								System.out.print("【Boolean】");
								cellValue = String.valueOf(cell.getBooleanCellValue());
								break;
							case _NONE:
								System.out.print("【Blank】");
								break;
							case NUMERIC:
								System.out.print("【number】");
								if (HSSFDateUtil.isCellDateFormatted(cell)) {
									System.out.print("【日期】");
									Date date = cell.getDateCellValue();
									String s = new DateTime(date).toString("yyyy-MM-dd");
								}else {
									System.out.print("转为字符串输出");
									cell.setCellType(CellType.STRING);
									cellValue = cell.toString();
								}
								break;
							case ERROR:
								System.out.print("类型错误");
								break;
						}
						System.out.println(cellValue);
					}
				}
			}
			
		}
		fileInputStream.close(); 
	}

计算公式

@Test
	public void testFormula() throws Exception{
		FileInputStream fileInputStream = new FileInputStream(PATH + "\\公式.xlsx");
		Workbook workbook = new XSSFWorkbook(fileInputStream);
		Sheet sheet = workbook.getSheetAt(0);
		Row row = sheet.getRow(4);
		Cell cell = row.getCell(0);
		//拿到计算公式
		XSSFFormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
		//输出单元格内容
		CellType cellType = cell.getCellType();
		switch (cellType){
			case FORMULA: //公式
				String cellFormula = cell.getCellFormula();
				System.out.println(cellFormula);
				//计算
				CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
				String s = evaluate.formatAsString();
				System.out.println(s);
				break;
		}

	}

EasyExcel

GitHub：EasyExcel链接

Write

@Data
public class easy {
	@ExcelProperty("字符串标题")
	private String string;
	@ExcelProperty("日期标题")
	private Date date;
	@ExcelProperty("数字标题")
	private Double doubleData;
	/**
	 * 忽略这个字段
	 */
	@ExcelIgnore
	private String ignore;
}
/
private List<easy> data() {
		List<easy> list = new ArrayList<easy>();
		for (int i = 0; i < 10; i++) {
			easy data = new easy();
			data.setString("字符串" + i);
			data.setDate(new Date());
			data.setDoubleData(0.56);
			list.add(data);
		}
		return list;
	}

	@Test
	public void testEasy(){//根据list写入
		String fileName = PATH + "\\EasyTest.xlsx";
		EasyExcel.write(fileName, easy.class).sheet("模板").doWrite(data());
		
	}

EasyExcel.write(fileName, easy.class).sheet("模板").doWrite(data());一行代码。

Read

固定套路：

1. 写入，固定格式进行写入。
2. 读取，根据监听器设置的规则进行读取。