util.java

一个java生成自动生成Excel

package net.sf.jxls.util;

import net.sf.jxls.parser.Cell;
import net.sf.jxls.transformer.Row;
import net.sf.jxls.transformer.RowCollection;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.hssf.util.CellReference;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
import java.util.regex.Pattern;

/**
 * This class contains many utility methods used by jXLS framework
 * @author Leonid Vysochyn
 * @author Vincent Dutat
 */
public final class Util {
    protected static Log log = LogFactory.getLog(Util.class);

    private static final String[][] ENTITY_ARRAY = {
        {"quot", "34"}, // " - double-quote
        {"amp", "38"}, // & - ampersand
        {"lt", "60"}, // < - less-than
        {"gt", "62"}, // > - greater-than
        {"apos", "39"} // XML apostrophe
    };

    private static Map xmlEntities = new HashMap();
    static{
        for(int i = 0; i < ENTITY_ARRAY.length; i++){
            xmlEntities.put( ENTITY_ARRAY[i][1], ENTITY_ARRAY[i][0] );
        }
    }


    public static void removeRowCollectionPropertiesFromRow(RowCollection rowCollection) {
        int startRow = rowCollection.getParentRow().getHssfRow().getRowNum();
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        for(int i = 0; i <= rowCollection.getDependentRowNumber(); i++){
            HSSFRow hssfRow = sheet.getRow( startRow + i );
            for (short j = hssfRow.getFirstCellNum(); j <= hssfRow.getLastCellNum(); j++) {
                HSSFCell cell = hssfRow.getCell(j);
                removeRowCollectionPropertyFromCell(cell, rowCollection.getCollectionProperty().getFullCollectionName());
            }
        }
    }

    private static void removeRowCollectionPropertyFromCell(HSSFCell cell, String collectionName) {
        String regex = "[-+*/().A-Za-z_0-9\\s]*";
        if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
            String cellValue = cell.getStringCellValue();
            String strToReplace = "\\$\\{" + regex + collectionName.replaceAll("\\.", "\\\\.") + "\\." + regex + "\\}";
            cell.setCellValue( cellValue.replaceAll( strToReplace, "") );
        }
    }

    /**
     * Removes merged region from sheet
     * @param sheet
     * @param region
     */
    private static void removeMergedRegion(HSSFSheet sheet, Region region) {
        int index = getMergedRegionIndex(sheet, region);
        sheet.removeMergedRegion( index );
    }

    /**
     * returns merged region index
     * @param sheet
     * @param mergedRegion
     * @return index of mergedRegion or -1 if the region not found
     */
    private static int getMergedRegionIndex(HSSFSheet sheet, Region mergedRegion){
        for(int i = 0; i < sheet.getNumMergedRegions(); i++){
            Region region = sheet.getMergedRegionAt( i );
            if( region.equals( mergedRegion ) ){
                return i;
            }
        }
        return -1;
    }

    private static boolean isNewMergedRegion(Region region, Collection mergedRegions){
        return !mergedRegions.contains(region);
    }

    public static Region getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
        for( int i = 0; i < sheet.getNumMergedRegions(); i++){
            Region merged = sheet.getMergedRegionAt( i );
            if( merged.contains( rowNum, cellNum) ){
                return merged;
            }
        }
        return null;
    }

    public static boolean removeMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
        Set mergedRegionNumbersToRemove = new TreeSet();
        for( int i = 0; i < sheet.getNumMergedRegions(); i++){
            Region merged = sheet.getMergedRegionAt( i );
            if( merged.contains( rowNum, cellNum) ){
                mergedRegionNumbersToRemove.add( new Integer(i) );
            }
        }
        for (Iterator iterator = mergedRegionNumbersToRemove.iterator(); iterator.hasNext();) {
            Integer regionNumber = (Integer) iterator.next();
            sheet.removeMergedRegion( regionNumber.intValue() );
        }
        return !mergedRegionNumbersToRemove.isEmpty();
    }

    public static void prepareCollectionPropertyInRowForDuplication(RowCollection rowCollection, String collectionItemName ){
        int startRow = rowCollection.getParentRow().getHssfRow().getRowNum();
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        for(int i = 0; i <= rowCollection.getDependentRowNumber(); i++){
            HSSFRow hssfRow = sheet.getRow( startRow + i );
            for (short j = hssfRow.getFirstCellNum(); j <= hssfRow.getLastCellNum(); j++) {
                HSSFCell cell = hssfRow.getCell(j);
                prepareCollectionPropertyInCellForDuplication(cell, rowCollection.getCollectionProperty().getFullCollectionName(), collectionItemName);
            }
        }
    }

    private static void prepareCollectionPropertyInCellForDuplication(HSSFCell cell, String collectionName, String collectionItemName) {
        if( cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING ){
            String cellValue = cell.getStringCellValue();
            String newValue = replaceCollectionProperty( cellValue, collectionName, collectionItemName );
//            String newValue = cellValue.replaceFirst(collectionName, collectionItemName);
            cell.setCellValue(newValue);
        }
    }

    private static String replaceCollectionProperty(String property, String collectionName, String newValue){
        return property.replaceAll(collectionName, newValue);
    }

    public static void prepareCollectionPropertyInRowForContentDuplication(RowCollection rowCollection) {
        for( int i = 0; i < rowCollection.getCells().size(); i++){
            Cell cell = (Cell) rowCollection.getCells().get(i);
            prepareCollectionPropertyInCellForDuplication( cell.getHssfCell(),
                    rowCollection.getCollectionProperty().getFullCollectionName(), rowCollection.getCollectionItemName());
        }
    }

    public static void duplicateRowCollectionProperty(RowCollection rowCollection){
        Collection collection = rowCollection.getCollectionProperty().getCollection();
        int rowNum = rowCollection.getParentRow().getHssfRow().getRowNum();
        HSSFRow srcRow = rowCollection.getParentRow().getHssfRow();
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        if( collection.size() > 1 ){
            for(int i = 1; i < collection.size(); i++){
                HSSFRow destRow = sheet.getRow( rowNum + i );
                for( int j = 0; j < rowCollection.getCells().size(); j++){
                    Cell cell = (Cell) rowCollection.getCells().get(j);
                    if( !cell.isEmpty() ){
                        HSSFCell destCell = destRow.getCell( cell.getHssfCell().getCellNum() );
                        if( destCell==null ){
                            destCell = destRow.createCell( cell.getHssfCell().getCellNum() );
                        }
                        copyCell( srcRow.getCell( cell.getHssfCell().getCellNum() ), destCell, false);
                    }
                }
            }
        }
    }


    public static int duplicateRow( RowCollection rowCollection ){
        Collection collection = rowCollection.getCollectionProperty().getCollection();
        int row = rowCollection.getParentRow().getHssfRow().getRowNum();
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        if (collection.size() > 1) {
            if (rowCollection.getDependentRowNumber() == 0) {
                sheet.shiftRows(row + 1, sheet.getLastRowNum(), collection.size() - 1, true, false);
                duplicateStyle(rowCollection, row, row + 1, collection.size() - 1);
                shiftUncoupledCellsUp( rowCollection);
            } else {
                for (int i = 0; i < collection.size() - 1; i++) {
                    shiftCopyRowCollection( rowCollection );
                }
                shiftUncoupledCellsUp( rowCollection );
            }
        }
        return (collection.size() - 1) * (rowCollection.getDependentRowNumber() + 1);
    }

    private static void shiftCopyRowCollection(RowCollection rowCollection) {
        HSSFSheet hssfSheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        int startRow = rowCollection.getParentRow().getHssfRow().getRowNum();
        int num = rowCollection.getDependentRowNumber();
        hssfSheet.shiftRows(startRow + num + 1,
                hssfSheet.getLastRowNum(), num + 1, true, false);
        copyRowCollection(rowCollection);
    }

    private static void copyRowCollection(RowCollection rowCollection) {
        HSSFSheet sheet = rowCollection.getParentRow().getSheet().getHssfSheet();
        int from = rowCollection.getParentRow().getHssfRow().getRowNum();
        int num = rowCollection.getDependentRowNumber() + 1;
        int to = from + num;
        Set mergedRegions  = new TreeSet();
        for (int i = from; i < from + num; i++) {
            HSSFRow srcRow = sheet.getRow(i);
            HSSFRow destRow = sheet.getRow(to + i - from);
            if (destRow == null) {
                destRow = sheet.createRow(to + i - from);
            }
            destRow.setHeight(srcRow.getHeight());
            for (short j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
                HSSFCell srcCell = srcRow.getCell(j);
                if (srcCell != null) {
                    HSSFCell destCell = destRow.createCell(j);
                    copyCell(srcCell, destCell, true);
                    Region mergedRegion = getMergedRegion(sheet, i, j);
                    if( mergedRegion != null ){
                        Region newMergedRegion = new Region( to - from + mergedRegion.getRowFrom(), mergedRegion.getColumnFrom(),
                                to - from + mergedRegion.getRowTo(), mergedRegion.getColumnTo() );
                        if( isNewMergedRegion( newMergedRegion, mergedRegions ) ){
                            mergedRegions.add( newMergedRegion );
                        }
                    }
                }
            }
        }
        // set merged regions
        for (Iterator iterator = mergedRegions.iterator(); iterator.hasNext();) {
            Region region = (Region) iterator.next();
            sheet.addMergedRegion( region );
        }
    }

    private static void shiftUncoupledCellsUp(RowCollection rowCollection) {
        Row row = rowCollection.getParentRow();
        if( row.getCells().size() > rowCollection.getCells().size() ){
            for (int i = 0; i < row.getCells().size(); i++) {
                Cell cell = (Cell) row.getCells().get(i);
                if( !rowCollection.containsCell( cell ) ){
                    shiftColumnUp(cell, row.getHssfRow().getRowNum() + rowCollection.getCollectionProperty().getCollection().size(),
                            rowCollection.getCollectionProperty().getCollection().size()-1);
                }
            }
        }
    }

    private static void shiftColumnUp(Cell cell, int startRow, int shiftNumber) {
        HSSFSheet sheet = cell.getRow().getSheet().getHssfSheet();
        short cellNum = cell.getHssfCell().getCellNum();
        List hssfMergedRegions = new ArrayList();
        // find all merged regions in this area
        for(int i = startRow; i<=sheet.getLastRowNum(); i++){
            Region region = getMergedRegion( sheet, i, cellNum );
            if( region!=null && isNewMergedRegion( region, hssfMergedRegions )){
                hssfMergedRegions.add( region );
            }
        }
        // move all related cells up
        for(int i = startRow; i <= sheet.getLastRowNum(); i++){
            if( sheet.getRow(i).getCell(cellNum)!=null ){
                HSSFCell destCell = sheet.getRow( i - shiftNumber ).getCell( cellNum );
                if( destCell == null ){
                    destCell = sheet.getRow( i - shiftNumber ).createCell( cellNum );
                }
                moveCell( sheet.getRow(i).getCell(cellNum), destCell );
            }
        }
        // remove previously shifted merged regions in this area
        for (Iterator iterator = hssfMergedRegions.iterator(); iterator.hasNext();) {
            removeMergedRegion( sheet, (Region) iterator.next() );
        }
        // set merged regions for shifted cells
        for (Iterator iterator = hssfMergedRegions.iterator(); iterator.hasNext();) {
            Region region = (Region) iterator.next();
            Region newRegion = new Region( region.getRowFrom() - shiftNumber, region.getColumnFrom(), region.getRowTo() - shiftNumber, region.getColumnTo() );
            sheet.addMergedRegion( newRegion );
        }
        // remove moved cells
        int i = sheet.getLastRowNum();