你的位置:首页 > Java教程

[Java教程]POI 读取 Excel 转 HTML 支持 03xls 和 07xlsx 版本 包含样式


 

工作需求:

  提供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 > &nbsp;</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>&nbsp;</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(" &nbsp; ");156         } else {157           // 将ascii码为160的空格转换为html下的空格(&nbsp;)158           sb.append(stringValue.replace(String.valueOf((char) 160),"&nbsp;"));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