一、Maven依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
二、代码块
1、控制层
package controller;
import model.ImportFileModel;
import model.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import service.ImportFileService;
@RestController
public class ImportFileController {
@Autowired
private ImportFileService importFileService;
@RequestMapping("/importFile")
@ResponseBody
public Result<ImportFileModel> importFile(@RequestParam(value = "file", required = false) MultipartFile file) {
Result<ImportFileModel> importFileModelResult = importFileService.importFile(file);
return importFileModelResult;
}
}
2、服务层
package service.impl;
import model.ImportFileModel;
import model.Result;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import service.ImportFileService;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
@Service
public class ImportFileServiceImpl implements ImportFileService {
@Override
public Result<ImportFileModel> importFile(MultipartFile file) {
try {
//获取上传文件的对象
Map<String, String> map = readExcel(file.getInputStream());
// 将获取的数据重新整合输出
Iterator iterator = map.keySet().iterator();
ImportFileModel model = new ImportFileModel();
while (iterator.hasNext()) {
String key = iterator.next().toString();
String value = (String) map.get(key);
if ("姓名".equals(key)) {
model.setName(value);
}
if ("年龄".equals(key)) {
model.setAge(value);
}
if ("出生日期".equals(key)) {
model.setBirth(value);
}
if ("总销量".equals(key)) {
model.setSalesVolume(Double.valueOf(value));
}
}
return new Result(model);
} catch (Exception e) {
return new Result<>(null, "", e.getMessage(), false);
}
}
/* * @Description //TODO 读取文件中的数据信息 * @Param [inputStream] * @return java.util.Map<java.lang.String,java.lang.String> **/
public Map<String, String> readExcel(InputStream inputStream) throws Exception {
Workbook hssfWorkbook = new XSSFWorkbook(inputStream);
Map<String, String> hashMap = new HashMap<>();
FormulaEvaluator evaluator = hssfWorkbook.getCreationHelper().createFormulaEvaluator();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
Row hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
Cell key = hssfRow.getCell(0);
Cell cell = hssfRow.getCell(1);
//解析文件中各种类型的数据信息
String stringCellValue = getStringCellValue(cell, evaluator);
//key值为去空格的值
hashMap.put(key.toString().trim(), stringCellValue);
}
}
}
return hashMap;
}
/** * @param cell Excel单元格 * @return String 单元格数据内容 */
private String getStringCellValue(Cell cell, FormulaEvaluator evaluator) {
//若表格为空,则直接返回空值
if (cell == null) {
return "";
}
String strCell = "";
switch (cell.getCellType()) {
// 表格中的数据为字符串型
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
// 表格中的数据为数值型
case Cell.CELL_TYPE_NUMERIC:
strCell = String.valueOf(cell.getNumericCellValue());
break;
// 表格中的数据为布尔型
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
// 表格中的数据为空值
case Cell.CELL_TYPE_BLANK:
strCell = "";
break;
// 表格中的数据信息为公式类
case Cell.CELL_TYPE_FORMULA:
double numberValue = evaluator.evaluate(cell).getNumberValue();
strCell = String.valueOf(numberValue);
break;
default:
strCell = "";
break;
}
if (strCell.equals("") || strCell == null) {
return "";
}
return strCell;
}
}
三、文件格式与输出
1、导入的文件格式
文章参考:
https://blog.csdn.net/weixin_42906244/article/details/105944860
文章评论