org.bbreak.excella.reports.util.ReportsUtil.java Source code

Java tutorial

Introduction

Here is the source code for org.bbreak.excella.reports.util.ReportsUtil.java

Source

/*************************************************************************
 *
 * 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());
    }
}