Examples with HSSFRow org.apache.poi.hssf.usermodel.HSSFRow used on opensource projects

Example 56 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project openolat by klemens.

the class ExcelDocument method readContent.

@Override
protected FileContent readContent(VFSLeaf leaf) throws IOException, DocumentException {
    int cellNullCounter = 0;
    int rowNullCounter = 0;
    int sheetNullCounter = 0;
    try (BufferedInputStream bis = new BufferedInputStream(leaf.getInputStream());
        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(bis))) {
        LimitedContentWriter content = new LimitedContentWriter((int) leaf.getSize(), FileDocumentFactory.getMaxFileSize());
        for (int sheetNumber = 0; sheetNumber < workbook.getNumberOfSheets(); sheetNumber++) {
            HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            if (sheet != null) {
                for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) {
                    HSSFRow row = sheet.getRow(rowNumber);
                    if (row != null) {
                        for (int cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
                            HSSFCell cell = row.getCell(cellNumber);
                            if (cell != null) {
                                if (cell.getCellTypeEnum() == CellType.STRING) {
                                    content.append(cell.getStringCellValue()).append(' ');
                                }
                            } else {
                                cellNullCounter++;
                            }
                        }
                    } else {
                        rowNullCounter++;
                    }
                }
            } else {
                sheetNullCounter++;
            }
        }
        if (log.isDebug()) {
            if ((cellNullCounter > 0) || (rowNullCounter > 0) || (sheetNullCounter > 0)) {
                log.debug("Read Excel content cell=null #:" + cellNullCounter + ", row=null #:" + rowNullCounter + ", sheet=null #:" + sheetNullCounter);
            }
        }
        content.close();
        return new FileContent(content.toString());
    } catch (Exception ex) {
        throw new DocumentException("Can not read XLS Content. File=" + leaf.getName(), ex);
    }
}

Example 57 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project OpenClinica by OpenClinica.

the class SpreadsheetPreview method createGroupsMap.

public Map<Integer, Map<String, String>> createGroupsMap(HSSFWorkbook workbook) {
    if (workbook == null || workbook.getNumberOfSheets() == 0) {
        return new HashMap<Integer, Map<String, String>>();
    }
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    // static group headers for a CRF; TODO: change these so they are not
    // static and hard-coded
    String[] groupHeaders = { "group_label", "group_layout", "group_header", "group_sub_header", "group_repeat_number", "group_repeat_max", "group_repeat_array", "group_row_start_number" };
    Map<String, String> rowCells = new HashMap<String, String>();
    SortedMap<Integer, Map<String, String>> allRows = new TreeMap<Integer, Map<String, String>>();
    String str = "";
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        str = workbook.getSheetName(i);
        if (str.equalsIgnoreCase("Groups")) {
            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                // time again.
                if (j > 1)
                    rowCells = new HashMap<String, String>();
                row = sheet.getRow(j);
                for (int k = 0; k < groupHeaders.length; k++) {
                    cell = row.getCell((short) k);
                    if (groupHeaders[k].equalsIgnoreCase("group_header")) {
                        rowCells.put(groupHeaders[k], getCellValue(cell).replaceAll("<[^>]*>", ""));
                    } else {
                    }
                }
                allRows.put(j, rowCells);
            }
        // end inner for loop
        }
    // end if
    }
    // end outer for
    return allRows;
}

Example 58 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project OpenClinica by OpenClinica.

the class SpreadsheetPreviewNw method createGroupsMap.

public Map<Integer, Map<String, String>> createGroupsMap(HSSFWorkbook workbook) {
    if (workbook == null || workbook.getNumberOfSheets() == 0) {
        return new HashMap<Integer, Map<String, String>>();
    }
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    sheet = workbook.getSheetAt(4);
    cell = sheet.getRow(1).getCell((short) 0);
    String version = cell.getStringCellValue();
    // static group headers for a CRF; TODO: change these so they are not
    // static and hard-coded
    // BWP>>remove "group_borders" column
    String[] groupHeaders = { "group_label", "repeating_group", "group_header", "group_repeat_number", "group_repeat_max" };
    if (version.equalsIgnoreCase("Version: 2.2") || version.equalsIgnoreCase("Version: 2.5") || version.equalsIgnoreCase("Version: 3.0")) {
        groupHeaders = new String[] { "group_label", "group_header", "group_repeat_number", "group_repeat_max" };
    }
    Map<String, String> rowCells = new HashMap<String, String>();
    SortedMap<Integer, Map<String, String>> allRows = new TreeMap<Integer, Map<String, String>>();
    String str;
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        str = workbook.getSheetName(i);
        if (str.equalsIgnoreCase("Groups")) {
            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                // time again.
                if (j > 1)
                    rowCells = new HashMap<String, String>();
                row = sheet.getRow(j);
                for (int k = 0; k < groupHeaders.length; k++) {
                    cell = row.getCell((short) k);
                    if (groupHeaders[k].equalsIgnoreCase("group_header")) {
                        rowCells.put(groupHeaders[k], getCellValue(cell));
                    } else {
                        rowCells.put(groupHeaders[k], getCellValue(cell).replaceAll("<[^>]*>", ""));
                    }
                }
                allRows.put(j, rowCells);
            }
        // end inner for loop
        }
    // end if
    }
    // end outer for
    return allRows;
}

Example 59 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project OpenClinica by OpenClinica.

the class SpreadsheetPreviewNw method createCrfMap.

/*
     * This method searches for a sheet named "Sections" in an Excel Spreadsheet object, then creates a HashMap containing that sheet's data. The HashMap
     * contains the sheet name as the key, and a List of cells (only the ones that contain data, not blank ones). This method was created primarly to get the
     * section names for a CRF preview page. The Map does not contain data for any sections that have duplicate names; just one section per section name. This
     * method does not yet validate the spreadsheet as a CRF.
     * @author Bruce Perry @returns A HashMap containing CRF section names as keys. Returns an empty HashMap if the spreadsheet does not contain any sheets
     * named "Sections."
     */
public Map<String, String> createCrfMap(HSSFWorkbook workbook) {
    if (workbook == null || workbook.getNumberOfSheets() == 0) {
        return new HashMap<String, String>();
    }
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    Map<String, String> crfInfo = new HashMap<String, String>();
    String mapKey = "";
    String val = "";
    String str = "";
    String[] crfHeaders = { "crf_name", "version", "version_description", "revision_notes" };
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        str = workbook.getSheetName(i);
        if (str.equalsIgnoreCase("CRF")) {
            row = sheet.getRow(1);
            for (int k = 0; k < crfHeaders.length; k++) {
                // The first cell in the row contains the header CRF_NAME
                mapKey = crfHeaders[k];
                cell = row.getCell((short) k);
                if (cell != null) {
                    switch(cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            val = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            val = Double.toString(cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            val = Boolean.toString(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:
                            cell.getCellFormula();
                            break;
                    }
                }
                crfInfo.put(mapKey, val);
            }
        }
    // end if
    }
    // end outer for
    return crfInfo;
}

Example 60 with HSSFRow

use of org.apache.poi.hssf.usermodel.HSSFRow in project OpenClinica by OpenClinica.

the class SpreadsheetPreviewNw method createItemsOrSectionMap.

/**
 * This method searches for a sheet named "Items" or "Sections" in an Excel Spreadsheet object, then creates a sorted Map whose members represent a row of
 * data for each "Item" or "Section" on the sheet. This method was created primarily to get Items and section data for previewing a CRF.
 *
 * @return A SortedMap implementation (TreeMap) containing row numbers, each pointing to a Map. The Maps represent each Item or section row in a
 *         spreadsheet. The items or sections themselves are in rows 1..N. An example data value from a Section row is: 1: {page_number=1.0,
 *         section_label=Subject Information, section_title=SimpleSection1} Returns an empty Map if the spreadsheet does not contain any sheets named
 *         "sections" or "items" (case insensitive).
 * @param workbook
 *            is an object representing a spreadsheet.
 * @param itemsOrSection
 *            should specify "items" or "sections" or the associated static variable, i.e. SpreadsheetPreview.ITEMS
 */
public Map<Integer, Map<String, String>> createItemsOrSectionMap(HSSFWorkbook workbook, String itemsOrSection) {
    if (workbook == null || workbook.getNumberOfSheets() == 0) {
        return new HashMap<Integer, Map<String, String>>();
    }
    if (itemsOrSection == null || !itemsOrSection.equalsIgnoreCase(ITEMS) && !itemsOrSection.equalsIgnoreCase(SECTIONS)) {
        return new HashMap<Integer, Map<String, String>>();
    }
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell;
    // static item headers for a CRF; TODO: change these so they are not
    // static and hard-coded
    /*
         * New itemHeaders String[] itemHeaders = {"item_name","description_label","left_item_text",
         * "units","right_item_text","section_label","group_label","header", "subheader","parent_item","column_number","page_number",
         * "question_number","response_type","response_label", "response_options_text","response_values","response_layout","default_value", "data_type",
         * "validation","validation_error_message","phi","required"};
         */
    String[] itemHeaders = { "item_name", "description_label", "left_item_text", "units", "right_item_text", "section_label", "group_label", "header", "subheader", "parent_item", "column_number", "page_number", "question_number", "response_type", "response_label", "response_options_text", "response_values", "response_layout", "default_value", "data_type", "width_decimal", "validation", "validation_error_message", "phi", "required" };
    String[] sectionHeaders = { "section_label", "section_title", "subtitle", "instructions", "page_number", "parent_section", "borders" };
    Map<String, String> rowCells = new HashMap<String, String>();
    SortedMap<Integer, Map<String, String>> allRows = new TreeMap<Integer, Map<String, String>>();
    String str;
    String dateFormat = "";
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        str = workbook.getSheetName(i);
        if (str.equalsIgnoreCase(itemsOrSection)) {
            for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
                String[] headers = itemsOrSection.equalsIgnoreCase(ITEMS) ? itemHeaders : sectionHeaders;
                // time again.
                if (j > 1)
                    rowCells = new HashMap<String, String>();
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                for (int k = 0; k < headers.length; k++) {
                    cell = row.getCell((short) k);
                    if ("default_value".equalsIgnoreCase(headers[k]) && isDateDatatype(headers, row) && !"".equalsIgnoreCase(getCellValue(cell))) {
                        try {
                            // BWP>> getDateCellValue() wll throw an
                            // exception if
                            // the value is an invalid date. Keep the date
                            // format the same as
                            // it is in the database; MM/dd/yyyy
                            // String pttrn = ResourceBundleProvider.getFormatBundle().getString("oc_date_format_string");
                            String pttrn = ApplicationConstants.getDateFormatInItemData();
                            dateFormat = new SimpleDateFormat(pttrn).format(cell.getDateCellValue());
                            rowCells.put(headers[k], dateFormat);
                            continue;
                        } catch (Exception e) {
                            String cellVal = getCellValue(cell);
                            logger.info("An invalid date format was encountered when reading a default value in the spreadsheet.");
                            rowCells.put(headers[k], cellVal);
                            continue;
                        }
                    }
                    if (headers[k].equalsIgnoreCase("left_item_text") || headers[k].equalsIgnoreCase("right_item_text") || headers[k].equalsIgnoreCase("header") || headers[k].equalsIgnoreCase("subheader") || headers[k].equalsIgnoreCase("question_number") || headers[k].equalsIgnoreCase("section_title") || headers[k].equalsIgnoreCase("subtitle") || headers[k].equalsIgnoreCase("instructions") || headers[k].equalsIgnoreCase("response_options_text")) {
                        rowCells.put(headers[k], getCellValue(cell).replaceAll("\\\\,", "\\,"));
                    } else {
                        rowCells.put(headers[k], getCellValue(cell).replaceAll("\\\\,", "\\,").replaceAll("<[^>]*>", ""));
                    }
                // logger.warn("BADS: "+headers[k]+": "+getCellValue(cell));
                }
                // item_name
                allRows.put(j, rowCells);
            }
        // end inner for loop
        }
    // end if
    }
    // end outer for
    return allRows;
}