Apache POI
POI提供API给JAVA程序对Microsoft Office格式档案读和写的功能

POI工具介绍

POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。主要是运用其中读取和输出excel的功能。

POI官网地址：

https://poi.apache.org/components/index.html

基本功能如下：

HSSF – 提供读写Excel格式（03）xls文件
XSSF – 提供读写Excel OOXML格式（07）xlsx文件
HWPF – 提供读写Word格式
HSLF – 提供读写PowerPoint格式
HDGF – 提供读写Visio格式
【注】03版本最多65535行，07版本的没有限制

可操作的文件类型

在这里插入图片描述

xls和xlsx的区别

xls是excel03版本 xlsx是excel07版本

最大的区别是行列数不同

xls最大支持65536行、256列
xlsx最大支持1048576行、16384列

poi操作

poi 操作xls
poi-ooml操作xlsx

POI - Excel写

步骤

1、导入依赖

<!-- excel工具 -->
<!--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>
		<!-- 日期格式化工具 -->
		<dependency>
		    <groupId>joda-time</groupId>
		    <artifactId>joda-time</artifactId>
		    <version>2.12.1</version>
		</dependency>

2、编写代码

4个主要步骤：
创建工作簿 – 创建工作表 – 创建行 – 创建列

2.1、少量数据

import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.joda.time.DateTime;

/**
 * @author xxms
 */
public class ExcelWriteTest
{
	
    public static void main(String[] args) throws Exception {
    	POIExcel03();
    	POIExcel07();
	}
    
    public static void POIExcel03() throws Exception {
    	String PATH = "D:\\Study\\Back-end\\";
    	// 1、创建一个工作簿 03
    	Workbook workbook = new HSSFWorkbook();
    	// 2、创建一个工作表
    	Sheet sheet = workbook.createSheet("xxms观众统计表");
    	// 3、创建一个行
    	Row row1 = sheet.createRow(0);
    	// 4、创建一个单元格 (1,1)
    	Cell cell11 = row1.createCell(0);
    	cell11.setCellValue("今日新增观众");
    	// (1,2)
    	Cell cell12 = row1.createCell(1);
    	cell12.setCellValue(666);
    	
    	// 第二行
    	Row row2 = sheet.createRow(1);
    	//(2,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流) 03版本就是使用xls结尾
    	FileOutputStream fileOutputStream = new FileOutputStream(PATH+"xxms观众统计表03.xls");
    	
    	workbook.write(fileOutputStream);
    	
    	// 关闭流
    	fileOutputStream.close();
    	
    	System.out.println("xxms观众统计表03 生成完毕");
    }
    
    public static void POIExcel07() throws Exception {
    	String PATH = "D:\\Study\\Back-end\\";
    	// 1、创建一个工作簿 07
    	Workbook workbook = new XSSFWorkbook();
    	// 2、创建一个工作表
    	Sheet sheet = workbook.createSheet("xxms观众统计表");
    	// 3、创建一个行
    	Row row1 = sheet.createRow(0);
    	// 4、创建一个单元格 (1,1)
    	Cell cell11 = row1.createCell(0);
    	cell11.setCellValue("今日新增观众");
    	// (1,2)
    	Cell cell12 = row1.createCell(1);
    	cell12.setCellValue(666);
    	
    	// 第二行
    	Row row2 = sheet.createRow(1);
    	//(2,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流) 07版本就是使用xlsx结尾
    	FileOutputStream fileOutputStream = new FileOutputStream(PATH+"xxms观众统计表07.xlsx");
    	
    	workbook.write(fileOutputStream);
    	
    	// 关闭流
    	fileOutputStream.close();
    	
    	System.out.println("xxms观众统计表07 生成完毕");
    }
}

2.2大数据量导入

package com.ruoyi.common.core.controller;


import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.joda.time.DateTime;

/**
 * 
 * 
 * @author xxms
 */
public class ExcelWriteBigDataTest
{
	
    public static void main(String[] args) throws Exception {
    	POIExcel03BigData();
    	POIExcel07BigData();
	}
    
    public static void POIExcel03BigData() throws Exception {
    	
    	//时间
    	long begin = System.currentTimeMillis();
    	
    	String PATH = "D:\\Study\\Back-end\\";
    	// 1、创建一个工作簿 03
    	Workbook workbook = new HSSFWorkbook();
    	// 2、创建一个工作表
    	Sheet sheet = workbook.createSheet("xxms观众统计表");
    	// 3、写入数据
    	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);
    		}
    	}
    	
    	// 生成一张表(IO流) 03版本就是使用xls结尾
    	FileOutputStream fileOutputStream = new FileOutputStream(PATH+"xxms观众统计表03BigData.xls");
    	
    	workbook.write(fileOutputStream);
    	
    	// 关闭流
    	fileOutputStream.close();
    	
    	long end = System.currentTimeMillis();
    	System.out.println("xxms观众统计表03BigData 生成完毕,时间消耗:"+(double)(end-begin));
    }
    
    public static void POIExcel07BigData() throws Exception {
    	
    	long begin = System.currentTimeMillis();
    	
    	String PATH = "D:\\Study\\Back-end\\";
    	// 1、创建一个工作簿 07
    	Workbook workbook = new XSSFWorkbook();
    	// 2、创建一个工作表
    	Sheet sheet = workbook.createSheet("xxms观众统计表");
    	// 3、写入数据
    	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);
    		}
    	}
    	
    	// 生成一张表(IO流) 07版本就是使用xlsx结尾
    	FileOutputStream fileOutputStream = new FileOutputStream(PATH+"xxms观众统计表07BigData.xlsx");
    	
    	workbook.write(fileOutputStream);
    	
    	// 关闭流
    	fileOutputStream.close();
    	
    	long end = System.currentTimeMillis();
    	System.out.println("xxms观众统计表07BigData 生成完毕,时间消耗:"+(double)(end-begin));
    }
}

时间消耗对比如下：

xxms观众统计表03BigData 生成完毕,时间消耗:847.0
xxms观众统计表07BigData 生成完毕,时间消耗:3048.0

2.3大量数据优化

HSSF仅能保存65535行数据，
XSSF无限制但速度比较慢，
因此可以使用优化后的XSSF，即SXSSF（可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存）
【注】会产生临时文件，需要清理临时文件（fileOutputStream.close();）（默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件，如果向自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）
代码如下：

public static void POIExcel07BigDataS() throws Exception {

		long begin = System.currentTimeMillis();

		String PATH = "D:\\Study\\Back-end\\";
		// 1、创建一个工作簿 07
		Workbook workbook = new SXSSFWorkbook();
		// 2、创建一个工作表
		Sheet sheet = workbook.createSheet("xxms观众统计表");
		// 3、写入数据
		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);
			}
		}

		// 生成一张表(IO流) 07版本就是使用xlsx结尾
		FileOutputStream fileOutputStream = new FileOutputStream(PATH + "xxms观众统计表07BigDataS.xlsx");

		workbook.write(fileOutputStream);

		// 关闭流
		fileOutputStream.close();

		// 清除临时文件
		((SXSSFWorkbook) workbook).dispose();

		long end = System.currentTimeMillis();
		System.out.println("xxms观众统计表07BigDataS 生成完毕,时间消耗:" + (double) (end - begin));
	}

时间对比如下：

xxms观众统计表07BigData 生成完毕,时间消耗:2697.0
xxms观众统计表07BigDataS 生成完毕,时间消耗:806.0

POI - Excel读

1、编写代码

import java.io.FileInputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

public class ExcelReadTest {
	public static void main(String[] args) throws Exception {
		POIExcel03Read();
		POIExcel07Read();
	}

	public static void POIExcel03Read() throws Exception {
		String PATH = "D:\\Study\\Back-end\\";

		// 获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "xxms观众统计表03.xls");

		// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
		Workbook workbook = new HSSFWorkbook(inputStream);
		// 2、得到表
		Sheet sheet = workbook.getSheetAt(0);
		// 3、得到行
		Row row = sheet.getRow(0);
		// 4、得到列
		Cell cell = row.getCell(0);

		// 获取字符串类型
		String value = cell.getStringCellValue();

		System.out.println("03版本的值:" + value);
		inputStream.close();
	}

	public static void POIExcel07Read() throws Exception {
		String PATH = "D:\\Study\\Back-end\\";

		// 获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "xxms观众统计表07.xlsx");

		// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
		Workbook workbook = new XSSFWorkbook(inputStream);
		// 2、得到表
		Sheet sheet = workbook.getSheetAt(0);
		// 3、得到行
		Row row = sheet.getRow(0);
		// 4、得到列
		Cell cell = row.getCell(0);

		// 获取字符串类型
		String value = cell.getStringCellValue();

		System.out.println("07版本的值:" + value);
		inputStream.close();
	}
}

2、读取Excel中不同类型的数据

public static void POIExcelReadTestCellType() throws Exception {
		String PATH = "D:\\Study\\Back-end\\";

		// 获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "明细表.xlsx");

		// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
		Workbook workbook = new XSSFWorkbook(inputStream);
		// 2、得到表
		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 +"|");
				}
			}
			System.out.println();
		}
		
		// 获取表中的内容
		// int rowCount = sheet.getLastRowNum() + 1; (范围更大）
		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();
						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 BLANK: //空
								System.out.print("【BLANK】");
								cellValue = cell.getStringCellValue();
								break;
							case NUMERIC: //数字（日期、普通数字）
								System.out.print("【NUMERIC】");
								if(DateUtil.isCellDateFormatted(cell)) { // 日期
									System.out.print("【日期】");
									Date date = cell.getDateCellValue();
									cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
								}else {
									//不是日期格式，防止数字过长！
									System.out.print("【转换为字符串输出】");
									//这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
									BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
				                    cellValue = bd.toPlainString();
									// 方法过期了
									// cell.setCellType(CellType.STRING);
									// cellValue = cell.toString();
								}
								break;
							case FORMULA: // 公式
				                cellValue = cell.getCellFormula();
							case ERROR: // 故障
				                cellValue = "ERROR VALUE";
				                break;
							default:
								cellValue = "UNKNOW VALUE";
								System.out.println("【数据类型错误】");
								break;
						}
						System.out.print(cellValue);
					}
					System.out.println();
				}
				
			}
		}
		inputStream.close();
	}

输出如下：

序号|卡号|持卡人|手机号|消费日期|小票号|商品编号|商品条码|商品名称|商品单位|原价|销售价|销售数量|销售金额|优惠金额|是否上架|
[2-1]【NUMERIC】【转换为字符串输出】1
[2-2]【NUMERIC】【转换为字符串输出】100088
[2-3]【STRING】张三
[2-4]【NUMERIC】【转换为字符串输出】1233333333333
[2-5]【NUMERIC】【日期】2020-04-21 00:00:00
[2-6]【NUMERIC】【转换为字符串输出】2392392093
[2-7]【STRING】PV92038038
[2-8]【STRING】PV98298392
[2-9]【STRING】蒙牛
[2-10]【STRING】瓶
[2-11]【NUMERIC】【转换为字符串输出】200.5
[2-12]【NUMERIC】【转换为字符串输出】1000
[2-13]【NUMERIC】【转换为字符串输出】1
[2-14]【NUMERIC】【转换为字符串输出】900
[2-15]【NUMERIC】【转换为字符串输出】100
[2-16]【BOOLEAN】true

3、计算公式

表格内容如下
在这里插入图片描述
代码如下

public static void POIExcelTestFormula() throws Exception{
		String PATH = "D:\\Study\\Back-end\\";

		// 获取文件流
		FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx");

		// 1、创建一个工作簿。使用excel能操作的这边他都可以操作
		Workbook workbook = new XSSFWorkbook(inputStream);
		// 2、得到表
		Sheet sheet = workbook.getSheetAt(0);
		
		Row row = sheet.getRow(4);
		Cell cell = row.getCell(0);
		
		//拿到计算公式 eval
		XSSFFormulaEvaluator xssfFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook)workbook);
		
		//输出单元格的内容
		String cellFormula = cell.getCellFormula();
		System.out.println(cellFormula); //SUM(A2,A3,A4)
		
		//计算
		CellValue evaluate = xssfFormulaEvaluator.evaluate(cell);
		String cellValue = evaluate.formatAsString();
		
		System.out.println(cellValue);//600.0
	}