最近公司新增功能要求导入excel,并读取其中数据批量写入数据库。于是就开始了这个事情,之前的文章,记录了上传文件,本篇记录如何通过POI读取excel数据并封装为对象上传。
上代码:
1、首先这是一个依赖第三方的事情,故而需要导入jar包
1 <!-- read excel start -->
2 <dependency>
3 <groupId>org.apache.poi</groupId>
4 <artifactId>poi</artifactId>
5 <version>3.17</version>
6 </dependency>
7 <dependency>
8 <groupId>org.apache.poi</groupId>
9 <artifactId>poi-ooxml</artifactId>
10 <version>3.17</version>
11 </dependency>
12 <!-- read excel start -->
切记下面的那个包不可忘记,否则报错RETURN_NULL_AND_BLANK
之前我们已经上传含有数据的excel模板,返回的是一个url,本博主对应的url的excel表内容如下:
那么我们传入导入的excel的url来进行读取。不做过多解释,该有的解释代码注释里面均有
1 package com.topband.sweepmachine.utils;
2
3 import java.io.File;
4 import java.io.FileInputStream;
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.text.DecimalFormat;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 import org.apache.commons.io.FileUtils;
12 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
14 import org.apache.poi.ss.usermodel.Cell;
15 import org.apache.poi.ss.usermodel.CellType;
16 import org.apache.poi.ss.usermodel.Row;
17 import org.apache.poi.ss.usermodel.Sheet;
18 import org.apache.poi.ss.usermodel.Workbook;
19 import org.apache.poi.ss.usermodel.WorkbookFactory;
20 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
21 import org.slf4j.Logger;
22 import org.slf4j.LoggerFactory;
23
24 import com.topband.cloud.common.utils.DateFormatUtil;
25 import com.topband.cloud.common.utils.StringUtil;
26
27 public class ReadExcelUtil {
28
29 private Logger logger = LoggerFactory.getLogger(this.getClass());
30 private static final String EXCEL_XLS = ".xls";
31 private static final String EXCEL_XLSX = ".xlsx";
32
33 /**
34 *读取excel数据
35 * @throws Exception
36 *
37 */
38 public static List<List<String>> readExcelInfo(String url) throws Exception{
39 /*
40 * workbook:工作簿,就是整个Excel文档
41 * sheet:工作表
42 * row:行
43 * cell:单元格
44 */
45
46 // BufferedWriter bw = new BufferedWriter(new FileWriter(new File(url)));
47 // 支持excel2003、2007
48 File excelFile = new File(url);//创建excel文件对象
49 InputStream is = new FileInputStream(excelFile);//创建输入流对象
50 checkExcelVaild(excelFile);
51 Workbook workbook = getWorkBook(is, excelFile);
52 // Workbook workbook = WorkbookFactory.create(is);//同时支持2003、2007、2010
53 // 获取Sheet数量
54 int sheetNum = workbook.getNumberOfSheets();
55 // 创建二维数组保存所有读取到的行列数据,外层存行数据,内层存单元格数据
56 List<List<String>> dataList = new ArrayList<List<String>>();
57 // FormulaEvaluator formulaEvaluator = null;
58 // 遍历工作簿中的sheet,第一层循环所有sheet表
59 for(int index = 0;index<sheetNum;index++){
60 Sheet sheet = workbook.getSheetAt(index);
61 if(sheet==null){
62 continue;
63 }
64 System.out.println("表单行数:"+sheet.getLastRowNum());
65 // 如果当前行没有数据跳出循环,第二层循环单sheet表中所有行
66 for(int rowIndex=0;rowIndex<=sheet.getLastRowNum();rowIndex++){
67 Row row = sheet.getRow(rowIndex);
68 // 根据文件头可以控制从哪一行读取,在下面if中进行控制
69 if(row==null){
70 continue;
71 }
72 // 遍历每一行的每一列,第三层循环行中所有单元格
73 List<String> cellList = new ArrayList<String>();
74 for(int cellIndex=0;cellIndex<row.getLastCellNum();cellIndex++){
75 Cell cell = row.getCell(cellIndex);
76 System.out.println("遍历行中cell数据:"+getCellValue(cell));
77 cellList.add(getCellValue(cell));
78 System.out.println("第"+cellIndex+"个: cell个数:"+cellList.size());
79 }
80 dataList.add(cellList);
81 System.out.println("第"+rowIndex+"行: 共几行:"+dataList.size());
82 }
83
84 }
85 is.close();
86 return dataList;
87 }
88 /**
89 *获取单元格的数据,暂时不支持公式
90 *
91 *
92 */
93 public static String getCellValue(Cell cell){
94 CellType cellType = cell.getCellTypeEnum();
95 String cellValue = "";
96 if(cell==null || cell.toString().trim().equals("")){
97 return null;
98 }
99
100 if(cellType==CellType.STRING){
101 cellValue = cell.getStringCellValue().trim();
102 return cellValue = StringUtil.isEmpty(cellValue)?"":cellValue;
103 }
104 if(cellType==CellType.NUMERIC){
105 if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断日期类型
106 cellValue = DateFormatUtil.formatDurationYMD(cell.getDateCellValue().getTime());
107 } else { //否
108 cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
109 }
110 return cellValue;
111 }
112 if(cellType==CellType.BOOLEAN){
113 cellValue = String.valueOf(cell.getBooleanCellValue());
114 return cellValue;
115 }
116 return null;
117
118 }
119 /**
120 *判断excel的版本,并根据文件流数据获取workbook
121 * @throws IOException
122 *
123 */
124 public static Workbook getWorkBook(InputStream is,File file) throws Exception{
125
126 Workbook workbook = null;
127 if(file.getName().endsWith(EXCEL_XLS)){
128 workbook = new HSSFWorkbook(is);
129 }else if(file.getName().endsWith(EXCEL_XLSX)){
130 workbook = new XSSFWorkbook(is);
131 }
132
133 return workbook;
134 }
135 /**
136 *校验文件是否为excel
137 * @throws Exception
138 *
139 *
140 */
141 public static void checkExcelVaild(File file) throws Exception {
142 String message = "该文件是EXCEL文件!";
143 if(!file.exists()){
144 message = "文件不存在!";
145 throw new Exception(message);
146 }
147 if(!file.isFile()||((!file.getName().endsWith(EXCEL_XLS)&&!file.getName().endsWith(EXCEL_XLSX)))){
148 System.out.println(file.isFile()+"==="+file.getName().endsWith(EXCEL_XLS)+"==="+file.getName().endsWith(EXCEL_XLSX));
149 System.out.println(file.getName());
150 message = "文件不是Excel";
151 throw new Exception(message);
152 }
153 }
154 /* public static void main(String[] args) throws Exception {
155 readExcelInfo("g://批量新增设备表.xlsx");
156 }*/
157 }
封装为对象,插入数据库,这里本博主不放入公司业务对象,删掉了
1 @PostMapping("/addBatchDevice")
2 public ResponseObj addBatchDevice(@RequestBody JSONObject jsonObject){
3 ResponseObj response = new ResponseObj();
4 response.setData(Defined.STATUS_SUCCESS);
5 response.setMessage("插入成功!");
6 String url = jsonObject.getString("url");
7 // 存放封装的设备
8 List<Device> devices = new ArrayList<Device>();
9 try {
10 List<List<String>> list = ReadExcelUtil.readExcelInfo(url);
11 for(int i=0;i<list.size();i++){
12 // new一个对象按照相应的字段设置进去就可以了,这里省略对象设置值,字段如下:
13 System.out.println("封装成对象后:"+"\t"+"设备名称--->"+list.get(i).get(0)+"型号--->"+list.get(i).get(1)+"数量---> "+list.get(i).get(2));
14 }
15 } catch (Exception e) {
16 // TODO Auto-generated catch block
17 e.printStackTrace();
18 }
19 // 插入数据库
20 // 调用相关插入方法,可以批量也可单条插入循环实现,看具体业务需要选择
21 return response;
22 }
调用接口运行效果如下:
OK,成功运行!