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 }
