package uap.pub.fs.pre.poi; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import nc.bs.logging.Logger; import nccloud.framework.core.exception.ExceptionUtils; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFPicture; import org.apache.poi.hssf.usermodel.HSSFPictureData; import org.apache.poi.hssf.usermodel.HSSFShape; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ooxml.POIXMLDocumentPart; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; 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.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFDrawing; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFPicture; import org.apache.poi.xssf.usermodel.XSSFShape; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; import uap.pub.fs.pre.poi.IPoiConvertService; public class ExcleConvertServiceImpl implements IPoiConvertService { OutputStream output; String[] bordesr = new String[]{"border-top:", "border-right:", "border-bottom:", "border-left:"}; String[] borderStyles = new String[]{"solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid ", "solid", "solid", "solid", "solid", "solid"}; public String convertHtml(String fileType, InputStream input, OutputStream output) throws EncryptedDocumentException, IOException { this.output = output; return "xls".equals(fileType)?this.convertBaseFile(input):this.convertXFile(input); } public String convertBaseFile(InputStream input) throws EncryptedDocumentException, IOException { Workbook wb = WorkbookFactory.create(input); HSSFWorkbook hWb = (HSSFWorkbook)wb; String htmlExcel = this.getExcelInfo(hWb, true); return htmlExcel; } public String convertXFile(InputStream input) throws EncryptedDocumentException, IOException { Workbook wb = WorkbookFactory.create(input); XSSFWorkbook xWb = (XSSFWorkbook)wb; String htmlExcel = this.getExcelInfo(xWb, true); return htmlExcel; } public String getExcelInfo(Workbook wb, boolean isWithStyle) throws UnsupportedEncodingException, IOException { StringBuffer sb = new StringBuffer(); sb.append(""); sb.append(""); sb.append("\r\n "); sb.append(""); sb.append("
"); int i; for(i = 0; i < wb.getNumberOfSheets(); ++i) { String sheet = wb.getSheetName(i); if(i == 0) { sb.append("" + sheet + ""); } else { sb.append("" + sheet + ""); } } sb.append("
"); sb.append("
"); for(i = 0; i < wb.getNumberOfSheets(); ++i) { Sheet var22 = wb.getSheetAt(i); sb.append("
"); int lastRowNum = var22.getLastRowNum(); Map[] map = this.getRowSpanColSpanMap(var22); sb.append(""); getSheetPictrues(i, var22, wb); new HashMap(); Row row = null; Cell cell = null; Row rowWidth = var22.getRow(0); int rowNum; if(rowWidth != null) { sb.append(""); for(rowNum = 0; rowNum < rowWidth.getLastCellNum(); ++rowNum) { float lastColNum = var22.getColumnWidthInPixels(rowNum); if((double)lastColNum != 0.0D) { sb.append(""); } } sb.append(""); } for(rowNum = var22.getFirstRowNum(); rowNum <= lastRowNum; ++rowNum) { row = var22.getRow(rowNum); if(row == null) { sb.append(""); } else { sb.append(""); short var23 = row.getLastCellNum(); if(var23 > 10000) { ExceptionUtils.wrapBusinessException("excle 超过1w条,有溢出风险,请下载查看"); } for(int colNum = 0; colNum < var23; ++colNum) { if((double)var22.getColumnWidthInPixels(colNum) != 0.0D) { String isCoupleColum; if(sb.length() > 1000) { for(int stringValue = 0; stringValue * 1000 < sb.length(); ++stringValue) { isCoupleColum = sb.substring(stringValue * 1000, (stringValue + 1) * 1000 > sb.length()?sb.length():(stringValue + 1) * 1000); this.output.write(isCoupleColum.toString().getBytes("UTF-8")); } sb.setLength(0); } cell = row.getCell(colNum); if(cell == null) { sb.append(""); } else { String var24 = this.getCellValue(cell); if(map[0].containsKey(rowNum + "," + colNum)) { isCoupleColum = (String)map[0].get(rowNum + "," + colNum); map[0].remove(rowNum + "," + colNum); int bottomeRow = Integer.valueOf(isCoupleColum.split(",")[0]).intValue(); int bottomeCol = Integer.valueOf(isCoupleColum.split(",")[1]).intValue(); int rowSpan = bottomeRow - rowNum + 1; int colSpan = bottomeCol - colNum + 1; sb.append(""); } } } sb.append(""); } } sb.append("
"); if(var24 != null && !"".equals(var24.trim())) { sb.append(var24.replace(String.valueOf(' '), " ")); } else { sb.append(" "); } sb.append("
"); sb.append("
"); } sb.append("

"); this.output.write(sb.toString().getBytes("UTF-8")); sb.setLength(0); return sb.toString(); } public static Map getSheetPictrues(int sheetNum, Sheet sheet, Workbook workbook) { return workbook instanceof HSSFWorkbook?getSheetPictrues03(sheetNum, (HSSFSheet)sheet, (HSSFWorkbook)workbook):(workbook instanceof XSSFWorkbook?getSheetPictrues07(sheetNum, (XSSFSheet)sheet, (XSSFWorkbook)workbook):null); } private static Map getSheetPictrues03(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) { HashMap sheetIndexPicMap = new HashMap(); List pictures = workbook.getAllPictures(); if(pictures.size() != 0) { Iterator var5 = sheet.getDrawingPatriarch().getChildren().iterator(); while(var5.hasNext()) { HSSFShape shape = (HSSFShape)var5.next(); HSSFClientAnchor anchor = (HSSFClientAnchor)shape.getAnchor(); shape.getLineWidth(); if(shape instanceof HSSFPicture) { HSSFPicture pic = (HSSFPicture)shape; int pictureIndex = pic.getPictureIndex() - 1; HSSFPictureData picData = (HSSFPictureData)pictures.get(pictureIndex); String picIndex = sheetNum + "_" + anchor.getRow1() + "_" + anchor.getCol1(); sheetIndexPicMap.put(picIndex, picData); } } return sheetIndexPicMap; } else { return null; } } private static Map getSheetPictrues07(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) { HashMap sheetIndexPicMap = new HashMap(); Iterator var4 = sheet.getRelations().iterator(); while(var4.hasNext()) { POIXMLDocumentPart dr = (POIXMLDocumentPart)var4.next(); if(dr instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing)dr; List shapes = drawing.getShapes(); Iterator var8 = shapes.iterator(); while(var8.hasNext()) { XSSFShape shape = (XSSFShape)var8.next(); XSSFPicture pic = (XSSFPicture)shape; XSSFClientAnchor anchor = pic.getPreferredSize(); CTMarker ctMarker = anchor.getFrom(); String picIndex = sheetNum + "_" + ctMarker.getRow() + "_" + ctMarker.getCol(); sheetIndexPicMap.put(picIndex, pic.getPictureData()); } } } return sheetIndexPicMap; } private static void printImpToWb(Map imgMap, Workbook wb) { Sheet sheet = null; Row row = null; String[] sheetRowCol = new String[3]; Iterator var5 = imgMap.keySet().iterator(); while(var5.hasNext()) { String key = (String)var5.next(); sheetRowCol = key.split("_"); sheet = wb.getSheetAt(Integer.parseInt(sheetRowCol[0])); row = sheet.getRow(Integer.parseInt(sheetRowCol[1])) == null?sheet.createRow(Integer.parseInt(sheetRowCol[1])):sheet.getRow(Integer.parseInt(sheetRowCol[1])); if(row.getCell(Integer.parseInt(sheetRowCol[2])) == null) { row.createCell(Integer.parseInt(sheetRowCol[2])); } else { row.getCell(Integer.parseInt(sheetRowCol[2])); } } } public static Map printImg(Map map) { HashMap imgMap = new HashMap(); Object imgName = null; try { Object[] e = map.keySet().toArray(); for(int i = 0; i < map.size(); ++i) { PictureData pic = (PictureData)map.get(e[i]); String picName = e[i].toString(); String ext = pic.suggestFileExtension(); byte[] var8 = pic.getData(); } } catch (Exception var9) { Logger.error(var9); } return imgMap; } private Map[] getRowSpanColSpanMap(Sheet sheet) { HashMap map0 = new HashMap(); HashMap map1 = new HashMap(); int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress range = null; for(int map = 0; map < mergedNum; ++map) { range = sheet.getMergedRegion(map); int topRow = range.getFirstRow(); int topCol = range.getFirstColumn(); int bottomRow = range.getLastRow(); int bottomCol = range.getLastColumn(); map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol); for(int tempRow = topRow; tempRow <= bottomRow; ++tempRow) { for(int tempCol = topCol; tempCol <= bottomCol; ++tempCol) { map1.put(tempRow + "," + tempCol, ""); } } map1.remove(topRow + "," + topCol); } Map[] var13 = new Map[]{map0, map1}; return var13; } private String getCellValue(Cell cell) { new String(); String result; switch(null.$SwitchMap$org$apache$poi$ss$usermodel$CellType[cell.getCellType().ordinal()]) { case 1: SimpleDateFormat value; if(DateUtil.isCellDateFormatted(cell)) { value = null; if(cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { value = new SimpleDateFormat("HH:mm"); } else { value = new SimpleDateFormat("yyyy-MM-dd"); } Date value1 = cell.getDateCellValue(); result = value.format(value1); } else if(cell.getCellStyle().getDataFormat() == 58) { value = new SimpleDateFormat("yyyy-MM-dd"); double value2 = cell.getNumericCellValue(); Date format = DateUtil.getJavaDate(value2); result = value.format(format); } else { double value3 = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format1 = new DecimalFormat(); String temp = style.getDataFormatString(); if("General".equals(temp)) { format1.applyPattern("#"); } else { format1.applyPattern(temp); } result = format1.format(value3); } break; case 2: result = cell.getRichStringCellValue().toString(); break; case 3: result = ""; break; default: result = ""; } return result; } private void dealExcelStyle(Workbook wb, Sheet sheet, Cell cell, StringBuffer sb, boolean isCoupleColum) { CellStyle cellStyle = cell.getCellStyle(); if(cellStyle != null) { HorizontalAlignment alignment = cellStyle.getAlignment(); sb.append("align=\'" + this.convertAlignToHtml(alignment) + "\' "); VerticalAlignment verticalAlignment = cellStyle.getVerticalAlignment(); sb.append("valign=\'" + this.convertVerticalAlignToHtml(verticalAlignment) + "\' "); String fontColorStr; if(wb instanceof XSSFWorkbook) { XSSFFont hf = ((XSSFCellStyle)cellStyle).getFont(); sb.append("style=\'"); boolean fontColor = hf.getBold(); sb.append(fontColor?"font-weight:bold;":""); sb.append("font-size: " + hf.getFontHeight() / 2 + "%;"); sheet.getColumnWidthInPixels(cell.getColumnIndex()); XSSFColor hc = hf.getXSSFColor(); if(hc != null && !"".equals(hc.getARGBHex())) { sb.append("color:#" + hc.getARGBHex().substring(2) + ";"); } XSSFColor boldWeight = (XSSFColor)cellStyle.getFillForegroundColorColor(); if(boldWeight != null && !"".equals(boldWeight.getARGBHex()) && boldWeight.getARGBHex() != null) { fontColorStr = boldWeight.getARGBHex().substring(2); if("FF000000".equals(boldWeight.getARGBHex())) { fontColorStr = "FFFFFF"; } sb.append("background-color:#" + fontColorStr + ";"); } sb.append(this.getBorderStyle(0, cellStyle.getBorderTop().getCode(), ((XSSFCellStyle)cellStyle).getTopBorderXSSFColor())); sb.append(this.getBorderStyle(1, cellStyle.getBorderRight().getCode(), ((XSSFCellStyle)cellStyle).getRightBorderXSSFColor())); sb.append(this.getBorderStyle(2, cellStyle.getBorderBottom().getCode(), ((XSSFCellStyle)cellStyle).getBottomBorderXSSFColor())); sb.append(this.getBorderStyle(3, cellStyle.getBorderLeft().getCode(), ((XSSFCellStyle)cellStyle).getLeftBorderXSSFColor())); } else if(wb instanceof HSSFWorkbook) { HSSFFont hf1 = ((HSSFCellStyle)cellStyle).getFont(wb); short fontColor1 = hf1.getColor(); sb.append("style=\'"); HSSFPalette palette = ((HSSFWorkbook)wb).getCustomPalette(); HSSFColor hc1 = palette.getColor(fontColor1); boolean boldWeight1 = hf1.getBold(); sb.append(boldWeight1?"font-weight:bold;":""); sb.append("font-size: " + hf1.getFontHeight() / 2 + "%;"); fontColorStr = this.convertToStardColor(hc1); if(fontColorStr != null && !"".equals(fontColorStr.trim())) { sb.append("color:" + fontColorStr + ";"); } int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()); sb.append("width:" + columnWidth + "px;"); short bgColor = cellStyle.getFillForegroundColor(); hc1 = palette.getColor(bgColor); String bgColorStr = this.convertToStardColor(hc1); if(bgColorStr != null && !"".equals(bgColorStr.trim())) { sb.append("background-color:" + bgColorStr + ";"); } sb.append(this.getBorderStyle(palette, 0, cellStyle.getBorderTop().getCode(), cellStyle.getTopBorderColor())); sb.append(this.getBorderStyle(palette, 1, cellStyle.getBorderRight().getCode(), cellStyle.getRightBorderColor())); sb.append(this.getBorderStyle(palette, 3, cellStyle.getBorderLeft().getCode(), cellStyle.getLeftBorderColor())); sb.append(this.getBorderStyle(palette, 2, cellStyle.getBorderBottom().getCode(), cellStyle.getBottomBorderColor())); } sb.append("\' "); } } private String convertAlignToHtml(HorizontalAlignment alignment) { String align = "left"; switch(null.$SwitchMap$org$apache$poi$ss$usermodel$HorizontalAlignment[alignment.ordinal()]) { case 1: align = "left"; break; case 2: align = "center"; break; case 3: align = "right"; } return align; } private String convertVerticalAlignToHtml(VerticalAlignment verticalAlignment) { String valign = "middle"; switch(null.$SwitchMap$org$apache$poi$ss$usermodel$VerticalAlignment[verticalAlignment.ordinal()]) { case 1: valign = "bottom"; break; case 2: valign = "center"; break; case 3: valign = "top"; } return valign; } private String convertToStardColor(HSSFColor hc) { StringBuffer sb = new StringBuffer(""); if(hc != null) { if(64 == hc.getIndex()) { return null; } sb.append("#"); for(int i = 0; i < hc.getTriplet().length; ++i) { sb.append(this.fillWithZero(Integer.toHexString(hc.getTriplet()[i]))); } } return sb.toString(); } private String fillWithZero(String str) { return str != null && str.length() < 2?"0" + str:str; } private String getBorderStyle(HSSFPalette palette, int b, short s, short t) { if(s == 0) { return this.bordesr[b] + this.borderStyles[s] + "#d0d7e5 1px;"; } else { String borderColorStr = this.convertToStardColor(palette.getColor(t)); borderColorStr = borderColorStr != null && borderColorStr.length() >= 1?borderColorStr:"#000000"; return this.bordesr[b] + this.borderStyles[s] + borderColorStr + " 1px;"; } } private String getBorderStyle(int b, short s, XSSFColor xc) { if(s == 0) { return this.bordesr[b] + this.borderStyles[s] + "#d0d7e5 1px;"; } else if(xc != null && !"".equals(xc.getARGBHex())) { String borderColorStr = xc.getARGBHex(); borderColorStr = borderColorStr != null && borderColorStr.length() >= 1?borderColorStr.substring(2):"#000000"; if(borderColorStr.indexOf("#") == -1) { borderColorStr = "#" + borderColorStr; } return this.bordesr[b] + this.borderStyles[s] + borderColorStr + " 1px;"; } else { return ""; } } }