1 public class ExcelToImage { 2 3 /** 4 * 临时文件夹 5 */ 6 private static String UPLOAD_DIR = "upload" + File.separator; 7 /** 8 * 获取上传的文件 9 * @param uploadPath 10 * @param file 11 * @return 12 */ 13 private static File getUploadCalendarFile(String uploadPath,MultipartFile file) 14 { 15 File path = new File(uploadPath); 16 if(!path.exists()) 17 path.mkdirs(); 18 String fileName = file.getOriginalFilename(); 19 String ext = fileName.substring(fileName.lastIndexOf(".")); 20 String uploadfileName = UUID.randomUUID().toString(); 21 uploadfileName = uploadfileName.replace("-", ""); 22 uploadfileName = uploadfileName + ext; 23 String pdfpath = uploadPath+uploadfileName; 24 File convFile = new File( pdfpath); 25 try { 26 file.transferTo(convFile); 27 } catch (IllegalStateException e) { 28 e.printStackTrace(); 29 } catch (IOException e) { 30 e.printStackTrace(); 31 } 32 return convFile; 33 } 34 35 /** 36 * 将Excel转换成图片 37 * @param filePath 38 * @param isWithStyle 39 * @param outPath 40 * @return 41 */ 42 public static String readExcelToHtml(MultipartFile file,HttpServletRequest request, boolean isWithStyle) { 43 44 InputStream is = null; 45 String htmlExcel = null; 46 try { 47 ServletContext contx = request.getSession().getServletContext(); 48 String savePath = contx.getRealPath("/") + UPLOAD_DIR; 49 //读取的Excel文件暂时存放 50 File sourcefile = getUploadCalendarFile(savePath,file); 51 if(sourcefile.exists()) 52 { 53 String pdfUploadName = sourcefile.getName(); 54 pdfUploadName = pdfUploadName.substring(0,pdfUploadName.lastIndexOf(".")); 55 56 is = new FileInputStream(sourcefile); 57 Workbook wb = WorkbookFactory.create(is); 58 if (wb instanceof XSSFWorkbook) { 59 XSSFWorkbook xWb = (XSSFWorkbook) wb; 60 htmlExcel = getExcelInfo(xWb, isWithStyle); 61 } else if (wb instanceof HSSFWorkbook) { 62 HSSFWorkbook hWb = (HSSFWorkbook) wb; 63 htmlExcel = getExcelInfo(hWb, isWithStyle); 64 } 65 //删除目录文件 66 sourcefile.delete(); 67 //获取Excel内容后，删除目录文件，同时生成Excel文件 68 if(htmlExcel != null && htmlExcel.length() > 0) 69 { 70 String outPath = savePath + File.separator + pdfUploadName + ".html"; 71 //将读取的Excel内容形成的html写入到文件中 72 writeFile(htmlExcel,outPath); 73 //filepath是否存在 74 File fileOut = new File(outPath); 75 //如果生成html,然后转换图片 76 if(fileOut.exists()) 77 { 78 URL urlHtmPath = fileOut.toURI().toURL(); 79 //形成图片 80 String imgPath = savePath + File.separator + pdfUploadName + ".png"; 81 HtmlToImage.getConvertToImagePath(urlHtmPath.toString(), imgPath); 82 File fileImg = new File(imgPath); 83 if(fileImg.exists()) 84 { 85 fileOut.delete(); 86 //将生成的图片进行大小限制：设定宽度最大为900 87 ImageFixSizeUtil.compressImage(imgPath, imgPath, CalendarImgSizeEnum.MAXLENGTH.getiValue()); 88 } 89 90 return imgPath; 91 } 92 } 93 } 94 } catch (Exception e) { 95 e.printStackTrace(); 96 } finally { 97 try { 98 is.close(); 99 } catch (IOException e) { 100 e.printStackTrace(); 101 } 102 } 103 return null; 104 } 105 106 /** 107 * 获取Excel信息 108 * @param wb 109 * @param isWithStyle：是否显示样式 110 * 默认只读取第一个Sheet的内容 111 * @return 112 */ 113 private static String getExcelInfo(Workbook wb, boolean isWithStyle) { 114 115 StringBuffer sb = new StringBuffer(); 116 // 获取第一个Sheet的内容 117 Sheet sheet = wb.getSheetAt(0); 118 int lastRowNum = sheet.getLastRowNum(); 119 Map<String, String> map[] = getRowSpanColSpanMap(sheet); 120 sb.append("<html><head><meta http-equiv='Content-Type' content='text/html; charset=UTF-8' /><body>"); 121 sb.append("<table style='border-collapse:collapse;border-spacing:0;'>"); 122 Row row = null; // 兼容 123 Cell cell = null; // 兼容 124 for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { 125 row = sheet.getRow(rowNum); 126 if (row == null) { 127 sb.append("<tr><td > </td></tr>"); 128 continue; 129 } 130 sb.append("<tr>"); 131 int lastColNum = row.getLastCellNum(); 132 for (int colNum = 0; colNum < lastColNum; colNum++) { 133 cell = row.getCell(colNum); 134 if (cell == null) { // 特殊情况 空白的单元格会返回null 135 sb.append("<td> </td>"); 136 continue; 137 } 138 String stringValue = getCellValue(cell); 139 if (map[0].containsKey(rowNum + "," + colNum)) { 140 String pointString = map[0].get(rowNum + "," + colNum); 141 map[0].remove(rowNum + "," + colNum); 142 int bottomeRow = Integer.valueOf(pointString.split(",")[0]); 143 int bottomeCol = Integer.valueOf(pointString.split(",")[1]); 144 int rowSpan = bottomeRow - rowNum + 1; 145 int colSpan = bottomeCol - colNum + 1; 146 sb.append("<td rowspan= '" + rowSpan + "' colspan= '" + colSpan + "' "); 147 } else if (map[1].containsKey(rowNum + "," + colNum)) { 148 map[1].remove(rowNum + "," + colNum); 149 continue; 150 } else { 151 sb.append("<td "); 152 } 153 // 判断是否需要样式 154 if (isWithStyle) { 155 dealExcelStyle(wb, sheet, cell, sb);// 处理单元格样式 156 } 157 sb.append(">"); 158 if (stringValue == null || "".equals(stringValue.trim())) { 159 sb.append(" "); 160 } else { 161 // 将ascii码为160的空格转换为html下的空格（ ） 162 sb.append(stringValue.replace(String.valueOf((char) 160), " ")); 163 } 164 sb.append("</td>"); 165 } 166 sb.append("</tr>"); 167 } 168 sb.append("</table>"); 169 sb.append("</body></html>"); 170 171 return sb.toString(); 172 } 173 174 /** 175 * 合并行或列 176 * @param sheet 177 * @return 178 */ 179 @SuppressWarnings({ "rawtypes", "unchecked" }) 180 private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) { 181 182 Map<String, String> map0 = new HashMap<String, String>(); 183 Map<String, String> map1 = new HashMap<String, String>(); 184 int mergedNum = sheet.getNumMergedRegions(); 185 CellRangeAddress range = null; 186 for (int i = 0; i < mergedNum; i++) { 187 range = sheet.getMergedRegion(i); 188 int topRow = range.getFirstRow(); 189 int topCol = range.getFirstColumn(); 190 int bottomRow = range.getLastRow(); 191 int bottomCol = range.getLastColumn(); 192 map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); 193 int tempRow = topRow; 194 while (tempRow <= bottomRow) { 195 int tempCol = topCol; 196 while (tempCol <= bottomCol) { 197 map1.put(tempRow + "," + tempCol, ""); 198 tempCol++; 199 } 200 tempRow++; 201 } 202 map1.remove(topRow + "," + topCol); 203 } 204 Map[] map = { map0, map1 }; 205 return map; 206 } 207 208 /** 209 * 获取表格单元格Cell内容 210 * 211 * @param cell 212 * @return 213 */ 214 private static String getCellValue(Cell cell) { 215 216 String result = new String(); 217 switch (cell.getCellType()) { 218 case Cell.CELL_TYPE_NUMERIC:// 数字类型 219 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 220 SimpleDateFormat sdf = null; 221 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { 222 sdf = new SimpleDateFormat("HH:mm"); 223 } else {// 日期 224 sdf = new SimpleDateFormat("yyyy-MM-dd"); 225 } 226 Date date = cell.getDateCellValue(); 227 result = sdf.format(date); 228 } else if (cell.getCellStyle().getDataFormat() == 58) { 229 // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) 230 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 231 double value = cell.getNumericCellValue(); 232 Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); 233 result = sdf.format(date); 234 } else { 235 double value = cell.getNumericCellValue(); 236 CellStyle style = cell.getCellStyle(); 237 DecimalFormat format = new DecimalFormat(); 238 String temp = style.getDataFormatString(); 239 // 单元格设置成常规 240 if (temp.equals("General")) { 241 format.applyPattern("#"); 242 } 243 result = format.format(value); 244 } 245 break; 246 case Cell.CELL_TYPE_STRING:// String类型 247 result = cell.getRichStringCellValue().toString(); 248 break; 249 case Cell.CELL_TYPE_BLANK: 250 result = ""; 251 break; 252 default: 253 result = ""; 254 break; 255 } 256 return result; 257 } 258 259 /** 260 * 处理表格样式 261 * 262 * @param wb 263 * @param sheet 264 * @param cell 265 * @param sb 266 */ 267 private static void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb) { 268 269 CellStyle cellStyle = cell.getCellStyle(); 270 if (cellStyle != null) { 271 short alignment = cellStyle.getAlignment(); 272 sb.append("align='" + convertAlignToHtml(alignment) + "' ");// 单元格内容的水平对齐方式 273 short verticalAlignment = cellStyle.getVerticalAlignment(); 274 sb.append("valign='" + convertVerticalAlignToHtml(verticalAlignment) + "' ");// 单元格中内容的垂直排列方式 275 276 if (wb instanceof XSSFWorkbook) { 277 XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont(); 278 short boldWeight = xf.getBoldweight(); 279 sb.append("style='"); 280 sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 281 XSSFColor xc = xf.getXSSFColor(); 282 if (xc != null && !"".equals(xc)) { 283 sb.append("color:#" + xc.getARGBHex().substring(2) + ";"); // 字体颜色 284 } 285 XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor(); 286 if (bgColor != null && !"".equals(bgColor)) { 287 sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";"); // 背景颜色 288 } 289 sb.append(getBorderStyle(0, cellStyle.getBorderTop(), 290 ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor())); 291 sb.append(getBorderStyle(1, cellStyle.getBorderRight(), 292 ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor())); 293 sb.append(getBorderStyle(2, cellStyle.getBorderBottom(), 294 ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor())); 295 sb.append(getBorderStyle(3, cellStyle.getBorderLeft(), 296 ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor())); 297 298 } else if (wb instanceof HSSFWorkbook) { 299 HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb); 300 short boldWeight = hf.getBoldweight(); 301 short fontColor = hf.getColor(); 302 sb.append("style='"); 303 HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式 304 HSSFColor hc = palette.getColor(fontColor); 305 sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 306 307 String fontColorStr = convertToStardColor(hc); 308 if (fontColorStr != null && !"".equals(fontColorStr.trim())) { 309 sb.append("color:" + fontColorStr + ";"); // 字体颜色 310 } 311 short bgColor = cellStyle.getFillForegroundColor(); 312 hc = palette.getColor(bgColor); 313 String bgColorStr = convertToStardColor(hc); 314 if (bgColorStr != null && !"".equals(bgColorStr.trim())) { 315 sb.append("background-color:" + bgColorStr + ";"); // 背景颜色 316 } 317 sb.append(getBorderStyle(palette, 0, cellStyle.getBorderTop(), cellStyle.getTopBorderColor())); 318 sb.append(getBorderStyle(palette, 1, cellStyle.getBorderRight(), cellStyle.getRightBorderColor())); 319 sb.append(getBorderStyle(palette, 3, cellStyle.getBorderLeft(), cellStyle.getLeftBorderColor())); 320 sb.append(getBorderStyle(palette, 2, cellStyle.getBorderBottom(), cellStyle.getBottomBorderColor())); 321 } 322 323 sb.append("' "); 324 } 325 } 326 327 /** 328 * 单元格内容的水平对齐方式 329 * 330 * @param alignment 331 * @return 332 */ 333 private static String convertAlignToHtml(short alignment) { 334 335 String align = "left"; 336 switch (alignment) { 337 case CellStyle.ALIGN_LEFT: 338 align = "left"; 339 break; 340 case CellStyle.ALIGN_CENTER: 341 align = "center"; 342 break; 343 case CellStyle.ALIGN_RIGHT: 344 align = "right"; 345 break; 346 default: 347 break; 348 } 349 return align; 350 } 351 352 /** 353 * 单元格中内容的垂直排列方式 354 * 355 * @param verticalAlignment 356 * @return 357 */ 358 private static String convertVerticalAlignToHtml(short verticalAlignment) { 359 360 String valign = "middle"; 361 switch (verticalAlignment) { 362 case CellStyle.VERTICAL_BOTTOM: 363 valign = "bottom"; 364 break; 365 case CellStyle.VERTICAL_CENTER: 366 valign = "center"; 367 break; 368 case CellStyle.VERTICAL_TOP: 369 valign = "top"; 370 break; 371 default: 372 break; 373 } 374 return valign; 375 } 376 377 /** 378 * 转换颜色 379 * @param hc 380 * @return 381 */ 382 private static String convertToStardColor(HSSFColor hc) { 383 384 StringBuffer sb = new StringBuffer(""); 385 if (hc != null) { 386 if (HSSFColor.AUTOMATIC.index == hc.getIndex()) { 387 return null; 388 } 389 sb.append("#"); 390 for (int i = 0; i < hc.getTriplet().length; i++) { 391 sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); 392 } 393 } 394 return sb.toString(); 395 } 396 397 /** 398 * 用0填充 399 * @param str 400 * @return 401 */ 402 private static String fillWithZero(String str) { 403 if (str != null && str.length() < 2) { 404 return "0" + str; 405 } 406 return str; 407 } 408 409 static String[] bordesr = { "border-top:", "border-right:", "border-bottom:", "border-left:" }; 410 static String[] borderStyles = { "solid ", "solid ", "solid ", 411 "solid ", "solid ", "solid ", "solid ", "solid ", 412 "solid ", "solid", "solid", "solid", "solid", "solid" }; 413 414 /** 415 * 设定表格边框：用于xls格式类型 416 * @param palette 417 * @param b 418 * @param s 419 * @param t 420 * @return 421 */ 422 private static String getBorderStyle(HSSFPalette palette, int b, short s, short t) { 423 424 if (s == 0) 425 return ""; 426 String borderColorStr = convertToStardColor(palette.getColor(t)); 427 borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" : borderColorStr; 428 return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; 429 } 430 431 /** 432 * 设定表格边框，用于xlsx格式 433 * @param b 434 * @param s 435 * @param xc 436 * @return 437 */ 438 private static String getBorderStyle(int b, short s, XSSFColor xc) { 439 440 if (s == 0) 441 return ""; 442 443 if (xc != null && !"".equals(xc)) { 444 String borderColorStr = xc.getARGBHex();// t.getARGBHex(); 445 borderColorStr = borderColorStr == null || borderColorStr.length() < 1 ? "#000000" 446 : borderColorStr.substring(2); 447 return bordesr[b] + borderStyles[s] + borderColorStr + " 1px;"; 448 } 449 return ""; 450 } 451 452 453 /** 454 * 将html写入到对应的文件中 455 * 456 * @param content 457 * @param path 458 */ 459 public static void writeFile(String content, String path) { 460 FileOutputStream fos = null; 461 BufferedWriter bw = null; 462 try { 463 File file = new File(path); 464 fos = new FileOutputStream(file); 465 bw = new BufferedWriter(new OutputStreamWriter(fos, "utf-8")); 466 bw.write(content); 467 } catch (FileNotFoundException fnfe) { 468 fnfe.printStackTrace(); 469 } catch (IOException ioe) { 470 ioe.printStackTrace(); 471 } finally { 472 try { 473 if (bw != null) 474 bw.close(); 475 if (fos != null) 476 fos.close(); 477 } catch (IOException ie) { 478 } 479 } 480 } 481 }