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:math.page.KnapsackTest.java

License:Apache License

public static void test3() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "price.xlsx";
    File file = new File(path);
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);
    List<Knapsack> bags = new ArrayList<Knapsack>();
    try {/*from   w ww  .  j a v a 2s  . co m*/
        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            // 
            // System.out.print(cell.toString() + "  ");
            // System.out.println(cell0.getCellComment().toString());

            // Integer integer = Double.valueOf(cell0.getNumericCellValue())
            // .intValue();
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }
            Knapsack knapsack = new Knapsack(integer, integer);
            knapsack.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());

            bags.add(knapsack);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    List<Total> list = test4();

    write(list, bags);

}

From source file:math.page.KnapsackTest.java

License:Apache License

public static List<Total> test4() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "total.xlsx";
    File file = new File(path);
    // Workbook workbook = Workbook.getWorkbook(file);
    Workbook workbook = WorkbookFactory.create(file);
    // Sheet sheet = workbook.getSheet(0);
    Sheet sheet = workbook.getSheetAt(0);
    List<Total> bags = new ArrayList<Total>();
    try {/*from   w w  w  . java2  s.  c  om*/

        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            // Cell[] cells = sheet.getRow(row);
            // System.out.println(cells[0].getContents());
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }

            Total total = new Total();
            total.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());
            total.setTotal(integer);
            bags.add(total);
        }
        Arrays.sort(bags.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }

    return bags;
}

From source file:model.ReadExcel.java

public void readExcelFileforImportingIssuedChecks(String excelFilePath, int colsExcel[], int startingRow) {

    try {//from ww w  .j  a  v  a2  s .c o m

        int validData = 0;
        Check check = new Check();
        queryData cd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            for (int i = 0; i < colsExcel.length; i++) {

                check.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

            }

            validData++;
            cd.insertData(check);

        }

        if (validData > 1)
            JOptionPane.showMessageDialog(null,
                    "There are " + rowEnd + " new issued checks for " + Global.getBranchName());
        else
            JOptionPane.showMessageDialog(null,
                    "There is " + rowEnd + " new issued check for " + Global.getBranchName());

    }

    catch (Exception ex) {

        JOptionPane.showMessageDialog(null, ex.toString() + rowStart);

    }

}

From source file:model.ReadExcel.java

public void readExcelFileforImportingClaimedChecks(String excelFilePath, int colsExcel[], int startingRow) {

    try {/*from  www.  j a  v a 2  s .c  om*/

        validation vd = new validation();
        ImportClaim importclaim = new ImportClaim();
        queryData cd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            if (r1.getCell(colsExcel[3], Row.RETURN_BLANK_AS_NULL).getStringCellValue() != null) {

                for (int i = 0; i < colsExcel.length; i++) {

                    importclaim.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

                }

                if (vd.validateClaim(importclaim.getCheckNum(), importclaim.getCheckAmount())) {

                    validImport++;
                    cd.updateClaimedfromImport(importclaim);

                }

            } else {

                return;
            }

        }

    }

    catch (Exception ex) {

        // JOptionPane.showMessageDialog(null,ex.toString() + rowStart);

    } finally {

        if (validImport > 0) {
            JOptionPane.showMessageDialog(null,
                    "Successfully tagged " + validImport + " check numbers for claimed status..");
        } else {
            JOptionPane.showMessageDialog(null,
                    "No data has been processed, please check your excel template..");
        }

    }

}

From source file:model.ReadExcel.java

public void readExcelFileforImportingReceivedByUCC(String excelFilePath, int colsExcel[], int startingRow) {

    try {// www .j  a v a  2  s  .c o m

        int validData = 0;
        ReceivedByUCC rb = new ReceivedByUCC();
        queryData qd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            for (int i = 0; i < colsExcel.length; i++) {

                rb.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

            }

            qd.updateStatusToReceivedByUCC(rb);
            validData++;

        }

        if (validData > 1)
            JOptionPane.showMessageDialog(null, "UCC received " + rowEnd + " checks..");
        else
            JOptionPane.showMessageDialog(null, "UCC received " + rowEnd + " check..");

    }

    catch (Exception ex) {

        JOptionPane.showMessageDialog(null, ex.toString() + rowStart);

    }

}

From source file:naivebayesimplementation.NaiveBayesClassifier.java

License:Open Source License

public void ReadTrainingExamples(Map<String, String[]> trainingExamples) {
    String excelFilePath = "resources/topics/AssignedTopics.xlsx";
    FileInputStream inputStream;//from  w w  w  .j  a  va 2s.c o  m
    try {

        inputStream = new FileInputStream(new File(excelFilePath));

        Workbook workbook = new XSSFWorkbook(inputStream);

        Sheet firstSheet = workbook.getSheetAt(0);

        String[] emtpyArray = new String[1];
        emtpyArray[0] = " ";

        // Using for Topics that don't fit any of the topics
        //trainingExamples.put("Another Topic", emtpyArray );

        for (int row = 0; row < 60; row++) {
            String topicName = firstSheet.getRow(row).getCell(1).toString();
            String topicWords = firstSheet.getRow(row).getCell(2).toString();

            String[] topicWordsArray = topicWords.split("\\s");

            trainingExamples.put(topicName, topicWordsArray);

        }

        // Saving this topics for scoring
        allTrainingTopics = trainingExamples;

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

    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:nc.noumea.mairie.appock.util.StockSpreadsheetExporter.java

License:Open Source License

private static void autosize(Workbook workbook) {
    Row row = workbook.getSheetAt(0).getRow(1);

    for (int colNum = 1; colNum < row.getLastCellNum() - 1; colNum++)
        workbook.getSheetAt(0).autoSizeColumn(colNum);

    int appockStockColumnWidth = workbook.getSheetAt(0).getColumnWidth(row.getLastCellNum() - 1);
    workbook.getSheetAt(0).setColumnWidth(row.getLastCellNum(), appockStockColumnWidth);

}

From source file:net.ceos.project.poi.annotated.core.MaskTest.java

License:Apache License

/**
 * //from   w  ww .j  a v a 2  s  .com
 * Test the marshal/unmarshal of one object applying mask at the Date
 * attribute with {@link XlsElement}
 */
@Test(dataProvider = "dataProvider")
public void checkDateFormatMask(ObjectMask charger, Workbook wb) throws Exception {
    // format date attributes
    Cell cellDate1 = extractCell(charger, wb.getSheetAt(0), 2);
    assertEquals(cellDate1.getDateCellValue(), charger.getDateAttribute2());

    Cell cellDate2 = extractCell(charger, wb.getSheetAt(0), 3);
    assertEquals(cellDate2.getDateCellValue(), charger.getDateAttribute3());

    Cell cellDate3 = extractCell(charger, wb.getSheetAt(0), 4);
    assertEquals(cellDate3.getDateCellValue(), charger.getDateAttribute4());
}

From source file:net.ceos.project.poi.annotated.core.MaskTest.java

License:Apache License

/**
 * //w  w  w .j a  v a2s  .c  o m
 * Test the marshal/unmarshal of one object applying mask at the Date
 * attribute with {@link XlsElement}
 */
@Test(dataProvider = "dataProvider")
public void checkDateTransformMask(ObjectMask charger, Workbook wb) throws Exception {
    // transform date attributes
    Cell cellDate1 = extractCell(charger, wb.getSheetAt(0), 5);
    validateDate(parserDate(cellDate1.getStringCellValue(), "yyyy-MM-dd"), charger.getDateAttribute5(), true,
            true, true);

    Cell cellDate2 = extractCell(charger, wb.getSheetAt(0), 6);
    validateDate(parserDate(cellDate2.getStringCellValue(), "yyMM"), charger.getDateAttribute6(), false, true,
            true);

    Cell cellDate3 = extractCell(charger, wb.getSheetAt(0), 7);
    validateDate(parserDate(cellDate3.getStringCellValue(), "yyyy"), charger.getDateAttribute7(), false, false,
            true);
}

From source file:net.ceos.project.poi.annotated.core.MaskTest.java

License:Apache License

/**
 * /* ww w .  j  a va 2s .co  m*/
 * Test the marshal/unmarshal of one object applying mask at the Double
 * attribute with {@link XlsElement}
 */
@Test(dataProvider = "dataProvider")
public void checkDoubleFormatMask(ObjectMask charger, Workbook wb) throws Exception {
    // format double attributes
    Cell cellDouble1 = extractCell(charger, wb.getSheetAt(0), 9);
    assertEquals(Double.valueOf(cellDouble1.getNumericCellValue()), charger.getDoubleAttribute2());

    Cell cellDouble2 = extractCell(charger, wb.getSheetAt(0), 10);
    assertEquals(Double.valueOf(cellDouble2.getNumericCellValue()), charger.getDoubleAttribute3());
}