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

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

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

Introduction

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

Prototype

@Override
public int getNumberOfSheets()

Source Link

Document

get the number of spreadsheets in the workbook (this will be three after serialization)

Usage

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

private HSSFWorkbook fillWorkbook(HSSFWorkbook wb, ArrayList<Value> values, String qcName, String qcType,
        String qcFormat, Integer plotType, ReportStatus status) throws Exception {
    int rowIndex, sheetIndex, valueIndex;
    HSSFSheet sheet;/*from   w w  w .j a va  2  s .co  m*/
    Row row;
    String lastAnalyte, lastFormat;

    lastAnalyte = "___";
    lastFormat = "___";
    sheet = null;
    sheetIndex = 1;
    valueIndex = 0;

    try {
        baseFont = wb.createFont();
        baseFont.setFontName("Arial");
        baseFont.setFontHeightInPoints((short) 8);
        baseStyle = wb.createCellStyle();
        baseStyle.setFont(baseFont);

        headerFont = wb.createFont();
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 8);
        headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(CellStyle.ALIGN_LEFT);
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
        headerStyle.setFont(headerFont);

        maxChars = new ArrayList<Integer>();
        worksheetColumnMap = new HashMap<String, HashMap<String, Integer>>();
        worksheetHeaders = new ArrayList<String>();
        worksheetHeaderNames = new ArrayList<String>();

        if (qcColumns != null && !qcColumns.isEmpty())
            rowIndex = 32;
        else
            rowIndex = 3;

        for (Value value : values) {
            valueIndex++;

            if ("N".equals(value.getIsPlot()))
                continue;

            if (!lastAnalyte.equals(value.getAnalyteName())) {
                if (!"___".equals(lastAnalyte)) {
                    while (rowIndex < sheet.getLastRowNum()) {
                        sheet.removeRow(sheet.getRow(rowIndex));
                        rowIndex++;
                    }
                    finishSheet(sheet, wb, qcName, qcType, lastAnalyte);
                }
                sheet = wb.getSheet("Sheet" + (sheetIndex++));
                if (sheet == null)
                    sheet = wb.createSheet();
                lastAnalyte = value.getAnalyteName();
                if (qcColumns != null && !qcColumns.isEmpty())
                    rowIndex = 32;
                else
                    rowIndex = 3;
                lastFormat = "___";

                if (Constants.dictionary().CHART_TYPE_FIXED.equals(plotType))
                    setStatisticCells(wb, sheet, value);
            }

            if (!lastFormat.equals(value.getWorksheetFormat())) {
                lastFormat = value.getWorksheetFormat();
                if (qcColumns == null || qcColumns.isEmpty())
                    loadWorksheetFormat(lastFormat);
            }

            row = sheet.createRow(rowIndex++);
            setBaseCells(value, row);
            setResultCells(value, row, qcFormat, lastFormat);

            status.setPercentComplete(70 * (valueIndex / values.size()) + 20);
            session.setAttribute("qcChartReport", status);
        }

        finishSheet(sheet, wb, qcName, qcType, lastAnalyte);

        while (sheetIndex < wb.getNumberOfSheets())
            wb.removeSheetAt(sheetIndex);
    } finally {
        baseFont = null;
        baseStyle = null;
        headerFont = null;
        headerStyle = null;
        maxChars = null;
        worksheetColumnMap = null;
        worksheetHeaders = null;
        worksheetHeaderNames = null;
    }

    return wb;
}

From source file:org.openurp.edu.other.web.action.OtherExamSignUpAction.java

License:Open Source License

protected EntityImporter buildEntityImporter() {
    String upload = "importFile";
    try {/*from w ww .java  2s .c o m*/
        File[] files = (File[]) ActionContext.getContext().getParameters().get(upload);
        if (files == null || files.length < 1) {
            logger.error("cannot get {} file.", upload);
        }
        String fileName = get(upload + "FileName");
        InputStream is = new FileInputStream(files[0]);
        if (fileName.endsWith(".xls")) {
            HSSFWorkbook wb = new HSSFWorkbook(is);
            if (wb.getNumberOfSheets() < 1 || wb.getSheetAt(0).getLastRowNum() == 0) {
                return null;
            }
            EntityImporter importer = new MultiEntityImporter();
            importer.setReader(new ExcelItemReader(wb, 1));
            put("importer", importer);
            return importer;
        } else {
            throw new RuntimeException("donot support other format except excel");
        }
    } catch (Exception e) {
        logger.error("error", e);
        return null;
    }
}

From source file:org.orbeon.oxf.processor.converter.FromXLSConverter.java

License:Open Source License

public ProcessorOutput createOutput(String name) {
    ProcessorOutput output = new ProcessorOutputImpl(FromXLSConverter.this, name) {
        public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) {

            try {
                // Read binary content of Excel file
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os);
                readInputAsSAX(context, INPUT_DATA, base64ContentHandler);
                final byte[] fileContent = os.toByteArray();

                // Generate XML from Excel file
                final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent);
                final org.dom4j.Document d = extractFromXLS(bais);
                final DOMGenerator domGenerator = new DOMGenerator(d, "from xls output",
                        DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext);
                domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver);

            } catch (IOException e) {
                throw new OXFException(e);
            }//from  w  w  w .j a v  a  2  s.c  om
        }

        private Document extractFromXLS(InputStream inputStream) throws IOException {

            // Create workbook
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));

            // Create document
            final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook");
            final NonLazyUserDataDocument resultDocument = new NonLazyUserDataDocument(root);

            // Add elements for each sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);

                final Element element = new NonLazyUserDataElement("sheet");
                resultDocument.getRootElement().add(element);

                // Go though each cell
                XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() {
                    public void cell(HSSFCell cell, String sourceXPath, String targetXPath) {
                        if (targetXPath != null) {
                            int cellType = cell.getCellType();
                            String value = null;
                            switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:
                            case HSSFCell.CELL_TYPE_BLANK:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                double doubleValue = cell.getNumericCellValue();
                                if (((double) ((int) doubleValue)) == doubleValue) {
                                    // This is an integer
                                    value = Integer.toString((int) doubleValue);
                                } else {
                                    // This is a floating point number
                                    value = XMLUtils.removeScientificNotation(doubleValue);
                                }
                                break;
                            }
                            if (value == null)
                                throw new OXFException("Unkown cell type " + cellType
                                        + " for XPath expression '" + targetXPath + "'");
                            addToElement(element, targetXPath, value);
                        }
                    }
                });
            }

            return resultDocument;
        }

        private void addToElement(Element element, String xpath, String value) {
            StringTokenizer elements = new StringTokenizer(xpath, "/");

            while (elements.hasMoreTokens()) {
                String name = elements.nextToken();
                if (elements.hasMoreTokens()) {
                    // Not the last: try to find sub element, otherwise create
                    Element child = element.element(name);
                    if (child == null) {
                        child = new NonLazyUserDataElement(name);
                        element.add(child);
                    }
                    element = child;
                } else {
                    // Last: add element, set content to value
                    Element child = new NonLazyUserDataElement(name);
                    child.add(Dom4jUtils.createText(value));
                    element.add(child);
                }
            }
        }
    };
    addOutput(name, output);
    return output;
}

From source file:org.orbeon.oxf.processor.generator.XLSGenerator.java

License:Open Source License

@Override
public ProcessorOutput createOutput(String name) {
    ProcessorOutput output = new ProcessorOutputImpl(XLSGenerator.this, name) {
        public void readImpl(PipelineContext context, XMLReceiver xmlReceiver) {

            try {
                // Read binary content of uploaded Excel file
                final byte[] fileContent;
                {/*w ww.j  a v a2 s  . com*/
                    final String NO_FILE = "No file was uploaded";
                    final DocumentInfo requestDocument = readInputAsTinyTree(context,
                            getInputByName(INPUT_REQUEST), XPathCache.getGlobalConfiguration());

                    final PooledXPathExpression expr = XPathCache.getXPathExpression(
                            requestDocument.getConfiguration(), requestDocument,
                            "/request/parameters/parameter[1]/value", getLocationData());

                    final Element valueElement = (Element) expr.evaluateSingleToJavaReturnToPoolOrNull();

                    if (valueElement == null)
                        throw new OXFException(NO_FILE);
                    String type = valueElement.attributeValue(XMLConstants.XSI_TYPE_QNAME);
                    if (type == null)
                        throw new OXFException(NO_FILE);

                    if (type.endsWith("anyURI")) {
                        // Read file from disk
                        String url = valueElement.getStringValue();
                        InputStream urlInputStream = new URL(url).openStream();
                        byte[] buffer = new byte[1024];
                        ByteArrayOutputStream fileByteArray = new ByteArrayOutputStream();
                        int size;
                        while ((size = urlInputStream.read(buffer)) != -1)
                            fileByteArray.write(buffer, 0, size);
                        urlInputStream.close();
                        fileContent = fileByteArray.toByteArray();
                    } else {
                        // Decode base64
                        fileContent = Base64.decode(valueElement.getStringValue());
                    }
                }

                // Generate XML from Excel file
                final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent);
                final org.dom4j.Document d = extractFromXLS(bais);
                final DOMGenerator domGenerator = new DOMGenerator(d, "xls generator output",
                        DOMGenerator.ZeroValidity, DOMGenerator.DefaultContext);
                domGenerator.createOutput(OUTPUT_DATA).read(context, xmlReceiver);
            } catch (IOException e) {
                throw new OXFException(e);
            }
        }

        private Document extractFromXLS(InputStream inputStream) throws IOException {

            // Create workbook
            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));

            // Create document
            final NonLazyUserDataElement root = new NonLazyUserDataElement("workbook");
            final Document resultDocument = new NonLazyUserDataDocument(root);

            // Add elements for each sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);

                final Element element = new NonLazyUserDataElement("sheet");
                resultDocument.getRootElement().add(element);

                // Go though each cell
                XLSUtils.walk(workbook.createDataFormat(), sheet, new XLSUtils.Handler() {
                    public void cell(HSSFCell cell, String sourceXPath, String targetXPath) {
                        if (targetXPath != null) {
                            int cellType = cell.getCellType();
                            String value = null;
                            switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:
                            case HSSFCell.CELL_TYPE_BLANK:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                double doubleValue = cell.getNumericCellValue();
                                if (((double) ((int) doubleValue)) == doubleValue) {
                                    // This is an integer
                                    value = Integer.toString((int) doubleValue);
                                } else {
                                    // This is a floating point number
                                    value = XMLUtils.removeScientificNotation(doubleValue);
                                }
                                break;
                            }
                            if (value == null)
                                throw new OXFException("Unkown cell type " + cellType
                                        + " for XPath expression '" + targetXPath + "'");
                            addToElement(element, targetXPath, value);
                        }
                    }
                });
            }

            return resultDocument;
        }

        private void addToElement(Element element, String xpath, String value) {
            StringTokenizer elements = new StringTokenizer(xpath, "/");

            while (elements.hasMoreTokens()) {
                String name = elements.nextToken();
                if (elements.hasMoreTokens()) {
                    // Not the last: try to find sub element, otherwise create
                    Element child = element.element(name);
                    if (child == null) {
                        child = new NonLazyUserDataElement(name);
                        element.add(child);
                    }
                    element = child;
                } else {
                    // Last: add element, set content to value
                    Element child = new NonLazyUserDataElement(name);
                    child.add(Dom4jUtils.createText(value));
                    element.add(child);
                }
            }
        }
    };
    addOutput(name, output);
    return output;
}

From source file:org.rti.zcore.dar.utils.PoiUtils.java

License:Apache License

/**
 * This utility is a version of HSSF.main that does not use deprecated methods.
 * It is helpful in figuring out what row a filed is on when outputting Excel files via POI.
 * @param pathExcelMaster/*from   w  w  w.  j a v  a 2s .c o m*/
 */
public static void testExcelOutput(String pathExcelMaster) {

    try {
        //HSSF hssf = new HSSF(args[ 0 ]);

        System.out.println("Data dump:\n");
        //HSSFWorkbook wb = hssf.hssfworkbook;
        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(pathExcelMaster));
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            System.out.println("Sheet " + k);
            HSSFSheet sheet = wb.getSheetAt(k);
            int rows = sheet.getPhysicalNumberOfRows();

            for (int r = 0; r < rows; r++) {
                //HSSFRow row   = sheet.getPhysicalRowAt(r);
                HSSFRow row = sheet.getRow(r);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("ROW " + row.getRowNum());
                    for (int c = 0; c < cells; c++) {
                        //HSSFCell cell  = row.getPhysicalCellAt(c);
                        HSSFCell cell = row.getCell(c);
                        String value = null;
                        if (cell != null) {
                            switch (cell.getCellType()) {

                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = "FORMULA ";
                                value = "FORMULA " + cell.getCellFormula();
                                break;

                            case HSSFCell.CELL_TYPE_NUMERIC:
                                value = "NUMERIC value=" + cell.getNumericCellValue();
                                break;

                            case HSSFCell.CELL_TYPE_STRING:
                                //value = "STRING value=" + cell.getStringCellValue();
                                HSSFRichTextString str = cell.getRichStringCellValue();
                                value = "STRING value=" + str;
                                break;

                            default:
                            }
                            //System.out.println("CELL col=" + cell.getCellNum()  + " VALUE=" + value);
                            System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}

From source file:org.sakaiproject.search.component.adapter.contenthosting.XLContentDigester.java

License:Educational Community License

public void loadContent(Writer writer, ContentResource contentResource) {
    if (contentResource != null && contentResource.getContentLength() > maxDigestSize) {
        throw new RuntimeException(
                "Attempt to get too much content as a string on " + contentResource.getReference());
    }/*from w  w w .  j  av  a  2  s.c o m*/
    if (contentResource == null) {
        throw new RuntimeException("Null contentResource passed the loadContent");
    }

    InputStream contentStream = null;
    try {
        contentStream = contentResource.streamContent();

        POIFSFileSystem fs = new POIFSFileSystem(contentStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0) {
                            writer.write(num + " ");
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0) {
                            writer.write(text + " ");
                        }
                        break;
                    }
                }
            }
        }

    } catch (Exception e) {
        throw new RuntimeException("Failed to read content for indexing ", e);
    } finally {
        if (contentStream != null) {
            try {
                contentStream.close();
            } catch (IOException e) {
                log.debug(e);
            }
        }
    }

}

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   w w w .  j  ava 2s.  c om
 * @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 w  w  .j  ava  2  s . com
 * @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);
    }
}

From source file:org.terrier.indexing.MSExcelDocument.java

License:Mozilla Public License

/** Get the reader appropriate for this InputStream. This involves
   converting the Excel document to a stream of words. On failure
   returns null and sets EOD to true, so no terms can be read from
   the object. /*w w  w .ja v  a 2 s  .  c om*/
   Uses the property <tt>indexing.excel.maxfilesize.mb</tt> to 
   determine if the file is too big to open
   @param docStream */
@SuppressWarnings("unchecked") //poi version used is for Java 1.4.
protected Reader getReader(InputStream docStream) {

    if (MAXFILESIZE > 0 && (filename == null || new File(filename).length() > MAXFILESIZE)) {

        logger.warn("WARNING: Excel document " + filename + " is too large for POI. Ignoring.");
        EOD = true;
        return null;
    }
    try {
        CharArrayWriter writer = new CharArrayWriter();
        //opening the file system
        POIFSFileSystem fs = new POIFSFileSystem(docStream);
        //opening the work book
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //got the i-th sheet from the work book
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {

                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0) {
                            writer.write(num + " ");
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0) {
                            writer.write(text + " ");
                        }
                        break;
                    }
                }
            }
        }
        return new CharArrayReader(writer.toCharArray());
    } catch (Exception e) {
        logger.warn("WARNING: Problem converting excel document" + e);
        EOD = true;
        return null;
    }
}

From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java

public static void addToDestination(HSSFWorkbook source, HSSFWorkbook destination) {
    ArrayList<String> listOfSheet = getListOfSheets(destination);
    int numberOfSheet = source.getNumberOfSheets();
    int j = 0;//  w  ww.j a v a2 s. co  m
    int sheetDestinationIndex;
    for (int i = 0; i < numberOfSheet; i++) {
        Sheet sheetSource = source.getSheetAt(i);
        String newName = sheetSource.getSheetName();
        sheetDestinationIndex = destination.getSheetIndex(newName);
        while (listOfSheet.contains(newName)) {
            newName = sheetSource.getSheetName() + "_" + j;
            newName = newName.substring(Math.max(newName.length() - 31, 0), Math.min(newName.length(), 31));
            sheetDestinationIndex = destination.getSheetIndex(newName);
            j++;
        }
        listOfSheet.add(newName);
        Sheet sheetDestination = destination.createSheet(newName);
        copyContent(sheetSource, sheetDestination);
    }
}