POI 读取 Excel 转 HTML 支持 03xls 和 07xlsx 版本 包含样式
浏览数：26 /
时间：2015年06月09日
工作需求：
提供EXCEL模板上传后预览；EXCEL解析成终端风格HTML。
处理方案：
POI解析EXCEL，预览时尽量获取原有表格的样式；终端使用EXCEL解析的无样式HTML，然后通过jQuery添加CSS样式
遇到问题：
CSDN上大牛处理03版xls格式的有成功例子；但是07版xlsx格式的样式处理未找到理想中的例子
下文是参考大牛的例子整理后的程序 供参考！
EXCEL表格07xlsx格式
通过POI解析带样式的效果
项目JAR文件注意版本
JAVA
1 package com.hboy.exceltohtml; 2 3 import java.io.File; 4 import java.io.FileInputStream; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.text.DecimalFormat; 8 import java.text.SimpleDateFormat; 9 import java.util.Date; 10 import java.util.HashMap; 11 import java.util.Map; 12 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 13 import org.apache.poi.hssf.usermodel.HSSFDataFormat; 14 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 15 import org.apache.poi.hssf.usermodel.HSSFFont; 16 import org.apache.poi.hssf.usermodel.HSSFPalette; 17 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 18 import org.apache.poi.hssf.util.HSSFColor; 19 import org.apache.poi.ss.usermodel.Cell; 20 import org.apache.poi.ss.usermodel.CellStyle; 21 import org.apache.poi.ss.usermodel.Row; 22 import org.apache.poi.ss.usermodel.Sheet; 23 import org.apache.poi.ss.usermodel.Workbook; 24 import org.apache.poi.ss.usermodel.WorkbookFactory; 25 import org.apache.poi.ss.util.CellRangeAddress; 26 import org.apache.poi.xssf.usermodel.XSSFCellStyle; 27 import org.apache.poi.xssf.usermodel.XSSFColor; 28 import org.apache.poi.xssf.usermodel.XSSFFont; 29 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 30 31 /** 32 * @功能描述 POI 读取 Excel 转 HTML 支持 03xls 和 07xlsx 版本 包含样式 33 * @author Devil 34 * @创建时间 2015/4/19 21:34 35 */ 36 public class POIReadExcelToHtml { 37 38 /** 39 * 测试 40 * @param args 41 */ 42 public static void main(String[] args) { 43 44 String path = "E://Microsoft Excel 工作表.xlsx";//E://Microsoft Excel 工作表.xlsx 45 InputStream is = null; 46 String htmlExcel = null; 47 try { 48 File sourcefile = new File(path); 49 is = new FileInputStream(sourcefile); 50 Workbook wb = WorkbookFactory.create(is);//此WorkbookFactory在POI-3.10版本中使用需要添加dom4j 51 if (wb instanceof XSSFWorkbook) { 52 XSSFWorkbook xWb = (XSSFWorkbook) wb; 53 htmlExcel = POIReadExcelToHtml.getExcelInfo(xWb,true); 54 }else if(wb instanceof HSSFWorkbook){ 55 HSSFWorkbook hWb = (HSSFWorkbook) wb; 56 htmlExcel = POIReadExcelToHtml.getExcelInfo(hWb,true); 57 } 58 System.out.println(htmlExcel); 59 } catch (Exception e) { 60 e.printStackTrace(); 61 }finally{ 62 try { 63 is.close(); 64 } catch (IOException e) { 65 e.printStackTrace(); 66 } 67 } 68 69 } 70 71 72 /** 73 * 程序入口方法 74 * @param filePath 文件的路径 75 * @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式 76 * @return <table>...</table> 字符串 77 */ 78 public String readExcelToHtml(String filePath , boolean isWithStyle){ 79 80 InputStream is = null; 81 String htmlExcel = null; 82 try { 83 File sourcefile = new File(filePath); 84 is = new FileInputStream(sourcefile); 85 Workbook wb = WorkbookFactory.create(is); 86 if (wb instanceof XSSFWorkbook) { 87 XSSFWorkbook xWb = (XSSFWorkbook) wb; 88 htmlExcel = POIReadExcelToHtml.getExcelInfo(xWb,isWithStyle); 89 }else if(wb instanceof HSSFWorkbook){ 90 HSSFWorkbook hWb = (HSSFWorkbook) wb; 91 htmlExcel = POIReadExcelToHtml.getExcelInfo(hWb,isWithStyle); 92 } 93 } catch (Exception e) { 94 e.printStackTrace(); 95 }finally{ 96 try { 97 is.close(); 98 } catch (IOException e) { 99 e.printStackTrace(); 100 } 101 } 102 return htmlExcel; 103 } 104 105 106 107 public static String getExcelInfo(Workbook wb,boolean isWithStyle){ 108 109 StringBuffer sb = new StringBuffer(); 110 Sheet sheet = wb.getSheetAt(0);//获取第一个Sheet的内容 111 int lastRowNum = sheet.getLastRowNum(); 112 Map<String, String> map[] = getRowSpanColSpanMap(sheet); 113 sb.append("<table style=‘border-collapse:collapse;‘ width=‘100%‘>"); 114 Row row = null; //兼容 115 Cell cell = null; //兼容 116 117 for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { 118 row = sheet.getRow(rowNum); 119 if (row == null) { 120 sb.append("<tr><td > </td></tr>"); 121 continue; 122 } 123 sb.append("<tr>"); 124 int lastColNum = row.getLastCellNum(); 125 for (int colNum = 0; colNum < lastColNum; colNum++) { 126 cell = row.getCell(colNum); 127 if (cell == null) { //特殊情况 空白的单元格会返回null 128 sb.append("<td> </td>"); 129 continue; 130 } 131 132 String stringValue = getCellValue(cell); 133 if (map[0].containsKey(rowNum + "," + colNum)) { 134 String pointString = map[0].get(rowNum + "," + colNum); 135 map[0].remove(rowNum + "," + colNum); 136 int bottomeRow = Integer.valueOf(pointString.split(",")[0]); 137 int bottomeCol = Integer.valueOf(pointString.split(",")[1]); 138 int rowSpan = bottomeRow - rowNum + 1; 139 int colSpan = bottomeCol - colNum + 1; 140 sb.append("<td rowspan= ‘" + rowSpan + "‘ colspan= ‘"+ colSpan + "‘ "); 141 } else if (map[1].containsKey(rowNum + "," + colNum)) { 142 map[1].remove(rowNum + "," + colNum); 143 continue; 144 } else { 145 sb.append("<td "); 146 } 147 148 //判断是否需要样式 149 if(isWithStyle){ 150 dealExcelStyle(wb, sheet, cell, sb);//处理单元格样式 151 } 152 153 sb.append(">"); 154 if (stringValue == null || "".equals(stringValue.trim())) { 155 sb.append(" "); 156 } else { 157 // 将ascii码为160的空格转换为html下的空格（ ） 158 sb.append(stringValue.replace(String.valueOf((char) 160)," ")); 159 } 160 sb.append("</td>"); 161 } 162 sb.append("</tr>"); 163 } 164 165 sb.append("</table>"); 166 return sb.toString(); 167 } 168 169 private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) { 170 171 Map<String, String> map0 = new HashMap<String, String>(); 172 Map<String, String> map1 = new HashMap<String, String>(); 173 int mergedNum = sheet.getNumMergedRegions(); 174 CellRangeAddress range = null; 175 for (int i = 0; i < mergedNum; i++) { 176 range = sheet.getMergedRegion(i); 177 int topRow = range.getFirstRow(); 178 int topCol = range.getFirstColumn(); 179 int bottomRow = range.getLastRow(); 180 int bottomCol = range.getLastColumn(); 181 map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); 182 // System.out.println(topRow + "," + topCol + "," + bottomRow + "," + bottomCol); 183 int tempRow = topRow; 184 while (tempRow <= bottomRow) { 185 int tempCol = topCol; 186 while (tempCol <= bottomCol) { 187 map1.put(tempRow + "," + tempCol, ""); 188 tempCol++; 189 } 190 tempRow++; 191 } 192 map1.remove(topRow + "," + topCol); 193 } 194 Map[] map = { map0, map1 }; 195 return map; 196 } 197 198 199 /** 200 * 获取表格单元格Cell内容 201 * @param cell 202 * @return 203 */ 204 private static String getCellValue(Cell cell) { 205 206 String result = new String(); 207 switch (cell.getCellType()) { 208 case Cell.CELL_TYPE_NUMERIC:// 数字类型 209 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 210 SimpleDateFormat sdf = null; 211 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { 212 sdf = new SimpleDateFormat("HH:mm"); 213 } else {// 日期 214 sdf = new SimpleDateFormat("yyyy-MM-dd"); 215 } 216 Date date = cell.getDateCellValue(); 217 result = sdf.format(date); 218 } else if (cell.getCellStyle().getDataFormat() == 58) { 219 // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) 220 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 221 double value = cell.getNumericCellValue(); 222 Date date = org.apache.poi.ss.usermodel.DateUtil 223 .getJavaDate(value); 224 result = sdf.format(date); 225 } else { 226 double value = cell.getNumericCellValue(); 227 CellStyle style = cell.getCellStyle(); 228 DecimalFormat format = new DecimalFormat(); 229 String temp = style.getDataFormatString(); 230 // 单元格设置成常规 231 if (temp.equals("General")) { 232 format.applyPattern("#"); 233 } 234 result = format.format(value); 235 } 236 break; 237 case Cell.CELL_TYPE_STRING:// String类型 238 result = cell.getRichStringCellValue().toString(); 239 break; 240 case Cell.CELL_TYPE_BLANK: 241 result = ""; 242 break; 243 default: 244 result = ""; 245 break; 246 } 247 return result; 248 } 249 250 /** 251 * 处理表格样式 252 * @param wb 253 * @param sheet 254 * @param cell 255 * @param sb 256 */ 257 private static void dealExcelStyle(Workbook wb,Sheet sheet,Cell cell,StringBuffer sb){ 258 259 CellStyle cellStyle = cell.getCellStyle(); 260 if (cellStyle != null) { 261 short alignment = cellStyle.getAlignment(); 262 sb.append("align=‘" + convertAlignToHtml(alignment) + "‘ ");//单元格内容的水平对齐方式 263 short verticalAlignment = cellStyle.getVerticalAlignment(); 264 sb.append("valign=‘"+ convertVerticalAlignToHtml(verticalAlignment)+ "‘ ");//单元格中内容的垂直排列方式 265 266 if (wb instanceof XSSFWorkbook) { 267 268 XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont(); 269 short boldWeight = xf.getBoldweight(); 270 sb.append("style=‘"); 271 sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 272 sb.append("font-size: " + xf.getFontHeight() / 2 + "%;"); // 字体大小 273 int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) ; 274 sb.append("width:" + columnWidth + "px;"); 275 276 XSSFColor xc = xf.getXSSFColor(); 277 if (xc != null && !"".equals(xc)) { 278 sb.append("color:#" + xc.getARGBHex().substring(2) + ";"); // 字体颜色 279 } 280 281 XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor(); 282 //System.out.println("************************************"); 283 //System.out.println("BackgroundColorColor: "+cellStyle.getFillBackgroundColorColor()); 284 //System.out.println("ForegroundColor: "+cellStyle.getFillForegroundColor());//0 285 //System.out.println("BackgroundColorColor: "+cellStyle.getFillBackgroundColorColor()); 286 //System.out.println("ForegroundColorColor: "+cellStyle.getFillForegroundColorColor()); 287 //String bgColorStr = bgColor.getARGBHex(); 288 //System.out.println("bgColorStr: "+bgColorStr); 289 if (bgColor != null && !"".equals(bgColor)) { 290 sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";"); // 背景颜色 291 } 292 sb.append(getBorderStyle(0,cellStyle.getBorderTop(), ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor())); 293 sb.append(getBorderStyle(1,cellStyle.getBorderRight(), ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor())); 294 sb.append(getBorderStyle(2,cellStyle.getBorderBottom(), ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor())); 295 sb.append(getBorderStyle(3,cellStyle.getBorderLeft(), ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor())); 296 297 }else if(wb instanceof HSSFWorkbook){ 298 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 sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); // 字体大小 307 String fontColorStr = convertToStardColor(hc); 308 if (fontColorStr != null && !"".equals(fontColorStr.trim())) { 309 sb.append("color:" + fontColorStr + ";"); // 字体颜色 310 } 311 int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) ; 312 sb.append("width:" + columnWidth + "px;"); 313 short bgColor = cellStyle.getFillForegroundColor(); 314 hc = palette.getColor(bgColor); 315 String bgColorStr = convertToStardColor(hc); 316 if (bgColorStr != null && !"".equals(bgColorStr.trim())) { 317 sb.append("background-color:" + bgColorStr + ";"); // 背景颜色 318 } 319 sb.append( getBorderStyle(palette,0,cellStyle.getBorderTop(),cellStyle.getTopBorderColor())); 320 sb.append( getBorderStyle(palette,1,cellStyle.getBorderRight(),cellStyle.getRightBorderColor())); 321 sb.append( getBorderStyle(palette,3,cellStyle.getBorderLeft(),cellStyle.getLeftBorderColor())); 322 sb.append( getBorderStyle(palette,2,cellStyle.getBorderBottom(),cellStyle.getBottomBorderColor())); 323 } 324 325 sb.append("‘ "); 326 } 327 } 328 329 /** 330 * 单元格内容的水平对齐方式 331 * @param alignment 332 * @return 333 */ 334 private static String convertAlignToHtml(short alignment) { 335 336 String align = "left"; 337 switch (alignment) { 338 case CellStyle.ALIGN_LEFT: 339 align = "left"; 340 break; 341 case CellStyle.ALIGN_CENTER: 342 align = "center"; 343 break; 344 case CellStyle.ALIGN_RIGHT: 345 align = "right"; 346 break; 347 default: 348 break; 349 } 350 return align; 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 private static String convertToStardColor(HSSFColor hc) { 378 379 StringBuffer sb = new StringBuffer(""); 380 if (hc != null) { 381 if (HSSFColor.AUTOMATIC.index == hc.getIndex()) { 382 return null; 383 } 384 sb.append("#"); 385 for (int i = 0; i < hc.getTriplet().length; i++) { 386 sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); 387 } 388 } 389 390 return sb.toString(); 391 } 392 393 private static String fillWithZero(String str) { 394 if (str != null && str.length() < 2) { 395 return "0" + str; 396 } 397 return str; 398 } 399 400 static String[] bordesr={"border-top:","border-right:","border-bottom:","border-left:"}; 401 static String[] borderStyles={"solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid","solid","solid","solid","solid"}; 402 403 private static String getBorderStyle( HSSFPalette palette ,int b,short s, short t){ 404 405 if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";; 406 String borderColorStr = convertToStardColor( palette.getColor(t)); 407 borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr; 408 return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;"; 409 410 } 411 412 private static String getBorderStyle(int b,short s, XSSFColor xc){ 413 414 if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";; 415 if (xc != null && !"".equals(xc)) { 416 String borderColorStr = xc.getARGBHex();//t.getARGBHex(); 417 borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr.substring(2); 418 return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;"; 419 } 420 421 return ""; 422 } 423 424 }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
