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

Example 56 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project rxlib by RockyLOMO.

the class Helper method readExcel.

public static List<Object[]> readExcel(String filePath, boolean skipColumn) throws IOException {
    List<Object[]> result = new ArrayList<>();
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
    for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        for (int rowIndex = skipColumn ? 1 : sheet.getFirstRowNum(); rowIndex < sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);
            List<Object> cellValues = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cellValues.add(null);
                    continue;
                }
                Object value;
                switch(cell.getCellTypeEnum()) {
                    case NUMERIC:
                        value = cell.getNumericCellValue();
                        break;
                    case BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    default:
                        value = cell.getStringCellValue();
                        break;
                }
                cellValues.add(value);
            }
            if (cellValues.contains(null)) {
                Logger.debug(String.format("current=%s offset=%s count=%s -> %s/%s", toJsonString(cellValues), row.getFirstCellNum(), row.getLastCellNum(), rowIndex, sheetIndex));
            }
            result.add(cellValues.toArray());
        }
    }
    return result;
}

Example 57 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project selenium_java by sergueik.

the class ExcelUtils method createTitle.

/**
 * 设置表头
 *
 * @param sheet
 */
private void createTitle(HSSFSheet sheet) {
    if (currentRow > 0) {
        return;
    }
    HSSFRow row = sheet.createRow(currentRow++);
    int column = 0;
    for (String key : headerMap.keySet()) {
        HSSFCell cell = row.createCell(column++);
        cell.setCellValue(key);
    }
}

Example 58 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project selenium_java by sergueik.

the class ExcelUtils method fillContent.

/**
 * 根据一个对象填充一行数据
 *
 * @param sheet
 * @param rowNum 行号
 * @param data 数据对象
 */
private void fillContent(HSSFSheet sheet, int rowNum, Object data) {
    if (sheet == null || data == null) {
        return;
    }
    HSSFRow row = sheet.createRow(rowNum);
    int column = 0;
    Class<? extends Object> dataCls = data.getClass();
    for (String key : headerMap.keySet()) {
        String name = headerMap.get(key);
        try {
            Method method = dataCls.getMethod(String.format("get%s%s", name.substring(0, 1).toUpperCase(), name.substring(1)));
            Object value = method.invoke(data);
            if (value != null) {
                HSSFCell cell = row.createCell(column);
                cell.setCellValue(value.toString());
                if (value.toString().equals(ReportStatus.EXCEPTION.name())) {
                    HSSFFont font = workbook.createFont();
                    font.setColor(HSSFFont.COLOR_RED);
                    HSSFCellStyle style = workbook.createCellStyle();
                    style.setFont(font);
                    cell.setCellStyle(style);
                }
            }
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            column++;
        }
    }
}

Example 59 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project tutorials by eugenp.

the class ExcelPOIHelper method readHSSFWorkbook.

private Map<Integer, List<MyCell>> readHSSFWorkbook(FileInputStream fis) throws IOException {
    Map<Integer, List<MyCell>> data = new HashMap<>();
    HSSFWorkbook workbook = null;
    try {
        workbook = new HSSFWorkbook(fis);
        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            data.put(i, new ArrayList<>());
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        HSSFCellStyle cellStyle = cell.getCellStyle();
                        MyCell myCell = new MyCell();
                        HSSFColor bgColor = cellStyle.getFillForegroundColorColor();
                        if (bgColor != null) {
                            short[] rgbColor = bgColor.getTriplet();
                            myCell.setBgColor("rgb(" + rgbColor[0] + "," + rgbColor[1] + "," + rgbColor[2] + ")");
                        }
                        HSSFFont font = cell.getCellStyle().getFont(workbook);
                        myCell.setTextSize(font.getFontHeightInPoints() + "");
                        if (font.getBold()) {
                            myCell.setTextWeight("bold");
                        }
                        HSSFColor textColor = font.getHSSFColor(workbook);
                        if (textColor != null) {
                            short[] rgbColor = textColor.getTriplet();
                            myCell.setTextColor("rgb(" + rgbColor[0] + "," + rgbColor[1] + "," + rgbColor[2] + ")");
                        }
                        myCell.setContent(readCellContent(cell));
                        data.get(i).add(myCell);
                    } else {
                        data.get(i).add(new MyCell(""));
                    }
                }
            }
        }
    } finally {
        if (workbook != null) {
            workbook.close();
        }
    }
    return data;
}

Example 60 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project OpenOLAT by OpenOLAT.

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);
    }
}