-
Java POI操作excel
Java poi
概述
1、将用户信息导出为excel表格（导出数据…)
2、将Excel表中的信息录入到网站数据库（习题上传…)
开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中!操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel !
poi
1、导入依赖
<dependencies> <dependency> <groupId>org.apache.poigroupId> <artifactId>poiartifactId> <version>4.0.1version> dependency> <dependency> <groupId>org.apache.poigroupId> <artifactId>poi-ooxmlartifactId> <version>4.0.1version> dependency> <dependency> <groupId>joda-timegroupId> <artifactId>joda-timeartifactId> <version>2.10.6version> dependency> <dependency> <groupId>junitgroupId> <artifactId>junitartifactId> <version>4.13.2version> dependency> dependencies>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
2、POI-Excel写入
xls 是03版的excel 对象是HSSFWorkbook
@Test public void testWrite01() throws IOException { // 1、创建一个工作簿 Workbook workbook = new HSSFWorkbook(); // 2、创建一个工作表 Sheet sheet = workbook.createSheet("员工信息表"); // 3、创建一行，0代表从第一行开始 Row row1 = sheet.createRow(0); // 4、创建一个单元格 Cell cell11 = row1.createCell(0); // 将坐标[0,0]的单元格写入一个数据 cell11.setCellValue("姓名"); // 将坐标[0,1]的单元格写入一个数据 Cell cell12 = row1.createCell(1); cell12.setCellValue("年龄"); // 将坐标[0,1]的单元格写入一个数据 Cell cell13 = row1.createCell(2); cell13.setCellValue("出生日期"); // 创建第二行 Row row2 = sheet.createRow(1); // 将坐标[1,0]的单元格写入一个数据 Cell cell21 = row2.createCell(0); cell21.setCellValue("张三"); // 将坐标[1,1]的单元格写入一个数据 Cell cell22 = row2.createCell(1); cell22.setCellValue(21); // 将坐标[1,1]的单元格写入一个数据 Cell cell23 = row2.createCell(2); String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell23.setCellValue(dateTime); // 生成一张表 FileOutputStream fileOutputStream = new FileOutputStream(path+"员工信息.xls"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); workbook.close(); System.out.println("生成完毕"); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
xlsx 是07版的excel 使用XSSFWorkbook
使用的对象不一样，但是因为多态的，代码可以不用变化太大
@Test public void testWrite02() throws IOException { // 1、创建一个工作簿 Workbook workbook = new XSSFWorkbook(); // 2、创建一个工作表 Sheet sheet = workbook.createSheet("员工信息表"); // 3、创建一行，0代表从第一行开始 Row row1 = sheet.createRow(0); // 4、创建一个单元格 Cell cell11 = row1.createCell(0); // 将坐标[0,0]的单元格写入一个数据 cell11.setCellValue("姓名"); // 将坐标[0,1]的单元格写入一个数据 Cell cell12 = row1.createCell(1); cell12.setCellValue("年龄"); // 将坐标[0,1]的单元格写入一个数据 Cell cell13 = row1.createCell(2); cell13.setCellValue("出生日期"); // 创建第二行 Row row2 = sheet.createRow(1); // 将坐标[1,0]的单元格写入一个数据 Cell cell21 = row2.createCell(0); cell21.setCellValue("张三"); // 将坐标[1,1]的单元格写入一个数据 Cell cell22 = row2.createCell(1); cell22.setCellValue(21); // 将坐标[1,1]的单元格写入一个数据 Cell cell23 = row2.createCell(2); String dateTime = new DateTime().toString("yyyy-MM-dd HH:mm:ss"); cell23.setCellValue(dateTime); // 生成一张表 FileOutputStream fileOutputStream = new FileOutputStream(path+"员工信息.xlsx"); workbook.write(fileOutputStream); // 关闭流 fileOutputStream.close(); workbook.close(); System.out.println("生成完毕"); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
大数据量的情况下写入：
@Test public void testWriteData03() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); 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); } } workbook.write(new FileOutputStream(path+"testData.xls")); long end = System.currentTimeMillis(); System.out.println(end-start); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
@Test public void testWriteData04() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); 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); } } workbook.write(new FileOutputStream(path+"testData.xlsx")); long end = System.currentTimeMillis(); System.out.println(end-start); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
使用流的方式创建更快
@Test public void testWriteData05() throws IOException { long start = System.currentTimeMillis(); Workbook workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); 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); } } workbook.write(new FileOutputStream(path+"testDataUp.xlsx")); long end = System.currentTimeMillis(); System.out.println(end-start); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
3、POI-Excel读取
@Test public void readTest01() throws IOException { FileInputStream fileInputStream = new FileInputStream(path + "员工信息.xls"); Workbook workbook = new HSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(1); Cell cell10 = row.getCell(0); String name = cell10.getStringCellValue(); Cell cell12 = row.getCell(1); Double age = cell12.getNumericCellValue(); System.out.println(name); System.out.println(age); fileInputStream.close(); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
@Test public void readTest02() throws IOException { FileInputStream fileInputStream = new FileInputStream(path + "员工信息.xlsx"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(1); Cell cell10 = row.getCell(0); String name = cell10.getStringCellValue(); Cell cell12 = row.getCell(1); Double age = cell12.getNumericCellValue(); System.out.println(name); System.out.println(age); fileInputStream.close(); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
读取不同的数据类型数据
@Test public void readTest03() throws IOException { FileInputStream fileInputStream = new FileInputStream(path + "data.xlsx"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); // 获取标题内容 , 获取第一行 Row title = sheet.getRow(0); if (!Objects.isNull(title)){ // 获取第一行中的列数 int cells = title.getPhysicalNumberOfCells(); for (int i = 0; i < cells; i++) { Cell cell = title.getCell(i); if (!Objects.isNull(cell)) { // 获取某一列的类型 CellType cellType = cell.getCellType(); System.out.println(cellType); String value = cell.getStringCellValue(); System.out.print(value + ","); } } } System.out.println("================================"); // 获取所有的行 int rows = sheet.getPhysicalNumberOfRows(); for (int rowNum = 1; rowNum < rows; rowNum++) { Row row = sheet.getRow(rowNum); if (!Objects.isNull(row)){ int cells = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cells; cellNum++) { Cell cell = row.getCell(cellNum); if (!Objects.isNull(cell)) { CellType cellType = cell.getCellType(); switch (cellType){ case _NONE: System.out.println("_NONE"); break; case BLANK: System.out.println("BLANK"); break; case ERROR: byte errorCellValue = cell.getErrorCellValue(); System.out.println(errorCellValue); System.out.println("ERROR"); break; case STRING: String value = cell.getStringCellValue(); System.out.println(value); System.out.println("STRING"); break; case BOOLEAN: boolean booleanCellValue = cell.getBooleanCellValue(); System.out.println(booleanCellValue); System.out.println("BOOLEAN"); break; case FORMULA: Date dateCellValue = cell.getDateCellValue(); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); String date = simpleDateFormat.format(dateCellValue); System.out.println(date); System.out.println("FORMULA"); break; case NUMERIC: double numericCellValue = cell.getNumericCellValue(); System.out.println(numericCellValue); System.out.println("NUMERIC"); break; } } } } } fileInputStream.close(); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
提取匹配方法：
public void dataType(InputStream inputStream) throws IOException { Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); // 获取所有的行 int rowCount = sheet.getPhysicalNumberOfRows(); for (int rowNum = 1; rowNum < rowCount; rowNum++) { Row row = sheet.getRow(rowNum); if (!Objects.isNull(row)){ // 获取所有的列 int cellCount = row.getPhysicalNumberOfCells(); for (int cellNum = 0; cellNum < cellCount; cellNum++) { Cell cell = row.getCell(cellNum); if (!Objects.isNull(cell)) { CellType cellType = cell.getCellType(); switch (cellType){ case _NONE: System.out.println("_NONE"); break; case BLANK: System.out.println("BLANK"); break; case ERROR: byte errorCellValue = cell.getErrorCellValue(); System.out.println(errorCellValue); System.out.println("ERROR"); break; case STRING: String value = cell.getStringCellValue(); System.out.println(value); System.out.println("STRING"); break; case BOOLEAN: boolean booleanCellValue = cell.getBooleanCellValue(); System.out.println(booleanCellValue); System.out.println("BOOLEAN"); break; case FORMULA: Date dateCellValue = cell.getDateCellValue(); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); String date = simpleDateFormat.format(dateCellValue); System.out.println(date); System.out.println("FORMULA"); break; case NUMERIC: double numericCellValue = cell.getNumericCellValue(); System.out.println(numericCellValue); System.out.println("NUMERIC"); break; } } } } } }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
计算公式
@Test public void test04() throws IOException { FileInputStream fileInputStream = new FileInputStream(path+"math.xlsx"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(4); Cell cell = row.getCell(0); FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook); String cellFormula = cell.getCellFormula(); System.out.println(cellFormula); CellValue evaluate = formulaEvaluator.evaluate(cell); String value = evaluate.formatAsString(); System.out.println(value); }- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
-
相关阅读:
Jquery data-属性的获取与使用
Scala数组常用函数（1）
基于IDEA的Maven（依赖介绍和引用）
idea 引用本地jar包
SpringBoot开发实战（微课视频版）
简述股票程序化交易的关键点是什么？
数据结构(二)——线性表(顺序表)
JSP application对象简介说明
基于MVT的医学图像处理平台设计与实现
黑客（自学方法）技术——网络安全
- 原文地址：https://blog.csdn.net/weixin_46073538/article/details/126849303
