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

Go to download
Show more of this group Show more artifacts with this name
Show all versions of jxls-jdk1.6 Show documentation
Small library for Excel generation based on XLS templates
The newest version!
package org.jxls.util;

import org.jxls.common.*;
import org.jxls.expression.ExpressionEvaluator;
import org.jxls.expression.JexlExpressionEvaluator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Utility class with various helper methods used by other classes
 * @author Leonid Vysochyn
 *         Date: 2/3/12
 */
public class Util {
    static Logger logger = LoggerFactory.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;
    }

    public static List getJointedCellRefs(String formula){
        return getStringPartsByPattern(formula, regexJointedCellRefPattern);
    }
    
    public static List getCellRefsFromJointedCellRef(String jointedCellRef){
        return getStringPartsByPattern(jointedCellRef, regexCellRefPattern);
    }
    
    public static boolean formulaContainsJointedCellRef(String formula){
        return regexJointedCellRefPattern.matcher(formula).find();
    }

    public static String createTargetCellRef(List targetCellDataList) {
        String resultRef = "";
        if( targetCellDataList != null && !targetCellDataList.isEmpty()){
            List cellRefs = new ArrayList();
            boolean rowRange = true;
            boolean colRange = true;
            Iterator iterator = targetCellDataList.iterator();
            CellRef firstCellRef = iterator.next();
            cellRefs.add(firstCellRef.getCellName());
            String sheetName = firstCellRef.getSheetName();
            int row = firstCellRef.getRow();
            int col = firstCellRef.getCol();
            while( iterator.hasNext() ) {
                CellRef cellRef = iterator.next();
                if( (rowRange || colRange) && !cellRef.getSheetName().equals(sheetName) ){
                    rowRange = false;
                    colRange = false;
                }
                if( rowRange && !(cellRef.getRow() - row == 1 && cellRef.getCol() == col )){
                    rowRange = false;
                }
                if( colRange && !(cellRef.getCol() - col == 1 && cellRef.getRow() == row)){
                    colRange = false;
                }
                sheetName = cellRef.getSheetName();
                row = cellRef.getRow();
                col = cellRef.getCol();
                cellRefs.add(cellRef.getCellName());
            }
            if( (rowRange || colRange) && cellRefs.size() > 1 ){
                resultRef = cellRefs.get(0) + ":" + cellRefs.get( cellRefs.size() -1 );
            }else{
                resultRef = joinStrings(cellRefs, ",");
            }
        }
        return resultRef;
    }
    
    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();
    }

    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;
    }

    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;
    }

    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;
    }

    public static Boolean isConditionTrue(ExpressionEvaluator evaluator, String condition, Context context){
        Object conditionResult = evaluator.evaluate(condition, context.toMap());
        if( !(conditionResult instanceof Boolean) ){
            throw new RuntimeException("Condition result is not a boolean value - " + condition);
        }
        return (Boolean)conditionResult;
    }

    public static Boolean isConditionTrue(JexlExpressionEvaluator evaluator, Context context){
        Object conditionResult = evaluator.evaluate(context.toMap());
        if( !(conditionResult instanceof Boolean) ){
            throw new RuntimeException("Condition result is not a boolean value - " + evaluator.getJexlExpression().getExpression());
        }
        return (Boolean)conditionResult;
    }

    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);
            }
        }
    }

    public static void setObjectProperty(Object obj, String propertyName, String propertyValue) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Method method = obj.getClass().getMethod("set" + Character.toUpperCase(propertyName.charAt(0)) + propertyName.substring(1), new Class[]{String.class} );
        method.invoke(obj, propertyValue);
    }
    
    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);
            }
        }
    }
    
    public static Object getObjectProperty(Object obj, String propertyName) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Method method = obj.getClass().getMethod("get" + Character.toUpperCase(propertyName.charAt(0)) + propertyName.substring(1));
        return method.invoke(obj);
    }
    
    public static Collection groupCollection(Collection collection, String groupProperty, String groupOrder){
        Collection result = new ArrayList();
        if( collection != null ){

            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(getObjectProperty(bean, groupProperty, true));
            }
            for (Iterator iterator = groupByValues.iterator(); iterator.hasNext();) {
                Object groupValue = iterator.next();
                List groupItems = new ArrayList();
                for (Object bean : collection) {
                    if( groupValue.equals(getObjectProperty(bean, groupProperty, true)) ){
                        groupItems.add( bean );
                    }
                }
                GroupData groupData = new GroupData( groupItems.get(0), groupItems );
                result.add(groupData);
            }
        }
        return result;
    }

    /**
   	 * Reads all the data from the input stream, and returns the bytes read.
   	 */
   	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();
   	}


    public static Collection transformToCollectionObject(ExpressionEvaluator expressionEvaluator, String collectionName, Context context){
        Object collectionObject = expressionEvaluator.evaluate(collectionName, context.toMap());
        if( !(collectionObject instanceof Collection) ){
            throw new RuntimeException(collectionName + " expression is not a collection");
        }
        return (Collection) collectionObject;
    }

    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;
    }
}