poi批量导入html,读取excel（POI）【转换为html】 - bcoffee的专栏 - 博客频道

读取excel(POI)【转换为html】 - bcoffee的专栏 - 博客频道 - CSDN.NET

2018-11-21

package test;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;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;public class POIReadExcel {public static void main(String[] args){try {POIReadExcel poire = new POIReadExcel();String path = "D://test.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();}}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("");HSSFRow row = null;HSSFCell cell=null;//System.out.println(sheet.getPhysicalNumberOfRows());for(int rowNum = sheet.getFirstRowNum(); rowNum < lastRowNum rowNum p>row = (HSSFRow) sheet.getRow(rowNum);if(row == null){sb.append(" ");continue;}sb.append("");int lastColNum = row.getLastCellNum();for(int colNum = 0; colNum < lastColNum colNum br style="font-size:14px;font-style:normal;font-weight:400;color:#333;" />cell = row.getCell(colNum);if(cell == null){sb.append(" ");continue;}String stringValue = getCellValue(cell);if(map[0].containsKey(rowNum + "," + colNum)) {String pointString = map[0].get(rowNum + "," + colNum);map[0].remove(rowNum + "," + colNum);int bottomeRow = Integer.valueOf(pointString.split(",")[0]);int bottomeCol = Integer.valueOf(pointString.split(",")[1]);int rowSpan = bottomeRow - rowNum + 1;int colSpan = bottomeCol - colNum + 1;sb.append("} else if(map[1].containsKey(rowNum + "," + colNum)){map[1].remove(rowNum + "," + colNum);continue;} else {sb.append("}HSSFCellStyle cellStyle = cell.getCellStyle();if(cellStyle != null){short alignment = cellStyle.getAlignment();sb.append("align="" + convertAlignToHtml(alignment) + "" ");short verticalAlignment = cellStyle.getVerticalAlignment();sb.append("valign="" + convertVerticalAlignToHtml(verticalAlignment) +"" ");HSSFFont hf = cellStyle.getFont(wb);short boldWeight = hf.getBoldweight() ;short fontColor = hf.getColor();sb.append("style="");HSSFPalette palette = wb.getCustomPalette(); //类HSSFPalette用于求的颜色的国际标准形式HSSFColor hc = palette.getColor(fontColor);sb.append("font-weight:" + boldWeight + ";"); //字体加粗//System.out.println(hf.getFontHeight());sb.append("font-size: " + hf.getFontHeight()/2 + "%;"); //字体大小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 + ";"); //背景颜色}short borderColor = cellStyle.getBottomBorderColor() ;hc = palette.getColor(borderColor);String borderColorStr = convertToStardColor(hc);if(borderColorStr != null && !"".equals(borderColorStr.trim())){sb.append("border-color:" + borderColorStr + ";"); //边框颜色}// boolean borderBoolean = cellStyle.getWrapText(); if(borderBoolean){// sb.append("border-style: inset;");// }sb.append("" ");}sb.append("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 p>int tempCol = topCol;while(tempCol < bottomCol br style="font-size:14px;font-style:normal;font-weight:400;color:#333;" />map1.put(tempRow + "," + tempCol,"");tempCol ++;}tempRow ++;}map1.remove(topRow + "," + topCol);}Map[] map = {map0,map1};return map;}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;}private 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 getTripletlength i br style="font-size:14px;font-style:normal;font-weight:400;color:#333;" />sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i]))) ;}}return sb.toString();}private String fillWithZero(String str){if(str != null && str.length() < 2 br style="font-size:14px;font-style:normal;font-weight:400;color:#333;" />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 "";}}}

免责声明：本文仅代表文章作者的个人观点，与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺，请读者仅作参考，并自行核实相关内容。

http://www.pinlue.com/style/images/nopic.gif