All Downloads are FREE. Search and download functionalities are using the official Maven repository.

org.jxls.util.Util Maven / Gradle / Ivy

Go to download

Small library for Excel generation based on XLS templates

The newest version!
package org.jxls.util;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.PropertyUtils;
import org.jxls.area.Area;
import org.jxls.command.Command;
import org.jxls.command.EachCommand;
import org.jxls.common.CellRef;
import org.jxls.common.CellRefColPrecedenceComparator;
import org.jxls.common.CellRefRowPrecedenceComparator;
import org.jxls.common.Context;
import org.jxls.common.GroupData;
import org.jxls.common.JxlsException;
import org.jxls.expression.EvaluationException;
import org.jxls.expression.ExpressionEvaluator;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * Utility class with various helper methods used by other classes
 *
 * @author Leonid Vysochyn
 */
public class Util {
    private static Logger logger = LogManager.getLogger(Util.class);
    public static final String regexJointedLookBehind = "(? getFormulaCellRefs(String formula) {
        return getStringPartsByPattern(formula, regexCellRefExcludingJointedPattern);
    }

    private static List getStringPartsByPattern(String str, Pattern pattern) {
        List cellRefs = new ArrayList();
        if (str != null) {
            Matcher cellRefMatcher = pattern.matcher(str);
            while (cellRefMatcher.find()) {
                cellRefs.add(cellRefMatcher.group());
            }
        }
        return cellRefs;
    }

    /**
     * Parses a formula to extract a list of so called "jointed cells"
     * The jointed cells are cells combined with a special notation "U_(cell1, cell2)" into a single cell
     * They are used in formulas like this "$[SUM(U_(F8,F13))]".
     * Here the formula will use both F8 and F13 source cells to calculate the sum
     * @param formula a formula string to parse
     * @return a list of jointed cells used in the formula
     */
    public static List getJointedCellRefs(String formula) {
        return getStringPartsByPattern(formula, regexJointedCellRefPattern);
    }

    /**
     * Parses a "jointed cell" reference and extracts individual cell references
     * @param jointedCellRef a jointed cell reference to parse
     * @return a list of cell names extracted from the jointed cell reference
     */
    public static List getCellRefsFromJointedCellRef(String jointedCellRef) {
        return getStringPartsByPattern(jointedCellRef, regexCellRefPattern);
    }

    /**
     * Checks if the formula contains jointed cell references
     * Jointed references have format U_(cell1, cell2) e.g. $[SUM(U_(F8,F13))]
     * @param formula string
     * @return true if the formula contains jointed cell references
     */
    public static boolean formulaContainsJointedCellRef(String formula) {
        return regexJointedCellRefPattern.matcher(formula).find();
    }

    /**
     * Combines a list of cell references into a range
     * E.g. for cell references A1, A2, A3, A4 it returns A1:A4
     * @param targetCellDataList -
     * @return a range containing all the cell references if such range exists or otherwise the passed cells separated by commas
     */
    public static String createTargetCellRef(List targetCellDataList) {
        // testcase: UtilCreateTargetCellRefTest. Can be optimized in Java 8.
        if (targetCellDataList == null) {
            return "";
        }
        int size = targetCellDataList.size();
        if (size == 0) {
            return "";
        } else if (size == 1) {
            return targetCellDataList.get(0).getCellName();
        }
        
        // falsify if same sheet
        for (int i = 0; i < size - 1; i++) {
            if (!targetCellDataList.get(i).getSheetName().equals(targetCellDataList.get(i + 1).getSheetName())) {
                return buildCellRefsString(targetCellDataList);
            }
        }
        
        // falsify if rectangular
        CellRef upperLeft = targetCellDataList.get(0);
        CellRef lowerRight = targetCellDataList.get(size - 1);
        int rowCount = lowerRight.getRow() - upperLeft.getRow() + 1;
        int colCount = lowerRight.getCol() - upperLeft.getCol() + 1;
        if (size != colCount * rowCount) {
            return buildCellRefsString(targetCellDataList);
        }
        // Fast exit if horizontal or vertical
        if (rowCount == 1 || colCount == 1) {
            return upperLeft.getCellName() + ":" + lowerRight.getCellName();
        }
        
        // Hole in rectangle with same cell count check
        // Check if upperLeft is most upper cell and most left cell. And check if lowerRight is most lower cell and most right cell.
        int minRow = upperLeft.getRow();
        int minCol = upperLeft.getCol();
        int maxRow = minRow;
        int maxCol = minCol;
        for (CellRef cell : targetCellDataList) {
            if (cell.getCol() < minCol) {
                minCol = cell.getCol();
            }
            if (cell.getCol() > maxCol) {
                maxCol = cell.getCol();
            }
            if (cell.getRow() < minRow) {
                minRow = cell.getRow();
            }
            if (cell.getRow() > maxRow) {
                maxRow = cell.getRow();
            }
        }
        if (!(maxRow == lowerRight.getRow() && minRow == upperLeft.getRow() && maxCol == lowerRight.getCol() && minCol == upperLeft.getCol())) {
            return buildCellRefsString(targetCellDataList);
        }

        // Selection is either vertical, horizontal line or rectangular -> same return structure in each case
        return upperLeft.getCellName() + ":" + lowerRight.getCellName();
    }

    private static String buildCellRefsString(List cellRefs) {
        String reply = "";
        for (CellRef cellRef : cellRefs) {
            reply += "," + cellRef.getCellName();
        }
        return reply.substring(1);
    }

    /**
     * Joins strings with a separator
     * @param strings -
     * @param separator -
     * @return a string consisting of all the passed strings joined with the separator
     */
    public static String joinStrings(List strings, String separator) {
        StringBuilder sb = new StringBuilder();
        String sep = "";
        for (String s : strings) {
            sb.append(sep).append(s);
            sep = separator;
        }
        return sb.toString();
    }

    /**
     * Groups a list of cell references into a list ranges which can be used in a formula substitution
     * @param cellRefList a list of cell references
     * @param targetRangeCount a number of ranges to use when grouping
     * @return a list of cell ranges grouped by row or by column
     */
    public static List> groupByRanges(List cellRefList, int targetRangeCount) {
        List> colRanges = groupByColRange(cellRefList);
        if (targetRangeCount == 0 || colRanges.size() == targetRangeCount) {
            return colRanges;
        }
        List> rowRanges = groupByRowRange(cellRefList);
        if (rowRanges.size() == targetRangeCount) {
            return rowRanges;
        } else {
            return colRanges;
        }
    }

    /**
     * Groups a list of cell references in a column into a list of ranges
     * @param cellRefList -
     * @return a list of cell reference groups
     */
    public static List> groupByColRange(List cellRefList) {
        List> rangeList = new ArrayList>();
        if (cellRefList == null || cellRefList.size() == 0) {
            return rangeList;
        }
        List cellRefListCopy = new ArrayList(cellRefList);
        Collections.sort(cellRefListCopy, new CellRefColPrecedenceComparator());

        String sheetName = cellRefListCopy.get(0).getSheetName();
        int row = cellRefListCopy.get(0).getRow();
        int col = cellRefListCopy.get(0).getCol();
        List currentRange = new ArrayList();
        currentRange.add(cellRefListCopy.get(0));
        boolean rangeComplete = false;
        for (int i = 1; i < cellRefListCopy.size(); i++) {
            CellRef cellRef = cellRefListCopy.get(i);
            if (!cellRef.getSheetName().equals(sheetName)) {
                rangeComplete = true;
            } else {
                int rowDelta = cellRef.getRow() - row;
                int colDelta = cellRef.getCol() - col;
                if (rowDelta == 1 && colDelta == 0) {
                    currentRange.add(cellRef);
                } else {
                    rangeComplete = true;
                }
            }
            sheetName = cellRef.getSheetName();
            row = cellRef.getRow();
            col = cellRef.getCol();
            if (rangeComplete) {
                rangeList.add(currentRange);
                currentRange = new ArrayList();
                currentRange.add(cellRef);
                rangeComplete = false;
            }
        }
        rangeList.add(currentRange);
        return rangeList;
    }

    /**
     * Groups a list of cell references in a row into a list of ranges
     * @param cellRefList -
     * @return -
     */
    public static List> groupByRowRange(List cellRefList) {
        List> rangeList = new ArrayList>();
        if (cellRefList == null || cellRefList.size() == 0) {
            return rangeList;
        }
        List cellRefListCopy = new ArrayList(cellRefList);
        Collections.sort(cellRefListCopy, new CellRefRowPrecedenceComparator());

        String sheetName = cellRefListCopy.get(0).getSheetName();
        int row = cellRefListCopy.get(0).getRow();
        int col = cellRefListCopy.get(0).getCol();
        List currentRange = new ArrayList();
        currentRange.add(cellRefListCopy.get(0));
        boolean rangeComplete = false;
        for (int i = 1; i < cellRefListCopy.size(); i++) {
            CellRef cellRef = cellRefListCopy.get(i);
            if (!cellRef.getSheetName().equals(sheetName)) {
                rangeComplete = true;
            } else {
                int rowDelta = cellRef.getRow() - row;
                int colDelta = cellRef.getCol() - col;
                if (colDelta == 1 && rowDelta == 0) {
                    currentRange.add(cellRef);
                } else {
                    rangeComplete = true;
                }
            }
            sheetName = cellRef.getSheetName();
            row = cellRef.getRow();
            col = cellRef.getCol();
            if (rangeComplete) {
                rangeList.add(currentRange);
                currentRange = new ArrayList();
                currentRange.add(cellRef);
                rangeComplete = false;
            }
        }
        rangeList.add(currentRange);
        return rangeList;
    }

    /**
     * Evaluates if the passed condition is true
     * @param evaluator expression evaluator instance
     * @param condition condition string
     * @param context Jxls context to use for evaluation
     * @return true if the condition is evaluated to true or false otherwise
     */
    public static Boolean isConditionTrue(ExpressionEvaluator evaluator, String condition, Context context) {
        Object conditionResult = evaluator.evaluate(condition, context.toMap());
        if (!(conditionResult instanceof Boolean)) {
            throw new JxlsException("Condition result is not a boolean value - " + condition);
        }
        return (Boolean) conditionResult;
    }

    /**
     * Evaluates if the passed condition is true
     * @param evaluator -
     * @param context Jxls context to use for evaluation
     * @return true if the condition is evaluated to true or false otherwise
     */
    public static Boolean isConditionTrue(ExpressionEvaluator evaluator, Context context) {
        Object conditionResult = evaluator.evaluate(context.toMap());
        if (!(conditionResult instanceof Boolean)) {
            throw new EvaluationException("Condition result is not a boolean value - " + evaluator.getExpression());
        }
        return (Boolean) conditionResult;
    }

    /**
     * Dynamically sets an object property via reflection
     * @param obj -
     * @param propertyName -
     * @param propertyValue -
     * @param ignoreNonExisting -
     */
    public static void setObjectProperty(Object obj, String propertyName, String propertyValue, boolean ignoreNonExisting) {
        try {
            setObjectProperty(obj, propertyName, propertyValue);
        } catch (Exception e) {
            String msg = "failed to set property '" + propertyName + "' to value '" + propertyValue + "' for object " + obj;
            if (ignoreNonExisting) {
                logger.info(msg, e);
            } else {
                logger.warn(msg);
                throw new IllegalArgumentException(e);
            }
        }
    }

    /**
     * Dynamically sets an object property via reflection
     * @param obj -
     * @param propertyName -
     * @param propertyValue -
     * @throws NoSuchMethodException -
     * @throws InvocationTargetException -
     * @throws IllegalAccessException -
     */
    public static void setObjectProperty(Object obj, String propertyName, String propertyValue)
            throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String name = "set" + Character.toUpperCase(propertyName.charAt(0)) + propertyName.substring(1);
        Method method = obj.getClass().getMethod(name, new Class[] { String.class });
        method.invoke(obj, propertyValue);
    }

    /**
     * Gets value of the passed object by the given property name.
     * @param obj Map, DynaBean or Java bean
     * @param propertyName -
     * @param failSilently -
     * @return value (can be null)
     */
    public static Object getObjectProperty(Object obj, String propertyName, boolean failSilently) {
        try {
            return getObjectProperty(obj, propertyName);
        } catch (Exception e) {
            String msg = "failed to get property '" + propertyName + "' of object " + obj;
            if (failSilently) {
                logger.info(msg, e);
                return null;
            } else {
                logger.warn(msg);
                throw new IllegalArgumentException(e);
            }
        }
    }

    /**
     * Gets value of the passed object by the given property name.
     * @param obj Map, DynaBean or Java bean
     * @param propertyName -
     * @return value (can be null)
     * @throws NoSuchMethodException -
     * @throws InvocationTargetException -
     * @throws IllegalAccessException -
     */
    public static Object getObjectProperty(Object obj, String propertyName) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if (obj instanceof Map) { // Map access
            return ((Map) obj).get(propertyName);
        } else { // DynaBean or Java bean access
            return PropertyUtils.getProperty(obj, propertyName);
        }
    }

    /**
     * Similar to {@link #groupIterable(Iterable, String, String)} method but works for collections
     * @param collection -
     * @param groupProperty -
     * @param groupOrder -
     * @return a collection of group data objects
     */
    public static Collection groupCollection(Collection collection, String groupProperty, String groupOrder) {
        Collection result = new ArrayList();
        if (collection == null) {
            return result;
        }
        Set groupByValues;
        if (groupOrder != null) {
            if ("desc".equalsIgnoreCase(groupOrder)) {
                groupByValues = new TreeSet<>(Collections.reverseOrder());
            } else {
                groupByValues = new TreeSet<>();
            }
        } else {
            groupByValues = new LinkedHashSet<>();
        }
        for (Object bean : collection) {
            groupByValues.add(getGroupKey(bean, groupProperty));
        }
        for (Iterator iterator = groupByValues.iterator(); iterator.hasNext();) {
            Object groupValue = iterator.next();
            List groupItems = new ArrayList<>();
            for (Object bean : collection) {
                if (groupValue.equals(getGroupKey(bean, groupProperty))) {
                    groupItems.add(bean);
                }
            }
            if (!groupItems.isEmpty()) {
                result.add(new GroupData(groupItems.get(0), groupItems));
            }
        }
        return result;
    }

    /**
     * Groups items from an iterable collection using passed group property and group order
     * @param iterable iterable object
     * @param groupProperty property to use to group the items
     * @param groupOrder an order to sort the groups
     * @return a collection of group data objects
     */
    public static Collection groupIterable(Iterable iterable, String groupProperty, String groupOrder) {
        Collection result = new ArrayList();
        if (iterable == null) {
            return result;
        }
        Set groupByValues;
        if (groupOrder != null) {
            if ("desc".equalsIgnoreCase(groupOrder)) {
                groupByValues = new TreeSet<>(Collections.reverseOrder());
            } else {
                groupByValues = new TreeSet<>();
            }
        } else {
            groupByValues = new LinkedHashSet<>();
        }
        for (Object bean : iterable) {
            groupByValues.add(getGroupKey(bean, groupProperty));
        }
        for (Iterator iterator = groupByValues.iterator(); iterator.hasNext();) {
            Object groupValue = iterator.next();
            List groupItems = new ArrayList<>();
            for (Object bean : iterable) {
                if (groupValue.equals(getGroupKey(bean, groupProperty))) {
                    groupItems.add(bean);
                }
            }
            if (!groupItems.isEmpty()) {
                result.add(new GroupData(groupItems.get(0), groupItems));
            }
        }
        return result;
    }

    private static Object getGroupKey(Object bean, String propertyName) {
        Object ret = getObjectProperty(bean, propertyName, true);
        return ret == null ? "null" : ret;
    }

    /**
     * Reads all the data from the input stream, and returns the bytes read.
     * 
     * @param stream -
     * @return byte array
     * @throws IOException -
     */
    public static byte[] toByteArray(InputStream stream) throws IOException {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        byte[] buffer = new byte[4096];
        int read = 0;
        while (read != -1) {
            read = stream.read(buffer);
            if (read > 0) {
                baos.write(buffer, 0, read);
            }
        }
        return baos.toByteArray();
    }

    /**
     * Evaluates passed collection name into a {@link Collection} object
     * @param expressionEvaluator -
     * @param collectionName -
     * @param context -
     * @return an evaluated {@link Collection} instance
     */
    public static Collection transformToCollectionObject(ExpressionEvaluator expressionEvaluator, String collectionName, Context context) {
        Object collectionObject = expressionEvaluator.evaluate(collectionName, context.toMap());
        if (!(collectionObject instanceof Collection)) {
            throw new JxlsException(collectionName + " expression is not a collection");
        }
        return (Collection) collectionObject;
    }

    /**
     * @param cellRefEntry -
     * @return the sheet name regular expression string
     */
    public static String sheetNameRegex(Map.Entry> cellRefEntry) {
        return (cellRefEntry.getKey().isIgnoreSheetNameInFormat() ? "(? createTargetCellRefListByColumn(CellRef targetFormulaCellRef, List targetCells,
            List cellRefsToExclude) {
        List resultCellList = new ArrayList<>();
        int col = targetFormulaCellRef.getCol();
        for (CellRef targetCell : targetCells) {
            if (targetCell.getCol() == col
                    && targetCell.getRow() < targetFormulaCellRef.getRow()
                    && !cellRefsToExclude.contains(targetCell)) {
                resultCellList.add(targetCell);
            }
        }
        return resultCellList;
    }

    /**
     * Calculates a number of occurences of a symbol in the string
     * @param str -
     * @param ch -
     * @return -
     */
    public static int countOccurences(String str, char ch) {
        int count = 0;
        for (int i = 0; i < str.length(); i++) {
            if (str.charAt(i) == ch) {
                count++;
            }
        }
        return count;
    }

    /**
     * Evaluates the passed collection name into an {@link Iterable} object
     * @param expressionEvaluator -
     * @param collectionName -
     * @param context -
     * @return an iterable object from the {@link Context} under given name
     */
    public static Iterable transformToIterableObject(ExpressionEvaluator expressionEvaluator, String collectionName, Context context) {
        Object collectionObject = expressionEvaluator.evaluate(collectionName, context.toMap());
        if (collectionObject == null) {
            return Collections.emptyList();
        } else if (collectionObject instanceof Object[]) {
            return Arrays.asList((Object[])/*cast is important*/ collectionObject);
        } else if (collectionObject instanceof Iterable) {
            @SuppressWarnings("unchecked")
            Iterable iterable = (Iterable) collectionObject;
            return iterable;
        }
        throw new JxlsException(collectionName + " expression is not a collection or an array");
    }

    /**
     * @param name -
     * @return regular expression to detect the passed cell name
     */
    public static String getStrictCellNameRegex(String name) {
        return "(?<=[^A-Z]|^)" + name + "(?=\\D|$)";
    }

    /**
     * Return names of all multi sheet template
     *
     * @param areaList list of area
     * @return string array
     */
    static List getSheetsNameOfMultiSheetTemplate(List areaList) {
        List templateSheetsName = new ArrayList<>();
        for (Area xlsArea : areaList) {
            for (Command command : xlsArea.findCommandByName("each")) {
                boolean isAreaHasMultiSheetAttribute = ((EachCommand) command).getMultisheet() != null && !((EachCommand) command).getMultisheet().isEmpty();
                if (isAreaHasMultiSheetAttribute) {
                    templateSheetsName.add(xlsArea.getAreaRef().getSheetName());
                    break;
                }
            }
        }
        return templateSheetsName;
    }
}
    

    

    

            

    

            



    
    






    
© 2015 - 2024 Weber Informatics LLC | Privacy Policy