将xls格式的Excel转换成图片

  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 > &nbsp;</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>&nbsp;</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(" &nbsp; ");
160                 } else {
161                     // 将ascii码为160的空格转换为html下的空格（&nbsp;）
162                     sb.append(stringValue.replace(String.valueOf((char) 160), "&nbsp;"));
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 }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/fuhengheng/p/8038187.html