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

Example 91 with HSSFSheet

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

the class Launcher method main.

public static void main(String[] args) throws IOException {
    Map<String, String> propertiesMap = PropertiesParser.getProperties(String.format("%s/src/main/resources/%s", System.getProperty("user.dir"), propertiesFileName));
    statusColumn = Integer.parseInt(propertiesMap.get("statusColumn"));
    testCase = (propertiesMap.get("testCase") != null) ? propertiesMap.get("testCase") : getPropertyEnv("testCase", String.format("%s\\Desktop\\%s", System.getenv("USERPROFILE"), defaultTestCase));
    System.err.println("Loading test case from: " + testCase);
    FileInputStream file = new FileInputStream(testCase);
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet indexSheet = workbook.getSheet("Index");
    for (int row = 1; row <= indexSheet.getLastRowNum(); row++) {
        Row indexRow = indexSheet.getRow(row);
        if (safeCellToString(indexRow.getCell(1)).equalsIgnoreCase("Yes") && !safeCellToString(indexRow.getCell(0)).isEmpty()) {
            if (debug) {
                System.err.println("Reading suite: " + indexRow.getCell(0).getStringCellValue());
            }
            Map<Integer, Map<String, String>> steps = readSteps(indexRow.getCell(0).getStringCellValue());
            for (int step = 0; step < steps.size(); step++) {
                Map<String, String> data = steps.get(step);
                for (String param : new ArrayList<String>(data.keySet())) {
                    if (data.get(param) == null) {
                        data.remove(param);
                    }
                }
                String keyword = data.get("keyword");
                KeywordLibrary.callMethod(keyword, data);
                writeStatus(indexRow.getCell(0).getStringCellValue(), step + 1);
            }
        }
    }
    if (debug) {
        System.err.println("Done");
    }
    workbook.close();
}

Example 92 with HSSFSheet

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

the class Launcher method readSteps.

// reads the spreadsheet into a hash of step keywords and parameters indexed
// by column number and step number
public static Map<Integer, Map<String, String>> readSteps(String sheetName) throws IOException {
    Map<String, String> data = new HashMap<>();
    Map<Integer, Map<String, String>> stepDataMap = new HashMap<>();
    FileInputStream file = new FileInputStream(testCase);
    HSSFWorkbook workbook = new HSSFWorkbook(file);
    HSSFSheet testcaseSheet = workbook.getSheet(sheetName);
    Row stepRow;
    Cell stepCell;
    for (int row = 1; row <= testcaseSheet.getLastRowNum(); row++) {
        if (debug) {
            System.err.println("Row: " + row);
        }
        data = new HashMap<>();
        stepRow = testcaseSheet.getRow(row);
        data.put("keyword", stepRow.getCell(0).getStringCellValue());
        for (int col = 1; col < statusColumn; col++) {
            stepCell = stepRow.getCell(col);
            String cellValue = null;
            try {
                cellValue = safeCellToString(stepCell);
            } catch (NullPointerException | IllegalStateException e) {
                System.err.println("Exception (ignored): " + e.toString());
                cellValue = "";
            }
            if (debug) {
                System.err.println("Column[" + col + "] = " + cellValue);
            }
            data.put(String.format("param%d", col), cellValue);
        }
        stepDataMap.put(row - 1, data);
    }
    workbook.close();
    return stepDataMap;
}

Example 93 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet 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 94 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet 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);
    }
}

Example 95 with HSSFSheet

use of org.apache.poi.hssf.usermodel.HSSFSheet in project titan.EclipsePlug-ins by eclipse.

the class ExportedProblemMerger method collectData.

/**
 * Collect all data from the files given in the constructor.
 */
private void collectData() {
    boolean first = true;
    for (final File file : files) {
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook(new FileInputStream(file));
            final HSSFSheet sheet = workbook.getSheetAt(0);
            if (first) {
                project = getProjectName(sheet);
                first = false;
            } else {
                // check if it is the same project as the first
                if (!project.equals(getProjectName(sheet))) {
                    continue;
                }
            }
            collectSmellNames(sheet);
            collectDates(file, sheet);
        } catch (ArrayIndexOutOfBoundsException e) {
            // outside content boundaries
            ErrorReporter.logExceptionStackTrace("Possibly wrong structure of " + file.getName(), e);
            System.out.println("Possibly wrong structure of " + file.getName());
        } catch (IOException e) {
            ErrorReporter.logExceptionStackTrace("Error while opening " + file.getName(), e);
            System.out.println("Error opening " + file.getName());
        } finally {
            if (workbook != null) {
                workbook = null;
            }
        }
    }
}