Example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFDataFormat
  6. getFormat

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFDataFormat getFormat.

Prototype

public String getFormat(short index)

Source Link

Document

get the format string that matches the given format index

Usage

From source file:org.orbeon.oxf.processor.serializer.legacy.XLSSerializer.java

License:Open Source License

protected void readInput(final PipelineContext pipelineContext, ProcessorInput input, Config config,
        OutputStream outputStream) {
    try {/*from  www .j a  v  a  2s.  c o  m*/
        Document dataDocument = readInputAsDOM4J(pipelineContext, INPUT_DATA);
        final DocumentWrapper wrapper = new DocumentWrapper(dataDocument, null,
                XPathCache.getGlobalConfiguration());

        Document configDocument = readInputAsDOM4J(pipelineContext, INPUT_CONFIG);

        // Read template sheet
        String templateName = configDocument.getRootElement().attributeValue("template");
        //String fileName = configDocument.getRootElement().attributeValue("filename");
        InputStream templateInputStream = URLFactory.createURL(templateName).openStream();
        final HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(templateInputStream));
        final HSSFDataFormat dataFormat = workbook.createDataFormat();
        templateInputStream.close();

        int sheetIndex = 0;

        PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(), wrapper,
                "/workbook/sheet", getLocationData());
        List<Object> nodes = expr.evaluateToJavaReturnToPool();

        for (Iterator i = nodes.iterator(); i.hasNext();) {

            final Element sheetElement = (Element) i.next();
            HSSFSheet sheet = workbook.cloneSheet(0);
            workbook.setSheetName(sheetIndex + 1, sheetElement.attributeValue("name"));

            // Duplicate rows if we find a "repeat-row" in the config
            for (Iterator j = configDocument.selectNodes("/config/repeat-row").iterator(); j.hasNext();) {

                // Get info about row to repeat
                Element repeatRowElement = (Element) j.next();
                final int rowNum = Integer.parseInt(repeatRowElement.attributeValue("row-num"));
                final String forEach = repeatRowElement.attributeValue("for-each");
                HSSFRow templateRow = sheet.getRow(rowNum);
                int repeatCount = ((Double) sheetElement.selectObject("count(" + forEach + ")")).intValue();

                // Move existing rows lower
                int lastRowNum = sheet.getLastRowNum();
                for (int k = lastRowNum; k > rowNum; k--) {
                    HSSFRow sourceRow = sheet.getRow(k);
                    HSSFRow newRow = sheet.createRow(k + repeatCount - 1);
                    XLSUtils.copyRow(workbook, newRow, sourceRow);
                }

                // Create rows, copying the template row
                for (int k = rowNum + 1; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.createRow(k);
                    XLSUtils.copyRow(workbook, newRow, templateRow);
                }

                // Modify the XPath expression on each row
                for (int k = rowNum; k < rowNum + repeatCount; k++) {
                    HSSFRow newRow = sheet.getRow(k);
                    for (short m = 0; m <= newRow.getLastCellNum(); m++) {
                        HSSFCell cell = newRow.getCell(m);
                        if (cell != null) {
                            String currentFormat = dataFormat.getFormat(cell.getCellStyle().getDataFormat());
                            final Matcher matcher = FORMAT_XPATH.matcher(currentFormat);
                            if (matcher.find()) {
                                String newFormat = matcher.group(1) + "\"" + forEach + "[" + (k - rowNum + 1)
                                        + "]/" + matcher.group(2) + "\"";
                                cell.getCellStyle().setDataFormat(dataFormat.getFormat(newFormat));
                            }
                        }
                    }
                }
            }

            // Set values in cells with an XPath expression
            XLSUtils.walk(dataFormat, sheet, new XLSUtils.Handler() {
                public void cell(HSSFCell cell, String sourceXPath, String targetXPath) {
                    if (sourceXPath.charAt(0) == '/')
                        sourceXPath = sourceXPath.substring(1);

                    // Set cell value
                    PooledXPathExpression expr = XPathCache.getXPathExpression(wrapper.getConfiguration(),
                            wrapper.wrap(sheetElement), "string(" + sourceXPath + ")", getLocationData());
                    String newValue = (String) expr.evaluateSingleToJavaReturnToPoolOrNull();

                    if (newValue == null) {
                        throw new OXFException("Nothing matches the XPath expression '" + sourceXPath
                                + "' in the input document");
                    }
                    try {
                        cell.setCellValue(Double.parseDouble(newValue));
                    } catch (NumberFormatException e) {
                        cell.setCellValue(newValue);
                    }

                    // Set cell format
                    Object element = sheetElement.selectObject(sourceXPath);
                    if (element instanceof Element) {
                        // NOTE: We might want to support other properties here
                        String bold = ((Element) element).attributeValue("bold");
                        if (bold != null) {
                            HSSFFont originalFont = workbook.getFontAt(cell.getCellStyle().getFontIndex());
                            HSSFFont newFont = workbook.createFont();
                            XLSUtils.copyFont(newFont, originalFont);
                            if ("true".equals(bold))
                                newFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                            cell.getCellStyle().setFont(newFont);
                        }
                    }
                }
            });
            sheetIndex++;
        }

        workbook.removeSheetAt(0);

        // Write out the workbook
        workbook.write(outputStream);
    } catch (IOException e) {
        throw new OXFException(e);
    }
}

From source file:org.orbeon.oxf.util.XLSUtils.java

License:Open Source License

private static void walk(boolean[][] merged, HSSFDataFormat dataFormat, HSSFRow row, Handler handler) {
    if (row != null) {
        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
            HSSFCell cell = row.getCell((short) cellNum);
            if (cell != null && !merged[row.getRowNum()][cellNum]) {
                short dataFormatId = cell.getCellStyle().getDataFormat();
                if (dataFormatId > 0) {
                    String format = dataFormat.getFormat(dataFormatId);
                    final Matcher matcher = FORMAT_XPATH.matcher(format);
                    if (matcher.find()) {
                        // Found XPath expression
                        String xpath = matcher.group(1);
                        int separtorPosition = xpath.indexOf('|');
                        String sourceXPath = separtorPosition == -1 ? xpath
                                : xpath.substring(0, separtorPosition);
                        String targetXPath = separtorPosition == -1 ? null
                                : xpath.substring(separtorPosition + 1);
                        handler.cell(cell, sourceXPath, targetXPath);
                    }//from  w ww. j a v  a  2s.c o m
                }
            }
        }
    }
}

From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsWriter.java

License:Apache License

public DfTableXlsWriter(OutputStream out) {
    _out = out;/*w w  w  .j a va2 s .  co m*/
    _workbook = new HSSFWorkbook();
    final HSSFDataFormat dataFormat = _workbook.createDataFormat();
    _dateStyle = _workbook.createCellStyle();
    _dateStyle.setDataFormat(dataFormat.getFormat(DATE_FORMAT));
    _base64Style = _workbook.createCellStyle();
    _base64Style.setDataFormat(dataFormat.getFormat(BASE64_FORMAT));
}

From source file:org.seasar.dbflute.helper.io.xls.DfXlsWriter.java

License:Apache License

public void setOutputStream(OutputStream out) {
    this.out = out;
    workbook = new HSSFWorkbook();
    HSSFDataFormat df = workbook.createDataFormat();
    dateStyle = workbook.createCellStyle();
    dateStyle.setDataFormat(df.getFormat(DATE_FORMAT));
    base64Style = workbook.createCellStyle();
    base64Style.setDataFormat(df.getFormat(BASE64_FORMAT));
}

From source file:org.seasar.extension.dataset.impl.XlsWriter.java

License:Apache License

/**
 * ???//from w  w w .ja  v  a  2  s.  c  o  m
 * 
 * @param out
 *            
 */
public void setOutputStream(OutputStream out) {
    this.out = out;
    workbook = new HSSFWorkbook();
    HSSFDataFormat df = workbook.createDataFormat();
    dateStyle = workbook.createCellStyle();
    dateStyle.setDataFormat(df.getFormat(DATE_FORMAT));
    base64Style = workbook.createCellStyle();
    base64Style.setDataFormat(df.getFormat(BASE64_FORMAT));
}

From source file:org.tentackle.ui.FormTableUtilityPopup.java

License:Open Source License

/**
 * Converts the table to an excel spreadsheet.
 * @param file the output file/*from   w  ww .  j a  v  a2 s. c  o m*/
 * @param onlySelected true if export only selected rows
 * @throws IOException if export failed
 */
public void excel(File file, boolean onlySelected) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    TableModel model = table.getModel();
    TableColumnModel columnModel = table.getColumnModel();

    int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {};

    int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows
    int cols = columnModel.getColumnCount(); // number of data columns

    short srow = 0; // current spreadsheet row

    // local copies cause might be changed
    String xTitle = this.title;
    String xIntro = this.intro;

    if (xTitle == null) {
        // get default from window title
        Window parent = FormHelper.getParentWindow(table);
        try {
            // paint page-title
            xTitle = ((FormWindow) parent).getTitle();
        } catch (Exception e) {
            xTitle = null;
        }
    }
    if (xTitle != null) {
        HSSFRow row = sheet.createRow(srow);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs.setFont(font);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(cs);
        cell.setCellValue(new HSSFRichTextString(xTitle));
        // region rowFrom, colFrom, rowTo, colTo
        sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1));
        srow++;
    }

    if (xIntro != null || onlySelected) {
        HSSFRow row = sheet.createRow(srow);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setWrapText(true);
        cell.setCellStyle(cs);
        if (onlySelected) {
            if (xIntro == null) {
                xIntro = "";
            } else {
                xIntro += ", ";
            }
            xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>");
        }
        cell.setCellValue(new HSSFRichTextString(xIntro));
        sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1));
        srow += 3;
    }

    // column headers
    boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel;
    srow++; // always skip one line
    HSSFRow row = sheet.createRow(srow);
    HSSFFont font = wb.createFont();
    font.setItalic(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle cs = wb.createCellStyle();
    cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cs.setFont(font);
    for (int c = 0; c < cols; c++) {
        HSSFCell cell = row.createCell(c);
        cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel
                ? ((AbstractFormTableModel) model)
                        .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex())
                : model.getColumnName(columnModel.getColumn(c).getModelIndex())));
        cell.setCellStyle(cs);
    }
    srow++;

    // default cell-style for date
    HSSFCellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    // cellstyles for numbers
    List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>();
    HSSFDataFormat format = wb.createDataFormat();

    for (int r = 0; r < rows; r++) {

        int modelRow = onlySelected ? selectedRows[r] : r;

        row = sheet.createRow(srow + (short) r);

        for (int i = 0; i < cols; i++) {

            int c = columnModel.getColumn(i).getModelIndex();

            Object value = model.getValueAt(modelRow, c);

            HSSFCell cell = row.createCell(i);

            if (value instanceof Boolean) {
                cell.setCellValue(((Boolean) value).booleanValue());
            } else if (value instanceof BMoney) {
                BMoney money = (BMoney) value;
                cell.setCellValue(money.doubleValue());

                String fmt = "#,##0";
                if (money.scale() > 0) {
                    fmt += ".";
                    for (int j = 0; j < money.scale(); j++) {
                        fmt += "0";
                    }
                }
                // create format
                short fmtIndex = format.getFormat(fmt);

                // check if there is already a cellstyle with this scale
                Iterator<HSSFCellStyle> iter = numberStyles.iterator();
                boolean found = false;
                while (iter.hasNext()) {
                    cs = iter.next();
                    if (cs.getDataFormat() == fmtIndex) {
                        // reuse that
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    // create a new style
                    cs = wb.createCellStyle();
                    cs.setDataFormat(fmtIndex);
                    numberStyles.add(cs);
                }
                cell.setCellStyle(cs);
            } else if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellValue((Date) value);
                cell.setCellStyle(dateStyle);
            } else if (value instanceof GregorianCalendar) {
                cell.setCellValue((GregorianCalendar) value);
                cell.setCellStyle(dateStyle);
            } else if (value != null) {
                cell.setCellValue(new HSSFRichTextString(value.toString()));
            }
        }
    }

    // set the width for each column
    for (int c = 0; c < cols; c++) {
        short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value
        sheet.setColumnWidth(c, width);
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();

    // open Excel
    URLHelper.openURL(file.getPath());
}

From source file:poi.hssf.usermodel.examples.InCellLists.java

License:Apache License

/**
 * Inserts a single bulleted item into a cell.
 *
 * @param workbook A reference to the HSSFWorkbook that 'contains' the
 *                 cell.//www  .  j  a  v  a  2  s . com
 * @param listItem An instance of the String class encapsulating the
 *                 items text.
 * @param cell An instance of the HSSFCell class that encapsulates a
 *             reference to the spreadsheet cell into which the list item
 *             will be written.
 */
public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) {
    // A format String must be built to ensure that the contents of the
    // cell appear as a bulleted item.
    HSSFDataFormat format = workbook.createDataFormat();
    String formatString = InCellLists.BULLET_CHARACTER + " @";
    int formatIndex = format.getFormat(formatString);

    // Construct an HSSFCellStyle and set it's data formt to use the
    // object created above.
    HSSFCellStyle bulletStyle = workbook.createCellStyle();
    bulletStyle.setDataFormat((short) formatIndex);

    // Set the cells contents and style.
    cell.setCellValue(new HSSFRichTextString(listItem));
    cell.setCellStyle(bulletStyle);
}

From source file:ro.nextreports.engine.exporter.XlsExporter.java

License:Apache License

private HSSFCellStyle buildBandElementStyle(BandElement bandElement, Object value, int gridRow, int gridColumn,
        int colSpan) {
    Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan);
    HSSFCellStyle cellStyle;//from  w  ww. j a  v  a 2 s  .c om
    HSSFFont cellFont = null;
    int fontKey = -1;
    // we have to create new fonts and styles if some formatting conditions are met  
    // also for subreports we may have a subreportCellStyle passed by ReportBandElement 
    boolean cacheFont = false;
    boolean cacheAllFont = false;
    if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) {
        fontKey = getFontKey(style);
        if (fontKey != -1) {
            cellFont = condFonts.get(fontKey);
        }
        cellStyle = wb.createCellStyle();
        if (cellFont == null) {
            cellFont = wb.createFont();
            cacheFont = true;
        }
        modifiedStyle[gridRow][gridColumn] = false;
    } else {
        cellStyle = styles[gridRow][gridColumn];
        fontKey = getFontKey(style);
        if (fontKey != -1) {
            cellFont = fonts.get(fontKey);
        }
        if ((cellFont == null) && (bandElement != null)) {
            cellFont = wb.createFont();
            cacheAllFont = true;
        }
    }

    // HSSFPalette cellPal = wb.getCustomPalette();        
    if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) {
        String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY);
        cellFont.setFontName(val);
    }
    if (style.containsKey(StyleFormatConstants.FONT_SIZE)) {
        Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE);
        cellFont.setFontHeightInPoints(val.shortValue());
    }
    if (style.containsKey(StyleFormatConstants.FONT_COLOR)) {
        Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR);
        cellFont.setColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) {
        if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        }
        if (StyleFormatConstants.FONT_STYLE_BOLD.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setItalic(true);
        }
        if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
            cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            cellFont.setItalic(true);
        }
    }

    if (cacheFont && (fontKey != -1)) {
        condFonts.put(fontKey, cellFont);
    }
    if (cacheAllFont && (fontKey != -1)) {
        fonts.put(fontKey, cellFont);
    }
    if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) {
        Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) {
        if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 1);
        }
        if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 3);
        }
        if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER
                .equals(style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
            cellStyle.setAlignment((short) 2);
        }
    }

    if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) {
        if (StyleFormatConstants.VERTICAL_ALIGN_TOP
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
        }
        if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        }
        if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM
                .equals(style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        }
    } else {
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    }

    short left = 0, right = 0, top = 0, bottom = 0;
    Color leftColor = Color.BLACK, rightColor = Color.BLACK, topColor = Color.BLACK, bottomColor = Color.BLACK;
    if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT);
        //
        left = val.shortValue();
        if (left == BORDER_THIN_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        }
        if (left == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (left == BORDER_THICK_VALUE) {
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
        }

        Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR);
        leftColor = color;
        cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT);
        //
        right = val.shortValue();
        if (right == BORDER_THIN_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        }
        if (right == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (right == BORDER_THICK_VALUE) {
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR);
        rightColor = color;
        cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_TOP)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP);
        //
        top = val.shortValue();
        if (top == BORDER_THIN_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        }
        if (top == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (top == BORDER_THICK_VALUE) {
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR);
        topColor = color;
        cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) {
        Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM);
        //
        bottom = val.shortValue();
        if (bottom == BORDER_THIN_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        }
        if (bottom == BORDER_MEDIUM_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
        }
        if (bottom == BORDER_THICK_VALUE) {
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
        }
        Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR);
        bottomColor = color;
        cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    border = new Border(left, right, top, bottom);
    border.setLeftColor(leftColor);
    border.setRightColor(rightColor);
    border.setTopColor(topColor);
    border.setBottomColor(bottomColor);

    if (cellFont != null) {
        cellStyle.setFont(cellFont);
    }

    if (style.containsKey(StyleFormatConstants.PATTERN)) {
        String pattern = (String) style.get(StyleFormatConstants.PATTERN);
        HSSFDataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat(pattern));
    }

    if (bandElement != null) {
        cellStyle.setWrapText(bandElement.isWrapText());
    }

    cellStyle = updateSubreportBandElementStyle(cellStyle, bandElement, value, gridRow, gridColumn, colSpan);

    return cellStyle;
}

From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java

@Override
public void exportData(String selectedFile, AnProperties props,
        ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception {
    if (selectedFile == null) {
        return;//from w w w .j  a v  a2s. c  o m
    }
    System.setProperty("java.awt.headless", "true");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    ps.setLandscape(true);

    HSSFCellStyle cellStyleT = wb.createCellStyle();
    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setFontHeightInPoints((short) 8);
    cellStyleT.setFont(font1);

    int rnumber = 0;
    HSSFRow row = sheet.createRow(0);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
    HSSFCell cell = row.createCell(0);

    cell.setCellValue(
            new HSSFRichTextString(" ???  ???  - "
                    + DateConverters.getDateToStr(LocalDate.now())));
    cell.setCellStyle(cellStyleT);
    rnumber++;

    if (props != null) {

        String val = " :  " + DateConverters.getDateToStr(props.getDateFrom())
                + "  " + DateConverters.getDateToStr(props.getDateTo());
        createHeaderCell(sheet, rnumber, val, cellStyleT);
        rnumber++;
        //----------
        if (props.isSalMode()) {
            String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE
                    ? " "
                    : "  ";
            val = "? " + type + "| " + props.getSaler().getNameSaler();
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
            if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) {
                type = props.isSalDirectSales() ? "? "
                        : " ";
                val = " : " + type;
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.isSalFixedDepartment()) {
                    val = " : ";
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }
        }
        if (props.isGoodMode()) {
            if (props.getGoodClassLev0() != null) {
                val = "? 1: " + props.getGoodClassLev0().getName();
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
                if (props.getGoodClassLev1() != null) {
                    val = "? 2: " + props.getGoodClassLev1().getName();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    if (props.getGoodClassLev2() != null) {
                        val = "? 3: " + props.getGoodClassLev2().getName();
                        createHeaderCell(sheet, rnumber, val, cellStyleT);
                        rnumber++;
                        if (props.getGoodClassLev3() != null) {
                            val = "? 4: " + props.getGoodClassLev3().getName();
                            createHeaderCell(sheet, rnumber, val, cellStyleT);
                            rnumber++;
                        }
                    }
                }

                if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) {
                    val = " : " + props.getGoodCustomSearch();
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

                if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) {
                    val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                    val = " : "
                            + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1));
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }
                if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) {
                    val = "+: ";
                    for (LocalDate ld : props.getGoodsIndateLst()) {
                        val += DateConverters.getDateToStr(ld) + "; ";
                    }
                    createHeaderCell(sheet, rnumber, val, cellStyleT);
                    rnumber++;
                }

            }

        }

        if (!props.getClLst().isEmpty()) {
            if (props.isClIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }

            val = "+: ";
            for (ClientBean cb : props.getClLst()) {
                val += cb.getClientCl() + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }

        if (!props.getVLst().isEmpty()) {
            if (props.isVIncluded()) {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            } else {
                val = " : ";
                createHeaderCell(sheet, rnumber, val, cellStyleT);
                rnumber++;
            }
            val = "";
            for (String v : props.getVLst()) {
                val += v + "; ";
            }
            createHeaderCell(sheet, rnumber, val, cellStyleT);
            rnumber++;
        }
    }
    //----------                                    

    HSSFCellStyle cellStyleH = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleH.setFont(font);
    cellStyleH.setWrapText(true);
    cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleH.setBorderLeft((short) 1);
    cellStyleH.setBorderRight((short) 1);
    cellStyleH.setBorderTop((short) 1);
    cellStyleH.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleHh = wb.createCellStyle();
    font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyleHh.setFont(font);
    cellStyleHh.setWrapText(true);
    cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    cellStyleHh.setBorderLeft((short) 1);
    cellStyleHh.setBorderRight((short) 1);
    cellStyleHh.setBorderTop((short) 1);
    cellStyleHh.setBorderBottom((short) 1);

    //filling table
    HSSFCellStyle cellStyleN = wb.createCellStyle();
    cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleN.setBorderLeft((short) 1);
    cellStyleN.setBorderRight((short) 1);
    cellStyleN.setBorderTop((short) 1);
    cellStyleN.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleI = wb.createCellStyle();
    cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
    cellStyleI.setBorderLeft((short) 1);
    cellStyleI.setBorderRight((short) 1);
    cellStyleI.setBorderTop((short) 1);
    cellStyleI.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleD = wb.createCellStyle();
    cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
    HSSFDataFormat df = wb.createDataFormat();
    cellStyleD.setDataFormat(df.getFormat("#,##0.0"));
    cellStyleD.setBorderLeft((short) 1);
    cellStyleD.setBorderRight((short) 1);
    cellStyleD.setBorderTop((short) 1);
    cellStyleD.setBorderBottom((short) 1);

    HSSFCellStyle cellStyleP = wb.createCellStyle();
    cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);

    cellStyleP.setDataFormat(df.getFormat("0.0\\%"));
    cellStyleP.setBorderLeft((short) 1);
    cellStyleP.setBorderRight((short) 1);
    cellStyleP.setBorderTop((short) 1);
    cellStyleP.setBorderBottom((short) 1);

    // filling column headers
    row = sheet.createRow(rnumber);

    String rowTitle = null;

    row = sheet.createRow(rnumber);
    row.setHeightInPoints(40);
    cell = row.createCell(0);
    cell.setCellValue(new HSSFRichTextString("?"));
    cell.setCellStyle(cellStyleH);

    for (int t = 0; t < columnTitles.size(); t++) {
        cell = row.createCell(t + 1);
        cell.setCellValue(new HSSFRichTextString(columnTitles.get(t)));
        cell.setCellStyle(cellStyleH);
    }

    // filling table with data
    rnumber++;

    for (ObservableList<Object> line : exportData) {
        row = sheet.createRow(rnumber);
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString((String) line.get(0)));
        cell.setCellStyle(cellStyleN);
        for (int i = 1; i < line.size(); i++) {
            Double val = (Double) line.get(i);
            cell = row.createCell(i);
            cell.setCellStyle(cellStyleD);
            cell.setCellValue(val);
        }
        rnumber++;
    }
    for (int t = 0; t < columnTitles.size(); t++) {
        sheet.autoSizeColumn((short) t);
    }
    saveWorkBook(wb, selectedFile);
    execute(selectedFile);
}