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: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  a 2s.  c  om*/

    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);/*  www.j a v  a  2 s. co  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./*from  w  w  w  .  j av  a  2 s. c o  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// ww  w.  j a  v  a2s  . com
 *            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./*from  w  w w.  j av  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.cenote.jasperstarter.ReportNGTest.java

License:Apache License

/**
 * Test of exportXls method, of class Report.
 */// w  ww .j  a v  a 2s  .c om
@Test(dependsOnMethods = { "testFill" })
public void testExportXls() throws Exception {
    System.out.println("exportXls");
    Config config = null;
    config = new Config();
    config.input = "target/test-classes/reports/compileToFile.jrprint";
    Report instance = new Report(config, new File(config.getInput()));
    instance.exportXls();
    assertEquals(((File) new File("target/test-classes/reports/compileToFile.xls")).exists(), true);
    // Read the content of a cell:
    InputStream inputStream = new FileInputStream("target/test-classes/reports/compileToFile.xls");
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0); // first sheet
    // select cell C12
    HSSFRow row = sheet.getRow(11);
    HSSFCell cell = row.getCell(2);
    assertEquals(cell.getStringCellValue(), "Carl Grant");
}

From source file:de.cenote.jasperstarter.ReportNGTest.java

License:Apache License

/**
 * Test of exportXlsMeta method, of class Report.
 *//*  w w  w  .j a va 2 s. co  m*/
@Test(dependsOnMethods = { "testFillMeta" })
public void testExportXlsMeta() throws Exception {
    System.out.println("exportXlsMeta");
    Config config = null;
    config = new Config();
    config.input = "target/test-classes/reports/csvMeta.jrprint";
    Report instance = new Report(config, new File(config.getInput()));
    instance.exportXlsMeta();
    assertEquals(((File) new File("target/test-classes/reports/csvMeta.xls")).exists(), true);
    // Read the content of a cell:
    InputStream inputStream = new FileInputStream("target/test-classes/reports/csvMeta.xls");
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0); // first sheet
    // select cell C12
    HSSFRow row = sheet.getRow(11);
    HSSFCell cell = row.getCell(2);
    assertEquals(cell.getStringCellValue(), "Dampremy");
}

From source file:de.chott.jfreechartsample.reader.FileReaderService.java

/**
 * Liest mit Hilfe der Apache-Poi-Library ein XLS-File aus und gibt die Daten darin als Liste von PieChartData-Objekten zurck. 
 * /*w ww.j av a2  s.  co m*/
 * @param stream Das File als Resource-InputStream
 * @return eine Liste der PieChartData
 * @throws IOException 
 */
public List<PieChartData> readPieChartDataFromXls(InputStream stream) throws IOException {
    List<PieChartData> retVal = new ArrayList<>();
    HSSFWorkbook workbook = new HSSFWorkbook(stream);

    HSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();

    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        PieChartData data = new PieChartData();

        Cell countryCell = row.getCell(0);
        Cell weightCell = row.getCell(1);

        if (countryCell != null && weightCell != null) {
            data.setCountry(countryCell.getStringCellValue());
            data.setWeight(weightCell.getNumericCellValue());

            retVal.add(data);
        }
    }

    return retVal;
}

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

/**
 * Gets a XLS file and parse it/*from   ww w.  ja  va2s  .  c o m*/
 * @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.sub.goobi.forms.ProzessverwaltungForm.java

License:Open Source License

/**
 * Generate result as PDF.//from w  w w  .ja  v  a2s . co m
 */
public void generateResultAsPdf() {
    FacesContext facesContext = FacesContext.getCurrentInstance();
    if (!facesContext.getResponseComplete()) {

        /*
         * Vorbereiten der Header-Informationen
         */
        HttpServletResponse response = (HttpServletResponse) facesContext.getExternalContext().getResponse();
        try {
            ServletContext servletContext = (ServletContext) facesContext.getExternalContext().getContext();
            String contentType = servletContext.getMimeType("search.pdf");
            response.setContentType(contentType);
            response.setHeader("Content-Disposition", "attachment;filename=\"search.pdf\"");
            ServletOutputStream out = response.getOutputStream();

            SearchResultGeneration sr = new SearchResultGeneration(this.filter, this.showClosedProcesses,
                    this.showArchivedProjects);
            HSSFWorkbook wb = sr.getResult();
            List<List<HSSFCell>> rowList = new ArrayList<>();
            HSSFSheet mySheet = wb.getSheetAt(0);
            Iterator<Row> rowIter = mySheet.rowIterator();
            while (rowIter.hasNext()) {
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator<Cell> cellIter = myRow.cellIterator();
                List<HSSFCell> row = new ArrayList<>();
                while (cellIter.hasNext()) {
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    row.add(myCell);
                }
                rowList.add(row);
            }
            Document document = new Document();
            Rectangle a4quer = new Rectangle(PageSize.A3.getHeight(), PageSize.A3.getWidth());
            PdfWriter.getInstance(document, out);
            document.setPageSize(a4quer);
            document.open();
            if (rowList.size() > 0) {
                Paragraph p = new Paragraph(rowList.get(0).get(0).toString());
                document.add(p);
                PdfPTable table = new PdfPTable(9);
                table.setSpacingBefore(20);
                for (List<HSSFCell> row : rowList) {
                    for (HSSFCell hssfCell : row) {
                        // TODO aufhbschen und nicht toString() nutzen
                        String stringCellValue = hssfCell.toString();
                        table.addCell(stringCellValue);
                    }
                }
                document.add(table);
            }

            document.close();
            out.flush();
            facesContext.responseComplete();
        } catch (Exception e) {
            logger.error(e);
        }
    }
}