Java tutorial
/************************************************************************* * * Copyright 2009 by bBreak Systems. * * ExCella Reports - Excel?? * * $Id: ReportsUtil.java 164 2010-08-05 10:33:13Z ogiharasf $ * $Revision: 164 $ * * This file is part of ExCella Reports. * * ExCella Reports is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License version 3 * only, as published by the Free Software Foundation. * * ExCella Reports is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License version 3 for more details * (a copy is included in the COPYING.LESSER file that accompanied this code). * * You should have received a copy of the GNU Lesser General Public License * version 3 along with ExCella Reports . If not, see * <http://www.gnu.org/licenses/lgpl-3.0-standalone.html> * for a copy of the LGPLv3 License. * ************************************************************************/ package org.bbreak.excella.reports.util; import java.lang.reflect.Array; import java.math.BigDecimal; import java.math.BigInteger; import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; import java.util.Date; import java.util.List; import java.util.Map; import org.apache.commons.beanutils.PropertyUtils; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.PrintSetup; 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.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.bbreak.excella.core.exception.ParseException; import org.bbreak.excella.core.util.PoiUtil; import org.bbreak.excella.reports.model.ParamInfo; import org.bbreak.excella.reports.model.ReportBook; import org.bbreak.excella.reports.model.ReportSheet; import org.bbreak.excella.reports.tag.ReportsTagParser; import org.bbreak.excella.reports.tag.SingleParamParser; /** * * * @since 1.0 */ public final class ReportsUtil { /** * */ private ReportsUtil() { } /** * ?? */ public static final String VALUE_SHEET_NAMES = "#SHEET_NAME[]"; /** * */ public static final String VALUE_SHEET_VALUES = "#SHEET_VALUE[]"; /** * ?????? * * @param sheetName ?? * @param reportBook * @return ???? */ public static ReportSheet getReportSheet(String sheetName, ReportBook reportBook) { if (reportBook != null) { for (ReportSheet reportSheet : reportBook.getReportSheets()) { if (reportSheet != null && reportSheet.getSheetName().equals(sheetName)) { return reportSheet; } } } return null; } /** * ???? * * @param reportBook * @return ?? */ public static List<String> getSheetNames(ReportBook reportBook) { List<String> names = new ArrayList<String>(); for (ReportSheet reportSheet : reportBook.getReportSheets()) { names.add(reportSheet.getSheetName()); } return names; } /** * ?????????????<BR> * * <pre> * ? * ??:??? * * $R[]:? * $C: * $:??? * ??????????:????$:??? * * ($BR[]?$BC[])?.???? * ????? * ??:???.??:???.??:??? * * $BR[]:.$R[]: * $BR[]:.?? * </pre> * * @param info * @param propertyNameString ? * @param parsers ? * @return ???? */ public static List<Object> getParamValues(ParamInfo info, String propertyNameString, List<ReportsTagParser<?>> parsers) { String[] levels = propertyNameString.split("\\."); List<Object> paramValues = new ArrayList<Object>(); ParamInfo[] paramInfos = new ParamInfo[] { info }; for (String level : levels) { String tagName = null; String propertyName = null; if (level.indexOf(":") != -1) { // ? String[] values = level.split(":"); tagName = values[0]; propertyName = values[1]; } else { // ?????? tagName = SingleParamParser.DEFAULT_TAG; propertyName = level; } List<SingleParamParser> singleParsers = new ArrayList<SingleParamParser>(); for (ReportsTagParser<?> reportsParser : parsers) { if (reportsParser instanceof SingleParamParser) { singleParsers.add((SingleParamParser) reportsParser); } } // ???? ReportsTagParser<?> targetParser = null; for (ReportsTagParser<?> tagParser : parsers) { if (tagParser.getTag().equals(tagName)) { targetParser = tagParser; } } if (targetParser == null) { // ?? break; } // ?? Object object = null; for (ParamInfo paramInfo : paramInfos) { object = targetParser.getParamData(paramInfo, propertyName); if (object != null) { break; } } if (object != null) { if (object instanceof ParamInfo[]) { // $BR[],$BC[] List<ParamInfo> newParamInfos = new ArrayList<ParamInfo>(); for (ParamInfo paramInfo : paramInfos) { ParamInfo[] params = (ParamInfo[]) targetParser.getParamData(paramInfo, propertyName); if (params != null) { newParamInfos.addAll(Arrays.asList(params)); } } paramInfos = newParamInfos.toArray(new ParamInfo[newParamInfos.size()]); } else if (object.getClass().isArray()) { if (Array.getLength(object) == 0) { continue; } if (Array.get(object, 0) instanceof String || Array.get(object, 0) instanceof Number || Array.get(object, 0) instanceof Date || Array.get(object, 0) instanceof Boolean) { // $R[],$C[] for (ParamInfo paramInfo : paramInfos) { Object arrayObj = targetParser.getParamData(paramInfo, propertyName); if (arrayObj != null) { for (int i = 0; i < Array.getLength(arrayObj); i++) { paramValues.add(Array.get(arrayObj, i)); } } } } else { // $BR[],$BC[] List<ParamInfo> newParamInfos = new ArrayList<ParamInfo>(); for (ParamInfo paramInfo : paramInfos) { Object[] params = (Object[]) targetParser.getParamData(paramInfo, propertyName); // POJOParamInfo??? for (Object obj : params) { if (obj instanceof ParamInfo) { newParamInfos.add((ParamInfo) obj); continue; } ParamInfo childParamInfo = new ParamInfo(); Map<String, Object> map = null; try { map = PropertyUtils.describe(obj); } catch (Exception e) { throw new RuntimeException( "????????", e); } for (Map.Entry<String, Object> entry : map.entrySet()) { for (ReportsTagParser<?> parser : singleParsers) { childParamInfo.addParam(parser.getTag(), entry.getKey(), entry.getValue()); } } newParamInfos.add(childParamInfo); } } paramInfos = newParamInfos.toArray(new ParamInfo[newParamInfos.size()]); } } else if (object instanceof Collection<?>) { for (ParamInfo paramInfo : paramInfos) { Collection<?> collection = (Collection<?>) targetParser.getParamData(paramInfo, propertyName); if (collection != null) { paramValues.addAll(collection); } } } else { // $,$I for (ParamInfo paramInfo : paramInfos) { Object value = targetParser.getParamData(paramInfo, propertyName); if (value != null) { paramValues.add(value); } } } } } return paramValues; } /** * ??? * * @param reportBook * @param propertyName ?? * @param parsers ? * @return ?? */ public static List<Object> getSheetValues(ReportBook reportBook, String propertyName, List<ReportsTagParser<?>> parsers) { List<Object> paramValues = new ArrayList<Object>(); // ?????? if (propertyName.indexOf(".") != -1) { return paramValues; } // ???? if (propertyName.indexOf(":") != -1) { return paramValues; } for (ReportSheet reportSheet : reportBook.getReportSheets()) { if (parsers != null) { for (ReportsTagParser<?> tagParser : parsers) { if (tagParser instanceof SingleParamParser) { Object object = reportSheet.getParam(tagParser.getTag(), propertyName); if (object != null) { paramValues.add(object); } } } } } return paramValues; } /** * ??? * * @param info * @param propertyName ? * @param parsers ? * @return ? */ public static BigDecimal getSumValue(ParamInfo info, String propertyName, List<ReportsTagParser<?>> parsers) { List<?> values = ReportsUtil.getParamValues(info, propertyName, parsers); BigDecimal sumValue = BigDecimal.ZERO; for (Object value : values) { if (value instanceof Number) { Number num = (Number) value; BigDecimal decimal = null; if (value instanceof Byte) { decimal = new BigDecimal(num.intValue()); } else if (value instanceof Short) { decimal = new BigDecimal(num.intValue()); } else if (value instanceof Integer) { decimal = new BigDecimal(num.intValue()); } else if (value instanceof Long) { decimal = new BigDecimal(num.longValue()); } else if (value instanceof Float) { Float floatValue = (Float) value; decimal = new BigDecimal(String.valueOf(floatValue)); } else if (value instanceof Double) { decimal = BigDecimal.valueOf(num.doubleValue()); } else if (value instanceof BigInteger) { decimal = new BigDecimal((BigInteger) value); } else if (value instanceof BigDecimal) { decimal = (BigDecimal) value; } if (decimal != null) { sumValue = sumValue.add(decimal); } } } return sumValue; } /** * ????????? * * @param sheet * @param rowIndex * @param columnIndex * @return ?? */ public static CellRangeAddress getMergedAddress(Sheet sheet, int rowIndex, int columnIndex) { CellRangeAddress rangeAddress = new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex); int fromSheetMargNums = sheet.getNumMergedRegions(); for (int i = 0; i < fromSheetMargNums; i++) { CellRangeAddress mergedAddress = null; if (sheet instanceof XSSFSheet) { mergedAddress = ((XSSFSheet) sheet).getMergedRegion(i); } else if (sheet instanceof HSSFSheet) { mergedAddress = ((HSSFSheet) sheet).getMergedRegion(i); } // fromAddress???? if (PoiUtil.containCellRangeAddress(mergedAddress, rangeAddress)) { return mergedAddress; } } return null; } /** * ?XXXCell=n:n??n??? * * @param cellParam n:n * @param tagName ?? * @return int??row?col * @throws ParseException */ public static int[] getCellIndex(String cellParam, String tagName) throws ParseException { String[] cellIndex = cellParam.split(":"); if (cellIndex.length != 2) { throw new ParseException("cellParam:" + cellParam); } // int?? int rowIndex = getIndex(cellIndex[0], tagName); int colIndex = getIndex(cellIndex[1], tagName); int[] index = { rowIndex, colIndex }; return index; } /** * ???? * * @param index ?? * @param tagName ?? * @return ??int?? * @throws ParseException * @exception ???????? */ private static int getIndex(String index, String tagName) throws ParseException { int intIndex = 0; try { intIndex = Integer.parseInt(index); } catch (Exception e) { throw new ParseException("index:" + index); } return intIndex; } /** * ????????String[?][?]????? ???????????null?? * * @param sheet ?? * @param bStartRowIndex ? * @param bEndRowIndex ? * @param bStartColIndex ? * @param bEndColIndex ? * @return */ public static Object[][] getBlockCellValue(Sheet sheet, int bStartRowIndex, int bEndRowIndex, int bStartColIndex, int bEndColIndex) { Object[][] blockCellValue = new Object[bEndRowIndex - bStartRowIndex + 1][bEndColIndex - bStartColIndex + 1]; int row = 0; for (int bRowIndex = bStartRowIndex; bRowIndex <= bEndRowIndex; bRowIndex++) { int col = 0; for (int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) { if (sheet.getRow(bRowIndex) != null && sheet.getRow(bRowIndex).getCell(bColIndex) != null) { blockCellValue[row][col] = PoiUtil.getCellValue(sheet.getRow(bRowIndex).getCell(bColIndex)); } else { blockCellValue[row][col] = null; } col++; } row++; } return blockCellValue; } /** * ????????CellStyle[?][?]????? ??null???null?? * * @param sheet ?? * @param bStartRowIndex ? * @param bEndRowIndex ? * @param bStartColIndex ? * @param bEndColIndex ? * @return */ public static CellStyle[][] getBlockCellStyle(Sheet sheet, int bStartRowIndex, int bEndRowIndex, int bStartColIndex, int bEndColIndex) { CellStyle[][] blockCellStyle = new CellStyle[bEndRowIndex - bStartRowIndex + 1][bEndColIndex - bStartColIndex + 1]; int row = 0; for (int bRowIndex = bStartRowIndex; bRowIndex <= bEndRowIndex; bRowIndex++) { int col = 0; for (int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) { if (sheet.getRow(bRowIndex) != null && sheet.getRow(bRowIndex).getCell(bColIndex) != null) { blockCellStyle[row][col] = sheet.getRow(bRowIndex).getCell(bColIndex).getCellStyle(); } else { blockCellStyle[row][col] = null; } col++; } row++; } return blockCellStyle; } /** * ?????int[?][?]????? ??null???Cell.CELL_TYPE_BLANK?? * * @param sheet ?? * @param bStartRowIndex ? * @param bEndRowIndex ? * @param bStartColIndex ? * @param bEndColIndex ? * @return ? */ public static CellType[][] getBlockCellType(Sheet sheet, int bStartRowIndex, int bEndRowIndex, int bStartColIndex, int bEndColIndex) { CellType[][] blockCellType = new CellType[bEndRowIndex - bStartRowIndex + 1][bEndColIndex - bStartColIndex + 1]; int rowIdx = 0; for (int bRowIndex = bStartRowIndex; bRowIndex <= bEndRowIndex; bRowIndex++) { int colIdx = 0; for (int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) { Row row = sheet.getRow(bRowIndex); if (row != null && row.getCell(bColIndex) != null) { blockCellType[rowIdx][colIdx] = row.getCell(bColIndex).getCellTypeEnum(); } else { blockCellType[rowIdx][colIdx] = CellType.BLANK; } colIdx++; } rowIdx++; } return blockCellType; } /** * ??????float[?]????? ???????-1?? * * @param sheet ?? * @param bStartRowIndex ? * @param bEndRowIndex ? * @return ?? */ public static float[] getRowHeight(Sheet sheet, int bStartRowIndex, int bEndRowIndex) { float[] height = new float[bEndRowIndex - bStartRowIndex + 1]; int rowIdx = 0; for (int bRowIndex = bStartRowIndex; bRowIndex <= bEndRowIndex; bRowIndex++) { Row row = sheet.getRow(bRowIndex); if (row == null) { // ?????????? // ???-1? height[rowIdx] = -1; } else { height[rowIdx] = row.getHeightInPoints(); } rowIdx++; } return height; } /** * ??????int[?]????? * * @param sheet ?? * @param bStartColIndex ? * @param bEndColIndex ? * @return ?? */ public static int[] getColumnWidth(Sheet sheet, int bStartColIndex, int bEndColIndex) { int[] columnWidth = new int[bEndColIndex - bStartColIndex + 1]; int colIdx = 0; for (int bColIndex = bStartColIndex; bColIndex <= bEndColIndex; bColIndex++) { columnWidth[colIdx] = sheet.getColumnWidth(bColIndex); colIdx++; } return columnWidth; } /** * ??????????? * * @param sheet ?? * @param baseFromCellRowIndex ? * @param baseToCellRowIndex ? * @param baseFromCellColIndex ? * @param baseToCellColIndex ? * @return ?? */ public static CellRangeAddress[] getMargedCells(Sheet sheet, int baseFromCellRowIndex, int baseToCellRowIndex, int baseFromCellColIndex, int baseToCellColIndex) { CellRangeAddress baseAddress = new CellRangeAddress(baseFromCellRowIndex, baseToCellRowIndex, baseFromCellColIndex, baseToCellColIndex); int num = sheet.getNumMergedRegions(); List<CellRangeAddress> maegedAddresses = new ArrayList<CellRangeAddress>(); for (int i = 0; i < num; i++) { CellRangeAddress targetAddress = sheet.getMergedRegion(i); if (PoiUtil.containCellRangeAddress(baseAddress, targetAddress)) { maegedAddresses.add(targetAddress); } } return maegedAddresses.toArray(new CellRangeAddress[0]); } /** * ?(??)?????????? * * @param rowCellTypes ? * @param rowCellValues ?? * @param rowCellStyles ? * @return ????true??????????false */ public static boolean isEmptyRow(CellType[] rowCellTypes, Object[] rowCellValues, CellStyle[] rowCellStyles) { // CellType? BLANK????? for (CellType cellType : rowCellTypes) { if (cellType != CellType.BLANK) { return false; } } // ? null????? for (Object cellValue : rowCellValues) { if (cellValue != null) { return false; } } // CellStyle? null????? for (CellStyle cellStyle : rowCellStyles) { if (cellStyle != null) { return false; } } return true; } /** * ?(??)?????????? * * @param cellType ? * @param cellValue ? * @param cellStyle ? * @return ????true??????????false */ public static boolean isEmptyCell(CellType cellType, Object cellValue, CellStyle cellStyle) { // CellType? BLANK????? if (cellType != CellType.BLANK) { return false; } // ? null????? if (cellValue != null) { return false; } // CellStyle? null????? if (cellStyle != null) { return false; } return true; } /** * fromIdx??toIdx????? * @param workbook fromIdx?toIdx??workbook * @param fromIdx ? * @param sheet */ public static void copyPrintSetup(Workbook workbook, int fromIdx, Sheet toSheet) { Sheet fromSheet = workbook.getSheetAt(fromIdx); // ? PrintSetup fromPrintSetup = fromSheet.getPrintSetup(); PrintSetup printSetup = toSheet.getPrintSetup(); printSetup.setCopies(fromPrintSetup.getCopies()); printSetup.setDraft(fromPrintSetup.getDraft()); printSetup.setFitHeight(fromPrintSetup.getFitHeight()); printSetup.setFitWidth(fromPrintSetup.getFitWidth()); printSetup.setFooterMargin(fromPrintSetup.getFooterMargin()); printSetup.setHeaderMargin(fromPrintSetup.getHeaderMargin()); printSetup.setHResolution(fromPrintSetup.getHResolution()); printSetup.setLandscape(fromPrintSetup.getLandscape()); printSetup.setLeftToRight(fromPrintSetup.getLeftToRight()); printSetup.setNoColor(fromPrintSetup.getNoColor()); printSetup.setNoOrientation(fromPrintSetup.getNoOrientation()); printSetup.setPageStart(fromPrintSetup.getPageStart()); printSetup.setPaperSize(fromPrintSetup.getPaperSize()); printSetup.setScale(fromPrintSetup.getScale()); printSetup.setUsePage(fromPrintSetup.getUsePage()); printSetup.setValidSettings(fromPrintSetup.getValidSettings()); printSetup.setVResolution(fromPrintSetup.getVResolution()); // ? String printArea = workbook.getPrintArea(fromIdx); if (printArea != null) { if (printArea.contains("!")) { printArea = printArea.substring(printArea.indexOf("!") + 1); } int toIdx = workbook.getSheetIndex(toSheet); workbook.setPrintArea(toIdx, printArea); } // ? toSheet.setRepeatingColumns(fromSheet.getRepeatingColumns()); toSheet.setRepeatingRows(fromSheet.getRepeatingRows()); } }