ExcelAddress.java

/*
 * Copyright (C) 2006-2016 DLR, Germany
 * 
 * All rights reserved
 * 
 * http://www.rcenvironment.de/
 */

package de.rcenvironment.components.excel.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel address representation.
 * 
 * @author Markus Kunde
 */
public class ExcelAddress implements Serializable {

    private static final long serialVersionUID = 169274298193312428L;

    private String fullAddress;

    private String worksheetName;

    private String firstCell;

    private String lastCell;

    private int numberOfRows;

    private int numberOfColumns;

    private int beginningRowNumber;

    private int beginningColumnNumber;

    private String userDefinedNameForAddress;

    /**
     * Copy Constructor.
     * 
     * @param addr Excel Address
     */
    public ExcelAddress(final ExcelAddress addr) {
        fullAddress = addr.fullAddress;
        worksheetName = addr.worksheetName;
        firstCell = addr.firstCell;
        lastCell = addr.lastCell;
        numberOfRows = addr.numberOfRows;
        numberOfColumns = addr.numberOfColumns;
        beginningRowNumber = addr.beginningRowNumber;
        beginningColumnNumber = addr.beginningColumnNumber;
        userDefinedNameForAddress = addr.userDefinedNameForAddress;
    }

    /**
     * Constructor for Excel cell address. Address will be validated with concrete Excel file.
     * 
     * @param excelFile Excel file
     * @param rawAddress sheetname and cell reference(s), e. g., "Sheet1!A1:B2"
     * @param ExcelException thrown if not a real Excel file
     */
    public ExcelAddress(final File excelFile, final String rawAddress) throws ExcelException {
        InputStream inp = null;
        try {
            inp = new FileInputStream(excelFile);
            org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(inp);
            Name name = wb.getName(rawAddress);

            if (name != null) {
                // User defined name
                fullAddress = name.getRefersToFormula();
                fullAddress = StringUtils.remove(fullAddress, ExcelComponentConstants.ABSOLUTEFLAG);

                userDefinedNameForAddress = name.getNameName();

                worksheetName = name.getSheetName();
                worksheetName = cutBeginningAndEndingApostrophe(worksheetName);

                AreaReference ar = new AreaReference(fullAddress);

                firstCell = StringUtils.split(ar.getFirstCell().formatAsString(), ExcelComponentConstants.DIVIDER_TABLECELLADDRESS)[1];
                lastCell = StringUtils.split(ar.getLastCell().formatAsString(), ExcelComponentConstants.DIVIDER_TABLECELLADDRESS)[1];

                int rowUpperLeft = ar.getFirstCell().getRow();
                int rowLowerRight = ar.getLastCell().getRow();
                numberOfRows = (rowLowerRight - rowUpperLeft) + 1;

                int colUpperLeft = ar.getFirstCell().getCol();
                int colLowerRight = ar.getLastCell().getCol();
                numberOfColumns = (colLowerRight - colUpperLeft) + 1;

                beginningRowNumber = ar.getFirstCell().getRow() + 1;
                beginningColumnNumber = ar.getFirstCell().getCol() + 1;
            } else {
                // No user defined name; test if valid address
                boolean isNewXlFile;

                if (wb instanceof HSSFWorkbook) {
                    isNewXlFile = false;
                } else if (wb instanceof XSSFWorkbook) {
                    isNewXlFile = true;
                } else {
                    throw new ExcelException("Could not determine if Excel file is old-style (Excel 97-2007) "
                        + "or new-style (Excel 2007+) based.");
                }

                // Short validation
                String[] splitAddress = rawAddress.split("!");
                String lastAddressToken = splitAddress[splitAddress.length - 1];
                String exceptionMessage = "Validation of address '" + rawAddress + "' failed. "
                    + "Most likely it's no valid Excel cell address.";
                if (isNewXlFile) {
                    if (splitAddress.length != 2
                        || !(lastAddressToken.matches("^\\$?([A-Za-z]{0,3})\\$?([0-9]{0,7}):?\\$?([A-Za-z]{0,3})\\$?([0-9]{0,7})$"))
                        || (lastAddressToken.matches("[A-Za-z]+"))
                        || (lastAddressToken.matches("[0-9]+"))
                        || (lastAddressToken.startsWith(ExcelComponentConstants.DIVIDER_CELLADDRESS))
                        || (lastAddressToken.endsWith(ExcelComponentConstants.DIVIDER_CELLADDRESS))) {
                        throw new ExcelException(exceptionMessage);
                    }
                } else {
                    if (splitAddress.length != 2
                        || !(lastAddressToken.matches("^\\$?([A-Za-z]{0,2})\\$?([0-9]{0,5}):?\\$?([A-Za-z]{0,2})\\$?([0-9]{0,5})$"))
                        || (lastAddressToken.matches("[A-Za-z]+"))
                        || (lastAddressToken.matches("[0-9]+"))
                        || (lastAddressToken.startsWith(ExcelComponentConstants.DIVIDER_CELLADDRESS))
                        || (lastAddressToken.endsWith(ExcelComponentConstants.DIVIDER_CELLADDRESS))) {
                        throw new ExcelException(exceptionMessage);
                    }
                }
                String rawAddressWithoutAbsoluteFlag = StringUtils.remove(rawAddress, ExcelComponentConstants.ABSOLUTEFLAG);

                worksheetName = StringUtils.split(rawAddressWithoutAbsoluteFlag, ExcelComponentConstants.DIVIDER_TABLECELLADDRESS)[0];
                worksheetName = cutBeginningAndEndingApostrophe(worksheetName);

                firstCell = getAddressPart(rawAddressWithoutAbsoluteFlag, true, isNewXlFile);

                lastCell = getAddressPart(rawAddressWithoutAbsoluteFlag, false, isNewXlFile);

                numberOfRows = getNumberOfRows(firstCell, lastCell);

                numberOfColumns = getNumberOfColumns(firstCell, lastCell);

                beginningRowNumber = Integer.valueOf(getRowNumberOfCell(firstCell));

                beginningColumnNumber = getNumberOfColumnChar(getColumnCharsOfCell(firstCell));

                fullAddress = worksheetName + ExcelComponentConstants.DIVIDER_TABLECELLADDRESS + firstCell
                    + ExcelComponentConstants.DIVIDER_CELLADDRESS + lastCell;

                // Test if full address
                AreaReference ar = new AreaReference(fullAddress);
                fullAddress = ar.formatAsString();

                Sheet sheet = wb.getSheet(worksheetName);
                if (sheet == null) {
                    throw new ExcelException("Cannot discover sheet in Excel file.");
                }
            }
        } catch (NumberFormatException e) {
            throw new ExcelException(e);
        } catch (FileNotFoundException e) {
            throw new ExcelException(e);
        } catch (InvalidFormatException e) {
            throw new ExcelException("Excel file has an invalid format.", e);
        } catch (IllegalArgumentException e) {
            throw new ExcelException("File is no Excel file.", e);
        } catch (IOException e) {
            throw new ExcelException("Excel file is not found or cannot be opened.", e);
        } finally {
            if (inp != null) {
                try {
                    inp.close();
                } catch (IOException e) {
                    throw new ExcelException("Cannot close access to Excel file.", e);
                }
            }

            // Not nice, but workbook-object will not released.
            ExcelUtils.destroyGarbage();
        }
    }

    private static String cutBeginningAndEndingApostrophe(final String rawString) {
        String resultString;
        resultString = StringUtils.removeStart(rawString, "'");
        resultString = StringUtils.removeEnd(resultString, "'");

        return resultString;
    }

    /**
     * Returns full (relative) Excel cell address.
     * 
     * @return full relative Excel cell address
     */
    public String getFullAddress() {
        return fullAddress;
    }

    /**
     * Returns user defined name or null if there is no user defined name.
     * 
     * @return user defined name or null
     */
    public String getUserDefinedName() {
        return userDefinedNameForAddress;
    }

    /**
     * Returns worksheet name of Excel cell address.
     * 
     * @return worksheet name
     */
    public String getWorkSheetName() {
        return worksheetName;
    }

    /**
     * Returns the upper left cell reference of a cell-area. In case there is only one cell its cell reference will be returned.
     * 
     * @return upper left cell reference.
     */
    public String getFirstCell() {
        return firstCell;
    }

    /**
     * Returns the lower right cell reference of a cell-area. In case there is only one cell its cell reference will be returned.
     * 
     * @return lower right cell reference.
     */
    public String getLastCell() {
        return lastCell;
    }

    /**
     * Returns the number of rows a cell-area is defined over.
     * 
     * @return number of rows
     */
    public int getNumberOfRows() {
        return numberOfRows;
    }

    /**
     * Returns the number of columns a cell-area is defined over.
     * 
     * @return number of columns
     */
    public int getNumberOfColumns() {
        return numberOfColumns;
    }

    /**
     * Returns beginning row number of cell-area. Begins with 1.
     * 
     * @return beginning row number
     */
    public int getBeginningRowNumber() {
        return beginningRowNumber;
    }

    /**
     * Returns beginning column number of cell-area. Begins with 1.
     * 
     * @return beginning column number
     */
    public int getBeginningColumnNumber() {
        return beginningColumnNumber;
    }

    /**
     * Tests if user defined name is valid regarding regex.
     * 
     * @param regex regular expression
     * @return true if regex on user defend name returns true
     */
    public boolean isUserDefindNameOfScheme(final String regex) {
        if (userDefinedNameForAddress != null && !userDefinedNameForAddress.isEmpty() && userDefinedNameForAddress.matches(regex)) {
            return true;
        }
        return false;
    }

    /**
     * Resizes the Excel address for a range. Starting point is first cell of Excel address.
     * 
     * @param excelFile Excel file
     * @param originAddr the address which will be used as a basis
     * @param rows the offset-number of rows the address should have
     * @param columns the offset-number of columns the address should have
     * @return the new ExcelAddress
     */
    public static ExcelAddress getExcelAddressForTableRange(final File excelFile, final ExcelAddress originAddr,
        final int rows, final int columns) {
        ExcelAddress address = new ExcelAddress(originAddr);

        if (rows > 0 && columns > 0) {
            address.lastCell = "";

            String columnName = getNextColumnName(getColumnCharsOfCell(address.firstCell), columns - 1);
            address.lastCell = columnName.concat(String.valueOf(Integer.valueOf(getRowNumberOfCell(address.firstCell)) + rows - 1));

            if (!originAddr.getFirstCell().equalsIgnoreCase(originAddr.getLastCell())) {
                address.fullAddress = StringUtils.removeEnd(address.fullAddress, originAddr.getLastCell());
            }

            if (!address.fullAddress.endsWith(ExcelComponentConstants.DIVIDER_CELLADDRESS)) {
                address.fullAddress = address.fullAddress.concat(ExcelComponentConstants.DIVIDER_CELLADDRESS);
            }

            address.fullAddress = address.fullAddress.concat(address.lastCell);
            address.userDefinedNameForAddress = null;
            address.numberOfColumns = columns;
            address.numberOfRows = rows;

            address = new ExcelAddress(excelFile, address.getFullAddress());
        }
        return address;
    }

    /**
     * Returns upper left address of Excel cell address.
     * 
     * @param address [Table1!A1, Table1!A:A, Table1!1:1, Table1!A1:B5]
     * @param leftAddress flag if left (true) or right (false) address should be discovered.
     * @param isXlsX should be true if Excel file is a "new" one with more rows and columns.
     * @return [A1]
     */
    private static String getAddressPart(final String address, final boolean leftAddress, final boolean isXlsX) {
        int addressFlag;
        if (leftAddress) {
            addressFlag = 0;
        } else {
            addressFlag = 1;
        }

        String cells = address.split(ExcelComponentConstants.DIVIDER_TABLECELLADDRESS)[1];
        // cells = [A1, A, 1, A1:B5]

        String[] normalizedCells = cells.split(ExcelComponentConstants.DIVIDER_CELLADDRESS);

        if (normalizedCells.length == 1) {
            return normalizedCells[0];
        }

        String columnChars = getColumnCharsOfCell(normalizedCells[addressFlag]);
        String rowNumber = getRowNumberOfCell(normalizedCells[addressFlag]);

        if (columnChars == null || columnChars.equals("")) {
            if (leftAddress) {
                columnChars = ExcelComponentConstants.DEFAULTCOLUMNBEGIN;
            } else {
                if (isXlsX) {
                    columnChars = SpreadsheetVersion.EXCEL2007.getLastColumnName();
                } else {
                    columnChars = SpreadsheetVersion.EXCEL97.getLastColumnName();
                }
            }
        }
        if (rowNumber == null || rowNumber.equals("")) {
            if (leftAddress) {
                rowNumber = ExcelComponentConstants.DEFAULTROWBEGIN;
            } else {
                if (isXlsX) {
                    rowNumber = Integer.toString(SpreadsheetVersion.EXCEL2007.getMaxRows());
                } else {
                    rowNumber = Integer.toString(SpreadsheetVersion.EXCEL97.getMaxRows());
                }
            }
        }

        return columnChars + rowNumber;
    }

    /**
     * Get row label number of Excel cell.
     * 
     * @param cellname complete cell name
     * @return row number; empty if there is no row number
     */
    private static String getRowNumberOfCell(final String cellname) {
        String[] result = cellname.split("[\\D]+");
        String returnVal = null;
        if (result.length == 0) {
            returnVal = "";
        } else if (result.length == 1) {
            returnVal = result[0];
        } else {
            returnVal = result[1];
        }
        return returnVal;
    }

    /**
     * Get column label chars of Excel cell.
     * 
     * @param cellname complete cell name
     * @return column chars; empty if there are no column chars
     */
    private static String getColumnCharsOfCell(final String cellname) {
        String[] result = cellname.split("[\\d]+");
        String returnVal = null;
        if (result.length == 0) {
            returnVal = "";
        } else {
            returnVal = result[0];
        }
        return returnVal;
    }

    /**
     * Get number of columns.
     * 
     * @param upperLeftCell upper left cell address
     * @param lowerRightCell lower right cell address
     * @return number of columns
     */
    private static int getNumberOfColumns(final String upperLeftCell, final String lowerRightCell) {
        String tempUl = getColumnCharsOfCell(upperLeftCell);
        if (tempUl != null && !tempUl.equals("")) {
            String tempLr = getColumnCharsOfCell(lowerRightCell);
            if (tempLr != null && !tempLr.equals("")) {
                int number = 1;
                while (!tempUl.equals(tempLr)) {
                    number++;
                    tempUl = getNextColumnName(tempUl, 1);
                }
                return number;
            }
        }
        return 1;
    }

    /**
     * Returns number of location of a column char. E. g.: "F" is 6; "AA" is 27.
     * 
     * @param columnChar Only column char
     * @return number of location
     */
    private static int getNumberOfColumnChar(final String columnChar) {
        return toPos(columnChar);
    }

    private static int toPos(String col) {
        final int twentySix = 26;
        final int thirtySix = 36;
        final int nine = 9;

        int pos = 0;
        for (int i = 0; i < col.length(); i++) {
            pos *= twentySix;
            pos += Integer.parseInt(col.substring(i, i + 1), thirtySix) - nine;
        }
        return pos;
    }

    private static String getOffsetCol(String col, int offset) {
        return toCol(toPos(col) + offset);
    }

    private static String toCol(int pos) {
        final int twentySix = 26;
        final int sixtyFive = 65;

        String col = "";
        while (pos > 0) {
            pos--;
            col = (char) (pos % twentySix + sixtyFive) + col;
            pos = pos / twentySix;
        }
        return col;
    }

    /**
     * Returns next column name regarding an offset.
     * 
     * @param currentColumnName Name of column, e. g., "A"
     * @param offset Offset of column, "1" for next column
     * @return next columnname regarding offset
     */
    private static String getNextColumnName(final String currentColumnName, final int offset) {
        return getOffsetCol(currentColumnName, offset);
    }

    /**
     * Get number of rows.
     * 
     * @param upperLeftCell upper left cell address
     * @param lowerRightCell lower right cell address
     * @return number of rows
     */
    private static int getNumberOfRows(final String upperLeftCell, final String lowerRightCell) {
        String tempUl = getRowNumberOfCell(upperLeftCell);
        if (tempUl != null && !tempUl.equals("")) {
            int ul = Integer.valueOf(tempUl);

            String tempLr = getRowNumberOfCell(lowerRightCell);
            if (tempLr != null && !tempLr.equals("")) {
                int lr = Integer.valueOf(tempLr);
                return (lr - ul) + 1;
            }
        }
        return 1;
    }

}