将excel模板数据导入数据库

 @Override
    public ResponseEntity<ResultInfo<Object>> userImportExcel(MultipartFile dataFile) throws IOException {
        //读取文件流
        String uploadFileFileName = dataFile.getOriginalFilename();
        String[] split = uploadFileFileName.split("\\.");
        String type = split[1];
        //判断文件类型
        if (!Constant.EXCEL_TYPE.equals(type)) {
            log.error(SystemError.SYS_10017.getMessageWithCode());
            return new ResponseEntity<>(new ResultInfo<Object>().error(SystemError.SYS_10017), HttpStatus.BAD_REQUEST);
        }
        //解析sheet
        InputStream in = dataFile.getInputStream();
        Map<String, ArrayList<ArrayList<String>>> dataMap = getResult(type, in);

 dataMap.forEach((k, v) -> {
                if (!CollectionUtils.isEmpty(v)) {
                    //解析当前sheet中数据
                    for (List<String> rowColumn : v) {
                        boolean existRecordFlag = edomsDesignInstanceDataImportService.getRecordByInstanceCodeAndPropertyCode(rowColumn.get(1), rowColumn.get(3));
                        if (existRecordFlag) {
                            continue;
                        }
                        EdomsDesignInstanceDataImportDTO edomsDesignInstanceDataImportDTO = handleEdomsDesignInstanceDataImportDTO(date, createBy, rowColumn);
                        EdomsDesignInstanceDataImportEntity entity = ConvertUtils.sourceToTarget(edomsDesignInstanceDataImportDTO, EdomsDesignInstanceDataImportEntity.class);
                        entity.setRecordId(finalRecordId);
                        insert(entity);
                    }
                }
            });
}

  private Map<String, ArrayList<ArrayList<String>>> getResult(String type, InputStream in) {
        Map<String, ArrayList<ArrayList<String>>> result = new HashMap<>();
        String xls = "xls";
        try {
            if (xls.equals(type)) {
                result = CommonUtilsTwoLine.readExcelXLS(in, 1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

package com.siact.product.edoms.common.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;

import java.io.BufferedInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class CommonUtilsTwoLine {

    /**
     * 读取excel类型为ExcelXLS
     * @param in
     * @return
     * @throws Exception
     */
    public static Map<String, ArrayList<ArrayList<String>>> readExcelXLS(InputStream in,int ignoreRow) throws Exception {
        BufferedInputStream buf = new BufferedInputStream(in);
        POIFSFileSystem poiFileSystem = new POIFSFileSystem(buf);
        HSSFWorkbook workbook = new HSSFWorkbook(poiFileSystem);
        return readExcelForXls(workbook,ignoreRow);
    }


    /**
     * 读取excel xls后缀名文件
     *
     * @param ignoreRow
     * @return
     */
    private static Map<String, ArrayList<ArrayList<String>>> readExcelForXls(HSSFWorkbook workbook, int ignoreRow) {
        Map<String, ArrayList<ArrayList<String>>> map = new HashMap<>();

        int rowSize = 0;
        try {
            HSSFCell cell = null;
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
                ArrayList<ArrayList<String>> lists = new ArrayList<>();
                for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum()+1; rowIndex++) {
                    HSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }

                    int tempRowSize = row.getLastCellNum() + 1;
                    if (tempRowSize > rowSize) {
                        rowSize = tempRowSize;
                    }
                    ArrayList<String> list = new ArrayList<>();
                    int col = 0;
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        cell = row.getCell(colIndex);
                        String value = "";
                        if (cell != null) {
                            CellType cellType = cell.getCellTypeEnum();

                            switch (cellType) {
                                case NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        value = String.valueOf(cell.getDateCellValue());
                                    } else {
                                        value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));
                                    }
                                    break;
                                case STRING:
                                    value = String.valueOf(cell.getStringCellValue());
                                    break;
                                case FORMULA:
                                    value = String.valueOf(cell.getCellFormula());
                                    break;
                                case BLANK:
                                    value = "";
                                    break;
                                case BOOLEAN:
                                    value = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case ERROR:
                                    value = String.valueOf(cell.getErrorCellValue());
                                    break;
                                default:
                                    value = "";
                            }
                            if (StringUtils.isNotBlank(value)) {
                                list.add(value);
                            } else {
                                col++;
                            }
                        }
                    }
                    if (col == row.getRowNum()) {
                        continue;
                    }
                    if (list.size() > 0) {
                        lists.add(list);
                    }
                }
                map.put(String.valueOf(sheetIndex), lists);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }


}

    private EdomsDesignInstanceDataImportDTO handleEdomsDesignInstanceDataImportDTO(Date date, String createBy, List<String> rowColumn) {
        EdomsDesignInstanceDataImportDTO edomsDesignInstanceDataImportDTO = new EdomsDesignInstanceDataImportDTO();
        edomsDesignInstanceDataImportDTO.setInstanceName(StringUtils.isNotBlank(rowColumn.get(0)) ? rowColumn.get(0) : null);
        edomsDesignInstanceDataImportDTO.setInstanceCode(StringUtils.isNotBlank(rowColumn.get(1)) ? rowColumn.get(1) : null);
        edomsDesignInstanceDataImportDTO.setPropertyName(StringUtils.isNotBlank(rowColumn.get(2)) ? rowColumn.get(2) : null);
        edomsDesignInstanceDataImportDTO.setPropertyCode(StringUtils.isNotBlank(rowColumn.get(3)) ? rowColumn.get(3) : null);
        edomsDesignInstanceDataImportDTO.setUnit(StringUtils.isNotBlank(rowColumn.get(4)) ? rowColumn.get(4) : null);
        edomsDesignInstanceDataImportDTO.setAccuracy(StringUtils.isNotBlank(rowColumn.get(5)) ? rowColumn.get(5) : null);
        edomsDesignInstanceDataImportDTO.setPoint(StringUtils.isNotBlank(rowColumn.get(6)) ? rowColumn.get(6) : null);
        edomsDesignInstanceDataImportDTO.setDataType(StringUtils.isNotBlank(rowColumn.get(7)) ? rowColumn.get(7) : null);
        edomsDesignInstanceDataImportDTO.setAcquisitionType(StringUtils.isNotBlank(rowColumn.get(8)) ? rowColumn.get(8) : null);

        edomsDesignInstanceDataImportDTO.setCreateBy(createBy);
        edomsDesignInstanceDataImportDTO.setTenantId(tenantId);
        edomsDesignInstanceDataImportDTO.setCreateTime(date);

        return edomsDesignInstanceDataImportDTO;
    }

模板导出：

 @Override
    public ResponseEntity<ResultInfo<Object>> downLoadTemplate(HttpServletResponse response) {
        //设置文件路径
        File file;
        //判断文件是否存在
        FileInputStream fileIn = null;
        ServletOutputStream out = null;
        try {
            String path = this.getClass().getClassLoader().getResource("templateFile").getPath();
            String filePath = path + "/点位模板.xls";
            file = new File(filePath);
            //获取文件名称
            String fileName = file.getName();
            // 设置强制下载不打开
            response.setContentType("application/octet-stream");
            // 设置文件名
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            fileIn = new FileInputStream(file);
            out = response.getOutputStream();
            byte[] outputByte = new byte[1024];
            int readTmp = -1;
            while ((readTmp = fileIn.read(outputByte)) != -1) {
                //并不是每次都能读到1024个字节，所有用readTmp作为每次读取数据的长度，否则会出现文件损坏的错误
                out.write(outputByte, 0, readTmp);
            }
        } catch (Exception e) {
            return new ResponseEntity<>(new ResultInfo<Object>().error(SystemError.SYS_10019), HttpStatus.BAD_REQUEST);
        } finally {
            try {
                assert fileIn != null;
                fileIn.close();
                assert out != null;
                out.flush();
                out.close();
            } catch (Exception e) {
                log.error("关闭流失败");
            }
        }
        return new ResponseEntity<>(new ResultInfo<Object>().ok(""), HttpStatus.OK);
    }