Example usage for org.apache.poi.ss.usermodel CellStyle cloneStyleFrom

List of usage examples for org.apache.poi.ss.usermodel CellStyle cloneStyleFrom

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. cloneStyleFrom

Introduction

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

Prototype

void cloneStyleFrom(CellStyle source);

Source Link

Document

Clones all the style information from another CellStyle, onto this one.

Usage

From source file:hornet.framework.web.service.export.AbstractTableExportService.java

License:CeCILL license

@Override
public HSSFWorkbook construireXlsModel(final T toExport) {

    // Blank workbook
    final HSSFWorkbook workbook = new HSSFWorkbook();

    // Create a blank sheet
    final HSSFSheet sheet = workbook.createSheet();
    int rownum = 0;

    // Style pour la bordure des cellules
    final CellStyle styleBordure = workbook.createCellStyle();
    styleBordure.setBorderBottom(BorderStyle.THIN);
    styleBordure.setBorderTop(BorderStyle.THIN);
    styleBordure.setBorderRight(BorderStyle.THIN);
    styleBordure.setBorderLeft(BorderStyle.THIN);

    final CellStyle styleEntete = workbook.createCellStyle();
    styleEntete.cloneStyleFrom(styleBordure);
    styleEntete.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    styleEntete.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    // Rcupration du Table VO
    final TableVO tableVo = construireTableauExport(toExport);

    final Collection<String> colTitles = tableVo.getColumnsTitles();
    final Iterator<String> itTitles = colTitles.iterator();

    // Titre des colonnes
    int cellnum = 0;
    final Row xlsRow = sheet.createRow(rownum++);
    while (itTitles.hasNext()) {
        final String title = itTitles.next();
        final Cell cell = xlsRow.createCell(cellnum++);
        cell.setCellValue(title);//  www .  ja  v a 2 s .  com
        cell.setCellStyle(styleEntete);
    }

    if (tableVo.getRows() != null) {
        final List<RowVO> rows = tableVo.getRows();
        final Iterator<RowVO> itRows = rows.iterator();
        // Lignes
        while (itRows.hasNext()) {
            this.exporteLigne(itRows, rownum, sheet, styleBordure);
            rownum++;
        }

        for (int i = 0; i < cellnum; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    return workbook;
}

From source file:info.informationsea.tableio.excel.ExcelSheetWriter.java

License:Open Source License

public void registerBaseCellStyle(Object index, CellStyle style) {
    CellStyle baseCellStyles = style;/* w  ww.  jav  a  2 s .  com*/

    // header style
    CellStyle headerCellStyles = sheet.getWorkbook().createCellStyle();
    headerCellStyles.cloneStyleFrom(style);
    Font headerFont = sheet.getWorkbook().createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerCellStyles.setFont(headerFont);
    headerCellStyles.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerCellStyles.setFillForegroundColor(IndexedColors.WHITE.getIndex());

    // alternative style
    CellStyle alternativeCellStyles = sheet.getWorkbook().createCellStyle();
    alternativeCellStyles.cloneStyleFrom(style);
    if (style instanceof XSSFCellStyle) {
        ((XSSFCellStyle) alternativeCellStyles).setFillForegroundColor(new XSSFColor(new Color(242, 242, 242)));
    } else {
        alternativeCellStyles.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    }
    alternativeCellStyles.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // link style
    Font linkFont = sheet.getWorkbook().createFont();
    linkFont.setColor(IndexedColors.BLUE.getIndex());
    CellStyle linkStyle = sheet.getWorkbook().createCellStyle();
    linkStyle.cloneStyleFrom(baseCellStyles);
    linkStyle.setFont(linkFont);
    CellStyle alternativeLinkStyle = sheet.getWorkbook().createCellStyle();
    alternativeLinkStyle.cloneStyleFrom(alternativeCellStyles);
    alternativeLinkStyle.setFont(linkFont);

    Map<CellStyleType, CellStyle> styleMap = new HashMap<>();
    styleMap.put(CellStyleType.BASE, baseCellStyles);
    styleMap.put(CellStyleType.HEADER, headerCellStyles);
    styleMap.put(CellStyleType.ALTERNATIVE, alternativeCellStyles);
    styleMap.put(CellStyleType.BASE, baseCellStyles);
    styleMap.put(CellStyleType.LINK, linkStyle);
    styleMap.put(CellStyleType.LINK_ALTERNATIVE, alternativeLinkStyle);
    styles.put(index, styleMap);
}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes,
        int beginRowData, int beginColumnData) {

    CellStyle dCellStyle = buildDataCellStyle(sheet);

    int rownum = beginRowData;
    short formatIndexInt = HSSFDataFormat.getBuiltinFormat("#,##0");
    CellStyle cellStyleInt = wb.createCellStyle(); // cellStyleInt is the default cell style for integers
    cellStyleInt.cloneStyleFrom(dCellStyle);
    cellStyleInt.setDataFormat(formatIndexInt);

    short formatIndexDoub = HSSFDataFormat.getBuiltinFormat("#,##0.00");
    CellStyle cellStyleDoub = wb.createCellStyle(); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    cellStyleDoub.setDataFormat(formatIndexDoub);

    CellStyle cellStyleDate = wb.createCellStyle(); // cellStyleDate is the default cell style for dates
    cellStyleDate.cloneStyleFrom(dCellStyle);
    //cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm"));
    cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d"));

    for (int i = 0; i < numberOfRows; i++) {
        Row rowVal = sheet.getRow(rownum);
        IRecord record = (IRecord) dataStore.getRecordAt(i);
        List fields = record.getFields();
        int length = extractedFieldsMetaData.size();
        for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) {
            IFieldMetaData metaField = extractedFieldsMetaData.get(fieldIndex);
            IField f = (IField) record.getFieldAt((Integer) metaField.getProperty("index"));
            if (f != null && f.getValue() != null) {

                Boolean visible = (Boolean) metaField.getProperty("visible");
                if (visible) {
                    Class c = metaField.getType();
                    logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]");
                    if (rowVal == null) {
                        rowVal = sheet.createRow(rownum);
                    }/*from  ww w. j  ava 2s. c o m*/
                    Cell cell = rowVal.createCell(fieldIndex + beginColumnData);
                    cell.setCellStyle(dCellStyle);
                    if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.intValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt);
                    } else if (Number.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.doubleValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleDoub);
                    } else if (String.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]");
                        String val = (String) f.getValue();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else if (Boolean.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]");
                        Boolean val = (Boolean) f.getValue();
                        cell.setCellValue(val.booleanValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                    } else if (Date.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]");
                        Date val = (Date) f.getValue();

                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        String dtString = df.format(val);
                        cell.setCellValue(dtString);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else {
                        logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]");
                        String val = f.getValue().toString();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    }
                }

            }
        }

        rownum++;
    }
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes,
        int beginRowData, int beginColumnData) {
    CellStyle dCellStyle = this.buildCellStyle(sheet);
    Iterator it = dataStore.iterator();
    int rownum = beginRowData;
    short formatIndexInt = this.getBuiltinFormat("#,##0");
    CellStyle cellStyleInt = this.buildCellStyle(sheet); // cellStyleInt is the default cell style for integers
    cellStyleInt.cloneStyleFrom(dCellStyle);
    cellStyleInt.setDataFormat(formatIndexInt);

    CellStyle cellStyleDate = this.buildCellStyle(sheet); // cellStyleDate is the default cell style for dates
    cellStyleDate.cloneStyleFrom(dCellStyle);
    cellStyleDate.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));

    IMetaData d = dataStore.getMetaData();

    while (it.hasNext()) {
        Row rowVal = sheet.getRow(rownum);
        IRecord record = (IRecord) it.next();
        List fields = record.getFields();
        int length = fields.size();
        for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) {
            IField f = (IField) fields.get(fieldIndex);
            if (f != null && f.getValue() != null) {

                Class c = d.getFieldType(fieldIndex);
                logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]");
                if (rowVal == null) {
                    rowVal = sheet.createRow(rownum);
                }/*ww w  .j a va  2s. c  o m*/
                Cell cell = rowVal.createCell(fieldIndex + beginColumnData);
                cell.setCellStyle(dCellStyle);
                if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]");
                    IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex);
                    String scaleFactor = (String) fieldMetaData.getProperty(
                            WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);
                    Number val = (Number) f.getValue();
                    Double doubleValue = MeasureScaleFactorOption.applyScaleFactor(val.doubleValue(),
                            scaleFactor);
                    cell.setCellValue(doubleValue);
                    cell.setCellType(this.getCellTypeNumeric());
                    cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt);
                } else if (Number.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]");
                    IFieldMetaData fieldMetaData = d.getFieldMeta(fieldIndex);
                    String decimalPrecision = (String) fieldMetaData
                            .getProperty(IFieldMetaData.DECIMALPRECISION);
                    CellStyle cs;
                    if (decimalPrecision != null) {
                        cs = getDecimalNumberFormat(new Integer(decimalPrecision), sheet, createHelper,
                                dCellStyle);
                    } else {
                        cs = getDecimalNumberFormat(DEFAULT_DECIMAL_PRECISION, sheet, createHelper, dCellStyle);
                    }
                    Number val = (Number) f.getValue();
                    Double value = val.doubleValue();
                    String scaleFactor = (String) fieldMetaData.getProperty(
                            WorkSheetSerializationUtils.WORKSHEETS_ADDITIONAL_DATA_FIELDS_OPTIONS_SCALE_FACTOR);
                    cell.setCellValue(MeasureScaleFactorOption.applyScaleFactor(value, scaleFactor));
                    cell.setCellType(this.getCellTypeNumeric());
                    cell.setCellStyle((cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cs);
                } else if (String.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]");
                    String val = (String) f.getValue();
                    cell.setCellValue(createHelper.createRichTextString(val));
                    cell.setCellType(this.getCellTypeString());
                } else if (Boolean.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]");
                    Boolean val = (Boolean) f.getValue();
                    cell.setCellValue(val.booleanValue());
                    cell.setCellType(this.getCellTypeBoolean());
                } else if (Date.class.isAssignableFrom(c)) {
                    logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]");
                    Date val = (Date) f.getValue();
                    cell.setCellValue(val);
                    cell.setCellStyle(cellStyleDate);
                } else {
                    logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]");
                    String val = f.getValue().toString();
                    cell.setCellValue(createHelper.createRichTextString(val));
                    cell.setCellType(this.getCellTypeString());
                }
            }
        }
        rownum++;
    }
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper,
        CellStyle dCellStyle) {//from  w w  w. j av  a  2s  .c o  m

    if (decimalFormats.get(j) != null)
        return decimalFormats.get(j);
    String decimals = "";
    for (int i = 0; i < j; i++) {
        decimals += "0";
    }

    CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
        format += "." + decimals;
    }
    cellStyleDoub.setDataFormat(df.getFormat(format));

    decimalFormats.put(j, cellStyleDoub);
    return cellStyleDoub;
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private CellStyle getStyle(Object styleId, Type cellType, Workbook wb, CreationHelper createHelper) {
    if (cellType == Type.DATE || cellType == Type.FLOAT || cellType == Type.CURRENCY) {
        String key = String.valueOf(styleId) + cellType;
        CellStyle style = styleMap.get(key);
        if (style == null) {
            style = wb.createCellStyle();
            CellStyle parentStyle = styleMap.get(styleId);
            if (parentStyle != null) {
                style.cloneStyleFrom(parentStyle);
            }/*from w  w  w .j a v  a  2s  .  c o m*/
            if (cellType == Type.DATE) {
                style.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
            } else {
                style.setDataFormat(createHelper.createDataFormat().getFormat("General"));
            }
            styleMap.put(key, style);
        }
        return style;
    }
    return styleMap.get(styleId);
}

From source file:jgnash.engine.budget.BudgetResultsExport.java

License:Open Source License

public static void exportBudgetResultsModel(final File file, final BudgetResultsModel model) {

    Resource rb = Resource.get();

    Workbook wb;/*from w ww . j a  v a  2s.co m*/

    String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {
        wb = new HSSFWorkbook();
    }

    CreationHelper createHelper = wb.getCreationHelper();

    // create a new sheet
    Sheet s = wb.createSheet(model.getBudget().getName());

    // create header cell styles
    CellStyle headerStyle = wb.createCellStyle();

    // create 2 fonts objects
    Font amountFont = wb.createFont();
    Font headerFont = wb.createFont();

    amountFont.setFontHeightInPoints((short) 10);
    amountFont.setColor(IndexedColors.BLACK.getIndex());

    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    DataFormat df = wb.createDataFormat();

    // Set the other cell style and formatting
    headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerStyle.setBorderTop(CellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    headerStyle.setDataFormat(df.getFormat("text"));
    headerStyle.setFont(headerFont);
    headerStyle.setAlignment(CellStyle.ALIGN_CENTER);

    int row = 0;
    Row r = s.createRow(row);

    // create period headers
    for (int i = 0; i < model.getDescriptorList().size(); i++) {
        Cell c = r.createCell(i * 3 + 1);
        c.setCellValue(
                createHelper.createRichTextString(model.getDescriptorList().get(i).getPeriodDescription()));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, i * 3 + 1, i * 3 + 3));
    }

    {
        int col = model.getDescriptorList().size() * 3 + 1;
        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Title.Summary")));
        c.setCellStyle(headerStyle);
        s.addMergedRegion(new CellRangeAddress(row, row, col, col + 2));
    }

    // create results header columns
    row++;
    r = s.createRow(row);

    {
        Cell c = r.createCell(0);
        c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Account")));
        c.setCellStyle(headerStyle);

        for (int i = 0; i <= model.getDescriptorList().size(); i++) {
            c = r.createCell(i * 3 + 1);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Budgeted")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 2);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Change")));
            c.setCellStyle(headerStyle);

            c = r.createCell(i * 3 + 3);
            c.setCellValue(createHelper.createRichTextString(rb.getString("Column.Remaining")));
            c.setCellStyle(headerStyle);
        }
    }

    // must sort the accounts, otherwise child structure is not correct
    List<Account> accounts = new ArrayList<>(model.getAccounts());
    Collections.sort(accounts);

    // create account rows
    for (Account account : accounts) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(account.getCurrencyNode());
        String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);
        cs.setIndention((short) (model.getDepth(account) * 2));

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(account.getName()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), account);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // add group summary rows
    for (AccountGroup group : model.getAccountGroupList()) {

        CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(amountFont);
        amountStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        amountStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        amountStyle.setBorderBottom(CellStyle.BORDER_THIN);
        amountStyle.setBorderTop(CellStyle.BORDER_THIN);
        amountStyle.setBorderLeft(CellStyle.BORDER_THIN);
        amountStyle.setBorderRight(CellStyle.BORDER_THIN);

        DecimalFormat format = (DecimalFormat) CommodityFormat.getFullNumberFormat(model.getBaseCurrency());
        String pattern = format.toLocalizedPattern().replace("", model.getBaseCurrency().getPrefix());
        amountStyle.setDataFormat(df.getFormat(pattern));

        row++;

        int col = 0;

        r = s.createRow(row);

        CellStyle cs = wb.createCellStyle();
        cs.cloneStyleFrom(headerStyle);
        cs.setAlignment(CellStyle.ALIGN_LEFT);

        Cell c = r.createCell(col);
        c.setCellValue(createHelper.createRichTextString(group.toString()));
        c.setCellStyle(cs);

        List<CellReference> budgetedRefList = new ArrayList<>();
        List<CellReference> changeRefList = new ArrayList<>();
        List<CellReference> remainingRefList = new ArrayList<>();

        for (int i = 0; i < model.getDescriptorList().size(); i++) {

            BudgetPeriodResults results = model.getResults(model.getDescriptorList().get(i), group);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getBudgeted().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference budgetedRef = new CellReference(row, col);
            budgetedRefList.add(budgetedRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(results.getChange().doubleValue());
            c.setCellStyle(amountStyle);

            CellReference changeRef = new CellReference(row, col);
            changeRefList.add(changeRef);

            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_FORMULA);
            c.setCellStyle(amountStyle);
            c.setCellFormula(budgetedRef.formatAsString() + "-" + changeRef.formatAsString());

            CellReference remainingRef = new CellReference(row, col);
            remainingRefList.add(remainingRef);
        }

        // add summary columns                               
        addSummaryCell(r, ++col, budgetedRefList, amountStyle);
        addSummaryCell(r, ++col, changeRefList, amountStyle);
        addSummaryCell(r, ++col, remainingRefList, amountStyle);
    }

    // force evaluation
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    evaluator.evaluateAll();

    short columnCount = s.getRow(1).getLastCellNum();

    // autosize all of the columns + 10 pixels
    for (int i = 0; i <= columnCount; i++) {
        s.autoSizeColumn(i);
        s.setColumnWidth(i, s.getColumnWidth(i) + 10);
    }

    // Save
    String filename = file.getAbsolutePath();

    if (wb instanceof XSSFWorkbook) {
        filename = FileUtils.stripFileExtension(filename) + ".xlsx";
    } else {
        filename = FileUtils.stripFileExtension(filename) + ".xls";
    }

    try (FileOutputStream out = new FileOutputStream(filename)) {
        wb.write(out);
    } catch (Exception e) {
        Logger.getLogger(BudgetResultsExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:net.illustrato.ctrl.CtrlCore.java

private Row copyRow(Workbook workbook, Sheet worksheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destinationRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {/*w ww.  j  av a2 s.c om*/
        newRow = worksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case HSSFCell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            //Si tenemos que modificar la formulario lo podemos hacer como string
            //oldCell.getCellFormula().replace("A"+sourceRowNum, "A"+destinationRowNum)
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case HSSFCell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
    return newRow;
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void updateCellColor(Cell cell, short color) {
    CellStyle style = workbook.createCellStyle();
    ;/*from w  w  w  .j  a  v a  2s  .c  o  m*/
    style.cloneStyleFrom(cell.getCellStyle());
    style.setFillForegroundColor(color);
    style.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    cell.setCellStyle(style);
}

From source file:org.displaytag.render.HssfTableWriter.java

License:Open Source License

@Override
protected void writeTableBodyCloser(TableModel model) throws Exception {
    // write totals, if there are any
    boolean hasTotals = false;
    for (HeaderCell cell : model.getHeaderCellList()) {
        hasTotals = hasTotals || cell.isTotaled();
    }/*  w  ww  . j  av  a 2 s. c o  m*/
    if (!hasTotals) {
        return;
    }
    TableTotaler tt = model.getTotaler();
    writeRowOpener(null);
    for (HeaderCell cell : model.getHeaderCellList()) {
        writeColumnOpener(null);
        Object columnValue = (cell.isTotaled()) ? tt.getTotalForColumn(cell.getColumnNumber(), 0) : null;
        writeCellValue(columnValue);
        CellStyle st = this.utils.getNewCellStyle();
        st.cloneStyleFrom(this.currentCell.getCellStyle());
        st.setBorderTop(CellStyle.BORDER_THIN);
        st.setTopBorderColor(IndexedColors.BLACK.getIndex());
        this.currentCell.setCellStyle(st);
        writeColumnCloser(null);
    }
    writeRowCloser(null);
}