Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getColumnIndex.

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:io.github.jonestimd.finance.file.excel.SheetParser.java

License:Open Source License

private Map<String, String> getRow(Sheet sheet, int index, Map<Integer, String> columnNames,
        DataFormatter formatter) {//from   w  w  w  . j  av a2s .  c om
    Map<String, String> values = new HashMap<>();
    for (Cell cell : sheet.getRow(index)) {
        String key = columnNames.get(cell.getColumnIndex());
        if (key != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell))
                values.put(key, String.valueOf(cell.getNumericCellValue()));
            else
                values.put(key, formatter.formatCellValue(cell));
        }
    }
    return values;
}

From source file:io.unravellingtechnologies.excalibur.Sheet.java

License:Open Source License

/**
 * Initializes the sheet header structure.
 * //from   w ww  .j a v a  2 s  .c o  m
 * @param sheet Sheet POI object used to initialize the header of this sheet.
 */
private void setSheetHeader(XSSFSheet sheet) {
    if (logger.isDebugEnabled()) {
        logger.debug("Setting sheet header...");
    }

    org.apache.poi.ss.usermodel.Row firstRow = sheet.getRow(sheet.getFirstRowNum());

    if (firstRow.getPhysicalNumberOfCells() == 0) {
        return;
    }

    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();

        sheetHeader.put(cell.getColumnIndex(), cell.getStringCellValue());
    }

    if (logger.isDebugEnabled()) {
        logger.debug("Finished setting the sheet header.");
    }
}

From source file:io.unravellingtechnologies.excalibur.Sheet.java

License:Open Source License

/**
 * Loads all the rows that have content into the Sheet structure.
 *///from   w ww.j a v  a2  s  . co  m
private void loadRows(XSSFSheet sheet) {
    if (logger.isDebugEnabled()) {
        logger.debug("Loading sheet rows...");
    }

    if (sheet.getPhysicalNumberOfRows() < 2) {
        return;
    }

    for (Iterator<org.apache.poi.ss.usermodel.Row> rowIt = sheet.rowIterator(); rowIt.hasNext();) {
        org.apache.poi.ss.usermodel.Row tableRow = rowIt.next();

        if (tableRow.getRowNum() != sheet.getFirstRowNum()) {
            Row row = new Row(new HashMap<String, String>());

            for (Iterator<Cell> cellIt = tableRow.cellIterator(); cellIt.hasNext();) {
                Cell cell = cellIt.next();

                row.addCell(getColumnName(cell.getColumnIndex()), cell.getStringCellValue());
            }

            rows.add(row);
        }
    }

    if (logger.isDebugEnabled()) {
        logger.debug("Completed loading " + rows.size() + " rows.");
    }
}

From source file:it.dontesta.liferay.example.portal.client.ImportUsersToLiferay.java

License:Open Source License

/**
 * Read users to import from excel file.
 * //from  w  w w  .j av  a 2 s.  c  o  m
 * @return Returns a list of users who are ready for import.
 */
private static List<UserToImport> getUsersToImportFromExcel() {
    String fileName = (System.getProperty("fileToImport") != null) ? System.getProperty("fileToImport")
            : FILE_TO_IMPORT_USERS;
    InputStream inp = null;
    List<UserToImport> usersList = new ArrayList<UserToImport>();
    UserToImport user = null;
    boolean readyForImport = true;

    try {
        inp = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            LOGGER.debug("Processing row index {}...", row.getRowNum());
            if (row.getRowNum() == 0) {
                LOGGER.debug("First row is the header. Skip this row");
                continue;
            } else {
                user = new UserToImport();
            }
            for (Cell cell : row) {
                LOGGER.debug("Processing cell index {}...", cell.getColumnIndex());
                switch (cell.getColumnIndex()) {
                case 0:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 1:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setScreenName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The username attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 2:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setEmail(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    } else {
                        LOGGER.warn("The email attribute is not null. Row skipped");
                        LOGGER.warn("Processing cell index {}...[FAILED]", cell.getColumnIndex());
                        break;
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 3:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setFirstName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 4:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setMiddleName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 5:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLastName(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 6:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setGender(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 7:
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Calendar calBirthDate = Calendar.getInstance();
                            calBirthDate.setTime(cell.getDateCellValue());

                            user.setBirthDate(calBirthDate);
                            LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                    cell.getDateCellValue());
                        } else {
                            LOGGER.warn("Value cell index {} {}", cell.getColumnIndex(),
                                    cell.getNumericCellValue());
                            LOGGER.warn("Value cell index {} not contain a date type format",
                                    cell.getColumnIndex());
                        }
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 8:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setJobTitle(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    break;
                case 9:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setSiteName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 10:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setRoleName(cell.getStringCellValue().split(","));
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                Arrays.toString(cell.getStringCellValue().split(",")));
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 11:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setLanguageId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 12:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setTimeZoneId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());
                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                case 13:
                    if (!cell.getStringCellValue().isEmpty()) {
                        user.setAccountId(cell.getStringCellValue());
                        LOGGER.debug("Value cell index {} is {}", cell.getColumnIndex(),
                                cell.getStringCellValue());

                    }
                    LOGGER.debug("Processing cell index {}...[OK]", cell.getColumnIndex());
                    break;
                default:
                    break;
                }
            }
            if (user.getScreenName() == null) {
                LOGGER.warn("The username attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (user.getEmail() == null) {
                LOGGER.warn("The email attribute can not be null for rowId {}", row.getRowNum());
                readyForImport = false;
            }
            if (readyForImport) {
                LOGGER.info("Add user object {} to user list...", user.toString());
                usersList.add(user);
            }
        }
    } catch (FileNotFoundException e) {
        LOGGER.error(e.getMessage());
    } catch (IllegalStateException e) {
        LOGGER.error(e.getMessage());
    } catch (InvalidFormatException e) {
        LOGGER.error(e.getMessage());
    } catch (IOException e) {
        LOGGER.error(e.getMessage());
    }

    return usersList;
}

From source file:it.eng.spagobi.engines.worksheet.services.export.ExportWorksheetAction.java

License:Mozilla Public License

public void exportMetadataToXLS(Workbook wb, WorkSheetXLSExporter exporter, CreationHelper createHelper,
        JSONArray metadataPropertiesJSON, JSONArray parametersJSON) throws Exception {

    int FIRST_ROW = 0;
    int FIRST_COLUMN = 0;
    int rowCount = 0;

    JSONArray technicalMetadataProperty;
    JSONArray shortBusinessMetadataProperty;
    JSONArray longBusinessMetadataProperty;

    org.apache.poi.ss.usermodel.Sheet sheet = wb
            .createSheet(EngineMessageBundle.getMessage("worksheet.export.metadata.title", this.getLocale()));

    sheet.setColumnWidth(FIRST_COLUMN, 256 * 25);
    sheet.setColumnWidth(FIRST_COLUMN + 1, 256 * 90);

    CellStyle headerCellStyle = exporter.buildMetadataTitleCellStyle(sheet);
    CellStyle metaNameCellStyle = exporter.buildMetadataNameCellStyle(sheet);
    CellStyle metaValueCellStyle = exporter.buildMetadataValueCellStyle(sheet);

    Row row;/*  w w w  .  j  a  v a  2s.co  m*/
    Cell nameCell;
    Cell valueCell;
    Cell headerCell;
    String text;

    technicalMetadataProperty = new JSONArray();
    shortBusinessMetadataProperty = new JSONArray();
    longBusinessMetadataProperty = new JSONArray();

    if (metadataPropertiesJSON != null) {
        for (int i = 0; i < metadataPropertiesJSON.length(); i++) {
            JSONObject metadataProperty = metadataPropertiesJSON.getJSONObject(i);
            String metadataPropertyType = metadataProperty.getString("meta_type");
            if ("SHORT_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                shortBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else if ("LONG_TEXT".equalsIgnoreCase(metadataPropertyType)) {
                longBusinessMetadataProperty.put(metadataProperty);
                continue;
            } else {
                technicalMetadataProperty.put(metadataProperty);
            }

        }

    }

    if (technicalMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.technicalMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        for (int i = 0; i < technicalMetadataProperty.length(); i++) {
            JSONObject metadataProperty = technicalMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

        rowCount = rowCount + 2;

    }

    if (shortBusinessMetadataProperty.length() + longBusinessMetadataProperty.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.businessMetadata", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);
        rowCount++;

        for (int i = 0; i < shortBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = shortBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");
            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        for (int i = 0; i < longBusinessMetadataProperty.length(); i++, rowCount++) {

            JSONObject metadataProperty = longBusinessMetadataProperty.getJSONObject(i);

            String metadataPropertyName = metadataProperty.getString("meta_name");
            String metadataPropertyValue = metadataProperty.getString("meta_content");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(metadataPropertyName));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);
            valueCell.setCellValue(createHelper.createRichTextString(metadataPropertyValue));
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
        }

        rowCount = rowCount + 2;

    }

    if (parametersJSON.length() > 0) {

        row = sheet.createRow((FIRST_ROW) + rowCount);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        headerCell = row.createCell(FIRST_COLUMN + 1);
        text = EngineMessageBundle.getMessage("worksheet.export.metadata.analyticalDrivers", this.getLocale());
        headerCell.setCellValue(createHelper.createRichTextString(text));
        headerCell.setCellType(exporter.getCellTypeString());
        headerCell.setCellStyle(headerCellStyle);

        rowCount++;

        Drawing drawing = sheet.createDrawingPatriarch();

        for (int i = 0; i < parametersJSON.length(); i++) {
            JSONObject parameterJSON = parametersJSON.getJSONObject(i);
            String name = parameterJSON.getString("name");
            String value = parameterJSON.getString("value");
            String description = parameterJSON.optString("description");

            row = sheet.createRow((FIRST_ROW) + rowCount);

            nameCell = row.createCell(FIRST_COLUMN);
            nameCell.setCellValue(createHelper.createRichTextString(name));
            nameCell.setCellType(exporter.getCellTypeString());
            nameCell.setCellStyle(metaNameCellStyle);

            valueCell = row.createCell(FIRST_COLUMN + 1);

            if (StringUtilities.isNotEmpty(description)) {

                valueCell.setCellValue(createHelper.createRichTextString(description));

                ClientAnchor anchor = createHelper.createClientAnchor();
                anchor.setCol1(valueCell.getColumnIndex());
                anchor.setCol2(valueCell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 3);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = createHelper.createRichTextString(value);
                comment.setString(str);
                comment.setAuthor("SpagoBI");

                valueCell.setCellComment(comment);
            } else {
                valueCell.setCellValue(createHelper.createRichTextString(value));
            }
            valueCell.setCellType(exporter.getCellTypeString());
            valueCell.setCellStyle(metaValueCellStyle);
            rowCount++;
        }

    }

}

From source file:itpreneurs.itp.report.archive.CellStyleDetails.java

License:Apache License

public static void main(String[] args) throws Exception {

    //     if(args.length == 0) {
    //        throw new IllegalArgumentException("Filename must be given");
    //     }//w  w w .  ja va2 s  .c  o  m

    String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx";

    Workbook wb = WorkbookFactory.create(new File(filename));
    DataFormatter formatter = new DataFormatter();

    for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
        Sheet sheet = wb.getSheetAt(sn);
        System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

        for (Row row : sheet) {
            System.out.println("  Row " + row.getRowNum());

            for (Cell cell : row) {
                CellReference ref = new CellReference(cell);
                System.out.print("    " + ref.formatAsString());
                System.out.print(" (" + cell.getColumnIndex() + ") ");

                CellStyle style = cell.getCellStyle();
                System.out.print("Format=" + style.getDataFormatString() + " ");
                System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " ");
                System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " ");

                Font font = wb.getFontAt(style.getFontIndex());
                System.out.print("Font=" + font.getFontName() + " ");
                System.out.print("FontColor=");
                if (font instanceof HSSFFont) {
                    System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb)));
                }
                if (font instanceof XSSFFont) {
                    System.out.print(renderColor(((XSSFFont) font).getXSSFColor()));
                }

                System.out.println();
                System.out.println("        " + formatter.formatCellValue(cell));
            }
        }

        System.out.println();
    }
}

From source file:javaapp.CompareOpenClose.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();/* w  w w .j  a  v a  2  s. c o m*/
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3
                    || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.println(rpps+"|"+dval+"|"+cval);
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ExReadSample.java

public static void main(String[] args) throws IOException {

    String excelFilePath = "GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    Workbook workbook = new XSSFWorkbook(inputStream);

    /*//  ww  w.ja  v  a 2 s .c o  m
    ArrayList<String> open = parseReport(5,1,2,5,8,44,46,"open");
    ArrayList<String> close = parseReport(24,1,2,5,8,44,46,"close");
    ArrayList<String> rinvoice = parseReport(34,0,1,2,3,5,6,"rinvoice");
    ArrayList<String> correction = parseReport(14,0,1,4,5,10,10,"correction");
    ArrayList<String> adjust = parseReport(18,0,4,7,8,11,11,"adjust");
    ArrayList<String> o1cf = parseReport(22,1,2,8,5,44,46,"o1cf");
    ArrayList<String> cdata = parseReport(36,0,1,2,3,7,7,"cdata");
    */

    //ArrayList<String> sheet_names = {"Uninv Opening Position","Uninv Closing Position","Debtor Reconciled Invoices","Uninv Debtor Data Corrections","Uninv Debtor Adjustments","Uninv One1Clear Features","Debtor Control Data"};
    String sheet_names[] = { "Uninv Opening Position", "Uninv Closing Position", "Debtor Reconciled Invoices",
            "Uninv Debtor Data Corrections", "Uninv Debtor Adjustments", "Uninv One1Clear Features",
            "Debtor Control Data" };
    int sheet_no;
    for (String str : sheet_names) {
        sheet_no = workbook.getSheetIndex(str);
        Sheet wb_sheet = workbook.getSheetAt(sheet_no);

        String sheet_name = str;
        Iterator<Row> iterator = wb_sheet.iterator();

        String rec = "";
        String pay = "";
        String per = "";
        String svc = "";
        double dval = 0;
        double cval = 0;
        String rpps = "";
        int j = 0;

        while (iterator.hasNext()) {
            j++;

            System.out.println(sheet_name + "----->row" + j);
            if (j == 10) {
                j = 0;
                break;
            }
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            dval = 0;
            cval = 0;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() == 1 || cell.getColumnIndex() == 2 || cell.getColumnIndex() == 5
                        || cell.getColumnIndex() == 8 || cell.getColumnIndex() == 44
                        || cell.getColumnIndex() == 46) {

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:

                        if (cell.getColumnIndex() == 1) {
                            rec = cell.getStringCellValue();
                        }
                        if (cell.getColumnIndex() == 2) {
                            pay = cell.getStringCellValue();
                        }
                        if (cell.getColumnIndex() == 5) {
                            svc = cell.getStringCellValue();
                        }
                        if (cell.getColumnIndex() == 8) {
                            per = cell.getStringCellValue();
                        }

                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        //System.out.print(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        //System.out.print(cell.getNumericCellValue());
                        if (cell.getColumnIndex() == 44) {
                            dval = cell.getNumericCellValue();
                        }
                        if (cell.getColumnIndex() == 46) {
                            cval = cell.getNumericCellValue();
                        }
                        break;
                    }

                }

            }
            if (rec.length() == 5 || rec.length() == 8) {
                rpps = rec + "-" + pay + "-" + per + "-" + svc;
                System.out.println(rpps + "|" + dval + "|" + cval);
                //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
                //System.out.println();
                // ADD insert Query to Array 
                //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            }
        }

    }

}

From source file:javaapp.ParseCreditorTransactionsData.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "S9/GBRCNCOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();//from ww w . j  a  v a 2 s.c  om
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 32 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 32) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {

                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("") || filter.equalsIgnoreCase("-")) {
                    System.out.print(rpps);
                    System.out.println(filter);
                    continue;
                }

                System.out.print(filter);
                System.out.println(rpps);

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }
    }

    workbook.close();
    inputStream.close();

    return ar;

}

From source file:javaapp.ParseCreditorTransactionsDataEE.java

public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6,
        String tbl) throws IOException {

    String excelFilePath = "EE/GBRMECOR.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    ArrayList<String> ar = new ArrayList<String>();
    String sqlstr = "";

    Workbook workbook = new XSSFWorkbook(inputStream);
    //Sheet uninv_open = workbook.getSheetAt(sno);  
    Sheet uninv_open = workbook.getSheet(name);
    //String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl);
    Iterator<Row> iterator = uninv_open.iterator();

    String rec = "";
    String pay = "";
    String per = "";
    String svc = "";
    double dval = 0;
    double cval = 0;
    String rpps = "";
    String filter = "";

    while (iterator.hasNext()) {

        rec = "";
        pay = "";

        Row nextRow = iterator.next();//from   www .  j  a v  a2  s. co m
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        dval = 0;
        cval = 0;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2
                    || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5
                    || cell.getColumnIndex() == c6) {

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:

                    if (cell.getColumnIndex() == c1) {
                        rec = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c2) {
                        pay = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c3) {
                        svc = cell.getStringCellValue();
                    }
                    if (cell.getColumnIndex() == c4) {
                        per = cell.getStringCellValue();
                    }

                    if (cell.getColumnIndex() == 23) {
                        filter = cell.getStringCellValue();
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.print(cell.getNumericCellValue());
                    if (cell.getColumnIndex() == c5) {
                        dval = cell.getNumericCellValue();
                    }
                    if (cell.getColumnIndex() == c6) {
                        cval = cell.getNumericCellValue();
                    }
                    break;
                }

            }

        }
        if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(rpps);
            //System.out.println("--->"+tbl);

            if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) {
                if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled")
                        || filter.equalsIgnoreCase("")) {
                    continue;
                }

            }

            // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){
            //System.out.println(rpps+"|"+dval+"|"+cval);
            //}
            //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
            //System.out.println();
            // ADD insert Query to Array 
            sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")";
            ar.add(sqlstr);
            //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")");
        }

    }

    workbook.close();
    inputStream.close();

    return ar;

}