最近接了一个外包项目,其中一个功能是按条件查询数据后展示表格,并能按条件查询后导出数据到excel表格中,他这个项目的数据量不大,项目又要的紧,我就按最简单的方式实现了此功能,重要代码如下所示:
pom.xml中导入如下价包依赖:
<!-- excel导出工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
设备数据实体类
package com.bootdo.system.domain;
import java.io.Serializable;
import java.util.Date;
/**
* 设备数据实体类
* @author xhz
*
*/
public class DevmonitorDataDO implements Serializable {
private static final long serialVersionUID = 1L;
private int id;//主键
private int srcDevId;//发送者ID
private int descDevId;//接收者ID
private int cmdId;//指令
private int cmdType;//指令类型
private int respCode;//指令响应码
private int proHeader;//协议头
private int addr;//地址位
private int readStatus;//读取状态位
private int atmos;//大气压
private int radiation;//辐射值
private int airTemperature;//空气温度
private int airHumidity;//空气湿度
private int soilMoisture1;//土壤1水分
private int soilTemperature1;//土壤1温度
private int soilMoisture2;//土壤2水分
private int soilTemperature2;//土壤2温度
private int soilMoisture3;//土壤3水分
private int soilTemperature3;//土壤3温度
private int soilMoisture4;//土壤4水分
private int soilTemperature4;//土壤4温度
private int electric;//电池电量
private int myear;//年份
private int mmonth;//月份
private int mday;//日期
private int mhour;//时
private int mmin;//分
private int bak;//预留
private int ver;//版本号
private int crc;//数据效验
private Date monitorTime;//监测时间
private Date beginMonitorTime;//开始监测时间
private Date endMonitorTime;//结束监测时间
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getSrcDevId() {
return srcDevId;
}
public void setSrcDevId(int srcDevId) {
this.srcDevId = srcDevId;
}
public int getDescDevId() {
return descDevId;
}
public void setDescDevId(int descDevId) {
this.descDevId = descDevId;
}
public int getCmdId() {
return cmdId;
}
public void setCmdId(int cmdId) {
this.cmdId = cmdId;
}
public int getCmdType() {
return cmdType;
}
public void setCmdType(int cmdType) {
this.cmdType = cmdType;
}
public int getRespCode() {
return respCode;
}
public void setRespCode(int respCode) {
this.respCode = respCode;
}
public int getProHeader() {
return proHeader;
}
public void setProHeader(int proHeader) {
this.proHeader = proHeader;
}
public int getAddr() {
return addr;
}
public void setAddr(int addr) {
this.addr = addr;
}
public int getReadStatus() {
return readStatus;
}
public void setReadStatus(int readStatus) {
this.readStatus = readStatus;
}
public int getAtmos() {
return atmos;
}
public void setAtmos(int atmos) {
this.atmos = atmos;
}
public int getRadiation() {
return radiation;
}
public void setRadiation(int radiation) {
this.radiation = radiation;
}
public int getAirTemperature() {
return airTemperature;
}
public void setAirTemperature(int airTemperature) {
this.airTemperature = airTemperature;
}
public int getAirHumidity() {
return airHumidity;
}
public void setAirHumidity(int airHumidity) {
this.airHumidity = airHumidity;
}
public int getSoilMoisture1() {
return soilMoisture1;
}
public void setSoilMoisture1(int soilMoisture1) {
this.soilMoisture1 = soilMoisture1;
}
public int getSoilTemperature1() {
return soilTemperature1;
}
public void setSoilTemperature1(int soilTemperature1) {
this.soilTemperature1 = soilTemperature1;
}
public int getSoilMoisture2() {
return soilMoisture2;
}
public void setSoilMoisture2(int soilMoisture2) {
this.soilMoisture2 = soilMoisture2;
}
public int getSoilTemperature2() {
return soilTemperature2;
}
public void setSoilTemperature2(int soilTemperature2) {
this.soilTemperature2 = soilTemperature2;
}
public int getSoilMoisture3() {
return soilMoisture3;
}
public void setSoilMoisture3(int soilMoisture3) {
this.soilMoisture3 = soilMoisture3;
}
public int getSoilTemperature3() {
return soilTemperature3;
}
public void setSoilTemperature3(int soilTemperature3) {
this.soilTemperature3 = soilTemperature3;
}
public int getSoilMoisture4() {
return soilMoisture4;
}
public void setSoilMoisture4(int soilMoisture4) {
this.soilMoisture4 = soilMoisture4;
}
public int getSoilTemperature4() {
return soilTemperature4;
}
public void setSoilTemperature4(int soilTemperature4) {
this.soilTemperature4 = soilTemperature4;
}
public int getElectric() {
return electric;
}
public void setElectric(int electric) {
this.electric = electric;
}
public int getMyear() {
return myear;
}
public void setMyear(int myear) {
this.myear = myear;
}
public int getMmonth() {
return mmonth;
}
public void setMmonth(int mmonth) {
this.mmonth = mmonth;
}
public int getMday() {
return mday;
}
public void setMday(int mday) {
this.mday = mday;
}
public int getMhour() {
return mhour;
}
public void setMhour(int mhour) {
this.mhour = mhour;
}
public int getMmin() {
return mmin;
}
public void setMmin(int mmin) {
this.mmin = mmin;
}
public int getBak() {
return bak;
}
public void setBak(int bak) {
this.bak = bak;
}
public int getVer() {
return ver;
}
public void setVer(int ver) {
this.ver = ver;
}
public int getCrc() {
return crc;
}
public void setCrc(int crc) {
this.crc = crc;
}
public Date getMonitorTime() {
return monitorTime;
}
public void setMonitorTime(Date monitorTime) {
this.monitorTime = monitorTime;
}
public Date getBeginMonitorTime() {
return beginMonitorTime;
}
public void setBeginMonitorTime(Date beginMonitorTime) {
this.beginMonitorTime = beginMonitorTime;
}
public Date getEndMonitorTime() {
return endMonitorTime;
}
public void setEndMonitorTime(Date endMonitorTime) {
this.endMonitorTime = endMonitorTime;
}
}
设备数据dao层
package com.bootdo.system.dao;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import com.bootdo.system.domain.DevmonitorDataDO;
/**
* 设备数据dao层
* @author xhz
*
*/
@Mapper
public interface DevmonitorDataDao {
/**
* 查询设备数据列表
* @param map
* @return
*/
List<DevmonitorDataDO> list(Map<String,Object> map);
}
设备数据service层
package com.bootdo.system.service;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import com.bootdo.system.domain.DevmonitorDataDO;
/**
* 设备数据service层
* @author xhz
*
*/
public interface DevmonitorDataService {
/**
* 查询设备数据列表
* @param map
* @return
*/
List<DevmonitorDataDO> list(Map<String,Object> map);
/**
* 导出数据到excel
* @param response
* @param fileName
* @param params
* @return
*/
Boolean exportExcel(HttpServletResponse response, String fileName, Map<String,Object> params);
}
设备数据service层实现类
package com.bootdo.system.service.impl;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.bootdo.system.dao.DevmonitorDataDao;
import com.bootdo.system.domain.DevmonitorDataDO;
import com.bootdo.system.service.DevmonitorDataService;
/**
* 设备数据service层实现类
* @author xhz
*
*/
@Service
public class DevmonitorDataServiceImpl implements DevmonitorDataService{
@Autowired
private DevmonitorDataDao devmonitorDataDao;
/**
* 查询设备数据列表
*/
@Override
public List<DevmonitorDataDO> list(Map<String, Object> map) {
return devmonitorDataDao.list(map);
}
/**
* 导出数据到excel
*/
@Override
public Boolean exportExcel(HttpServletResponse response, String fileName, Map<String,Object> params) {
try {
List<DevmonitorDataDO> devmonitorDataList = devmonitorDataDao.list(params);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(fileName);
HSSFRow row = null;
row = sheet.createRow(0);//创建第一个单元格
row.setHeight((short) (22.25 * 20));
row.createCell(0).setCellValue("设备编号");
row.createCell(1).setCellValue("检测时间");
row.createCell(2).setCellValue("大气压(KPa)");
row.createCell(3).setCellValue("辐射值(W/㎡)");
row.createCell(4).setCellValue("空气温度(℃)");
row.createCell(5).setCellValue("空气湿度(%)");
row.createCell(6).setCellValue("土壤1水分(%)");
row.createCell(7).setCellValue("土壤1温度(℃)");
row.createCell(8).setCellValue("土壤2水分(%)");
row.createCell(9).setCellValue("土壤2温度(℃)");
row.createCell(10).setCellValue("土壤3水分(%)");
row.createCell(11).setCellValue("土壤3温度(℃)");
row.createCell(12).setCellValue("土壤4水分(%)");
row.createCell(13).setCellValue("土壤4温度(℃)");
row.createCell(14).setCellValue("电池电量(%)");
row.createCell(15).setCellValue("年");
row.createCell(16).setCellValue("月");
row.createCell(17).setCellValue("日");
row.createCell(18).setCellValue("时");
row.createCell(19).setCellValue("分");
row.createCell(20).setCellValue("预留");
row.createCell(21).setCellValue("版本号");
row.createCell(22).setCellValue("数据效验");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置日期格式
//遍历所获取的数据
for (int i = 0; i < devmonitorDataList.size(); i++) {
row = sheet.createRow(i + 1);
DevmonitorDataDO devmonitorData = devmonitorDataList.get(i);
row.createCell(0).setCellValue(devmonitorData.getSrcDevId());
row.createCell(1).setCellValue(simpleDateFormat.format(devmonitorData.getMonitorTime()));
row.createCell(2).setCellValue(devmonitorData.getAtmos()/ 10);
row.createCell(3).setCellValue(devmonitorData.getRadiation());
row.createCell(4).setCellValue(devmonitorData.getAirTemperature()/ 10);
row.createCell(5).setCellValue(devmonitorData.getAirHumidity()/ 10);
row.createCell(6).setCellValue(devmonitorData.getSoilMoisture1()/ 10);
row.createCell(7).setCellValue(devmonitorData.getSoilTemperature1()/ 10);
row.createCell(8).setCellValue(devmonitorData.getSoilMoisture2()/ 10);
row.createCell(9).setCellValue(devmonitorData.getSoilTemperature2()/ 10);
row.createCell(10).setCellValue(devmonitorData.getSoilMoisture3()/ 10);
row.createCell(11).setCellValue(devmonitorData.getSoilTemperature3()/ 10);
row.createCell(12).setCellValue(devmonitorData.getSoilMoisture4()/ 10);
row.createCell(13).setCellValue(devmonitorData.getSoilTemperature4()/ 10);
row.createCell(14).setCellValue(devmonitorData.getElectric());
row.createCell(15).setCellValue(devmonitorData.getMyear());
row.createCell(16).setCellValue(devmonitorData.getMmonth());
row.createCell(17).setCellValue(devmonitorData.getMday());
row.createCell(18).setCellValue(devmonitorData.getMhour());
row.createCell(19).setCellValue(devmonitorData.getMmin());
row.createCell(20).setCellValue(devmonitorData.getBak());
row.createCell(21).setCellValue(devmonitorData.getVer());
row.createCell(22).setCellValue(devmonitorData.getCrc());
}
sheet.setDefaultRowHeight((short) (16.5 * 20));
//列宽自适应
for (int i = 0; i <= 13; i++) {
sheet.autoSizeColumn(i);
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-disposition", "attachment;filename="+fileName+"-"+simpleDateFormat.format(new Date())+".xls");//默认Excel名称
wb.write(os);
os.flush();
os.close();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
}
controller层
package com.bootdo.system.controller;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.bootdo.common.annotation.Log;
import com.bootdo.common.config.Constant;
import com.bootdo.common.controller.BaseController;
import com.bootdo.common.utils.PageUtils;
import com.bootdo.common.utils.Query;
import com.bootdo.common.utils.R;
import com.bootdo.system.domain.DevinfoDO;
import com.bootdo.system.domain.DevmonitorDataDO;
import com.bootdo.system.domain.FileLogDO;
import com.bootdo.system.service.DevinfoService;
import com.bootdo.system.service.DevmonitorDataService;
import com.bootdo.system.service.FileLogService;
import io.swagger.annotations.ApiOperation;
/**
* 设备信息管理controller层
* @author xhz
*
*/
@Controller
@RequestMapping("/sys/devinfo")
public class DevinfoController extends BaseController {
private String prefix = "system/devinfo";
@Autowired
private DevinfoService devinfoService;
@Autowired
private DevmonitorDataService devmonitorDataService;
@Autowired
private FileLogService fileLogService;
@ApiOperation(value="导出excel", notes="")
@ResponseBody
@GetMapping("/exportExcel")
@RequiresPermissions("sys:devinfo:table")
public String exportExcel(HttpServletResponse response,
@RequestParam("srcDevId") String srcDevId,
@RequestParam("beginMonitorTime") String beginMonitorTime,
@RequestParam("endMonitorTime") String endMonitorTime) {
try {
Map<String,Object> params=new HashMap<String, Object>();
params.put("srcDevId", srcDevId);
params.put("beginMonitorTime", beginMonitorTime);
params.put("endMonitorTime", endMonitorTime);
Boolean isOk = devmonitorDataService.exportExcel(response, srcDevId, params);
if (isOk) {
return "导出成功";
} else {
return "导出失败";
}
} catch (Exception e) {
e.printStackTrace();
return "导出异常";
}
}
}
启动项目运行,直接切到导出Excel表格模块,如下所示:
按条件查询总共有5条数据,点击右上方“导出excel”,导出后的excel中的数据如下所示:
OK,到此为止,简单的将数据导出excel表格的功能就实现了,细节待优化。