Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook setMissingCellPolicy

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook setMissingCellPolicy.

Prototype

@Override
public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) 

Source Link

Document

Sets the policy on what to do when getting missing or blank cells from a row.

Usage

From source file:com.carlos.projects.billing.ExcelToMySQLImporter.java

License:Open Source License

public Long importData(MultipartFile excelFile) throws ImportException {
    XSSFWorkbook workbook;
    File componentsFile;/*w ww  . j  a  va 2 s  .  c om*/
    try {
        componentsFile = new File("components-" + new Date().getTime() + ".xlsx");
        excelFile.transferTo(componentsFile);
        workbook = new XSSFWorkbook(componentsFile.getAbsolutePath());
    } catch (IOException e) {
        throw new ImportException(messages.getProperty("import.error"), e);
    }
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    Iterator<Row> rowIterator = workbook.getSheetAt(workbook.getActiveSheetIndex()).iterator();
    Long numberOfImportedItems = 0L;
    log.info("Starting reading from file " + excelFile.getOriginalFilename()
            + " to import components to database");
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String familyCode = row.getCell(FAMILY_CODE).getStringCellValue().trim();
        //The first row of the excel file is the one with the titles
        if (row.getRowNum() != 0 && StringUtils.isNotBlank(familyCode)) {
            Family family = familyDAO.getById(Family.class, familyCode);
            boolean saveFamily = false;
            if (family == null) {
                family = createFamilyFromRow(row);
                saveFamily = true;
            }
            String componentCode = row.getCell(COMPONENT_CODE).getStringCellValue().trim();
            Component component = componentDAO.getById(Component.class, componentCode);
            boolean addComponent = false;
            if (component == null) {
                addComponent = true;
                component = createComponent(row, family);
                numberOfImportedItems += 1L;
            }
            if (saveFamily) {
                if (addComponent) {
                    family.addComponent(component);
                }
                familyDAO.save(family);
                log.info("Family " + family + " saved into the database");
            } else {
                componentDAO.save(component);
                log.info("Component " + component + " saved into the database");
            }
        }
    }
    closeAndDeleteTemporaryFiles(componentsFile);
    log.info("Components import to database finished");
    return numberOfImportedItems;
}

---

From source file:org.alfresco.bm.report.XLSXReporter.java

License:Open Source License

@Override
public void export(OutputStream os) {
    XSSFWorkbook workbook = new XSSFWorkbook();
    // Set defaults
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);

    // Write to the workbook
    try {/*from ww  w  . ja va2s.  c om*/
        writeToWorkbook(workbook);
        workbook.write(os);
    } catch (Exception e) {
        throw new RuntimeException("Failed to write workbook: " + this, e);
    } finally {
        try {
            os.close();
        } catch (Throwable e) {
        }
    }
}

---

From source file:org.shareok.data.documentProcessor.ExcelHandler.java

/**
 * Based on the file extension to create corresponding workbook object and
 * return the Sheet object//from  ww  w  .  j  a  v a 2s  .com
 * 
 * @param extension : file extension name of the excel file
 * @param file : FileInputStream
 * @return Sheet object
 * @throws IOException : IO exception handler
 * 
 */
private Sheet getWorkbookSheet(String extension, FileInputStream file) throws IOException {
    Sheet sheet = null;
    if ("xlsx".equals(extension)) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        workbook.setMissingCellPolicy(HSSFRow.RETURN_NULL_AND_BLANK);
        sheet = workbook.getSheetAt(0);
    }
    if ("xls".equals(extension)) {
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        workbook.setMissingCellPolicy(HSSFRow.RETURN_NULL_AND_BLANK);
        sheet = workbook.getSheetAt(0);
    }
    return sheet;
}

---