一、引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
二、工具类
直接上代码。
package com.wb.srpingboot.demoz.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* <p>
* Description: poi将excle文件中的信息导入到数据库中工具类
* </p>
*
* @author 阿萨
* @version v1.0.0
* @see com.wb.srpingboot.demoz.utils
* @since 2020-08-25 19:42:28
*/
public class ImportExcel {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* TODO: 判断文件是否符合标准
*
* @param fileName 文件命
* @return boolean
*/
private static Result isXls(String fileName) {
// (?i)忽略大小写
if (fileName.matches("^.+\\.(?i)(xls)$")) {
return Result.ok("xls");
} else if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
return Result.ok("xlsx");
} else {
return Result.fail("文件格式不对");
}
}
/**
* TODO: 读取信息方法
*
* @param fileName 文件命
* @param inputStream 文件
* @return List<Map < String, Object>>
*/
public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) {
Result ret = isXls(fileName);
//System.out.println(ret);
Workbook workbook = null;
// 根据后缀创建不同的对象
try {
if (XLS.equals(ret.getMessage())) {
workbook = new HSSFWorkbook(inputStream);
} else if (XLSX.equals(ret.getMessage())) {
workbook = new XSSFWorkbook(inputStream);
} else {
return null;
}
Sheet sheet = workbook.getSheetAt(0);
// 得到标题行
Row titleRow = sheet.getRow(0);
int lastRowNum = sheet.getLastRowNum();
int lastCellNum = titleRow.getLastCellNum();
List<Map<String, Object>> list = new ArrayList<>();
for (int i = 1; i <= lastRowNum; i++) {
Map<String, Object> map = new HashMap<>();
Row row = sheet.getRow(i);
for (int j = 0; j < lastCellNum; j++) {
// 得到列名
String key = titleRow.getCell(j).getStringCellValue();
Cell cell = row.getCell(j);
cell.setCellType(CellType.STRING);
map.put(key, cell.getStringCellValue());
}
list.add(map);
}
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
三、controller代码
/**
* TODO: 将上传的excel文件导入到数据库中(仅支持xls和xlsx文件)
* @param mFile 上传的文件
* @return com.wb.srpingboot.demoz.utils.Result
*/
@RequestMapping("/uploadExcel")
@ResponseBody
public Result importExcel(@RequestParam(value = "mFile")MultipartFile mFile) {
InputStream inputStream = null;
try {
String fileName = mFile.getOriginalFilename();
// 获取上传文件的输入流
inputStream = mFile.getInputStream();
// 调用工具类中方法,读取excel文件中数据
List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);
// 将对象先转为json格式字符串,然后再转为List<SysUser> 对象
ObjectMapper objMapper = new ObjectMapper();
String infos = objMapper.writeValueAsString(sourceList);
// json字符串转对象
List<User> list = objMapper.readValue(infos, new TypeReference<List<User>>() {});
if (list != null && list.size() >0) {
// 批量添加
return userService.userAdds(list);
}
return Result.fail("文件格式错误,仅支持xls和xlsx格式的文件");
} catch (Exception e) {
e.printStackTrace();
return Result.fail("文件格式错误");
}finally {
if (inputStream != null ){
try {
inputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
四、给出Result返回值类
我还是自定义了一个返回值类型,可以自己根据自己公司的规范进行更改。
package com.wb.srpingboot.demoz.utils;
import java.io.Serializable;
/**
* Controller中处理方法的返回值类型
* @author 阿萨
* @since 1.0
*/
public class Result implements Serializable {
// 状态码
private int code;
// 消息
private String message;
// 数据
private Object data;
public Result() { }
public Result(int code, String message, Object data) {
this.code = code;
this.message = message;
this.data = data;
}
public Result(int code, String message) {
this(code, message, null);
}
public Result(int code, Object data) {
this(code, null, data);
}
public static Result ok(String message, Object data) {
return new Result(0, message, data);
}
public static Result ok(String message) {
return new Result(0, message, null);
}
public static Result ok(Object data) {
return new Result(0, null, data);
}
public static Result fail(String message) {
return new Result(-1, message);
}
public static Result fail(String message, Object data) {
return new Result(-1, message, data);
}
public static Result fail(Object data) {
return new Result(-1, null, data);
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public Object getData() {
return data;
}
public void setData(Object data) {
this.data = data;
}
@Override
public String toString() {
return "Result{" +
"code=" + code +
", message='" + message + '\'' +
", data=" + data +
'}';
}
}
感谢观看。