最近正在完善之前和小伙伴假期做的一个项目的一些小功能，其中一个就是上传excel文件并且将excel文件中的数据导入到指定数据库中。在这其中有遇到一些很小的细节的问题，所以决定将这个实现过程记录下来。
一.基本介绍
（1）前后台分离，前端使用form表单提交，直接将文件流传递给后台，后台通过poi进行解析。
（2）在Excel中，有几个基础的概念
- 一个Execl就是一个Workbook
- 一个Sheet就是一张表格
- 一个Workbook可以包含多个Sheet
- 一行为一个Row
- 每一行（Row）的每一列就是一个单元格（Cell）
（3）实现思路：在读取Excel文件的过程中，我们可以首先构造一个Workbook实例，然后遍历每一个Sheet，对于每一个sheet，我们再遍历每一个row，基于预先设定好的列名，读取每个cell的值。将读取到的每一个cell值，放进预先创建好的对象实例中去。【每一个row就相当于一个实例对象】，最后我们将这些实例对象组成一个对象列表，批量插入到数据库中。
二.代码实现
1.导入依赖包以及基础配置
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.3</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>在spring-mvc.xml中添加代码
<bean id="multipartResolve" class="org.springframework.web.multipart.support.StandardServletMultipartResolver">
</bean>在web.xml中央控制器中添加支持
2.Controller层代码
后台使用MultipartFile来获取上传的文件。需要注意的是其中的@RequestParam中的参数值需要与前端页面表单中type="file"的name的属性值相同。
//导入excel用户信息
@ResponseBody
@RequestMapping(value = "/importUserInfo.do", method = RequestMethod.POST)
public ResultModel importUserInfo(@RequestParam("file") MultipartFile file) throws Exception {
if (!file.isEmpty()) {
InputStream inputStream = file.getInputStream();
return ResultModel.builder()
.data(userService.importUserInfo(inputStream, file.getOriginalFilename()))
.code(SUCCESS)
.build();
} else
return ResultModel.builder()
.data("File is empty!")
.code(SUCCESS)
.build();
}
3.userService中的实现方法importUserInfo
@Override
public Integer importUserInfo(InputStream filePath, String file) {
List<User> list = new ArrayList<>();
String[] s;
if (!file.endsWith(".xls") && !file.endsWith(".xlsx")) {
//文件上传格式出错
return -2;
}
Workbook workbook = null;
try {
//获取Workbook实例
workbook = ExcelUtil.getWorkBook(file, filePath);
} catch (Exception e) {
e.printStackTrace();
}
Sheet sheet = workbook.getSheetAt(0);
//去除sheet中的一些无效行，比如值为空但是有格式的空白行
sheet = ExcelUtil.resetSheet(sheet);
int lastRowNum = sheet.getLastRowNum();
for (int i = 1; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
int cells = sheet.getRow(i).getPhysicalNumberOfCells();
//获取每一行的数值，并将其填入一个字符串的数组
s = ExcelUtil.getExcelRows(row, cells);
User user = new User();
for (int j = 0; j < s.length; j++) {
if (j == 0 && !s[j].equals(""))
user.setInstitute(s[j]);
else if (j == 1 && !s[j].equals(""))
user.setMajor(s[j]);
else if (j == 2 && !s[j].equals(""))
user.setClassId(Integer.getInteger(s[j]));
else if (j == 3 && !s[j].equals(""))
user.setUsername(s[j]);
else if (j == 4 && !s[j].equals(""))
user.setRealName(s[j]);
else if (j == 5 && !s[j].equals(""))
user.setBirthday(DateUtil.parseYYYYMMDDDate(s[j]));
else if (j == 6 && !s[j].equals(""))
user.setQq(s[j]);
else if (j == 7 && !s[j].equals(""))
user.setTieba(s[j]);
else if (j == 8 && !s[j].equals(""))
user.setWeibo(s[j]);
else if (j == 9 && !s[j].equals(""))
user.setBlog(s[j]);
user.setPassword("huhugty7tgf6f");
}
list.add(user);
}
return userDao.insertList(list);
}
4.Excel读取类ExcelUtil
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
public class ExcelUtil {
//判断文件类型是否满足要求
public static Workbook getWorkBook(String file, InputStream fis)throws Exception{
Workbook workbook = null;
if(!file.endsWith(".xls") && !file.endsWith(".xlsx")){
throw new Exception("上传表格的格式错误！");
}
if(file.endsWith(".xls")){
workbook = new HSSFWorkbook(fis,true);
}
if(file.endsWith(".xlsx")){
workbook = new XSSFWorkbook(fis);
}
return workbook;
}
//获取每一行的数据，并且返回一个字符串数组
public static String[] getExcelRows(Row row , int columuNum){
String[] s ;
StringBuilder builder = new StringBuilder();
for(int x = 0;x<columuNum;x++){
Cell cell = row.getCell(x);
builder.append(getStringCellValue(cell)+",");
}
s = builder.toString().split(",");
return s;
}
//获取单元内的有效数据
public static String getStringCellValue(Cell cell){
StringBuilder sb = new StringBuilder();
switch (cell.getCellType()){
//数字
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)){
sb.append(cell.getDateCellValue());
}else {
//将该数字强制转化为字符串类型获取
cell.setCellType(Cell.CELL_TYPE_STRING);
sb.append(cell.getStringCellValue());
}
break;
//字符串
case Cell.CELL_TYPE_STRING:
sb.append(cell.getStringCellValue());
break;
//布尔
case Cell.CELL_TYPE_BOOLEAN:
sb.append(cell.getBooleanCellValue());
break;
//公式
case Cell.CELL_TYPE_FORMULA:
sb.append(cell.getCellFormula());
break;
//空值
case Cell.CELL_TYPE_BLANK:
sb.append("");
break;
//故障
case Cell.CELL_TYPE_ERROR:
sb.append("");
break;
default:
sb.append("");
break;
}
return sb.toString();
}
//过滤掉表中的无意义空白行，因为getLastRowNum（）在获取Row行数时，对于表中没有值但却有格式的无意义空白行也将计入
public static Sheet resetSheet(Sheet sheet){
CellReference cellReference = new CellReference("A4");
boolean flag;
for(int i=cellReference.getRow();i<=sheet.getLastRowNum();){
Row r = sheet.getRow(i);
if(r == null){
sheet.shiftRows(i+1,sheet.getLastRowNum(),-1);
continue;
}
flag = false;
for(Cell c : r){
if(c.getCellType()!=Cell.CELL_TYPE_BLANK){
flag = true;
break;
}
}
if(flag){
i++;
continue;
}
else{ //如果是空白行，没有数据，但是有格式
if(i == sheet.getLastRowNum())
sheet.removeRow(r);
else
sheet.shiftRows(i+1,sheet.getLastRowNum(),-1);
}
}
return sheet;
}
}
三.测试
这里我使用postman来测试后台接口：
测试结果：
扫一扫
357
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
