Java使用POI操作Excel（把mysql数据库转换成Excel）
POI简介
- 简介
Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java对Microsoft Office格式档案读和写的功能。 - 常用的包
HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF － 提供读写Microsoft Word DOC格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读Microsoft Visio格式档案的功能。
HPBF － 提供读Microsoft Publisher格式档案的功能。
HSMF － 提供读Microsoft Outlook格式档案的功能。
- 引入的依赖
<!--xls--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!--xlsx--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.10</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency>
代码编写
创建项目
删掉最开始的那个模块，父模块，然后就是这个样子了，
引入依赖
pom.xml
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>POIStudy</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <!--xlsx--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.10</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.junit.jupiter</groupId> <artifactId>junit-jupiter</artifactId> <version>5.8.1</version> <scope>compile</scope> </dependency> </dependencies> </project>
创建工作蒲（写数据）
对于java操作Excel主要是操作这几个地方
工作薄
工作表
行
列
需要注意的是：2003 版本和 2007 版本存在兼容性的问题！03最多只有 65535 行！
03版本
文件结构
演示代码
public class Main { String path = "D:\\系统默认\\桌面\\博客素材\\POIDemo\\"; @Test public void testExcel03() throws IOException{ // 1.创建一个工作薄 Workbook workbook = new HSSFWorkbook(); // 2.创建一个工作表 Sheet sheet = workbook.createSheet("极客李华的粉丝表"); // 3.创建一个行（1,1） Row row1 = sheet.createRow(0); // 4.创建一个单元格 Cell cell1 = row1.createCell(0); cell1.setCellValue("今日新增粉丝"); // (1, 2) Cell cell12 = row1.createCell(1); cell12.setCellValue(233); // 创建第二行 Row row2 = sheet.createRow(1); // 创建单元格（col 2-1） Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); // 创建第三行单元格 Cell cell22 = row2.createCell(1); // 格式化创建时间 String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(dateTime); // 新建一输出文件流 FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls"); // 把相应的Excel工作蒲存盘 workbook.write(out); // 操作结束 关闭文件 out.close(); System.out.println("文件生成成功"); } }
这里就是生成的Excel文件
07版本
07版本相比于03版本它的不同之处在于，这个创建工作薄的时候使用的方法不一样，然后07版与03班的Excel文件的结尾也是不一样的，07是.xlsx。
项目结构
项目代码
@Test public void testExcel07() throws IOException{ // 1.创建一个工作薄 Workbook workbook = new XSSFWorkbook(); // 2.创建一个工作表 Sheet sheet = workbook.createSheet("极客李华的粉丝表"); // 3.创建一个行（1,1） Row row1 = sheet.createRow(0); // 4.创建一个单元格 Cell cell1 = row1.createCell(0); cell1.setCellValue("今日新增粉丝"); // (1, 2) Cell cell12 = row1.createCell(1); cell12.setCellValue(233); // 创建第二行 Row row2 = sheet.createRow(1); // 创建单元格（col 2-1） Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); // 创建第三行单元格 Cell cell22 = row2.createCell(1); // 格式化创建时间 String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(dateTime); // 新建一输出文件流 FileOutputStream out = new FileOutputStream(path + "极客李华的观众统计表03.xls"); // 把相应的Excel工作蒲存盘 workbook.write(out); // 操作结束 关闭文件 out.close(); System.out.println("文件生成成功"); }
运行结果
对比
- 对于03版本
缺点：最多只能处理65536行，否则会抛出异常
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
演示代码
这里用的是HSSFWorkbook
@Test public void testWrite03BigData() throws IOException { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个SXSSFWorkbook Workbook workbook = new HSSFWorkbook(); //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 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); } } System.out.println("操作结束"); FileOutputStream out = new FileOutputStream(path+"bigdata03.xls"); workbook.write(out); // 操作结束，关闭文件 out.close(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end - begin)/1000); }
运行结果
写满数据这个1s不到就写完了，还是很快的。
- 对于07版本
缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量，如20万条
演示代码
这里用的是XSSFWorkbook
@Test public void testWrite07BigData() throws IOException { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个XSSFWorkbook Workbook workbook = new XSSFWorkbook(); //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 for (int rowNum = 0; rowNum < 100000; rowNum++) { //创建一个行 Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格 Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("done"); FileOutputStream out = new FileOutputStream(path+"bigdata07.xlsx"); workbook.write(out); // 操作结束，关闭文件 out.close(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end - begin)/1000); }
运行结果
这个结果花了近10s还是很慢的，虽然可以操作的数据变多了，但是时间消耗更多了
演示代码
这里用的是SXSSFWorkbook
项目结构
优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存
注意：
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件
如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量 )
@Test public void testWrite07BigDataFast() throws IOException { //记录开始时间 long begin = System.currentTimeMillis(); //创建一个SXSSFWorkbook Workbook workbook = new SXSSFWorkbook(); //创建一个sheet Sheet sheet = workbook.createSheet(); //xls文件最大支持65536行 for (int rowNum = 0; rowNum < 100000; rowNum++) { //创建一个行 Row row = sheet.createRow(rowNum); for (int cellNum = 0; cellNum < 10; cellNum++) {//创建单元格 Cell cell = row.createCell(cellNum); cell.setCellValue(cellNum); } } System.out.println("done"); FileOutputStream out = new FileOutputStream(path+"bigdata07-fast.xlsx"); workbook.write(out); // 操作结束，关闭文件 out.close(); //清除临时文件 ((SXSSFWorkbook)workbook).dispose(); //记录结束时间 long end = System.currentTimeMillis(); System.out.println((double)(end - begin)/1000); }
运行结果：
从结果看这个运行结果还是蛮快的
读工作蒲（读数据）
03版本
这里演示的是如何读取03版本的Excel
@Test public void testRead03() throws Exception{ InputStream is = new FileInputStream(path+"极客李华的观众统计表03.xls"); Workbook workbook = new HSSFWorkbook(is); // 这里是读取第一个工作薄 Sheet sheet = workbook.getSheetAt(0); // 读取第一行第一列 Row row = sheet.getRow(0); Cell cell = row.getCell(0); // 输出单元内容 System.out.println(cell.getStringCellValue()); // 操作结束，关闭文件 is.close(); }
运行结果
07版本
这里演示的是如何读取07版本的Excel
演示代码
@Test public void testRead07() throws Exception{ InputStream is = new FileInputStream(path+"极客李华的观众统计表07.xlsx"); Workbook workbook = new XSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); // 读取第一行第一列 Row row = sheet.getRow(0); Cell cell = row.getCell(0); // 输出单元内容 System.out.println(cell.getStringCellValue()); // 操作结束，关闭文件 is.close(); }
运行结果
读这个Excel
@Test public void WriteExcel03() throws Exception { InputStream is = new FileInputStream(path + "会员消费商品明细表.xls"); // 创建工作薄 Workbook workbook = new HSSFWorkbook(is); // 获取第一个工作表 Sheet sheet = workbook.getSheetAt(0); // 读取标题所有内容 Row rowTitle = sheet.getRow(0); if (rowTitle != null) {// 行不为空 // 读取cell int cellCount = rowTitle.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = rowTitle.getCell(cellNum); if (cell != null) { 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) {// 行不为空 // 读取cell 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) { 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 Cell.CELL_TYPE_ERROR: System.out.print("【数据类型错误】"); break; } System.out.println(cellValue); } } } } is.close(); }
运行结果
公式计算（这个东西了解即可没有很多应用）
现在要对这个Excel表格进行求和处理。
获取A5位置的公式
如果获取的单元格没有公式的话，那么就是打印的结果是没有结果的。
运行结果
@Test public void testFormula() throws Exception{ InputStream is = new FileInputStream(path + "计算公式.xls"); Workbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); // 读取第五行第一列 // 这个row获取的是 Row row = sheet.getRow(4); Cell cell = row.getCell(0); //公式计算器 FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); // 输出单元内容 int cellType = cell.getCellType(); System.out.println(cellType); switch (cellType) { case Cell.CELL_TYPE_FORMULA://2 //得到公式 String formula = cell.getCellFormula(); System.out.println(formula); CellValue evaluate = formulaEvaluator.evaluate(cell); String cellValue = evaluate.formatAsString(); System.out.println(cellValue); break; } }
如果大家觉得有用的话，可以关注我下面的微信公众号，极客李华，我会在里面更新更多行业资讯，企业面试内容，编程资源，如何写出可以让大厂面试官眼前一亮的简历等内容，让大家更好学习编程，我的抖音，B站也叫极客李华。
