1.Maven文件
<!--读取Excel的架包-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-examples -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-examples</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-excelant -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.15</version>
</dependency>
2.实体类对象
package com.mz.monotoring.Domain; /**
* @author sjl
* @date 2019-03-28 10:09
*/
public class User {
private String name; private String sex; private String age; private String money; public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public String getSex() {
return sex;
} public void setSex(String sex) {
this.sex = sex;
} public String getAge() {
return age;
} public void setAge(String age) {
this.age = age;
} public String getMoney() {
return money;
} public void setMoney(String money) {
this.money = money;
}
}
3.Controller层接收Base64位的Excel文件(Postman测试界面)
4.Controller层
package com.mz.monotoring.Controller; import com.mz.monotoring.Domain.User;
import com.mz.monotoring.Util.Base64File;
import com.mz.monotoring.Util.ReadExcelUtils;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile; import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.UUID; /**
* @author sjl
* @date 2019-03-26 13:56
*/
@Controller
public class ExcelController { @RequestMapping("excel")
@ResponseBody
public List<User> excel(@RequestBody Map map) {
List<User> excelInfo = null;
//读取到的Excel保存的位置
//linux保存路径
//String path1 = "/usr/local/tomcatusps/apache-tomcat-8.5.15/Root/excel/";
try {
//Window保存路径
String path1 = "F:\\excel";
File files = new File(path1);
files.mkdir(); String[] w1 = map.get("key").toString().split(",");
String s1 = new StringBuilder().append(UUID.randomUUID().toString()).append(".").append("xlsx").toString();
String s = new StringBuilder().append(path1).append("\\").append(s1).toString();
Base64File.decoderBase64File(w1[1], path1 + "\\" + s1);
ReadExcelUtils excelUtils = new ReadExcelUtils();
File newFile = new File(s);
FileInputStream input = new FileInputStream(newFile);
MultipartFile multipartFile = new MockMultipartFile("excelUtils", newFile.getName(), "text/plain", input); excelInfo = excelUtils.getExcelInfo(multipartFile);
} catch (IOException e) {
e.printStackTrace();
}
return excelInfo;
}
}
5.Base64File将读取到的base64位字符串转化为文件存储至本地
package com.mz.monotoring.Util; import sun.misc.BASE64Decoder; import java.io.FileOutputStream;
import java.io.IOException; public class Base64File { /**
* 将base64字符解码保存文件
*
* @param base64Code
* @param targetPath
* @throws Exception
*/ public static void decoderBase64File(String base64Code, String targetPath) {
byte[] buffer;
FileOutputStream out = null;
try {
buffer = new BASE64Decoder().decodeBuffer(base64Code);
out = new FileOutputStream(targetPath);
out.write(buffer);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
} }
6.读取文件 判断是什么版本Excel并且循环读取每层数据存储实体类中
package com.mz.monotoring.Util; import com.mz.monotoring.Domain.User;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.web.multipart.MultipartFile; import java.io.IOException;
import java.io.InputStream;
import java.util.*; /**
* @author sjl
* @date 2019-03-27 11:11
*/
public class ReadExcelUtils {
// 总行数
private int totalRows = 0;
// 总条数
private int totalCells = 0;
// 错误信息接收器
private String errorMsg; //构造方法
public ReadExcelUtils() {
} public int getTotalRows() {
return totalRows;
} public int getTotalCells() {
return totalCells;
} public String getErrorMsg() {
return errorMsg;
} public List<User> getExcelInfo(MultipartFile mFile) {
//获取文件名
String fileName = mFile.getOriginalFilename();
try {
//验证文件名是否合格
if (!validateExcel(fileName)) {
return null;
}
//根据文件名判断文件是2003版本还是2007版本
boolean isExcel2003 = true;
if (isExcel2007(fileName)) {
isExcel2003 = false;
}
return createExcel(mFile.getInputStream(), isExcel2003);
} catch (IOException e) {
e.printStackTrace();
}
return null;
} /**
* 根据excel里面的内容读取客户信息
*
* @param is 输入流
* @param isExcel2003 excel是2003还是2007版本
* @return
* @throws IOException
*/
private List<User> createExcel(InputStream is, boolean isExcel2003) {
try {
Workbook wb = null;
//当excel是2003时,创建excel2003
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
//读取Excel里面的信息
return readExcelValue(wb);
} catch (IOException e) {
e.printStackTrace();
}
return null;
} private List<User> readExcelValue(Workbook wb) {
//得到都一个shell
Sheet sheet = wb.getSheetAt(0);
//得到Excel的行数
this.totalRows = sheet.getPhysicalNumberOfRows();
//得到Excel的列数(前提是有行数)
if (totalRows >= 1 && sheet.getRow(0) != null) {
this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<User> userList = new ArrayList<>();
//循环Excel行数
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
//循环Excel的列
Map<String, Object> map = new HashMap<>(16);
if (isRowEmpty(row) == false) {
User user = new User();
for (int c = 0; c < this.totalCells; c++) {
Cell cell = row.getCell(c);
if (cell != null) {
if (c == 0) {
//判断其中是否包含空格
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String name = String.valueOf(cell.getNumericCellValue());
user.setName(name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));
} else if (cell.getStringCellValue() != null) {
//导入姓名
user.setName(cell.getStringCellValue());
} else {
user.setName("");
}
} else if (c == 1) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String sex = String.valueOf(cell.getNumericCellValue());
user.setSex(sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1));
} else if (cell.getStringCellValue() != null) {
//性别
user.setSex(cell.getStringCellValue());
} else {
user.setSex("");
}
} else if (c == 2) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String age = String.valueOf(cell.getNumericCellValue());
user.setAge(age.substring(0, age.length() - 2 > 0 ? age.length() - 2 : 1));
} else if (cell.getStringCellValue() != null) {
//年龄
user.setAge(cell.getStringCellValue());
} else {
user.setAge("");
}
} else if (c == 3) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
String money = String.valueOf(cell.getNumericCellValue());
user.setMoney(money.substring(0, money.length() - 2 > 0 ? money.length() - 2 : 1));
} else if (cell.getStringCellValue() != null) {
//工资
user.setMoney(cell.getStringCellValue());
} else {
user.setMoney("");
}
}
}
}
//将读取到的数据添加到list集合中
userList.add(user);
}
} return userList;
} /**
* 判断EXCEL是否为空
*
* @param row
* @return
*/
private boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
} }
return false;
} /**
* 验证EXCEL文件
*
* @param fileName
* @return
*/
private boolean validateExcel(String fileName) {
if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
errorMsg = "文件不是excel格式";
return false;
}
return true;
} /**
* 是否是2007的excel，返回true是2007
*
* @param fileName
* @return
*/
private boolean isExcel2007(String fileName) {
return fileName.matches("^.+\\.(?i)(xlsx)$");
} /**
* 是否是2003的excel，返回true是2003
*
* @param fileName
* @return
*/
private boolean isExcel2003(String fileName) {
return fileName.matches("^.+\\.(?i)(xls)$");
}
}
7.读取的Excel文件内容截图
8.Postman测试返回的List<User>数据