Java tutorial
/* * ReportXlsUtil.java 2010-11-11 * * Copyright 2010 Guangzhou Donghong Software Technology Inc. * Licensed under the www.jxstar.org */ package org.jxstar.report.util; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.math.BigDecimal; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFPicture; import org.apache.poi.hssf.usermodel.HSSFPictureData; import org.apache.poi.hssf.usermodel.HSSFRow; 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.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.jxstar.service.BoException; import org.jxstar.service.define.FunDefineDao; import org.jxstar.service.studio.AttachBO; import org.jxstar.util.DateUtil; import org.jxstar.util.MapUtil; import org.jxstar.util.StringUtil; import org.jxstar.util.config.SystemVar; import org.jxstar.util.factory.FactoryUtil; /** * excel * * @author TonyTan * @version 1.0, 2010-11-11 */ public class ReportXlsUtil extends ReportUtil { /** * ? * @param sheet -- * @param lsHeadInfo -- ? * @param mpUser -- ? * @return */ public static HSSFSheet fillHead(HSSFSheet sheet, List<Map<String, String>> lsHeadInfo, Map<String, String> mpUser) { if (lsHeadInfo == null || lsHeadInfo.isEmpty()) return sheet; Map<String, String> mpHeadInfo = null; String strColName = null, strColValue = null, strColPostion = null, strStyle = null; HSSFRow row = null; HSSFCell cell = null; String strValue = ""; int posi[] = null; for (int i = 0, n = lsHeadInfo.size(); i < n; i++) { mpHeadInfo = lsHeadInfo.get(i); if (mpHeadInfo.isEmpty()) continue; strStyle = mpHeadInfo.get("format"); strColName = mpHeadInfo.get("display"); strColValue = mpHeadInfo.get("col_code"); strColPostion = mpHeadInfo.get("col_pos"); posi = getPosition(strColPostion); if (posi.length != 2) { _log.showWarn(strColName + " [" + strColPostion + "] position is error!"); continue; } //?? row = sheet.getRow(posi[0]); if (row == null) row = sheet.createRow(posi[0]); cell = row.getCell(posi[1]); if (cell == null) cell = row.createCell(posi[1]); if (strColValue.equalsIgnoreCase("{CURUSERNAME}")) { //? strValue = MapUtil.getValue(mpUser, "user_name"); } else if (strColValue.equalsIgnoreCase("{CURDATE}")) { //? strValue = convertValue(DateUtil.getTodaySec(), strStyle); } else if (strColValue.equalsIgnoreCase("{CURDEPTNAME}")) { //? strValue = MapUtil.getValue(mpUser, "dept_name"); } else { //cell strValue = strColValue; strValue = (strValue.equalsIgnoreCase("null")) ? "" : strValue; } cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(strValue.trim()); } return sheet; } /** * ? * @param sheet -- * @param lsData -- ? * @param lsField -- * @param mpUser -- ? * @param pageSize -- ? * @param pos -- * @param curPage -- ? * @param sumPage -- * @return */ public static HSSFSheet fillGrid(HSSFSheet sheet, List<Map<String, String>> lsData, List<Map<String, String>> lsField, Map<String, String> mpUser, int pageSize, int pos, int curPage, int sumPage) { if (lsField == null || lsData == null) { _log.showWarn("data is null or field is null!"); return sheet; } if (lsField.isEmpty() || lsData.isEmpty()) { _log.showDebug("data is empty or field is null!"); return sheet; } HSSFRow row = null; //excel HSSFCell cell = null; //excel String strValue = null; //?? Map<String, String> mpData = null; //??? Map<String, String> mpField = null; //??? //HSSFCellStyle style = null; //excel? int posi = (pageSize > 0 && pos >= 0) ? pos : 0; int cnt = (pageSize <= 0) ? lsData.size() : pageSize + posi; int[] posis = new int[2]; int index = 0, rowIndex = 0; int currRow = 0; int cntCol = 1; //?? String strStyle = null, strColName = null, strColCode = null, strColTag = null; //?? List<Map<String, String>> lsStatCol = getStatField(lsField); String isOutZero = "0"; Map<String, String> mpStat = null, mpStatValue = FactoryUtil.newMap(); String strCol = null; BigDecimal bdStat = null; boolean isStatCol = false; //?? for (rowIndex = posi, index = 0; rowIndex < cnt; rowIndex++, index++) { if (lsData.size() <= rowIndex) break; //rowIndex mpData = lsData.get(rowIndex); for (int i = 0, n = lsField.size(); i < n; i++) { mpField = lsField.get(i); isOutZero = mpField.get("is_outzero"); if (isOutZero == null) isOutZero = "1"; strStyle = mpField.get("format"); //? strColName = mpField.get("display"); //?? strColCode = mpField.get("col_code").toLowerCase(); //? strColTag = mpField.get("combo_code"); // posis = getPosition(mpField.get("col_pos")); if (posis.length != 2) { _log.showWarn(strColName + " [" + mpField.get("col_pos") + "] position is error!"); continue; } //_log.showDebug("col_code=" + strColCode + " col_pos=" + posis); row = sheet.getRow(posis[0] + index); currRow = posis[0] + index; if (row == null) row = sheet.createRow(posis[0] + index); cell = row.getCell(posis[1]); if (cell == null) cell = row.createCell(posis[1]); if (strColCode.equalsIgnoreCase("{CURUSERNAME}")) { //? strValue = MapUtil.getValue(mpUser, "user_name"); } else if (strColCode.equalsIgnoreCase("{CURDATE}")) { //? strValue = convertValue(DateUtil.getTodaySec(), strStyle); } else if (strColCode.equalsIgnoreCase("{CURDEPTNAME}")) { //? strValue = MapUtil.getValue(mpUser, "dept_name"); } else if (strColCode.equalsIgnoreCase("{NUMBER}")) { //?? strValue = Integer.toString(rowIndex + 1); cntCol = (short) posis[1]; } else if (strColCode.equalsIgnoreCase("{CURPAGENUM}")) { //? strValue = Integer.toString(curPage); } else if (strColCode.equalsIgnoreCase("{CURSUMPAGE}")) { //? strValue = Integer.toString(sumPage); } else { //cell strValue = mpData.get(strColCode); //?0??0 strValue = (strValue != null) ? strValue : ""; strValue = (strValue.equalsIgnoreCase("null")) ? "" : strValue; // strValue = getComboTitle(strValue, strColTag); //??? strValue = convertValue(strValue, strStyle); if (isOutZero.equals("0")) strValue = getZeroOut(strValue, strStyle); } //??? if (strStyle.equals("int") || strStyle.indexOf("num") == 0) { if (strValue.length() == 0) strValue = "0"; cell.setCellValue(Double.parseDouble(strValue)); } else { cell.setCellValue(strValue.trim()); } if (!lsStatCol.isEmpty()) { for (int iStat = 0, statNum = lsStatCol.size(); iStat < statNum; iStat++) { mpStat = lsStatCol.get(iStat); if (mpStat.isEmpty()) continue; strCol = (mpStat.get("col_code")).toLowerCase(); if (strColCode.equalsIgnoreCase(strCol)) { isStatCol = true; if (mpStatValue.get(strCol) == null) bdStat = new BigDecimal("0"); else bdStat = new BigDecimal(mpStatValue.get(strCol)); if (strValue.length() == 0) strValue = "0"; mpStatValue.put(strCol, bdStat.add(new BigDecimal(strValue)).toString()); } } } } } //?? if (isStatCol == true) { currRow++; //??0 ??1 ?? String sumstr = "?"; String sumpos = SystemVar.getValue("sys.report.sumpos", "0"); if (sumpos.equals("1")) { sumstr = "?"; int row_mod = 0; if (pageSize > 0) row_mod = pageSize - (rowIndex % pageSize); if (row_mod == pageSize) row_mod = 0; currRow = currRow + row_mod; } row = sheet.getRow(currRow); if (row == null) row = sheet.createRow(currRow); cell = row.getCell(cntCol); if (cell == null) cell = row.createCell(cntCol); cell.setCellValue(sumstr); for (int i = 0, colNum = lsStatCol.size(); i < colNum; i++) { mpField = lsStatCol.get(i); posis = getPosition(mpField.get("col_pos")); //if (row == null) row = sheet.createRow(posi + cnt); cell = row.getCell(posis[1]); if (cell == null) cell = row.createCell(posis[1]); strColCode = (mpField.get("col_code")).toLowerCase(); strValue = mpStatValue.get(strColCode); strStyle = mpField.get("format"); strValue = convertValue(strValue, strStyle); cell.setCellValue(Double.parseDouble(strValue)); } } return sheet; } /** * ?? * @param funId -- ID * @param sheet -- * @param mpData -- ?? * @param mpUser -- ? * @param lsField -- ? * @param curPage -- ? * @param sumPage -- * @return */ public static HSSFSheet fillForm(String funId, HSSFSheet sheet, Map<String, String> mpData, List<Map<String, String>> lsField, Map<String, String> mpUser, int curPage, int sumPage) { if (lsField == null || mpData == null) { return sheet; } if (lsField.isEmpty() || mpData.isEmpty()) { _log.showDebug("data is empty or field is null!"); return sheet; } //? String strValue = ""; for (int i = 0, n = lsField.size(); i < n; i++) { Map<String, String> mpField = lsField.get(i); String strStyle = mpField.get("format"); //? String isOutZero = mpField.get("is_outzero"); //0? if (isOutZero == null) isOutZero = "1"; String strColName = mpField.get("display"); //?? String strColCode = (mpField.get("col_code")).toLowerCase(); //? String strColTag = mpField.get("combo_code"); // int[] posi = getPosition(mpField.get("col_pos")); if (posi.length != 2) { _log.showWarn(strColName + " [" + mpField.get("col_pos") + "] position is error!"); continue; } if (strColCode.equalsIgnoreCase("{CURUSERNAME}")) { //? strValue = MapUtil.getValue(mpUser, "user_name"); } else if (strColCode.equalsIgnoreCase("{CURDATE}")) { //? strValue = convertValue(DateUtil.getTodaySec(), strStyle); } else if (strColCode.equalsIgnoreCase("{CURDEPTNAME}")) { //? strValue = MapUtil.getValue(mpUser, "dept_name"); } else if (strColCode.equalsIgnoreCase("{CURPAGENUM}")) { //? strValue = Integer.toString(curPage); } else if (strColCode.equalsIgnoreCase("{CURSUMPAGE}")) { //? strValue = Integer.toString(sumPage); } else { //cell?? if (!strStyle.equals("image")) { strValue = mpData.get(strColCode); strValue = (strValue != null) ? strValue : ""; strValue = (strValue.equalsIgnoreCase("null")) ? "" : strValue; //? strValue = getComboTitle(strValue, strColTag); //??? strValue = convertValue(strValue, strStyle); if (isOutZero.equals("0")) strValue = getZeroOut(strValue, strStyle); } } //?? HSSFRow row = sheet.getRow(posi[0]); if (row == null) row = sheet.createRow(posi[0]); HSSFCell cell = row.getCell(posi[1]); if (cell == null) cell = row.createCell(posi[1]); //? if (!strStyle.equals("image")) { if (strStyle.equals("barcode") && strValue.length() > 0) { if (!printBarcode(strValue, cell)) { //cell.setCellValue(JsMessage.getValue("report.xlsutil.noimage")); _log.showDebug("..........not barcode value!"); } } else { cell.setCellValue(strValue.trim()); //?? ? if (strStyle.equals("int") || strStyle.indexOf("num") == 0) { if (strValue.length() == 0) strValue = "0"; cell.setCellValue(Double.parseDouble(strValue)); } else { cell.setCellValue(strValue.trim()); } } } else { //?custPrintImage String donot_image = MapUtil.getValue(mpData, "donot_image"); //?? if (!donot_image.equals("1") && !printCellImage(funId, strColCode, mpData, cell)) { //cell.setCellValue(JsMessage.getValue("report.xlsutil.noimage")); _log.showDebug("..........not image file!"); } } } return sheet; } /** * * @param areaId -- ID * @param sheet -- ? * @param mpData -- ??field -- bytes * @return */ public static HSSFSheet custPrintImage(String areaId, HSSFSheet sheet, Map<String, Object> mpData) { //?? List<Map<String, String>> lsField = ReportDao.getImageCol(areaId); if (lsField == null || lsField.isEmpty()) { _log.showDebug("--------custPrintImage(): not find report detail image col."); return sheet; } for (int i = 0, n = lsField.size(); i < n; i++) { Map<String, String> mpField = lsField.get(i); String colcode = mpField.get("col_code"); String colpos = mpField.get("col_pos"); int[] posi = getPosition(colpos); if (posi.length != 2) { _log.showWarn(colcode + " [" + posi + "] position is error!"); continue; } //?? HSSFRow row = sheet.getRow(posi[0]); if (row == null) row = sheet.createRow(posi[0]); HSSFCell cell = row.getCell(posi[1]); if (cell == null) cell = row.createCell(posi[1]); //? byte[] bytes = (byte[]) mpData.get(colcode); if (bytes != null && bytes.length > 0) { addImageToSheet(cell, bytes); } } return sheet; } /** * ???? * ???ID?ID?ID? * @param funId -- ID * @param areaId -- ID * @param sheet -- ? * @param mpData -- * @return */ public static HSSFSheet fillCheckInfo(String funId, String areaId, HSSFSheet sheet, Map<String, String> mpData) { //?? List<Map<String, String>> lsField = ReportDao.getAreaWfCol(areaId); if (lsField == null || lsField.isEmpty()) { _log.showDebug("--------outCheckInfo(): not find report detail wfcol."); return sheet; } //? String dataId = getKeyValue(funId, mpData); //???ID String preNodeId = ""; //??? Map<String, String> mpCheck = null; //??? String allCheckDesc = ""; for (int i = 0, n = lsField.size(); i < n; i++) { Map<String, String> mpField = lsField.get(i); String format = mpField.get("format"); String nodeId = mpField.get("node_id"); String colCode = mpField.get("col_code"); //check_sign String colPos = mpField.get("col_pos"); String processId = mpField.get("process_id"); //???????? //??check_user, check_date, check_desc if (!preNodeId.equals(nodeId)) { List<Map<String, String>> lsCheck = ReportDao.getCheckInfo(funId, dataId, processId, nodeId); mpCheck = getCheckUser(lsCheck); allCheckDesc = getCheckDesc(lsCheck, "\r\n"); } if (mpCheck == null || mpCheck.isEmpty()) continue; String strValue = mpCheck.get(colCode); //?? if (strValue == null || strValue.length() == 0) continue; strValue = convertValue(strValue, format); _log.showDebug("................checkfield={0}; checkvalue={1}; reportpos={2}; format={3}", colCode, strValue, colPos, format); int[] posi = getPosition(mpField.get("col_pos")); if (posi.length != 2) { _log.showWarn(colCode + " [" + posi + "] position is error!"); continue; } //?? HSSFRow row = sheet.getRow(posi[0]); if (row == null) row = sheet.createRow(posi[0]); HSSFCell cell = row.getCell(posi[1]); if (cell == null) cell = row.createCell(posi[1]); //? if (!colCode.equals("check_desc")) { cell.setCellValue(strValue.trim()); } else { cell.setCellValue(allCheckDesc); } } return sheet; } /** * * @param mainSheet -- ? * @param subSheet -- ? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) { return appendSheet(mainSheet, subSheet, -1); } /** * * @param mainSheet -- ? * @param subSheet -- ? * @param tempRow -- ????? * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet, int tempRow) { if (mainSheet == null || subSheet == null) return null; //?? if (!isAllowOut(mainSheet)) return mainSheet; //? int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; //? copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); //?? CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; //int k = 0; //? mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext();) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); /** * ??????orgcell = mainSheet.getRow(row).getCell(column); * ?? * ??orgcell.getCellStyle()????sheet?? * This Style does not belong to the supplied Workbook. * ?descell.getCellStyle().cloneStyleFrom(sourcell.getCellStyle());???excel * HSSFCellStyle cs = mainSheet.getWorkbook().createCellStyle(); * cs.cloneStyleFrom(sourcell.getCellStyle()); * descell.setCellStyle(cs);//excel? * tempRow???? */ //???????? int row = sourcell.getRowIndex(); if (tempRow > 0 && row > tempRow) { row = tempRow; } orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { //orgcell.getCellType()???0 descell.setCellType(HSSFCell.CELL_TYPE_STRING); //??? descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; } /** * ?PageSize? * @param sheet -- ? * @param startRow -- ???PageSize * @param rows -- ? * @return */ public static HSSFSheet insertSheetRow(HSSFSheet sheet, int startRow, int rows) { if (sheet == null) return null; sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false); HSSFCell sourcell = null, descell = null; HSSFRow sourow = sheet.getRow(startRow - 1); for (int i = 0; i < rows; i++) { HSSFRow descrow = sheet.createRow(startRow + i); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); descell.setCellType(sourcell.getCellType()); descell.setCellStyle(sourcell.getCellStyle()); } } //?? insertSheetRegions(sheet, startRow, rows); return sheet; } //?????? private static void insertSheetRegions(HSSFSheet sheet, int startRow, int rows) { //????? List<CellRangeAddress> lsRange = FactoryUtil.newList(); //? int copyi = startRow - 1; //???? int mergedNum = sheet.getNumMergedRegions(); CellRangeAddress region = null; for (int i = 0; i < mergedNum; i++) { region = sheet.getMergedRegion(i); if (region.getFirstRow() == copyi && region.getLastRow() == copyi) { lsRange.add(region.copy()); } } if (lsRange.isEmpty()) return; CellRangeAddress range = null; //?? for (int i = 0; i < rows; i++) { for (int j = 0; j < lsRange.size(); j++) { range = lsRange.get(j); range.setFirstRow(range.getFirstRow() + 1); range.setLastRow(range.getLastRow() + 1); sheet.addMergedRegion(range); } } } /** * ???1SHEET1SHEET * * @param destBook -- * @param srcBook -- ?? */ private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) { //??? HSSFSheet srcSheet = srcBook.getSheetAt(0); //? HSSFSheet destSheet = destBook.getSheetAt(0); //??? int endRowNum = destSheet.getPhysicalNumberOfRows(); //???? List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet); _log.showDebug("----------source picture size:" + lsSrcPicture.size()); if (lsSrcPicture.isEmpty()) return; //????? List<HSSFPictureData> lsPicData = null; try { lsPicData = srcBook.getAllPictures(); } catch (Exception e) { _log.showWarn( "book?getAllPictures?book??"); HSSFWorkbook tmpBook = copyWorkbook(srcBook); if (tmpBook != null) { lsPicData = tmpBook.getAllPictures(); tmpBook = null; } /* ???? //??? lsPicData = destBook.getAllPictures(); if (lsPicData == null || lsPicData.isEmpty()) return; //??1? List<HSSFPictureData> destData = FactoryUtil.newList(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { destData.add(lsPicData.get(0)); } lsPicData = destData;*/ } if (lsPicData == null || lsPicData.isEmpty()) return; _log.showDebug("----------source data size:" + lsPicData.size()); //???? //????sheet???book if (lsSrcPicture.size() > lsPicData.size()) { _log.showWarn("?????"); return; } //?? HSSFPatriarch destDraw = destSheet.getDrawingPatriarch(); if (destDraw == null) { destDraw = destSheet.createDrawingPatriarch(); } //?? List<HSSFPicture> lsDestPicture = getAllPicture(destSheet); int index = lsDestPicture.size(); for (int i = 0, n = lsSrcPicture.size(); i < n; i++) { //? HSSFPicture picture = lsSrcPicture.get(i); //????? HSSFPictureData picdata = lsPicData.get(i); //?? byte[] datas = picdata.getData(); //??? HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor(); //?? anchor.setRow1(anchor.getRow1() + endRowNum); anchor.setRow2(anchor.getRow2() + endRowNum); //??? destBook.addPicture(datas, picdata.getFormat()); //???????+1?? index++; _log.showDebug("---------copy new image index=" + index); destDraw.createPicture(anchor, index); } } /** * ???? * @param value * @param cell * @return */ private static boolean printBarcode(String value, HSSFCell cell) { byte[] bytes = JxBarcodeUtil.createBarcode(value); if (bytes == null || bytes.length == 0) return false; //? addImageToSheet(cell, bytes); return true; } /** * ? * @param funId -- ID * @param field -- * @param mpData -- ??? * @param cell -- ? * */ private static boolean printCellImage(String funId, String fieldName, Map<String, String> mpData, HSSFCell cell) { //?? Map<String, String> mpFun = FunDefineDao.queryFun(funId); if (mpFun == null || mpFun.isEmpty()) { _log.showDebug("--------printCellImage(): not find function is null!!"); return false; } //??? String tableName = mpFun.get("table_name"); //???? String pkcol = mpFun.get("pk_col"); pkcol = StringUtil.getNoTableCol(pkcol); //? String dataId = mpData.get(pkcol); if (dataId == null || dataId.length() == 0) { _log.showDebug("--------printCellImage(): not find data id!!"); return false; } AttachBO attachBO = new AttachBO(); //? Map<String, String> mpAttach = null; try { mpAttach = attachBO.queryAttach(dataId, tableName, fieldName); } catch (BoException e1) { e1.printStackTrace(); } if (mpAttach == null || mpAttach.isEmpty()) { _log.showDebug("--------printCellImage(): not attach dataId=" + dataId + "; tableName=" + tableName + ";fieldName=" + fieldName); return false; } //?? byte[] bytes = null; try { bytes = attachBO.queryAttachContent(mpAttach); } catch (BoException e1) { e1.printStackTrace(); } if (bytes == null || bytes.length == 0) { _log.showDebug("--------printCellImage(): not find attach image file!!"); return false; } //? addImageToSheet(cell, bytes); return true; } /** * ??POI?ReportImageUtil * @param cell -- ? * @param bytes -- */ public static void addImageToSheet(HSSFCell cell, byte[] bytes) { if (cell == null) { _log.showError("-----insertImageToSheet: cell is null!"); return; } if (bytes == null || bytes.length == 0) { _log.showError("-----insertImageToSheet: bytes is null!"); return; } //?? HSSFSheet sheet = cell.getSheet(); //? int firstRow = cell.getRowIndex(); int lastRow = cell.getRowIndex(); int firstCol = cell.getColumnIndex(); int lastCol = cell.getColumnIndex(); //?? CellRangeAddress range = getMergedRegion(cell); if (range != null) { firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); } _log.showDebug("---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]"); //????5??1023255 HSSFClientAnchor anchor = new HSSFClientAnchor(10, 5, 1013, 250, (short) firstCol, firstRow, (short) lastCol, lastRow); anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE); //?? HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) { draw = sheet.createDrawingPatriarch(); } //??? sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); //???????+1?? List<HSSFPicture> lsPicture = getAllPicture(sheet); int index = lsPicture.size() + 1; _log.showDebug("---------new image index=" + index); draw.createPicture(anchor, index); } /** * ?book?????workbookPOIBUG * tmpBook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); * lsPicData = tmpBook.getAllPictures();// * java.lang.NullPointerException org.apache.poi.hssf.record.AbstractEscherHolderRecord.decode(AbstractEscherHolderRecord.java:260) org.apache.poi.hssf.usermodel.HSSFWorkbook.getAllPictures(HSSFWorkbook.java:1571) org.jxstar.report.util.ReportXlsUtil.addImageToSheet(ReportXlsUtil.java:816) * @param datas * @return */ private static HSSFWorkbook copyWorkbook(HSSFWorkbook srcBook) { byte[] datas = new byte[0]; ByteArrayInputStream ins = null; ByteArrayOutputStream ons = new ByteArrayOutputStream(); try { try { srcBook.write(ons); ons.flush(); datas = ons.toByteArray(); if (datas != null && datas.length > 0) { ins = new ByteArrayInputStream(datas); return new HSSFWorkbook(ins); } } finally { ons.close(); if (ins != null) ins.close(); } } catch (IOException e) { e.printStackTrace(); } return null; } /** * ?? * @param sheet -- ? * @return */ private static List<HSSFPicture> getAllPicture(HSSFSheet sheet) { List<HSSFPicture> lsPicture = FactoryUtil.newList(); HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) return lsPicture; //??? List<HSSFShape> lsShape = draw.getChildren(); if (lsShape == null || lsShape.isEmpty()) return lsPicture; for (int i = 0, n = lsShape.size(); i < n; i++) { HSSFShape shape = lsShape.get(i); if (shape instanceof HSSFPicture) { lsPicture.add((HSSFPicture) shape); } } return lsPicture; } /** * ???? * @param cell -- ? * @return */ private static CellRangeAddress getMergedRegion(HSSFCell cell) { HSSFSheet sheet = cell.getSheet(); CellRangeAddress range = null; int mergedNum = sheet.getNumMergedRegions(); for (int i = 0; i < mergedNum; i++) { range = sheet.getMergedRegion(i); if (range.getFirstColumn() == cell.getColumnIndex() && range.getFirstRow() == cell.getRowIndex()) { return range; } } return null; } /** * ?xlsHSSFWorkbook * @param fileName * @return */ public static HSSFWorkbook readWorkBook(String fileName) { POIFSFileSystem fs = null; FileInputStream fis = null; HSSFWorkbook wb = null; if (fileName == null || fileName.length() == 0) return wb; try { fis = new FileInputStream(fileName); fs = new POIFSFileSystem(fis); fis.close(); wb = new HSSFWorkbook(fs); } catch (FileNotFoundException e) { _log.showWarn("not find excel file: " + fileName); fs = null; return wb; } catch (IOException e) { _log.showWarn(e.getMessage()); fs = null; return wb; } fis = null; return wb; } /** * ?? * * @param sheet -- * @return */ public static boolean isAllowOut(HSSFSheet sheet) { boolean ret = true; String maxXlsNum = SystemVar.getValue("report.xls.num", "50000"); if (sheet.getPhysicalNumberOfRows() > Integer.parseInt(maxXlsNum)) { _log.showWarn("EXCEL{0}?", maxXlsNum); ret = false; } return ret; } }