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

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

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

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

@Override
protected boolean isValidSpreadsheet(Workbook workBook) throws IllegalStateException {
    boolean isValid = false;

    // get version information from registration sheet
    Sheet regSheet = workBook.getSheet(SHEET_TYPE_REGISTRATION);
    log.debug("Validating: {}", regSheet.getSheetName());
    isValid = isValidRegistrationSheet(regSheet);

    // continue validating result sheets
    if (isValid) {
        for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
            Sheet sheet = workBook.getSheetAt(i);
            if (sheet != null && !SHEET_TYPE_REGISTRATION.equals(sheet.getSheetName())
                    && !SHEET_TYPE_DUMMY.equals(sheet.getSheetName())) {
                log.debug("Validating: {}", sheet.getSheetName());
                isValid = isValidResultSheet(sheet);
                if (!isValid) {
                    log.warn("[{}] Invalid result sheet", sheet.getSheetName());
                    break;
                }//from w ww  .  j a  v a 2  s  .  co m
            }
        }
    } else {
        log.warn("[{}] Invalid registration sheet", regSheet.getSheetName());
    }

    return isValid;
}

From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java

License:Open Source License

@Override
protected List<Event> parseEvents(Workbook workBook) throws IllegalStateException {
    evaluator = workBook.getCreationHelper().createFormulaEvaluator();
    List<Event> events = new CopyOnWriteArrayList<Event>();
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        Sheet sheet = workBook.getSheetAt(i);
        if (isValidResultSheet(sheet)) {
            log.debug("Parsing: {}", sheet.getSheetName());
            Row firstRow = sheet.getRow(4); // first row with event results
            if (firstRow != null) {
                Cell cell = firstRow.getCell(1); // first cell with event results
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { // only parse sheet with content
                    Event event = parseEventDetails(sheet);
                    event.setLive(workBook.getActiveSheetIndex() == i);
                    List<Result> results = new CopyOnWriteArrayList<Result>();
                    for (int j = 4; j < sheet.getLastRowNum(); j++) {
                        Row row = sheet.getRow(j);
                        if (row != null) {
                            Result result = parseResultRow(row, event);
                            if (result.getFirstname() != null && result.getSurname() != null) {
                                results.add(result);
                            }// w ww. j  a  v  a 2  s. c o  m
                        }
                    }
                    if (!results.isEmpty()) {
                        event.setResults(results);
                        events.add(event);
                    }
                }
            }
        }
    }
    return events;
}

From source file:edu.gatech.pmase.capstone.awesome.impl.database.AbstractDatabaseDriver.java

License:Open Source License

/**
 * Reads the custom attribute values from the custom attribute sheet.
 *
 * @param wb the workbook to read the custom attributes from
 */// w w w .  ja  va 2 s.  c  o m
protected synchronized void setCustomAttributes(final Workbook wb) {
    LOGGER.info("Loading custom attribute list");
    customAttributes.clear();

    final Sheet customSheet = wb.getSheetAt(CUSTOM_ATTR_SHEET_NUM);
    if (null != customSheet) {
        LOGGER.debug("Custom attribute list stored in sheet: " + customSheet.getSheetName());

        final int maxRows = customSheet.getPhysicalNumberOfRows();
        if (maxRows > 1) {
            for (int rowIter = 1; rowIter < maxRows; rowIter++) {
                final Row row = customSheet.getRow(rowIter);
                if (null != row) {
                    try {
                        customAttributes.add(AbstractDatabaseDriver.getAttributeDescriptionFromRow(row));
                    } catch (ClassNotFoundException ex) {
                        LOGGER.error("Could not load custom attribute for row: " + row.getRowNum(), ex);
                    }
                }
            }
        }
    } else {
        LOGGER.warn("Could not load customer sheet");
    }
}

From source file:edu.gatech.pmase.capstone.awesome.impl.database.AbstractDatabaseDriver.java

License:Open Source License

/**
 * Reads the options from the workbook./*  w  w w . j a  va  2s .  c  o m*/
 *
 * @param workbook the workbook to read from
 *
 * @return the List of options in the workbook
 */
private List<T> readOptionsFromWorkbook(final Workbook workbook) {
    final List<T> options = new ArrayList<>();

    // get first sheet
    final Sheet sheet = workbook.getSheetAt(0);

    // max rows
    int maxRows = sheet.getPhysicalNumberOfRows();
    if (maxRows > 1) {
        for (int rowIter = 1; rowIter < maxRows; rowIter++) {
            final Row row = sheet.getRow(rowIter);
            if (null != row) {
                final T opt = this.getOptionFromRow(row);
                if (null != opt) {
                    options.add(opt);
                } else {
                    LOGGER.trace("Could not make option from row " + rowIter);
                }
            } else {
                LOGGER.debug("Loaded Invalid Row: " + rowIter);
            }
        }
    } else {
        LOGGER.error("Database does not have the expected number of rows. Must have more than one row.");
    }

    return options;
}

From source file:edu.jhu.pha.vospace.process.tika.ExcelParser.java

License:Apache License

@Override
public void parse(InputStream stream, ContentHandler handler, Metadata metadata, ParseContext context)
        throws IOException, SAXException, TikaException {

    XHTMLContentHandler xhtml = new XHTMLContentHandler(handler, metadata);
    xhtml.startDocument();/*from w  w w . j  ava  2 s .com*/

    Workbook wb;

    try {
        wb = WorkbookFactory.create(stream);
    } catch (InvalidFormatException e) {
        throw new TikaException("Invalid format");
    }

    Sheet sheet = wb.getSheetAt(0);
    int nRows = sheet.getLastRowNum();
    int nCols = sheet.getRow(0).getLastCellNum();
    xhtml.startElement("section", "id", String.valueOf(1));

    AttributesImpl attributes = new AttributesImpl();
    //attributes.addAttribute("", "id", "id", "CDATA", String.valueOf(1));
    attributes.addAttribute("", "columns", "columns", "CDATA", String.valueOf(nCols));
    xhtml.startElement("table", attributes);

    Row headerRow = sheet.getRow(0);
    xhtml.startElement("th", "info", "columnNames");
    for (int j = 0; j < nCols; j++) {
        Cell cell = headerRow.getCell(j);
        String columnName = cell.getStringCellValue();
        xhtml.element("td", columnName);
    }
    xhtml.endElement("th");
    Row firstDataRow = sheet.getRow(1);
    xhtml.startElement("th", "info", "columnTypes");
    for (int j = 0; j < nCols; j++) {
        Cell cell = firstDataRow.getCell(j);
        int type = cell.getCellType();
        String columnType = null;
        switch (type) {
        case Cell.CELL_TYPE_NUMERIC:
            columnType = "D";
            break;
        case Cell.CELL_TYPE_STRING:
            columnType = "A";
            break;
        }
        xhtml.element("td", columnType);
    }
    xhtml.endElement("th");

    for (int i = 1; i <= nRows; i++) {
        Row row = sheet.getRow(i);
        xhtml.startElement("tr");
        for (int j = 0; j < nCols; j++) {
            Cell cell = row.getCell(j);
            int type = cell.getCellType();
            switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                xhtml.element("td", String.valueOf(cell.getNumericCellValue()));
                break;
            case Cell.CELL_TYPE_STRING:
                xhtml.element("td", cell.getStringCellValue());
                break;
            }
        }
        xhtml.endElement("tr");

    }
    xhtml.endElement("table");
    xhtml.endElement("section");
    xhtml.endDocument();

    metadata.add(TikaCoreProperties.TYPE, "EXCEL");
}

From source file:edu.swjtu.excel.InportArrExcel.java

/**
 * ?excel//from  w  w  w.java 2  s  . com
 * 2016716?7:24:58
 * @author jimolonely
 * @param excelPath
 * @throws IOException
 * @throws InstantiationException
 * @throws IllegalAccessException
 */
public ArrayList<ArrCarLine> inport(String excelPath)
        throws IOException, InstantiationException, IllegalAccessException {
    InputStream fis = new FileInputStream(excelPath);
    String fileType = excelPath.substring(excelPath.lastIndexOf(".") + 1, excelPath.length());
    ArrayList<ArrCarLine> data = new ArrayList<ArrCarLine>();
    Workbook wb = null;
    if (fileType.equals("xlsx")) {
        wb = new XSSFWorkbook(fis);
    } else if (fileType.equals("xls")) {
        wb = new HSSFWorkbook(fis);
    }
    Sheet sht0 = wb.getSheetAt(0);
    for (Row r : sht0) {
        if (r.getRowNum() < 1) {
            continue;// 
        }
        ArrCarLine acl = new ArrCarLine();
        for (int i = 0; i < 7; i++) {
            if (r.getCell(i) == null)
                return null;
            r.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
            String cellValue = r.getCell(i).getStringCellValue();

            switch (i) {
            case 0:
                acl.setArrangeId(Integer.parseInt(cellValue));
                break;
            case 1:
                acl.setArrName(cellValue);
                break;
            case 2:
                acl.setDate(cellValue);
                break;
            case 3:
                acl.setTime(cellValue);
                break;
            case 4:
                acl.setLineName(cellValue);
                break;
            case 5:
                acl.setLicensePlate(cellValue);
                break;
            case 6:
                acl.setDriver(cellValue);
                break;
            }
        }
        data.add(acl);
    }
    fis.close();
    return data;
}

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.ImportTextTableDialog.java

License:Open Source License

/**
 * Load from the data source.<br>/*from w  w  w .j  ava  2  s.  c o m*/
 *
 * @param evt
 * @throws Exception
 */
private void importButtonActionPerformed(ActionEvent evt) throws Exception {
    if (checkDataSourceError() == false)
        return;

    boolean importAll = importAllCheckBox.isSelected();

    /*
     * Get start line number. If "transfer" check box is true, then start
     * reading from the second line.
     */
    int startLineNumber;
    final int spinnerNumber = Integer.parseInt(startRowSpinner.getValue().toString());

    if (transferNameCheckBox.isSelected()) {
        startLineNumber = spinnerNumber;
    } else {
        startLineNumber = spinnerNumber - 1;
    }

    final String commentChar = commentLineTextField.getText();

    /*
     * Get import flags
     */
    final int colCount = previewPanel.getPreviewTable().getColumnModel().getColumnCount();
    importFlag = new boolean[colCount];

    for (int i = 0; i < colCount; i++) {
        importFlag[i] = ((AttributePreviewTableCellRenderer) previewPanel.getPreviewTable().getCellRenderer(0,
                i)).getImportFlag(i);
    }

    /*
     * Get Attribute Names
     */
    final String[] attributeNames;
    final List<String> attrNameList = new ArrayList<String>();

    Object curName = null;

    for (int i = 0; i < colCount; i++) {
        curName = previewPanel.getPreviewTable().getColumnModel().getColumn(i).getHeaderValue();

        if (attrNameList.contains(curName)) {
            int dupIndex = 0;

            for (int idx = 0; idx < attrNameList.size(); idx++) {
                if (curName.equals(attrNameList.get(idx))) {
                    dupIndex = idx;

                    break;
                }
            }

            if (importFlag[i] && importFlag[dupIndex]) {
                final JLabel label = new JLabel("Duplicate Attribute Name Found: " + curName);
                label.setForeground(Color.RED);
                JOptionPane.showMessageDialog(this, label);

                return;
            }
        }

        if (curName == null) {
            attrNameList.add("Column " + i);
        } else {
            attrNameList.add(curName.toString());
        }
    }

    attributeNames = attrNameList.toArray(new String[0]);

    /*
     * Get attribute data types
     */

    // final byte[] attributeTypes = new byte[previewPanel.getPreviewTable()
    // .getColumnCount()];
    final Byte[] test = previewPanel.getDataTypes(previewPanel.getSelectedSheetName());
    final Byte[] attributeTypes = new Byte[test.length];

    for (int i = 0; i < test.length; i++) {
        attributeTypes[i] = test[i];
    }

    // for (int i = 0; i < attributeTypes.length; i++) {
    // attributeTypes[i] = attributeDataTypes.get(i);
    // }
    final List<Integer> aliasList = new ArrayList<Integer>();
    String mappingAttribute = ID;

    if (dialogType != NETWORK_IMPORT) {
        /*
         * Get column indecies for alias
         */
        JTable curTable = aliasTableMap.get(previewPanel.getSelectedSheetName());

        if (curTable != null) {
            for (int i = 0; i < curTable.getModel().getRowCount(); i++) {
                if ((Boolean) curTable.getModel().getValueAt(i, 0) == true) {
                    aliasList.add(i);
                }
            }
        }

        /*
         * Get mapping attribute
         */
        mappingAttribute = mappingAttributeComboBox.getSelectedItem().toString();
    }

    ObjectType objType = null;

    if (dialogType != NETWORK_IMPORT) {
        if (nodeRadioButton.isSelected()) {
            objType = NODE;
        } else if (edgeRadioButton.isSelected()) {
            objType = EDGE;
        } else {
            objType = NETWORK;
        }
    }

    /*
     * Switch readers based on the dialog type.
     */
    switch (dialogType) {
    case SIMPLE_ATTRIBUTE_IMPORT:

        /*
         * Case 1: Attribute table import.
         */
        // Extract URL from the text table.
        final URL source = new URL(targetDataSourceTextField.getText());
        // Make sure primary key index is up-to-date.
        keyInFile = primaryKeyComboBox.getSelectedIndex();

        // Build mapping parameter object.
        final AttributeMappingParameters mapping;
        final List<String> del;

        System.out.println("IsCytoscapeAttributeFile " + previewPanel.isCytoscapeAttributeFile(source));
        if (previewPanel.isCytoscapeAttributeFile(source)) {
            del = new ArrayList<String>();
            del.add(" += +");
        } else {
            del = checkDelimiter();
        }
        mapping = new AttributeMappingParameters(objType, del, listDelimiter, keyInFile, mappingAttribute,
                aliasList, attributeNames, attributeTypes, listDataTypes, importFlag, caseSensitive);

        if (source.toString().endsWith(SupportedFileType.EXCEL.getExtension())
                || source.toString().endsWith(SupportedFileType.OOXML.getExtension())) {
            /*
             * Read one sheet at a time
             */
            InputStream is = null;
            Workbook wb = null;

            try {
                is = source.openStream();
                wb = WorkbookFactory.create(is);
            } finally {
                if (is != null) {
                    is.close();
                }
            }

            // Load all sheets in the table
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                final Sheet sheet = wb.getSheetAt(i);

                loadAnnotation(new ExcelAttributeSheetReader(sheet, mapping, startLineNumber, importAll),
                        source.toString());
            }
        } else {
            loadAnnotation(new DefaultAttributeTableReader(source, mapping, startLineNumber, null, importAll),
                    source.toString());
        }

        break;

    case ONTOLOGY_AND_ANNOTATION_IMPORT:

        /*
         * Case 2: Import Ontology and its annotation.
         */
        final String selectedOntologyName = ontologyComboBox.getSelectedItem().toString();
        final String ontologySourceLocation = ontologyUrlMap.get(selectedOntologyName);

        /*
         * If selected ontology is not loaded, load it first.
         */
        if (Cytoscape.getOntologyServer().getOntologyNames().contains(selectedOntologyName) == false)
            loadOntology(ontologySourceLocation, selectedOntologyName);

        /*
         * Now, load & map annotation.
         */
        final String annotationSource = annotationUrlMap.get(annotationComboBox.getSelectedItem());
        final URL annotationSourceUrl = new URL(annotationSource);

        if (previewPanel.getFileType() == FileTypes.GENE_ASSOCIATION_FILE) {
            /*
             * This is a Gene Association file.
             */
            GeneAssociationReader gaReader = null;
            keyInFile = this.primaryKeyComboBox.getSelectedIndex();

            InputStream is = null;
            try {
                is = URLUtil.getInputStream(annotationSourceUrl);
                gaReader = new GeneAssociationReader(selectedOntologyName, is, mappingAttribute, importAll,
                        keyInFile, caseSensitive);
            } catch (Exception e) {
                if (is != null) {
                    is.close();
                }
                throw e;
            }

            loadGeneAssociation(gaReader, selectedOntologyName, annotationSource);
        } else {
            /*
             * This is a custom annotation file.
             */
            final int ontologyIndex = ontologyInAnnotationComboBox.getSelectedIndex();

            final AttributeAndOntologyMappingParameters aoMapping = new AttributeAndOntologyMappingParameters(
                    objType, checkDelimiter(), listDelimiter, keyInFile, mappingAttribute, aliasList,
                    attributeNames, attributeTypes, listDataTypes, importFlag, ontologyIndex,
                    selectedOntologyName, caseSensitive);
            final OntologyAnnotationReader oaReader = new OntologyAnnotationReader(annotationSourceUrl,
                    aoMapping, commentChar, startLineNumber);

            loadAnnotation(oaReader, annotationSource);
        }

        break;

    case NETWORK_IMPORT:

        /*
         * Case 3: read as network table (Network + Edge Attributes)
         */

        // Extract URL from the text table.
        /*
         * Now multiple files are supported.
         */
        URL[] sources = new URL[inputFiles.length];

        for (int i = 0; i < sources.length; i++) {
            sources[i] = inputFiles[i].toURI().toURL();
        }

        //final URL networkSource = new URL(targetDataSourceTextField.getText());
        final int sourceColumnIndex = networkImportPanel.getSourceIndex();
        final int targetColumnIndex = networkImportPanel.getTargetIndex();

        final String defaultInteraction = defaultInteractionTextField.getText();

        final int interactionColumnIndex = networkImportPanel.getInteractionIndex();

        final NetworkTableMappingParameters nmp = new NetworkTableMappingParameters(checkDelimiter(),
                listDelimiter, attributeNames, attributeTypes, null, importFlag, sourceColumnIndex,
                targetColumnIndex, interactionColumnIndex, defaultInteraction);

        NetworkTableReader reader;
        String networkName;
        boolean multi = true;

        if (sources.length == 1)
            multi = false;

        for (int i = 0; i < sources.length; i++) {
            if (sources[i].toString().endsWith(SupportedFileType.EXCEL.getExtension())
                    || sources[i].toString().endsWith(SupportedFileType.OOXML.getExtension())) {
                // Extract name from the sheet name.
                InputStream is = null;
                Workbook wb = null;
                try {
                    is = sources[i].openStream();
                    wb = WorkbookFactory.create(is);
                } finally {
                    if (is != null) {
                        is.close();
                    }
                }

                Sheet sheet = wb.getSheetAt(0);
                networkName = wb.getSheetName(0);

                reader = new ExcelNetworkSheetReader(networkName, sheet, nmp, startLineNumber);
            } else {
                // Get name from URL.
                if ((commentChar != null) && (commentChar.length() != 0) && transferNameCheckBox.isSelected()) {
                    startLineNumber++;
                }

                final String[] parts = sources[i].toString().split("/");
                networkName = parts[parts.length - 1];
                reader = new NetworkTableReader(networkName, sources[i], nmp, startLineNumber, commentChar);
            }

            loadNetwork(networkName, reader, sources[i], multi);
        }

        if (multi) {
            StringBuilder builder = new StringBuilder();
            builder.append("The following networks are loaded:\n\n");

            for (File f : inputFiles) {
                builder.append(f.getName() + "\n");
            }

            JOptionPane.showMessageDialog(this, builder.toString(), "Multiple Networks Loaded",
                    JOptionPane.INFORMATION_MESSAGE);
        }

        break;

    default:
        return;
    }

    Cytoscape.firePropertyChange(Cytoscape.ATTRIBUTES_CHANGED, null, null);

    dispose();
}

From source file:edu.ucsd.bioeng.coreplugin.tableImport.ui.PreviewTablePanel.java

License:Open Source License

/**
 * Load file and show preview.//from   ww w.  j  a v a 2 s  . co  m
 * 
 * @param sourceURL
 * @param delimiters
 * @param renderer
 *            renderer for this table. Can be null.
 * @param size
 * @param commentLineChar
 *            TODO
 * @param startLine
 *            TODO
 * @throws IOException
 */
public void setPreviewTable(URL sourceURL, List<String> delimiters, TableCellRenderer renderer, int size,
        final String commentLineChar, final int startLine) throws IOException {
    TableCellRenderer curRenderer = renderer;

    if ((commentLineChar != null) && (commentLineChar.trim().length() != 0))
        this.commentChar = commentLineChar;
    else
        this.commentChar = null;
    /*
     * If rendrer is null, create default one.
     */
    if (curRenderer == null) {
        curRenderer = new AttributePreviewTableCellRenderer(0, new ArrayList<Integer>(),
                AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST,
                AttributePreviewTableCellRenderer.PARAMETER_NOT_EXIST, null,
                TextFileDelimiters.PIPE.toString());
    }

    /*
     * Reset current state
     */
    for (int i = 0; i < tableTabbedPane.getTabCount(); i++)
        tableTabbedPane.removeTabAt(i);

    previewTables = new HashMap<String, JTable>();

    TableModel newModel;

    fileTypeLabel.setVisible(true);

    if (sourceURL.toString().endsWith(SupportedFileType.EXCEL.getExtension())
            || sourceURL.toString().endsWith(SupportedFileType.OOXML.getExtension())) {

        fileTypeLabel.setIcon(SPREADSHEET_ICON.getIcon());
        fileTypeLabel.setText("Excel" + '\u2122' + " Workbook");

        InputStream is = null;
        final Workbook wb;

        try {
            is = sourceURL.openStream();
            wb = WorkbookFactory.create(is);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            throw new IllegalArgumentException("Could not read Excel file.  Maybe the file is broken?");
        } finally {
            if (is != null)
                is.close();
        }

        if (wb.getNumberOfSheets() == 0)
            throw new IllegalStateException("No sheet found in the workbook.");

        /*
         * Load each sheet in the workbook.
         */
        logger.debug("# of Sheets = " + wb.getNumberOfSheets());

        Sheet sheet = wb.getSheetAt(0);
        logger.debug("Sheet name = " + wb.getSheetName(0) + ", ROW = " + sheet.rowIterator().hasNext());

        newModel = parseExcel(sourceURL, size, curRenderer, sheet, startLine);

        if (newModel.getRowCount() == 0)
            throw new IllegalStateException("No data found in the Excel sheet.");

        DataTypeUtil.guessTypes(newModel, wb.getSheetName(0), dataTypeMap);
        listDataTypeMap.put(wb.getSheetName(0), initListDataTypes(newModel));
        addTableTab(newModel, wb.getSheetName(0), curRenderer);
    } else {
        if (isCytoscapeAttributeFile(sourceURL)) {
            fileTypeLabel.setText("Cytoscape Attribute File");
            fileTypeLabel.setIcon(new ImageIcon(Cytoscape.class.getResource("images/icon48.png")));
            newModel = parseText(sourceURL, size, curRenderer, null, 1);
        } else {
            fileTypeLabel.setText("Text File");
            fileTypeLabel.setIcon(TEXT_FILE_ICON.getIcon());
            newModel = parseText(sourceURL, size, curRenderer, delimiters, startLine);
        }

        String[] urlParts = sourceURL.toString().split("/");
        final String tabName = urlParts[urlParts.length - 1];
        DataTypeUtil.guessTypes(newModel, tabName, dataTypeMap);
        listDataTypeMap.put(tabName, initListDataTypes(newModel));
        addTableTab(newModel, tabName, curRenderer);
    }

    if (getFileType() == FileTypes.GENE_ASSOCIATION_FILE) {
        fileTypeLabel.setText("Gene Association");
        fileTypeLabel.setToolTipText("This is a fixed-format Gene Association file.");
    }

    loadFlag = true;
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-2]  ?  : ? ?  ? ?    ?/*from   w  ww .j  a v  a2 s . co m*/
 */
@Test
public void testModifyCellContents() throws Exception {

    try {
        String content = "Use \n with word wrap on to create a new line";
        short rownum = 2;
        int cellnum = 2;

        LOGGER.debug("testModifyCellContents start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyCellContents.xls");

        if (!EgovFileUtil.isExistsFile(sb.toString())) {
            Workbook wbT = new HSSFWorkbook();
            wbT.createSheet();

            //  ? ?
            excelService.createWorkbook(wbT, sb.toString());
        }

        //  ? 
        Workbook wb = excelService.loadWorkbook(sb.toString());
        LOGGER.debug("testModifyCellContents after loadWorkbook....");

        Sheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        //Word Wrap MUST be turned on
        cs.setWrapText(true);

        Row row = sheet.createRow(rownum);
        row.setHeight((short) 0x349);
        Cell cell = row.createCell(cellnum);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(content));
        cell.setCellStyle(cs);

        sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20)));

        //excelService.writeWorkbook(wb);

        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        //  ? 
        Workbook wb1 = excelService.loadWorkbook(sb.toString());

        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell(cellnum);

        // ? ?  ?
        LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue());
        LOGGER.debug("cont ###{}###", content);

        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testModifyCellContents end....");
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java

License:Apache License

/**
 * [Flow #-3]  ? ?  :  ?? ?(? ?, Border? ?, ? ?,  )? 
 *//*from   w w  w. ja  v  a 2s.c  o m*/
@Test
public void testWriteExcelFileAttribute() throws Exception {

    try {
        LOGGER.debug("testWriteExcelFileAttribute start....");

        short rowheight = 40 * 10;
        int columnwidth = 30;

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls");

        // delete file
        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            LOGGER.debug("Delete file....{}", sb.toString());
        }

        Workbook wb = new HSSFWorkbook();

        Sheet sheet1 = wb.createSheet("new sheet");
        wb.createSheet("second sheet");

        // ? ?
        sheet1.setDefaultRowHeight(rowheight);
        sheet1.setDefaultColumnWidth(columnwidth);

        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();

        cs.setFont(f2);
        cs.setWrapText(true);

        // 
        cs.setAlignment(CellStyle.ALIGN_RIGHT);

        cs.setFillPattern(CellStyle.DIAMONDS); //  ?

        // ? ?
        cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); //  
        cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // 

        sheet1.setDefaultColumnStyle((short) 0, cs);

        Workbook tmp = excelService.createWorkbook(wb, sb.toString());

        Sheet sheetTmp1 = tmp.getSheetAt(0);

        assertEquals(rowheight, sheetTmp1.getDefaultRowHeight());
        assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth());

        CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1));

        LOGGER.debug("getAlignment : {}", cs1.getAlignment());
        assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment());

        LOGGER.debug("getFillPattern : {}", cs1.getFillPattern());
        assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern());

        LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor());
        LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor());
        assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor());
        assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor());

    } catch (Exception e) {
        LOGGER.error(e.toString());
        throw new Exception(e);
    } finally {
        LOGGER.debug("testWriteExcelFileAttribute end....");
    }
}