最开始做的时候比较笨,查阅各种资料,推荐一个不错的地址:http://easypoi.mydoc.io/
从这里学着做的导出,不需要模版,写好,把包也导入就ok了。
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportServer;
public void exportExcels
(@RequestParam(value = "customerId", required = true) String customerId,
@RequestParam(value = "status", required = false) Integer status,
@RequestParam(value = "nameAndCode", required = false) String nameAndCode,
@RequestParam(value = "regionId", required = false) String regionId,
@RequestParam(value = "siteTypeId", required = false) Integer siteTypeId,
HttpServletResponse response) throws Exception
{
List<FamRelay> famRelays = famRelayClients.exportRelayExcels(customerId,
status, nameAndCode, regionId, siteTypeId);
List<ExcelExportEntity> entityList = new ArrayList<>();
entityList.add(new ExcelExportEntity("联网状态", "relay_status", 15));
entityList.add(new ExcelExportEntity("单位编码", "site_code", 15));
entityList.add(new ExcelExportEntity("单位名称", "site_name", 15));
entityList.add(new ExcelExportEntity("设备编码", "device_code", 15));
entityList.add(new ExcelExportEntity("单位地址", "site_address", 15));
entityList.add(new ExcelExportEntity("消控室电话", "control_room_contactinfo", 15));
entityList.add(new ExcelExportEntity("最新联网时间", "update_time", 15));
Map<String, Object> dataResult;
List<Map<String, Object>> famRelayList = new ArrayList<>();
for (FamRelay famRelay : famRelays) {
dataResult = new HashMap<String, Object>();
if (famRelay.getStatus() == OnlinStatus.ONLINE_STATUS.code()) {
dataResult.put("relay_status", "联网");
} else {
dataResult.put("relay_status", "未联网");
}
dataResult.put("site_code", famRelay.getFamCustomerSite().getCode());
dataResult.put("site_name", famRelay.getFamCustomerSite().getName());
dataResult.put("device_code", famRelay.getCode());
if (null != famRelay.getAddress()) {
dataResult.put("site_address", famRelay.getAddress().getAddress());
} else {
dataResult.put("site_address", "无");
}
dataResult.put("control_room_contactinfo",
famRelay.getFamCustomerSite().getControlRoomContactinfo());
if (null == famRelay.getUpdateTime()) {
dataResult.put("update_time", "无");
} else {
dataResult.put("update_time",TimeUtils.timeStringFromLong(famRelay.getUpdateTime(),
"yyyy-MM-dd HH:mm:ss", null));
}
famRelayList.add(dataResult);
}
ExcelExportServer server = new ExcelExportServer();
Workbook workbook = new HSSFWorkbook();
ExportParams exportParams = new ExportParams();
exportParams.setSheetName("单位在线状态列表");
server.createSheetForMap(workbook, exportParams, entityList, famRelayList);
response.setCharacterEncoding("UTF-8");
String filedisplay = "onlinetate.xls";
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
}
后来发现这种东西导出的excel很low的。于是自己做一个excel模版。想导成什么样都ok。
单位在线状态 (总计:{{allCount}}家:联网数量:{{onlineCount}}家 | 未联网数量:{{outlineCount}}家) | |||||||
联网: | {{onlineState}} | 未联网: | {{outlineState}} | 区域: | {{regionName}} | 查询内容: | {{name}} |
序号 | 联网状态 | 单位编码 | 单位名称 | 联网方式 | 单位地址 | 消控室电话 | 最新联网时间 |
{{$fe: model t.number | t.state | t.siteCode | t.siteName | t.deviceName | t.endStatusId | t.receivingTime | t.lastUpdateTIme}} |
import java.io.OutputStream;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import org.apache.poi.ss.usermodel.Workbook;
private void exportOnlineCustomerSite(List<FamRelay> famRelays, String url, String filedisplay, String regionName, HttpServletResponse response, String nameAndCode, String onlineState, String outlineState, String allCount, String onlineCount, String outlineCount, Map<String, String> modeTypeMap) throws Exception {
TemplateExportParams params = new TemplateExportParams(url);
Map<String, Object> map = new HashMap<String, Object>();
map.put("allCount", allCount);
map.put("onlineCount", onlineCount);
map.put("outlineCount", outlineCount);
map.put("onlineState", onlineState);
map.put("outlineState", outlineState);
map.put("regionName", regionName);
map.put("name", nameAndCode != null ? nameAndCode : "");
List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
Map<String, String> lm;
int i = 1;
for (FamRelay entity : famRelays) {
lm = new HashMap<String, String>();
lm.put("number", i + "");
lm.put("state", entity.getStatus() ==
OnlinStatus.ONLINE_STATUS.code() ?
OnlinStatus.ONLINE_STATUS.desc() : OnlinStatus.OUTLINE_STATUS.desc());
lm.put("siteCode", entity.getFamCustomerSite().getCode());
lm.put("siteName", entity.getFamCustomerSite().getName());
lm.put("type", modeTypeMap.get(entity.getModelTypeId()));
lm.put("address", entity.getAddress() != null ? entity.getAddress().getAddress() : "");
lm.put("tel", entity.getFamCustomerSite() != null ?
entity.getFamCustomerSite().getControlRoomContactinfo() : "");
lm.put("lastUpdateTIme", entity.getUpdateTime() == null ? ""
: TimeUtils.timeStringFromLong(entity.getUpdateTime(), "yyyy-MM-dd HH:mm:ss", null));
listMap.add(lm);
i++;
}
map.put("model", listMap);
Workbook workbook = ExcelExportUtil.exportExcel(params, map);
response.setCharacterEncoding("UTF-8");
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
}
效果类似于这样子了~~~
——个人笔记,仅供学习参考