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:ddf.metrics.reporting.internal.rest.MetricsEndpointTest.java

License:Open Source License

@Test
public void testGetMetricsReportAsXls() throws Exception {
    // Create RRD file that Metrics Endpoint will detect
    int dateOffset = 900; // 15 minutes in seconds
    createRrdFile(dateOffset, "uptime");

    UriInfo uriInfo = createUriInfo();//w ww .j ava 2s . c  om

    // Get the metrics data from the endpoint
    MetricsEndpoint endpoint = getEndpoint();
    endpoint.setMetricsDir(TEST_DIR);

    Response response = endpoint.getMetricsReport("xls", null, null, Integer.toString(dateOffset), "minute",
            uriInfo);

    cleanupRrd();

    MultivaluedMap<String, Object> headers = response.getHeaders();
    assertTrue(headers.getFirst("Content-Disposition").toString().contains("attachment; filename="));

    InputStream is = (InputStream) response.getEntity();
    assertThat(is, not(nullValue()));

    HSSFWorkbook wb = new HSSFWorkbook(is);
    assertThat(wb.getNumberOfSheets(), equalTo(1));
    HSSFSheet sheet = wb.getSheetAt(0);
    assertThat(sheet, not(nullValue()));
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

@Test
public void testMetricsXlsDataWithCounter() throws Exception {
    String rrdFilename = TEST_DIR + "queryCount_Counter" + RRD_FILE_EXTENSION;
    long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).build();

    MetricsRetriever metricsRetriever = new RrdMetricsRetriever();
    OutputStream os = metricsRetriever.createXlsData("queryCount", rrdFilename, START_TIME, endTime);
    InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
    assertThat(xls, not(nullValue()));// w  w w  .java 2 s.  c o m

    HSSFWorkbook wb = new HSSFWorkbook(xls);
    assertThat(wb.getNumberOfSheets(), equalTo(1));

    HSSFSheet sheet = wb.getSheet("Query Count");
    if (null != sheet) {
        assertThat(sheet, not(nullValue()));
        verifyWorksheet(sheet, "Query Count", 6, true);
    } else {
        fail();
    }
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

@Test
public void testMetricsXlsDataWithGauge() throws Exception {
    String rrdFilename = TEST_DIR + "queryCount_Gauge" + RRD_FILE_EXTENSION;
    long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).dsType(DsType.GAUGE).build();

    MetricsRetriever metricsRetriever = new RrdMetricsRetriever();
    OutputStream os = metricsRetriever.createXlsData("queryCount", rrdFilename, START_TIME, endTime);
    InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
    assertThat(xls, not(nullValue()));/*from  www. j  a va 2s.co  m*/

    HSSFWorkbook wb = new HSSFWorkbook(xls);
    assertThat(wb.getNumberOfSheets(), equalTo(1));
    HSSFSheet sheet = wb.getSheet("Query Count");
    if (null != sheet) {
        assertThat(sheet, not(nullValue()));
        verifyWorksheet(sheet, "Query Count", 6, false);
    } else {
        fail();
    }
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

@Test
public void testMetricsXlsReport() throws Exception {
    String rrdFilename = TEST_DIR + "queryCount_Counter" + RRD_FILE_EXTENSION;
    new RrdFileBuilder().rrdFileName(rrdFilename).build();

    rrdFilename = TEST_DIR + "queryCount_Gauge" + RRD_FILE_EXTENSION;
    long endTime = new RrdFileBuilder().rrdFileName(rrdFilename).dsType(DsType.GAUGE).build();

    List<String> metricNames = new ArrayList<String>();
    metricNames.add("queryCount_Counter");
    metricNames.add("queryCount_Gauge");

    MetricsRetriever metricsRetriever = new RrdMetricsRetriever();
    OutputStream os = metricsRetriever.createXlsReport(metricNames, TEST_DIR, START_TIME, endTime, null);
    InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
    assertThat(xls, not(nullValue()));/*from w w  w .  jav  a2s . c o m*/

    HSSFWorkbook wb = new HSSFWorkbook(xls);
    assertThat(wb.getNumberOfSheets(), equalTo(2));

    HSSFSheet sheet = wb.getSheetAt(0);
    assertThat(sheet, not(nullValue()));
    verifyWorksheet(sheet, wb.getSheetName(0), 6, true);

    sheet = wb.getSheetAt(1);
    assertThat(sheet, not(nullValue()));
    verifyWorksheet(sheet, wb.getSheetName(1), 6, false);
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

@Test
public void testMetricsXlsReportSummary() throws Exception {
    String metricName = "queryCount_Gauge";
    long endTime = new DateTime(DateTimeZone.UTC).getMillis();
    List<String> metricNames = new ArrayList<String>();
    metricNames.add(metricName);/*from ww w. ja  v a  2  s .c  o  m*/
    for (RrdMetricsRetriever.SUMMARY_INTERVALS interval : RrdMetricsRetriever.SUMMARY_INTERVALS.values()) {
        long startTime = 0L;
        switch (interval) {
        case minute:
            startTime = new DateTime(DateTimeZone.UTC).minusHours(1).getMillis();
            break;
        case hour:
            startTime = new DateTime(DateTimeZone.UTC).minusDays(1).getMillis();
            break;
        case day:
            startTime = new DateTime(DateTimeZone.UTC).minusWeeks(1).getMillis();
            break;
        case week:
            startTime = new DateTime(DateTimeZone.UTC).minusMonths(1).getMillis();
            break;
        case month:
            startTime = new DateTime(DateTimeZone.UTC).minusYears(1).getMillis();
            break;
        }
        int sampleSize = (int) ((endTime - startTime) / (RRD_STEP * 1000));
        new RrdFileBuilder().rrdFileName(TEST_DIR + metricName + RRD_FILE_EXTENSION).dsType(DsType.GAUGE)
                .numSamples(sampleSize).numRows(sampleSize).startTime(startTime).build();
        MetricsRetriever metricsRetriever = new RrdMetricsRetriever();
        OutputStream os = metricsRetriever.createXlsReport(metricNames, TEST_DIR, startTime, endTime,
                interval.toString());
        InputStream xls = new ByteArrayInputStream(((ByteArrayOutputStream) os).toByteArray());
        assertThat(xls, not(nullValue()));

        HSSFWorkbook wb = new HSSFWorkbook(xls);
        assertThat(wb.getNumberOfSheets(), equalTo(1));

        HSSFSheet sheet = wb.getSheetAt(0);
        assertThat(sheet, not(nullValue()));
    }
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei./*  w w  w  . j a v a 2  s  .co  m*/
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    final int numberOfSheets = wb.getNumberOfSheets();
    final List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();

        final int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows + 1][columns];
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    wb.close();
    return sheetList;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//from   w  w  w  .  jav a2 s. c  o  m
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    final List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        final List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                final List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    wb.close();
    return sl;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Replace null cells into empty cells./*  w  w w  . ja v  a2 s .co  m*/
 *
 * @param excelSheet
 *            the excel sheet
 * @return the HSSF workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static HSSFWorkbook replaceNullCellsIntoEmptyCells(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j, Cell.CELL_TYPE_BLANK);
                    }
                }
            }
        }
    }
    return wb;
}

From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java

/**
 * Gets a XLS file and parse it/*from   w  ww .  j a  v a  2 s  .  c om*/
 * @param file The XLS File that you want to get parsed
 * @return A ArrayList where the first object is a Array containing the Data and the Second the Header
 */
public ArrayList<Object> parseXLSFile(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;
    try {
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        assert workbook != null;
        String[] strings = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strings.length; i++) {
            strings[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strings, strings[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strings.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            if (row != null) {
                headers = new String[row.getLastCellNum()];

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    headers[i] = row.getCell(i).toString();
                }
            }

            data = new String[sheet.getLastRowNum()][];
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                int rowCount = row.getLastCellNum();
                String[] dataRow = new String[rowCount];
                for (int i = 0; i < rowCount; i++) {
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    dataRow[i] = cell.toString();
                }
                data[j - 1] = dataRow;
            }
        } else {
            return null;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    ArrayList<Object> returnData = new ArrayList<>();
    returnData.add(data);
    returnData.add(headers);

    return returnData;
}

From source file:de.uni_siegen.wineme.come_in.thumbnailer.util.mime.XlsFileIdentifier.java

License:Open Source License

@Override
public String identify(String mimeType, byte[] bytes, File file) {

    if (isOfficeFile(mimeType) && !XLS_MIME_TYPE.equals(mimeType)) {
        try {//from w w  w  .  j  a v a2  s. c o  m
            FileInputStream stream = new FileInputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook(stream);

            if (workbook.getNumberOfSheets() != 0) {
                return XLS_MIME_TYPE;
            }
        } catch (Throwable e) {

        }
    }

    return mimeType;
}