Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index)

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:com.nkapps.billing.services.BankStatementPrintServiceImpl.java

@Override
public HSSFWorkbook printPaymentManual(BankStatement bs) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "payment_manual.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);

    HSSFRow row = sheet.getRow(0);// w w w  .j  av a2s .  c  om
    row.getCell(1).setCellValue(bs.getId());

    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum();
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    row = sheet.createRow(3);

    createCell(row, 1, style, "");
    createCell(row, 2, style, bs.getMfo());
    createCell(row, 3, style, bs.getChet());
    createCell(row, 4, style, bs.getPaymentNum());
    createCell(row, 5, style, dateFormat.format(bs.getPaymentDate()));
    createCell(row, 6, style, "");
    createCell(row, 7, style, bs.getTin());

    return workbook;
}

From source file:com.nkapps.billing.services.PaymentServiceImpl.java

@Override
public void parseAndSavePaymentManual(File file, Long issuerSerialNumber, String issuerIp) throws Exception {
    FileInputStream fis = new FileInputStream(file);
    POIFSFileSystem fs = new POIFSFileSystem(fis);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);

    String bankStatementId = null;
    List<Payment> paymentList = new LinkedList<Payment>();
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");

    int rowCurrent = 0, cellCurrent = 0;
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        rowCurrent++;//from   ww w. j  av a  2s  . co  m
        cellCurrent = 0;

        Row row = rowIterator.next();

        if (rowCurrent == 1) { // bank statement id
            bankStatementId = row.getCell(1).getStringCellValue();

        } else if (rowCurrent > 3) {
            Payment payment = new Payment();
            payment.setTin(row.getCell(1).getStringCellValue());
            payment.setPaymentNum(row.getCell(4).getStringCellValue());
            payment.setPaymentDate(dateFormat.parse(row.getCell(5).getStringCellValue()));
            payment.setPaymentSum(new BigDecimal(row.getCell(6).getNumericCellValue()));
            payment.setTinDebtor(row.getCell(7).getStringCellValue());
            payment.setSourceCode((short) 1);
            payment.setState((short) 1);
            payment.setClaim((short) 0);

            paymentList.add(payment);
        }
    }
    fis.close();

    paymentDao.savePaymentManual(bankStatementId, paymentList, issuerSerialNumber, issuerIp);

}

From source file:com.nkapps.billing.services.ReportPrintServiceImpl.java

@Override
public HSSFWorkbook printClick(List<ReportClickListPojo> listPojos) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "report_click.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title/*from   w  ww  .  j a va2s.c o  m*/
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (ReportClickListPojo rclp : listPojos) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, dateFormat.format(rclp.getOperationDate()));
        createCell(row, cellCurrent++, style, dateFormat.format(rclp.getBsPaymentDate()));
        createCell(row, cellCurrent++, style, rclp.getBsPaymentNum());
        createCell(row, cellCurrent++, style, rclp.getBsPaymentSum());
        createCell(row, cellCurrent++, style, rclp.getClickPaymentSum());
        createCell(row, cellCurrent++, style, rclp.getDiffSum());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 4); // for 
    totalCellIndexes.add(CELL_START + 5); // for 
    totalCellIndexes.add(CELL_START + 6); // for 
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.nkapps.billing.services.ReportPrintServiceImpl.java

@Override
public HSSFWorkbook printSmst(List<ReportSmstListPojo> listPojos) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "report_smst.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title/*from   w ww  .  j a va2s . c o  m*/
    //HSSFRow row = sheet.getRow(1);
    //HSSFCell cell = row.getCell(1);
    //String title;
    //cell.setCellValue(title);
    //
    HSSFRow row;
    final int CELL_START = 1;
    final int ROW_START = sheet.getLastRowNum() - 1;
    final int CELL_END = sheet.getRow(ROW_START).getLastCellNum();

    int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent;
    //
    HSSFCellStyle[] style = new HSSFCellStyle[CELL_END];
    short styleHeight = sheet.getRow(rowCurrent).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle();
    }

    // total style
    HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END];
    short totalStyleHeight = sheet.getRow(rowTotal).getHeight();
    for (int i = CELL_START; i < CELL_END; i++) {
        totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle();
    }

    SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
    int numQuantity = 0;
    for (ReportSmstListPojo rslp : listPojos) {
        numQuantity++;

        row = sheet.createRow(rowCurrent++);
        row.setHeight(styleHeight);

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, dateFormat.format(rslp.getOperationDate()));
        createCell(row, cellCurrent++, style, rslp.getBsPaymentSum());
        createCell(row, cellCurrent++, style, rslp.getSmstPaymentSum());
        createCell(row, cellCurrent++, style, rslp.getDiffSum());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 2); // for 
    totalCellIndexes.add(CELL_START + 3); // for 
    totalCellIndexes.add(CELL_START + 4); // for 
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

From source file:com.pdf.GetPdf.java

public static void addXls(Document document, String url, String type) throws IOException, DocumentException {
    Iterator<Row> rowIterator;
    int colNo;/*  www  .ja v  a2  s .c om*/
    if (type.equals("xls")) {
        HSSFWorkbook excelWorkbook = new HSSFWorkbook(new URL(url).openStream());
        HSSFSheet my_worksheet = excelWorkbook.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    } else {
        XSSFWorkbook excelWorkbook1 = new XSSFWorkbook(new URL(url).openStream());
        XSSFSheet my_worksheet = excelWorkbook1.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    }
    PdfPTable my_table = new PdfPTable(colNo);
    PdfPCell table_cell = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next(); //Read Rows from Excel document       
        Iterator<Cell> cellIterator = row.cellIterator();//Read every column for every row that is READ
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next(); //Fetch CELL
            if (cell.getCellType() == (Cell.CELL_TYPE_NUMERIC)) {
                table_cell = new PdfPCell(new Phrase(new Double(cell.getNumericCellValue()).toString()));
                System.out.println(cell.getNumericCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_STRING)) {
                table_cell = new PdfPCell(new Phrase(cell.getStringCellValue()));
                System.out.println(cell.getStringCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_FORMULA)) {
                table_cell = new PdfPCell(new Phrase(cell.getCellFormula()));
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_BLANK)) {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            } else {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            }
        }
    }
    document.add(my_table);
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);/*from   ww w.  j av  a2 s . c  o m*/
    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        header.getCell(i).setCellStyle(cellStyle);
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestSuitesFromXls(String filePath, String testSuiteName) {
    boolean hasTrue = false;
    Iterator<Row> rowIterator;
    try {//from  www.ja v a 2  s .co  m
        FileInputStream myInput = new FileInputStream(filePath);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

        HSSFSheet mySheet = myWorkBook.getSheetAt(0);
        rowIterator = mySheet.rowIterator();
        for (int i = 0; i <= 2; i++) {
            rowIterator.next();
        }
        while (rowIterator.hasNext()) {
            Row next = rowIterator.next();
            if (getValue(next.getCell(2)).equalsIgnoreCase(testSuiteName)) {
                mySheet.removeRow(next);
                int rowNum = next.getRowNum();
                int newNum = rowNum + 1;
                mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                int numberOfSheets = myWorkBook.getNumberOfSheets();
                for (int j = 0; j < numberOfSheets; j++) {
                    HSSFSheet mySheet1 = myWorkBook.getSheetAt(j);
                    if (mySheet1.getSheetName().equalsIgnoreCase(testSuiteName)) {
                        myWorkBook.removeSheetAt(j);
                        hasTrue = true;
                        break;
                    }
                }
                myInput.close();
                FileOutputStream outFile = new FileOutputStream(filePath);
                myWorkBook.write(outFile);
                outFile.close();
                break;
            }
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private void readTestSuitesFromXLS(List<TestSuite> excels, StringBuilder sb)
        throws FileNotFoundException, IOException, UnknownHostException, PhrescoException {
    Iterator<Row> rowIterator;
    FileInputStream myInput = new FileInputStream(sb.toString());
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);

    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    rowIterator = mySheet.rowIterator();
    for (int i = 0; i <= 2; i++) {
        rowIterator.next();// w w  w  . j a  v a  2 s .  c  o  m
    }
    while (rowIterator.hasNext()) {
        Row next = rowIterator.next();
        if (StringUtils.isNotEmpty(getValue(next.getCell(2)))
                && !getValue(next.getCell(2)).equalsIgnoreCase("Total")) {
            TestSuite createObject = createObject(next);
            excels.add(createObject);
        }
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestCasesFromXls(String filePath, String testSuiteName, String testCaseId) {
    boolean hasTrue = false;
    List<TestCase> testCases = new ArrayList<TestCase>();
    TestCase tstCase = new TestCase();
    Iterator<Row> rowIterator;
    try {/*ww  w .  j  av  a 2 s . c o  m*/
        FileInputStream myInput = new FileInputStream(filePath);
        HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            HSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                for (int i = 0; i <= 23; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next = rowIterator.next();
                    if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) {
                        tstCase = readTest(next);
                        mySheet.removeRow(next);
                        int rowNum = next.getRowNum();
                        int newNum = rowNum + 1;
                        HSSFRow row = mySheet.getRow(newNum);
                        if (row != null) {
                            mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                        }
                        hasTrue = true;
                        break;
                    }
                }
            }
        }

        if (hasTrue) {
            for (int j = 0; j < numberOfSheets; j++) {
                HSSFSheet myHSSFSheet = myWorkBook.getSheetAt(j);
                if (myHSSFSheet.getSheetName().equals(testSuiteName)) {
                    rowIterator = myHSSFSheet.rowIterator();
                    for (int i = 0; i <= 23; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next = rowIterator.next();
                        TestCase createObject = readTest(next);
                        if (StringUtils.isNotEmpty(createObject.getTestCaseId())) {
                            testCases.add(createObject);
                        }
                    }
                    float totalPass = 0;
                    float totalFail = 0;
                    float totalNotApplicable = 0;
                    float totalBlocked = 0;
                    int totalTestCases = testCases.size();
                    for (TestCase testCase : testCases) {
                        String testCaseStatus = testCase.getStatus();
                        if (testCaseStatus.equalsIgnoreCase("success")) {
                            totalPass = totalPass + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("failure")) {
                            totalFail = totalFail + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) {
                            totalNotApplicable = totalNotApplicable + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("blocked")) {
                            totalBlocked = totalBlocked + 1;
                        }
                    }

                    //                      if(tstCase.getStatus().equalsIgnoreCase("success")) {
                    //                         totalPass = totalPass - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("failure")) {
                    //                         totalFail = totalFail - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) {
                    //                         totalNotApplicable = totalNotApplicable - 1;
                    //                      } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) {
                    //                         totalBlocked = totalBlocked - 1;
                    //                      } 

                    HSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet1.rowIterator();
                    for (int i = 0; i <= 2; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next1 = rowIterator.next();
                        if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                                && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                            TestSuite createObject = createObject(next1);
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())
                                    && createObject.getName().equals(testSuiteName)) {
                                updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1,
                                        totalTestCases, "delete");
                            }
                        }
                    }
                }
            }

            myInput.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            myWorkBook.write(outFile);
            outFile.close();
        }
    } catch (Exception e) {

    }

    return hasTrue;
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

private List<TestCase> readTestCase(String filePath, String fileName,
        com.photon.phresco.commons.model.TestCase tstCase) throws PhrescoException {
    List<TestCase> testCases = new ArrayList<TestCase>();
    try {//from w  w  w. j a va  2s .co m
        File testDir = new File(filePath);
        StringBuilder sb = new StringBuilder(filePath);
        if (testDir.isDirectory()) {
            FilenameFilter filter = new PhrescoFileFilter("", "xlsx");
            File[] listFiles = testDir.listFiles(filter);
            if (listFiles.length != 0) {
                for (File file1 : listFiles) {
                    if (file1.isFile()) {
                        sb.append(File.separator);
                        sb.append(file1.getName());
                        break;
                    }
                }
                updateTestCaseToXLSX(fileName, tstCase, testCases, sb);
            } else {
                FilenameFilter filter1 = new PhrescoFileFilter("", "xls");
                File[] listFiles1 = testDir.listFiles(filter1);
                if (listFiles1.length != 0) {
                    for (File file2 : listFiles1) {
                        if (file2.isFile()) {
                            sb.append(File.separator);
                            sb.append(file2.getName());
                            break;
                        }
                    }
                    FileInputStream myInput = new FileInputStream(sb.toString());
                    HSSFWorkbook myWorkBook = new HSSFWorkbook(myInput);
                    int numberOfSheets = myWorkBook.getNumberOfSheets();
                    for (int j = 0; j < numberOfSheets; j++) {
                        HSSFSheet mySheet = myWorkBook.getSheetAt(j);
                        if (mySheet.getSheetName().equals(fileName)) {
                            readTestFromSheet(tstCase, testCases, mySheet);
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) {
                                updateIndexPage(fileName, tstCase, testCases, myWorkBook);
                            }
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())) {
                                myInput.close();
                                FileOutputStream outFile = new FileOutputStream(sb.toString());
                                myWorkBook.write(outFile);
                                outFile.close();
                            }
                        }
                    }
                } else {
                    FilenameFilter odsFilter = new PhrescoFileFilter("", "ods");
                    File[] odsListFiles = testDir.listFiles(odsFilter);
                    for (File file2 : odsListFiles) {
                        if (file2.isFile()) {
                            sb.append(File.separator);
                            sb.append(file2.getName());
                            break;
                        }
                    }
                    testCases = readTestCasesFormODS(sb, testCases, fileName, tstCase);
                }
            }
        }
    } catch (Exception e) {
    }
    return testCases;
}