Java POI实现Excel文件批量导入（兼容xls,xlsx）|江阴雨辰互联

Java POI实现Excel文件批量导入（兼容xls,xlsx）

1、POI使用详解
1.1、什么是Apache POI？
POI是Apache软件基金会用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。
1.2、POI的jar包导入
使用poi需要用到poi-3.14.jar和poi-ooxml-3.14.jar两个jar包，maven包依赖

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.14</version>
</dependency>

1.3、POI的API讲解
1.3.1结构

HSSF － 提供读写Microsoft Excel格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

1.3.2 对象

本文主要介绍HSSF和XSSF两种组件，简单的讲HSSF用来操作Office 2007版本前excel.xls文件，XSSF用来操作Office 2007版本后的excel.xlsx文件，
注意二者的后缀是不一样的。

1.3.3 操作步骤

以HSSF为例，XSSF操作相同。
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象，用行对象得到Cell对象
4、对Cell对象读写。

2、代码操作
2.1、效果图

导入后效果：

2.2、代码详解
这里我以SpringBoot+MybatisPlus为基础
Controller:

package com.unicloud.navigation.controller;import com.unicloud.navigation.exception.ErrorMsg;
import com.unicloud.navigation.service.NavigationObjectService;
import com.unicloud.scmon.core.util.R;
import com.unicloud.scmon.log.annotation.SysLog;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;/*** 导览对象* @author songweihao* @date 2021/9/24*/
@RestController
@RequiredArgsConstructor
@RequestMapping("/navigation_object")
@Api(value = "导览对象Controller", tags = "导览对象操作接口")
public class NavigationObjectController {@Autowiredprivate final NavigationObjectService navigationObjectService;/*** 批量导入* @param file excel文件* @return R*/@ApiOperation(value = "批量导入", notes = "批量导入")@SysLog("批量导入" )@PostMapping("/batch_import" )public Object batchImport(@RequestParam("file") MultipartFile file) {Object res = navigationObjectService.batchImport(file);if (res instanceof ErrorMsg) {return R.failed(((ErrorMsg) res).getCode(), ((ErrorMsg) res).getMsg());}return res;}
}

Service:

package com.unicloud.navigation.service.Impl;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.unicloud.navigation.entity.AttractionManagement;
import com.unicloud.navigation.entity.NavigationClassification;
import com.unicloud.navigation.entity.NavigationObject;
import com.unicloud.navigation.exception.ErrorMsg;
import com.unicloud.navigation.mapper.AttractionManagementMapper;
import com.unicloud.navigation.mapper.NavigationClassificationMapper;
import com.unicloud.navigation.mapper.NavigationObjectMapper;
import com.unicloud.navigation.service.NavigationObjectService;
import com.unicloud.scmon.core.util.R;
import org.apachemons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;/*** 导览对象* @author songweihao* @date 2021/9/24*/
@Service
public class NavigationObjectServiceImpl extends ServiceImpl<NavigationObjectMapper, NavigationObject> implements NavigationObjectService {@Autowiredprivate NavigationObjectMapper navigationObjectMapper;@Autowiredprivate NavigationClassificationMapper navigationClassificationMapper;@Autowiredprivate AttractionManagementMapper attractionManagementMapper;private final static String XLS = "xls";private final static String XLSX = "xlsx";/*** 导入Excel，兼容xls和xlsx*/@Overridepublic Object batchImport(MultipartFile file) {// 获得文件名Workbook workbook = null;String fileName = file.getOriginalFilename();if(fileName.endsWith(XLS)){// 2003try {workbook = new HSSFWorkbook(file.getInputStream());} catch (IOException e) {log.error("",e);}}else if(fileName.endsWith(XLSX)){// 2007try {workbook = new XSSFWorkbook(file.getInputStream());} catch (IOException e) {log.error("",e);}}else{ErrorMsg error = ErrorMsg.NO_EXCEL;return error;}Sheet sheet = workbook.getSheet("Sheet1");// 指的行数，一共有多少行int rows = sheet.getLastRowNum();if(rows == 2){ErrorMsg error = ErrorMsg.NO_ROW;return error;}List<NavigationObject> list = new LinkedList<>();for (int i = 3; i < rows + 1; i++) {// 读取左上端单元格Row row = sheet.getRow(i);// 行不为空if (row != null) {int num = row.getRowNum() + 1;// 读取cellNavigationObject navigationObject = new NavigationObject();// 导览对象名称String navigationObjectName = getCellValue(row.getCell(0));if (StringUtils.isBlank(navigationObjectName)) {return R.failed(10003,"第" + num + "行导览对象名称数据有误，请改正后，再导入");}navigationObject.setNavigationObjectName(navigationObjectName);// 导览分类String classificationName = getCellValue(row.getCell(1));LambdaQueryWrapper<NavigationClassification> queryWrapper1 = new LambdaQueryWrapper<>();queryWrapper1.eq(NavigationClassification::getClassificationName, classificationName);NavigationClassification navigationClassification = navigationClassificationMapper.selectOne(queryWrapper1);if (navigationClassification == null) {return R.failed(10004,"第" + num + "行导览分类数据有误，请改正后，再导入");}navigationObject.setNavigationClassificationId(navigationClassification.getId());// 所属景点String attractionName = getCellValue(row.getCell(2));LambdaQueryWrapper<AttractionManagement> queryWrapper2 = new LambdaQueryWrapper<>();queryWrapper2.eq(AttractionManagement::getAttractionName, attractionName);AttractionManagement attractionManagement = attractionManagementMapper.selectOne(queryWrapper2);if (attractionManagement == null) {return R.failed(10005,"第" + row.getRowNum() + 1 + "行所属景点数据有误，请改正后，再导入");}navigationObject.setAttractionId(attractionManagement.getId());// 所在位置String location = getCellValue(row.getCell(3));navigationObject.setLocation(location);// 经纬度坐标String longitudeAndLatitude = getCellValue(row.getCell(4));LambdaQueryWrapper<NavigationObject> queryWrapper3 = new LambdaQueryWrapper<>();queryWrapper3.eq(NavigationObject::getLongitudeAndLatitude, longitudeAndLatitude);NavigationObject no = navigationObjectMapper.selectOne(queryWrapper3);if (no != null) {return R.failed(10006,"第" + num + "行经纬度坐标数据存在重复，请改正后，再导入");}if (StringUtils.isBlank(longitudeAndLatitude)) {return R.failed(10007,"第" + num + "行经纬度坐标数据有误，请改正后，再导入");}navigationObject.setLongitudeAndLatitude(longitudeAndLatitude);// 是否显示String isShow = getCellValue(row.getCell(5));if ("是".equals(isShow) || "否".equals(isShow)) {if ("是".equals(isShow)) {navigationObject.setIsShow(1);} else {navigationObject.setIsShow(0);}} else {return R.failed(10008,"第" + num + "行是否显示数据有误，请改正后，再导入");}// 导览对象简介String navigationObjectIntroduction = getCellValue(row.getCell(6));navigationObject.setNavigationObjectIntroduction("<p>" + navigationObjectIntroduction + "</p>");list.add(navigationObject);}}for (NavigationObject navigationObject : list) {navigationObjectMapper.insert(navigationObject);}return R.ok("数据导入成功");}/*** 获得Cell内容*/private String getCellValue(Cell cell) {String value = "";if (cell != null) {// 以下是判断数据的类型switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_NUMERIC: // 数字value = cell.getNumericCellValue() + "";if (HSSFDateUtil.isCellDateFormatted(cell)) {Date date = cell.getDateCellValue();if (date != null) {value = new SimpleDateFormat("yyyy-MM-dd").format(date);} else {value = "";}} else {value = new DecimalFormat("0").format(cell.getNumericCellValue());}break;case HSSFCell.CELL_TYPE_STRING: // 字符串value = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_BOOLEAN: // Booleanvalue = cell.getBooleanCellValue() + "";break;case HSSFCell.CELL_TYPE_FORMULA: // 公式value = cell.getCellFormula() + "";break;case HSSFCell.CELL_TYPE_BLANK: // 空值value = "";break;case HSSFCell.CELL_TYPE_ERROR: // 故障value = "非法字符";break;default:value = "未知类型";break;}}return value.trim();}
}