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

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

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

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??/* w  w w  . ja  va 2s  .  c  o  m*/
 * @param sheet
 * @param map
 * @throws Exception 
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(IF_DELETE)) {
                    if (Boolean.valueOf(
                            eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map)
                                    .toString())) {
                        PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

From source file:cn.bzvs.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;/*from ww w . j a va 2  s.c om*/
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        row = rows.next();
        // ???,?,?
        // keyIndex ??,??
        if (params.getKeyIndex() != null
                && (row.getCell(params.getKeyIndex()) == null
                        || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))
                && object != null) {
            for (ExcelCollectionParams param : excelCollection) {
                addListContinue(object, param, row, titlemap, targetId, pictures, params);
            }
        } else {
            object = PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            saveFieldValue(params, object, cell, excelParams, titleString, row);
                        }
                    }
                }

                for (ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                }
                if (verifyingDataValidity(object, row, params, pojoClass)) {
                    collection.add(object);
                }
            } catch (ExcelImportException e) {
                if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java

License:Apache License

private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) {
    MetaInfo metaInfo = MetaInfo.forType(type, locale);
    Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Iterator<Row> rows = sheet.rowIterator();
    if (!rows.hasNext()) {
        return Collections.emptyList();
    }//w  w w . j a v a2s.c  o  m
    Row firstRow = rows.next();
    Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size());
    for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) {
        Cell cell = it.next();
        JsonElement jsonElement = parseAsJsonElement(cell, evaluator);
        if (jsonElement != null) {
            Field field = metaInfo.getField(jsonElement.getAsString());
            if (field != null) {
                String name = field.getName();
                int index = cell.getColumnIndex();
                columnIndexToFieldName.put(index, name);
            }
        }
    }
    if (columnIndexToFieldName.isEmpty()) {
        return Collections.emptyList();
    }
    List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum());
    while (rows.hasNext()) {
        result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type));
    }
    return result;
}

From source file:cn.poi.api.example.ExcelExample.java

License:Open Source License

public static void ReadExcel(String excel, String brandcode)
        throws EncryptedDocumentException, InvalidFormatException, IOException {
    List<BrandConfigCommand> list = new ArrayList<>();
    InputStream inp = resourceLoader.getResource(excel).getInputStream();
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(4);
    Row tempRow = null;//from   w  ww.j a  v  a  2 s  .co  m
    for (int i = 1; i < sheet.getLastRowNum(); i++) {
        BrandConfigCommand brandConfigCommand = new BrandConfigCommand();
        String[] array = new String[5];
        Row row = sheet.getRow(i);
        if (StringUtils.isEmpty(row.getCell(4).toString())) {
            continue;
        }
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (j == 0 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(0).getStringCellValue();
                continue;
            } else if (j == 0) {
                tempRow = row;
            }
            if (j == 1 && StringUtils.isEmpty(row.getCell(j).toString())) {
                System.out.print(tempRow.getCell(j).getStringCellValue() + "    ");
                array[j] = tempRow.getCell(j).getStringCellValue();
                continue;
            }
            array[j] = row.getCell(j).getStringCellValue();
            System.out.print(row.getCell(j).toString() + "    ");

        }

        brandConfigCommand.setStoreCode(array[0]);
        brandConfigCommand.setStoreDate(array[1]);
        brandConfigCommand.setProvice(array[2]);
        brandConfigCommand.setCity(array[3]);
        brandConfigCommand.setArea(array[4]);
        brandConfigCommand.setArea_name(array[2] + array[3] + array[4]);
        list.add(brandConfigCommand);
        System.out.println();
    }

    List<String> listStr = new ArrayList<>();
    for (BrandConfigCommand brandConfigCommand : list) {
        String str = sqlStart + brandConfigCommand.getArea_name() + sqlEnd + provice
                + brandConfigCommand.getProvice() + city + brandConfigCommand.getCity() + area
                + brandConfigCommand.getArea() + code + brandConfigCommand.getStoreCode() + brand_code
                + brandcode + "'";
        listStr.add(str);
    }
    System.out.println(JSON.toJSONString(listStr));

}

From source file:com.a9ski.jsf.exporter.DataTableExcelExporter.java

License:Apache License

protected void exportCells(final DataTable table, final Sheet sheet) throws ExportException {
    final int sheetRowIndex = sheet.getLastRowNum() + 1;
    final Row row = sheet.createRow(sheetRowIndex);

    for (final UIColumn col : getTableColumns(table)) {
        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }//  w ww  . jav  a 2s  . c  o m

        if (col.isRendered() && col.isExportable()) {
            addColumnValue(row, col.getChildren(), col, table);
        }
    }
}

From source file:com.a9ski.jsf.exporter.DataTableExcelExporter.java

License:Apache License

protected void addColumnFacets(final DataTable table, DataTableExporterOptions options, final Sheet sheet,
        final ColumnType columnType) throws ExportException {
    final int sheetRowIndex = columnType.equals(ColumnType.HEADER) ? getFirstHeaderRow(options)
            : (sheet.getLastRowNum() + 1);
    final Row rowHeader = sheet.createRow(sheetRowIndex);

    for (final UIColumn col : getTableColumns(table)) {
        if (col instanceof DynamicColumn) {
            ((DynamicColumn) col).applyStatelessModel();
        }/* www.  jav  a  2  s .c o  m*/

        if (col.isRendered() && col.isExportable()) {
            final UIComponent facet = col.getFacet(columnType.facet());
            if (facet != null) {
                addColumnValue(rowHeader, facet, col);
            } else {
                String textValue;
                switch (columnType) {
                case HEADER:
                    textValue = col.getHeaderText();
                    break;

                case FOOTER:
                    textValue = col.getFooterText();
                    break;

                default:
                    textValue = "";
                    break;
                }

                addColumnValue(rowHeader, new ExportValue(textValue, textValue, table), col);
            }
        }
    }
}

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

License:Open Source License

public void populateReport(Study study) throws Exception {
    assert study != null;

    this.study = study;

    initWorkbook();//from   w w w.j ava 2s .c  o  m

    //Create the workbook
    populateWorkBook();

    //Post processing
    //Add Table borders (between different styles of cells)
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (int r = 4; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null)
                continue;

            Row rowUp = sheet.getRow(r - 1);
            Row rowDown = sheet.getRow(r + 1);
            for (int c = 0; c <= row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                Cell cellLeft = c == 0 ? null : row.getCell(c - 1);
                boolean borderLeftAbove = cellLeft != null && cellLeft.getCellStyle().getBorderTop() == 1;
                boolean borderLeftUnder = cellLeft != null && cellLeft.getCellStyle().getBorderBottom() == 1;

                if ((cell != null
                        && cell.getCellStyle().getBorderLeft() + cell.getCellStyle().getBorderRight() > 0)
                        || (cell == null && c + 1 <= row.getLastCellNum() && row.getCell(c + 1) != null)) {
                    if (borderLeftAbove)
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    if (borderLeftUnder)
                        drawLineUnder(sheet, r, c, c, (short) 1);
                }

                if (cell != null) {
                    Font font = wb.getFontAt(cell.getCellStyle().getFontIndex());
                    if (font.getFontHeightInPoints() >= 12)
                        continue;

                    Cell cellUp = rowUp != null && c < rowUp.getLastCellNum() ? rowUp.getCell(c) : null;
                    Cell cellDown = rowDown != null && c < rowDown.getLastCellNum() ? rowDown.getCell(c) : null;

                    if (cellUp == null /*|| (cell.getCellType()!=0 && cellUp.getCellType()!=0 && cellUp.getCellType()!=cell.getCellType())*/ ) {
                        //Border above
                        drawLineAbove(sheet, r, c, c, (short) 1);
                    }
                    if (cellDown == null /*|| (cell.getCellType()!=0 && cellDown.getCellType()!=0 && cellDown.getCellType()!=cell.getCellType())*/) {
                        //Border under
                        drawLineUnder(sheet, r, c, c, (short) 1);
                    }
                }
            }
        }
    }

}

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  w w  . j a va2s .com
                }
            }
        }
    }

    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

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);//  ww  w . j  a  v a  2s.c  om
        if (r == null || r.getFirstCellNum() < 0)
            continue;
        short maxH = 0;

        for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) {
            Cell c = r.getCell(col);
            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.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();//from w w  w .  java 2s  .  c  om
    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());
    }

}