提供EXCEL模板上傳后預覽；EXCEL解析成終端風格HTML。
處理方案：POI解析EXCEL，預覽時盡量獲取原有表格的樣式；終端使用EXCEL解析的無樣式HTML，然后通過jQuery添加CSS樣式
遇到問題：CSDN上大牛處理03版xls格式的有成功例子；但是07版xlsx格式的樣式處理未找到理想中的例子
下文是參考大牛的例子整理后的程序 供參考！
EXCEL表格07xlsx格式
通過POI解析帶樣式的效果
項目JAR文件注意版本
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) { //特殊情況 空白的單元格會返回null128 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 cell202 * @return203 */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 wb253 * @param sheet254 * @param cell255 * @param sb256 */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());//0285 //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 alignment332 * @return333 */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 verticalAlignment356 * @return357 */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 }View Code新聞熱點
疑難解答
