Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getRow.

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected void drawLineUnder(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//from w ww.  j  av a  2 s . c o  m
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersUnder.get((c.getCellStyle().getIndex() << 4) + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderBottom(thickness);
            styleWithBordersUnder.put((c.getCellStyle().getIndex() << 4) + thickness, style);
        }
        c.setCellStyle(style);

    }
}

From source file:com.actelion.research.spiritapp.report.AbstractReport.java

License:Open Source License

protected void drawLineAbove(Sheet sheet, int row, int colMin, int colMax, short thickness) {
    Row r = sheet.getRow(row);
    if (r == null)
        r = sheet.createRow(row);//  ww  w.j ava  2s .  c om
    for (int col = colMin; col <= colMax; col++) {
        Cell c = r.getCell(col);
        if (c == null)
            c = r.createCell(col);
        CellStyle style = styleWithBordersAbove.get(c.getCellStyle().getIndex() << 4 + thickness);
        if (style == null) {
            style = sheet.getWorkbook().createCellStyle();
            style.cloneStyleFrom(c.getCellStyle());
            style.setBorderTop(thickness);
            styleWithBordersAbove.put(c.getCellStyle().getIndex() << 4 + thickness, style);
        }
        c.setCellStyle(style);
    }
}

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }// w  ww  .  j  av  a 2  s  . c om
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
    Class[] types = getTypes(table);
    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/*from   w w  w.j av a 2s .c o m*/
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 15);
    style = wb.createCellStyle();
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 10);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("th", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-border", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-double", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-right", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-bold", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-date", style);

    Sheet sheet = wb.createSheet();
    sheet.setFitToPage(true);

    Cell cell;

    int maxRows = 0;
    for (int r = 0; r < table.length; r++) {
        Row row = sheet.createRow(r);
        if (r == 0) {
            row.setRowStyle(styles.get("th"));
        }

        int rows = 1;
        for (int c = 0; c < table[r].length; c++) {
            cell = row.createCell(c);
            String s = table[r][c];
            if (s == null)
                continue;
            rows = Math.max(rows, s.split("\n").length);
            try {
                if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
                    cell.setCellStyle(styles.get("th"));
                    cell.setCellValue(s);

                } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
                    if (r == 0 && c == 0) {
                        cell.setCellStyle(styles.get("td"));
                    } else {
                        cell.setCellStyle(styles.get("th"));
                    }
                    cell.setCellValue(s);
                } else if (types[c] == Double.class) {
                    cell.setCellStyle(styles.get("td-double"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(s));
                } else if (types[c] == String.class) {
                    cell.setCellStyle(
                            styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(s);
                } else {
                    cell.setCellStyle(styles.get("td-right"));
                    cell.setCellValue(s);
                }
            } catch (Exception e) {
                cell.setCellStyle(styles.get("td"));
                cell.setCellValue(s);
            }
        }
        maxRows = Math.max(maxRows, rows);
        row.setHeightInPoints(rows * 16f);

    }

    // Add footer notes
    if (footerData.size() > 0) {
        Row row = sheet.createRow(table.length);
        row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
        cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
                row.getRowNum(), //last row  (0-based)
                0, //first column (0-based)
                table[0].length - 1 //last column  (0-based)
        ));
        //for ( String data : footerData ) {
        style = wb.createCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
        //}
    }
    footerData.clear();

    autoSizeColumns(sheet);
    if (table.length > 0) {
        for (int c = 0; c < table[0].length; c++) {
            if (sheet.getColumnWidth(c) > 10000)
                sheet.setColumnWidth(c, 3000);
        }
    }

    if (exportMode == ExportMode.HEADERS_TOPLEFT) {
        for (int r = 1; r < table.length; r++) {
            sheet.getRow(r).setHeightInPoints(maxRows * 16f);
        }
    }

    File reportFile = IOUtils.createTempFile("export_", ".xlsx");
    FileOutputStream out = new FileOutputStream(reportFile);
    wb.write(out);
    wb.close();
    out.close();
    Desktop.getDesktop().open(reportFile);
}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) {
    ListHashMap<Integer, Integer> col2lens = new ListHashMap<>();
    for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) {
        Row r = sheet.getRow(row);
        if (r == null || r.getFirstCellNum() < 0)
            continue;
        short maxH = 0;

        for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) {
            Cell c = r.getCell(col);/*from w w w  .j  a  va 2 s .  c om*/
            if (c == null
                    || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC))
                continue;

            Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex());
            String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue()
                    : "" + c.getNumericCellValue();
            String[] lines = MiscUtils.split(s, "\n");
            int maxLen = 1;
            for (int i = 0; i < lines.length; i++) {
                maxLen = Math.max(lines[i].length(), maxLen);
            }
            if (font.getFontHeightInPoints() < 12) {
                col2lens.add(col, 700
                        + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20);
            }
            maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2));
        }
        if (resizeHeight)
            r.setHeight(maxH);
    }

    for (int col : col2lens.keySet()) {
        List<Integer> lens = col2lens.get(col);
        Collections.sort(lens);
        int len = lens.get(lens.size() - 1);
        if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) {
            len = lens.get(lens.size() - 2);
        }
        sheet.setColumnWidth(col,
                Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500));
    }
}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 *  excel/* www  . ja  v a2 s  .c o m*/
 *
 * @param excelSheet   sheet ?
 * @param workbook     
 * @param outputStream ?
 */
private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream)
        throws IOException, InvocationTargetException, IllegalAccessException {

    Sheet sheet;
    sheet = workbook.createSheet();
    workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex());

    // write head
    writeHead(excelSheet, sheet);
    // sheet
    int writeRowIndex = excelSheet.getStartRowIndex();
    if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) {
        for (Object rowData : excelSheet.getDataList()) {
            // proc row
            Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex));

            writeRow(excelSheet, row, rowData);
            writeRowIndex++;
        }
    }
    workbook.write(outputStream);
}

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 * //from   ww w.j  a  va 2 s. c om
 *
 * @param excelSheet sheet ?
 * @param sheet      sheet
 */
private static void writeHead(ExcelSheet excelSheet, Sheet sheet) {
    Row row = Optional.ofNullable(sheet.getRow(excelSheet.getHeadRowIndex()))
            .orElse(sheet.createRow(excelSheet.getHeadRowIndex()));

    for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping()
            .export().entrySet()) {
        ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue();
        Integer colIndex = attribute.getIndex();
        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        cell.setCellValue(attribute.getHead());
    }

}

From source file:com.adobe.acs.commons.mcp.impl.TestGenericReportExcelServlet.java

License:Apache License

@Test
public void testReport() throws Exception {
    int numRows = 10;
    String reportPath = "/var/acs-commons/mcp/instances/junit/jcr:content/report";
    ResourceBuilder rb = slingContext.build()
            .resource(reportPath, "columns", new String[] { "ColumnA", "ColumnB" }, "name", "report",
                    "sling:resourceType",
                    "acs-commons/components/utilities/process-instance/process-generic-report")
            .resource("rows");
    rb.siblingsMode();//w  w  w. ja  v a  2 s  . co m
    for (int i = 1; i <= numRows; i++) {
        rb.resource("row-" + i, "ColumnA", "abcdef-" + i, "ColumnB", "qwerty-" + i);
    }
    MockSlingHttpServletRequest request = slingContext.request();
    request.setResource(slingContext.resourceResolver().getResource(reportPath));
    MockSlingHttpServletResponse response = slingContext.response();

    slingContext.addModelsForClasses(GenericReport.class);

    GenericReportExcelServlet servlet = new GenericReportExcelServlet();

    servlet.doGet(request, response);

    assertEquals("application/vnd.ms-excel", response.getContentType());

    Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(response.getOutput()));
    Sheet sh = wb.getSheetAt(0);
    assertEquals(numRows, sh.getLastRowNum());
    Row header = sh.getRow(0);
    assertEquals("Column A", header.getCell(0).getStringCellValue());
    assertEquals("Column B", header.getCell(1).getStringCellValue());
    for (int i = 1; i <= numRows; i++) {
        Row row = sh.getRow(i);
        assertEquals("abcdef-" + i, row.getCell(0).getStringCellValue());
        assertEquals("qwerty-" + i, row.getCell(1).getStringCellValue());
    }

}

From source file:com.adobe.ags.curly.controller.DataImporterController.java

License:Apache License

private List<List<String>> readSheet(Sheet sheet) {
    List<List<String>> data = new ArrayList<>();
    IntegerProperty numColumns = new SimpleIntegerProperty(sheet.getRow(0).getLastCellNum());
    sheet.forEach(row -> {//  w w w.ja  va2  s .  co m
        List<String> rowData = new ArrayList<>();
        numColumns.set(Math.max(numColumns.get(), row.getLastCellNum()));
        for (int i = 0; i < numColumns.get(); i++) {
            Cell cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL);
            String col = getStringValueFromCell(cell);
            rowData.add(col);
        }
        data.add(rowData);
    });
    return data;
}

From source file:com.antonov.elparser.impl.domain.ExcelWorker.java

public String getUniversity() throws Exception {

    String result = null;/*from  w ww.j  a va  2 s .  com*/

    try (FileInputStream is = new FileInputStream(filePath)) {

        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(ROW_UNIVERSITY);
        Cell cell = row.getCell(COLUMN_UNIVERSITY);
        result = cell.getStringCellValue().split("-")[1].trim();
    } catch (Throwable ex) {
        String message = "  ?? ?  ";
        logger.error(message, ex);
        throw new Exception(message, ex);
    }
    return result;
}