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

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

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

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:eleanalysis.SampleLibrary.java

/**
 * writes a report in the form of an excel spreadsheet that is exported.
 * Stage variable is for using a filechooser to pick where to save file.
 * @param myStage Stage is for saving file using FileChooser
 * @throws FileNotFoundException /*from  w ww.j av a  2 s . c  om*/
 */
public void writeReport(Stage myStage) throws FileNotFoundException {
    FileChooser pickFile = new FileChooser();
    pickFile.setInitialDirectory(new File("C:\\Users\\Yan\\Documents\\NetBeansProjects\\EleAnalysis\\"));
    pickFile.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("XLS", "*.xls"),
            new FileChooser.ExtensionFilter("XLSX", "*.xlsx"));
    File writeFile = pickFile.showSaveDialog(myStage);
    FileOutputStream fileOut;
    fileOut = new FileOutputStream(writeFile);

    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("WDXRF");
    sheet1.setDefaultColumnWidth(15);
    // Create a cell space
    for (int i = 0; i < ElementUtils.skf.length + 5; i++) {
        Row tempR = sheet1.createRow(i);
        for (int j = 0; j <= array.size(); j++) {
            Cell tempC = tempR.createCell(j);
        }
    }
    CellStyle csCenter = wb.createCellStyle();
    csCenter.setAlignment(CellStyle.ALIGN_CENTER);
    csCenter.setBorderTop(CellStyle.BORDER_THIN);
    csCenter.setBorderLeft(CellStyle.BORDER_THIN);
    csCenter.setBorderRight(CellStyle.BORDER_THIN);
    csCenter.setBorderBottom(CellStyle.BORDER_THIN);
    CellStyle csRight = wb.createCellStyle();
    csRight.setAlignment(CellStyle.ALIGN_RIGHT);
    csRight.setBorderTop(CellStyle.BORDER_THIN);
    csRight.setBorderLeft(CellStyle.BORDER_THIN);
    csRight.setBorderRight(CellStyle.BORDER_THIN);
    csRight.setBorderBottom(CellStyle.BORDER_THIN);
    CellStyle csLeft = wb.createCellStyle();
    csLeft.setAlignment(CellStyle.ALIGN_LEFT);
    csLeft.setBorderTop(CellStyle.BORDER_THIN);
    csLeft.setBorderLeft(CellStyle.BORDER_THIN);
    csLeft.setBorderRight(CellStyle.BORDER_THIN);
    csLeft.setBorderBottom(CellStyle.BORDER_THIN);

    //Top Row
    sheet1.getRow(0).setHeightInPoints(25);
    sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, array.size()));
    sheet1.getRow(0).getCell(0).setCellValue("WDXRF Analysis");
    sheet1.getRow(0).getCell(0).setCellStyle(csCenter);
    sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, array.size()));

    //Second Row
    sheet1.getRow(1).getCell(0).setCellValue("Conc as Wt%");
    sheet1.getRow(1).getCell(0).setCellStyle(csCenter);

    // Third Row
    sheet1.getRow(2).setHeightInPoints(35);
    sheet1.getRow(2).getCell(0).setCellValue("Common Oxides/Oxication States");
    sheet1.getRow(2).getCell(0).setCellStyle(csLeft);
    for (int j = 1; j <= array.size(); j++) {
        sheet1.getRow(2).getCell(j).setCellStyle(csLeft);
        sheet1.getRow(2).getCell(j).setCellValue(array.get(j - 1).getName());
    }

    //Fourth Row
    sheet1.getRow(3).getCell(0).setCellValue("% Detectable");
    sheet1.getRow(3).getCell(0).setCellStyle(csLeft);
    for (int j = 1; j <= array.size(); j++) {
        sheet1.getRow(3).getCell(j).setCellValue("0.00");
        sheet1.getRow(3).getCell(j).setCellStyle(csLeft);
    }

    //Fifth Row
    sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, array.size()));
    sheet1.getRow(4).getCell(0).setCellValue("Results Normalized with Respect to Detectable Concentration");
    sheet1.getRow(4).getCell(0).setCellStyle(csCenter);

    //Rows 6 and beyond. Prints element list and defaults values to 0
    for (int i = 5; i < ElementUtils.skf.length + 5; i++) {
        sheet1.getRow(i).getCell(0).setCellValue(ElementUtils.skf[i - 5]);
        sheet1.getRow(i).getCell(0).setCellStyle(csLeft);
        for (int j = 1; j <= array.size(); j++) {
            sheet1.getRow(i).getCell(j).setCellValue("0.0");
            sheet1.getRow(i).getCell(j).setCellStyle(csRight);
        }
    }

    // Copies values in SampleLibrary array into report
    for (int i = 0; i < array.size(); i++) {
        List<Element> eleArray = array.get(i).getArrayCopy();
        for (int j = 0; j < ElementUtils.skf.length; j++) {
            for (int k = 0; k < eleArray.size(); k++) {

                if (ElementUtils.skf[j].contains(eleArray.get(k).getBaseElement()))
                    sheet1.getRow(j + 5).getCell(i + 1).setCellValue(eleArray.get(k).getConcWeight());
            }

        }
    }
    try {
        wb.write(fileOut);
        wb.close();
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(SampleLibrary.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:eu.alpinweiss.filegen.service.impl.GenerateXlsxFileServiceImpl.java

License:Apache License

public void generateExcel(String excelFilename, int rowCount, List<FieldDefinition> fieldDefinitionList,
        int sheetCount) {

    long startTime = new Date().getTime();

    outputWriterHolder.writeValueInLine("Excel data generation started");

    Workbook wb = new SXSSFWorkbook();

    try {//from   ww  w .j a v a  2s.c om
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(IndexedColors.LIME.getIndex());
        cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        Font f = wb.createFont();
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f.setFontHeightInPoints((short) 12);
        cs.setFont(f);

        SXSSFSheet sheet1 = (SXSSFSheet) wb.createSheet("dataSheet");

        int columnCount = fieldDefinitionList.size();

        Map<Integer, Input2TableInfo> input2TableInfoMap = new LinkedHashMap<>(columnCount);

        for (int i = 0; i < columnCount; i++) {
            Input2TableInfo input2TableInfo = new Input2TableInfo();
            FieldDefinition fieldDefinition = fieldDefinitionList.get(i);
            input2TableInfo.setFieldText(fieldDefinition.getFieldName());
            input2TableInfo.setFieldDefinition(fieldDefinition);
            input2TableInfo.initCellStyle(wb);
            input2TableInfo.initGenerator();
            input2TableInfoMap.put(i, input2TableInfo);
        }

        if (sheetCount > 1) {
            CountDownLatch startSignal = new CountDownLatch(1);
            CountDownLatch doneSignal;

            doneSignal = new CountDownLatch(sheetCount);

            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            SheetProcessor stringProcessorSheet1 = new SheetProcessor(parameterVault, startSignal, doneSignal,
                    cs, sheet1, columnCount, input2TableInfoMap, outputWriterHolder);
            new Thread(stringProcessorSheet1, "Processor-" + sheetCount).start();

            for (int i = 0; i < sheetCount - 1; i++) {
                SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("dataSheet_" + i);
                ParameterVault parameterVaultRest = new DefaultParameterVault(i + 1, rowCount);
                SheetProcessor stringProcessor = new SheetProcessor(parameterVaultRest, startSignal, doneSignal,
                        cs, sheet, columnCount, input2TableInfoMap, outputWriterHolder);
                new Thread(stringProcessor, "Processor-" + i).start();
            }

            startSignal.countDown();
            doneSignal.await();
        } else {
            ParameterVault parameterVault = new DefaultParameterVault(0, rowCount);
            new SheetProcessor(outputWriterHolder).generateSheetData(parameterVault, cs, sheet1, columnCount,
                    input2TableInfoMap);
        }

        outputWriterHolder.writeValueInLine("Excel data generation finished.");
        long generationTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((generationTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Writing to file.");

        FileOutputStream fileOut = new FileOutputStream(excelFilename.trim());

        wb.write(fileOut);
        fileOut.close();

        long writeTime = new Date().getTime();
        outputWriterHolder.writeValueInLine("Time used " + ((writeTime - generationTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Total time used " + ((writeTime - startTime) / 1000) + " sec");
        outputWriterHolder.writeValueInLine("Done");
    } catch (Exception e) {
        LOGGER.error(e.getMessage(), e);
    } finally {
        try {
            wb.close();
        } catch (IOException e) {
            LOGGER.error(e.getMessage(), e);
        }
    }

}

From source file:eu.alpinweiss.filegen.util.Input2TableInfo.java

License:Apache License

public void initCellStyle(Workbook wb) {
    FieldType type = fieldDefinition.getType();
    if (FieldType.DATE.equals(type) || FieldType.DATERANGE.equals(type)) {
        DataFormat dataFormat = wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(dataFormat.getFormat("dd/MM/yyyy"));
    }//from   w  w  w  .j  a v a2  s. c  om
}

From source file:eu.esdihumboldt.hale.io.xls.writer.XLSLookupTableWriter.java

License:Open Source License

/**
 * @see eu.esdihumboldt.hale.common.core.io.impl.AbstractIOProvider#execute(eu.esdihumboldt.hale.common.core.io.ProgressIndicator,
 *      eu.esdihumboldt.hale.common.core.io.report.IOReporter)
 *//*  w ww .j a  v a 2 s .c  om*/
@Override
protected IOReport execute(ProgressIndicator progress, IOReporter reporter)
        throws IOProviderConfigurationException, IOException {

    Workbook workbook;
    // write xls file
    if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) {
        workbook = new HSSFWorkbook();
    }
    // write xlsx file
    else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        reporter.error(new IOMessageImpl("Content type is invalid!", null));
        reporter.setSuccess(false);
        return reporter;
    }

    Sheet sheet = workbook.createSheet();
    workbook.setSheetName(0, "Lookup table");
    Row row = null;
    Cell cell = null;
    DataFormat df = workbook.createDataFormat();

    // create cell style of the header
    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));

    // create cell style
    CellStyle rowStyle = workbook.createCellStyle();
    // set thin border around the cell
    rowStyle.setBorderBottom(CellStyle.BORDER_THIN);
    rowStyle.setBorderLeft(CellStyle.BORDER_THIN);
    rowStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    rowStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    rowStyle.setWrapText(true);

    Map<Value, Value> table = getLookupTable().getTable().asMap();

    int rownum = 0;

    // write header
    row = sheet.createRow(rownum++);
    cell = row.createCell(0);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_SOURCE_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    cell = row.createCell(1);
    cell.setCellValue(getParameter(LookupTableExportConstants.PARAM_TARGET_COLUMN).as(String.class));
    cell.setCellStyle(headerStyle);

    for (Value key : table.keySet()) {
        // create a row
        row = sheet.createRow(rownum);

        cell = row.createCell(0);
        cell.setCellValue(key.as(String.class));
        cell.setCellStyle(rowStyle);

        Value entry = table.get(key);
        cell = row.createCell(1);
        cell.setCellValue(entry.as(String.class));
        cell.setCellStyle(rowStyle);
        rownum++;
    }

    // write file
    FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath());
    workbook.write(out);
    out.close();

    reporter.setSuccess(true);
    return reporter;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @return the header cell style/*  w  ww .  ja va 2s  . c  om*/
 */
public static CellStyle getHeaderStyle(Workbook workbook) {

    CellStyle headerStyle = workbook.createCellStyle();
    Font headerFont = workbook.createFont();
    DataFormat df = workbook.createDataFormat();
    // use bold font
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);
    // set a medium border
    headerStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
    // set cell data format to text
    headerStyle.setDataFormat(df.getFormat("@"));

    return headerStyle;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the normal cell style//  w w  w . j a  v  a2s  . c o  m
 */
public static CellStyle getNormalStyle(Workbook workbook, boolean strikeOut) {

    // create cell style
    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    cellStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    cellStyle.setWrapText(true);

    if (strikeOut) {
        // strike out font
        Font disabledFont = workbook.createFont();
        disabledFont.setStrikeout(true);
        disabledFont.setColor(IndexedColors.GREY_40_PERCENT.getIndex());
        cellStyle.setFont(disabledFont);
    }

    return cellStyle;
}

From source file:eu.esdihumboldt.hale.io.xls.XLSCellStyles.java

License:Open Source License

/**
 * @param workbook the workbook of the cell
 * @param strikeOut true, if cell should be striked out
 * @return the highlighted cell style/*from w w w  . jav a  2  s. c  om*/
 */
public static CellStyle getHighlightedStyle(Workbook workbook, boolean strikeOut) {

    // create highlight style for type cells
    CellStyle highlightStyle = workbook.createCellStyle();
    DataFormat df = workbook.createDataFormat();
    // set thin border around the cell
    highlightStyle.setBorderBottom(CellStyle.BORDER_THIN);
    highlightStyle.setBorderLeft(CellStyle.BORDER_THIN);
    highlightStyle.setBorderRight(CellStyle.BORDER_THIN);
    // set cell data format to text
    highlightStyle.setDataFormat(df.getFormat("@"));
    // display multiple lines
    highlightStyle.setWrapText(true);
    highlightStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    highlightStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    if (strikeOut) {
        Font disabledTypeFont = workbook.createFont();
        disabledTypeFont.setStrikeout(true);
        disabledTypeFont.setColor(IndexedColors.BLACK.getIndex());
        highlightStyle.setFont(disabledTypeFont);
    }

    return highlightStyle;
}

From source file:ExcelFx.FXMLDocumentController.java

private static CellStyle borderedStyleNormal(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}

From source file:ExcelFx.FXMLDocumentController.java

private static CellStyle borderedStyleTotal(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_MEDIUM);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_MEDIUM);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_MEDIUM);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_MEDIUM);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}

From source file:ExcelFx.FXMLDocumentController.java

private static CellStyle borderedStyleHeader(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}