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.elbeesee.poink.representation.HSSFCellImplementation.java

License:Open Source License

public HSSFCellImplementation(HSSFWorkbook aWorkbook, int aSheetIndex, int aRowIndex, int aCellIndex) {
    this.mCell = aWorkbook.getSheetAt(aSheetIndex).getRow(aRowIndex).getCell(aCellIndex);
}

From source file:com.elbeesee.poink.representation.HSSFRowImplementation.java

License:Open Source License

public HSSFRowImplementation(HSSFWorkbook aWorkbook, int aSheetIndex, int aRowIndex) {
    this.mRow = aWorkbook.getSheetAt(aSheetIndex).getRow(aRowIndex);
}

From source file:com.elbeesee.poink.representation.HSSFSheetImplementation.java

License:Open Source License

public HSSFSheetImplementation(HSSFWorkbook aWorkbook, int aSheetIndex) {
    this.mSheet = aWorkbook.getSheetAt(aSheetIndex);
}

From source file:com.elbeesee.poink.transreptor.HSSFWorkbookToXML.java

License:Open Source License

public void onTransrept(INKFRequestContext aContext) throws Exception {
    IHSSFWorkbookRepresentation aIHSSFWorkbookRepresentation = (IHSSFWorkbookRepresentation) aContext
            .sourcePrimary(IHSSFWorkbookRepresentation.class);
    HSSFWorkbook vWorkbook = aIHSSFWorkbookRepresentation.getWorkbookReadOnly();

    StringBuilder vWorkbookXML = new StringBuilder();

    int vNumberOfSheets = vWorkbook.getNumberOfSheets();
    vWorkbookXML.append("<workbook numSheets=\"");
    vWorkbookXML.append(vNumberOfSheets);
    vWorkbookXML.append("\">");

    // do the sheets
    for (int i = 0; i < vNumberOfSheets; i++) {
        IHSSFSheetRepresentation vHSSFSheetRepresentation = new HSSFSheetImplementation(
                vWorkbook.getSheetAt(i));
        String vSheetXML = aContext.transrept(vHSSFSheetRepresentation, String.class);
        vWorkbookXML.append(vSheetXML);/*  w w w . ja  v  a2s. c o m*/
    }
    //

    vWorkbookXML.append("</workbook>");

    INKFResponse vResponse = aContext.createResponseFrom(vWorkbookXML.toString());
    vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT);
}

From source file:com.eryansky.core.excelTools.JsGridReportBase.java

License:Apache License

/**
 * ??/*  w  w w .j a  va2s.  c om*/
 * 
 * @param
 * @return HashMap<String,HSSFCellStyle>
 */
private HashMap<String, HSSFCellStyle> initStyles(HSSFWorkbook wb) {
    HashMap<String, HSSFCellStyle> ret = new HashMap<String, HSSFCellStyle>();
    try {
        POIFSFileSystem fs = new POIFSFileSystem(getClass().getClassLoader().getResourceAsStream(MODULE_PATH));

        HSSFWorkbook src = new HSSFWorkbook(fs);
        HSSFSheet sheet = src.getSheetAt(0);

        buildStyle(wb, src, sheet, 0, ret, "TITLE");
        buildStyle(wb, src, sheet, 1, ret, "SUB_TITLE");
        buildStyle(wb, src, sheet, 2, ret, "SUB_TITLE2");

        buildStyle(wb, src, sheet, 4, ret, "TABLE_HEADER");
        buildStyle(wb, src, sheet, 5, ret, "STRING");
        buildStyle(wb, src, sheet, 6, ret, "INT");
        buildStyle(wb, src, sheet, 7, ret, "D2");
        buildStyle(wb, src, sheet, 8, ret, "D3");

        buildStyle(wb, src, sheet, 10, ret, "STRING_C");
        buildStyle(wb, src, sheet, 11, ret, "INT_C");
        buildStyle(wb, src, sheet, 12, ret, "D2_C");
        buildStyle(wb, src, sheet, 13, ret, "D3_C");

        buildStyle(wb, src, sheet, 15, ret, "RED_BG");
        buildStyle(wb, src, sheet, 16, ret, "YELLOW_BG");
        buildStyle(wb, src, sheet, 17, ret, "GREEN_BG");
    } catch (Exception e) {
        e.printStackTrace();
    }
    return ret;
}

From source file:com.essa.main.ReadExcel.java

public static void main(String[] args) {
    try {//from www  .  j a va2  s. c o  m

        InputStream input = new BufferedInputStream(new FileInputStream("sample.xls"));
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);

        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            System.out.println("\n");
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {

                HSSFCell cell = (HSSFCell) cells.next();
                if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType())
                    System.out.print(cell.getNumericCellValue() + "     ");
                else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType())
                    System.out.print(cell.getStringCellValue() + "     ");
                else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType())
                    System.out.print(cell.getBooleanCellValue() + "     ");
                else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType())
                    System.out.print("BLANK     ");
                else
                    System.out.print("Unknown cell type");

            }

        }

    } catch (IOException ex) {
        ex.printStackTrace();
    }
}

From source file:com.etest.view.tq.itemanalysis.FileUploadWindow.java

void readContentFromExcelFile(File excelFile) {
    try {/*from   ww  w.j av a2s  .c  o  m*/
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
        HSSFRow row;
        HSSFCell cell;

        boolean stop = false;
        boolean nonBlankRowFound;
        int s;
        HSSFRow lastRow = null;

        while (stop == false) {
            nonBlankRowFound = false;
            lastRow = sheet.getRow(sheet.getLastRowNum());
            for (s = lastRow.getFirstCellNum(); s <= lastRow.getLastCellNum(); s++) {
                cell = lastRow.getCell(s);
                if (cell != null && lastRow.getCell(s).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                    nonBlankRowFound = true;
                }
            }
            if (nonBlankRowFound == true) {
                stop = true;
            } else {
                sheet.removeRow(lastRow);
            }
        }

        int rows; // No of rows
        rows = sheet.getPhysicalNumberOfRows();

        int cols = 0; // No of columns
        int tmp = 0;

        // This trick ensures that we get the data properly even if it doesn't start from first few rows
        for (int i = 0; i < 10 || i < rows; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                if (tmp > cols)
                    cols = tmp;
            }
        }

        List<ItemAnalysis> itemAnalysisList = new ArrayList<>();
        List<Character> answer;
        ItemAnalysis itemAnalysis = null;

        for (int c = 0; c < cols; c++) {
            itemAnalysis = new ItemAnalysis();
            answer = new ArrayList<>();
            for (int r = 0; r < rows; r++) {
                row = sheet.getRow(r);
                if (row == null || row.toString().isEmpty()) {
                    ShowErrorNotification.error("Remove all blank/empty rows after the last Item!");
                    return;
                } else {
                    //                   if(row != null){
                    cell = row.getCell(c);
                    if (cell == null || cell.toString().isEmpty()) {
                        ShowErrorNotification.error("Remove all blank/empty columns after the last student!");
                        return;
                    } else {
                        //                       if(cell != null){                           
                        if (c != 0) {
                            if (r == 0) {
                                itemAnalysis.setStudentNumber(cell.toString().trim());
                            } else {
                                answer.add(cell.toString().trim().charAt(0));
                            }
                        } else {
                            if (r != 0) {
                                totalItems++;
                            }
                        }
                    }
                }
            }
            if (c != 0) {
                itemAnalysis.setAnswer(answer);
                itemAnalysisList.add(itemAnalysis);
            }
        }

        if (tq.getCellItemIdByTQCoverageId(getTqCoverageId()).size() != totalItems) {
            ShowErrorNotification.error("Total Items do not MATCH!");
            totalItems = 0;
            return;
        }

        studentNoAndTotalScore = new HashMap<>();
        studentNoAndAnswer = new HashMap<>();
        totalItems = 1;
        new Thread() {

            @Override
            public void run() {
                totalData = itemAnalysisList.size();
                for (ItemAnalysis i : itemAnalysisList) {
                    try {
                        Thread.sleep(50);
                        studentNoAndTotalScore.put(i.getStudentNumber(), ItemAnalysisInterpretation
                                .getTotalScoresOfAllStudent(tqCoverageId, i.getAnswer()));
                        studentNoAndAnswer.put(i.getStudentNumber(), i.getAnswer());

                        getUI().access(new Runnable() {

                            @Override
                            public void run() {
                                if (totalItems < itemAnalysisList.size()) {
                                    analyze.setValue("Analyzing data.. "
                                            + CommonUtilities.roundOffToTwoDecimal((current / totalData) * 100)
                                            + "%");
                                    current++;
                                } else {
                                    analyze.setValue("Data analyzed... 100%");
                                    getLowerAndUpperGroupStudent(studentNoAndTotalScore);

                                    HorizontalLayout h = new HorizontalLayout();
                                    h.setWidth("100%");

                                    h.addComponent(viewTableProportion());
                                    h.addComponent(viewStudentsTotalScore());
                                    h.addComponent(approveItemAnalysis());
                                    v.addComponent(h);

                                    v.addComponent(itemAnalysisGridPanel());
                                }
                            }

                        });

                        totalItems++;
                    } catch (InterruptedException ex) {
                        Logger.getLogger(FileUploadWindow.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }

        }.start();
        UI.getCurrent().setPollInterval(500);
    } catch (IOException ex) {
        Logger.getLogger(TQItemAnalysisUI.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.ferid.app.classroom.edit.EditStudentActivity.java

License:Apache License

/**
 * Import students form excel//w  w w. ja va2 s . c om
 * @param fileName Excel file name
 */
private void readXlsFile(String fileName) {
    ArrayList<String> studentsList = new ArrayList<>();
    progressDialog = ProgressDialog.show(this, getString(R.string.wait), getString(R.string.ongoing), true,
            false);

    try {
        // Creating Input Stream
        File file = new File(fileName);
        FileInputStream fileInputStream = new FileInputStream(file);

        // Create a POIFSFileSystem object
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(fileInputStream);

        // Create a workbook using the File System
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);

        // Get the first sheet from workbook
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

        // Iterate through the cells
        Iterator rowIter = hssfSheet.rowIterator();

        StringBuilder studentName; //full name

        while (rowIter.hasNext()) {
            studentName = new StringBuilder("");

            HSSFRow hssfRow = (HSSFRow) rowIter.next();
            Iterator cellIter = hssfRow.cellIterator();
            while (cellIter.hasNext()) {
                HSSFCell currentCell = (HSSFCell) cellIter.next();
                if (!currentCell.toString().trim().equals("")) {
                    //put space in between name, surname, etc.
                    if (studentName.toString().length() > 0) {
                        studentName.append(" ");
                    }
                    studentName.append(currentCell.toString());
                }
            }

            //add to list
            if (!studentName.toString().equals("")) {
                studentsList.add(studentName.toString());
            }
        }
    } catch (Exception e) {
        progressDialog.dismiss();

        excelFileError();
    }

    if (!studentsList.isEmpty()) {
        new InsertMultipleStudents().execute(studentsList);
    } else {
        progressDialog.dismiss();
    }
}

From source file:com.flexive.extractor.ExcelExtractor.java

License:Open Source License

/**
 * Extracts the text informations from the excel file.
 *
 * @param in the input stream to read from
 * @return the extraxted informations, or null if no text extraction was possible
 *//*from  w  w w . j  a  va  2s .  co m*/
public ExtractedData extract(final InputStream in) {

    BufferedInputStream bis = null;
    try {

        writer = new ByteArrayOutputStream();

        // We need to read the stream 2 times, so we use a buffered input stream and mark the
        // beginning
        bis = new BufferedInputStream(in);
        bis.mark(Integer.MAX_VALUE);

        // Retrieve summary information
        POIFSReader r = new POIFSReader();
        r.registerListener(this, "\005SummaryInformation");
        r.read(bis);
        bis.reset();

        // Retrieve text by processing all sheets
        HSSFWorkbook wb = new HSSFWorkbook(bis);
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            processSheet(sheet);
        }

        // Append summary info to text
        if (fxsi != null) {
            writer.write(FxSharedUtils.getBytes(fxsi.getFTIndexInformations()));
        }
        writer.flush();

        return new ExtractedData(fxsi, writer.toString());
    } catch (Exception exc) {
        exc.printStackTrace();
        return null;
    } finally {
        try {
            if (writer != null)
                writer.close();
        } catch (Exception exc) {
            /*ignore*/}
        try {
            if (bis != null)
                bis.close();
        } catch (Exception exc) {
            /*ignore*/}
    }
}

From source file:com.frameworkset.platform.cms.searchmanager.extractors.CmsExtractorMsExcel.java

License:Open Source License

/**
 * Extracts the text from the Excel table content.<p>
 * /*  www . j a  va2  s  . c o  m*/
 * @param in the document input stream
 * @return the extracted text
 * @throws IOException if something goes wring
 */
protected String extractTableContent(InputStream in) throws IOException {

    HSSFWorkbook excelWb = new HSSFWorkbook(in);
    StringBuffer result = new StringBuffer(4096);

    int numberOfSheets = excelWb.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
        HSSFSheet sheet = excelWb.getSheetAt(i);
        int numberOfRows = sheet.getPhysicalNumberOfRows();
        if (numberOfRows > 0) {

            if (CmsStringUtil.isNotEmpty(excelWb.getSheetName(i))) {
                // append sheet name to content
                if (i > 0) {
                    result.append("\n\n");
                }
                result.append(excelWb.getSheetName(i).trim());
                result.append(":\n\n");
            }

            Iterator rowIt = sheet.rowIterator();
            while (rowIt.hasNext()) {
                HSSFRow row = (HSSFRow) rowIt.next();
                if (row != null) {
                    boolean hasContent = false;
                    Iterator it = row.cellIterator();
                    while (it.hasNext()) {
                        HSSFCell cell = (HSSFCell) it.next();
                        String text = null;
                        try {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_BLANK:
                            case HSSFCell.CELL_TYPE_ERROR:
                                // ignore all blank or error cells
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                text = Double.toString(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                text = Boolean.toString(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                            default:
                                text = cell.getStringCellValue();
                                break;
                            }
                        } catch (Exception e) {
                            // ignore this cell
                        }
                        if (CmsStringUtil.isNotEmpty(text)) {
                            result.append(text.trim());
                            result.append(' ');
                            hasContent = true;
                        }
                    }
                    if (hasContent) {
                        // append a newline at the end of each row that has content                            
                        result.append('\n');
                    }
                }
            }
        }
    }

    return result.toString();
}