历史导出

whyclxw

2025-02-13 f4739452add5718a1e4d55172ba85c4d779747d7

历史导出

 src/main/java/com/whyc/controller/ExportFileController.java

New file
@@ -0,0 +1,25 @@
package com.whyc.controller;

import com.whyc.service.ExportService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;

@RestController
@Api(tags = "导出文件")
@RequestMapping("export")
public class ExportFileController {
    @Autowired
    private ExportService service;

    @GetMapping("exportHis")
    @ApiOperation(value = "历史数据导出")
    public void exportHis(@RequestParam int lockId, @RequestParam String startTime, @RequestParam String endTime,HttpServletRequest req, HttpServletResponse resp ) throws ParseException {
        service.exportHis(lockId,startTime,endTime,req,resp);
    }
}

 src/main/java/com/whyc/service/ExportService.java

New file
@@ -0,0 +1,121 @@
package com.whyc.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.whyc.mapper.LockInfMapper;
import com.whyc.pojo.db_area.LockInf;
import com.whyc.pojo.db_lock_his.LockHis;
import com.whyc.util.ActionUtil;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class ExportService {
    @Autowired
    private SubTablePageInfoUtil util;

    @Autowired(required = false)
    private LockInfMapper linfMapper;

    //历史数据导出
    public void exportHis(int lockId, String startTime, String endTime, HttpServletRequest req, HttpServletResponse resp) throws ParseException {
        LockHis his=new LockHis();
        his.setLockId(lockId);
        List<LockHis> list=util.getLockHisWithReal(ThreadLocalUtil.parse(startTime,1),ThreadLocalUtil.parse(endTime,1)
                ,"db_lock_his","tb_lock_his_"+lockId,his);
        //获取锁的信息
        LockInf linf=linfMapper.getlinfBylockId(lockId);

        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "bl-"+nowFormat;

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("锁的基础信息");

        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        int rowNumSheet = 0;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("所属区域："+linf.getAreaName());
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("锁的名称："+linf.getLockName());
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("锁的类型：实体锁");
        rowNumSheet++;

        sheet.createRow(rowNumSheet);
        switch (linf.getLockState()){
            case -1:sheet.getRow(rowNumSheet).createCell(0).setCellValue("锁的状态：未安装");break;
            case 0:sheet.getRow(rowNumSheet).createCell(0).setCellValue("锁的状态：闭锁");break;
            case 1:sheet.getRow(rowNumSheet).createCell(0).setCellValue("锁的状态：开锁");break;
        }
        switch (linf.getLockOnline()){
            case 0:sheet.getRow(rowNumSheet).createCell(1).setCellValue("锁的状态：离线");break;
            case 1:sheet.getRow(rowNumSheet).createCell(1).setCellValue("锁的状态：在线");break;
        }
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("锁具开启次数："+linf.getLockOpenCount());
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("上一次开锁时间："+ThreadLocalUtil.format(linf.getLastUpdateTime(),1));

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("屏柜类型："+linf.getScreenBoxType());
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("屏柜品牌："+linf.getScreenBoxProduct());

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("开始时间:"+startTime);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("结束时间:"+endTime);

        XSSFSheet sheet1 = wb.createSheet("历史数据");
        //抬头
        int rowNum = 0;
        XSSFRow row = sheet1.createRow(rowNum);
        row.createCell(0).setCellValue("记录时间(yyyy-MM-dd HH:mm:ss)");
        row.createCell(1).setCellValue("锁具状态");
        row.createCell(2).setCellValue("开锁方式");
        rowNum++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            LockHis lock=list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(ThreadLocalUtil.format(lock.getRecordTime(),1));
            sheet1.getRow(rowNum).createCell(1).setCellValue(lock.getLockState()==0?"闭锁":lock.getLockState()==1?"开锁":"未知");
            switch (lock.getUnlockType()){
                case 0:sheet1.getRow(rowNum).createCell(2).setCellValue("485开锁");break;
                case 1:sheet1.getRow(rowNum).createCell(2).setCellValue("刷卡开锁");break;
                case 2:sheet1.getRow(rowNum).createCell(2).setCellValue("蓝牙开锁");break;
                case 3:sheet1.getRow(rowNum).createCell(2).setCellValue("DI开锁");break;
            }
            rowNum++;
        }

        rowNum++;

        try {
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
                    + new String(excelName.getBytes("UTF-8"), "ISO8859-1")
                    + ".xlsx");
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}