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

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

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

Introduction

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

Prototype

@Override
void close() throws IOException;

Source Link

Document

Close the underlying input resource (File or Stream), from which the Workbook was read.

Usage

From source file:org.drools.workbench.screens.guided.dtable.backend.server.conversion.DecisionTableGuidedToDecisionTableXLSConverter.java

License:Apache License

public XLSConversionResult convert(final Path originPath) throws IOException {
    final PackageDataModelOracle dmo = dataModelService.getDataModel(originPath);
    final GuidedDecisionTable52 dtable = guidedDecisionTableEditorService.load(originPath);
    final XLSBuilder.BuildResult buildResult = new XLSBuilder(dtable, dmo).build();

    if (buildResult.getConversionResult().isConverted()) {

        final Workbook workbook = buildResult.getWorkbook();

        final ByteArrayOutputStream fileOut = new ByteArrayOutputStream();
        workbook.write(fileOut);/*from   w  ww . jav  a2  s  .  co  m*/

        final ByteArrayInputStream inStream = new ByteArrayInputStream(fileOut.toByteArray());

        decisionTableXLSService.create(getDestinationFilePath(originPath), inStream, sessionInfo.getId(),
                "Converted from " + originPath.getFileName());

        fileOut.close();
        workbook.close();
        inStream.close();
    }

    return buildResult.getConversionResult();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.DescriptionCodeLookupFromExcelService.java

License:RPL License

private void loadMapOfCodeMaps(String codeTableExcelFilePath) throws FileNotFoundException, IOException {
    log.info("Recache code table maps.");

    mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    FileInputStream inputStream = new FileInputStream(new File(codeTableExcelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);/* w w w .  jav a  2s.com*/

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }

            String codeOrDescription = StringUtils
                    .upperCase(row.getCell(row.getLastCellNum() - 1).getStringCellValue());
            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.SimpleExcelReaderExample.java

License:RPL License

public static void main(String[] args) throws IOException {
    Map<String, Map<String, Integer>> mapOfCodeMaps = new HashMap<String, Map<String, Integer>>();

    String excelFilePath = "src/test/resources/codeSpreadSheets/PimaCountyAnalyticsCodeTables.xlsx";
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        System.out.println("Sheet Name: " + sheet.getSheetName());

        Map<String, Integer> codePkMap = new HashMap<String, Integer>();
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);//from   www.ja va2  s . c  om

            if (row.getCell(row.getLastCellNum() - 1).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                row.getCell(row.getLastCellNum() - 1).setCellType(Cell.CELL_TYPE_STRING);
            }

            String codeOrDescription = row.getCell(row.getLastCellNum() - 1).getStringCellValue();

            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();
            codePkMap.put(codeOrDescription, pkId);
        }

        mapOfCodeMaps.put(sheet.getSheetName(), codePkMap);

    }

    workbook.close();
    inputStream.close();
}

From source file:org.ojbc.adapters.analyticsstaging.custody.service.SqlScriptFromExcelGenerator.java

License:RPL License

private static void generatePolulateCodeTableScript(String sqlScriptPath, String excelFilePath,
        boolean isSqlServerInsert) throws FileNotFoundException, IOException {
    Path adamsSqlPath = Paths.get(sqlScriptPath);

    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    StringBuilder sb = new StringBuilder();
    sb.append("/*\n "/*  w  w  w.  j  a va  2  s .c  o m*/
            + "* Unless explicitly acquired and licensed from Licensor under another license, the contents of\n "
            + "* this file are subject to the Reciprocal Public License (\"RPL\") Version 1.5, or subsequent\n "
            + "* versions as allowed by the RPL, and You may not copy or use this file in either source code\n "
            + "* or executable form, except in compliance with the terms and conditions of the RPL\n " + "* \n "
            + "* All software distributed under the RPL is provided strictly on an \"AS IS\" basis, WITHOUT\n "
            + "* WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, AND LICENSOR HEREBY DISCLAIMS ALL SUCH\n "
            + "* WARRANTIES, INCLUDING WITHOUT LIMITATION, ANY WARRANTIES OF MERCHANTABILITY, FITNESS FOR A\n "
            + "* PARTICULAR PURPOSE, QUIET ENJOYMENT, OR NON-INFRINGEMENT. See the RPL for specific language\n "
            + "* governing rights and limitations under the RPL.\n " + "*\n "
            + "* http://opensource.org/licenses/RPL-1.5\n " + "*\n "
            + "* Copyright 2012-2015 Open Justice Broker Consortium\n " + "*/\n");

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);

        if (isSqlServerInsert) {
            sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " ON;\n");
        }
        String idColumnName = sheet.getRow(0).getCell(0).getStringCellValue();
        String descriptionColumnName = sheet.getRow(0).getCell(1).getStringCellValue();

        System.out.println("descriptionColumnName: " + descriptionColumnName);
        String baseString = "insert into " + sheet.getSheetName() + " (" + idColumnName + ", "
                + descriptionColumnName + ") values (";
        for (int j = 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);

            String description = row.getCell(1).getStringCellValue();
            Integer pkId = Double.valueOf(row.getCell(0).getNumericCellValue()).intValue();

            String insertString = baseString + "'" + pkId + "', " + "'" + description.replace("'", "''")
                    + "');\n";
            sb.append(insertString);
        }

        if (isSqlServerInsert) {
            sb.append("SET IDENTITY_INSERT dbo." + sheet.getSheetName() + " OFF;\n");
        }
    }

    workbook.close();
    inputStream.close();

    try (BufferedWriter writer = Files.newBufferedWriter(adamsSqlPath)) {
        writer.write(sb.toString());
    }

    System.out.println("Sql script " + sqlScriptPath + " generated. ");
}

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

public void prepareNextOutputFile() throws KettleException {
    try {/*  w  ww. j  a  v a  2  s  .co  m*/
        // sheet name shouldn't exceed 31 character
        if (data.realSheetname != null && data.realSheetname.length() > 31) {
            throw new KettleException(
                    BaseMessages.getString(PKG, "ExcelWriterStep.Exception.MaxSheetName", data.realSheetname));
        }
        // clear style cache
        int numOfFields = meta.getOutputFields() != null && meta.getOutputFields().length > 0
                ? meta.getOutputFields().length
                : 0;
        if (numOfFields == 0) {
            numOfFields = data.inputRowMeta != null ? data.inputRowMeta.size() : 0;
        }
        data.clearStyleCache(numOfFields);

        // build new filename
        String buildFilename = buildFilename(data.splitnr);

        data.file = KettleVFS.getFileObject(buildFilename, getTransMeta());

        if (log.isDebug()) {
            logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.OpeningFile", buildFilename));
        }

        // determine whether existing file must be deleted
        if (data.file.exists() && data.createNewFile) {
            if (!data.file.delete()) {
                if (log.isBasic()) {
                    logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.CouldNotDeleteStaleFile",
                            buildFilename));
                }
                setErrors(1);
                throw new KettleException("Could not delete stale file " + buildFilename);
            }
        }

        // adding filename to result
        if (meta.isAddToResultFiles()) {
            // Add this to the result file names...
            ResultFile resultFile = new ResultFile(ResultFile.FILE_TYPE_GENERAL, data.file,
                    getTransMeta().getName(), getStepname());
            resultFile
                    .setComment("This file was created with an Excel writer step by Pentaho Data Integration");
            addResultFile(resultFile);
        }
        boolean appendingToSheet = true;
        // if now no file exists we must create it as indicated by user
        if (!data.file.exists()) {
            // if template file is enabled
            if (meta.isTemplateEnabled()) {
                // handle template case (must have same format)
                // ensure extensions match
                String templateExt = KettleVFS.getFileObject(data.realTemplateFileName).getName()
                        .getExtension();
                if (!meta.getExtension().equalsIgnoreCase(templateExt)) {
                    throw new KettleException("Template Format Mismatch: Template has extension: " + templateExt
                            + ", but output file has extension: " + meta.getExtension()
                            + ". Template and output file must share the same format!");
                }

                if (KettleVFS.getFileObject(data.realTemplateFileName).exists()) {
                    // if the template exists just copy the template in place
                    copyFile(KettleVFS.getFileObject(data.realTemplateFileName, getTransMeta()), data.file);
                } else {
                    // template is missing, log it and get out
                    if (log.isBasic()) {
                        logBasic(BaseMessages.getString(PKG, "ExcelWriterStep.Log.TemplateMissing",
                                data.realTemplateFileName));
                    }
                    setErrors(1);
                    throw new KettleException("Template file missing: " + data.realTemplateFileName);
                }
            } else {
                // handle fresh file case, just create a fresh workbook
                Workbook wb = meta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook()
                        : new HSSFWorkbook();
                BufferedOutputStreamWithCloseDetection out = new BufferedOutputStreamWithCloseDetection(
                        KettleVFS.getOutputStream(data.file, false));
                wb.createSheet(data.realSheetname);
                wb.write(out);
                out.close();
                wb.close();
            }
            appendingToSheet = false;
        }

        // file is guaranteed to be in place now
        if (meta.getExtension().equalsIgnoreCase("xlsx")) {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(KettleVFS.getInputStream(data.file));
            if (meta.isStreamingData() && !meta.isTemplateEnabled()) {
                data.wb = new SXSSFWorkbook(xssfWorkbook, 100);
            } else {
                //Initialize it later after writing header/template because SXSSFWorkbook can't read/rewrite existing data,
                // only append.
                data.wb = xssfWorkbook;
            }
        } else {
            data.wb = new HSSFWorkbook(KettleVFS.getInputStream(data.file));
        }

        int existingActiveSheetIndex = data.wb.getActiveSheetIndex();
        int replacingSheetAt = -1;

        if (data.wb.getSheet(data.realSheetname) != null) {
            // sheet exists, replace or reuse as indicated by user
            if (data.createNewSheet) {
                replacingSheetAt = data.wb.getSheetIndex(data.wb.getSheet(data.realSheetname));
                data.wb.removeSheetAt(replacingSheetAt);
            }
        }

        // if sheet is now missing, we need to create a new one
        if (data.wb.getSheet(data.realSheetname) == null) {
            if (meta.isTemplateSheetEnabled()) {
                Sheet ts = data.wb.getSheet(data.realTemplateSheetName);
                // if template sheet is missing, break
                if (ts == null) {
                    throw new KettleException(BaseMessages.getString(PKG,
                            "ExcelWriterStep.Exception.TemplateNotFound", data.realTemplateSheetName));
                }
                data.sheet = data.wb.cloneSheet(data.wb.getSheetIndex(ts));
                data.wb.setSheetName(data.wb.getSheetIndex(data.sheet), data.realSheetname);
                // unhide sheet in case it was hidden
                data.wb.setSheetHidden(data.wb.getSheetIndex(data.sheet), false);
                if (meta.isTemplateSheetHidden()) {
                    data.wb.setSheetHidden(data.wb.getSheetIndex(ts), true);
                }
            } else {
                // no template to use, simply create a new sheet
                data.sheet = data.wb.createSheet(data.realSheetname);
            }
            if (replacingSheetAt > -1) {
                data.wb.setSheetOrder(data.sheet.getSheetName(), replacingSheetAt);
            }
            // preserves active sheet selection in workbook
            data.wb.setActiveSheet(existingActiveSheetIndex);
            data.wb.setSelectedTab(existingActiveSheetIndex);
            appendingToSheet = false;
        } else {
            // sheet is there and should be reused
            data.sheet = data.wb.getSheet(data.realSheetname);
        }
        // if use chose to make the current sheet active, do so
        if (meta.isMakeSheetActive()) {
            int sheetIndex = data.wb.getSheetIndex(data.sheet);
            data.wb.setActiveSheet(sheetIndex);
            data.wb.setSelectedTab(sheetIndex);
        }
        // handle write protection
        if (meta.isSheetProtected()) {
            protectSheet(data.sheet, data.realPassword);
        }

        // starting cell support
        data.startingRow = 0;
        data.startingCol = 0;
        if (!Utils.isEmpty(data.realStartingCell)) {
            CellReference cellRef = new CellReference(data.realStartingCell);
            data.startingRow = cellRef.getRow();
            data.startingCol = cellRef.getCol();
        }

        data.posX = data.startingCol;
        data.posY = data.startingRow;

        // Find last row and append accordingly
        if (!data.createNewSheet && meta.isAppendLines() && appendingToSheet) {
            data.posY = 0;
            if (data.sheet.getPhysicalNumberOfRows() > 0) {
                data.posY = data.sheet.getLastRowNum();
                data.posY++;
            }
        }

        // offset by configured value
        // Find last row and append accordingly
        if (!data.createNewSheet && meta.getAppendOffset() != 0 && appendingToSheet) {
            data.posY += meta.getAppendOffset();
        }

        // may have to write a few empty lines
        if (!data.createNewSheet && meta.getAppendEmpty() > 0 && appendingToSheet) {
            for (int i = 0; i < meta.getAppendEmpty(); i++) {
                openLine();
                if (!data.shiftExistingCells || meta.isAppendLines()) {
                    data.posY++;
                }
            }
        }

        // may have to write a header here
        if (meta.isHeaderEnabled()
                && !(!data.createNewSheet && meta.isAppendOmitHeader() && appendingToSheet)) {
            writeHeader();
        }
        if (meta.isStreamingData() && meta.isTemplateEnabled()) {
            Sheet templateSheet = ((XSSFWorkbook) data.wb).getSheet(data.realSheetname);
            int currentRowNum = templateSheet.getLastRowNum();
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook((XSSFWorkbook) data.wb, 100);
            Sheet aNewSheet = sxssfWorkbook.getSheet(data.realSheetname);
            int aNewSheetRowCount = aNewSheet.getLastRowNum();
            while (currentRowNum > aNewSheetRowCount) {
                templateSheet.removeRow(templateSheet.getRow(currentRowNum));
                currentRowNum--;
            }
            data.wb = sxssfWorkbook;
            data.sheet = sxssfWorkbook.getSheet(data.realSheetname);
        }
        if (log.isDebug()) {
            logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.FileOpened", buildFilename));
        }
        // this is the number of the new output file
        data.splitnr++;
    } catch (Exception e) {
        logError("Error opening new file", e);
        setErrors(1);
        throw new KettleException(e);
    }
}

From source file:org.talend.dataprep.schema.xls.serialization.XlsxStreamRunnable.java

License:Open Source License

/**
 * @see Runnable#run()/*from   w  w  w . j a v a 2  s  .c  o m*/
 */
@Override
public void run() {
    try {
        JsonGenerator generator = jsonFactory.createGenerator(jsonOutput);
        Workbook workbook = StreamingReader.builder() //
                .bufferSize(4096) //
                .rowCacheSize(1) //
                .open(rawContent);
        try {
            Sheet sheet = StringUtils.isEmpty(metadata.getSheetName()) ? //
                    workbook.getSheetAt(0) : workbook.getSheet(metadata.getSheetName());
            generator.writeStartArray();
            for (Row row : sheet) {
                if (limit > 0 && row.getRowNum() > limit) {
                    break;
                }
                if (!XlsSerializer.isHeaderLine(row.getRowNum(), metadata.getRowMetadata().getColumns())) {
                    generator.writeStartObject();
                    // data quality Analyzer doesn't like to not have all columns even if we don't have any values
                    // so create so field with empty value otherwise we get exceptions
                    int i = 0;
                    for (ColumnMetadata columnMetadata : metadata.getRowMetadata().getColumns()) {
                        Cell cell = row.getCell(i);
                        String cellValue = cell == null ? null : cell.getStringCellValue(); // StringUtils.EMPTY
                        generator.writeFieldName(columnMetadata.getId());
                        if (cellValue != null) {
                            generator.writeString(cellValue);
                        } else {
                            generator.writeNull();
                        }
                        i++;
                    }
                    generator.writeEndObject();
                }
            }
            generator.writeEndArray();
            generator.flush();
        } finally {
            workbook.close();
        }
    } catch (Exception e) {
        // Consumer may very well interrupt consumption of stream (in case of limit(n) use for sampling).
        // This is not an issue as consumer is allowed to partially consumes results, it's up to the
        // consumer to ensure data it consumed is consistent.
        LOG.debug("Unable to continue serialization for {}. Skipping remaining content.", metadata.getId(), e);
    } finally {
        try {
            jsonOutput.close();
        } catch (IOException e) {
            LOG.error("Unable to close output", e);
        }
    }
}

From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java

License:Open Source License

private List<Schema.SheetContent> parseAllSheetsStream(Request request) {
    Workbook workbook = StreamingReader.builder() //
            .bufferSize(4096) //
            .rowCacheSize(1) //
            .open(request.getContent());
    try {// w w  w.j  a v  a2  s .c o  m
        List<Schema.SheetContent> schemas = new ArrayList<>();
        int sheetNumber = 0;
        for (Sheet sheet : workbook) {
            List<ColumnMetadata> columnsMetadata = createMetadataFromFirstNonEmptyRowAndInitSheet(sheet);
            int totalColumnsNumber = getTotalColumnsNumber((StreamingSheet) sheet);

            /*
             * Protecting the app against too large data sets => It would break mongo by submitting too large empty
             * column metadata or saturate the memory during analysis.
             *
             * @see https://jira.talendforge.org/browse/TDP-3459
             */
            if (totalColumnsNumber > maxNumberOfColumns) {
                throw new TDPException(DataSetErrorCodes.DATASET_HAS_TOO_MANY_COLUMNS, ExceptionContext.build()
                        .put("number-of-columns", totalColumnsNumber).put("max-allowed", maxNumberOfColumns));
            }

            String sheetName = sheet.getSheetName();
            Schema.SheetContent sheetContent = new Schema.SheetContent(
                    StringUtils.isEmpty(sheetName) ? "sheet-" + sheetNumber : sheetName, columnsMetadata);

            // if less columns found than the metadata we complete
            completeWithEmptyColumnsMetadata(columnsMetadata, totalColumnsNumber);
            schemas.add(sheetContent);
        }
        return schemas;
    } finally {
        try {
            workbook.close();
        } catch (IOException e) {
            LOGGER.error("Unable to close excel file.", e);
        }
    }
}

From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java

License:Open Source License

/**
 * Parse all xls sheets for old excel document type
 *
 * @param request the xls request.//w w w  .  j a  va 2  s  .co  m
 * @return The parsed sheets request.
 */
private List<Schema.SheetContent> parseAllSheetsOldFormat(Request request) {

    final Marker marker = Markers.dataset(request.getMetadata().getId());

    try {
        InputStream inputStream = request.getContent();
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        Workbook hssfWorkbook = WorkbookFactory.create(inputStream);

        List<Schema.SheetContent> schemas;
        try {
            if (hssfWorkbook == null) {
                throw new IOException("could not open " + request.getMetadata().getId() + " as an excel file");
            }
            int sheetNumber = hssfWorkbook.getNumberOfSheets();
            if (sheetNumber < 1) {
                LOGGER.debug(marker, "has not sheet to read");
                return Collections.emptyList();
            }
            schemas = new ArrayList<>();
            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = hssfWorkbook.getSheetAt(i);
                if (sheet.getLastRowNum() < 1) {
                    LOGGER.debug(marker, "sheet '{}' do not have rows skip ip", sheet.getSheetName());
                    continue;
                }
                List<ColumnMetadata> columnsMetadata = parsePerSheet(sheet, //
                        request.getMetadata().getId(), //
                        hssfWorkbook.getCreationHelper().createFormulaEvaluator());
                String sheetName = sheet.getSheetName();
                // update XlsSerializer if this default sheet naming change!!!
                schemas.add(
                        new Schema.SheetContent(sheetName == null ? "sheet-" + i : sheetName, columnsMetadata));
            }
        } finally {
            hssfWorkbook.close();
        }
        return schemas;
    } catch (Exception e) {
        LOGGER.debug(marker, "Exception during parsing xls request :" + e.getMessage(), e);
        throw new TDPException(CommonErrorCodes.UNEXPECTED_EXCEPTION, e);
    }
}

From source file:packtest.CellComments.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();

    CreationHelper factory = wb.getCreationHelper();

    Sheet sheet = wb.createSheet();/*from   ww w. ja  va2  s .co  m*/

    Cell cell1 = sheet.createRow(3).createCell(5);
    cell1.setCellValue("F4");

    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();

    Comment comment1 = drawing.createCellComment(anchor);
    RichTextString str1 = factory.createRichTextString("Hello, World!");
    comment1.setString(str1);
    comment1.setAuthor("Apache POI");
    cell1.setCellComment(comment1);

    Cell cell2 = sheet.createRow(2).createCell(2);
    cell2.setCellValue("C3");

    Comment comment2 = drawing.createCellComment(anchor);
    RichTextString str2 = factory.createRichTextString("XSSF can set cell comments");
    //apply custom font to the text in the comment
    Font font = wb.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 14);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(IndexedColors.RED.getIndex());
    str2.applyFont(font);

    comment2.setString(str2);
    comment2.setAuthor("Apache POI");
    //        comment2.setAddress(new CellAddress("C3"));

    String fname = "comments.xlsx";
    FileOutputStream out = new FileOutputStream(fname);
    wb.write(out);
    out.close();

    wb.close();
}

From source file:packtest.CreateUserDefinedDataFormats.java

License:Apache License

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    Sheet sheet = wb.createSheet("format sheet");
    CellStyle style;//from  w  ww .  j av a2 s  . co  m
    DataFormat format = wb.createDataFormat();
    Row row;
    Cell cell;
    short rowNum = 0;
    short colNum = 0;

    row = sheet.createRow(rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("0.0"));
    cell.setCellStyle(style);

    row = sheet.createRow(++rowNum);
    cell = row.createCell(colNum);
    cell.setCellValue(11111.25);
    style = wb.createCellStyle();
    style.setDataFormat(format.getFormat("#,##0.0000"));
    cell.setCellStyle(style);

    FileOutputStream fileOut = new FileOutputStream("ooxml_dataFormat.xlsx");
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}