Java 读取Excel格式xls、xlsx数据工具类
需要POI的jar包支持
调用方式
|
1
2
|
ReadExcelTest excelTest = new ReadExcelTest();excelTest.readExcel("D:\\data1.xlsx"); |
|
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
package com.util; import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream; 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.apache.xmlbeans.impl.piccolo.io.FileFormatException; public class ReadExcelTest { private static final String EXTENSION_XLS = "xls"; private static final String EXTENSION_XLSX = "xlsx"; /*** * <pre> * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类) * xls:HSSFWorkbook * xlsx：XSSFWorkbook * @param filePath * @return * @throws IOException * </pre> */ private Workbook getWorkbook(String filePath) throws IOException { Workbook workbook = null; InputStream is = new FileInputStream(filePath); if (filePath.endsWith(EXTENSION_XLS)) { workbook = new HSSFWorkbook(is); } else if (filePath.endsWith(EXTENSION_XLSX)) { workbook = new XSSFWorkbook(is); } return workbook; } /** * 文件检查 * @param filePath * @throws FileNotFoundException * @throws FileFormatException */ private void preReadCheck(String filePath) throws FileNotFoundException, FileFormatException { // 常规检查 File file = new File(filePath); if (!file.exists()) { throw new FileNotFoundException("传入的文件不存在：" + filePath); } if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) { throw new FileFormatException("传入的文件不是excel"); } } /** * 读取excel文件内容 * @param filePath * @throws FileNotFoundException * @throws FileFormatException */ public void readExcel(String filePath) throws FileNotFoundException, FileFormatException { // 检查 this.preReadCheck(filePath); // 获取workbook对象 Workbook workbook = null; try { workbook = this.getWorkbook(filePath); // 读文件 一个sheet一个sheet地读取 for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) { Sheet sheet = workbook.getSheetAt(numSheet); if (sheet == null) { continue; } System.out.println("=======================" + sheet.getSheetName() + "========================="); int firstRowIndex = sheet.getFirstRowNum(); int lastRowIndex = sheet.getLastRowNum(); // 读取首行 即,表头 Row firstRow = sheet.getRow(firstRowIndex); for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) { Cell cell = firstRow.getCell(i); String cellValue = this.getCellValue(cell, true); System.out.print(" " + cellValue + "\t"); } System.out.println(""); // 读取数据行 for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) { Row currentRow = sheet.getRow(rowIndex);// 当前行 int firstColumnIndex = currentRow.getFirstCellNum(); // 首列 int lastColumnIndex = currentRow.getLastCellNum();// 最后一列 for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) { Cell currentCell = currentRow.getCell(columnIndex);// 当前单元格 String currentCellValue = this.getCellValue(currentCell, true);// 当前单元格的值 System.out.print(currentCellValue + "\t"); } System.out.println(""); } System.out.println("======================================================"); } } catch (Exception e) { e.printStackTrace(); } finally { if (workbook != null) { try { workbook.close(); } catch (IOException e) { e.printStackTrace(); } } } } /** * 取单元格的值 * @param cell 单元格对象 * @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”) * @return */ private String getCellValue(Cell cell, boolean treatAsStr) { if (cell == null) { return ""; } if (treatAsStr) { // 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0” // 加上下面这句，临时把它当做文本来读取 cell.setCellType(Cell.CELL_TYPE_STRING); } if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } else { return String.valueOf(cell.getStringCellValue()); } } } |
使用poi读取xlsx格式的Excel总结
今天遇到的坑
公司实习生项目，其中有个功能是读取Excel数据，之前做过以为很快就能搞定，万万没想到，本地写的一切都正常，可就在要发布生产了，尼玛测试环境居然出bug了读取xlsx格式的Excel，读不了，本地完全可以，就是测试环境上不行，心里一万只曹尼玛奔过
下面是代码部分：
我使用的是springmvc，首先是controller部分
|
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
|
@RequestMapping("ReadFromExcel")@ResponseBody public Response ReadFromExcel(@RequestParam(value = "file") MultipartFile file,@RequestAttribute("userNo") String userNo) { try { //校验文件 checkFile(file); List<ArrayList<String>> list =excelService.readExcel(file); if (CollectionUtils.isEmpty(list)) { return new Response(ERROR_CODE, "导入的文件没有数据",false); } }catch (Exception e){ logger.error("ReadFromExcel异常",e); } return new Response(ERROR_CODE, "导入失败", false); }private void checkFile(MultipartFile file) throws IOException { //判断文件是否存在 if(null == file){ logger.error("文件不存在！"); throw new FileNotFoundException("文件不存在！"); } //获得文件名 String fileName = file.getOriginalFilename(); logger.info("ReadFromExcel fileName",fileName); //判断文件是否是excel文件 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){ logger.error(fileName + "不是excel文件"); throw new IOException(fileName + "不是excel文件"); } } |
然后是读取Excel文件部分，也就是service部分
这些网上随便一搜都能搜到
|
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
@Override public List<ArrayList<String>> readExcel(MultipartFile file) { List<ArrayList<String>> list = new ArrayList<ArrayList<String>>(); try { // 检查文件 logger.info("ExcelServiceImpl 获取文件名", file.getOriginalFilename()); // 获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); // 创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 logger.info("获得Workbook工作薄对象", file.getOriginalFilename()); if (workbook != null) { for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { // 获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(sheetNum); logger.info("获得当前sheet工作表", file.getOriginalFilename()); if (sheet == null) { continue; } // 获得当前sheet的开始行 int firstRowNum = sheet.getFirstRowNum(); // 获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); // 循环除了第一行的所有行 for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) { // 获得当前行 Row row = sheet.getRow(rowNum); if (row == null) { continue; } // 获得当前行的开始列 int firstCellNum = row.getFirstCellNum(); // 获得当前行的列数 int lastCellNum = row.getPhysicalNumberOfCells(); ArrayList<String> cells = new ArrayList<>(); // 循环当前行 for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) { Cell cell = row.getCell(cellNum); cells.add(getCellValue(cell)); } list.add(cells); } } } } catch (Exception e) { logger.error("readExcel Exception", e.getMessage()); } return list; } private Workbook getWorkBook(MultipartFile file) { // 获得文件名 String fileName = file.getOriginalFilename(); // 创建Workbook工作薄对象，表示整个excel Workbook workbook = null; try { // 获取excel文件的io流 InputStream is = file.getInputStream(); logger.info("获取excel文件的io流"); // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith(xls)) { // 2003 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(xlsx)) { // 2007 workbook = new XSSFWorkbook(is); } } catch (Exception e) { logger.info(e.getMessage()); } return workbook; } private String getCellValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } // 把数字当成String来读，避免出现1读成1.0的情况 if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { cell.setCellType(Cell.CELL_TYPE_STRING); } // 判断数据的类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // 数字 cellValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: // 字符串 cellValue = String.valueOf(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: // Boolean cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: // 公式 cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case Cell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } return cellValue; } |
spring-servlet.xml 配置如下
|
1
2
3
4
5
|
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"><property name="defaultEncoding" value="utf-8"/><property name="maxUploadSize" value="10485760000"/><property name="maxInMemorySize" value="40960"/></bean> |
最初的maven是这么配置的
|
1
2
3
4
5
6
7
8
9
10
|
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency> |
好了，本地走起来完全没毛病，很开心，终于可以发布了，可以早点回学校写论文了，发到测试环境，测试读取xls也是没毛病，可尼玛，想读取个xlsx的文件试试看，网页提示404，这什么鬼，打日志查问题，还一直以为是前端的哥们出问题，可一看日志不对啊，请求已经进来了，可是走到这一步就没了 workbook = new XSSFWorkbook(is);这是为什么，赶紧网上查，一堆解决方案，一个个试，最后实在没办法把别人所有的方法一个个试，最后又加了如下jar包
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<dependency><groupId>xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>2.3.0</version><type>pom</type></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-examples</artifactId><version>3.9</version></dependency> |
问题是解决了，可却不知其所以然，记录一下。以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/wangjianyu0115/article/details/51344853
