java-poi
使用poi操作excel，仅供学习，实际中推荐easy_excel工具
https://github.com/alibaba/easyexcel/
https://www.yuque.com/easyexcel/doc/easyexcel
1.导入maven依赖
<dependencies>
<!--xls03-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx07-->
<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.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
2.写的测试类
package com.coding; import org.apache.poi.hssf.usermodel.HSSFSheet; 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.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.joda.time.DateTime; import org.junit.Test; import java.io.FileOutputStream; import java.io.IOException; /** * @description * @author: wuliang * @date: 2021/2/2310:04 */ public class ExcelWriteTest { // 03 - 65536 ，超出这个记录就会报错 private final static String PATH = "D:\\coding_edu_api\\text-excel-poi2"; @Test public void testWriter03() throws IOException { //新建一个excel 工作簿 xls HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //创建一个默认的工作表 sheet0 HSSFSheet sheet = hssfWorkbook.createSheet("会员统计"); //创建一行记录，从下标0开始，0就代表row1 Row row1 = sheet.createRow(0); //添加每一格数据(1-1) Cell cell11 = row1.createCell(0); cell11.setCellValue("今日注册"); //(1-2) Cell cell12 = row1.createCell(1); cell12.setCellValue("999"); //创建一行记录，从下标0开始，1就代表row2 Row row2 = sheet.createRow(1); //添加每一格数据(1-1) Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); //(1-2) Cell cell22 = row2.createCell(1); String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(dateTime); //文件编写完毕，然后需要将数据导出成为xls FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testWrite03.xls"); hssfWorkbook.write(fileOutputStream); fileOutputStream.close(); System.out.println("文件生成完毕"); } @Test public void testWriter07() throws IOException { //easy_excel重新了poi对07版Excel的解析（原生的API太慢了） //新建一个excel工作簿xlsx07，对象的变化 Workbook xssfSheets = new XSSFWorkbook(); //创建一个默认的工作表 sheet0 Sheet sheet = xssfSheets.createSheet("会员统计"); //创建一行记录，从下标0开始，0就代表row1 Row row1 = sheet.createRow(0); //添加每一格数据(1-1) Cell cell11 = row1.createCell(0); cell11.setCellValue("今日注册"); //(1-2) Cell cell12 = row1.createCell(1); cell12.setCellValue("999"); //创建一行记录，从下标0开始，1就代表row2 Row row2 = sheet.createRow(1); //添加每一格数据(1-1) Cell cell21 = row2.createCell(0); cell21.setCellValue("统计时间"); //(1-2) Cell cell22 = row2.createCell(1); String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell22.setCellValue(dateTime); //文件编写完毕，然后需要将数据导出成为xlsx FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testWrite07.xlsx"); xssfSheets.write(fileOutputStream); fileOutputStream.close(); System.out.println("文件生成完毕"); } // 一般使用这个来处理大的对象！加速 SXSSF @Test public void testWriter07BigDataFast() throws IOException { long begin = System.currentTimeMillis(); // 创建工作簿。 会产生临时文件， // 100条记录保存在内存中，超过这个数据，前面的内容就写入到 excel SXSSFWorkbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); // 填写数据 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("写入完毕"); // 文件编写完毕，然后需要数据到处成为 xls FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\testWrite07-bigobject-fast.xls"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); // 建议：清除临时文件 workbook.dispose(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); } }
3.读的测试类
package com.coding; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.joda.time.DateTime; import org.junit.Test; import java.io.FileInputStream; import java.io.IOException; import java.util.Date; /** * 首页 * * @description * @author: wuliang * @date: 2021/2/2310:43 */ public class ExcelReadTest { private final static String PATH = "D:\\coding_edu_api\\text-excel-poi2\\"; @Test public void testRead03() throws IOException { //要读取的文件 FileInputStream is = new FileInputStream(PATH + "testWrite03.xls"); //读取到workbook Workbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); //获取表 //读取记录（1-1） Row row = sheet.getRow(1); Cell cell = row.getCell(1); System.out.println(cell.getStringCellValue()); // 关闭流 is.close(); } @Test public void testRead07() throws IOException { // 要读取的文件 FileInputStream is = new FileInputStream(PATH + "testWrite07.xlsx"); // 读取到Workbook Workbook workbook = new XSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); // 获取表 // 读取记录 （1-1） Row row = sheet.getRow(1); Cell cell = row.getCell(1); System.out.println(cell.getStringCellValue()); // 关闭流 is.close(); } //读取 数据类型问题，封装为工具类 @Test public void testCellType() throws Exception { FileInputStream is = new FileInputStream(PATH + "会员消费商品明细表.xls"); Workbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); // 获取表 // 读取所有的标题（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.println(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 = rowData.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { //打印数据 System.out.println("[" + (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】"); // cellValue = cell.getNumericCellValue(); // 日期判断 ， 如果不是日期 if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期 System.out.println("【日期】"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); } else { System.out.println("【转换为字符串】"); 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(); } // 代码实现单元格统计 (数据=> 汇总计算) @Test public void testFormula() throws Exception { // 要读取的文件 FileInputStream is = new FileInputStream(PATH+"计算公式.xls"); // 读取到Workbook 03 Workbook workbook = new HSSFWorkbook(is); Sheet sheet = workbook.getSheetAt(0); // 获取表 // 读取 5,1 题库（全部都是Excel录入的） Row row = sheet.getRow(4); Cell cell = row.getCell(0); // 公式计算器 eval HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook); // formulaEvaluator 输出单元的内容 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; } } }
4.可以封装成工具类
package com.coding; //import lombok.Data; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.Date; //@Data public class ExcelImportUtil { private HSSFFormulaEvaluator formulaEvaluator; private HSSFSheet sheet; private String pattern;// 日期格式 public ExcelImportUtil() { super(); } public ExcelImportUtil(InputStream is) throws IOException { this(is, 0, true); } public ExcelImportUtil(InputStream is, int sheetIndex) throws IOException { this(is, sheetIndex, true); } public ExcelImportUtil(InputStream is, int sheetIndex, boolean evaluateFormular) throws IOException { super(); HSSFWorkbook workbook = new HSSFWorkbook(is); this.sheet = workbook.getSheetAt(sheetIndex); if (evaluateFormular) { this.formulaEvaluator = new HSSFFormulaEvaluator(workbook); } } public String getCellValue(Cell cell) throws Exception { int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_NUMERIC://0 if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期 Date date = cell.getDateCellValue(); if (pattern != null) { SimpleDateFormat sdf = new SimpleDateFormat(pattern); return sdf.format(date); } else { return date.toString(); } } else { // 不是日期格式，则防止当数字过长时以科学计数法显示 cell.setCellType(HSSFCell.CELL_TYPE_STRING); return cell.toString(); } case Cell.CELL_TYPE_STRING://1 return cell.getStringCellValue(); case Cell.CELL_TYPE_FORMULA://2 if (this.formulaEvaluator == null) {//得到公式 return cell.getCellFormula(); } else {//计算公式 CellValue evaluate = this.formulaEvaluator.evaluate(cell); return evaluate.formatAsString(); } case Cell.CELL_TYPE_BLANK://3 //注意空和没有值不一样，从来没有录入过内容的单元格不属于任何数据类型，不会走这个case return ""; case Cell.CELL_TYPE_BOOLEAN://4 return String.valueOf(cell.getBooleanCellValue()); case Cell.CELL_TYPE_ERROR: default: throw new Exception("Excel数据类型错误"); } } }
