java上传excel以及解析

一、前言

在写管理后台的需求的时候，经常会用到上传excel的功能，需要我们解析Excel的内容，导入数据等。

二、上传

上传到文件服务器，文件服务有相关的上传接口，需要我们调用API上传。

  @PostMapping("/v100/uploadFile")
    @ResponseBody
    public BaseResponse<String> uploadFile(HttpServletRequest request,
                                           @RequestHeader(required = false) String customerFileName) {
        BaseResponse<String> ret = new BaseResponse<String>();
        // 转换request，解析出request中的文件
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

        // 获取文件map集合
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        if (CollectionUtil.isNullOrEmpty(fileMap)) {
            ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
            ret.setMsg("上传失败，请选择文件");
            return ret;
        }

        MultipartFile file = fileMap.get("file");

        if (file.isEmpty()) {
            ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
            ret.setMsg("上传失败，请选择文件");
            return ret;
        }

        String fileName = file.getOriginalFilename();
        /**
         * 校验文件后缀
         */
        if (fileName.toLowerCase().endsWith(".csv") || fileName.toLowerCase().endsWith(".xlsx")) {
            try {
                InputStream inputStream = file.getInputStream();
                //上传私密文件 一般都是公司内部访问到，不对外公开的
                String path = FileSdk.uploadCmsFile(inputStream, fileName);
                String encryptFileCms = FileSdk.getEncryptFileCms(path);
                logger.info(encryptFileCms);
                logger.info("上传成功");
                ret.setStatus(CommonResponseCodeEnum.SUCCESS.getCode());
                ret.setData(path);
                return ret;
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
            ret.setMsg("只允许上传csv和xlsx文件");
            return ret;
        }
        ret.setStatus(CommonResponseCodeEnum.ERROR.getCode());
        ret.setMsg("上传失败");
        return ret;

    }

另一种就是上传到某个机器的文件位置：

@PostMapping("v100/uploadFile")
	@ResponseBody
	public BaseResponse<String> uploadFile(HttpServletRequest request,
			@RequestHeader(required = false) String customerFileName) {
		BaseResponse<String> ret = new BaseResponse<String>();
		// 转换request，解析出request中的文件
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

		// 获取文件map集合
		Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
		if (CollectionUtil.isNullOrEmpty(fileMap)) {
			ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
			ret.setMsg("上传失败，请选择文件");
			return ret;
		}

		MultipartFile file = fileMap.get("file");
		if (file.isEmpty()) {
			ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
			ret.setMsg("上传失败，请选择文件");
			return ret;
		}

		String fileName = ShortUUID.generate() + "_" + file.getOriginalFilename();
		FileNameVO fileNameVO = new FileNameVO();
		fileNameVO.setCustomerFileName(customerFileName);

		/**
		 * 校验文件后缀
		 */
		if (fileName.toLowerCase().endsWith(".csv") || fileName.toLowerCase().endsWith(".xlsx")) {
		   //文件全路径，比如/opt/file.csv，可以从配置中心获取全路径
			String totalFileName = xxxxxx + fileName;
			File dest = new File(totalFileName);
			File parent = dest.getParentFile();
			if (parent != null && !parent.exists()) {
				parent.mkdirs();
			}

			try {
				dest.createNewFile();
				file.transferTo(dest);
				logger.info("上传成功");
				ret.setStatus(CommonResponseCodeEnum.SUCCESS.getCode());
				ret.setData(totalFileName);
				return ret;
			} catch (IOException e) {
				logger.error(e.toString(), e);
			}
		} else {
			ret.setStatus(CommonResponseCodeEnum.PARAM_ERROR.getCode());
			ret.setMsg("只允许上传csv和xlsx文件");
			return ret;
		}
		ret.setStatus(CommonResponseCodeEnum.ERROR.getCode());
		ret.setMsg("上传失败");
		return ret;
	}

配置参数：

#\u9ed8\u8ba4\u652f\u6301\u6587\u4ef6\u4e0a\u4f20.
spring.http.multipart.enabled=true
#\u652f\u6301\u6587\u4ef6\u5199\u5165\u78c1\u76d8.
spring.http.multipart.file-size-threshold=100Mb
# \u4e0a\u4f20\u6587\u4ef6\u7684\u4e34\u65f6\u76ee\u5f55
spring.http.multipart.location=/home/workspace/file
#spring.http.multipart.location=/Users/apple/Downloads
# \u6700\u5927\u652f\u6301\u6587\u4ef6\u5927\u5c0f
spring.http.multipart.max-file-size=100Mb
# \u6700\u5927\u652f\u6301\u8bf7\u6c42\u5927\u5c0f
spring.http.multipart.max-request-size=100Mb


multipart.enabled=true
multipart.max-file-size=50mb
multipart.max-request-size=50mb

三、解析

    public static List<Integer> getUserIdFromCsvFile(String filePath) {

        Set<Integer> set = new HashSet<>();
        try {
            InputStream inputStream = FileSdk.getInputStream(filePath);
            CsvReader csvReader = new CsvReader(inputStream, Charset.forName("UTF-8"));
            csvReader.readHeaders();
            String[] head = csvReader.getHeaders(); //获取表头

            while (csvReader.readRecord()) {
                int i = Math.toIntExact(csvReader.getCurrentRecord()) + 2;
                int columnCount = csvReader.getColumnCount();
                if (columnCount > 1) {
                    logger.warn(ExcelReadErrorEnum.CODE_4.getText());
                    throw new RequestException(ExcelReadErrorEnum.CODE_4.getText());
                }

                String cell0 = csvReader.get(head[0]);
                Integer userId = null;
                if (!StringUtil.isBlank(cell0)) {
                    try {
                        userId = Integer.valueOf(cell0);
                    } catch (Exception e) {
                        logger.warn("第{}行第1列数据异常，请检查", i);
                        throw new RequestException("第" + i + "行第1列数据异常，请检查");
                    }
                }
                if (userId != null) {
                    set.add(userId);
                }
            }

            csvReader.close();

        } catch (RequestException e) {
            throw e;
        } catch (Exception e) {
            logger.error(ExcelReadErrorEnum.CODE_3.getText(), e);
            throw new RequestException(ExcelReadErrorEnum.CODE_3.getText());
        }
        if (set.size() == 0) {
            logger.error("会员编号不能为空");
            throw new RequestException("会员编号不能为空");
        }
        return new ArrayList<>(set);
    }

public static List<Integer> getUserIdFromExcelFile(String filePath) {

        Set<Integer> set = new HashSet<>();
        try {
            InputStream inputStream = FileSdk.getInputStream(filePath);
            ExcelTool excelTool = ExcelTool.getInstance();
            Workbook workbook = excelTool.getExcelWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            Iterator<Row> it = sheet.rowIterator();
            Row row = null;
            while (it.hasNext()) {
                row = it.next();
                int i = row.getRowNum() + 1;

                short lastCellNum = row.getLastCellNum();
                if (lastCellNum > 1) {
                    logger.warn(ExcelReadErrorEnum.CODE_4.getText());
                    throw new RequestException("第" + i 
                            + "行,"
                            + ExcelReadErrorEnum.CODE_4.getText());
                }

                if (i == 1) {
                    i++;
                    continue;
                }

                Cell cell0 = row.getCell(0);
                Integer userId = null;
                if (cell0 != null) {
                    try {
                        userId = getInteger(cell0);
                    } catch (Exception e) {
                        logger.warn("第{}行第1列数据异常，请检查", i);
                        throw new RequestException("第" + i + "行第1列数据异常，请检查");
                    }
                }
                if (userId != null) {
                    set.add(userId);
                }

            }
        } catch (RequestException e) {
            throw e;
        } catch (Exception e) {
            logger.error(ExcelReadErrorEnum.CODE_3.getText(), e);
            throw new RequestException(ExcelReadErrorEnum.CODE_3.getText());
        }
        if (set.size() == 0) {
            logger.error("会员编号不能为空");
            throw new RequestException("会员编号不能为空");
        }
        return new ArrayList<>(set);
    }

excelTool：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelTool {
    private static Logger logger = LoggerFactory.getLogger(ExcelTool.class);
    private static ExcelTool tool = new ExcelTool();

    public static ExcelTool getInstance() {
        Class var0 = ExcelTool.class;
        synchronized(ExcelTool.class) {
            if (tool == null) {
                tool = new ExcelTool();
            }
        }

        return tool;
    }

    private ExcelTool() {
    }

    public Workbook getExcelWorkbook(String filePath) throws Exception {
        return this.getExcelWorkbook(new File(filePath));
    }

    public Workbook getExcelWorkbook(File file) throws Exception {
        FileInputStream is = new FileInputStream(file);
        Workbook wb = this.getExcelWorkbook((InputStream)is);
        return wb;
    }

    public Workbook getExcelWorkbook(InputStream in) throws InvalidFormatException, IOException {
        Workbook wb = WorkbookFactory.create(in);
        return wb;
    }

    public String getCellContent(Sheet sheet, int rowNum, int colNum) {
        Row row = sheet.getRow(rowNum);
        String contents = "";
        if (row != null && row.getCell(colNum) != null) {
            contents = this.getCellContent(row.getCell(colNum)).trim();
        }

        return contents;
    }

    public String getCellContent(Cell cell) {
        DecimalFormat df = new DecimalFormat("0");
        if (cell == null) {
            return "";
        } else {
            int type = cell.getCellType();
            String value;
            switch(type) {
            case 0:
                value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim();
                if (value.endsWith(".0")) {
                    value = value.substring(0, value.length() - 2).trim();
                }
                break;
            case 1:
                value = cell.getStringCellValue().toString().trim();
                break;
            case 2:
                try {
                    value = String.valueOf(df.format(cell.getNumericCellValue())).toString().trim();
                    break;
                } catch (Exception var6) {
                    logger.info("处理公式单元格失败！");
                    value = cell.getStringCellValue();
                    return value;
                }
            case 3:
            default:
                value = cell.toString().trim();
                break;
            case 4:
                value = String.valueOf(cell.getBooleanCellValue()).toString().trim();
            }

            return value;
        }
    }

    public boolean isNullRow(Sheet sheet, int rowNum) {
        int rowCount = sheet.getLastRowNum();
        return rowCount < rowNum;
    }

    public int getSheetCount(Workbook wb) {
        return wb.getNumberOfSheets();
    }

    public int getSheetRows(Sheet sheet) {
        return sheet.getPhysicalNumberOfRows();
    }

    public HSSFWorkbook createWorkbook() {
        HSSFWorkbook wb = new HSSFWorkbook();
        return wb;
    }

    public HSSFSheet createSheet(HSSFWorkbook wb, String sheetName) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        return sheet;
    }

    public HSSFWorkbook createHead(HSSFWorkbook wb, HSSFSheet sheet, String[] heads) {
        HSSFRow row = sheet.createRow(0);

        for(int i = 0; i < heads.length; ++i) {
            HSSFCell cell = row.createCell(i);
            cell.setCellType(1);
            cell.setCellValue(new HSSFRichTextString(heads[i]));
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment((short)2);
            cellStyle.setVerticalAlignment((short)1);
            cellStyle.setWrapText(true);
            HSSFFont font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeight((short)250);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
        }

        return wb;
    }

    public HSSFWorkbook createRow(HSSFWorkbook wb, HSSFSheet sheet, String[] params) {
        HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);

        for(int i = 0; i < params.length; ++i) {
            HSSFCell cell = row.createCell(i);
            cell.setCellType(1);
            cell.setCellValue(new HSSFRichTextString(params[i]));
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment((short)2);
            cellStyle.setVerticalAlignment((short)1);
            cellStyle.setWrapText(true);
            HSSFFont font = wb.createFont();
            font.setFontHeight((short)250);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i);
        }

        return wb;
    }

    public void exportExcel(HSSFWorkbook wb, String fileName) {
        FileOutputStream fos = null;

        try {
            fos = new FileOutputStream(new File(fileName));
            wb.write(fos);
        } catch (FileNotFoundException var15) {
            logger.error("文件未找到,fileName" + fileName, var15);
        } catch (IOException var16) {
            logger.error("导出失败", var16);
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException var14) {
            }

        }

    }
}

public enum ExcelReadErrorEnum {

    CODE_1(1, "文件不存在"),

    CODE_2(2, "文件超过限制大小"),

    CODE_3(3, "文件解析失败 请下载指定excel模板后，重新上传"),

    CODE_4(3, "数据请在第一列填写，请检查");

    private Integer code;
    private String text;

    ExcelReadErrorEnum(Integer code, String text) {
        this.code = code;
        this.text = text;
    }

    public Integer getCode() {
        return code;
    }

    public String getText() {
        return text;
    }

    public static String getTextByCode(Integer code) {
        if (StringUtil.isBlank(code)) {
            return "";
        }
        for (ExcelReadErrorEnum enumOne : ExcelReadErrorEnum.values()) {
            if (enumOne.getCode().equals(code)) {
                return enumOne.getText();
            }
        }
        return "";
    }
}

public class RequestException extends RuntimeException {

    public RequestException() {
        super();
    }

    public RequestException(String message) {
        super(message);
    }

    public RequestException(String message, Throwable cause) {
        super(message, cause);
    }

    public RequestException(Throwable cause) {
        super(cause);
    }

}

四、需要注意的点

1.nginx的配置，nginx配置了每次请求的最大值，可以修改client_max_body_size 200mb来解决