Excel读取代码
package app.excel;
import app.service.PersonJobNumberService;
import app.util.DateUtils;
import org.apache.commons.lang3.StringUtils;
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.Component;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;
import java.util.logging.Logger;
/**
* Author: Dreamer-1
* Date: 2019-03-01
* Time: 10:21
* Description: 读取Excel内容
*/
@Component
public class ExcelReader {
private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // 日志打印类
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
@Autowired
private PersonJobNumberService personJobNumberService;
/**
* 根据文件后缀名类型获取对应的工作簿对象
* @param inputStream 读取文件的输入流
* @param fileType 文件后缀名类型(xls或xlsx)
* @return 包含文件数据的工作簿对象
* @throws IOException
*/
public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
/**
* 读取Excel文件内容
* @param file 上传的Excel文件
* @return 读取结果列表,读取失败时返回null
*/
public List<KpiAssginVO> readExcel(MultipartFile file) {
Workbook workbook = null;
try {
// 获取Excel后缀名
String fileName = file.getOriginalFilename();
if (fileName == null || fileName.isEmpty() || fileName.lastIndexOf(".") < 0) {
logger.warning("解析Excel失败,因为获取到的Excel文件名非法!");
return null;
}
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
// 获取Excel工作簿
workbook = getWorkbook(file.getInputStream(), fileType);
// 读取excel中的数据
List<KpiAssginVO> resultDataList = parseExcel(workbook);
return resultDataList;
} catch (Exception e) {
logger.warning("解析Excel失败,文件名:" + file.getOriginalFilename() + " 错误信息:" + e.getMessage());
return null;
} finally {
try {
if (null != workbook) {
workbook.close();
}
} catch (Exception e) {
logger.warning("关闭数据流出错!错误信息:" + e.getMessage());
return null;
}
}
}
/**
* 解析Excel数据
* @param workbook Excel工作簿对象
* @return 解析结果
*/
private List<KpiAssginVO> parseExcel(Workbook workbook) {
List<KpiAssginVO> resultDataList = new ArrayList<>();
// 解析sheet
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 校验sheet是否合法
if (sheet == null) {
continue;
}
// 获取第一行数据
int firstRowNum = sheet.getFirstRowNum();
Row firstRow = sheet.getRow(firstRowNum);
if (null == firstRow) {
logger.warning("解析Excel失败,在第一行没有读取到任何数据!");
}
// 解析每一行的数据,构造数据对象
int rowStart = firstRowNum + 1;
int rowEnd = sheet.getPhysicalNumberOfRows();
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row row = sheet.getRow(rowNum);
if (null == row) {
continue;
}
KpiAssginVO resultData = convertRowToData(row);
if (null == resultData) {
logger.warning("第 " + row.getRowNum() + "行数据不合法,已忽略!");
continue;
}
resultDataList.add(resultData);
}
}
return resultDataList;
}
/**
* 将单元格内容转换为字符串
* @param cell
* @return
*/
private static String convertCellValueToString(Cell cell) {
if(cell==null){
return null;
}
String returnValue = null;
switch (cell.getCellType()) {
case NUMERIC: //数字
Double doubleValue = cell.getNumericCellValue();
// 格式化科学计数法,取一位整数
DecimalFormat df = new DecimalFormat("0");
returnValue = df.format(doubleValue);
break;
case STRING: //字符串
returnValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔
Boolean booleanValue = cell.getBooleanCellValue();
returnValue = booleanValue.toString();
break;
case BLANK: // 空值
break;
case FORMULA: // 公式
returnValue = cell.getCellFormula();
break;
case ERROR: // 故障
break;
default:
break;
}
return returnValue;
}
/**
* 提取每一行中需要的数据,构造成为一个结果数据对象
*
* 当该行中有单元格的数据为空或不合法时,忽略该行的数据
*
* @param row 行数据
* @return 解析后的行数据对象,行数据错误时返回null
*/
private KpiAssginVO convertRowToData(Row row) {
KpiAssginVO resultData = new KpiAssginVO();
Cell cell;
int cellNum = 0;
// 获取年份
cell = row.getCell(cellNum++);
String year = convertCellValueToString(cell);
if(!year.endsWith("年")){
year = year + "年";
}
resultData.setYear(year);
// 获取类目
cell = row.getCell(cellNum++);
String category = convertCellValueToString(cell);
resultData.setCategory(category);
// 获取KPI
cell = row.getCell(cellNum++);
String kpiName = convertCellValueToString(cell);
resultData.setKpiName(kpiName);
// 获取必保目标
cell = row.getCell(cellNum++);
String guaranteedTarget = convertCellValueToString(cell);
if(StringUtils.isNotEmpty(guaranteedTarget)){
resultData.setGuaranteedTarget(new BigDecimal(guaranteedTarget));
}else {
resultData.setGuaranteedTarget(new BigDecimal(0));
}
// 获取挑战目标
cell = row.getCell(cellNum++);
String challengingTarget = convertCellValueToString(cell);
if (StringUtils.isNotEmpty(challengingTarget)) {
resultData.setChallengingTarget(new BigDecimal(challengingTarget));
}else {
resultData.setChallengingTarget(new BigDecimal(0));
}
// 获取开始时间
cell = row.getCell(cellNum++);
String startTime = convertCellValueToString(cell);
if(StringUtils.isNotEmpty(startTime)){
resultData.setStartTime(DateUtils.toDate(startTime,"yyyy-MM-dd"));
}else {
resultData.setStartTime(null);
}
// 获取截止时间
cell = row.getCell(cellNum++);
String endTime = convertCellValueToString(cell);
if(StringUtils.isNotEmpty(endTime)){
resultData.setEndTime(DateUtils.toDate(endTime,"yyyy-MM-dd"));
}else {
resultData.setEndTime(null);
}
// 获取责任人工号,并查出用户id
cell = row.getCell(cellNum++);
String jobNumber = convertCellValueToString(cell);
if(StringUtils.isNotEmpty(jobNumber)){
String personLiable = personJobNumberService.findByJobNumber(jobNumber);
resultData.setPersonLiable(personLiable);
}else {
resultData.setPersonLiable(null);
}
return resultData;
}
}
Controller层
@ApiOperation("批量分解")
@PostMapping("/batchAssgin")
public ResponseResult batchAssgin(MultipartFile file) {
// 检查前台数据合法性
if (null == file || file.isEmpty()) {
logger.warning("上传的Excel批量分解数据文件为空!上传时间:" + new Date());
return getErrResponseResult(1111L,"上传的Excel批量分解数据文件为空!上传时间:" + new Date());
}
try {
// 解析Excel
List<KpiAssginVO> kpiAssginVOS = excelReader.readExcel(file);
// todo 进行业务操作
String userId = getUserId();
OrgUserPO orgUserPO = orgUserService.findByUserId(userId);
if(ObjectUtils.isEmpty(orgUserPO)){
throw new RuntimeException("当前用户不存在,请确认后重试!");
}
for (KpiAssginVO kpiAssginVO : kpiAssginVOS) {
KpiAssginPO kpiAssginPO = new KpiAssginPO();
BeanUtils.copyProperties(kpiAssginVO,kpiAssginPO);
assginMethod(kpiAssginPO,orgUserPO);
}
return getOkResponseResult("批量分解成功");
} catch (Exception e) {
logger.warning(e.getMessage());
return getErrResponseResult(1111L,e.getMessage());
}
}