| 提交 | 用户 | 时间 | ||
| 179784 | 1 | package com.iailab.module.model.common.utils; |
| D | 2 | |
| 3 | import com.iailab.module.model.api.mcs.dto.PreDataItemChartRespVO; | |
| 4 | import com.iailab.module.model.api.mcs.dto.PreDataSampleViewRespDTO; | |
| 5 | import org.apache.poi.hssf.usermodel.*; | |
| 6 | import org.apache.poi.ss.usermodel.HorizontalAlignment; | |
| 7 | import org.apache.poi.ss.usermodel.VerticalAlignment; | |
| 8 | import org.apache.poi.ss.util.CellRangeAddress; | |
| 9 | ||
| 10 | import javax.servlet.http.HttpServletResponse; | |
| 11 | import java.io.IOException; | |
| 12 | import java.io.OutputStream; | |
| 13 | import java.lang.reflect.Field; | |
| 14 | import java.text.SimpleDateFormat; | |
| 15 | import java.util.Date; | |
| 16 | import java.util.LinkedHashMap; | |
| 17 | import java.util.List; | |
| 18 | import java.util.Map; | |
| 19 | import java.util.logging.Level; | |
| 20 | import java.util.logging.Logger; | |
| 21 | ||
| 22 | /** | |
| 23 | * Excel 导出通用工具类 | |
| 24 | * | |
| 25 | * @author PanZhibao | |
| 26 | * @date 2021年06月29日 9:06 | |
| 27 | */ | |
| 28 | public class ExcelUtil { | |
| 29 | ||
| 30 | /** | |
| 31 | * 导出PointValueExcel | |
| 32 | * | |
| 33 | * @param sheetTitle | |
| 34 | * @param title | |
| 35 | * @param list | |
| 36 | * @param response | |
| 37 | * @return | |
| 38 | */ | |
| 39 | public static byte[] exportPointValue(String sheetTitle, String[] title, List<Object> list, HttpServletResponse response) { | |
| 40 | ||
| 41 | HSSFWorkbook wb = new HSSFWorkbook();//创建excel表 | |
| 42 | HSSFSheet sheet = wb.createSheet(sheetTitle); | |
| 43 | sheet.setDefaultColumnWidth(20);//设置默认行宽 | |
| 44 | ||
| 45 | //表头样式（加粗，水平居中，垂直居中） | |
| 46 | HSSFCellStyle cellStyle = wb.createCellStyle(); | |
| 47 | cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 | |
| 48 | cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 49 | ||
| 50 | HSSFFont fontStyle = wb.createFont(); | |
| 51 | ||
| 52 | cellStyle.setFont(fontStyle); | |
| 53 | ||
| 54 | //标题样式（加粗，垂直居中） | |
| 55 | HSSFCellStyle cellStyle2 = wb.createCellStyle(); | |
| 56 | cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 57 | cellStyle2.setFont(fontStyle); | |
| 58 | ||
| 59 | //字段样式（垂直居中） | |
| 60 | HSSFCellStyle cellStyle3 = wb.createCellStyle(); | |
| 61 | cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 62 | ||
| 63 | //数字样式 | |
| 64 | HSSFCellStyle cellStyle4 = wb.createCellStyle(); | |
| 65 | HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式 | |
| 66 | cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 | |
| 67 | cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));//设置数值类型格式为保留两位小数 | |
| 68 | ||
| 69 | //创建表头 | |
| 70 | HSSFRow row = sheet.createRow(0); | |
| 71 | //row.setHeightInPoints(20);//行高 | |
| 72 | ||
| 73 | HSSFCell cell = row.createCell(0); | |
| 74 | cell.setCellValue(sheetTitle); | |
| 75 | cell.setCellStyle(cellStyle); | |
| 76 | ||
| 77 | sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.length - 1))); | |
| 78 | ||
| 79 | //创建标题 | |
| 80 | HSSFRow rowTitle = sheet.createRow(1); | |
| 81 | //rowTitle.setHeightInPoints(20); | |
| 82 | ||
| 83 | HSSFCell hc; | |
| 84 | for (int i = 0; i < title.length; i++) { | |
| 85 | hc = rowTitle.createCell(i); | |
| 86 | hc.setCellValue(title[i]); | |
| 87 | hc.setCellStyle(cellStyle2); | |
| 88 | } | |
| 89 | ||
| 90 | byte result[] = null; | |
| 91 | ||
| 92 | try { | |
| 93 | //创建表格数据 | |
| 94 | Field[] fields; | |
| 95 | int i = 2; | |
| 96 | for (Object obj : list) { | |
| 97 | fields = obj.getClass().getDeclaredFields(); | |
| 98 | ||
| 99 | HSSFRow rowBody = sheet.createRow(i); | |
| 100 | //rowBody.setHeightInPoints(20); | |
| 101 | ||
| 102 | int j = 0; | |
| 103 | for (Field f : fields) { | |
| 104 | ||
| 105 | f.setAccessible(true); | |
| 106 | ||
| 107 | Object va = f.get(obj); | |
| 108 | if (null == va) { | |
| 109 | va = ""; | |
| 110 | } | |
| 111 | ||
| 112 | hc = rowBody.createCell(j); | |
| 113 | ||
| 114 | if (j == 0) { | |
| 115 | hc.setCellValue(Double.parseDouble(va.toString())); | |
| 116 | hc.setCellStyle(cellStyle4); | |
| 117 | } else { | |
| 118 | hc.setCellValue(va.toString()); | |
| 119 | hc.setCellStyle(cellStyle3); | |
| 120 | } | |
| 121 | ||
| 122 | ||
| 123 | j++; | |
| 124 | } | |
| 125 | ||
| 126 | i++; | |
| 127 | } | |
| 128 | ||
| 129 | //设置Http响应头告诉浏览器下载这个附件 | |
| 130 | response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); | |
| 131 | OutputStream outputStream = response.getOutputStream(); | |
| 132 | wb.write(outputStream); | |
| 133 | outputStream.close(); | |
| 134 | return wb.getBytes(); | |
| 135 | } catch (Exception ex) { | |
| 136 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 137 | return result; | |
| 138 | } finally { | |
| 139 | try { | |
| 140 | wb.close(); | |
| 141 | } catch (IOException ex) { | |
| 142 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 143 | } | |
| 144 | } | |
| 145 | } | |
| 146 | ||
| 147 | //预测图表数据转excel | |
| 148 | public static byte[] exportchart(String sheetTitle, PreDataItemChartRespVO respVO, HttpServletResponse response) { | |
| 149 | ||
| 150 | SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); | |
| 151 | ||
| 152 | HSSFWorkbook wb = new HSSFWorkbook(); | |
| 153 | HSSFSheet sheet = wb.createSheet(sheetTitle); | |
| 154 | sheet.setDefaultColumnWidth(20);//设置默认行宽 | |
| 155 | ||
| 156 | // 表头样式（加粗，水平居中，垂直居中） | |
| 157 | HSSFCellStyle cellStyle = wb.createCellStyle(); | |
| 158 | cellStyle.setAlignment(HorizontalAlignment.CENTER); | |
| 159 | cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); | |
| 160 | HSSFFont fontStyle = wb.createFont(); | |
| 161 | cellStyle.setFont(fontStyle); | |
| 162 | ||
| 163 | // 标题样式（加粗，垂直居中） | |
| 164 | HSSFCellStyle cellStyle2 = wb.createCellStyle(); | |
| 165 | cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); | |
| 166 | cellStyle2.setFont(fontStyle); | |
| 167 | ||
| 168 | // 字段样式（垂直居中） | |
| 169 | HSSFCellStyle cellStyle3 = wb.createCellStyle(); | |
| 170 | cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER); | |
| 171 | ||
| 172 | // 数字样式 | |
| 173 | HSSFCellStyle cellStyle4 = wb.createCellStyle(); | |
| 174 | HSSFDataFormat df = wb.createDataFormat(); | |
| 175 | cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER); | |
| 176 | cellStyle4.setDataFormat(df.getFormat("#,##0.000000")); | |
| 177 | ||
| 178 | // 创建标题 | |
| 179 | String[] title = new String[respVO.getLegend().size() * 4]; | |
| 180 | for (int i = 0; respVO.getLegend().size() > i; i++) { | |
| 181 | title[i * 4] = "日期时间"; | |
| 182 | title[i * 4 + 1] = respVO.getLegend().get(i) + ":真实值"; | |
| 183 | title[i * 4 + 2] = "日期时间"; | |
| 184 | title[i * 4 + 3] = respVO.getLegend().get(i) + ":预测值"; | |
| 185 | } | |
| 186 | HSSFRow rowTitle = sheet.createRow(0); | |
| 187 | HSSFCell hc; | |
| 188 | for (int i = 0; i < title.length; i++) { | |
| 189 | hc = rowTitle.createCell(i); | |
| 190 | hc.setCellValue(title[i]); | |
| 191 | hc.setCellStyle(cellStyle2); | |
| 192 | } | |
| 193 | ||
| 194 | LinkedHashMap<String, PreDataSampleViewRespDTO> viewMap = respVO.getViewMap(); | |
| 195 | byte result[] = null; | |
| 196 | try { | |
| 197 | // 创建表格数据 | |
| 198 | //逐列插入 | |
| 199 | for (int j = 0; viewMap.size() > j; j++) { | |
| 200 | //去掉标题行，从第一行开始 | |
| 201 | //逐行插入 | |
| 202 | for (Map.Entry<String, PreDataSampleViewRespDTO> entry : viewMap.entrySet()) { | |
| 203 | //真实值 | |
| 204 | List<Object[]> realData = entry.getValue().getRealData(); | |
| 205 | //预测值 | |
| 206 | List<Object[]> preDataN = entry.getValue().getPreDataN(); | |
| 207 | HSSFRow rowBody; | |
| 208 | ||
| 209 | //插入真实值 | |
| 210 | for (int i = 0; realData.size() > i; i++) { | |
| 211 | //判断是否创建过行，如果没有创建行，否则更新行 | |
| 212 | if (sheet.getRow(i + 1) == null) { | |
| 213 | rowBody = sheet.createRow(i + 1); | |
| 214 | } else { | |
| 215 | rowBody = sheet.getRow(i + 1); | |
| 216 | } | |
| 217 | //插入时间 | |
| 218 | hc = rowBody.createCell(j * 4); | |
| 219 | if (realData.get(i)[0] != null) { | |
| 220 | if (realData.get(i)[0] instanceof Date) { | |
| 221 | String formattedDate = sdf.format(realData.get(i)[0]); | |
| 222 | hc.setCellValue(formattedDate); | |
| 223 | } else { | |
| 224 | hc.setCellValue(realData.get(i)[0].toString()); | |
| 225 | } | |
| 226 | hc.setCellStyle(cellStyle3); | |
| 227 | } else { | |
| 228 | hc.setCellValue(""); | |
| 229 | hc.setCellStyle(cellStyle3); | |
| 230 | } | |
| 231 | //插入值 | |
| 232 | hc = rowBody.createCell(j * 4 + 1); | |
| 233 | if (realData.get(i)[1] != null) { | |
| 234 | hc.setCellValue(realData.get(i)[1].toString()); | |
| 235 | hc.setCellStyle(cellStyle4); | |
| 236 | } else { | |
| 237 | hc.setCellValue(""); | |
| 238 | hc.setCellStyle(cellStyle4); | |
| 239 | } | |
| 240 | } | |
| 241 | //插入预测值 | |
| 242 | for (int i = 0; preDataN.size() > i; i++) { | |
| 243 | //判断是否创建过行，如果没有创建行，否则更新行 | |
| 244 | if (sheet.getRow(i + 1) == null) { | |
| 245 | rowBody = sheet.createRow(i + 1); | |
| 246 | } else { | |
| 247 | rowBody = sheet.getRow(i + 1); | |
| 248 | } | |
| 249 | //插入时间 | |
| 250 | hc = rowBody.createCell(j * 4 + 2); | |
| 251 | if (preDataN.get(i)[0] != null) { | |
| 252 | if (preDataN.get(i)[0] instanceof Date) { | |
| 253 | String formattedDate = sdf.format(preDataN.get(i)[0]); | |
| 254 | hc.setCellValue(formattedDate); | |
| 255 | } else { | |
| 256 | hc.setCellValue(preDataN.get(i)[0].toString()); | |
| 257 | } | |
| 258 | hc.setCellStyle(cellStyle3); | |
| 259 | } else { | |
| 260 | hc.setCellValue(""); | |
| 261 | hc.setCellStyle(cellStyle3); | |
| 262 | } | |
| 263 | //插入值 | |
| 264 | hc = rowBody.createCell(j * 4 + 3); | |
| 265 | if (preDataN.get(i)[1] != null) { | |
| 266 | hc.setCellValue(preDataN.get(i)[1].toString()); | |
| 267 | hc.setCellStyle(cellStyle4); | |
| 268 | } else { | |
| 269 | hc.setCellValue(""); | |
| 270 | hc.setCellStyle(cellStyle4); | |
| 271 | } | |
| 272 | } | |
| 273 | } | |
| 274 | } | |
| 275 | // 设置Http响应头告诉浏览器下载这个附件 | |
| 276 | response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls"); | |
| 277 | OutputStream outputStream = response.getOutputStream(); | |
| 278 | wb.write(outputStream); | |
| 279 | outputStream.close(); | |
| 280 | return wb.getBytes(); | |
| 281 | } catch (Exception ex) { | |
| 282 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 283 | return result; | |
| 284 | } finally { | |
| 285 | try { | |
| 286 | wb.close(); | |
| 287 | } catch (IOException ex) { | |
| 288 | Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex); | |
| 289 | } | |
| 290 | } | |
| 291 | } | |
| 292 | } | |
