Excel数据解析

Excel解析

pom文件导入jar包

       <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>

Controller层代码

@Slf4j
@RestController
@RequestMapping(value = "/excel")
@Api(tags = "excel",description = "数据解析")
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @PostMapping("/import")
    @ApiOperation(value = "解析Excel文件",notes = "解析Excel文件",httpMethod = "POST")
    public Response thermFactory(@RequestParam("file")MultipartFile file){
        return new Response.Builder()
                .withResult(excelService.analyzeData(file))
                .build();
    }
}

Service层代码

同时在代码中进行事务管理，如果导入的Excel表格中或者数据库中有相同的数据则进行手动回滚，并提示相同的数据

@Autowired
    private CustomerDAO customerDAO;

    @Transactional(rollbackFor = Exception.class)
    public List<String> analyzeData(MultipartFile file) {
        try(InputStream stream = file.getInputStream()) {
            Workbook wb = new XSSFWorkbook(stream);
            Sheet sheet = wb.getSheetAt(0);
            List<ThermFactoryPO> list = new ArrayList<>();
            int firstRowIndex = sheet.getFirstRowNum() + 1;
            int lastRowIndex = sheet.getLastRowNum();
            SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                Row row = sheet.getRow(rIndex);
                if (row != null) {
                    ThermFactoryPO thermFactoryPO = new ThermFactoryPO();
                    ArrayList<String> strings = new ArrayList<>();
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();
                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {
                        Cell cell = row.getCell(cIndex);
                        if (cell.getCellType() == CellType.STRING) {
                            strings.add(cell.toString());
                        } else if (cell.getCellType() == CellType.NUMERIC) {
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                strings.add(simpleDateFormat.format(cell.getDateCellValue()));
                            } else {
                                long numericCellValue = (long)cell.getNumericCellValue();
                                strings.add(numericCellValue + "");
                            }
                        }
                    }
                    thermFactoryPO.setImportTime(simpleDateFormat1.format(System.currentTimeMillis()));
                    thermFactoryPO.setThermId(strings.get(0));
                    thermFactoryPO.setModel(strings.get(1));
                    thermFactoryPO.setSsid(strings.get(2));
                    thermFactoryPO.setFactory(strings.get(3));
                    thermFactoryPO.setDeliveryTime(strings.get(4));
                    thermFactoryPO.setThermMac(strings.get(5));
                    thermFactoryPO.setPassword(strings.get(6));
                    thermFactoryPO.setSocketurl(strings.get(7));
                    thermFactoryPO.setLiences(strings.get(8));
                    list.add(thermFactoryPO);
                }
            }
            excelDAO.save(list);
            List<String> thermIds = excelDAO.getIds();
            if (thermIds.size() != 0){
               //手动回滚
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return thermIds;
            }else {
                return new ArrayList<String>();
            }
        } catch (IOException e) {
            log.error("file parse error:{}", e.getMessage());
        }
        return null;
    }

Dao层代码

 /**
     * 保存表格信息
     * @param list
     * @return
     */
     int save(@Param("list")List<ThermFactoryPO> list);

     /**
      * 判断导入的数据，是否重复
      * @param
      * @return
      */
      List<String> getIds();

Mapper文件

 <insert id="save" parameterType="com.hr.admin.po.ThermFactoryPO" >
        INSERT INTO thermostatfactory(therm_id,therm_mac,model,ssid,password,factory,delivery_time) VALUES
        <foreach collection="list" item="factoryPO" separator=",">
            (#{factoryPO.thermId},#{factoryPO.thermMac},#{factoryPO.model},#{factoryPO.ssid},#{factoryPO.password},#{factoryPO.factory},#{factoryPO.deliveryTime})
        </foreach>
    </insert>

<select id="getIds" resultType="java.lang.String">
      select therm_id from thermostatfactory group by therm_id having count(therm_id) >1;
    </select>

实体类

@Data
public class ThermFactoryPO {
    private String deliveryTime;

    private String factory;

    private String model;

    private String password;

    private String ssid;

    private String thermId;

    private String thermMac;

    private Long id;
}

注意点

导入的Excel表格中数据列要和service层中的代码进行对应，否则将导致导入错误或者数据错乱。