首页 > 代码库 > excel转html 实现在线预览

excel转html 实现在线预览

首先说一下,本人发布的代码都是经过本人亲测,并且用在实际项目中。如果觉得可以,希望大家点个赞,谢谢大家。

有什么问题,大家评论出来,一起交流。好了,不废话了,下面来说一说这个东西怎么做。

网上也有许多版本,不过都不是我想要的,转出的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              Map<String, String> map[] = getRowSpanColSpanMap(sheet);
 44              sb.append("<table style=‘border-collapse:collapse;‘ width=‘100%‘>");
 45              Row row = null;        //兼容
 46              Cell cell = null;    //兼容
 47              
 48              for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
 49                  row = sheet.getRow(rowNum);
 50                  if (row == null) {
 51                      sb.append("<tr><td ><nobr> </nobr></td></tr>");
 52                      continue;
 53                  }
 54                  sb.append("<tr>");
 55                  int lastColNum = row.getLastCellNum();
 56                  for (int colNum = 0; colNum < lastColNum; colNum++) {
 57                      cell = row.getCell(colNum);
 58                      if (cell == null) {    //特殊情况 空白的单元格会返回null
 59                          sb.append("<td> </td>");
 60                          continue;
 61                      }
 62      
 63                      String stringValue =http://www.mamicode.com/ 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("<td rowspan= ‘" + rowSpan + "‘ colspan= ‘"+ colSpan + "‘ ");
 72                      } else if (map[1].containsKey(rowNum + "," + colNum)) {
 73                          map[1].remove(rowNum + "," + colNum);
 74                          continue;
 75                      } else {
 76                          sb.append("<td ");
 77                      }
 78                      
 79                      //判断是否需要样式
 80                      if(isWithStyle){
 81                          dealExcelStyle(wb, sheet, cell, sb);//处理单元格样式
 82                      }
 83                      
 84                      sb.append("><nobr>");
 85                      if (stringValue =http://www.mamicode.com/= 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("</nobr></td>");
 92                  }
 93                  sb.append("</tr>");
 94              }
 95      
 96              sb.append("</table>");
 97              return sb.toString();
 98          }
 99          
100          private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) {
101      
102              Map<String, String> map0 = new HashMap<String, String>();
103              Map<String, String> map1 = new HashMap<String, String>();
104              int mergedNum = sheet.getNumMergedRegions();
105              CellRangeAddress range = null;
106              for (int i = 0; i < mergedNum; 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 =http://www.mamicode.com/ cell.getNumericCellValue();  
153                      Date date = org.apache.poi.ss.usermodel.DateUtil  
154                              .getJavaDate(value);  
155                      result = sdf.format(date);  
156                  } else {  
157                      double value =http://www.mamicode.com/ 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 < hc.getTriplet().length; 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() < 2) {
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()<1?"#000000":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()<1?"#000000":borderColorStr.substring(2);
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><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=GBK\"><title>Html Test</title></head><body>");
360                sb.append("<div>");
361                sb.append(content);
362                sb.append("</div>");
363                sb.append("</body></html>");
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 实现在线预览