导出Excel文件
package com.huawei.iop.service.contentoperation.hitquery; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; 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; import org.json.JSONException; import org.json.JSONObject; import com.huawei.iop.bean.HitOperation; import com.huawei.iop.common.AbstractService; import com.huawei.iop.common.IopConst; import com.huawei.iop.common.IopException; import com.huawei.iop.common.SetCellStyle; import com.huawei.iop.manager.contentoperation.hitquery.ContentOperationManager; import com.huawei.iop.util.IOHelperUtil; import com.huawei.iop.util.MixedUtil; /** * 热播查询--点播内容导出 * * @author jWX419155 * @version 1.0, 2018-3-8 */ public class ExportHitContent extends AbstractService { private ContentOperationManager contentOperationManager; public void setContentOperationManager(ContentOperationManager contentOperationManager) { this.contentOperationManager = contentOperationManager; } @Override public String excute(Map<String, Object> context) throws IopException { logger.info("enter ExportHitContent ... "); logger.info("params : " + context); String execute = IopConst.IOP_RUN_SUCCESS; String temple = (String) context.get(IopConst.REQUEST_JSON); String savePath = request.getSession().getServletContext().getRealPath("tmp"); String urlPath = request.getServletContext().getContextPath() + "/tmp"; // 输出流,用于最终excel的输出 FileOutputStream fileOut = null; // 输入流,用于服务器模板excel的读入 InputStream is = null; try { File saveFile = new File(savePath); List<Boolean> flagList = new ArrayList<Boolean>(IopConst.DEFAULTNUM); if (!saveFile.exists()) { // 新建 flagList.add(saveFile.mkdirs()); } String url = request.getScheme() + "://" + request.getServerName() + ':' + request.getServerPort() + urlPath; // HSSFWorkbook workBook = new HSSFWorkbook(); // 服务器模板本地地址 String modelpath = request.getSession().getServletContext().getRealPath("WEB-INF") + '/' + "protection.xls"; is = new FileInputStream(modelpath); HSSFWorkbook workBook = new HSSFWorkbook(is); JSONObject jsonObject = new JSONObject(temple); Map<String, Object> map = new HashMap<String, Object>(IopConst.DEFAULTNUM); // 内容名称或ID String content = jsonObject.getString("content"); map.put("content", content); String contentName = contentOperationManager.getContentName(content); // 开始时间 String beginTime = jsonObject.getString("beginTime"); map.put("beginTime", beginTime); // 结束时间 String endTime = jsonObject.getString("endTime"); map.put("endTime", endTime); // 时日周月类型 int type = jsonObject.getInt("type"); map.put("type", type); // 区域ID集合 List<String> areaIDList = jsonUtils.arrayToStrList(jsonObject.optJSONArray("areaIDList")); map.put("areaIDList", areaIDList); List<String> areaNameList = jsonUtils.arrayToStrList(jsonObject.optJSONArray("areaNameList")); int len = areaNameList.size(); String areaName = IopConst.STRING_EMPTY; String areaNameStr1 = IopConst.STRING_EMPTY; String areaNameStr = IopConst.STRING_EMPTY; StringBuffer sb = new StringBuffer(); if (len == 1) { areaNameStr1 = areaNameList.get(0); } else if (len == 2) { sb.append(areaNameList.get(0)).append("、").append(areaNameList.get(1)); areaNameStr1 = sb.toString(); } else { sb.append(areaNameList.get(0)).append("、").append(areaNameList.get(1)).append("等"); areaNameStr1 = sb.toString(); } sb = new StringBuffer(); for (int i = 0; i < len; i++) { areaName = areaNameList.get(i); if (i == len - 1) { sb.append(areaName); } else { sb.append(areaName).append("|"); } } areaNameStr = sb.toString(); if (len == 1 && areaNameList.get(0).equals("88888")) { areaNameStr = "全省"; areaNameStr1 = "全省"; } List<HitOperation> hitContentList = contentOperationManager.queryHitContent(map, areaIDList); // HSSFSheet sheet1 = workBook.cloneSheet(0); // workBook.setSheetName(workBook.getNumberOfSheets() - 1, // beginTime.replaceAll(String.valueOf('-'), "") + '-' + endTime.replaceAll(String.valueOf('-'), "") + "热播统计图"); // // File file = new File(savePath); // File fileCache = new File(savePath); // // String picBase = jsonObject.getString(IopConst.PICBASE); // picBase = picBase.replaceAll(" ", "+"); // String picPath = DecodeBase.decodeBase64(picBase, file); // File fileTemp = new File(picPath); // BufferedImage image = ImageIO.read(fileTemp); // HSSFPatriarch patriarch = sheet1.createDrawingPatriarch(); // ByteArrayOutputStream byteArrayOut = null; // byteArrayOut = new ByteArrayOutputStream(); // ImageIO.setCacheDirectory(fileCache); // ImageIO.write(image, "png", byteArrayOut); // HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 225, 225, (short) 1, 1, (short) 18, 25); // // 插入图片 // patriarch.createPicture(anchor, workBook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); HSSFSheet sheet = workBook.cloneSheet(0); workBook.setSheetName(workBook.getNumberOfSheets() - 1, beginTime.replaceAll(String.valueOf('-'), "") + '-' + endTime.replaceAll(String.valueOf('-'), "") + "热播统计表"); sheet.setColumnWidth(0, 4500); sheet.setColumnWidth(1, 7000); sheet.setColumnWidth(2, 4500); sheet.setColumnWidth(3, 15000); sheet.setColumnWidth(4, 4500); sheet.setColumnWidth(5, 4500); sheet.setColumnWidth(6, 4500); sheet.setColumnWidth(7, 4500); HSSFRow row0 = sheet.createRow(0);// 第一行 HSSFCellStyle cellStyle1 = SetCellStyle.getCellStyle3(workBook); HSSFCellStyle cellStyle2 = SetCellStyle.getCellStyle1(workBook); row0.createCell(0).setCellValue("时间"); row0.createCell(1).setCellValue("内容名称"); row0.createCell(2).setCellValue("内容ID"); row0.createCell(3).setCellValue("区域"); row0.createCell(4).setCellValue("UV"); row0.createCell(5).setCellValue("VV"); row0.createCell(6).setCellValue("流量"); row0.createCell(7).setCellValue("户均流量"); for (int a = 0; a < 8; a++) { sheet.getRow(0).getCell(a).setCellStyle(cellStyle1);// 表头字段格式 } int length = hitContentList.size(); HSSFRow row = null; MixedUtil mixedUtil = MixedUtil.getInstance(); Date date = new Date(); SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat(MixedUtil.FORMARTDATETYPE_6); SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat(MixedUtil.FORMARTDATETYPE_2); SimpleDateFormat simpleDateFormat3 = new SimpleDateFormat(MixedUtil.FORMARTDATETYPE_7); for (int j = 0; j < length; j++) { row = sheet.createRow(j + 1); switch (type) { case 1: date = mixedUtil.parseDate(hitContentList.get(j).getDataDate(), "yyyyMMddHH"); row.createCell(0).setCellValue(simpleDateFormat1.format(date)); break; case 2: case 3: date = mixedUtil.parseDate(hitContentList.get(j).getDataDate(), "yyyyMMdd"); row.createCell(0).setCellValue(simpleDateFormat2.format(date)); break; case 4: date = mixedUtil.parseDate(hitContentList.get(j).getDataDate(), "yyyyMM"); row.createCell(0).setCellValue(simpleDateFormat3.format(date)); break; default: break; } row.createCell(1).setCellValue(hitContentList.get(j).getContentName()); row.createCell(2).setCellValue(hitContentList.get(j).getContentID()); row.createCell(3).setCellValue(areaNameStr); row.createCell(4).setCellValue(hitContentList.get(j).getUniqueView()); row.createCell(5).setCellValue(hitContentList.get(j).getVisitView()); row.createCell(6).setCellValue(hitContentList.get(j).getDataFlow()); row.createCell(7).setCellValue(hitContentList.get(j).getDataFlowUser()); for (int k = 0; k < 8; k++) { sheet.getRow(j + 1).getCell(k).setCellStyle(cellStyle2); } } workBook.removeSheetAt(0); // 保存 String name = null; // 获得时间,用于文件命名 String time = MixedUtil.getCurrentTime(); name = contentName + areaNameStr1 + "热播统计表" + '_' + time + ".xls"; String upath = savePath + '/' + name; fileOut = null; fileOut = new FileOutputStream(upath); workBook.write(fileOut); workBook.close(); String path = url + String.valueOf('/') + name; this.appendJSON("url", path); } catch (JSONException e) { logger.error("ExportHitContent:", e); execute = IopConst.IOP_RUN_FAILSE; contextMap.put(IopConst.STATUES, execute); } catch (IOException e) { logger.error("ExportHitContent:", e); execute = IopConst.IOP_RUN_FAILSE; } catch (Exception e) { logger.error("ExportHitContent:", e); execute = IopConst.IOP_RUN_FAILSE; } finally { IOHelperUtil.close(is, fileOut); } logger.info("ExportHitContent Finished ..."); return execute; } }