Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

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

Prototype

Sheet createSheet(String sheetname);

Source Link

Document

Create a new sheet for this Workbook and return the high level representation.

Usage

From source file:com.opendoorlogistics.studio.LoadedDatastore.java

License:Open Source License

private void updateWorkbookWithModifications(Workbook wb, ExecutionReport report) {
    // parse the original tables; these will be held in the datastore with the same index as the sheet
    int nbOriginal = originalLoadedDs.getTableCount();
    if (nbOriginal != wb.getNumberOfSheets()) {
        throw new RuntimeException();
    }/*from   ww w.  j a  va 2 s. c  o m*/

    ArrayList<ODLTableReadOnly> oldOnesToReadd = new ArrayList<>();
    for (int i = nbOriginal - 1; i >= 0; i--) {
        ODLTableReadOnly originalTable = originalLoadedDs.getTableAt(i);
        ODLTableReadOnly newTable = ds.getTableByImmutableId(originalTable.getImmutableId());

        if (newTable == null) {
            // table was deleted
            wb.removeSheetAt(i);
        } else if (DatastoreComparer.isSame(originalTable, newTable, DatastoreComparer.CHECK_ALL) == false) {
            Sheet sheet = wb.getSheetAt(i);

            boolean sameStructure = DatastoreComparer.isSameStructure(originalTable, newTable,
                    DatastoreComparer.CHECK_ALL);
            if (sameStructure) {
                // re-write all values but skip the header row
                int nbOversized = 0;
                for (int iRow = 0; iRow < newTable.getRowCount(); iRow++) {
                    int iTargetRow = iRow + 1;
                    Row row = sheet.getRow(iTargetRow);
                    if (row == null) {
                        row = sheet.createRow(iTargetRow);
                    }

                    int nc = newTable.getColumnCount();
                    for (int col = 0; col < nc; col++) {
                        Cell cell = row.getCell(col);
                        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                            // don't set the value of formula cells...
                            continue;
                        }
                        if (cell == null) {
                            cell = row.createCell(col);
                        }

                        String sval = TableUtils.getValueAsString(newTable, iRow, col);
                        if (sval != null && sval.length() > PoiIO.MAX_CHAR_COUNT_IN_EXCEL_CELL) {
                            nbOversized++;
                        }
                        cell.setCellValue(sval);
                    }
                }

                // delete any rows after the last row (including 1 for the header)
                int lastOKRow = newTable.getRowCount();
                while (sheet.getLastRowNum() > lastOKRow) {
                    sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
                }

                if (nbOversized > 0 && report != null) {
                    report.log(PoiIO.getOversizedWarningMessage(nbOversized, newTable.getName()));
                    ;
                }

            } else {
                // delete and replace. replace after parsing all original tables as we can get table name conflicts
                wb.removeSheetAt(i);
                oldOnesToReadd.add(newTable);
            }

        }

    }

    // re-add any totally replaced tables
    for (ODLTableReadOnly table : oldOnesToReadd) {
        Sheet sheet = wb.createSheet(table.getName());
        if (sheet != null) {
            PoiIO.exportTable(sheet, table, report);
        }
    }

    // add new tables at the end
    for (int i = 0; i < ds.getTableCount(); i++) {
        ODLTableReadOnly newTable = ds.getTableAt(i);
        if (originalLoadedDs.getTableByImmutableId(newTable.getImmutableId()) == null) {
            // new table...
            Sheet sheet = wb.createSheet(newTable.getName());
            if (sheet != null) {
                PoiIO.exportTable(sheet, newTable, report);
            }
        }

    }
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * /*w w w .j a  va 2  s. c o m*/
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.qihang.winter.poi.excel.export.ExcelExportServer.java

License:Apache License

public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel export start ,class is {}", pojoClass);
        LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }/*from   w  w w .j a  va 2  s  .  c o m*/
    if (workbook == null || entity == null || pojoClass == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    super.type = entity.getType();
    if (type.equals(ExcelType.XSSF)) {
        MAX_NUM = 1000000;
    }
    Sheet sheet = null;
    try {
        sheet = workbook.createSheet(entity.getSheetName());
    } catch (Exception e) {
        // ????,???,???Sheet
        sheet = workbook.createSheet();
    }
    try {
        dataHanlder = entity.getDataHanlder();
        if (dataHanlder != null) {
            needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
        }
        // ?
        setExcelExportStyler((com.qihang.winter.poi.excel.export.styler.IExcelExportStyler) entity.getStyle()
                .getConstructor(Workbook.class).newInstance(workbook));
        Drawing patriarch = sheet.createDrawingPatriarch();
        List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
        if (entity.isAddIndex()) {
            excelParams.add(indexExcelEntity(entity));
        }
        // 
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        String targetId = etarget == null ? null : etarget.value();
        getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null);
        sortAllParams(excelParams);
        int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
        int titleHeight = index;
        setCellWith(excelParams, sheet);
        short rowHeight = getRowHeight(excelParams);
        setCurrentIndex(1);
        Iterator<?> its = dataSet.iterator();
        List<Object> tempList = new ArrayList<Object>();
        while (its.hasNext()) {
            Object t = its.next();
            index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
            tempList.add(t);
            if (index >= MAX_NUM)
                break;
        }
        mergeCells(sheet, excelParams, titleHeight);

        if (entity.getFreezeCol() != 0) {
            sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
        }

        its = dataSet.iterator();
        for (int i = 0, le = tempList.size(); i < le; i++) {
            its.next();
            its.remove();
        }
        // ??
        addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);

        // ?list Sheet
        if (dataSet.size() > 0) {
            createSheet(workbook, entity, pojoClass, dataSet);
        }

    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
}

From source file:com.qihang.winter.poi.excel.export.ExcelExportServer.java

License:Apache License

public void createSheetForMap(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList,
        Collection<? extends Map<?, ?>> dataSet) {
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
    }//from  w  w  w .j a  v  a  2s .c om
    if (workbook == null || entity == null || entityList == null || dataSet == null) {
        throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
    }
    super.type = entity.getType();
    if (type.equals(ExcelType.XSSF)) {
        MAX_NUM = 1000000;
    }
    Sheet sheet = null;
    try {
        sheet = workbook.createSheet(entity.getSheetName());
    } catch (Exception e) {
        // ????,???,???Sheet
        sheet = workbook.createSheet();
    }
    try {
        dataHanlder = entity.getDataHanlder();
        if (dataHanlder != null) {
            needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
        }
        // ?
        setExcelExportStyler((com.qihang.winter.poi.excel.export.styler.IExcelExportStyler) entity.getStyle()
                .getConstructor(Workbook.class).newInstance(workbook));
        Drawing patriarch = sheet.createDrawingPatriarch();
        List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
        if (entity.isAddIndex()) {
            excelParams.add(indexExcelEntity(entity));
        }
        excelParams.addAll(entityList);
        sortAllParams(excelParams);
        int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
        int titleHeight = index;
        setCellWith(excelParams, sheet);
        short rowHeight = getRowHeight(excelParams);
        setCurrentIndex(1);
        Iterator<?> its = dataSet.iterator();
        List<Object> tempList = new ArrayList<Object>();
        while (its.hasNext()) {
            Object t = its.next();
            index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
            tempList.add(t);
            if (index >= MAX_NUM)
                break;
        }
        if (entity.getFreezeCol() != 0) {
            sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
        }

        mergeCells(sheet, excelParams, titleHeight);

        its = dataSet.iterator();
        for (int i = 0, le = tempList.size(); i < le; i++) {
            its.next();
            its.remove();
        }
        // ?list Sheet
        if (dataSet.size() > 0) {
            createSheetForMap(workbook, entity, entityList, dataSet);
        }

    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
        throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
    }
}

From source file:com.qualogy.qafe.service.DocumentExporter.java

License:Apache License

public static void exportExcel(DocumentExportInput input, ByteArrayOutputStream bout) {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("Exported data");
    if (input.getData() != null) {
        if (bout != null) {
            short index = 0;

            List<String> headerColumns = input.getColumnSequence();
            if (headerColumns == null) {
                headerColumns = getHeaderColumns(input.getData());
            }//from w  w w.ja  v  a  2s .  c o  m
            if (headerColumns != null) {
                if (input.isGenerateColumnHeader()) {
                    Row row = sheet.createRow(index++);
                    for (int i = 0; i < headerColumns.size(); i++) {
                        String columnHeader = headerColumns.get(i);
                        Cell cell = row.createCell(i);
                        cell.setCellValue(columnHeader);
                    }
                }

                for (Map<String, Object> map : input.getData()) {
                    Row row = sheet.createRow(index++);
                    for (int i = 0; i < headerColumns.size(); i++) {
                        String key = headerColumns.get(i);
                        Object value = map.get(key);
                        Cell cell = row.createCell(i);
                        setCellValue(cell, value);
                    }
                }
            }
        }
    }

    try {

        wb.write(bout);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body./*from ww w  . j  a v  a  2 s.  c om*/
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void sheetListing() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        for (String sn : names) {
            wb.createSheet(sn);
        }/*w ww . j  a v a 2s.  c  om*/
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        String[] foundNames = excerpter.getSheetNames(tmp);
        assertEquals(names.length, foundNames.length);
        for (int i = 0; i < names.length; i++) {
            assertEquals(names[i], foundNames[i]);
        }
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);//from ww  w .j  av  a2 s .c o  m
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptRemovesUnUsed() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        // Create some dummy content
        for (String sn : names) {
            Sheet s = wb.createSheet(sn);
            s.createRow(0).createCell(0).setCellValue(sn);
        }//from ww  w .j  a v a2  s  . co m

        // Excerpt by index
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        int[] excI = new int[] { 0, 1, 2, 4, 5 };
        excerpter.excerpt(excI, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(5, newwb.getNumberOfSheets());
        assertEquals(names[excI[0]], newwb.getSheetName(0));
        assertEquals(names[excI[1]], newwb.getSheetName(1));
        assertEquals(names[excI[2]], newwb.getSheetName(2));
        assertEquals(names[excI[3]], newwb.getSheetName(3));
        assertEquals(names[excI[4]], newwb.getSheetName(4));

        assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue());

        // Excerpt by name
        String[] excN = new String[] { "b", "ccc", "f", "gg" };
        baos = new ByteArrayOutputStream();
        excerpter.excerpt(excN, tmp, baos);

        newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(4, newwb.getNumberOfSheets());
        assertEquals(excN[0], newwb.getSheetName(0));
        assertEquals(excN[1], newwb.getSheetName(1));
        assertEquals(excN[2], newwb.getSheetName(2));
        assertEquals(excN[3], newwb.getSheetName(3));

        assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());

        // Can't excerpt by invalid index
        try {
            excerpter.excerpt(new int[] { 0, 10 }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }

        // Can't excerpt by invalid name
        try {
            excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }
    }
}

From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java

License:Apache License

/**
 * Creates a XSSFWorkbook with 1 default sheet "Sheet1" and returns the Worksheet
 * This is for simple temporary spreadsheets for calculation purpose
 * /*from w w  w  .j  av  a2s  .  com*/
 * @return
 */
public static Sheet createSimpleWorksheet(int numCols, int numRows) {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Sheet1");
    for (int i = 0; i < numRows; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < numCols; j++) {
            row.createCell(j);
        }
    }
    return sheet;
}