读取excel文件.xls格式转为html格式在页面中显示

分手后的思念是犯贱 2022-04-11 13:18 383阅读 0赞

使用poi的jar包，读取excel文件内容

import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFPalette;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    /**
     * 利用POI读取excel,转换为html
     * 
     */
    public class POIReadExcel {
    	@SuppressWarnings("rawtypes")
        public String getExcelInfo(HSSFWorkbook wb) throws Exception {
    		StringBuffer sb = new StringBuffer();
    		Sheet sheet = wb.getSheetAt(0);
    		int lastRowNum = sheet.getLastRowNum();
    		Map map[] = getRowSpanColSpanMap(sheet);
    		sb.append("<table class='excel_table'><tbody>");
    		HSSFRow row = null;
    		HSSFCell cell = null;
    		
    		// System.out.println(sheet.getPhysicalNumberOfRows());
    		for (int rowNum = sheet.getFirstRowNum(); rowNum < lastRowNum; rowNum++) {//遍历每一行
    			row = (HSSFRow) sheet.getRow(rowNum);
    			if (row == null) {
    				sb.append(" ");
    				continue;
    			}
    			
    			int height = row.getHeight()/15;
    			
    			sb.append("<tr>");
    			int lastColNum = row.getLastCellNum();//这一行有多少列
    			
    			int[] widthArray = new int[lastColNum];
    			for (int colNum = 0; colNum < lastColNum; colNum++) {
    				widthArray[colNum] = sheet.getColumnWidth(colNum)/32;
    			}
    			
    			int width = 0;
    			for (int colNum = 0; colNum < lastColNum; colNum++) {//遍历这一行的每一列
    				cell = row.getCell(colNum);
    				if (cell == null) {
    					sb.append(" ");
    					continue;
    				}
    				int bottomeCol = 0;
    				int colSpan = 0;
    				String stringValue = getCellValue(cell);//读取这行这列的内容
    				if (map[0].containsKey(rowNum + "," + colNum)) {//如果这是合并单元格的第一个就进这里
    					String pointString = (String) map[0].get(rowNum + "," + colNum);
    					map[0].remove(rowNum + "," + colNum);
    					//int bottomeRow = Integer.valueOf(pointString.split(",")[0]);
    					bottomeCol = Integer.valueOf(pointString.split(",")[1]);
    					//int rowSpan = bottomeRow - rowNum + 1;//占了多少行
    					colSpan = bottomeCol - colNum + 1;//占了多少列
    					//width = single.multiply(new BigDecimal(colSpan));
    					for (int i = colNum; i <= bottomeCol; i++) {
    						width = widthArray[i] + width;
    					}
    					sb.append(" ");
    				} else if (map[1].containsKey(rowNum + "," + colNum)) {//如果这是合并单元格的非第一个就进这里
    					map[1].remove(rowNum + "," + colNum);
    					continue;
    				} else {
    					colSpan = 1;
    					sb.append(" ");//如果这个格不是合并单元格就进这里
    					width = widthArray[colNum];
    				}
    				HSSFCellStyle cellStyle = cell.getCellStyle();
    				
    				if (cellStyle != null) {//给这行的这列添加样式
    					HSSFFont hf = cellStyle.getFont(wb);
    					short fontColor = hf.getColor();
    					HSSFPalette palette = wb.getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式
    					HSSFColor hc = palette.getColor(fontColor);
    					
    					sb.append("<td");
    					sb.append(" colspan='"+colSpan+"'");//占多少列
    					
    					sb.append(" style='");//style开始
    					sb.append("width:"+width+"px;height:"+height+"px;");//列宽
    					
    					short boldWeight = hf.getBoldweight();//font-weight属性
    					sb.append("font-weight:"+boldWeight+";");
    					
    					String fontColorStr = convertToStardColor(hc);
    					if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
    						sb.append("color:" + fontColorStr + ";"); // 字体颜色
    					}
    					short bgColor = cellStyle.getFillForegroundColor();
    					hc = palette.getColor(bgColor);
    					String bgColorStr = convertToStardColor(hc);
    					if (bgColorStr != null && !"".equals(bgColorStr.trim())) {
    						sb.append("background-color:" + bgColorStr + ";"); // 背景颜色
    					}
    					
    					//border-top: 5px solid #000;
    					short borderTop = cellStyle.getBorderTop();
    					short borderRight = cellStyle.getBorderRight();
    					short borderBottom = cellStyle.getBorderBottom();
    					short borderLeft = cellStyle.getBorderLeft();
    					if (borderTop > 0) {
    						sb.append("border-top: 1px solid #000;"); // 上边框
    					}
    					if (borderRight > 0) {
    						sb.append("border-right: 1px solid #000;"); // 右边框
    					}
    					if (borderBottom > 0) {
    						sb.append("border-bottom: 1px solid #000;"); // 下边框
    					}
    					if (borderLeft > 0) {
    						sb.append("border-left: 1px solid #000;"); // 左边框
    					}
    					
    					sb.append("'>");//style终止
    					
    					
    					short alignment = cellStyle.getAlignment();//对齐方式
    					sb.append("<p style='text-align:"+convertAlignToHtml(alignment)+";'>");
    					
    					sb.append("<span style='font-size:"+hf.getFontHeight() / 20+"pt;'>");// 字体大小
    					
    					if (stringValue == null || "".equals(stringValue.trim())) {//值为空
    						sb.append("");
    					} else {//值不为空
    						sb.append(rightTrim(stringValue));
    					}
    					
    					sb.append("</span>");
    					
    					sb.append("</p>");
    					
    					sb.append("</td>");
    					
    					width = 0;
    				}
    				sb.append(" ");
    			}
    			sb.append("</tr>");
    		}
    		sb.append("</tbody></table>");
    		return sb.toString();
    	}
    	
    	@SuppressWarnings({ "unchecked", "rawtypes" })
    	private Map[] getRowSpanColSpanMap(Sheet sheet) {
    		Map map0 = new HashMap();
    		Map map1 = new HashMap();
    		int mergedNum = sheet.getNumMergedRegions();
    		CellRangeAddress range = null;
    		for (int i = 0; i < mergedNum; i++) {
    			range = sheet.getMergedRegion(i);
    			int topRow = range.getFirstRow();
    			int topCol = range.getFirstColumn();
    			int bottomRow = range.getLastRow();
    			int bottomCol = range.getLastColumn();
    			map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);
    			// System.out.println(topRow + "," + topCol + "," + bottomRow + ","
    			// + bottomCol);
    			int tempRow = topRow;
    			while (tempRow <= bottomRow) {
    				int tempCol = topCol;
    				while (tempCol <= bottomCol) {
    					map1.put(tempRow + "," + tempCol, "");
    					tempCol++;
    				}
    				tempRow++;
    			}
    			map1.remove(topRow + "," + topCol);
    		}
    		Map[] map = { map0, map1 };
    		return map;
    	}
    
    	/**
         * 去掉字符串右边的空格
         * @param str 要处理的字符串
         * @return 处理后的字符串
         */
         public static String rightTrim(String str) {
           if (str == null) {
               return "";
           }
           int length = str.length();
           for (int i = length - 1; i >= 0; i--) {
               if (str.charAt(i) != 0x20) {
                  break;
               }
               length--;
           }
           return str.substring(0, length);
        }
    	
    	private String convertAlignToHtml(short alignment) {
    		String align = "left";
    		switch (alignment) {
    		case HSSFCellStyle.ALIGN_LEFT:
    			align = "left";
    			break;
    		case HSSFCellStyle.ALIGN_CENTER:
    			align = "center";
    			break;
    		case HSSFCellStyle.ALIGN_RIGHT:
    			align = "right";
    			break;
    		default:
    			break;
    		}
    		return align;
    	}
    
    	public String convertVerticalAlignToHtml(short verticalAlignment) {
    		String valign = "middle";
    		switch (verticalAlignment) {
    		case HSSFCellStyle.VERTICAL_BOTTOM:
    			valign = "bottom";
    			break;
    		case HSSFCellStyle.VERTICAL_CENTER:
    			valign = "center";
    			break;
    		case HSSFCellStyle.VERTICAL_TOP:
    			valign = "top";
    			break;
    		default:
    			break;
    		}
    		return valign;
    	}
    
    	private String convertToStardColor(HSSFColor hc) {
    		StringBuffer sb = new StringBuffer("");
    		if (hc != null) {
    			if (HSSFColor.AUTOMATIC.index == hc.getIndex()) {
    				return null;
    			}
    			sb.append("#");
    			for (int i = 0; i < hc.getTriplet().length; i++) {
    				sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
    			}
    		}
    		return sb.toString();
    	}
    
    	private String fillWithZero(String str) {
    		if (str != null && str.length() < 2) {
    			return "0" + str;
    		}
    		return str;
    	}
    
    	private String getCellValue(HSSFCell cell) {
    		switch (cell.getCellType()) {
    		case HSSFCell.CELL_TYPE_NUMERIC:
    			DecimalFormat format = new DecimalFormat("#0.##");
    			return format.format(cell.getNumericCellValue());
    			// return String.valueOf(cell.getNumericCellValue());
    		case HSSFCell.CELL_TYPE_STRING:
    			return cell.getStringCellValue();
    			// case HSSFCell.CELL_TYPE_FORMULA:
    			//
    			// return cell.getCellFormula();
    		default:
    			return "";
    		}
    	}
    	
    	public static void main(String[] args) {
    		try {
    			POIReadExcel poire = new POIReadExcel();
    			String path = "E:\\gaofendengji2015.xls";
    			File sourcefile = new File(path);
    			InputStream is = new FileInputStream(sourcefile);
    			POIFSFileSystem fs = new POIFSFileSystem(is);
    			HSSFWorkbook wb = new HSSFWorkbook(fs);
    			System.out.println(poire.getExcelInfo(wb));
    			is.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    }