excel转html 实现在线预览
2021-07-14 23:04
阅读：515
YPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
标签：ble first eof 排版 length 单元 多版本 fda img
首先说一下，本人发布的代码都是经过本人亲测，并且用在实际项目中。如果觉得可以，希望大家点个赞，谢谢大家。
有什么问题，大家评论出来，一起交流。好了，不废话了，下面来说一说这个东西怎么做。
网上也有许多版本，不过都不是我想要的，转出的html文件，要带表格，而且还能根据excel文件样式来生成html文件，不过太复杂的样式就支持不太强大了。
我采用poi去做这个功能，虽然还有jxl等其它方式，其它方式都有大的缺陷，有的不支持excel07版本，有的不能跨平台使用，这些都是很致命的缺点。
所以我采用poi去研究，我测试office和wps的excel版本都支持，并且兼容excel03及07以后版本，效果达到我的要求了，下面贴出源码，供大家一起学习。
jar包主要是这些 
1
import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/** 2 3 * @param filePath excel源文件文件的路径 4 * @param htmlPositon 生成的html文件的路径 5 * @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式 6 7 */ 8 public static String readExcelToHtml(String filePath ,String htmlPositon, boolean isWithStyle){ 9 10 InputStream is = null; 11 String htmlExcel = null; 12 try { 13 File sourcefile = new File(filePath); 14 is = new FileInputStream(sourcefile); 15 Workbook wb = WorkbookFactory.create(is); 16 if (wb instanceof XSSFWorkbook) { //03版excel处理方法 17 XSSFWorkbook xWb = (XSSFWorkbook) wb; 18 htmlExcel = FileConverter.getExcelInfo(xWb,isWithStyle); 19 }else if(wb instanceof HSSFWorkbook){ //07及10版以后的excel处理方法 20 HSSFWorkbook hWb = (HSSFWorkbook) wb; 21 htmlExcel = FileConverter.getExcelInfo(hWb,isWithStyle); 22 } 23 writeFile(htmlExcel,htmlPositon); 24 } catch (Exception e) { 25 e.printStackTrace(); 26 }finally{ 27 try { 28 is.close(); 29 } catch (IOException e) { 30 e.printStackTrace(); 31 } 32 } 33 return htmlPositon; 34 } 35 36 37 38 private static String getExcelInfo(Workbook wb,boolean isWithStyle){ 39 40 StringBuffer sb = new StringBuffer(); 41 Sheet sheet = wb.getSheetAt(0);//获取第一个Sheet的内容 42 int lastRowNum = sheet.getLastRowNum(); 43 Mapmap[] = getRowSpanColSpanMap(sheet); 44 sb.append(" "); 45 Row row = null; //兼容 46 Cell cell = null; //兼容 47 48 for (int rowNum = sheet.getFirstRowNum(); rowNum ) { 49 row = sheet.getRow(rowNum); 50 if (row == null) { 51 sb.append("
"); 97 return sb.toString(); 98 } 99 100 private static Map"); 52 continue; 53 } 54 sb.append(" "); 55 int lastColNum = row.getLastCellNum(); 56 for (int colNum = 0; colNum ) { 57 cell = row.getCell(colNum); 58 if (cell == null) { //特殊情况 空白的单元格会返回null 59 sb.append(" "); 94 } 95 96 sb.append(""); 60 continue; 61 } 62 63 String stringValue = getCellValue(cell); 64 if (map[0].containsKey(rowNum + "," + colNum)) { 65 String pointString = map[0].get(rowNum + "," + colNum); 66 map[0].remove(rowNum + "," + colNum); 67 int bottomeRow = Integer.valueOf(pointString.split(",")[0]); 68 int bottomeCol = Integer.valueOf(pointString.split(",")[1]); 69 int rowSpan = bottomeRow - rowNum + 1; 70 int colSpan = bottomeCol - colNum + 1; 71 sb.append(" ); 72 } else if (map[1].containsKey(rowNum + "," + colNum)) { 73 map[1].remove(rowNum + "," + colNum); 74 continue; 75 } else { 76 sb.append(" ); 77 } 78 79 //判断是否需要样式 80 if(isWithStyle){ 81 dealExcelStyle(wb, sheet, cell, sb);//处理单元格样式 82 } 83 84 sb.append("> "); 92 } 93 sb.append(""); 85 if (stringValue == null || "".equals(stringValue.trim())) { 86 sb.append(" "); 87 } else { 88 // 将ascii码为160的空格转换为html下的空格（ ） 89 sb.append(stringValue.replace(String.valueOf((char) 160)," ")); 90 } 91 sb.append(" [] getRowSpanColSpanMap(Sheet sheet) { 101 102 Map map0 = new HashMap (); 103 Map map1 = new HashMap (); 104 int mergedNum = sheet.getNumMergedRegions(); 105 CellRangeAddress range = null; 106 for (int i = 0; i ) { 107 range = sheet.getMergedRegion(i); 108 int topRow = range.getFirstRow(); 109 int topCol = range.getFirstColumn(); 110 int bottomRow = range.getLastRow(); 111 int bottomCol = range.getLastColumn(); 112 map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); 113 // System.out.println(topRow + "," + topCol + "," + bottomRow + "," + bottomCol); 114 int tempRow = topRow; 115 while (tempRow bottomRow) { 116 int tempCol = topCol; 117 while (tempCol bottomCol) { 118 map1.put(tempRow + "," + tempCol, ""); 119 tempCol++; 120 } 121 tempRow++; 122 } 123 map1.remove(topRow + "," + topCol); 124 } 125 Map[] map = { map0, map1 }; 126 return map; 127 } 128 129 130 /** 131 * 获取表格单元格Cell内容 132 * @param cell 133 * @return 134 */ 135 private static String getCellValue(Cell cell) { 136 137 String result = new String(); 138 switch (cell.getCellType()) { 139 case Cell.CELL_TYPE_NUMERIC:// 数字类型 140 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 141 SimpleDateFormat sdf = null; 142 if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { 143 sdf = new SimpleDateFormat("HH:mm"); 144 } else {// 日期 145 sdf = new SimpleDateFormat("yyyy-MM-dd"); 146 } 147 Date date = cell.getDateCellValue(); 148 result = sdf.format(date); 149 } else if (cell.getCellStyle().getDataFormat() == 58) { 150 // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) 151 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); 152 double value = cell.getNumericCellValue(); 153 Date date = org.apache.poi.ss.usermodel.DateUtil 154 .getJavaDate(value); 155 result = sdf.format(date); 156 } else { 157 double value = cell.getNumericCellValue(); 158 CellStyle style = cell.getCellStyle(); 159 DecimalFormat format = new DecimalFormat(); 160 String temp = style.getDataFormatString(); 161 // 单元格设置成常规 162 if (temp.equals("General")) { 163 format.applyPattern("#"); 164 } 165 result = format.format(value); 166 } 167 break; 168 case Cell.CELL_TYPE_STRING:// String类型 169 result = cell.getRichStringCellValue().toString(); 170 break; 171 case Cell.CELL_TYPE_BLANK: 172 result = ""; 173 break; 174 default: 175 result = ""; 176 break; 177 } 178 return result; 179 } 180 181 /** 182 * 处理表格样式 183 * @param wb 184 * @param sheet 185 * @param sb 186 */ 187 private static void dealExcelStyle(Workbook wb,Sheet sheet,Cell cell,StringBuffer sb){ 188 189 CellStyle cellStyle = cell.getCellStyle(); 190 if (cellStyle != null) { 191 short alignment = cellStyle.getAlignment(); 192 // sb.append("align=‘" + convertAlignToHtml(alignment) + "‘ ");//单元格内容的水平对齐方式 193 short verticalAlignment = cellStyle.getVerticalAlignment(); 194 sb.append("valign=‘"+ convertVerticalAlignToHtml(verticalAlignment)+ "‘ ");//单元格中内容的垂直排列方式 195 196 if (wb instanceof XSSFWorkbook) { 197 198 XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont(); 199 short boldWeight = xf.getBoldweight(); 200 String align = convertAlignToHtml(alignment); 201 sb.append("style=‘"); 202 sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 203 sb.append("font-size: " + xf.getFontHeight() / 2 + "%;"); // 字体大小 204 int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) ; 205 sb.append("width:" + columnWidth + "px;"); 206 sb.append("text-align:" + align + ";");//表头排版样式 207 XSSFColor xc = xf.getXSSFColor(); 208 if (xc != null && !"".equals(xc)) { 209 sb.append("color:#" + xc.getARGBHex().substring(2) + ";"); // 字体颜色 210 } 211 212 XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor(); 213 if (bgColor != null && !"".equals(bgColor)) { 214 sb.append("background-color:#" + bgColor.getARGBHex().substring(2) + ";"); // 背景颜色 215 } 216 sb.append(getBorderStyle(0,cellStyle.getBorderTop(), ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor())); 217 sb.append(getBorderStyle(1,cellStyle.getBorderRight(), ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor())); 218 sb.append(getBorderStyle(2,cellStyle.getBorderBottom(), ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor())); 219 sb.append(getBorderStyle(3,cellStyle.getBorderLeft(), ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor())); 220 221 }else if(wb instanceof HSSFWorkbook){ 222 223 HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb); 224 short boldWeight = hf.getBoldweight(); 225 short fontColor = hf.getColor(); 226 sb.append("style=‘"); 227 HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式 228 HSSFColor hc = palette.getColor(fontColor); 229 sb.append("font-weight:" + boldWeight + ";"); // 字体加粗 230 sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); // 字体大小 231 String align = convertAlignToHtml(alignment); 232 sb.append("text-align:" + align + ";");//表头排版样式 233 String fontColorStr = convertToStardColor(hc); 234 if (fontColorStr != null && !"".equals(fontColorStr.trim())) { 235 sb.append("color:" + fontColorStr + ";"); // 字体颜色 236 } 237 int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) ; 238 sb.append("width:" + columnWidth + "px;"); 239 short bgColor = cellStyle.getFillForegroundColor(); 240 hc = palette.getColor(bgColor); 241 String bgColorStr = convertToStardColor(hc); 242 if (bgColorStr != null && !"".equals(bgColorStr.trim())) { 243 sb.append("background-color:" + bgColorStr + ";"); // 背景颜色 244 } 245 sb.append( getBorderStyle(palette,0,cellStyle.getBorderTop(),cellStyle.getTopBorderColor())); 246 sb.append( getBorderStyle(palette,1,cellStyle.getBorderRight(),cellStyle.getRightBorderColor())); 247 sb.append( getBorderStyle(palette,3,cellStyle.getBorderLeft(),cellStyle.getLeftBorderColor())); 248 sb.append( getBorderStyle(palette,2,cellStyle.getBorderBottom(),cellStyle.getBottomBorderColor())); 249 } 250 251 sb.append("‘ "); 252 } 253 } 254 255 /** 256 * 单元格内容的水平对齐方式 257 * @param alignment 258 * @return 259 */ 260 private static String convertAlignToHtml(short alignment) { 261 262 String align = "center"; 263 switch (alignment) { 264 case CellStyle.ALIGN_LEFT: 265 align = "left"; 266 break; 267 case CellStyle.ALIGN_CENTER: 268 align = "center"; 269 break; 270 case CellStyle.ALIGN_RIGHT: 271 align = "right"; 272 break; 273 default: 274 break; 275 } 276 return align; 277 } 278 279 /** 280 * 单元格中内容的垂直排列方式 281 * @param verticalAlignment 282 * @return 283 */ 284 private static String convertVerticalAlignToHtml(short verticalAlignment) { 285 286 String valign = "middle"; 287 switch (verticalAlignment) { 288 case CellStyle.VERTICAL_BOTTOM: 289 valign = "bottom"; 290 break; 291 case CellStyle.VERTICAL_CENTER: 292 valign = "center"; 293 break; 294 case CellStyle.VERTICAL_TOP: 295 valign = "top"; 296 break; 297 default: 298 break; 299 } 300 return valign; 301 } 302 303 private static String convertToStardColor(HSSFColor hc) { 304 305 StringBuffer sb = new StringBuffer(""); 306 if (hc != null) { 307 if (HSSFColor.AUTOMATIC.index == hc.getIndex()) { 308 return null; 309 } 310 sb.append("#"); 311 for (int i = 0; i ) { 312 sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); 313 } 314 } 315 316 return sb.toString(); 317 } 318 319 private static String fillWithZero(String str) { 320 if (str != null && str.length() ) { 321 return "0" + str; 322 } 323 return str; 324 } 325 326 static String[] bordesr={"border-top:","border-right:","border-bottom:","border-left:"}; 327 static String[] borderStyles={"solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid","solid","solid","solid","solid"}; 328 329 private static String getBorderStyle( HSSFPalette palette ,int b,short s, short t){ 330 331 if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";; 332 String borderColorStr = convertToStardColor( palette.getColor(t)); 333 borderColorStr=borderColorStr==null|| borderColorStr.length():borderColorStr; 334 return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;"; 335 336 } 337 338 private static String getBorderStyle(int b,short s, XSSFColor xc){ 339 340 if(s==0)return bordesr[b]+borderStyles[s]+"#d0d7e5 1px;";; 341 if (xc != null && !"".equals(xc)) { 342 String borderColorStr = xc.getARGBHex();//t.getARGBHex(); 343 borderColorStr=borderColorStr==null|| borderColorStr.length()); 344 return bordesr[b]+borderStyles[s]+borderColorStr+" 1px;"; 345 } 346 347 return ""; 348 } 349 /* 350 * @param content 生成的excel表格标签 351 * @param htmlPath 生成的html文件地址 352 */ 353 private static void writeFile(String content,String htmlPath){ 354 File file2 = new File(htmlPath); 355 StringBuilder sb = new StringBuilder(); 356 try { 357 file2.createNewFile();//创建文件 358 359 sb.append(" Html Test "); 360 sb.append(""); 361 sb.append(content); 362 sb.append(""); 363 sb.append(""); 364 365 PrintStream printStream = new PrintStream(new FileOutputStream(file2)); 366 367 printStream.println(sb.toString());//将字符串写入文件 368 369 } catch (IOException e) { 370 371 e.printStackTrace(); 372 } 373 374 } 375 376
大家如果有什么不懂的，希望大家提出来，一起交流，一起进步。
excel转html 实现在线预览
标签：ble first eof 排版 length 单元 多版本 fda img
原文地址：http://www.cnblogs.com/chengpanpan/p/7074275.html
评论
亲，登录后才可以留言！