1.在pom.xml中添加依赖
<!-- 导出excel https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.6</version>
</dependency>
2.添加excel表生成工具类ExcelUtil.java
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelUtil {
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
//创建一个HSSFWorkbook,对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
//在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
//设置列宽
sheet.setColumnWidth(0, 20 * 330);
sheet.setColumnWidth(1, 20 * 130);
sheet.setColumnWidth(2, 20 * 400);
sheet.setColumnWidth(3, 20 * 400);
sheet.setColumnWidth(4, 20 * 400);
sheet.setColumnWidth(5, 20 * 330);
sheet.setColumnWidth(6, 20 * 400);
sheet.setColumnWidth(7, 20 * 800);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
}
3.数据转换类DeviceExportBuilder.java
package com.cloudthings.maintenance.utils.export;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.aspectj.weaver.ast.And;
import com.cloudthings.maintenance.device.model.DeviceQueryModel;
public class DeviceExportBuilder {
private static String[] titles = { "设备SN号", "设备类型", "签约单位", "项目名称", "梯号", "注册代码", "所在地区", "详细地址" };
/**
* 对外接口 构建Excel表
*
* @param datas
* @param areas
* @param searchKey
*/
public static HSSFWorkbook buildExcel(List<DeviceQueryModel> datas, List<Area> areas, String searchKey) {
List<DeviceExportModel> eds = getContent(datas, areas);
// excel文件名
String fileName = "[" + searchKey + "]设备表.xls";
// sheet名
String sheetName = "设备";
String[][] content = new String[eds.size()][titles.length];
if (eds.size() > 0)
for (int i = 0; i < eds.size(); i++) {
content[i][0] = eds.get(i).getSn();
content[i][1] = eds.get(i).getDevTypeStr();
content[i][2] = eds.get(i).getBuyCompanyName();
content[i][3] = eds.get(i).getProjectName();
content[i][4] = eds.get(i).getEvOrder();
content[i][5] = eds.get(i).getRegCode();
content[i][6] = eds.get(i).getCountyName();
content[i][7] = eds.get(i).getProjectAddress();
}
// 创建HSSFWorkbook
return ExcelUtil.getHSSFWorkbook(sheetName, titles, content, null);
}
// 构建导出列表的数据
private static List<DeviceExportModel> getContent(List<DeviceQueryModel> datas, List<Area> areas) {
List<DeviceExportModel> exportModels = new ArrayList<DeviceExportBuilder.DeviceExportModel>();
if (datas != null && datas.size() > 0) {
for (DeviceQueryModel resModel : datas) {
DeviceExportModel expModel = new DeviceExportModel(resModel.getSn(), resModel.getDeviceType(),
resModel.getBuyCompanyName(), resModel.getProjectName(), resModel.getEvOrder(),
resModel.getRegCode(),
getCountyNameById(resModel.getProvince(), resModel.getCity(), resModel.getCountry(), areas),
resModel.getAddress());
exportModels.add(expModel);
}
}
return exportModels;
}
// 获取区域信息
private static String getCountyNameById(Long proId, Long cityId, Long countyId, List<Area> areas) {
if (proId == null || cityId == null || countyId == null) {
return "";
}
String proStr = "", cityStr = "", countyStr = "";
for (Area area : areas) {
if (proId.longValue() == area.getUuid().longValue()) {
proStr = area.getAreaName();
}
if (cityId.longValue() == area.getUuid().longValue()) {
cityStr = area.getAreaName();
}
if (countyId.longValue() == area.getUuid().longValue()) {
countyStr = area.getAreaName();
}
}
return (proStr + cityStr + countyStr).trim();
}
public static class DeviceExportModel {
private String sn;
private String devTypeStr;// (1.Nano、2.mix)
private String buyCompanyName;// 签约公司名称
private String projectName;
private String evOrder;
private String regCode;
private String countyName;// 所在县,区域 如:四川省成都市天府新区
private String projectAddress;
public DeviceExportModel() {
super();
}
public DeviceExportModel(String sn, Integer deviceType, String buyCompanyName, String projectName,
String evOrder, String regCode, String countyName, String projectAddress) {
super();
this.sn = sn == null ? "" : sn;
this.devTypeStr = (deviceType == null) ? "" : ((deviceType == 1) ? "Nano" : (deviceType == 2 ? "Mix" : ""));
this.buyCompanyName = buyCompanyName == null ? "" : buyCompanyName;
this.projectName = projectName == null ? "" : projectName;
this.evOrder = evOrder == null ? "" : evOrder;
this.regCode = regCode == null ? "" : regCode;
this.countyName = countyName == null ? "" : countyName;
this.projectAddress = projectAddress == null ? "" : projectAddress;
}
@Override
public String toString() {
return "DeviceExportModel [sn=" + sn + ", devTypeStr=" + devTypeStr + ", buyCompanyName=" + buyCompanyName
+ ", projectName=" + projectName + ", evOrder=" + evOrder + ", regCode=" + regCode + ", countyName="
+ countyName + ", projectAddress=" + projectAddress + "]";
}
public String getSn() {
return sn;
}
public void setSn(String sn) {
this.sn = sn;
}
public String getDevTypeStr() {
return devTypeStr;
}
public void setDevTypeStr(Integer deviceType) {// (1.Nano、2.mix)
this.devTypeStr = (deviceType == null) ? "" : ((deviceType == 1) ? "Nano" : (deviceType == 2 ? "Mix" : ""));
}
public String getBuyCompanyName() {
return buyCompanyName;
}
public void setBuyCompanyName(String buyCompanyName) {
this.buyCompanyName = buyCompanyName;
}
public String getProjectName() {
return projectName;
}
public void setProjectName(String projectName) {
this.projectName = projectName;
}
public String getEvOrder() {
return evOrder;
}
public void setEvOrder(String evOrder) {
this.evOrder = evOrder;
}
public String getRegCode() {
return regCode;
}
public void setRegCode(String regCode) {
this.regCode = regCode;
}
public String getCountyName() {
return countyName;
}
public void setCountyName(String countyName) {
this.countyName = countyName;
}
public String getProjectAddress() {
return projectAddress;
}
public void setProjectAddress(String projectAddress) {
this.projectAddress = projectAddress;
}
}
}
4.在controller中调用
/**
* 设备导出
*
* @param vo
* @return
*/
@ApiOperation(value = "设备导出", notes = "设备导出", httpMethod = "GET", produces = MediaType.APPLICATION_JSON_VALUE)
@RequestMapping(value = "/export", method = RequestMethod.GET)
@ResponseBody
@Transactional
public String deviceExport(HttpServletResponse response,@ModelAttribute("page") PageVO page,
@ApiParam(required = false, name = "deviceType", value = "设备类型(1报警设备、2多媒体设备)") @RequestParam(value = "deviceType", required = false) Integer deviceType,
@ApiParam(required = true, name = "projectName", value = "项目名称") @RequestParam(value = "projectName", required = false) String projectName,
@ApiParam(required = false, name = "buyCompanyName", value = "购买者名字") @RequestParam(value = "buyCompanyName", required = false) String buyCompanyName,
@ApiParam(required = false, name = "onlineStatus", value = "在线状态,1:在线,2:离线") @RequestParam(value = "onlineStatus", required = false) Integer onlineStatus) {
//TokenUtils.checkToken(getToken());
DeviceQueryModel qm = new DeviceQueryModel();
page.setPage(1);
page.setPageSize(100000);
qm.setPage(buildPage(page));
qm.setCode(code);
qm.setPhone(phone);
qm.setDeviceType(deviceType);
qm.setEvOrder(evOrder);
qm.setSn(sn);
qm.setIsBind(isBind);
qm.setWtCode(wtCode);
qm.setProjectName(projectName);
qm.setBuyCompanyName(buyCompanyName);
qm.setRegCode(regCode);
qm.setProvince(province);
qm.setCity(city);
qm.setCountry(country);
if (onlineStatus != null && (onlineStatus.intValue() > Indicator.DEVICE_ONLINE_NO.getCode()
|| onlineStatus.intValue() < Indicator.DEVICE_ONLINE_YES.getCode())) {
onlineStatus = Indicator.DEVICE_ONLINE_NO.getCode();
}
qm.setOnlineStatus(onlineStatus);
if(projectName==null||projectName=="") {
throw new DeviceException(ExceptionStatusDevice.PARAMAS_NEED);
}
Page<DeviceQueryModel> deviceModelPage = deviceService.getAdminListByPage(qm);
String searchKey = projectName;//前端控制只能搜索项目来导出 ,这里默认是搜索的项目名称项目
List<Area> areas = deviceService.getAreas();
//将数据处理成excel返回实体
HSSFWorkbook book = DeviceExportBuilder.buildExcel(deviceModelPage.getResult(), areas, searchKey);
try {
//设置返回头
// response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(searchKey+"设备列表.xls","UTF-8"));
//处理Mac保存文件名乱码问题
response.setHeader("Content-Disposition", "attachment;fileName=" + new String((searchKey+"设备列表.xls").getBytes("UTF-8"), "iso-8859-1"));
OutputStream os = response.getOutputStream();
book.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
return "Exception";
}
return "SUCCESS";
}
导出Excel效果: