导入数据的工具类PoiExcelImport、以及后台如何运用。

工具类:

package com.nbpi.company.modules.train.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;

public class PoiExcelImport {

    /**
     * 检验文件是否有效
     *
     * @param file
     * @throws Exception
     */
    public static void checkFile(MultipartFile file) throws Exception {
        // 判断文件是否存在
        if (null == file) {
            throw new FileNotFoundException("文件不存在！");
        }
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            throw new IOException(fileName + "不是excel文件");
        }
    }


    /**
     * 获取workbook
     *
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        // 获得文件名
        String fileName = file.getOriginalFilename();
        // 创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            // 获取excel文件的io流
            InputStream is = file.getInputStream();
            // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                // 2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                // 2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {

        }
        return workbook;
    }

    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case _NONE:
                return "";
            case BOOLEAN:
                return cell.getBooleanCellValue() ? "是" : "否";
            case NUMERIC:
                return Double.valueOf(new BigDecimal(cell.getNumericCellValue()).doubleValue()).toString();
            case STRING:
                return cell.getStringCellValue().replaceAll("\\n", "");
            case BLANK:
                return "";
            case ERROR:
                return "";
            case FORMULA:
                return "";
            default:
                return "";
        }
    }
}

后端如何运用：

/*
    导入数据
    * */
    @PostMapping({"/importStdFile"})
    @JsonResultAnnotation
    @AssertLoginAnnotation
    public JsonResult importStdFile(MultipartHttpServletRequest request, int projectId) {

        Iterator<String> iterator = request.getFileNames();
        while (iterator.hasNext()) {
            try (Workbook workBook = PoiExcelImport.getWorkBook(request.getFile(iterator.next()));) {
                Sheet sheet = workBook.getSheetAt(0);
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    Row row = sheet.getRow(i);
                    
                    //开始给实体类赋值
                    
                    BusinessPoint businessPoint = new BusinessPoint();
                    //PoiExcelImport.getCellValue(row.getCell(0));为表的第一列的数据
                    String adress = PoiExcelImport.getCellValue(row.getCell(0));
                    Integer integer = jpaQueryFactory.select(qUnit.id).from(qUnit).where(qUnit.name.eq(adress)).fetchOne();
                    if (integer != null) {
                        businessPoint.setAdress(integer);
                    }
                    businessPoint.setPointName(PoiExcelImport.getCellValue(row.getCell(1)));
                    businessPoint.setPointAdress(PoiExcelImport.getCellValue(row.getCell(2)));
                    String sblx = PoiExcelImport.getCellValue(row.getCell(3));
                    Integer integer1 = jpaQueryFactory.select(qSmCode.id).from(qSmCode).where(qSmCode.name.eq(sblx)).fetchOne();
                    if (integer1 != null) {
                        businessPoint.setEquipmentType(integer1);
                    }
                    String cds = PoiExcelImport.getCellValue(row.getCell(4));
                    String[] split = cds.split("\\.");

                    if ("无".equals(cds)) {
                        businessPoint.setLaneNum(0);
                    } else {
                        Integer integer2 = jpaQueryFactory.select(qSmCode.id).from(qSmCode).where(qSmCode.name.eq(split[0])).fetchOne();
                        if (integer2 != null) {
                            businessPoint.setLaneNum(integer2);
                        }
                    }
                    String azfs = PoiExcelImport.getCellValue(row.getCell(5));
                    Integer integer3 = jpaQueryFactory.select(qSmCode.id).from(qSmCode).where(qSmCode.name.eq(azfs)).fetchOne();
                    if (integer3 != null) {
                        businessPoint.setInstallationType(integer3);
                    }
                    businessPoint.setPoleNews(PoiExcelImport.getCellValue(row.getCell(6)));
                    String azgd = PoiExcelImport.getCellValue(row.getCell(7));
                    Integer integer4 = jpaQueryFactory.select(qSmCode.id).from(qSmCode).where(qSmCode.name.eq(azgd)).fetchOne();
                    if (integer4 != null) {
                        businessPoint.setBatHeight(integer4);
                    }
                    String cellValue = PoiExcelImport.getCellValue(row.getCell(8));
                    businessPoint.setBoomLength(Double.valueOf(cellValue));
                    businessPoint.setMonitorAdress(PoiExcelImport.getCellValue(row.getCell(9)));
                    String isLight = PoiExcelImport.getCellValue(row.getCell(10));
                    if ("是".equals(isLight)) {
                        businessPoint.setIsLight(true);
                    } else {
                        businessPoint.setIsLight(false);
                    }
                    String person = PoiExcelImport.getCellValue(row.getCell(11));
                    Integer integer5 = jpaQueryFactory.select(qUser.id).from(qUser).where(qUser.name.eq(person)).fetchOne();
                    if (integer5 != null) {
                        businessPoint.setPerson(integer5);
                    }
                    businessPoint.setApprovalState(11);
                    businessPoint.setProjectId(projectId);
                    String imgWallGuid = GuidHelper.CreateGuid();
                    businessPoint.setImageGuid(imgWallGuid);
                    
                    //赋值完毕保存
                    
                    businessPointDao.getRepository().save(businessPoint);
                    return JsonHelper.toJson("导入成功");

                }
            } catch (IOException e) {
                log.error("[{}]", e);
            } catch (Exception e) {
                return JsonHelper.toJson("导入失败");
            }
        }
        return JsonHelper.toJson("导入成功");
    }