Example usage for org.apache.poi.ss.usermodel Sheet getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to hide the duplicate values,
 * and make the list easier to read. In this example, when the table is sorted by Region,
 * the second (and subsequent) occurences of each region name will have white font colour.
 *///  w  ww  .ja  v  a2  s .co  m
static void hideDupplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("City");
    sheet.createRow(1).createCell(0).setCellValue("Boston");
    sheet.createRow(2).createCell(0).setCellValue("Boston");
    sheet.createRow(3).createCell(0).setCellValue("Chicago");
    sheet.createRow(4).createCell(0).setCellValue("Chicago");
    sheet.createRow(5).createCell(0).setCellValue("New York");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("A2=A1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A6") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(1).createCell(1)
            .setCellValue("<== the second (and subsequent) "
                    + "occurences of each region name will have white font colour.  "
                    + "Condition: Formula Is   =A2=A1   (White Font)");
}

---

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight duplicate entries in a column.
 *///from   w w w  .  j  a  va2s. co  m
static void formatDuplicates(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Code");
    sheet.createRow(1).createCell(0).setCellValue(4);
    sheet.createRow(2).createCell(0).setCellValue(3);
    sheet.createRow(3).createCell(0).setCellValue(6);
    sheet.createRow(4).createCell(0).setCellValue(3);
    sheet.createRow(5).createCell(0).setCellValue(5);
    sheet.createRow(6).createCell(0).setCellValue(8);
    sheet.createRow(7).createCell(0).setCellValue(0);
    sheet.createRow(8).createCell(0).setCellValue(2);
    sheet.createRow(9).createCell(0).setCellValue(8);
    sheet.createRow(10).createCell(0).setCellValue(6);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A11") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(1).setCellValue("<== Duplicates numbers in the column are highlighted.  "
            + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1   (Blue Font)");
}

---

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 * Use Excel conditional formatting to highlight items that are in a list on the worksheet.
 *///www. j  av a2  s.co  m
static void inList(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Codes");
    sheet.createRow(1).createCell(0).setCellValue("AA");
    sheet.createRow(2).createCell(0).setCellValue("BB");
    sheet.createRow(3).createCell(0).setCellValue("GG");
    sheet.createRow(4).createCell(0).setCellValue("AA");
    sheet.createRow(5).createCell(0).setCellValue("FF");
    sheet.createRow(6).createCell(0).setCellValue("XX");
    sheet.createRow(7).createCell(0).setCellValue("CC");

    sheet.getRow(0).createCell(2).setCellValue("Valid");
    sheet.getRow(1).createCell(2).setCellValue("AA");
    sheet.getRow(2).createCell(2).setCellValue("BB");
    sheet.getRow(3).createCell(2).setCellValue("CC");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A8") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(2).createCell(3).setCellValue(
            "<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}

---

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
 *  In this example, Due dates are entered in cells A2:A4.
 *///from  w ww .j  a va2  s  .com
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));

    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");

    for (int rownum = 1; rownum <= 3; rownum++)
        sheet.getRow(rownum).getCell(0).setCellStyle(style);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

---

From source file:com.wantdo.stat.excel.poi_src.formula.UserDefinedFunctionExample.java

License:Apache License

public static void main(String[] args) {

    if (args.length != 2) {
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;// w  w  w .ja  va  2 s  .c  om
    }

    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);

    File workbookFile = new File(args[0]);

    try {
        FileInputStream fis = new FileInputStream(workbookFile);
        Workbook workbook = WorkbookFactory.create(fis);
        fis.close();

        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };

        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);

        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);

        CellValue value = evaluator.evaluate(cell);

        System.out.println("returns value: " + value);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

---

From source file:com.waveconn.Excel2MySQL.java

License:Apache License

void dbImport() {
    FileInputStream excel_file = null;
    try {//from   ww w .  j  av  a  2  s. c  o m
        excel_file = new FileInputStream(new File(excel_file_path));
    } catch (FileNotFoundException e) {
        System.out.println("File not found: " + excel_file_path);
        System.exit(-3);
    }

    try {
        workbook = WorkbookFactory.create(excel_file);
        evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formatter = new DataFormatter(true);

        Sheet sheet = null;
        Row row = null;
        int lastRowNum = 0;

        System.out.println("Reading excel file content from " + excel_file_path);

        // Discover how many sheets there are in the workbook....
        int numSheets = workbook.getNumberOfSheets();

        // and then iterate through them.
        for (int i = 0; i < numSheets; i++) {

            // Get a reference to a sheet and check to see if it contains any rows.
            sheet = workbook.getSheetAt(i);
            if (sheet.getPhysicalNumberOfRows() > 0) {

                // Note down the index number of the bottom-most row and
                // then iterate through all of the rows on the sheet starting
                // from the very first row - number 1 - even if it is missing.
                // Recover a reference to the row and then call another method
                // which will strip the data from the cells and build lines
                lastRowNum = sheet.getLastRowNum();

                int start = 0;
                if (!is_read_first_line)
                    start = 1;

                for (int j = start; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    this.rowToData(row);
                }
            }
        }

    } catch (IOException e) {
        e.printStackTrace();
        System.out.println("IOException: " + excel_file_path);
        System.exit(-4);
    } catch (InvalidFormatException e) {
        e.printStackTrace();
        System.out.println("Invalid Format: " + excel_file_path);
        System.exit(-5);
    } finally {
        if (excel_file != null) {
            try {
                excel_file.close();
            } catch (IOException e) {
                e.printStackTrace();
                System.out.println("IOException: " + excel_file_path);
                System.exit(-6);
            }
        }
    }

    //put valid rows into DB
    System.out.println("Inserting valid rows into DB table " + db_url + "/" + db_table);
    insertDB();

    System.out.println();

    //save invalid rows if any
    int errs = errorRows.size();
    if (errs > 0) {
        saveError();
    } else {
        System.out.println("There is no invalid row");
    }
}

---

From source file:com.wuliu.biz.util.export.strategy.CarIndexExport.java

License:Open Source License

private void fillSheet(Sheet sheet, List<WuliuMergedOrderModel> mergedOrderModels) {
    int index = 0;
    for (WuliuMergedOrderModel item : mergedOrderModels) {
        index++;//ww  w .  j av  a 2 s .  c o m
        Row row = sheet.getRow(index);
        if (row == null) {
            row = sheet.createRow(index);
        }

        Cell cell = row.getCell(0);
        if (cell == null) {
            cell = row.createCell(0);
        }
        cell.setCellValue(item.getOrderNumber());
    }
}

---

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

private void fillSheet(Sheet sheet, List<WuliuMergedOrderModel> mergedOrderModels) {
    // file header
    if (sheet.getHeader() != null) {
        String right = sheet.getHeader().getRight();
        if (right != null && right.length() > 5) {
            right = right.substring(0, right.length() - 5) + "" + mergedOrderModels.get(0).getCarIndex()
                    + "";
            sheet.getHeader().setRight(right);
        }/* w  w  w .j a v  a  2 s . c o  m*/
    }

    // file name
    Row row3 = sheet.getRow(3);
    Cell nameCell = row3.getCell(3);
    if (nameCell == null) {
        nameCell = row3.createCell(3);
    }
    nameCell.setCellValue(mergedOrderModels.get(0).getName());

    Cell phoneCell = row3.getCell(6);
    if (phoneCell == null) {
        phoneCell = row3.createCell(6);
    }
    phoneCell.setCellValue(getPhoneNumber(mergedOrderModels.get(0)));

    // fill address
    Row row4 = sheet.getRow(4);
    Cell addressCell = row4.getCell(3);
    if (addressCell == null) {
        addressCell = row4.createCell(3);
    }
    addressCell.setCellValue(mergedOrderModels.get(0).getAddress());

    // fill send date
    Cell sendDateCell = row4.getCell(6);
    if (sendDateCell == null) {
        sendDateCell = row4.createCell(6);
    }
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
    sendDateCell.setCellValue("?" + sdf.format(mergedOrderModels.get(0).getSendDate()));

    int rowNum = 6;
    int index = 1;
    for (WuliuMergedOrderModel item : mergedOrderModels) {
        List<CellInfo> cellInfoList = convertCellInfoList(item);

        for (CellInfo cellInfo : cellInfoList) {

            Row row = sheet.getRow(rowNum);
            rowNum += 1;

            Cell cell = createCellIfNotExit(row, 1);
            cell.setCellValue(index);
            index += 1;

            if (cellInfo.getOrderNumber() != null) {
                cell = createCellIfNotExit(row, 2);
                cell.setCellValue(cellInfo.getOrderNumber());
            }

            if (cellInfo.getGuige() != null) {
                cell = createCellIfNotExit(row, 3);
                cell.setCellValue(cellInfo.getGuige());
            }

            if (cellInfo.getUnit() != null) {
                cell = createCellIfNotExit(row, 4);
                cell.setCellValue(cellInfo.getUnit());
            }

            if (cellInfo.getCount() != null) {
                cell = createCellIfNotExit(row, 5);
                cell.setCellValue(cellInfo.getCount());
            }

            if (cellInfo.getCost() != null) {
                cell = createCellIfNotExit(row, 7);
                cell.setCellValue(Double.valueOf(cellInfo.getCost()));
            }

            if (cellInfo.getComments() != null) {
                cell = createCellIfNotExit(row, 8);
                cell.setCellValue(cellInfo.getComments());
            }
        }
    }
}

---

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

private void evaluate(Sheet sheet, FormulaEvaluator evaluator) {
    for (int i = 0; i < ROW_MAX; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }/*  www .ja va  2s . com*/
        for (int j = 0; j < COL_MAX; j++) {
            Cell cell = row.getCell(j);
            if (cell == null) {
                continue;
            }

            if (cell.getCellTypeEnum() == CellType.FORMULA) {
                evaluator.evaluateFormulaCell(cell);
            }
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »