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

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

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

Introduction

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

Prototype

@Override
public String getSheetName(int sheetIndex)

Source Link

Usage

From source file:org.apache.cocoon.generation.HSSFGenerator.java

License:Apache License

/**
 * Writes out the workbook data as XML, without formatting information
 *//*from   w w  w  .ja  v a2 s  .co  m*/
private void writeXML(HSSFWorkbook workbook) throws SAXException {
    this.contentHandler.startDocument();
    start("Workbook");
    start("SheetNameIndex");
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        start("SheetName");
        data(workbook.getSheetName(i));
        end("SheetName");
    }
    end("SheetNameIndex");
    start("Sheets");
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        start("Sheet");
        start("Name");
        data(workbook.getSheetName(i));
        end("Name");
        start("MaxCol");
        data(Integer.toString(getMaxCol(sheet)));
        end("MaxCol");
        start("MaxRow");
        data(Integer.toString(sheet.getLastRowNum()));
        end("MaxRow");
        if (formatting) {
            writeStyles(workbook, sheet);
        }

        start("Cells");
        final Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            final HSSFRow row = (HSSFRow) rows.next();
            final Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                final HSSFCell cell = (HSSFCell) cells.next();
                attribute("Row", Integer.toString(row.getRowNum()));
                attribute("Col", Short.toString(cell.getCellNum()));
                attribute("ValueType", getValueType(cell.getCellType()));
                start("Cell");
                data(getValue(cell));
                end("Cell");
            }
        }
        end("Cells");

        end("Sheet");
    }
    end("Sheets");
    end("Workbook");
    this.contentHandler.endDocument();
}

From source file:org.eclipse.birt.report.data.oda.excel.impl.util.ExcelFileReader.java

License:Open Source License

public static List<String> getSheetNamesInExcelFile(Object file) throws MalformedURLException, IOException {
    String extension = getExtensionName(file);
    InputStream fis = ResourceLocatorUtil.getURIStream(file);
    List<String> sheetNames = new ArrayList<String>();
    try {/*  w w w.j  a  va  2s  .  c  om*/

        // using uri, we may not know the extension name of the file.
        if (isXlsxFile(extension)) {
            XlsxFileReader poiRdr = new XlsxFileReader(fis);

            LinkedHashMap<String, String> lxlsxWorkSheetList = poiRdr.getSheetNames();
            for (Map.Entry<String, String> entry : lxlsxWorkSheetList.entrySet()) {
                sheetNames.add(entry.getKey());
            }
        } else if (isXlsFile(extension)) {
            //Only called in design env
            HSSFWorkbook lworkBook = new HSSFWorkbook(fis);
            for (int i = 0; i < lworkBook.getNumberOfSheets(); i++) {
                sheetNames.add(lworkBook.getSheetName(i));
            }
        }
    } catch (FileNotFoundException e) {
        // do nothing
    } catch (IOException e) {
        // do nothing
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        fis.close();
    }
    return sheetNames;
}

From source file:org.gageot.excel.core.ExcelTemplate.java

License:Apache License

/**
 * Read the sheet names of an Excel file.
 * @return an array containing a java.lang.String for each sheet. Empty if not sheet.
 * @throws DataAccessException if there is any problem
 *//*from  w w w  .j  av  a 2 s  .  c om*/
public String[] getSheetNames() {
    return read(new Function<HSSFWorkbook, String[]>() {
        @Override
        public String[] apply(HSSFWorkbook workbook) {
            int sheetCount = workbook.getNumberOfSheets();

            String[] sheetNames = new String[sheetCount];
            for (int i = 0; i < sheetCount; i++) {
                sheetNames[i] = workbook.getSheetName(i);
            }

            return sheetNames;
        }
    });
}

From source file:org.jlibrary.core.search.extraction.ExcelExtractor.java

License:Open Source License

/**
 * Extracts the text from the Excel table content.<p>
 * /*from   w w  w .  ja v a 2  s  .  co  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);
    StringBuilder result = new StringBuilder(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 ((excelWb.getSheetName(i) != null) && !excelWb.getSheetName(i).equals("")) {
                // 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 ((text != null) && !text.equals("")) {
                            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();
}

From source file:org.mili.core.text.MockFactory.java

License:Apache License

/**
 * Assert workbook.//  ww w. j  a  va 2  s  .  c o m
 *
 * @param workbook the workbook
 */
public static void assertWorkbook(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow row = sheet.getRow(0);
    assertEquals("unknown", workbook.getSheetName(0));
    assertEquals(1, sheet.getLastRowNum());
    assertEquals(11, row.getLastCellNum());
    assertEquals("byte", row.getCell((short) 0).getStringCellValue());
    assertEquals("short", row.getCell((short) 1).getStringCellValue());
    assertEquals("integer", row.getCell((short) 2).getStringCellValue());
    assertEquals("char", row.getCell((short) 3).getStringCellValue());
    assertEquals("long", row.getCell((short) 4).getStringCellValue());
    assertEquals("float", row.getCell((short) 5).getStringCellValue());
    assertEquals("double", row.getCell((short) 6).getStringCellValue());
    assertEquals("boolean", row.getCell((short) 7).getStringCellValue());
    assertEquals("string", row.getCell((short) 8).getStringCellValue());
    assertEquals("foo", row.getCell((short) 9).getStringCellValue());
    assertEquals("date", row.getCell((short) 10).getStringCellValue());
    assertEquals("null", row.getCell((short) 11).getStringCellValue());
    row = sheet.getRow(1);
    //        assertEquals("1", row.getCell((short) 0).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 1).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 2).getStringCellValue());
    assertEquals("c", row.getCell((short) 3).getStringCellValue());
    //        assertEquals("1", row.getCell((short) 4).getStringCellValue());
    assertEquals(1.0, row.getCell((short) 5).getNumericCellValue(), 0.0);
    //        assertEquals(1.0, row.getCell((short) 6).getNumericCellValue(), 0.0);
    assertEquals("true", row.getCell((short) 7).getStringCellValue());
    assertEquals("abbas", row.getCell((short) 8).getStringCellValue());
    assertEquals("Foo", row.getCell((short) 9).getStringCellValue());
    assertEquals("Thu Feb 01 00:00:00 CET 3900", row.getCell((short) 10).getStringCellValue());
    assertEquals("", row.getCell((short) 11).getStringCellValue());
}

From source file:org.mili.core.text.MockFactory.java

License:Apache License

/**
 * Assert empty workbook.//from   w w  w.  ja v  a2 s .com
 *
 * @param workbook the workbook
 */
public static void assertEmptyWorkbook(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow row = sheet.getRow(0);
    assertEquals("unknown", workbook.getSheetName(0));
    assertEquals(0, sheet.getLastRowNum());
    assertEquals(0, row.getLastCellNum());
    assertEquals("Keine Daten vorhanden !", row.getCell((short) 0).getStringCellValue());
}

From source file:org.modeshape.sequencer.msoffice.excel.ExcelMetadataReader.java

License:Apache License

public static ExcelMetadata instance(InputStream stream) throws IOException {
    ExcelMetadata metadata = new ExcelMetadata();
    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(stream));

    List<ExcelSheetMetadata> sheets = new ArrayList<ExcelSheetMetadata>();

    for (int sheetInd = 0; sheetInd < wb.getNumberOfSheets(); sheetInd++) {
        ExcelSheetMetadata meta = new ExcelSheetMetadata();
        meta.setName(wb.getSheetName(sheetInd));
        sheets.add(meta);/*from w  w w.  j av a 2s  .c o  m*/

        HSSFSheet worksheet = wb.getSheetAt(sheetInd);
        int lastRowNum = worksheet.getLastRowNum();

        StringBuilder buff = new StringBuilder();
        for (int rowNum = worksheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) {
            HSSFRow row = worksheet.getRow(rowNum);

            // Empty rows are returned as null
            if (row == null) {
                continue;
            }

            int lastCellNum = row.getLastCellNum();
            for (int cellNum = row.getFirstCellNum(); cellNum < lastCellNum; cellNum++) {
                HSSFCell cell = row.getCell(cellNum);

                // Undefined cells are returned as null
                if (cell == null) {
                    continue;
                }

                /*
                 * Builds a string of body content from all string, numeric,
                 * and formula values in the body of each worksheet.
                 * 
                 *  This code currently duplicates the POI 3.1 ExcelExtractor behavior of
                 *  combining the body text from all worksheets into a single string.
                 */
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    buff.append(cell.getRichStringCellValue().getString());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    buff.append(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    buff.append(cell.getCellFormula());
                    break;
                }

                HSSFComment comment = cell.getCellComment();
                if (comment != null) {
                    // Filter out row delimiter characters from comment
                    String commentText = comment.getString().getString().replace(ROW_DELIMITER_CHAR, ' ');

                    buff.append(" [");
                    buff.append(commentText);
                    buff.append(" by ");
                    buff.append(comment.getAuthor());
                    buff.append(']');
                }

                if (cellNum < lastCellNum - 1) {
                    buff.append(CELL_DELIMITER_CHAR);
                } else {
                    buff.append(ROW_DELIMITER_CHAR);
                }
            }
        }
        meta.setText(buff.toString());
    }

    metadata.setSheets(sheets);
    metadata.setMetadata(wb.getSummaryInformation());
    return metadata;
}

From source file:org.opencms.search.extractors.CmsExtractorMsExcel.java

License:Open Source License

/**
 * Extracts the text from the Excel table content.<p>
 * /*from   w w w .j a v a2 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 ((text != null) && (text.length() != 0)) {
                            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();
}

From source file:org.silverpeas.core.index.indexing.parser.excelParser.ExcelParser.java

License:Open Source License

/**
 * Read the text content of a pdf file and store it in out to be ready to be indexed.
 * @param out//from www  .  j  a va 2s.  co m
 * @param path
 * @param encoding
 * @throws IOException
 */
@Override
public void outPutContent(Writer out, String path, String encoding) throws IOException {
    FileInputStream file = new FileInputStream(path);
    try {
        POIFSFileSystem fs = new POIFSFileSystem(file);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        HSSFSheet sheet;
        for (int nbSheet = 0; nbSheet < workbook.getNumberOfSheets(); nbSheet++) {
            // extract sheet's name
            out.write(workbook.getSheetName(nbSheet));
            sheet = workbook.getSheetAt(nbSheet);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        out.write(cell.getStringCellValue());
                        out.write(' ');
                    }
                }
            }
        }
    } catch (IOException ioe) {
        SilverTrace.error("indexing", "ExcelParser.outPutContent()", "indexing.MSG_IO_ERROR_WHILE_READING",
                path, ioe);
    } finally {
        IOUtils.closeQuietly(file);
    }
}

From source file:org.silverpeas.search.indexEngine.parser.excelParser.ExcelParser.java

License:Open Source License

/**
 *Read the text content of a pdf file and store it in out to be ready to be indexed.
 * @param out/* w ww. jav a2  s .co m*/
 * @param path
 * @param encoding
 * @throws IOException
 */
@Override
public void outPutContent(Writer out, String path, String encoding) throws IOException {
    FileInputStream file = new FileInputStream(path);
    try {
        POIFSFileSystem fs = new POIFSFileSystem(file);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        HSSFSheet sheet = null;
        for (int nbSheet = 0; nbSheet < workbook.getNumberOfSheets(); nbSheet++) {
            // extract sheet's name
            out.write(workbook.getSheetName(nbSheet));
            SilverTrace.debug("indexEngine", "ExcelParser.outputContent", "root.MSG_GEN_PARAM_VALUE",
                    "sheetName = " + workbook.getSheetName(nbSheet));
            sheet = workbook.getSheetAt(nbSheet);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    Cell cell = cells.next();
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        out.write(cell.getStringCellValue());
                        out.write(' ');
                        SilverTrace.debug("indexEngine", "ExcelParser.outputContent",
                                "root.MSG_GEN_PARAM_VALUE", "cellValue = " + cell.getStringCellValue());
                    }
                }
            }
        }
    } catch (IOException ioe) {
        SilverTrace.error("indexEngine", "ExcelParser.outPutContent()",
                "indexEngine.MSG_IO_ERROR_WHILE_READING", path, ioe);
    } finally {
        IOUtils.closeQuietly(file);
    }
}