Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

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

    /*/*from  w w w .  ja v a 2s.c  om*/
    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.SimpleExcelReaderExample.java

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

    String excelFilePath = "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.getSheetAt(sno);
    String sname = workbook.getSheetName(sno);
    System.out.println("Parsing Sheet Number ---> " + sno + "---> Name : " + sname + " ---> " + 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();/*from   w ww  .  j a va 2 s  .c om*/
        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) {
            rpps = rec + "-" + pay + "-" + per + "-" + svc;
            //System.out.print(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:joinery.impl.Serialization.java

License:Open Source License

public static DataFrame<Object> readXls(final InputStream input) throws IOException {
    final Workbook wb = new HSSFWorkbook(input);
    final Sheet sheet = wb.getSheetAt(0);
    final List<Object> columns = new ArrayList<>();
    final List<List<Object>> data = new ArrayList<>();

    for (final Row row : sheet) {
        if (row.getRowNum() == 0) {
            // read header
            for (final Cell cell : row) {
                columns.add(readCell(cell));
            }/*  ww  w. j  av  a2  s .c  o  m*/
        } else {
            // read data values
            final List<Object> values = new ArrayList<>();
            for (final Cell cell : row) {
                values.add(readCell(cell));
            }
            data.add(values);
        }
    }

    // create data frame
    final DataFrame<Object> df = new DataFrame<>(columns);
    for (final List<Object> row : data) {
        df.append(row);
    }

    return df.convert();
}

From source file:jp.qpg.ExcelTo.java

License:Apache License

/**
 * excel to pdf/*  ww w.  j a  va 2  s. c o  m*/
 * 
 * @param book excel workbook
 * @param out output to pdf
 * @param documentSetup document setup
 * @throws IOException I/O exception
 */
public static void pdf(Workbook book, OutputStream out, Consumer<PDFPrinter> documentSetup) throws IOException {
    Objects.requireNonNull(book);
    Objects.requireNonNull(out);
    try (PDFPrinter printer = new PDFPrinter()) {
        printer.documentSetup = Optional.ofNullable(documentSetup);
        for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) {
            Sheet sheet = book.getSheetAt(i);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= 0) {
                logger.info(sheet.getSheetName() + ": empty");
                continue; /* skip blank sheet */
            }
            logger.info(sheet.getSheetName() + ": " + rowCount + " rows");
            printer.println("sheet name: " + sheet.getSheetName());
            printer.println("max row index: " + sheet.getLastRowNum());
            printer.println("max column index: "
                    + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0));
            eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println(
                    '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString())
                            + "] " + value)));
            eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text)));
            printer.newPage();
        }
        printer.getDocument().save(out);
    }
}

From source file:jp.qpg.ExcelTo.java

License:Apache License

/**
 * excel to text//from w ww . ja v a 2 s.  c  o m
 * 
 * @param book excel workbook
 * @param out output to text
 */
public static void text(Workbook book, OutputStream out) {
    Objects.requireNonNull(book);
    Objects.requireNonNull(out);
    try (PrintStream printer = Try.to(() -> new PrintStream(out, true, System.getProperty("file.encoding")))
            .get()) {
        for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) {
            Sheet sheet = book.getSheetAt(i);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= 0) {
                logger.info(sheet.getSheetName() + ": empty");
                continue; /* skip blank sheet */
            }
            logger.info(sheet.getSheetName() + ": " + rowCount + " rows");
            printer.println("sheet name: " + sheet.getSheetName());
            printer.println("max row index: " + sheet.getLastRowNum());
            printer.println("max column index: "
                    + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0));
            eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println(
                    '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString())
                            + "] " + value)));
            sheet.getCellComments().entrySet().forEach(entry -> {
                printer.println("[comment " + entry.getKey() + "] " + entry.getValue().getString());
            });
            eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text)));
            printer.println("--------");
        }
    }
}

From source file:kaflib.utils.FileUtils.java

License:Open Source License

/**
 * Reads a spreadsheet to a set of matrices (one per worksheet).
 * @param file/*w  ww.  j  a v  a 2s  .  co  m*/
 * @return
 * @throws Exception
 */
public static Map<String, Matrix<String>> readXLSX(final File file, final boolean columnTitles)
        throws Exception {
    Map<String, Matrix<String>> matrices = new HashMap<String, Matrix<String>>();
    Workbook workbook = null;
    workbook = new XSSFWorkbook(new FileInputStream(file));

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        String name = workbook.getSheetName(i);
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        Matrix<String> matrix = new Matrix<String>();

        int start = 0;
        if (columnTitles) {
            Row row = sheet.getRow(0);
            if (row != null) {
                List<String> labels = new ArrayList<String>();
                for (int k = 0; k < row.getLastCellNum(); k++) {
                    labels.add(row.getCell(k).toString());
                }
                matrix.setColumnLabels(labels);
            }
            start = 1;
        }
        for (int j = start; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            for (int k = 0; k <= row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    matrix.set(j - start, k, cell.toString());
                }
            }
        }
        matrices.put(name, matrix);
    }
    workbook.close();
    return matrices;
}

From source file:ket_noi_DB.ket_noi_kh.java

public void loadFile(Workbook workbook, String tenCSDL) {
    Sheet sheet = workbook.getSheetAt(0);
    int num = 0;/*from   w  w  w  .  j av  a2s  .  c o  m*/
    for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
        String sql = "INSERT INTO " + tenCSDL + " (makh, tenkh, sdt, diachi, tichdiem) VALUES (";
        Row row = rit.next();
        for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
            Cell cell = cit.next();
            cell.setCellType(Cell.CELL_TYPE_STRING);
            sql = sql + "'" + cell.getRichStringCellValue().toString() + "'" + ",";
        }
        sql = sql.substring(0, sql.length() - 1);
        sql = sql + ")";
        try {
            statement.executeUpdate(sql);
        } catch (SQLException ex) {
            // li lnh truy vn, do trng lp m khch hng
            num++;
        }
    }
    JOptionPane.showMessageDialog(null,
            "? thm d liu, c " + num + " m khch hng b trng khng c thm");
}

From source file:ket_noi_excel.ket_noi_excel_cd.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {/*from ww  w  .j  a v a2 s. c  om*/
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableCD
                    + " (`Tn SP`, `M SP`, `Tn Ca S`, `Tn Nhc S`, "
                    + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)"
                    + " VALUES (?,?,?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else if (i == 6 || i == 11)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}

From source file:ket_noi_excel.ket_noi_excel_dvd.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {//from   ww w  .ja va  2 s  .  c om
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableDVD
                    + " (`Tn SP`, `M SP`, `Tn Din Vin`, `Tn ?o Din`, "
                    + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)"
                    + " VALUES (?,?,?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else if (i == 6 || i == 11)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}

From source file:ket_noi_excel.ket_noi_excel_sach.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {// w ww.  j  a v  a  2  s.c om
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableSach + " (`Tn SP`, `M SP`,"
                    + " `Tn T/G`, `Th Loi`,`Gi Bn`, `S Lng`, `Ngy Nhp`,  "
                    + "`S Phiu`, `Chit Khu`) VALUES (?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 6 || i == 9)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}