Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getNumberOfSheets

Introduction

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

Prototype

@Override
public int getNumberOfSheets() 

Source Link

Document

Get the number of worksheets in the this workbook

Usage

From source file:org.ecocean.servlet.importer.ImportExcelMetadata.java

License:Open Source License

public void processExcel(File dataFile, HttpServletResponse response, boolean committing,
        Hashtable<String, MediaAsset> assetIds, Shepherd myShepherd, PrintWriter out) throws IOException {

    FileInputStream fs = new FileInputStream(dataFile);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet;/*  ww  w .j  a v  a2  s  . c  o m*/
    XSSFRow row;

    sheet = wb.getSheetAt(0);

    if (wb.getNumberOfSheets() < 1) {
        out.println("!!! XSSFWorkbook did not find any sheets !!!");
    } else if (sheet.getClass() == null) {
        out.println("!!! Sheet was not successfully extracted !!!");
    } else {
        out.println("+++ Success creating FileInputStream and XSSF Worksheet +++");
    }

    int numSheets = wb.getNumberOfSheets();
    out.println("Num Sheets = " + numSheets);

    int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    out.println("Num Rows = " + physicalNumberOfRows);

    int rows = sheet.getPhysicalNumberOfRows();
    ; // No of rows
    int cols = sheet.getRow(0).getPhysicalNumberOfCells(); // No of columns
    out.println("Num Cols = " + cols);
    out.println("committing = " + committing);

    int printPeriod = 25;
    out.println("+++++ LOOPING THROUGH FILE +++++");
    String encId = null;
    boolean isValid = true;
    for (int i = 1; i < rows; i++) {
        try {
            if (committing)
                myShepherd.beginDBTransaction();

            row = sheet.getRow(i);

            // example
            if (getStringOrIntString(row, 7) != null) {
                encId = String.valueOf(getInteger(row, 7));
            } else {
                isValid = false;
            }
            out.println("---- CURRENT ID: " + encId + " ----");

            Encounter enc = null;
            if (committing && isValid == true) {
                enc = parseEncounter(row, myShepherd, out);
                String indID = null;
                try {
                    indID = getStringOrIntString(row, 7);
                } catch (Exception e) {
                    out.println("Not a valid indy for this row!");
                }

                MarkedIndividual ind = null;
                boolean needToAddEncToInd = false;
                if (indID != null) {
                    ind = myShepherd.getMarkedIndividualQuiet(indID);
                    if (ind == null) {
                        ind = new MarkedIndividual(indID, enc);
                    } else {
                        needToAddEncToInd = true;
                    }
                }
                try {
                    out.println("Adding media asset : " + encId);
                    enc.setState("approved");

                    myShepherd.beginDBTransaction();
                    if (committing && isValid == true)
                        myShepherd.storeNewEncounter(enc, Util.generateUUID());
                    myShepherd.commitDBTransaction();

                    String encIdS = String.valueOf(encId);
                    MediaAsset mal = assetIds.get(encIdS + "l");
                    MediaAsset mar = assetIds.get(encIdS + "r");
                    MediaAsset mac = assetIds.get(encIdS + "c");
                    MediaAsset map = assetIds.get(encIdS + "p");
                    try {
                        myShepherd.beginDBTransaction();
                        if (mal != null) {
                            enc.addMediaAsset(mal);
                        }
                        if (mac != null) {
                            enc.addMediaAsset(mac);
                        }
                        if (map != null) {
                            enc.addMediaAsset(map);
                        }
                        if (mar != null) {
                            enc.addMediaAsset(mar);
                        }
                        myShepherd.commitDBTransaction();
                    } catch (Exception npe) {
                        npe.printStackTrace();
                        out.println("!!! Failed to Add Media asset to Encounter  !!!");
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    out.println("!!! Failed to Store New Encounter  !!!");
                }
                if (committing && ind != null) {
                    myShepherd.beginDBTransaction();
                    myShepherd.storeNewMarkedIndividual(ind);
                    myShepherd.commitDBTransaction();
                    out.println("=== CREATED INDIVIDUAL " + ind.getName() + " ===");
                }
                myShepherd.beginDBTransaction();
                if (ind != null)
                    ind.addEncounter(enc);
                myShepherd.commitDBTransaction();

                // New Close it.
                if (i % printPeriod == 0) {
                    out.println("Parsed row (" + i + "), containing Enc " + enc.getEncounterNumber()
                            + " with Latitude " + enc.getDecimalLatitude() + " and Longitude "
                            + enc.getDecimalLongitude() + ", dateInMillis " + enc.getDateInMilliseconds()
                            + ", individualID " + enc.getIndividualID() + ", sex " + enc.getSex()
                            + ", living status " + enc.getLivingStatus() + ", identification notes "
                            + enc.getIdentificationRemarks());
                }
            }
        } catch (Exception e) {
            fs.close();
            out.println("!!! Encountered an error while Iterating through rows !!!");
            e.printStackTrace(out);
            myShepherd.rollbackDBTransaction();
        }
        isValid = true;
    }
    fs.close();
    wb.close();
}

---

From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java

License:Open Source License

/**
 * Returns only a text from .xlsx file content.
 * //from  ww  w.j a  v a2  s  .c o  m
 * @param is an input stream with .xls file content.
 * @return The string only with text from file content.
 */
public String getContentAsText(final InputStream is) throws IOException, DocumentReadException {
    if (is == null) {
        throw new IllegalArgumentException("InputStream is null.");
    }

    StringBuilder builder = new StringBuilder("");
    SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT);

    try {
        if (is.available() == 0) {
            return "";
        }

        XSSFWorkbook wb;
        try {
            wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() {
                public XSSFWorkbook run() throws Exception {
                    return new XSSFWorkbook(is);
                }
            });
        } catch (IOException e) {
            throw new DocumentReadException("Can't open spreadsheet.", e);
        } catch (OpenXML4JRuntimeException e) {
            return builder.toString();
        }
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            XSSFSheet sheet = wb.getSheetAt(sheetNum);
            if (sheet != null) {
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
                    XSSFRow row = sheet.getRow(rowNum);

                    if (row != null) {
                        int lastcell = row.getLastCellNum();
                        for (int k = 0; k < lastcell; k++) {
                            XSSFCell cell = row.getCell(k);
                            if (cell != null) {
                                switch (cell.getCellType()) {
                                case XSSFCell.CELL_TYPE_NUMERIC: {
                                    double d = cell.getNumericCellValue();
                                    if (isCellDateFormatted(cell)) {
                                        Date date = HSSFDateUtil.getJavaDate(d);
                                        String cellText = dateFormat.format(date);
                                        builder.append(cellText).append(" ");
                                    } else {
                                        builder.append(d).append(" ");
                                    }
                                    break;
                                }
                                case XSSFCell.CELL_TYPE_FORMULA:
                                    builder.append(cell.getCellFormula().toString()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_BOOLEAN:
                                    builder.append(cell.getBooleanCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_ERROR:
                                    builder.append(cell.getErrorCellValue()).append(" ");
                                    break;
                                case XSSFCell.CELL_TYPE_STRING:
                                    builder.append(cell.getStringCellValue().toString()).append(" ");
                                    break;
                                default:
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
    } finally {
        if (is != null) {
            try {
                is.close();
            } catch (IOException e) {
                if (LOG.isTraceEnabled()) {
                    LOG.trace("An exception occurred: " + e.getMessage());
                }
            }
        }
    }
    return builder.toString();
}

---

From source file:org.hlc.utility.excel.ExcelInputHandler.java

License:Apache License

/**
 * Import excel./*from   w  w  w.ja v  a2 s .com*/
 *
 * @param <T> the generic type
 * @param type the type
 * @param in the in
 * @return the list
 */
@SuppressWarnings("rawtypes")
public <T> List<T> importExcel2007(Class<T> type, InputStream in) {

    Excel excelAnn = type.getAnnotation(Excel.class);
    if (excelAnn == null) {
        throw new ExcelException("The Class <" + type + "> did not Excel");
    }

    List<T> list = new ArrayList<T>();

    Map<String, Method> mapping = new LinkedHashMap<String, Method>();
    Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>();

    try {
        // Step1 ??
        Field fileds[] = type.getDeclaredFields();
        for (int i = 0; i < fileds.length; i++) {
            Field field = fileds[i];
            ExcelColumn column = field.getAnnotation(ExcelColumn.class);
            if (column != null) {
                Method setMethod = ReflectionUtils.setValueMethod(field, type);
                mapping.put(column.value(), setMethod);
                if (column.converter() != TypeHandler.class) {
                    converters.put(setMethod.getName().toString(), column.converter().newInstance());
                } else {
                    converters.put(setMethod.getName().toString(),
                            TypeHandlerFactory.getHandler(field.getType()));
                }
            }
        }

        T temp = null;
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(in);
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            // ?Sheet
            List<Method> methods = new ArrayList<Method>();
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

                XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                // ?
                if (rowNum == 0) {
                    for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) {
                        String title = hssfRow.getCell(cellNum).getStringCellValue();
                        Method me = mapping.get(title);
                        if (me == null) {
                            continue;
                        }
                        methods.add(me);
                    }
                    continue;
                }

                temp = type.newInstance();
                for (int cellNum = 0; cellNum < methods.size(); cellNum++) {
                    XSSFCell xh = hssfRow.getCell(cellNum);
                    if (xh == null) {
                        continue;
                    }
                    Method m = methods.get(cellNum);
                    TypeHandler handler = converters.get(m.getName());
                    if (handler == null) {
                        continue;
                    }
                    xh.setCellType(Cell.CELL_TYPE_STRING);
                    String value = xh.getStringCellValue();
                    if (StringUtils.isEmpty(value)) {
                        continue;
                    }
                    Object val = null;
                    try {
                        val = handler.stringToType(value);
                    } catch (Exception e) {
                        throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1)
                                + "" + (cellNum + 1) + "" + value + "??");
                    }
                    methods.get(cellNum).invoke(temp, val);
                }
                list.add(temp);
            }
        }
    } catch (Exception e) {
        throw new ExcelException("Excel processing error?", e);
    }
    return list;
}

---

From source file:org.nuxeo.ecm.core.convert.plugins.text.extractors.XLX2TextConverter.java

License:Apache License

@Override
public BlobHolder convert(BlobHolder blobHolder, Map<String, Serializable> parameters)
        throws ConversionException {

    InputStream stream = null;/*from ww  w  .  j a  va  2 s .co  m*/
    StringBuffer sb = new StringBuffer();

    try {
        Blob blob = blobHolder.getBlob();

        if (blob.getLength() > maxSize4POI) {
            return runFallBackConverter(blobHolder, "xl/");
        }

        stream = blob.getStream();

        OPCPackage p = OPCPackage.open(stream);
        XSSFWorkbook workbook = new XSSFWorkbook(p);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
                XSSFRow row = (XSSFRow) rows.next();
                Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    XSSFCell cell = (XSSFCell) cells.next();
                    appendTextFromCell(cell, sb);
                }
                sb.append(ROW_SEP);
            }
        }
        return new SimpleCachableBlobHolder(Blobs.createBlob(sb.toString()));
    } catch (IOException | OpenXML4JException e) {
        throw new ConversionException("Error during XLX2Text conversion", e);
    } finally {
        if (stream != null) {
            try {
                stream.close();
            } catch (IOException e) {
                log.error("Error while closing Blob stream", e);
            }
        }
    }
}

---

From source file:org.olat.search.service.document.file.ExcelOOXMLDocument.java

License:Apache License

private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) {
    for (int i = 0; i < document.getNumberOfSheets(); i++) {
        final XSSFSheet sheet = document.getSheetAt(i);
        buffy.append(document.getSheetName(i)).append(' ');

        // Header(s), if present
        extractHeaderFooter(buffy, sheet.getFirstHeader());
        extractHeaderFooter(buffy, sheet.getOddHeader());
        extractHeaderFooter(buffy, sheet.getEvenHeader());

        // Rows and cells
        for (final Object rawR : sheet) {
            final Row row = (Row) rawR;
            for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) {
                final Cell cell = ri.next();

                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                        || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    buffy.append(cell.getRichStringCellValue().getString()).append(' ');
                } else {
                    final XSSFCell xc = (XSSFCell) cell;
                    final String rawValue = xc.getRawValue();
                    if (rawValue != null) {
                        buffy.append(rawValue).append(' ');
                    }//from  w w  w  .j  a  v a2 s.c o  m

                }

                // Output the comment in the same cell as the content
                final Comment comment = cell.getCellComment();
                if (comment != null) {
                    buffy.append(comment.getString().getString()).append(' ');
                }
            }
        }

        // Finally footer(s), if present
        extractHeaderFooter(buffy, sheet.getFirstFooter());
        extractHeaderFooter(buffy, sheet.getOddFooter());
        extractHeaderFooter(buffy, sheet.getEvenFooter());
    }
}

---

From source file:org.talend.dataprep.qa.util.ExcelComparator.java

License:Open Source License

public static boolean compareTwoFile(XSSFWorkbook workbook1, XSSFWorkbook workbook2) {
    int nbSheet1 = workbook1.getNumberOfSheets();
    int nbSheet2 = workbook2.getNumberOfSheets();
    if (nbSheet1 != nbSheet2) {
        return false;
    }//from w  w w.  j  ava 2s  .c o m
    boolean equalFile = true;
    for (int i = 0; i <= nbSheet1 - 1; i++) {
        XSSFSheet sheet1 = workbook1.getSheetAt(i);
        XSSFSheet sheet2 = workbook2.getSheetAt(i);
        if (!compareTwoSheets(sheet1, sheet2)) {
            equalFile = false;
            break;
        }
    }

    return equalFile;
}

---

From source file:org.tiefaces.components.websheet.chart.ChartHelper.java

License:MIT License

/**
 * initial chart map for XSSF format file. XSSF file is actually the only
 * format in POI support chart object./*from ww w. ja  v  a  2s . c o  m*/
 *
 * @param wb
 *            xssf workbook.
 * @param chartsData
 *            the charts data
 */
private void initXSSFChartsMap(final XSSFWorkbook wb, final ChartsData chartsData) {

    initAnchorsMap(wb, chartsData);
    Map<String, ClientAnchor> anchorMap = chartsData.getChartAnchorsMap();

    Map<String, BufferedImage> chartMap = chartsData.getChartsMap();
    Map<String, ChartData> chartDataMap = chartsData.getChartDataMap();
    chartMap.clear();
    chartDataMap.clear();

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        XSSFSheet sheet = wb.getSheetAt(i);
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        List<XSSFChart> charts = drawing.getCharts();
        if ((charts != null) && (!charts.isEmpty())) {
            for (XSSFChart chart : charts) {
                generateSingleXSSFChart(chart, getChartIdFromParent(chart, sheet.getSheetName()), sheet,
                        anchorMap, chartMap, chartDataMap);
            }
        }
    }

}

---

From source file:org.tiefaces.components.websheet.utility.ChartUtility.java

License:MIT License

/**
 * retrieve anchor information from draw.xml for all the charts in the
 * workbook. then save them to anchors map.
 *
 * @param wb//from   w  ww  . ja  va  2s. c  o  m
 *            workbook.
 * @param charsData
 *            the chars data
 */
public static void initXSSFAnchorsMap(final XSSFWorkbook wb, final ChartsData charsData) {

    Map<String, ClientAnchor> anchortMap = charsData.getChartAnchorsMap();
    Map<String, String> positionMap = charsData.getChartPositionMap();
    anchortMap.clear();
    positionMap.clear();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        initXSSFAnchorsMapForSheet(anchortMap, positionMap, wb.getSheetAt(i));
    }
}

---

From source file:org.tiefaces.components.websheet.utility.PicturesUtility.java

License:MIT License

/**
 * Gets the XSSF pictrues map.//from  w  ww  . j ava 2 s .  c o m
 *
 * @param wb
 *            the wb
 * @param picMap
 *            the pic map
 */
private static void getXSSFPictruesMap(final XSSFWorkbook wb, final Map<String, Picture> picMap) {

    picMap.clear();
    List<XSSFPictureData> pictures = wb.getAllPictures();
    if (pictures.isEmpty()) {
        return;
    }
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        XSSFSheet sheet = wb.getSheetAt(i);
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            try {
                indexPictureInMap(picMap, sheet, dr);
            } catch (Exception ex) {
                LOG.log(Level.SEVERE, "Load Picture error = " + ex.getLocalizedMessage(), ex);
            }
        }
    }

    return;

}

---

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java

License:Open Source License

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);/* www  . j  a  v  a  2 s .  co  m*/
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• »