Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

Introduction

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

Prototype

@Override
public XSSFRow getRow(int rownum) 

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:sv.com.mined.sieni.controller.GestionNotasController.java

public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum,
        int destinationRowNum) {
    // Get the source / new row
    XSSFRow origen = worksheetSource.getRow(sourceRowNum);
    XSSFRow destino = worksheetDestination.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < origen.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = origen.getCell(i);
        XSSFCell newCell = destino.createCell(i);
        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;/*from w  w w  .  j  a v a2 s  .  c  om*/
            continue;
        }

        //Ajustar tamaos columnas
        worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i));

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());
        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

}

---

From source file:tan.jam.jsf.OrignalFileDownloadBean.java

public void downloadHandler(ActionEvent e) {

    System.out.println("download click handler");
    try {/*from ww w  . j  a v a2 s.  c  o  m*/
        InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext()
                .getContext()).getResourceAsStream("/resources/demo/file/Table.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(stream));
        XSSFSheet sheet = wb.getSheetAt(0);
        wb.setForceFormulaRecalculation(true);

        /* 
         */

        String name;
        String acc;
        HashSet OnlineTransferToAccounts = new HashSet();
        int RowForOnlineTransferT0 = 105;
        int RowForRecurringDailyPayment = 105;
        List<String> LenderDescription = new ArrayList<>();
        List<String> LenderDate = new ArrayList<>();
        List<Double> LenderAmount = new ArrayList<>();
        List<Double> UniqueDailypaymentkeywordAmount = new ArrayList<>();
        List<String> UniqueDailypaymentkeywordDate = new ArrayList<>();
        List<String> UniqueDailypaymentkeywordDescription = new ArrayList<>();

        for (int a = 0; a < companies.size(); a++) {
            Company c = companies.get(a);
            name = c.getCompanyName();
            acc = c.getAccountNumber();
            String month = c.getDate().toLowerCase();
            int R = 3;
            if (month.startsWith(("jan"))) {
                R = 3;
            } else if (month.startsWith("feb")) {
                R = 4;
            } else if (month.startsWith("mar")) {
                R = 5;
            } else if (month.startsWith("apr")) {
                R = 6;
            } else if (month.startsWith("may")) {
                R = 7;
            } else if (month.startsWith("jun")) {
                R = 8;
            } else if (month.startsWith("jul")) {
                R = 9;
            } else if (month.startsWith("aug")) {
                R = 10;
            } else if (month.startsWith("sep")) {
                R = 11;
            } else if (month.startsWith("oct")) {
                R = 12;
            } else if (month.startsWith("nov")) {
                R = 13;
            } else if (month.startsWith("dec")) {
                R = 14;
            } else if (month.startsWith("02") | month.startsWith("2")) {
                R = 4;
            } else if (month.startsWith("03") | month.startsWith("3")) {
                R = 5;
            } else if (month.startsWith("04") | month.startsWith("4")) {
                R = 6;
            } else if (month.startsWith("05") | month.startsWith("5")) {
                R = 7;
            } else if (month.startsWith("06") | month.startsWith("6")) {
                R = 8;
            } else if (month.startsWith("07") | month.startsWith("7")) {
                R = 9;
            } else if (month.startsWith("08") | month.startsWith("8")) {
                R = 10;
            } else if (month.startsWith("09") | month.startsWith("9")) {
                R = 11;
            } else if (month.startsWith("10") | month.startsWith("10")) {
                R = 12;
            } else if (month.toUpperCase().startsWith("11")) {
                R = 13;
            } else if (month.toUpperCase().startsWith("12")) {
                R = 14;
            }
            ////%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%       %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%////

            sheet.getRow(1).getCell(1).setCellValue("Bank Account**" + acc);
            if (a == 0) {
                sheet.getRow(1).getCell(7).setCellValue(name + "**" + acc);
                sheet.getRow(1).getCell(12).setCellValue(name + "**" + acc);
                sheet.getRow(R).getCell(7)
                        .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-"
                                + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")");
                // int Mov=Shifting.FindShift(wb, sheet); 
                sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance());
                sheet.getRow(R).getCell(13).setCellValue(c.getMinValue());
                sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues());
                // int Onl=Shifting.Online(wb, sheet);

            } else {
                int Decision = 0;
                String Name = name + "**" + acc;
                for (int s = 0; s < Shifting.FindShift(wb, sheet) + 1; s++) {
                    if (sheet.getRow(1).getCell(7 + s).getStringCellValue().equals(Name)) {
                        Decision = 1;
                        sheet.getRow(R).getCell(7 + s)
                                .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom()
                                        + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")");
                    }
                }
                if (Decision == 0) {
                    int Mov = Shifting.FindShift(wb, sheet);
                    Shifting.InsertColumn(wb, sheet);
                    sheet.getRow(1).getCell(8 + Mov).setCellValue(name + "**" + acc);
                    sheet.getRow(R).getCell(8 + Mov)
                            .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-"
                                    + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")");
                }
                int selectcolumn = Shifting.FindShift(wb, sheet) + 12;
                if (sheet.getRow(1).getCell(selectcolumn).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn).getStringCellValue().equals(Name)) {

                } else if (sheet.getRow(1).getCell(selectcolumn + 6).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 6;
                } else if (sheet.getRow(1).getCell(selectcolumn + 12).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 12;
                } else if (sheet.getRow(1).getCell(selectcolumn + 18).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 18;
                } else if (sheet.getRow(1).getCell(selectcolumn + 24).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 24;
                } else if (sheet.getRow(1).getCell(selectcolumn + 30).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 30;
                } else if (sheet.getRow(1).getCell(selectcolumn + 36).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 36;
                } else if (sheet.getRow(1).getCell(selectcolumn + 42).getCellType() == 3
                        || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().isEmpty()
                        || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().equals(Name)) {
                    selectcolumn = selectcolumn + 42;
                }

                sheet.getRow(1).getCell(selectcolumn).setCellValue(name + "**" + acc);
                sheet.getRow(R).getCell(selectcolumn).setCellValue(c.getAverageLedgerBalance());
                sheet.getRow(R).getCell(selectcolumn + 1).setCellValue(c.getMinValue());
                sheet.getRow(R).getCell(selectcolumn + 2).setCellValue(c.getNoOfNegativeValues());
            }
            /*
            // int Mov=Shifting.FindShift(wb, sheet);
            sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance());
            sheet.getRow(R).getCell(13).setCellValue(c.getMinValue());
            sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues());
            // int Onl=Shifting.Online(wb, sheet); */

            ///%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%/////
            for (int b = 0; b < c.getOnlineTransferToDate().size(); b++) {
                sheet.getRow(RowForOnlineTransferT0).getCell(9)
                        .setCellValue(c.getOnlineTransferToDate().get(b));
                sheet.getRow(RowForOnlineTransferT0).getCell(10)
                        .setCellValue(c.getOnlineTransferToDescription().get(b));
                sheet.getRow(RowForOnlineTransferT0).getCell(12)
                        .setCellValue(c.getOnlineTransferToAmount().get(b));
                RowForOnlineTransferT0++;
            }
            for (int L = 0; L < c.getKeywordAmount().size(); L++) {
                int index = 0;
                if (LenderDescription.contains(c.getKeywordDescription().get(L))) {
                    index = LenderDescription.indexOf(c.getKeywordDescription().get(L));
                    LenderDate.set(index, c.getKeywordDate().get(L));
                    LenderAmount.set(index, c.getKeywordAmount().get(L));
                } else {
                    LenderDescription.add(c.getKeywordDescription().get(L));
                    LenderDate.add(c.getKeywordDate().get(L));
                    LenderAmount.add(c.getKeywordAmount().get(L));
                }

            }
            for (int TT = 0; TT < c.getOnlineTransferToCheckNoDuplicateRemoved().size(); TT++) {
                OnlineTransferToAccounts.add(c.getOnlineTransferToCheckNoDuplicateRemoved().get(TT));
            }
            if (!name.equals("BlackHills")) {
                for (int U = 0; U < c.getDailypaymentKeywordDescription().size(); U++) {
                    sheet.getRow(RowForRecurringDailyPayment).getCell(14)
                            .setCellValue(c.getDailypaymentKeywordDate().get(U));
                    sheet.getRow(RowForRecurringDailyPayment).getCell(15)
                            .setCellValue(c.getDailypaymentKeywordDescription().get(U));
                    sheet.getRow(RowForRecurringDailyPayment).getCell(17)
                            .setCellValue(c.getDailypaymentKeywordAmount().get(U));
                    RowForRecurringDailyPayment++;

                    if (!UniqueDailypaymentkeywordDescription
                            .contains(c.getDailypaymentKeywordDescription().get(U))) {
                        UniqueDailypaymentkeywordAmount.add(c.getDailypaymentKeywordAmount().get(U));
                        UniqueDailypaymentkeywordDate.add(c.getDailypaymentKeywordDate().get(U));
                        UniqueDailypaymentkeywordDescription.add(c.getDailypaymentKeywordDescription().get(U));
                    }
                }
            }

        }
        for (int LL = 0; LL < LenderDescription.size(); LL++)
            if (25 + LL < 31) {
                { // if(25+LL > 29)
                    {
                        //Shifting.InsertRow(wb, sheet,25+LL,26+LL);
                    }
                    sheet.getRow(LL + 25).getCell(1).setCellValue(LenderDate.get(LL));
                    sheet.getRow(LL + 25).getCell(2).setCellValue(LenderDescription.get(LL));
                    sheet.getRow(LL + 25).getCell(5).setCellValue(LenderAmount.get(LL));
                }
            }
        List<String> OnlineTransferToUniqueAccounts = new ArrayList<>(OnlineTransferToAccounts);
        for (int TT = 0; TT < OnlineTransferToUniqueAccounts.size(); TT++) {
            int Mov = Shifting.FindShift(wb, sheet);
            Shifting.InsertColumn(wb, sheet);
            sheet.getRow(1).getCell(8 + Mov).setCellValue("**" + OnlineTransferToUniqueAccounts.get(TT));
        }
        //**************************  Daily Payment portion  ******************************************//

        for (int D = 0; D < UniqueDailypaymentkeywordDescription.size(); D++) {
            for (int r = 25; r < 31; r++) {
                if (sheet.getRow(r).getCell(2).getCellType() == 3) {
                    sheet.getRow(r).getCell(1).setCellValue(UniqueDailypaymentkeywordDate.get(D));
                    sheet.getRow(r).getCell(2).setCellValue(UniqueDailypaymentkeywordDescription.get(D));
                    sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D));
                    sheet.getRow(r).getCell(0).setCellValue("OPEN");
                    break;
                } else if (sheet.getRow(r).getCell(2).getStringCellValue()
                        .equals(UniqueDailypaymentkeywordDescription.get(D))) {
                    sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D));
                    sheet.getRow(r).getCell(0).setCellValue("OPEN");
                    break;
                }

            }
        }

        Shifting.InsertFormulas(wb, sheet);
        Shifting.MergeCells(wb, sheet);
        // Write output to a file

        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
        InputStream inp = new FileInputStream("workbook.xlsx");
        file = new DefaultStreamedContent(inp, "application/vnd.ms-excel", "Table_downloaded.xlsx");
    } catch (IOException ex) {
        Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex);
    } catch (InvalidFormatException ex) {
        Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:tan.jam.jsf.Shifting.java

public static int FindShift(XSSFWorkbook workbook, XSSFSheet worksheet) {
    String s = "TOTAL BANKS";
    XSSFRow Row = worksheet.getRow(2);
    for (int i = 9; i < 26; i++) {
        XSSFCell cell = Row.getCell(i);// w w w. j  a  v  a 2s .  co m
        String g = cell.getStringCellValue();
        if (g.equals("TOTAL BANKS")) {
            return i - 9;
        }
    }
    return 0;
}

---

From source file:tan.jam.jsf.Shifting.java

public static int Online(XSSFWorkbook workbook, XSSFSheet worksheet) {
    for (int i = 104; i < 300; i++) {
        XSSFCell c = worksheet.getRow(i).getCell(9);
        if (c == null || c.getCellType() == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK) {
            return i;
        }/*from   w w  w.  ja va2  s  . com*/
    }
    return 104;
}

---

From source file:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {//www.  ja v  a2s.co  m
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}

---

From source file:tan.jam.jsf.Shifting.java

public static void InsertColumn(XSSFWorkbook workbook, XSSFSheet worksheet) {
    int Mov = FindShift(workbook, worksheet);
    for (int i = 1; i < 16; i++) {
        CopyData(workbook, worksheet, i, i, Mov);
    }/* w  w w  .j a  v  a  2 s  .co m*/
    char Ch = 'i';
    int Asc = Ch + Mov + 1;
    worksheet.getRow(15).getCell(8 + Mov + 1)
            .setCellFormula("AVERAGE(" + (char) Asc + "4:" + (char) Asc + "15)");
}

---

From source file:tan.jam.jsf.Shifting.java

private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum, int Mov) {
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) {

        int d = i - 1;
        XSSFCell oldCell = sourceRow.getCell(d);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;/*w w w  .j  a  v a2  s.  co m*/
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

---

From source file:tan.jam.jsf.Shifting.java

public static void InsertFormulas(XSSFWorkbook workbook, XSSFSheet worksheet) {
    int Mov = FindShift(workbook, worksheet);
    char Chr = 'i';
    int Asci = Chr + Mov;
    for (int i = 3; i < 15; i++) {
        int I = i + 1; //System.out.println("Cell Type "+worksheet.getRow(i).getCell(7).getCellType());
        for (int s = 7; s <= 7 + Mov; s++) {
            if (worksheet.getRow(i).getCell(s).getCellType() == 2) {
                worksheet.getRow(i).getCell(9 + Mov).setCellFormula("sum(H" + I + ":" + (char) Asci + I + ")");
                break;
                // System.out.println("sum(H"+I+":"+(char)Asci+I+")");
            }//from ww  w .  ja va 2 s  .c o m
        }
    }
    char ch = 'j';
    int C = ch + Mov;
    worksheet.getRow(15).getCell(9 + Mov).setCellFormula("AVERAGE(" + (char) C + "4:" + (char) C + "15)");
    worksheet.getRow(31).getCell(8).setCellFormula("+(J32*21+H24)/" + (char) C + "16");
}

---

From source file:testpoi.ReadExcelRowsRandomly.java

License:Open Source License

public static void main(String args[]) {
    try {//from w ww .ja v  a  2s .  c om

        FileInputStream file = new FileInputStream(new File(
                "C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\1.xlsx"));

        //Get the workbook instance for XLS file 
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        double random = Math.random();
        int rowNum = (int) (random * sheet.getPhysicalNumberOfRows());

        Row row = sheet.getRow(rowNum);

        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                double cellValue = cell.getNumericCellValue();
                System.out.print(cellValue + "\t");
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + "\t");
            }
        }
        System.out.println();

    } catch (Exception e) {
        e.printStackTrace();
    }
}

---

From source file:Tools.CompareDrotVSRoster.java

public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException {
    File xlsxFile = new File(leaveXlsxRoster);
    try {//  w w  w  . ja v  a 2 s . c om
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            // List<String> keyList = new ArrayList<>(); //keep track info of each column
            while (it.hasNext()) {
                //keyList.add(it.next().getStringCellValue());   
                it.next();
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { // correct xlsx file                 
                int rowNum = 1;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    //row.getRowStyle();
                    rowNum++;
                }
                for (int i = 1; i < rowNum; i++) {
                    Row row = mySheet.getRow(i);
                    foregroundColorSetUpV1(row, myWorkBook); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;
                String targetFile = null;
                String[] names = leaveXlsxRoster.split("\\.");
                if (!names[0].equals(leaveXlsxRoster)) { //doesn't contain surfix
                    targetFile = names[0] + "COLORED.xlsx";
                } else {
                    targetFile = leaveXlsxRoster + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();
                //myWorkBook.write(output);

            } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { // full ssn roster
                System.out.println("CompareDrotVsRoster.java: Unsupported.");
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info! "
                        + "CompareDrotVsRoster.java: Title V2 is not supported");
            } else {
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.");
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "Xlsx file not found!");
    }

}

---

• «
• 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
• »