package com.whyc.service;
|
|
import com.whyc.mapper.LockInfMapper;
|
import com.whyc.pojo.plus_inf.LockInf;
|
import com.whyc.pojo.plus_lock_his.LockHis;
|
import com.whyc.util.SubTablePageInfoUtil;
|
import com.whyc.util.ThreadLocalUtil;
|
import org.apache.poi.ss.usermodel.*;
|
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)
|
,"plus_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("锁的基础信息");
|
// 创建一个单元格样式，并设置字体大小为5
|
sheet.setDefaultColumnWidth(20);
|
sheet.setDefaultRowHeightInPoints(20);
|
|
int rowNumSheet = 0;
|
Row row1 = sheet.createRow(rowNumSheet);
|
row1.setHeightInPoints(20.0f);
|
row1.createCell(0).setCellValue("所属机房："+linf.getStationName());
|
row1.createCell(1).setCellValue("锁的名称："+linf.getLockName());
|
rowNumSheet++;
|
|
Row row2 = sheet.createRow(rowNumSheet);
|
row2.setHeightInPoints(20.0f);
|
switch (linf.getLockState()){
|
case 0:row2.createCell(0).setCellValue("锁的状态：闭锁");break;
|
case 1:row2.createCell(0).setCellValue("锁的状态：开锁");break;
|
}
|
switch (linf.getLockOnline()){
|
case 0:row2.createCell(1).setCellValue("锁的状态：离线");break;
|
case 1:row2.createCell(1).setCellValue("锁的状态：在线");break;
|
}
|
rowNumSheet++;
|
Row row3 = sheet.createRow(rowNumSheet);
|
row3.setHeightInPoints(20.0f);
|
row3.createCell(0).setCellValue("锁具开启次数："+linf.getLockOpenCount());
|
row3.createCell(1).setCellValue("上一次开锁时间："+ThreadLocalUtil.format(linf.getLastUpdateTime(),1));
|
|
rowNumSheet++;
|
Row row4 = sheet.createRow(rowNumSheet);
|
row4.setHeightInPoints(20.0f);
|
row4.createCell(0).setCellValue("屏柜类型："+linf.getScreenBoxType());
|
row4.createCell(1).setCellValue("屏柜品牌："+linf.getScreenBoxProduct());
|
|
rowNumSheet++;
|
Row row5 = sheet.createRow(rowNumSheet);
|
row5.setHeightInPoints(20.0f);
|
row5.createCell(0).setCellValue("开始时间:"+startTime);
|
row5.createCell(1).setCellValue("结束时间:"+endTime);
|
|
XSSFSheet sheet1 = wb.createSheet("历史数据");
|
sheet1.setDefaultColumnWidth(20);
|
sheet1.setDefaultRowHeightInPoints(20);
|
//抬头
|
int rowNum = 0;
|
Row row6 = sheet1.createRow(rowNum);
|
row6.setHeightInPoints(20.0f);
|
row6.createCell(0).setCellValue("记录时间(yyyy-MM-dd HH:mm:ss)");
|
row6.createCell(1).setCellValue("锁具状态");
|
row6.createCell(2).setCellValue("开锁方式");
|
rowNum++;
|
//数据栏
|
for (int i = 0; i < list.size(); i++) {
|
Row row=sheet1.createRow(rowNum); //创建行
|
LockHis lock=list.get(i);
|
row.createCell(0).setCellValue(ThreadLocalUtil.format(lock.getRecordTime(),1));
|
row.createCell(1).setCellValue(lock.getLockState()==0?"闭锁":lock.getLockState()==1?"开锁":"未知");
|
switch (lock.getUnlockType()){
|
case 0:row.createCell(2).setCellValue("485开锁");break;
|
case 1:row.createCell(2).setCellValue("刷卡开锁");break;
|
case 2:row.createCell(2).setCellValue("蓝牙开锁");break;
|
case 3:row.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();
|
}
|
}
|
}
|
