org.apache.poi.ss.usermodel.Workbook

Here are the examples of the java api org.apache.poi.ss.usermodel.Workbook taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

930 Examples 7

19 Source : MSExcelParser.java
with Apache License 2.0
from ZuInnoTe

/*
*
* This clreplaced is responsible for parsing Excel content in OOXML format and old excel format
*
*/
public clreplaced MSExcelParser implements OfficeReaderParserInterface {

    // application/vnd.ms-excel.sheet.binary.macroEnabled.12
    private static final String MIMETYPE_EXCEL_BINARY_ID = "ms-excel.sheet.binary";

    private static final String MATCH_ALL = "matchAll";

    private static final String NOT_MATCHING = "Not matching: ";

    private static final String COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT = "Could not retrieve linked workbooks for old Excel format.";

    private static final Log LOG = LogFactory.getLog(MSExcelParser.clreplaced.getName());

    public static final String DATE_FORMAT = "hh:mm:ss dd.MM.yyyy";

    public static final int MAX_LINKEDWB_OLDEXCEL = 100;

    private FormulaEvaluator formulaEvaluator;

    private InputStream in;

    private DataFormatter useDataFormatter = null;

    private String[] sheets = null;

    private Workbook currentWorkbook = null;

    // current sheet where we are
    private int currentSheet = 0;

    // current index of sheets, if specified
    private int sheetsIndex = 0;

    private int currentRow = 0;

    private String currentSheetName = "";

    private HashMap<String, FormulaEvaluator> addedFormulaEvaluators;

    private ArrayList<Workbook> addedWorkbooks;

    private boolean filtered = false;

    private HadoopOfficeReadConfiguration hocr;

    private String[] header;

    private int currentSkipLine = 0;

    /*
	* In the default case all sheets are parsed one after the other.
	* @param hocr HadoopOffice configuration for reading files:
	* locale to use (if null then default locale will be used), see java.util.Locale
	* filename Filename of the doreplacedent
	* ignoreMissingLinkedWorkbooks ignore missing linked Workbooks
	* preplacedword Preplacedword of this doreplacedent (null if no preplacedword)
	* metadataFilter filter on metadata. The name is the metadata attribute name and the property is a filter which contains a regular expression. Currently the following are supported for .xlsx doreplacedents: category,contentstatus, contenttype,created,creator,description,identifier,keywords,lastmodifiedbyuser,lastprinted,modified,revision,subject,replacedle. Additionally all custom.* are defined as custom properties. Example custom.myproperty. Finally, matchAll can be set to true (all metadata needs to be matched), or false (at least one of the metadata item needs to match).
 Currently the following are supported for .xls doreplacedents: applicationname,author,charcount, comments, createdatetime,edittime,keywords,lastauthor,lastprinted,lastsavedatetime,pagecount,revnumber,security,subject,template,replacedle,wordcount. Finally, matchAll can be set to true (all metadata needs to be matched), or false (at least one of the metadata item needs to match).
	*
	*/
    public MSExcelParser(HadoopOfficeReadConfiguration hocr) {
        this(hocr, null);
    }

    /*
	*
	* Only process selected sheets (one after the other)
	*
	* @param hocr HadoopOffice configuration for reading files:
	* locale to use (if null then default locale will be used), see java.util.Locale
	* ignoreMissingLinkedWorkbooks ignore missing linked Workbooks
	* preplacedword Preplacedword of this doreplacedent (null if no preplacedword)
	* metadataFilter filter on metadata. The name is the metadata attribute name and the property is a filter which contains a regular expression. Currently the following are supported for .xlsx doreplacedents: category,contentstatus, contenttype,created,creator,description,identifier,keywords,lastmodifiedbyuser,lastprinted,modified,revision,subject,replacedle. Additionally all custom.* are defined as custom properties. Example custom.myproperty. Finally, matchAll can be set to true (all metadata needs to be matched), or false (at least one of the metadata item needs to match).
 Currently the following are supported for .xls doreplacedents: applicationname,author,charcount, comments, createdatetime,edittime,keywords,lastauthor,lastprinted,lastsavedatetime,pagecount,revnumber,security,subject,template,replacedle,wordcount. Finally, matchAll can be set to true (all metadata needs to be matched), or false (at least one of the metadata item needs to match).
	* @param sheets selecrted sheets
	*
	*/
    public MSExcelParser(HadoopOfficeReadConfiguration hocr, String[] sheets) {
        this.sheets = sheets;
        this.hocr = hocr;
        if (hocr.getLocale() == null) {
            if (!this.hocr.getEmulateCSV()) {
                // use default locale
                useDataFormatter = new DataFormatter();
            } else {
                // use default locale, emulateCSV
                useDataFormatter = new DataFormatter(true);
            }
        } else {
            if (!this.hocr.getEmulateCSV()) {
                useDataFormatter = new DataFormatter(hocr.getLocale());
            } else {
                useDataFormatter = new DataFormatter(hocr.getLocale(), true);
            }
        }
        this.addedFormulaEvaluators = new HashMap<>();
        this.addedWorkbooks = new ArrayList<>();
    }

    /*
	*
	* Parses the given InputStream containing Excel data. The type of InputStream (e.g. FileInputStream, BufferedInputStream etc.) does not matter here, but it is recommended to use an appropriate
	* type to avoid performance issues. 
	*
	* @param in InputStream containing Excel data
	*
	* @throws org.zuinnote.hadoop.office.format.common.parser.FormatNotUnderstoodException in case there are issues reading from the Excel file, e.g. wrong preplacedword or unknown format
	*
	*/
    @Override
    public void parse(InputStream in) throws FormatNotUnderstoodException {
        // read xls
        try {
            this.currentWorkbook = WorkbookFactory.create(in, this.hocr.getPreplacedword());
        } catch (EncryptedDoreplacedentException | IOException e) {
            LOG.error(e);
            throw new FormatNotUnderstoodException(e.toString());
        } finally {
            if (this.in != null) {
                try {
                    this.in.close();
                } catch (IOException e) {
                    LOG.error(e);
                }
            }
        }
        // check if signature should be verified
        if (this.hocr.getVerifySignature()) {
            LOG.info("Verifying signature of doreplacedent");
            if (!(this.currentWorkbook instanceof XSSFWorkbook)) {
                throw new FormatNotUnderstoodException("Can only verify signatures for files using the OOXML (.xlsx) format");
            } else {
                // 
                OPCPackage pgk = ((XSSFWorkbook) this.currentWorkbook).getPackage();
                SignatureConfig sic = new SignatureConfig();
                sic.setOpcPackage(pgk);
                SignatureInfo si = new SignatureInfo();
                si.setSignatureConfig(sic);
                if (!si.verifySignature()) {
                    throw new FormatNotUnderstoodException("Cannot verify signature of OOXML (.xlsx) file: " + this.hocr.getFileName());
                } else {
                    LOG.info("Successfully verifed first part signature of OXXML (.xlsx) file: " + this.hocr.getFileName());
                }
                Iterator<SignaturePart> spIter = si.getSignatureParts().iterator();
                while (spIter.hasNext()) {
                    SignaturePart currentSP = spIter.next();
                    if (!(currentSP.validate())) {
                        throw new FormatNotUnderstoodException("Could not validate all signature parts for file: " + this.hocr.getFileName());
                    } else {
                        X509Certificate currentCertificate = currentSP.getSigner();
                        try {
                            if ((this.hocr.getX509CertificateChain().size() > 0) && (!CertificateChainVerificationUtil.verifyCertificateChain(currentCertificate, this.hocr.getX509CertificateChain()))) {
                                throw new FormatNotUnderstoodException("Could not validate signature part for principal \"" + currentCertificate.getSubjectX500Principal().getName() + "\" : " + this.hocr.getFileName());
                            }
                        } catch (CertificateException | NoSuchAlgorithmException | NoSuchProviderException | InvalidAlgorithmParameterException e) {
                            LOG.error("Could not validate signature part for principal \"" + currentCertificate.getSubjectX500Principal().getName() + "\" : " + this.hocr.getFileName(), e);
                            throw new FormatNotUnderstoodException("Could not validate signature part for principal \"" + currentCertificate.getSubjectX500Principal().getName() + "\" : " + this.hocr.getFileName());
                        }
                    }
                }
                LOG.info("Successfully verifed all signatures of OXXML (.xlsx) file: " + this.hocr.getFileName());
            }
        }
        // formulaEvaluator
        this.formulaEvaluator = this.currentWorkbook.getCreationHelper().createFormulaEvaluator();
        // add the formulator evaluator of this file as well or we will see a strange Exception
        this.addedFormulaEvaluators.put(this.hocr.getFileName(), this.formulaEvaluator);
        this.formulaEvaluator.setIgnoreMissingWorkbooks(this.hocr.getIgnoreMissingLinkedWorkbooks());
        this.filtered = this.checkFiltered();
        this.currentRow = 0;
        if (this.sheets == null) {
            this.currentSheetName = this.currentWorkbook.getSheetAt(0).getSheetName();
        } else if (sheets.length < 1) {
            throw new FormatNotUnderstoodException("Error: no sheets selected");
        } else {
            this.currentSheetName = sheets[0];
        }
        // check skipping of additional lines
        this.currentRow += this.hocr.getSkipLines();
        // check header
        if (this.hocr.getReadHeader()) {
            LOG.debug("Reading header...");
            Object[] firstRow = this.getNext();
            if (firstRow != null) {
                this.header = new String[firstRow.length];
                for (int i = 0; i < firstRow.length; i++) {
                    if ((firstRow[i] != null) && (!"".equals(((SpreadSheetCellDAO) firstRow[i]).getFormattedValue()))) {
                        this.header[i] = ((SpreadSheetCellDAO) firstRow[i]).getFormattedValue();
                    }
                }
                this.header = MSExcelParser.sanitizeHeaders(this.header, this.hocr.getColumnNameRegex(), this.hocr.getColumnNameReplace());
            } else {
                this.header = new String[0];
            }
        }
    }

    /**
     * Adds a linked workbook that is referred from this workbook. If the filename is already in the list then it is not processed twice. Note that the inputStream is closed after parsing
     *
     * @param name fileName (without path) of the workbook
     * @param inputStream content of the linked workbook
     * @param preplacedword if doreplacedent is encrypted, null if not encrypted
     *
     * @return true if it has been added, false if it has been already added
     *
     * @throws org.zuinnote.hadoop.office.format.common.parser.FormatNotUnderstoodException in case there are issues reading from the Excel file
     */
    @Override
    public boolean addLinkedWorkbook(String name, InputStream inputStream, String preplacedword) throws FormatNotUnderstoodException {
        // check if already added
        if (this.addedFormulaEvaluators.containsKey(name)) {
            return false;
        }
        LOG.debug("Start adding  \"" + name + "\" to current workbook");
        // create new parser, select all sheets, no linkedworkbookpreplacedwords,no metadatafilter
        HadoopOfficeReadConfiguration linkedWBHOCR = new HadoopOfficeReadConfiguration();
        linkedWBHOCR.setLocale(this.hocr.getLocale());
        linkedWBHOCR.setSheets(null);
        linkedWBHOCR.setIgnoreMissingLinkedWorkbooks(this.hocr.getIgnoreMissingLinkedWorkbooks());
        linkedWBHOCR.setFileName(name);
        linkedWBHOCR.setPreplacedword(preplacedword);
        linkedWBHOCR.setMetaDataFilter(null);
        MSExcelParser linkedWBMSExcelParser = new MSExcelParser(linkedWBHOCR, null);
        // parse workbook
        linkedWBMSExcelParser.parse(inputStream);
        // add linked workbook
        this.addedWorkbooks.add(linkedWBMSExcelParser.getCurrentWorkbook());
        this.addedFormulaEvaluators.put(name, linkedWBMSExcelParser.getCurrentFormulaEvaluator());
        this.formulaEvaluator.setupReferencedWorkbooks(addedFormulaEvaluators);
        return true;
    }

    /**
     * Provides a list of filenames that contain workbooks that are linked with the current one. Officially supported only for new Excel format. For the old Excel format this is experimental
     *
     * @return list of filenames (without path) belonging to linked workbooks
     */
    @Override
    public List<String> getLinkedWorkbooks() {
        List<String> result = new ArrayList<>();
        if (this.currentWorkbook instanceof HSSFWorkbook) {
            result = getLinkedWorkbooksHSSF();
        } else if (this.currentWorkbook instanceof XSSFWorkbook) {
            // use its API
            for (ExternalLinksTable element : ((XSSFWorkbook) this.currentWorkbook).getExternalLinksTable()) {
                result.add(element.getLinkedFileName());
            }
        } else {
            LOG.warn("Cannot determine linked workbooks");
        }
        return result;
    }

    private List<String> getLinkedWorkbooksHSSF() {
        List<String> result = new ArrayList<>();
        try {
            // this is a hack to fetch linked workbooks in the Old Excel format
            // we use reflection to access private fields
            // might not work if internal structure of the clreplaced changes
            InternalWorkbook intWb = ((HSSFWorkbook) this.currentWorkbook).getInternalWorkbook();
            // method to fetch link table
            Method linkTableMethod = InternalWorkbook.clreplaced.getDeclaredMethod("getOrCreateLinkTable");
            linkTableMethod.setAccessible(true);
            Object linkTable = linkTableMethod.invoke(intWb);
            // method to fetch external book and sheet name
            Method externalBooksMethod = linkTable.getClreplaced().getDeclaredMethod("getExternalBookAndSheetName", int.clreplaced);
            externalBooksMethod.setAccessible(true);
            // now we need to browse through the table until we hit an array out of bounds
            int i = 0;
            try {
                while (i < MSExcelParser.MAX_LINKEDWB_OLDEXCEL) {
                    String[] externalBooks = (String[]) externalBooksMethod.invoke(linkTable, i++);
                    if ((externalBooks != null) && (externalBooks.length > 0)) {
                        result.add(externalBooks[0]);
                    }
                }
            } catch (java.lang.reflect.InvocationTargetException e) {
                if (!(e.getCause() instanceof java.lang.IndexOutOfBoundsException)) {
                    throw e;
                }
            }
        } catch (NoSuchMethodException nsme) {
            LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
            LOG.error(nsme);
        } catch (IllegalAccessException iae) {
            LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
            LOG.error(iae);
        } catch (InvocationTargetException ite) {
            LOG.error(COULD_NOT_RETRIEVE_LINKED_WORKBOOKS_FOR_OLD_EXCEL_FORMAT);
            LOG.error(ite);
        }
        return result;
    }

    /**
     * Check if doreplacedent matches to a metadata filter
     *
     * @return true, if doreplacedent matches metadata filter, false if not
     */
    @Override
    public boolean getFiltered() {
        return this.filtered;
    }

    /**
     * returns the current formula evaluator of the workbook
     *
     * @return Formulaevalutor of the workbook
     */
    public FormulaEvaluator getCurrentFormulaEvaluator() {
        return this.formulaEvaluator;
    }

    /*
	* returns the current workbook
	*
	* @return current workbook
	*
	*/
    public Workbook getCurrentWorkbook() {
        return this.currentWorkbook;
    }

    /* returns the current row number starting from 1
	*
	* @return current row number
	*
	*/
    @Override
    public long getCurrentRow() {
        return (long) this.currentRow;
    }

    /* returns the current sheet name
	*
	* @return current sheet name
	*
	*/
    @Override
    public String getCurrentSheetName() {
        return this.currentSheetName;
    }

    /*
	* Returns the next row in the set of sheets. If sheets==null then all available sheets are returned in the order as specified in the doreplacedent. If sheets contains specific sheets then rows of the specific sheets are returned in order of the sheets specified.
	*
	* @return column array of SpreadSheetCellDAO (may contain nulls if cell is without content), null if no further rows exist
	* 
	*/
    @Override
    public Object[] getNext() {
        SpreadSheetCellDAO[] result = null;
        // all sheets?
        if (this.sheets == null) {
            // go on with all sheets
            if (!nextAllSheets()) {
                return result;
            }
        } else {
            // go on with specified sheets
            if (!nextSpecificSheets()) {
                return result;
            }
        }
        // read row from the sheet currently to be processed
        Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet);
        Row rRow = rSheet.getRow(this.currentRow);
        if ((rRow == null) || (rRow.getLastCellNum() < 0)) {
            this.currentRow++;
            // emtpy row
            return new SpreadSheetCellDAO[0];
        }
        result = new SpreadSheetCellDAO[rRow.getLastCellNum()];
        for (int i = 0; i < rRow.getLastCellNum(); i++) {
            Cell currentCell = rRow.getCell(i);
            if (currentCell == null) {
                result[i] = null;
            } else {
                String formattedValue = useDataFormatter.formatCellValue(currentCell, this.formulaEvaluator);
                String formula = "";
                if (currentCell.getCellType() == CellType.FORMULA) {
                    formula = currentCell.getCellFormula();
                }
                Comment currentCellComment = currentCell.getCellComment();
                String comment = "";
                if (currentCellComment != null) {
                    comment = currentCellComment.getString().getString();
                }
                String address = currentCell.getAddress().toString();
                String sheetName = currentCell.getSheet().getSheetName();
                SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue, comment, formula, address, sheetName);
                result[i] = mySpreadSheetCellDAO;
            }
        }
        // increase rows
        this.currentRow++;
        return result;
    }

    private boolean nextAllSheets() {
        if (this.currentWorkbook == null) {
            return false;
        }
        while ((this.currentWorkbook.getSheetAt(this.currentSheet) != null) && (this.currentRow > this.currentWorkbook.getSheetAt(this.currentSheet).getLastRowNum())) {
            // end of row reached? => next sheet
            this.currentSheet++;
            this.currentRow = 0;
            // check if we need to skip lines
            if (this.hocr.getSkipLinesAllSheets()) {
                this.currentRow += this.hocr.getSkipLines();
            }
            // check if we need to skip header
            if (this.hocr.getIgnoreHeaderInAllSheets()) {
                this.currentRow++;
            }
            if (this.currentSheet >= this.currentWorkbook.getNumberOfSheets()) {
                // no more sheets available?
                return false;
            }
            this.currentSheetName = this.currentWorkbook.getSheetAt(this.currentSheet).getSheetName();
        }
        return true;
    }

    private boolean nextSpecificSheets() {
        if (this.currentWorkbook == null) {
            return false;
        }
        // go through sheets specified until one found
        while (this.sheetsIndex != this.sheets.length) {
            if ((this.currentWorkbook.getSheet(this.sheets[this.sheetsIndex]) == null)) {
                // log only if sheet not found
                LOG.warn("Sheet \"" + this.sheets[this.sheetsIndex] + "\" not found");
            } else {
                // sheet found, check number of rows
                if (this.currentRow <= this.currentWorkbook.getSheet(this.sheets[this.sheetsIndex]).getLastRowNum()) {
                    // we have a sheet where we still need to process rows
                    this.currentSheet = this.currentWorkbook.getSheetIndex(this.currentWorkbook.getSheet(this.sheets[this.sheetsIndex]));
                    this.currentSheetName = this.currentWorkbook.getSheetAt(this.currentSheet).getSheetName();
                    break;
                }
            }
            while ((this.currentWorkbook != null) && (this.currentWorkbook.getSheetAt(this.currentSheet) != null) && (this.currentRow > this.currentWorkbook.getSheet(this.sheets[this.sheetsIndex]).getLastRowNum())) {
                this.sheetsIndex++;
                if (this.sheetsIndex == this.sheets.length) {
                    LOG.info("No further sheets found");
                    break;
                }
                this.currentRow = 0;
                // check if we need to skip lines
                if (this.hocr.getSkipLinesAllSheets()) {
                    this.currentRow += this.hocr.getSkipLines();
                }
                // check if we need to skip header
                if (this.hocr.getIgnoreHeaderInAllSheets()) {
                    this.currentRow++;
                }
            }
        }
        if (this.sheetsIndex >= this.sheets.length) {
            // all sheets processed
            return false;
        }
        return true;
    }

    /**
     * Close parser and linked workbooks
     */
    @Override
    public void close() throws IOException {
        if (this.in != null) {
            in.close();
        }
        if (this.currentWorkbook != null) {
            LOG.debug("Closing current Workbook \"" + this.hocr.getFileName() + "\"");
            this.currentWorkbook.close();
        }
        for (Workbook addedWorkbook : this.addedWorkbooks) {
            addedWorkbook.close();
        }
    }

    /*
	* Check if doreplacedent matches the metadata filters
	*
	* @return true, if it matches, false if not
	*
	*/
    private boolean checkFiltered() {
        if ((this.hocr.getMetaDataFilter() == null) || (this.hocr.getMetaDataFilter().size() == 0)) {
            // if no filter is defined it does match by definition
            return true;
        }
        if (this.currentWorkbook instanceof XSSFWorkbook) {
            return checkFilteredXSSF();
        } else if (this.currentWorkbook instanceof HSSFWorkbook) {
            return checkFilteredHSSF();
        } else {
            LOG.error("Unknown workbook format. Cannot check if doreplacedent matches metadata filter");
            return false;
        }
    }

    /*
	* Check if doreplacedent matches the metadata filters for XSSF doreplacedents. 
	*
	* @return true, if it matches, false if not
	*
	*/
    private boolean checkFilteredXSSF() {
        XSSFWorkbook currentXSSFWorkbook = (XSSFWorkbook) this.currentWorkbook;
        POIXMLProperties props = currentXSSFWorkbook.getProperties();
        SimpleDateFormat format = new SimpleDateFormat(MSExcelParser.DATE_FORMAT);
        // check for each defined property
        // check if we need to match all
        boolean matchAll = true;
        boolean matchFull = true;
        boolean matchOnce = false;
        if (this.hocr.getMetaDataFilter().get(MATCH_ALL) != null) {
            if ("true".equalsIgnoreCase(this.hocr.getMetaDataFilter().get(MATCH_ALL))) {
                matchAll = true;
                LOG.info("matching all metadata properties");
            } else if ("false".equalsIgnoreCase(this.hocr.getMetaDataFilter().get(MATCH_ALL))) {
                matchAll = false;
                LOG.info("matching at least one metadata property");
            } else {
                LOG.error("Metadata property matchAll not defined correctly. replaceduming that at only least one attribute needs to match");
            }
        }
        // check core properties
        String corePropertyName;
        POIXMLProperties.CoreProperties coreProp = props.getCoreProperties();
        corePropertyName = "category";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getCategory();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "contentstatus";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getContentStatus();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "contenttype";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getContentType();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "created";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date corePropStr = coreProp.getCreated();
            if ((corePropStr != null) && (format.format(corePropStr).matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + format.format(corePropStr) + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
                LOG.debug(corePropertyName);
            }
        }
        corePropertyName = "creator";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getCreator();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "description";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getDescription();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "identifier";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getIdentifier();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "keywords";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getKeywords();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "lastmodifiedbyuser";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getLastModifiedByUser();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "lastprinted";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date corePropStr = coreProp.getLastPrinted();
            if ((corePropStr != null) && (format.format(corePropStr).matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + format.format(corePropStr) + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
                LOG.debug(corePropertyName);
            }
        }
        corePropertyName = "modified";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date corePropStr = coreProp.getModified();
            if ((corePropStr != null) && (format.format(corePropStr).matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
                LOG.debug(corePropertyName);
            }
        }
        corePropertyName = "revision";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getRevision();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "subject";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getSubject();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        corePropertyName = "replacedle";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String corePropStr = coreProp.getreplacedle();
            if ((corePropStr != null) && (corePropStr.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
                LOG.debug(NOT_MATCHING + corePropStr + ":" + this.hocr.getMetaDataFilter().get(corePropertyName));
            }
        }
        // check for custom properties
        POIXMLProperties.CustomProperties custProp = props.getCustomProperties();
        for (Map.Entry<String, String> entry : this.hocr.getMetaDataFilter().entrySet()) {
            if (entry.getKey().startsWith("custom.")) {
                String strippedKey = entry.getKey().substring("custom.".length());
                if (strippedKey.length() > 0) {
                    String valueMatch = entry.getValue();
                    if (valueMatch != null) {
                        if ((custProp.getProperty(strippedKey) != null) && (custProp.getProperty(strippedKey).getName() != null) && (custProp.getProperty(strippedKey).getLpwstr().matches(valueMatch))) {
                            matchOnce = true;
                        } else {
                            matchFull = false;
                        }
                    }
                }
            }
        }
        if (!(matchAll)) {
            return matchOnce;
        } else {
            return matchFull;
        }
    }

    /*
	* Check if doreplacedent matches the metadata filters for HSSF doreplacedents
	*
	* @return true, if it matches, false if not
	*
	*/
    private boolean checkFilteredHSSF() {
        HSSFWorkbook currentHSSFWorkbook = (HSSFWorkbook) this.currentWorkbook;
        SummaryInformation summaryInfo = currentHSSFWorkbook.getSummaryInformation();
        boolean matchAll = true;
        boolean matchFull = true;
        boolean matchOnce = false;
        if (this.hocr.getMetaDataFilter().get(MATCH_ALL) != null) {
            if ("true".equalsIgnoreCase(this.hocr.getMetaDataFilter().get(MATCH_ALL))) {
                matchAll = true;
            } else if ("false".equalsIgnoreCase(this.hocr.getMetaDataFilter().get(MATCH_ALL))) {
                matchAll = false;
            } else {
                LOG.error("Metadata property matchAll not defined correctly. replaceduming that at only least one attribute needs to match");
            }
        }
        String corePropertyName;
        corePropertyName = "applicationname";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getApplicationName();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "author";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getAuthor();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "charcount";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            int coreProp = summaryInfo.getCharCount();
            if (String.valueOf(coreProp).matches(this.hocr.getMetaDataFilter().get(corePropertyName))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "comments";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getComments();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "createddatetime";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date coreProp = summaryInfo.getCreateDateTime();
            if ((coreProp != null) && (coreProp.toString().matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "edittime";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            long coreProp = summaryInfo.getEditTime();
            if (String.valueOf(coreProp).matches(this.hocr.getMetaDataFilter().get(corePropertyName))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "keywords";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getKeywords();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "lastauthor";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getLastAuthor();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "lastprinted";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date coreProp = summaryInfo.getLastPrinted();
            if ((coreProp != null) && (coreProp.toString().matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "lastsavedatetime";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            Date coreProp = summaryInfo.getLastSaveDateTime();
            if ((coreProp != null) && (coreProp.toString().matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "pagecount";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            int coreProp = summaryInfo.getPageCount();
            if (String.valueOf(coreProp).matches(this.hocr.getMetaDataFilter().get(corePropertyName))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "revnumber";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getRevNumber();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "security";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            int coreProp = summaryInfo.getSecurity();
            if (String.valueOf(coreProp).matches(this.hocr.getMetaDataFilter().get(corePropertyName))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "subject";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getSubject();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "template";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getTemplate();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "replacedle";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            String coreProp = summaryInfo.getreplacedle();
            if ((coreProp != null) && (coreProp.matches(this.hocr.getMetaDataFilter().get(corePropertyName)))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        corePropertyName = "wordcount";
        if (this.hocr.getMetaDataFilter().get(corePropertyName) != null) {
            int coreProp = summaryInfo.getWordCount();
            if (String.valueOf(coreProp).matches(this.hocr.getMetaDataFilter().get(corePropertyName))) {
                matchOnce = true;
            } else {
                matchFull = false;
            }
        }
        if (!(matchAll)) {
            return matchOnce;
        } else {
            return matchFull;
        }
    }

    @Override
    public void setCurrentRow(long row) {
        this.currentRow = (int) row;
    }

    @Override
    public void setCurrentSheet(long sheet) {
        this.currentSheet = (int) sheet;
    }

    @Override
    public long getCurrentSheet() {
        return this.currentSheet;
    }

    @Override
    public String[] getHeader() {
        return this.header;
    }

    /**
     * Sanitize headers
     * 1) Replace empty/null column names by "Col" + column number
     * 2) Replace duplicate column names by column name + increasing number
     * 3) Replace column names based on regular expressions with another string (useful in case your Big Data platform does not support certain characters in the column String)
     *
     * @param headers original headers to sanitize
     * @param regex regex to be searched in each column name (all occurrences, see String.replaceAll)
     * @param replace String to replace the matched regular expression
     * @return sanitized headers
     */
    public static String[] sanitizeHeaders(String[] headers, String regex, String replace) {
        String[] result = new String[headers.length];
        // for detecting duplicates
        HashMap<String, Integer> headerHashMap = new HashMap<>();
        for (int i = 0; i < headers.length; i++) {
            String currentColumn = headers[i];
            // if column name is empty create artificial column name
            if ((currentColumn == null) || "".equals(currentColumn)) {
                currentColumn = HadoopOfficeReadConfiguration.PREFIX_UNKNOWN_COL + i;
            }
            // apply regex to column name
            if ((regex != null) && (!"".equals(regex))) {
                currentColumn = currentColumn.replaceAll(regex, replace);
            }
            // check if column name already exist
            Integer existingEntry = headerHashMap.get(currentColumn);
            if (existingEntry == null) {
                existingEntry = 1;
            } else {
                existingEntry += 1;
                currentColumn += existingEntry;
            }
            result[i] = currentColumn;
            headerHashMap.put(currentColumn, existingEntry);
        }
        return result;
    }
}

19 Source : ExcelUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 读取excel数据
 * @param path
 */
public static ArrayList<Map<String, String>> readExcelToObj(String path) {
    Workbook wb = null;
    ArrayList<Map<String, String>> result = null;
    try {
        wb = WorkbookFactory.create(new File(path));
        result = readExcel(wb, 0, 2, 0);
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return result;
}

19 Source : ExcelToHtmlServer.java
with Apache License 2.0
from zhangdaiscott

/**
 * Excel转换成Html 服务
 *
 * @author JueYue
 * @date 2015年5月10日 上午11:41:15
 */
public clreplaced ExcelToHtmlServer {

    private Workbook wb;

    private int sheetNum;

    private int cssRandom;

    /* 是不是完成界面 */
    private boolean completeHTML;

    private Formatter out;

    /* 已经完成范围处理 */
    private boolean gotBounds;

    private int firstColumn;

    private int endColumn;

    private static final String COL_HEAD_CLreplaced = "colHeader";

    // private static final String ROW_HEAD_CLreplaced = "rowHeader";
    private static final String DEFAULTS_CLreplaced = "excelDefaults";

    public ExcelToHtmlServer(Workbook wb, boolean completeHTML, int sheetNum) {
        this.wb = wb;
        this.completeHTML = completeHTML;
        this.sheetNum = sheetNum;
        cssRandom = (int) Math.ceil(Math.random() * 1000);
    }

    public String printPage() {
        try {
            ensureOut();
            if (completeHTML) {
                out.format("<!DOCTYPE HTML>%n");
                out.format("<html>%n");
                out.format("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">%n");
                out.format("<head>%n");
            }
            new StylerHelper(wb, out, sheetNum, cssRandom);
            if (completeHTML) {
                out.format("</head>%n");
                out.format("<body>%n");
            }
            print();
            if (completeHTML) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
            return out.toString();
        } finally {
            if (out != null)
                out.close();
        }
    }

    private void print() {
        printSheets();
    }

    private void ensureOut() {
        if (out == null)
            out = new Formatter(new StringBuilder());
    }

    private void printSheets() {
        Sheet sheet = wb.getSheetAt(sheetNum);
        printSheet(sheet);
    }

    private void printSheet(Sheet sheet) {
        out.format("<table clreplaced='%s' width='%s'>%n", DEFAULTS_CLreplaced, getTableWidth(sheet));
        printCols(sheet);
        printSheetContent(sheet);
        out.format("</table>%n");
    }

    private void printCols(Sheet sheet) {
        // out.format("<col/>%n");
        ensureColumnBounds(sheet);
        for (int i = firstColumn; i < endColumn; i++) {
            out.format("<col style='width:%spx;' />%n", sheet.getColumnWidth(i) / 32);
        }
    }

    private int getTableWidth(Sheet sheet) {
        ensureColumnBounds(sheet);
        int width = 0;
        for (int i = firstColumn; i < endColumn; i++) {
            width = width + (sheet.getColumnWidth(i) / 32);
        }
        return width;
    }

    private void ensureColumnBounds(Sheet sheet) {
        if (gotBounds)
            return;
        Iterator<Row> iter = sheet.rowIterator();
        firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
        endColumn = 0;
        while (iter.hasNext()) {
            Row row = iter.next();
            short firstCell = row.getFirstCellNum();
            if (firstCell >= 0) {
                firstColumn = Math.min(firstColumn, firstCell);
                endColumn = Math.max(endColumn, row.getLastCellNum());
            }
        }
        gotBounds = true;
    }

    @SuppressWarnings("unused")
    private /**
     * 本来是用来生成 A,B 那个列名称的*
     */
    void printColumnHeads(Sheet sheet) {
        out.format("<thead>%n");
        out.format("  <tr clreplaced=%s>%n", COL_HEAD_CLreplaced);
        out.format("    <th clreplaced=%s>◊</th>%n", COL_HEAD_CLreplaced);
        StringBuilder colName = new StringBuilder();
        for (int i = firstColumn; i < endColumn; i++) {
            colName.setLength(0);
            int cnum = i;
            do {
                colName.insert(0, (char) ('A' + cnum % 26));
                cnum /= 26;
            } while (cnum > 0);
            out.format("    <th clreplaced=%s>%s</th>%n", COL_HEAD_CLreplaced, colName);
        }
        out.format("  </tr>%n");
        out.format("</thead>%n");
    }

    private void printSheetContent(Sheet sheet) {
        // printColumnHeads(sheet);
        MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
        CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
        out.format("<tbody>%n");
        Iterator<Row> rows = sheet.rowIterator();
        int rowIndex = 1;
        while (rows.hasNext()) {
            Row row = rows.next();
            out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
            // out.format("    <td clreplaced='%s'>%d</td>%n", ROW_HEAD_CLreplaced,
            // row.getRowNum() + 1);
            for (int i = firstColumn; i < endColumn; i++) {
                if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
                    String content = " ";
                    CellStyle style = null;
                    if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                        Cell cell = row.getCell(i);
                        if (cell != null) {
                            style = cell.getCellStyle();
                            content = cellValueHelper.getHtmlValue(cell);
                        }
                    }
                    if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
                        Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
                        out.format("    <td rowspan='%s' colspan='%s' clreplaced='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
                    } else {
                        out.format("    <td clreplaced='%s'>%s</td>%n", styleName(style), content);
                    }
                }
            }
            out.format("  </tr>%n");
            rowIndex++;
        }
        out.format("</tbody>%n");
    }

    private String styleName(CellStyle style) {
        if (style == null)
            return "";
        return String.format("style_%02x_%s font_%s_%s", style.getIndex(), cssRandom, style.getFontIndex(), cssRandom);
    }
}

19 Source : ExcelExportStylerBorderImpl.java
with Apache License 2.0
from zhangdaiscott

@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
    return isWarp ? stringNoneWrapStyle : stringNoneStyle;
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from zhangdaiscott

/**
 * 抽象接口提供两个公共方法
 *
 * @author JueYue
 * @date 2015年1月9日 下午5:48:55
 */
public abstract clreplaced AbstractExcelExportStyler implements IExcelExportStyler {

    // 单行
    protected CellStyle stringNoneStyle;

    protected CellStyle stringNoneWrapStyle;

    // 间隔行
    protected CellStyle stringSeptailStyle;

    protected CellStyle stringSeptailWrapStyle;

    protected Workbook workbook;

    protected static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");

    protected void createStyles(Workbook workbook) {
        this.stringNoneStyle = stringNoneStyle(workbook, false);
        this.stringNoneWrapStyle = stringNoneStyle(workbook, true);
        this.stringSeptailStyle = stringSeptailStyle(workbook, false);
        this.stringSeptailWrapStyle = stringSeptailStyle(workbook, true);
        this.workbook = workbook;
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEnreplacedy enreplacedy) {
        if (noneStyler && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringNoneWrapStyle;
        }
        if (noneStyler) {
            return stringNoneStyle;
        }
        if (noneStyler == false && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringSeptailWrapStyle;
        }
        return stringSeptailStyle;
    }

    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        return null;
    }

    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        return null;
    }
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from zhangdaiscott

public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
    return null;
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from zhangdaiscott

public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
    return null;
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from zhangdaiscott

protected void createStyles(Workbook workbook) {
    this.stringNoneStyle = stringNoneStyle(workbook, false);
    this.stringNoneWrapStyle = stringNoneStyle(workbook, true);
    this.stringSeptailStyle = stringSeptailStyle(workbook, false);
    this.stringSeptailWrapStyle = stringSeptailStyle(workbook, true);
    this.workbook = workbook;
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 转换成为完整界面
 *
 * @param wb
 *            Excel
 * @param sheetNum
 *            sheetNum
 * @return
 */
public static String toAllHtml(Workbook wb) {
    return new ExcelToHtmlServer(wb, true, 0).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 转换成为Table
 *
 * @param wb
 *            Excel
 * @return
 */
public static String toTableHtml(Workbook wb) {
    return new ExcelToHtmlServer(wb, false, 0).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 转换成为完整界面
 *
 * @param wb
 *            Excel
 * @param sheetNum
 *            sheetNum
 * @return
 */
public static String toAllHtml(Workbook wb, int sheetNum) {
    return new ExcelToHtmlServer(wb, true, sheetNum).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 转换成为Table
 *
 * @param wb
 *            Excel
 * @param sheetNum
 *            sheetNum
 * @return
 */
public static String toTableHtml(Workbook wb, int sheetNum) {
    return new ExcelToHtmlServer(wb, false, sheetNum).printPage();
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * @param enreplacedy
 *            表格标题属性
 * @param pojoClreplaced
 *            Excel对象Clreplaced
 * @param dataSet
 *            Excel对象数据List
 */
public static Workbook exportExcel(ExportParams enreplacedy, Clreplaced<?> pojoClreplaced, Collection<?> dataSet) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(enreplacedy.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new ExcelExportServer().createSheet(workbook, enreplacedy, pojoClreplaced, dataSet);
    return workbook;
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 根据Map创建对应的Excel
 *
 * @param enreplacedy
 *            表格标题属性
 * @param pojoClreplaced
 *            Excel对象Clreplaced
 * @param dataSet
 *            Excel对象数据List
 */
public static Workbook exportExcel(ExportParams enreplacedy, List<ExcelExportEnreplacedy> enreplacedyList, Collection<? extends Map<?, ?>> dataSet) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(enreplacedy.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new ExcelExportServer().createSheetForMap(workbook, enreplacedy, enreplacedyList, dataSet);
    return workbook;
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 一个excel 创建多个sheet
 *
 * @param list
 *            多个Map key replacedle 对应表格replacedle key enreplacedy 对应表格对应实体 key data
 *            Collection 数据
 * @return
 */
public static Workbook exportExcel(List<Map<String, Object>> list, String type) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(type)) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }
    for (Map<String, Object> map : list) {
        ExcelExportServer server = new ExcelExportServer();
        server.createSheet(workbook, (ExportParams) map.get("replacedle"), (Clreplaced<?>) map.get("enreplacedy"), (Collection<?>) map.get("data"));
    }
    return workbook;
}

19 Source : ExcelImportResult.java
with Apache License 2.0
from zhangdaiscott

public void setWorkbook(Workbook workbook) {
    this.workbook = workbook;
}

19 Source : ExcelToHtmlServer.java
with Apache License 2.0
from zhangdaiscott

/**
 * Excel转换成Html 服务
 *
 * @author JEECG
 * @date 2015年5月10日 上午11:41:15
 */
public clreplaced ExcelToHtmlServer {

    private Workbook wb;

    private int sheetNum;

    private int cssRandom;

    /* 是不是完成界面 */
    private boolean completeHTML;

    private Formatter out;

    /* 已经完成范围处理 */
    private boolean gotBounds;

    private int firstColumn;

    private int endColumn;

    private static final String COL_HEAD_CLreplaced = "colHeader";

    // private static final String ROW_HEAD_CLreplaced = "rowHeader";
    private static final String DEFAULTS_CLreplaced = "excelDefaults";

    public ExcelToHtmlServer(Workbook wb, boolean completeHTML, int sheetNum) {
        this.wb = wb;
        this.completeHTML = completeHTML;
        this.sheetNum = sheetNum;
        cssRandom = (int) Math.ceil(Math.random() * 1000);
    }

    public String printPage() {
        try {
            ensureOut();
            if (completeHTML) {
                out.format("<!DOCTYPE HTML>%n");
                out.format("<html>%n");
                out.format("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">%n");
                out.format("<head>%n");
            }
            new StylerHelper(wb, out, sheetNum, cssRandom);
            if (completeHTML) {
                out.format("</head>%n");
                out.format("<body>%n");
            }
            print();
            if (completeHTML) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
            return out.toString();
        } finally {
            if (out != null)
                out.close();
        }
    }

    private void print() {
        printSheets();
    }

    private void ensureOut() {
        if (out == null)
            out = new Formatter(new StringBuilder());
    }

    private void printSheets() {
        Sheet sheet = wb.getSheetAt(sheetNum);
        printSheet(sheet);
    }

    private void printSheet(Sheet sheet) {
        out.format("<table clreplaced='%s' width='%s'>%n", DEFAULTS_CLreplaced, getTableWidth(sheet));
        printCols(sheet);
        printSheetContent(sheet);
        out.format("</table>%n");
    }

    private void printCols(Sheet sheet) {
        // out.format("<col/>%n");
        ensureColumnBounds(sheet);
        for (int i = firstColumn; i < endColumn; i++) {
            out.format("<col style='width:%spx;' />%n", sheet.getColumnWidth(i) / 32);
        }
    }

    private int getTableWidth(Sheet sheet) {
        ensureColumnBounds(sheet);
        int width = 0;
        for (int i = firstColumn; i < endColumn; i++) {
            width = width + (sheet.getColumnWidth(i) / 32);
        }
        return width;
    }

    private void ensureColumnBounds(Sheet sheet) {
        if (gotBounds)
            return;
        Iterator<Row> iter = sheet.rowIterator();
        firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
        endColumn = 0;
        while (iter.hasNext()) {
            Row row = iter.next();
            short firstCell = row.getFirstCellNum();
            if (firstCell >= 0) {
                firstColumn = Math.min(firstColumn, firstCell);
                endColumn = Math.max(endColumn, row.getLastCellNum());
            }
        }
        gotBounds = true;
    }

    @SuppressWarnings("unused")
    private /**
     * 本来是用来生成 A,B 那个列名称的*
     */
    void printColumnHeads(Sheet sheet) {
        out.format("<thead>%n");
        out.format("  <tr clreplaced=%s>%n", COL_HEAD_CLreplaced);
        out.format("    <th clreplaced=%s>◊</th>%n", COL_HEAD_CLreplaced);
        StringBuilder colName = new StringBuilder();
        for (int i = firstColumn; i < endColumn; i++) {
            colName.setLength(0);
            int cnum = i;
            do {
                colName.insert(0, (char) ('A' + cnum % 26));
                cnum /= 26;
            } while (cnum > 0);
            out.format("    <th clreplaced=%s>%s</th>%n", COL_HEAD_CLreplaced, colName);
        }
        out.format("  </tr>%n");
        out.format("</thead>%n");
    }

    private void printSheetContent(Sheet sheet) {
        // printColumnHeads(sheet);
        MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
        CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
        out.format("<tbody>%n");
        Iterator<Row> rows = sheet.rowIterator();
        int rowIndex = 1;
        while (rows.hasNext()) {
            Row row = rows.next();
            out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
            // out.format("    <td clreplaced='%s'>%d</td>%n", ROW_HEAD_CLreplaced,
            // row.getRowNum() + 1);
            for (int i = firstColumn; i < endColumn; i++) {
                if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
                    String content = " ";
                    CellStyle style = null;
                    if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                        Cell cell = row.getCell(i);
                        if (cell != null) {
                            style = cell.getCellStyle();
                            content = cellValueHelper.getHtmlValue(cell);
                        }
                    }
                    if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
                        Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
                        out.format("    <td rowspan='%s' colspan='%s' clreplaced='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
                    } else {
                        out.format("    <td clreplaced='%s'>%s</td>%n", styleName(style), content);
                    }
                }
            }
            out.format("  </tr>%n");
            rowIndex++;
        }
        out.format("</tbody>%n");
    }

    private String styleName(CellStyle style) {
        if (style == null)
            return "";
        return String.format("style_%02x_%s font_%s_%s", style.getIndex(), cssRandom, style.getFontIndex(), cssRandom);
    }
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from zhangdaiscott

/**
 * 抽象接口提供两个公共方法
 *
 * @author JEECG
 * @date 2015年1月9日 下午5:48:55
 */
public abstract clreplaced AbstractExcelExportStyler implements IExcelExportStyler {

    // 单行
    protected CellStyle stringNoneStyle;

    protected CellStyle stringNoneWrapStyle;

    // 间隔行
    protected CellStyle stringSeptailStyle;

    protected CellStyle stringSeptailWrapStyle;

    protected Workbook workbook;

    protected static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");

    protected void createStyles(Workbook workbook) {
        this.stringNoneStyle = stringNoneStyle(workbook, false);
        this.stringNoneWrapStyle = stringNoneStyle(workbook, true);
        this.stringSeptailStyle = stringSeptailStyle(workbook, false);
        this.stringSeptailWrapStyle = stringSeptailStyle(workbook, true);
        this.workbook = workbook;
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEnreplacedy enreplacedy) {
        if (noneStyler && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringNoneWrapStyle;
        }
        if (noneStyler) {
            return stringNoneStyle;
        }
        if (noneStyler == false && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringSeptailWrapStyle;
        }
        return stringSeptailStyle;
    }

    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        return null;
    }

    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        return null;
    }
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

// ---update-begin-----autor:scott------date:20191016-------for:导出字段支持自定义--------
/**
 * 根据Enreplacedy创建对应的Excel
 *
 * @param enreplacedy
 *            表格标题属性
 * @param pojoClreplaced
 *            Excel对象Clreplaced
 * @param dataSet
 *            Excel对象数据List
 * @param exportFields
 * 	          自定义导出Excel字段数组
 */
public static Workbook exportExcel(ExportParams enreplacedy, Clreplaced<?> pojoClreplaced, Collection<?> dataSet, String[] exportFields) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(enreplacedy.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new ExcelExportServer().createSheet(workbook, enreplacedy, pojoClreplaced, dataSet, exportFields);
    return workbook;
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

/**
 * 一个excel 创建多个sheet
 *
 * @param list
 *            多个Map key replacedle 对应表格replacedle key enreplacedy 对应表格对应实体 key data
 *            Collection 数据
 * @return
 */
public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(type)) {
        workbook = new HSSFWorkbook();
    } else {
        workbook = new XSSFWorkbook();
    }
    for (Map<String, Object> map : list) {
        ExcelExportServer server = new ExcelExportServer();
        server.createSheet(workbook, (ExportParams) map.get("replacedle"), (Clreplaced<?>) map.get("enreplacedy"), (Collection<?>) map.get("data"), null);
    }
    return workbook;
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from zhangdaiscott

// ---update-end-----autor:scott------date:20191016-------for:导出字段支持自定义--------
/**
 * 根据Enreplacedy创建对应的Excel
 *
 * @param enreplacedy
 *            表格标题属性
 * @param pojoClreplaced
 *            Excel对象Clreplaced
 * @param dataSet
 *            Excel对象数据List
 */
public static Workbook exportExcel(ExportParams enreplacedy, Clreplaced<?> pojoClreplaced, Collection<?> dataSet) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(enreplacedy.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new ExcelExportServer().createSheet(workbook, enreplacedy, pojoClreplaced, dataSet, null);
    return workbook;
}

19 Source : SheetWriterTest.java
with MIT License
from zerouwar

@Test
public void test() throws IOException {
    Workbook workbook = new XSSFWorkbook();
    String rootPath = this.getClreplaced().getClreplacedLoader().getResource("").getPath();
    FileOutputStream os = new FileOutputStream(rootPath + "/sheetWriter.xlsx");
    // DefaultSheetWriter writer = new DefaultSheetWriter(new XmlConfigFactory(this.getClreplaced().getClreplacedLoader().getResourcereplacedtream("applicants.xml")));
    DefaultSheetWriter writer = new DefaultSheetWriter(config);
    writer.write(workbook.createSheet(), applicantsList);
    workbook.write(os);
}

19 Source : DefaultExcelReader.java
with MIT License
from zerouwar

/**
 * @author chenhuanming
 * Created at 2019-01-09
 */
public clreplaced DefaultExcelReader<T> implements ExcelReader<T> {

    protected Workbook workbook;

    public DefaultExcelReader(Workbook workbook) {
        if (workbook == null) {
            throw new NullPointerException("workbook can not be null");
        }
        this.workbook = workbook;
    }

    @Override
    public SheetReader<T> get(int index, Config config, CellPosition startPoint) {
        return new DefaultSheetReader<>(workbook.getSheetAt(index), config, startPoint);
    }

    @Override
    public SheetReader<T> get(String sheetName, Config config, CellPosition startPoint) throws SheetNotFoundException {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                return get(i, config, startPoint);
            }
        }
        throw new SheetNotFoundException("not found:" + sheetName);
    }
}

19 Source : Octopus.java
with MIT License
from zerouwar

/**
 * read data from sheet at the specified position in excel
 *
 * @param is            excel file
 * @param index         position,starting from 0
 * @param config        get config through @{{@link ConfigFactory}}
 * @param startPosition where to start read,starting from 0
 * @param <T>           clreplaced type of data you want
 * @return data
 * @throws IOException                if an error occurs while reading the data
 * @throws InvalidFormatException     if the contents of the file cannot be parsed into a {@link Workbook}
 * @throws EncryptedDoreplacedentException If the workbook given is preplacedword protected
 */
public static <T> SheetReader<T> readOneSheet(InputStream is, int index, Config config, CellPosition startPosition) throws IOException, InvalidFormatException, EncryptedDoreplacedentException {
    Workbook workbook = WorkbookFactory.create(is);
    return new DefaultExcelReader<T>(workbook).get(index, config, startPosition);
}

19 Source : Octopus.java
with MIT License
from zerouwar

public static <T> SheetReader<CheckedData<T>> readBySheetNameWithValidation(InputStream is, String sheetName, Config config, CellPosition startPosition) throws IOException, InvalidFormatException, EncryptedDoreplacedentException, SheetNotFoundException {
    Workbook workbook = WorkbookFactory.create(is);
    return new CheckedExcelReader<T>(workbook).get(sheetName, config, startPosition);
}

19 Source : Octopus.java
with MIT License
from zerouwar

public static <T> SheetReader<CheckedData<T>> readOneSheetWithValidation(InputStream is, int index, Config config, CellPosition startPosition) throws IOException, InvalidFormatException, EncryptedDoreplacedentException {
    Workbook workbook = WorkbookFactory.create(is);
    return new CheckedExcelReader<T>(workbook).get(index, config, startPosition);
}

19 Source : Octopus.java
with MIT License
from zerouwar

/**
 * read data from sheet by nodeName in excel
 *
 * @param is            excel file
 * @param sheetName     nodeName of sheet in the excel
 * @param config        get config through @{{@link ConfigFactory}}
 * @param startPosition where to start read,starting from 0
 * @param <T>           clreplaced type of data you want
 * @return data
 * @throws IOException                if an error occurs while reading the data
 * @throws InvalidFormatException     if the contents of the file cannot be parsed into a {@link Workbook}
 * @throws EncryptedDoreplacedentException If the workbook given is preplacedword protected
 * @throws SheetNotFoundException     when none of sheets'nodeName is <code>sheetName</code>
 */
public static <T> SheetReader<T> readBySheetName(InputStream is, String sheetName, Config config, CellPosition startPosition) throws IOException, InvalidFormatException, EncryptedDoreplacedentException, SheetNotFoundException {
    Workbook workbook = WorkbookFactory.create(is);
    return new DefaultExcelReader<T>(workbook).get(sheetName, config, startPosition);
}

19 Source : WorkbookContext.java
with MIT License
from zerouwar

/**
 * context of one workbook
 * manage limited resources of workbook,such as color,fieldCellStyle
 *
 * @author chenhuanming
 * Created at 2019-02-18
 */
public clreplaced WorkbookContext {

    private Workbook book;

    private Map<Field, CellStyle> cellStyleMap;

    private Map<Field, CellStyle> headerStyleMap;

    public WorkbookContext(Workbook book) {
        this.book = book;
        this.cellStyleMap = new HashMap<>();
        this.headerStyleMap = new HashMap<>();
    }

    public CellStyle getCellStyle(Field field) {
        CellStyle style = cellStyleMap.get(field);
        if (style == null) {
            style = book.createCellStyle();
            Font font = book.createFont();
            FieldCellStyle fieldCellStyle = field.getFieldCellStyle();
            font.setFontHeightInPoints(fieldCellStyle.getFontSize());
            font.setBold(fieldCellStyle.isBold());
            ColorUtils.setColor(book, font, fieldCellStyle.getColor());
            style.setFont(font);
            ColorUtils.setForegroundColor(book, style, fieldCellStyle.getForegroundColor());
            setStyleBorder(style, fieldCellStyle.getBorder());
            ColorUtils.setBorderColor(book, style, fieldCellStyle.getBorderColor());
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyleMap.put(field, style);
            return style;
        }
        return style;
    }

    public CellStyle getHeaderStyle(Field field) {
        CellStyle style = headerStyleMap.get(field);
        if (style == null) {
            style = book.createCellStyle();
            Font font = book.createFont();
            FieldCellStyle fieldCellStyle = field.getHeaderFieldCellStyle();
            font.setFontHeightInPoints(fieldCellStyle.getFontSize());
            font.setBold(fieldCellStyle.isBold());
            ColorUtils.setColor(book, font, fieldCellStyle.getColor());
            style.setFont(font);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            ColorUtils.setForegroundColor(book, style, fieldCellStyle.getForegroundColor());
            setStyleBorder(style, fieldCellStyle.getBorder());
            ColorUtils.setBorderColor(book, style, fieldCellStyle.getBorderColor());
            cellStyleMap.put(field, style);
            return style;
        }
        return style;
    }

    private void setStyleBorder(CellStyle style, BorderStyle[] border) {
        if (border != null) {
            if (border[0] != null) {
                style.setBorderTop(border[0]);
            }
            if (border[1] != null) {
                style.setBorderRight(border[1]);
            }
            if (border[2] != null) {
                style.setBorderBottom(border[2]);
            }
            if (border[3] != null) {
                style.setBorderLeft(border[3]);
            }
        }
    }
}

19 Source : ExcelToHtmlServer.java
with Apache License 2.0
from xiaolanglang

/**
 * Excel转换成Html 服务
 * @author JueYue
 * @date 2015年5月10日 上午11:41:15
 */
public clreplaced ExcelToHtmlServer {

    private Workbook wb;

    private int sheetNum;

    private int cssRandom;

    /*是不是完成界面*/
    private boolean completeHTML;

    private Formatter out;

    /*已经完成范围处理*/
    private boolean gotBounds;

    private int firstColumn;

    private int endColumn;

    private static final String COL_HEAD_CLreplaced = "colHeader";

    // private static final String ROW_HEAD_CLreplaced = "rowHeader";
    private static final String DEFAULTS_CLreplaced = "excelDefaults";

    public ExcelToHtmlServer(Workbook wb, boolean completeHTML, int sheetNum) {
        this.wb = wb;
        this.completeHTML = completeHTML;
        this.sheetNum = sheetNum;
        cssRandom = (int) Math.ceil(Math.random() * 1000);
    }

    public String printPage() {
        try {
            ensureOut();
            if (completeHTML) {
                out.format("<!DOCTYPE HTML>%n");
                out.format("<html>%n");
                out.format("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=UTF-8\">%n");
                out.format("<head>%n");
            }
            new StylerHelper(wb, out, sheetNum, cssRandom);
            if (completeHTML) {
                out.format("</head>%n");
                out.format("<body>%n");
            }
            print();
            if (completeHTML) {
                out.format("</body>%n");
                out.format("</html>%n");
            }
            return out.toString();
        } finally {
            if (out != null)
                out.close();
        }
    }

    private void print() {
        printSheets();
    }

    private void ensureOut() {
        if (out == null)
            out = new Formatter(new StringBuilder());
    }

    private void printSheets() {
        Sheet sheet = wb.getSheetAt(sheetNum);
        printSheet(sheet);
    }

    private void printSheet(Sheet sheet) {
        out.format("<table clreplaced='%s' width='%s'>%n", DEFAULTS_CLreplaced, getTableWidth(sheet));
        printCols(sheet);
        printSheetContent(sheet);
        out.format("</table>%n");
    }

    private void printCols(Sheet sheet) {
        // out.format("<col/>%n");
        ensureColumnBounds(sheet);
        for (int i = firstColumn; i < endColumn; i++) {
            out.format("<col style='width:%spx;' />%n", sheet.getColumnWidth(i) / 32);
        }
    }

    private int getTableWidth(Sheet sheet) {
        ensureColumnBounds(sheet);
        int width = 0;
        for (int i = firstColumn; i < endColumn; i++) {
            width = width + (sheet.getColumnWidth(i) / 32);
        }
        return width;
    }

    private void ensureColumnBounds(Sheet sheet) {
        if (gotBounds)
            return;
        Iterator<Row> iter = sheet.rowIterator();
        firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
        endColumn = 0;
        while (iter.hasNext()) {
            Row row = iter.next();
            short firstCell = row.getFirstCellNum();
            if (firstCell >= 0) {
                firstColumn = Math.min(firstColumn, firstCell);
                endColumn = Math.max(endColumn, row.getLastCellNum());
            }
        }
        gotBounds = true;
    }

    @SuppressWarnings("unused")
    private /**
     * 本来是用来生成 A,B 那个列名称的*
     */
    void printColumnHeads(Sheet sheet) {
        out.format("<thead>%n");
        out.format("  <tr clreplaced=%s>%n", COL_HEAD_CLreplaced);
        out.format("    <th clreplaced=%s>◊</th>%n", COL_HEAD_CLreplaced);
        StringBuilder colName = new StringBuilder();
        for (int i = firstColumn; i < endColumn; i++) {
            colName.setLength(0);
            int cnum = i;
            do {
                colName.insert(0, (char) ('A' + cnum % 26));
                cnum /= 26;
            } while (cnum > 0);
            out.format("    <th clreplaced=%s>%s</th>%n", COL_HEAD_CLreplaced, colName);
        }
        out.format("  </tr>%n");
        out.format("</thead>%n");
    }

    private void printSheetContent(Sheet sheet) {
        // printColumnHeads(sheet);
        MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
        CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
        out.format("<tbody>%n");
        Iterator<Row> rows = sheet.rowIterator();
        int rowIndex = 1;
        while (rows.hasNext()) {
            Row row = rows.next();
            out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
            // out.format("    <td clreplaced='%s'>%d</td>%n", ROW_HEAD_CLreplaced, row.getRowNum() + 1);
            for (int i = firstColumn; i < endColumn; i++) {
                if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
                    String content = " ";
                    CellStyle style = null;
                    if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
                        Cell cell = row.getCell(i);
                        if (cell != null) {
                            style = cell.getCellStyle();
                            content = cellValueHelper.getHtmlValue(cell);
                        }
                    }
                    if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
                        Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
                        out.format("    <td rowspan='%s' colspan='%s' clreplaced='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
                    } else {
                        out.format("    <td clreplaced='%s'>%s</td>%n", styleName(style), content);
                    }
                }
            }
            out.format("  </tr>%n");
            rowIndex++;
        }
        out.format("</tbody>%n");
    }

    private String styleName(CellStyle style) {
        if (style == null)
            return "";
        return String.format("style_%02x_%s font_%s_%s", style.getIndex(), cssRandom, style.getFontIndex(), cssRandom);
    }
}

19 Source : AbstractExcelExportStyler.java
with Apache License 2.0
from xiaolanglang

/**
 * 抽象接口提供两个公共方法
 * @author JueYue
 * @date 2015年1月9日 下午5:48:55
 */
public abstract clreplaced AbstractExcelExportStyler implements IExcelExportStyler {

    // 单行
    protected CellStyle stringNoneStyle;

    protected CellStyle stringNoneWrapStyle;

    // 间隔行
    protected CellStyle stringSeptailStyle;

    protected CellStyle stringSeptailWrapStyle;

    protected Workbook workbook;

    protected static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");

    protected void createStyles(Workbook workbook) {
        this.stringNoneStyle = stringNoneStyle(workbook, false);
        this.stringNoneWrapStyle = stringNoneStyle(workbook, true);
        this.stringSeptailStyle = stringSeptailStyle(workbook, false);
        this.stringSeptailWrapStyle = stringSeptailStyle(workbook, true);
        this.workbook = workbook;
    }

    @Override
    public CellStyle getStyles(boolean noneStyler, ExcelExportEnreplacedy enreplacedy) {
        if (noneStyler && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringNoneWrapStyle;
        }
        if (noneStyler) {
            return stringNoneStyle;
        }
        if (noneStyler == false && (enreplacedy == null || enreplacedy.isWrap())) {
            return stringSeptailWrapStyle;
        }
        return stringSeptailStyle;
    }

    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        return null;
    }

    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        return null;
    }
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from xiaolanglang

/**
 * 转换成为完整界面
 * @param wb Excel
 * @param sheetNum sheetNum
 * @return
 */
public static String toAllHtml(Workbook wb, int sheetNum) {
    return new ExcelToHtmlServer(wb, true, sheetNum).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from xiaolanglang

/**
 * 转换成为Table
 * @param wb Excel
 * @return
 */
public static String toTableHtml(Workbook wb) {
    return new ExcelToHtmlServer(wb, false, 0).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from xiaolanglang

/**
 * 转换成为Table
 * @param wb Excel
 * @param sheetNum sheetNum
 * @return
 */
public static String toTableHtml(Workbook wb, int sheetNum) {
    return new ExcelToHtmlServer(wb, false, sheetNum).printPage();
}

19 Source : ExcelToHtmlUtil.java
with Apache License 2.0
from xiaolanglang

/**
 * 转换成为完整界面
 * @param wb Excel
 * @param sheetNum sheetNum
 * @return
 */
public static String toAllHtml(Workbook wb) {
    return new ExcelToHtmlServer(wb, true, 0).printPage();
}

19 Source : ExcelExportUtil.java
with Apache License 2.0
from xiaolanglang

/**
 * 根据Map创建对应的Excel
 * @param enreplacedy
 *            表格标题属性
 * @param pojoClreplaced
 *            Excel对象Clreplaced
 * @param dataSet
 *            Excel对象数据List
 */
public static Workbook exportExcel(ExportParams enreplacedy, List<ExcelExportEnreplacedy> enreplacedyList, Collection<? extends Map<?, ?>> dataSet) {
    Workbook workbook;
    if (ExcelType.HSSF.equals(enreplacedy.getType())) {
        workbook = new HSSFWorkbook();
    } else if (dataSet.size() < 1000) {
        workbook = new XSSFWorkbook();
    } else {
        workbook = new SXSSFWorkbook();
    }
    new ExcelExportServer().createSheetForMap(workbook, enreplacedy, enreplacedyList, dataSet);
    return workbook;
}

19 Source : PoiUtils.java
with MIT License
from wu-boy

/**
 * 关闭Workbook
 * @param workbook
 */
public static void close(Workbook workbook) {
    try {
        if (workbook != null) {
            workbook.close();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

19 Source : TExcelConnection.java
with Apache License 2.0
from wso2

public clreplaced TExcelConnection extends TConnection {

    private static final Log log = LogFactory.getLog(TExcelConnection.clreplaced);

    private Workbook workbook;

    /**
     * The time in seconds which the lock will try waiting to acquire the lock for the file
     */
    private final int LOCK_TIMEOUT = 20;

    /**
     * Lock used to lock the excel book while editing or modifying.
     */
    private static final Lock lock = new ReentrantLock();

    // variable used for debug purposes
    private static int lockCount = 0;

    private String filePath;

    public TExcelConnection(Properties props) throws SQLException {
        super(props);
        filePath = (String) props.get(Constants.DRIVER_PROPERTIES.FILE_PATH);
        this.workbook = this.createConnectionToExcelDoreplacedent(filePath);
    }

    /**
     * Creates a connection to the given Excel doreplacedent and returns a workbook instance
     *
     * @param filePath Path to the Excel file
     * @return Instance of workbook clreplaced containing which represents a database in the
     *         world of SQL
     * @throws SQLException SQLException
     */
    private Workbook createConnectionToExcelDoreplacedent(String filePath) throws SQLException {
        Workbook workbook;
        try {
            acquireLock();
            InputStream fin = TDriverUtil.getInputStreamFromPath(filePath);
            workbook = WorkbookFactory.create(fin);
        } catch (FileNotFoundException e) {
            throw new SQLException("Could not locate the EXCEL datasource in the provided " + "location", e);
        } catch (IOException | InvalidFormatException e) {
            throw new SQLException("Error occurred while initializing the EXCEL datasource", e);
        } catch (InterruptedException e) {
            throw new SQLException("Error Acquiring the lock for the workbook path - " + filePath, e);
        } finally {
            releaseLock();
        }
        return workbook;
    }

    /**
     * Helper method to acquire a lock for the transaction purpose.
     *
     * @throws InterruptedException
     * @throws SQLException
     */
    private synchronized void acquireLock() throws InterruptedException, SQLException {
        if (lock.tryLock(LOCK_TIMEOUT, TimeUnit.SECONDS)) {
            if (log.isDebugEnabled()) {
                lockCount++;
                log.debug("Acquired the lock for the excel file to make it transactional, current lock count - " + lockCount);
            }
        } else {
            throw new SQLException("Error acquiring lock for the excel file even after 20 second wait, filePath - " + this.filePath);
        }
    }

    /**
     * Helper method to release the acquired lock at the end of the transaction.
     */
    private synchronized void releaseLock() {
        try {
            lock.unlock();
            if (log.isDebugEnabled()) {
                lockCount--;
                log.debug("Released the lock for excel file after the transaction, current lock count - " + lockCount);
            }
        } catch (IllegalMonitorStateException e) {
            if (log.isDebugEnabled()) {
                log.debug("Failed to release the lock as it is already released, lock count - " + lockCount, e);
            }
        } catch (Exception e) {
            if (log.isDebugEnabled()) {
                log.debug("Failed to release the lock as it is already released, lock count - " + lockCount, e);
            }
        }
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public Statement createStatement(String sql) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public Statement createStatement() throws SQLException {
        return new TPreparedStatement();
    }

    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        throw new SQLFeatureNotSupportedException("CallableStatements are not supported");
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return new TPreparedStatement(this, sql);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return null;
    }

    /**
     * Begin transaction method for Excel connections, This will reread the workbook and acquire the lock as well.
     *
     * @throws SQLException
     */
    public void beginExcelTransaction() throws SQLException {
        this.workbook = this.createConnectionToExcelDoreplacedent(filePath);
    }

    public void commit() throws SQLException {
        releaseLock();
    }

    public void rollback() throws SQLException {
        releaseLock();
    }

    public void close() throws SQLException {
        releaseLock();
    }
}

19 Source : TExcelConnection.java
with Apache License 2.0
from wso2

/**
 * Creates a connection to the given Excel doreplacedent and returns a workbook instance
 *
 * @param filePath Path to the Excel file
 * @return Instance of workbook clreplaced containing which represents a database in the
 *         world of SQL
 * @throws SQLException SQLException
 */
private Workbook createConnectionToExcelDoreplacedent(String filePath) throws SQLException {
    Workbook workbook;
    try {
        acquireLock();
        InputStream fin = TDriverUtil.getInputStreamFromPath(filePath);
        workbook = WorkbookFactory.create(fin);
    } catch (FileNotFoundException e) {
        throw new SQLException("Could not locate the EXCEL datasource in the provided " + "location", e);
    } catch (IOException | InvalidFormatException e) {
        throw new SQLException("Error occurred while initializing the EXCEL datasource", e);
    } catch (InterruptedException e) {
        throw new SQLException("Error Acquiring the lock for the workbook path - " + filePath, e);
    } finally {
        releaseLock();
    }
    return workbook;
}

19 Source : ExcelConfig.java
with Apache License 2.0
from wso2

public Workbook createWorkbook() throws IOException, InvalidFormatException, DataServiceFault {
    InputStream ins = DBUtils.getInputStreamFromPath(this.getExcelDataSourcePath());
    Workbook wb = WorkbookFactory.create(ins);
    return wb;
}

19 Source : ExcelConfig.java
with Apache License 2.0
from wso2

@Override
public boolean isActive() {
    try {
        Workbook wb = this.createWorkbook();
        return wb != null;
    } catch (Exception e) {
        log.error("Error in checking Excel config availability", e);
        return false;
    }
}

19 Source : ExcelReader.java
with Apache License 2.0
from warlock-china

public final clreplaced ExcelReader implements Closeable {

    private static final Logger LOG = LoggerFactory.getLogger(ExcelReader.clreplaced);

    /**
     * 时日类型的数据默认格式化方式
     */
    private DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private int startRow;

    private String sheetName;

    private final String excelFilePath;

    private final Workbook workbook;

    /**
     * 构造方法,传入需要操作的excel文件路径
     *
     * @param excelFilePath 需要操作的excel文件的路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(String excelFilePath) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = excelFilePath;
        this.workbook = createWorkbook();
    }

    /**
     * 通过数据流操作excel,仅用于读取数据
     *
     * @param inputStream excel数据流
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(InputStream inputStream) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = "";
        this.workbook = WorkbookFactory.create(inputStream);
    }

    /**
     * 通过数据流操作excel
     *
     * @param inputStream excel数据流
     * @param outFilePath 输出的excel文件路径
     * @throws IOException            IO流异常
     * @throws InvalidFormatException 非法的格式异常
     */
    public ExcelReader(InputStream inputStream, String outFilePath) throws IOException, InvalidFormatException {
        this.startRow = 0;
        this.sheetName = "Sheet1";
        this.excelFilePath = outFilePath;
        this.workbook = WorkbookFactory.create(inputStream);
    }

    /**
     * 开始读取的行数,这里指的是标题内容行的行数,不是数据开始的那行
     *
     * @param startRow 开始行数
     */
    public void setStartRow(int startRow) {
        if (startRow < 1) {
            throw new RuntimeException("最小为1");
        }
        this.startRow = --startRow;
    }

    /**
     * 设置需要读取的sheet名字,不设置默认的名字是Sheet1,也就是excel默认给的名字,所以如果文件没有自已修改,这个方法也就不用调了
     *
     * @param sheetName 需要读取的Sheet名字
     */
    public void setSheetName(String sheetName) {
        // Sheet sheet = this.workbook.getSheet(sheetName);
        // if (null == sheet) {
        // throw new RuntimeException("sheetName:" + sheetName + " is not exist");
        // }
        this.sheetName = sheetName;
    }

    /**
     * 设置时间数据格式
     *
     * @param format 格式
     */
    public void setFormat(String format) {
        this.format = new SimpleDateFormat(format);
    }

    /**
     * 解析读取excel文件
     *
     * @param clazz 对应的映射类型
     * @param <T>   泛型
     * @return 读取结果
     */
    public <T> List<T> parse(Clreplaced<T> clazz) {
        List<T> resultList = null;
        try {
            Sheet sheet = workbook.getSheet(this.sheetName);
            if (null != sheet) {
                resultList = new ArrayList<T>(sheet.getLastRowNum() - 1);
                Row row = sheet.getRow(this.startRow);
                Map<String, Field> fieldMap = new HashMap<String, Field>();
                Map<String, String> replacedleMap = new HashMap<String, String>();
                Field[] fields = clazz.getDeclaredFields();
                // 这里开始处理映射类型里的注解
                for (Field field : fields) {
                    if (field.isAnnotationPresent(replacedleCell.clreplaced)) {
                        replacedleCell mapperCell = field.getAnnotation(replacedleCell.clreplaced);
                        fieldMap.put(mapperCell.name(), field);
                    }
                }
                for (Cell replacedle : row) {
                    CellReference cellRef = new CellReference(replacedle);
                    replacedleMap.put(cellRef.getCellRefParts()[2], replacedle.getRichStringCellValue().getString());
                }
                for (int i = this.startRow + 1; i <= sheet.getLastRowNum(); i++) {
                    T t = clazz.newInstance();
                    Row dataRow = sheet.getRow(i);
                    for (Cell data : dataRow) {
                        CellReference cellRef = new CellReference(data);
                        String cellTag = cellRef.getCellRefParts()[2];
                        String name = replacedleMap.get(cellTag);
                        Field field = fieldMap.get(name);
                        if (null != field) {
                            field.setAccessible(true);
                            getCellValue(data, t, field);
                        }
                    }
                    resultList.add(t);
                }
            } else {
                throw new RuntimeException("sheetName:" + this.sheetName + " is not exist");
            }
        } catch (InstantiationException e) {
            LOG.error("初始化异常", e);
        } catch (IllegalAccessException e) {
            LOG.error("初始化异常", e);
        } catch (ParseException e) {
            LOG.error("时间格式化异常:{}", e);
        } catch (Exception e) {
            LOG.error("其他异常", e);
        }
        return resultList;
    }

    private void getCellValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
        LOG.debug("cell:{}, field:{}, type:{}", cell.getCellTypeEnum(), field.getName(), field.getType().getName());
        switch(cell.getCellTypeEnum()) {
            case BLANK:
                break;
            case BOOLEAN:
                field.setBoolean(o, cell.getBooleanCellValue());
                break;
            case ERROR:
                field.setByte(o, cell.getErrorCellValue());
                break;
            case FORMULA:
                field.set(o, cell.getCellFormula());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    if (field.getType().getName().equals(Date.clreplaced.getName())) {
                        field.set(o, cell.getDateCellValue());
                    } else {
                        field.set(o, format.format(cell.getDateCellValue()));
                    }
                } else {
                    if (field.getType().isreplacedignableFrom(Integer.clreplaced) || field.getType().getName().equals("int")) {
                        field.setInt(o, (int) cell.getNumericCellValue());
                    } else if (field.getType().isreplacedignableFrom(Short.clreplaced) || field.getType().getName().equals("short")) {
                        field.setShort(o, (short) cell.getNumericCellValue());
                    } else if (field.getType().isreplacedignableFrom(Float.clreplaced) || field.getType().getName().equals("float")) {
                        field.setFloat(o, (float) cell.getNumericCellValue());
                    } else if (field.getType().isreplacedignableFrom(Byte.clreplaced) || field.getType().getName().equals("byte")) {
                        field.setByte(o, (byte) cell.getNumericCellValue());
                    } else if (field.getType().isreplacedignableFrom(Double.clreplaced) || field.getType().getName().equals("double")) {
                        field.setDouble(o, cell.getNumericCellValue());
                    } else if (field.getType().isreplacedignableFrom(String.clreplaced)) {
                        String s = String.valueOf(cell.getNumericCellValue());
                        if (s.contains("E")) {
                            s = s.trim();
                            BigDecimal bigDecimal = new BigDecimal(s);
                            s = bigDecimal.toPlainString();
                        }
                        // 防止整数判定为浮点数
                        if (s.endsWith(".0")) {
                            s = s.substring(0, s.indexOf(".0"));
                        }
                        field.set(o, s);
                    } else {
                        field.set(o, cell.getNumericCellValue());
                    }
                }
                break;
            case STRING:
                if (field.getType().getName().equals(Date.clreplaced.getName())) {
                    field.set(o, format.parse(cell.getRichStringCellValue().getString()));
                } else {
                    field.set(o, cell.getRichStringCellValue().getString());
                }
                break;
            default:
                field.set(o, cell.getStringCellValue());
                break;
        }
    }

    private Workbook createWorkbook() throws IOException, InvalidFormatException {
        Workbook workbook;
        File file = new File(this.excelFilePath);
        if (!file.exists()) {
            LOG.warn("文件:{} 不存在!创建此文件!", this.excelFilePath);
            if (!file.createNewFile()) {
                throw new IOException("文件创建失败");
            }
            workbook = new XSSFWorkbook();
        } else {
            workbook = WorkbookFactory.create(file);
        }
        return workbook;
    }

    /**
     * 获取指定单元格的值
     *
     * @param rowNumber  行数,从1开始
     * @param cellNumber 列数,从1开始
     * @return 该单元格的值
     */
    public String getCellValue(int rowNumber, int cellNumber) {
        String result;
        checkRowAndCell(rowNumber, cellNumber);
        Sheet sheet = this.workbook.getSheet(this.sheetName);
        Row row = sheet.getRow(--rowNumber);
        Cell cell = row.getCell(--cellNumber);
        switch(cell.getCellTypeEnum()) {
            case BLANK:
                result = cell.getStringCellValue();
                break;
            case BOOLEAN:
                result = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                result = String.valueOf(cell.getErrorCellValue());
                break;
            case FORMULA:
                result = cell.getCellFormula();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    result = format.format(cell.getDateCellValue());
                } else {
                    result = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:
                result = cell.getRichStringCellValue().getString();
                break;
            default:
                result = cell.getStringCellValue();
                break;
        }
        return result;
    }

    @Override
    public void close() throws IOException {
        this.workbook.close();
    }

    private void checkRowAndCell(int rowNumber, int cellNumber) {
        if (rowNumber < 1) {
            throw new RuntimeException("rowNumber less than 1");
        }
        if (cellNumber < 1) {
            throw new RuntimeException("cellNumber less than 1");
        }
    }
}

19 Source : ExcelSheetsExtractor.java
with Apache License 2.0
from vividus-framework

private List<Sheet> getAllSheetsFromWorkbook(Workbook wb) {
    return IntStream.range(0, wb.getNumberOfSheets()).mapToObj(wb::getSheetAt).collect(Collectors.toCollection(LinkedList::new));
}

19 Source : AbstractXlsxStreamingView.java
with MIT License
from Vip-Augus

/**
 * This implementation disposes of the {@link SXSSFWorkbook} when done with rendering.
 * @see org.apache.poi.xssf.streaming.SXSSFWorkbook#dispose()
 */
@Override
protected void renderWorkbook(Workbook workbook, HttpServletResponse response) throws IOException {
    super.renderWorkbook(workbook, response);
    // Dispose of temporary files in case of streaming variant...
    ((SXSSFWorkbook) workbook).dispose();
}

19 Source : XsWorkbookTest.java
with MIT License
from Vatavuk

/**
 * Create workbook containing rows and cells.
 * @throws IOException If fails
 */
@Test
public void createsWorkbookWithRowsAndCells() throws IOException {
    final String text = "someText";
    final Workbook workbook = new XsWorkbook(new XsSheet(new XsRow(new TextCell(text)))).asWorkbook();
    Matcherreplacedert.replacedertThat(workbook.getSheetAt(0).getRow(0).getCell(0).getStringCellValue(), Matchers.containsString(text));
}

19 Source : XsWorkbookTest.java
with MIT License
from Vatavuk

/**
 * Create styled workbook.
 * @throws IOException If fails
 */
@Test
public void createsWorkbookWithStyles() throws IOException {
    final Workbook workbook = new XsWorkbook(new XsSheet(new XsRow(new TextCell("text")))).with(new XsStyle(new ForegroundColor(IndexedColors.GOLD.getIndex()))).asWorkbook();
    Matcherreplacedert.replacedertThat(workbook.getSheetAt(0).getRow(0).getCell(0).getCellStyle().getFillForegroundColor(), Matchers.equalTo(IndexedColors.GOLD.getIndex()));
}

19 Source : XsWorkbookTest.java
with MIT License
from Vatavuk

/**
 * Creates workbook with multiple sheets.
 * @throws IOException If fails
 */
@Test
public void createsWorkbookWithMultipleSheets() throws IOException {
    final String fsheet = "sheet1";
    final String ssheet = "sheet2";
    final Workbook wbook = new XsWorkbook().with(new XsSheet(new XsRow(new TextCell(fsheet)))).with(new XsSheet(new XsRow(new TextCell(ssheet)))).asWorkbook();
    Matcherreplacedert.replacedertThat(wbook.getSheetAt(0).getRow(0).getCell(0).getStringCellValue(), Matchers.equalTo(fsheet));
    Matcherreplacedert.replacedertThat(wbook.getSheetAt(1).getRow(0).getCell(0).getStringCellValue(), Matchers.equalTo(ssheet));
}

19 Source : XsRowTest.java
with MIT License
from Vatavuk

/**
 * Add styled row to a sheet.
 * @throws IOException If fails
 */
@Test
public void addsRowWithStyleToSheet() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final Row row = new XsRow().with(new TextCells("a", "b", "c")).with(new TextCell("text").with(new XsStyle(new ForegroundColor(IndexedColors.GOLD.getIndex())))).with(new XsStyle(new ForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()))).attachTo(wbook.createSheet());
        Matcherreplacedert.replacedertThat(row.getCell((int) row.getLastCellNum() - 2).getCellStyle().getFillForegroundColor(), Matchers.equalTo(IndexedColors.GREY_50_PERCENT.getIndex()));
        Matcherreplacedert.replacedertThat(row.getCell((int) row.getLastCellNum() - 1).getCellStyle().getFillForegroundColor(), Matchers.equalTo(IndexedColors.GOLD.getIndex()));
    }
}

19 Source : XsRowTest.java
with MIT License
from Vatavuk

/**
 * Add row to a sheet.
 * @throws IOException If fails
 */
@Test
@SuppressWarnings("PMD.AvoidUsingShortType")
public void addsRowToSheet() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final int expected = 4;
        final Row row = new XsRow().with(new TextCell("someText")).with(new TextCells("a", "b", "c")).attachTo(wbook.createSheet());
        Matcherreplacedert.replacedertThat(row.getLastCellNum(), Matchers.equalTo((short) expected));
    }
}

19 Source : XsRowTest.java
with MIT License
from Vatavuk

/**
 * Add row to specific position in sheet.
 * @throws IOException If fails
 */
@Test
public void addsRowWithAbsolutePositionToSheet() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final int position = 2;
        final int expected = 1;
        final Row row = new XsRow(position, new TextCell("textPos")).attachTo(wbook.createSheet());
        Matcherreplacedert.replacedertThat(row.getRowNum(), Matchers.equalTo(expected));
    }
}

19 Source : StyleTemplateTest.java
with MIT License
from Vatavuk

/**
 * Create custom cell style.
 * @throws IOException If fails
 */
@Test
public void createsCustomStyle() throws IOException {
    try (final Workbook wbook = new XSSFWorkbook()) {
        final Cell cell = wbook.createSheet().createRow(0).createCell(0);
        new StyleTemplateTest.GrayBackground().attachTo(cell);
        Matcherreplacedert.replacedertThat(cell.getCellStyle().getFillForegroundColor(), Matchers.equalTo(IndexedColors.GREY_25_PERCENT.getIndex()));
    }
}

See More Examples