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.mycompany.mavenproject1.MainExecutor.java

static Map<String, Object[]> getNewWorkbookData(HSSFWorkbook inputExcel, Iterator<String> playersIterator) {
    Map<String, Object[]> data = new HashMap<String, Object[]>();
    data.put("1", new Object[] { "Player", "FSP.1", "FSW.1", "SSP.1", "SSW.1", "ACE.1", "DBF.1", "WNR.1",
            "UFE.1", "BPC.1", "BPW.1", "NPA.1", "NPW.1" });
    int key = 2;//from  ww w.  ja v  a2  s.c o  m
    while (playersIterator.hasNext()) {
        String player = playersIterator.next();
        for (int i = 0; i < inputExcel.getNumberOfSheets(); i++) {
            Iterator<Row> rowIterator = inputExcel.getSheetAt(i).iterator();
            rowIterator.next();
            int row_counter = 0;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Object[] rowValues = new Object[13];

                String player_1_cell = row.getCell(0).getStringCellValue();
                String player_2_cell = row.getCell(1).getStringCellValue();
                int dataRangeLow = 0;
                int dataRangeHigh = 0;

                if (player.equals(player_1_cell)) {
                    dataRangeLow = 6;
                    dataRangeHigh = 17;
                    rowValues[0] = player;
                } else if (player.equals(player_2_cell)) {
                    dataRangeLow = 24;
                    dataRangeHigh = 35;
                    rowValues[0] = player;
                } else {
                    // Go to another row.
                }
                int cell_counter = 0;
                for (int j = dataRangeLow; j < dataRangeHigh; j++) {
                    Cell cell = row.getCell(j);
                    System.out.println(cell_counter);
                    if (cell != null)
                        rowValues[j - dataRangeLow + 1] = cell.getNumericCellValue();

                    if (cell_counter == 5) {
                        Object debug = new Object();
                    }
                    cell_counter++;
                    data.put("" + key, rowValues);
                    key++;
                }

                System.out.println(row_counter);
                row_counter++;
            }
        }
    }

    return data;
}

From source file:com.netxforge.netxstudio.data.importer.HSSFMetricValuesImporter.java

License:Open Source License

@Override
protected int processFile(File file) throws Exception {
    final InputStream is = new FileInputStream(file);
    final HSSFWorkbook workBook = new HSSFWorkbook(is);
    final HSSFSheet sheet = workBook.getSheetAt(((MappingXLS) getMapping()).getSheetNumber());
    currentSheet = sheet;//w  w w  . j  a v a 2  s  . c  o m

    if (sheet.getLastRowNum() < getMapping().getFirstDataRow()) {
        getFailedRecords().add(createMappingRecord(

                "There is no data in the sheet, first data row is " + getMapping().getFirstDataRow()
                        + " but the sheet has only " + sheet.getLastRowNum() + " rows."));
        return 0;
    }
    is.close();
    final int totalRows = processRows();
    currentSheet = null;
    return totalRows;
}

From source file:com.netxforge.netxstudio.models.importer.MasterDataImporter.java

License:Open Source License

public void process(InputStream is) {
    HSSFWorkbook workBook;
    try {//  w  w  w . jav  a  2  s  .  c o m
        workBook = new HSSFWorkbook(is);

        // Multi pass the worksheets, do not add objects after the first
        // pass.
        // int passes = 2;

        for (int passIndex = 1; passIndex <= passes; passIndex++) {

            // Clear after each pass...
            unresolvedReferences.clear();

            if (ImportActivator.DEBUG) {
                System.out.println("DATA IMPORTER: pass=" + passIndex);
            }

            for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {

                ProcessWorkSheet pw = new ProcessWorkSheet(passIndex, sheetIndex,
                        workBook.getSheetAt(sheetIndex));

                // We only add object on the first pass
                List<RowResult> sheetResult = pw.getSheetResult();
                if (!pw.isMultiRefSheet() && passIndex == 1) {
                    for (final RowResult rowResult : sheetResult) {
                        resource.getContents().add(rowResult.getEObject());
                        this.addToGlobalIndex(rowResult.getIndex(), rowResult.getEObject());
                    }
                }
            }
            if (ImportActivator.DEBUG) {
                printResult();
                printIndex();
            }

        }
    } catch (final Exception e) {
        throw new IllegalStateException(e);
    }
}

From source file:com.netxforge.netxstudio.models.importer.MasterDataImporterBackup.java

License:Open Source License

public void process(InputStream is) {
    HSSFWorkbook workBook;
    try {//from w w w.  j  a va 2  s  . c  o m
        workBook = new HSSFWorkbook(is);
        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {

            ProcessWorkSheet pw = new ProcessWorkSheet(i, workBook.getSheetAt(i));
            List<RowResult> sheetResult = pw.getSheetResult();
            if (!pw.isMultiRefSheet()) {
                for (final RowResult rowResult : sheetResult) {
                    EObject resultObject = EcoreUtil.copy(rowResult.getEObject());
                    IndexedObject io = new IndexedObject(rowResult.getIndex(), resultObject);
                    resultList.add(io);
                }
            }
        }

        printResult();

    } catch (final Exception e) {
        throw new IllegalStateException(e);
    }
}

From source file:com.netxforge.netxstudio.server.dataimport.MasterDataImporter.java

License:Open Source License

public void process(HSSFWorkbook workBook) {
    try {/* w ww .  ja va 2s .  c o  m*/
        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
            processWorkSheet(i, workBook.getSheetAt(i));
        }

        for (final RowResult rowResult : rowResults) {
            processEReferences(rowResult.getEObject(), rowResult.getRow());
            final Resource resource = dataProvider.getResource(rowResult.getEObject().eClass());
            resource.getContents().add(rowResult.getEObject());
        }
    } catch (final Exception e) {
        throw new IllegalStateException(e);
    }
}

From source file:com.nfa.drs.data.StudentWindTunnelFormatXls.java

private List<String> readXlsLines(Path file) {
    List<String> lines = new ArrayList<>();

    try {/*from  w  w  w  .  ja v a  2s .  c  om*/
        FileInputStream stream = new FileInputStream(file.toFile());
        HSSFWorkbook book = new HSSFWorkbook(stream);
        HSSFSheet sheet = book.getSheetAt(0);

        for (Row row : sheet) {
            int rowIndex = row.getRowNum();
            while (rowIndex > lines.size() - 1) {
                lines.add("");
            }

            StringBuilder line = new StringBuilder();
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    line.append(cell.getStringCellValue());
                } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    line.append(cell.getNumericCellValue());
                }
                line.append(",");
            }
            lines.add(line.toString());
        }
    } catch (IOException ex) {

    }

    return lines;
}

From source file:com.nikoo28.excel.parser.ExcelParser.java

License:Apache License

public String parseExcelData(InputStream is) {
    try {/*w ww.  j  a  v  a2 s .c  om*/
        HSSFWorkbook workbook = new HSSFWorkbook(is);

        // Taking first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        currentString = new StringBuilder();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    bytesRead++;
                    currentString.append(cell.getBooleanCellValue() + "\t");
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    bytesRead++;
                    currentString.append(cell.getNumericCellValue() + "\t");
                    break;

                case Cell.CELL_TYPE_STRING:
                    bytesRead++;
                    currentString.append(cell.getStringCellValue() + "\t");
                    break;

                }
            }
            currentString.append("\n");
        }
        is.close();
    } catch (IOException e) {
        LOG.error("IO Exception : File not found " + e);
    }
    return currentString.toString();

}

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

@Override
public HSSFWorkbook printClaim(List<PrintClaimPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(
            new FileInputStream(servletContext.getRealPath("templates") + File.separator + "print_claim.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title//from   w w w  . j a va  2s.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 (PrintClaimPojo pcp : listPojo) {
        numQuantity++;

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

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, numQuantity);
        createCell(row, cellCurrent++, style, pcp.getTin());
        createCell(row, cellCurrent++, style, pcp.getMfo());
        createCell(row, cellCurrent++, style, pcp.getChet());
        createCell(row, cellCurrent++, style, pcp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(pcp.getPaymentDate()));
        createCell(row, cellCurrent++, style, pcp.getPaymentSum());
        createCell(row, cellCurrent++, style, pcp.getPaymentDetails());
    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 6); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

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

@Override
public HSSFWorkbook printRegister(List<PrintRegisterPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "print_register.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title//from   w ww.j  a v a 2  s.com
    //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 (PrintRegisterPojo prp : listPojo) {
        numQuantity++;

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

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, prp.getInvoiceNum());
        createCell(row, cellCurrent++, style, prp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(prp.getPaymentDate()));
        createCell(row, cellCurrent++, style, prp.getTin());
        createCell(row, cellCurrent++, style, prp.getName());
        createCell(row, cellCurrent++, style, prp.getPaymentSum());

    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 5); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}

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

@Override
public HSSFWorkbook printClaimRegister(List<PrintClaimRegisterPojo> listPojo) throws Exception {
    HSSFWorkbook workbook = null;

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
            servletContext.getRealPath("templates") + File.separator + "print_claim_register.xls"));
    workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    // title/*from   w  w w. j a va 2 s . 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 (PrintClaimRegisterPojo pcrp : listPojo) {
        numQuantity++;

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

        cellCurrent = CELL_START;
        createCell(row, cellCurrent++, style, pcrp.getInvoiceNum());
        createCell(row, cellCurrent++, style, pcrp.getPaymentNum());
        createCell(row, cellCurrent++, style, dateFormat.format(pcrp.getPaymentDate()));
        createCell(row, cellCurrent++, style, pcrp.getTin());
        createCell(row, cellCurrent++, style, pcrp.getName());
        createCell(row, cellCurrent++, style, pcrp.getPaymentSum());

    }
    List<Integer> totalCellIndexes = new ArrayList<>();
    totalCellIndexes.add(CELL_START + 5); // for payment sum columns
    createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent);

    return workbook;
}