Examples with XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook used on opensource projects

Example 86 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestWorkbookProtection method testIntegration.

@SuppressWarnings("resource")
@Test
public void testIntegration() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    wb.createSheet("Testing purpose sheet");
    assertFalse(wb.isRevisionLocked());
    wb.lockRevision();
    wb.setRevisionsPassword("test", null);
    wb = writeOutAndReadBack(wb);
    assertTrue(wb.isRevisionLocked());
    assertTrue(wb.validateRevisionsPassword("test"));
}

Example 87 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class TestWorkbookProtection method workbookAndRevisionPassword.

@Test
public void workbookAndRevisionPassword() throws Exception {
    XSSFWorkbook workbook;
    String password = "test";
    // validate password with an actual office file (Excel 2010)
    workbook = openSampleWorkbook("workbookProtection-workbook_password_user_range-2010.xlsx");
    assertTrue(workbook.validateWorkbookPassword(password));
    // validate with another office file (Excel 2013)
    workbook = openSampleWorkbook("workbookProtection-workbook_password-2013.xlsx");
    assertTrue(workbook.validateWorkbookPassword(password));
    workbook = openSampleWorkbook("workbookProtection_not_protected.xlsx");
    // setting a null password shouldn't introduce the protection element
    workbook.setWorkbookPassword(null, null);
    assertNull(workbook.getCTWorkbook().getWorkbookProtection());
    // compare the hashes
    workbook.setWorkbookPassword(password, null);
    int hashVal = CryptoFunctions.createXorVerifier1(password);
    int actualVal = Integer.parseInt(workbook.getCTWorkbook().getWorkbookProtection().xgetWorkbookPassword().getStringValue(), 16);
    assertEquals(hashVal, actualVal);
    assertTrue(workbook.validateWorkbookPassword(password));
    // removing the password again
    workbook.setWorkbookPassword(null, null);
    assertFalse(workbook.getCTWorkbook().getWorkbookProtection().isSetWorkbookPassword());
    // removing the whole protection structure
    workbook.unLock();
    assertNull(workbook.getCTWorkbook().getWorkbookProtection());
    // setting a null password shouldn't introduce the protection element
    workbook.setRevisionsPassword(null, null);
    assertNull(workbook.getCTWorkbook().getWorkbookProtection());
    // compare the hashes
    password = "TЀSTĀpasswordWhichIsLongerThan15Chars";
    workbook.setRevisionsPassword(password, null);
    hashVal = CryptoFunctions.createXorVerifier1(password);
    actualVal = Integer.parseInt(workbook.getCTWorkbook().getWorkbookProtection().xgetRevisionsPassword().getStringValue(), 16);
    assertEquals(hashVal, actualVal);
    assertTrue(workbook.validateRevisionsPassword(password));
}

Example 88 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project hale by halestudio.

the class XLSLookupTableReader method execute.

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 */
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
    int keyColumn = getParameter(LookupTableExportConstants.LOOKUP_KEY_COLUMN).as(Integer.class);
    int valueColumn = getParameter(LookupTableExportConstants.LOOKUP_VALUE_COLUMN).as(Integer.class);
    boolean skipFirst = getParameter(LookupTableExportConstants.PARAM_SKIP_FIRST_LINE).as(Boolean.class);
    boolean ignoreEmptyStrings = getParameter(LookupTableExportConstants.PARAM_IGNORE_EMPTY_STRING).as(Boolean.class);
    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook(getSource().getInput());
    } else // write xlsx file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook(getSource().getInput());
    } else {
        reporter.setSuccess(false);
        return reporter;
    }
    DefaultXLSLookupTableReader reader = new DefaultXLSLookupTableReader();
    Map<Value, Value> map = reader.read(workbook, skipFirst, keyColumn, valueColumn, ignoreEmptyStrings);
    lookupTable = new LookupTableInfoImpl(new LookupTableImpl(map), getName(), getDescription());
    reporter.setSuccess(true);
    return reporter;
}

Example 89 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project hale by halestudio.

the class XLSAlignmentMappingWriter method execute.

@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
    super.execute(progress, reporter);
    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    } else // write xlsx file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }
    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Mapping table");
    Row row = null;
    Cell cell = null;
    // create cell style of the header
    CellStyle headerStyle = XLSCellStyles.getHeaderStyle(workbook);
    // create cell style
    CellStyle cellStyle = XLSCellStyles.getNormalStyle(workbook, false);
    // create highlight style for type cells
    CellStyle highlightStyle = XLSCellStyles.getHighlightedStyle(workbook, false);
    // create disabled style
    CellStyle disabledStyle = XLSCellStyles.getNormalStyle(workbook, true);
    // create disabled highlight style
    CellStyle disabledTypeStyle = XLSCellStyles.getHighlightedStyle(workbook, true);
    List<Map<CellType, CellInformation>> mapping = getMappingList();
    // determine if cells are organized by type cell
    boolean byTypeCell = isByTypeCell();
    int rownum = 0;
    // write header
    row = sheet.createRow(rownum++);
    for (int i = 0; i < getMappingHeader().size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(getMappingHeader().get(i));
        cell.setCellStyle(headerStyle);
    }
    // write all mappings
    for (Map<CellType, CellInformation> entry : mapping) {
        boolean disabled = false;
        if (getParameter(TRANSFORMATION_AND_DISABLED_FOR).as(Boolean.class)) {
            List<String> transformationDisabled = entry.get(CellType.TRANSFORMATION_AND_DISABLED).getText();
            disabled = !transformationDisabled.isEmpty() && !transformationDisabled.contains(TransformationMode.active.displayName());
        }
        // create a row
        row = sheet.createRow(rownum);
        CellStyle rowStyle = cellStyle;
        String targetProp = getCellValue(entry, CellType.TARGET_PROPERTIES);
        boolean isTypeCell = targetProp == null || targetProp.isEmpty();
        if (isTypeCell && byTypeCell) {
            if (disabled) {
                // disabled type cell
                rowStyle = disabledTypeStyle;
            } else {
                // normal type cell
                rowStyle = highlightStyle;
            }
        } else if (disabled) {
            // disabled property cell
            rowStyle = disabledStyle;
        }
        List<CellType> celltypes = getCellTypes();
        for (int i = 0; i < celltypes.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(getCellValue(entry, celltypes.get(i)));
            cell.setCellStyle(rowStyle);
        }
        rownum++;
    }
    // could be integrated in configuration page
    // int maxColWidth = calculateWidth(getParameter(MAX_COLUMN_WIDTH).as(Integer.class));
    int maxColWidth = calculateWidth(maxWidth);
    // autosize all columns
    for (int i = 0; i < getMappingHeader().size(); i++) {
        sheet.autoSizeColumn(i);
        if (sheet.getColumnWidth(i) > maxColWidth)
            sheet.setColumnWidth(i, maxColWidth);
    }
    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();
    reporter.setSuccess(true);
    return reporter;
}

Example 90 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project hale by halestudio.

the class XLSInstanceWriter method execute.

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 */
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
    boolean solveNestedProperties = getParameter(InstanceTableIOConstants.SOLVE_NESTED_PROPERTIES).as(Boolean.class, false);
    // get the parameter to get the type definition
    String exportType = getParameter(InstanceTableIOConstants.EXPORT_TYPE).as(String.class);
    QName selectedTypeName = null;
    if (exportType != null && !exportType.equals("") && !exportType.equals(" ")) {
        selectedTypeName = QName.valueOf(exportType);
    }
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        wb = new HSSFWorkbook();
    } else // write xlsx file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        wb = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        return reporter;
    }
    cellStyle = XLSCellStyles.getNormalStyle(wb, false);
    headerStyle = XLSCellStyles.getHeaderStyle(wb);
    // get all instances of the selected Type
    InstanceCollection instances = getInstanceCollection(selectedTypeName);
    Iterator<Instance> instanceIterator = instances.iterator();
    Instance instance = null;
    try {
        instance = instanceIterator.next();
    } catch (NoSuchElementException e) {
        reporter.error(new IOMessageImpl("There are no instances for the selected type.", e));
        return reporter;
    }
    List<Instance> remainingInstances = new ArrayList<Instance>();
    headerRowStrings = new ArrayList<String>();
    // all instances with equal type definitions are stored in an extra
    // sheet
    TypeDefinition definition = instance.getDefinition();
    Sheet sheet = wb.createSheet(definition.getDisplayName());
    Row headerRow = sheet.createRow(0);
    int rowNum = 1;
    Row row = sheet.createRow(rowNum++);
    writeRow(row, super.getPropertyMap(instance, headerRowStrings, solveNestedProperties));
    while (instanceIterator.hasNext()) {
        Instance nextInst = instanceIterator.next();
        if (nextInst.getDefinition().equals(definition)) {
            row = sheet.createRow(rowNum++);
            writeRow(row, super.getPropertyMap(nextInst, headerRowStrings, solveNestedProperties));
        } else
            remainingInstances.add(nextInst);
    }
    writeHeaderRow(headerRow, headerRowStrings);
    setCellStyle(sheet, headerRowStrings.size());
    resizeSheet(sheet);
    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    wb.write(out);
    out.close();
    reporter.setSuccess(true);
    return reporter;
}