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:com.griffinslogistics.excel.BDLGenerator.java

private static Map<String, CellStyle> createStyles(Workbook workbook) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/*  w w  w  .  j a v a2s. c om*/
    Font titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 26);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("title", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("pulsioName", style);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("contacts", style);

    CellStyle footerStyle = workbook.createCellStyle();
    Font footerFont = workbook.createFont();
    footerFont.setFontHeightInPoints((short) 14);
    footerFont.setFontName("Calibri");
    footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    footerStyle.setFont(footerFont);
    footerStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);

    styles.put("footer", footerStyle);

    titleFont = workbook.createFont();
    titleFont.setFontHeightInPoints((short) 14);
    titleFont.setFontName("Calibri");
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont.setItalic(true);
    style = workbook.createCellStyle();
    style.setFont(titleFont);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);

    // Override 25% grey to lighter grey
    HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
    HSSFPalette palette = hssfWorkbook.getCustomPalette();
    palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte) 242, //RGB red (0-255)
            (byte) 242, //RGB green
            (byte) 242 //RGB blue
    );

    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderLeft(CellStyle.BORDER_MEDIUM);
    style.setBorderTop(CellStyle.BORDER_MEDIUM);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);

    styles.put("tableHeadersLeft", style);

    CellStyle headerRowMiddleCellStyle = workbook.createCellStyle();
    headerRowMiddleCellStyle.cloneStyleFrom(style);
    headerRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableHeadersMiddle", headerRowMiddleCellStyle);

    CellStyle headerRowRightCellStyle = workbook.createCellStyle();
    headerRowRightCellStyle.cloneStyleFrom(style);
    headerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableHeadersRight", headerRowRightCellStyle);

    CellStyle footerRowRightCellStyle = workbook.createCellStyle();
    footerRowRightCellStyle.cloneStyleFrom(style);
    footerRowRightCellStyle.setFillPattern(CellStyle.NO_FILL);
    footerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
    footerRowRightCellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);

    styles.put("tableFooters", footerRowRightCellStyle);

    CellStyle bodyRowLeftCellStyle = workbook.createCellStyle();
    bodyRowLeftCellStyle.cloneStyleFrom(style);
    Font titleBodyFont = workbook.createFont();
    titleBodyFont.setFontHeightInPoints((short) 14);
    titleBodyFont.setFontName("Calibri");
    bodyRowLeftCellStyle.setFont(titleBodyFont);
    bodyRowLeftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
    bodyRowLeftCellStyle.setFillPattern(CellStyle.NO_FILL);

    styles.put("tableBodyLeft", bodyRowLeftCellStyle);

    CellStyle bodyRowMiddleCellStyle = workbook.createCellStyle();
    bodyRowMiddleCellStyle.cloneStyleFrom(bodyRowLeftCellStyle);
    bodyRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN);

    styles.put("tableBodyMiddle", bodyRowMiddleCellStyle);

    CellStyle bodyRowRightCellStyle = workbook.createCellStyle();
    bodyRowRightCellStyle.cloneStyleFrom(bodyRowMiddleCellStyle);
    bodyRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);

    styles.put("tableBodyRight", bodyRowRightCellStyle);

    return styles;
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Return the session report as an Excel file
 * //from   ww w  .  j a va 2 s.co  m
 * @param sessionData
 * @return
 */
private ByteArrayOutputStream getSessionReportAsExcel(CompanyInfo company, AppUserInfo user,
        SessionDataInfo sessionData) throws AgileBaseException, IOException, SQLException {
    BaseReportInfo report = sessionData.getReport();
    if (report == null) {
        throw new ObjectNotFoundException("No report found in the session");
    }
    // create Excel spreadsheet
    Workbook workbook = new SXSSFWorkbook();
    // the pane 2 report
    String reportName = report.getReportName();
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    reportName = reportName.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    Sheet reportSheet;
    try {
        reportSheet = workbook.createSheet(reportName);
    } catch (IllegalArgumentException iaex) {
        reportSheet = workbook.createSheet(reportName + " " + report.getInternalReportName());
    }
    int rowNum = 0;
    // header
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Row row = reportSheet.createRow(rowNum);
    int columnNum = 0;
    Set<ReportFieldInfo> reportFields = report.getReportFields();
    for (ReportFieldInfo reportField : reportFields) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(reportField.getFieldName());
        cell.setCellStyle(boldCellStyle);
        BaseField field = reportField.getBaseField();
        if (field.equals(field.getTableContainingField().getPrimaryKey())) {
            reportSheet.setColumnHidden(columnNum, true);
        }
        columnNum++;
    }
    // data
    rowNum++;
    DataManagementInfo dataManagement = this.databaseDefn.getDataManagement();
    List<DataRowInfo> reportDataRows = dataManagement.getReportDataRows(company, report,
            sessionData.getReportFilterValues(), false, sessionData.getReportSorts(), -1, QuickFilterType.AND,
            false);
    String fieldValue = "";
    boolean defaultReport = (report.equals(report.getParentTable().getDefaultReport()));
    for (DataRowInfo dataRow : reportDataRows) {
        Map<BaseField, DataRowFieldInfo> dataRowFieldMap = dataRow.getDataRowFields();
        row = reportSheet.createRow(rowNum);
        columnNum = 0;
        for (ReportFieldInfo reportField : reportFields) {
            BaseField field = reportField.getBaseField();
            if (field instanceof TextField) {
                fieldValue = dataRowFieldMap.get(field).getKeyValue();
            } else {
                fieldValue = dataRowFieldMap.get(field).getDisplayValue();
            }
            if (!fieldValue.equals("")) {
                Cell cell;
                DatabaseFieldType dbFieldType = field.getDbType();
                if ((defaultReport) && (field instanceof RelationField)) {
                    dbFieldType = ((RelationField) field).getDisplayField().getDbType();
                }
                switch (dbFieldType) {
                case FLOAT:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Double.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                    }
                    break;
                case INTEGER:
                case SERIAL:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC);
                    try {
                        cell.setCellValue(Integer.valueOf(fieldValue.replace(",", "")));
                    } catch (NumberFormatException nfex) {
                        logger.debug(nfex.toString() + ": value " + fieldValue.replace(",", ""));
                        // Fall back to a string representation
                        cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                        cell.setCellValue(fieldValue);
                        logger.debug("Successfully set string instead");
                    }
                    break;
                case VARCHAR:
                default:
                    cell = row.createCell(columnNum, Cell.CELL_TYPE_STRING);
                    cell.setCellValue(Helpers.unencodeHtml(fieldValue));
                    break;
                }
            }
            columnNum++;
        }
        rowNum++;
    }
    // Export info worksheet
    addReportMetaDataWorksheet(company, user, sessionData, report, workbook);
    // one worksheet for each of the report summaries
    for (ChartInfo savedChart : report.getSavedCharts()) {
        this.addSummaryWorksheet(company, sessionData, savedChart, workbook);
    }
    // the default summary
    ChartInfo reportSummary = report.getChart();
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    if ((groupings.size() > 0) || (aggregateFunctions.size() > 0)) {
        this.addSummaryWorksheet(company, sessionData, reportSummary, workbook);
    }
    // write to output
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    workbook.write(outputStream);
    return outputStream;
}

From source file:com.gtwm.pb.servlets.ReportDownloader.java

License:Open Source License

/**
 * Add a worksheet to the report for the specified workbook
 *///  w w  w  .  j a  v  a 2s  . c om
private void addSummaryWorksheet(CompanyInfo company, SessionDataInfo sessionData, ChartInfo reportSummary,
        Workbook workbook) throws SQLException, CantDoThatException {
    ChartDataInfo reportSummaryData = this.databaseDefn.getDataManagement().getChartData(company, reportSummary,
            sessionData.getReportFilterValues(), false);
    if (reportSummaryData == null) {
        return;
    }
    int rowNum;
    Row row;
    Cell cell;
    int columnNum;
    String fieldValue;
    Sheet summarySheet;
    String summaryTitle = reportSummary.getTitle();
    if (summaryTitle == null) {
        summaryTitle = "Summary";
    } else if (summaryTitle.equals("")) {
        summaryTitle = "Summary";
    }
    // Replace any invalid characters : \ / ? * [ or ]
    // http://support.microsoft.com/kb/215205
    summaryTitle = summaryTitle.replaceAll("[\\/\\:\\\\\\?\\*\\[\\]]", "-");
    try {
        summarySheet = workbook.createSheet(summaryTitle);
    } catch (IllegalArgumentException iaex) {
        // sheet name must be unique
        summarySheet = workbook.createSheet(summaryTitle + " " + reportSummary.getId());
    }
    // header
    rowNum = 0;
    row = summarySheet.createRow(rowNum);
    columnNum = 0;
    CellStyle boldCellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    Set<ChartAggregateInfo> aggregateFunctions = reportSummary.getAggregateFunctions();
    Set<ChartGroupingInfo> groupings = reportSummary.getGroupings();
    for (ChartGroupingInfo grouping : groupings) {
        BaseField groupingBaseField = grouping.getGroupingReportField().getBaseField();
        if (groupingBaseField instanceof RelationField) {
            fieldValue = groupingBaseField.getTableContainingField() + ": "
                    + ((RelationField) groupingBaseField).getDisplayField();
        } else {
            fieldValue = groupingBaseField.getFieldName();
        }
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
        fieldValue = aggregateFunction.toString();
        cell = row.createCell(columnNum);
        cell.setCellValue(fieldValue);
        cell.setCellStyle(boldCellStyle);
        columnNum++;
    }
    List<ChartDataRowInfo> reportSummaryDataRows = reportSummaryData.getChartDataRows();
    rowNum++;
    for (ChartDataRowInfo summaryDataRow : reportSummaryDataRows) {
        row = summarySheet.createRow(rowNum);
        columnNum = 0;
        for (ChartGroupingInfo grouping : groupings) {
            fieldValue = summaryDataRow.getGroupingValue(grouping);
            row.createCell(columnNum).setCellValue(fieldValue);
            columnNum++;
        }
        for (ChartAggregateInfo aggregateFunction : aggregateFunctions) {
            Double number = summaryDataRow.getAggregateValue(aggregateFunction).doubleValue();
            row.createCell(columnNum, Cell.CELL_TYPE_NUMERIC).setCellValue(number);
            columnNum++;
        }
        rowNum++;
    }
}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos,
        Medico AdministratorUser) throws IOException {

    Guardias[] lGuardias;/*w w w.  j a v  a2  s.com*/

    Gson gson = new GsonBuilder().create();

    lGuardias = gson.fromJson(JSONContenidos, Guardias[].class);

    boolean xlsx = true;
    int year = calendar.get(Calendar.YEAR);
    int month = calendar.get(Calendar.MONTH);

    DateFormat _format = new SimpleDateFormat("yyyy-MM-dd");

    Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    calendar.set(Calendar.MONTH, month);
    calendar.set(Calendar.DAY_OF_MONTH, 1);

    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    //create a sheet for each month
    Sheet sheet = wb.createSheet(_format.format(calendar.getTime()));

    CellStyle styleBORDER = wb.createCellStyle();
    styleBORDER.setBorderRight(CellStyle.BORDER_THICK);
    styleBORDER.setBorderBottom(CellStyle.BORDER_THICK);
    styleBORDER.setBorderTop(CellStyle.BORDER_THICK);
    styleBORDER.setBorderLeft(CellStyle.BORDER_THICK);
    styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    //

    //turn off gridlines
    sheet.setDisplayGridlines(true);
    sheet.autoSizeColumn(0);
    sheet.setPrintGridlines(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);

    //header with month titles
    Row monthRow = sheet.createRow(1);
    Font fontH = wb.createFont();
    CellStyle CStyleH = wb.createCellStyle();
    CStyleH.setBorderRight(CellStyle.BORDER_THICK);
    CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
    fontH.setBold(true);
    CStyleH.setFont(fontH);
    for (int i = 0; i < days.length; i++) {

        Cell monthCell = monthRow.createCell(i);

        monthCell.setCellStyle(CStyleH);
        monthCell.setCellValue(days[i]);
        sheet.autoSizeColumn(i);

    }

    int cnt = 1, day = 1;
    int rownum = 2;
    for (int j = 0; j < 6; j++) {
        Row row = sheet.createRow(rownum++);
        Row rowGuardias;
        boolean bRowsCreated = false;

        // row.setHeightInPoints(100);
        for (int i = 0; i < days.length; i++) {
            Cell dayCell_1 = row.createCell(i);
            //  Cell dayCell_2 = row.createCell(i*2 + 1);

            int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7;
            //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);  
            if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) {

                Font font = wb.createFont();
                CellStyle CStyle = wb.createCellStyle();
                short colorI = HSSFColor.AQUA.index; // presencia
                //font.set(colorI);
                CStyle.setFillForegroundColor(colorI);
                CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //CStyle.setBorderBottom( colorBorder);
                CStyle.setBorderRight(CellStyle.BORDER_THICK);
                CStyle.setBorderBottom(CellStyle.BORDER_THICK);
                CStyle.setBorderTop(CellStyle.BORDER_THICK);
                CStyle.setBorderLeft(CellStyle.BORDER_THICK);
                CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());
                //CStyle.setFont(font);

                dayCell_1.setCellValue(day);
                dayCell_1.setCellStyle(CStyle);

                sheet.autoSizeColumn(i);

                String _Dia = _format.format(calendar.getTime());

                int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia
                for (int d = 0; d < lGuardias.length; d++) {

                    Guardias oGuardias = lGuardias[d];
                    if (oGuardias.getDiaGuardia().equals(_Dia)) {

                        if (!bRowsCreated)
                            rowGuardias = sheet.createRow(rownum++);
                        else
                            rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont);

                        Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i);
                        //    Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1);

                        List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(),
                                AdministratorUser.getServicioId());

                        Medico _oMedico = _lMedico.get(0);

                        font = wb.createFont();
                        CStyle = wb.createCellStyle();
                        // PRESENCIA 
                        // LOCALIZADA
                        //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre());
                        colorI = HSSFColor.LIGHT_ORANGE.index; // presencia                        
                        if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase()))
                            colorI = HSSFColor.GREEN.index;
                        else if (oGuardias.getTipo()
                                .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase()))
                            colorI = HSSFColor.BLUE.index;
                        else if (oGuardias.getTipo().equals("")) // residente
                            colorI = HSSFColor.RED.index;

                        font.setColor(colorI);

                        CStyle.setFont(font);
                        //CStyle.setBorderBottom( colorBorder);
                        CStyle.setBorderRight(CellStyle.BORDER_THICK);
                        CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex());

                        dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre()
                                + "[" + _oMedico.getIDMEDICO() + "]");
                        dayCell_1_GUARDIAS.setCellStyle(CStyle);

                        DataRowCont++;

                    }

                }
                bRowsCreated = true;

                //      dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString());

                calendar.set(Calendar.DAY_OF_MONTH, ++day);

                /*if(i == 0 || i == days.length-1) {
                    dayCell_1.setCellStyle(styles.get("weekend_left"));
                    dayCell_2.setCellStyle(styles.get("weekend_right"));
                } else {
                    dayCell_1.setCellStyle(styles.get("workday_left"));
                    dayCell_2.setCellStyle(styles.get("workday_right"));
                }
                } else {
                dayCell_1.setCellStyle(styles.get("grey_left"));
                dayCell_2.setCellStyle(styles.get("grey_right"));*/
            }
            cnt++;
        }
        if (calendar.get(Calendar.MONTH) > month)
            break;
    }
    //  }

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

    wb.close();

}

From source file:com.guardias.excel.CalendarToExcel.java

License:Apache License

/**
 * cell styles used for formatting calendar sheets
 *//*from  w w w . ja va2s  .  c o m*/
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 48);
    titleFont.setColor(IndexedColors.DARK_BLUE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    monthFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("month", style);

    Font dayFont = wb.createFont();
    dayFont.setFontHeightInPoints((short) 14);
    dayFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("weekend_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("weekend_right", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setLeftBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    style.setFont(dayFont);
    styles.put("workday_left", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("workday_right", style);

    style = wb.createCellStyle();
    style.setBorderLeft(BorderStyle.THIN);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_left", style);

    style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(borderColor);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(borderColor);
    styles.put("grey_right", style);

    return styles;
}

From source file:com.haulmont.mp2xls.helper.LogHelper.java

License:Apache License

public static CellStyle getStyleByType(Workbook workbook, LocalizationLog.Type type) {
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();

    switch (type) {
    case ADDED: {
        font.setColor(HSSFColor.GREEN.index);
        style.setFillBackgroundColor(HSSFColor.GREEN.index);
    }/*from   ww  w.  j a v  a  2  s .co m*/
        break;
    case ADDED_IN_NEW_FILE: {
        font.setColor(HSSFColor.BLUE.index);
        style.setFillBackgroundColor(HSSFColor.BLUE.index);
    }
        break;
    case CHANGED: {
        font.setColor(HSSFColor.ORANGE.index);
        style.setFillBackgroundColor(HSSFColor.ORANGE.index);
    }
        break;
    case MOVED: {
        font.setColor(HSSFColor.RED.index);
        style.setFillBackgroundColor(HSSFColor.RED.index);
    }
        break;
    }

    style.setFont(font);
    return style;
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?//from w ww .j a va2 s  .  c  o  m
 * 
 * @param wb
 *            
 * @return ?
 */
private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont1 = wb.createFont();
    titleFont1.setFontName("Arial");
    titleFont1.setFontHeightInPoints((short) 10);
    titleFont1.setBoldweight(Font.BOLDWEIGHT_BOLD);
    titleFont1.setColor(Font.COLOR_RED);
    style.setFont(titleFont1);
    styles.put("title1", style);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    // style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
}

From source file:com.hotaviano.tableexporter.xls.XLSExporter.java

License:Open Source License

private CellStyle createHeaderStyle(Workbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    Font font = wb.createFont();//from  w ww .  ja va  2s.  co m
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);
    return cellStyle;
}

From source file:com.hp.amss.util.HyperlinkExample.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);/*from   www  .  j  a va 2  s  . c om*/

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short) 0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell((short) 0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short) 0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //TODO
    cell.setCellValue(createHelper.createRichTextString(""));
    cell.setCellType(Cell.CELL_TYPE_STRING);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short) 0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream("C:\\hyperinks.xlsx");
    wb.write(out);
    out.close();

}

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * Excel//from   w w  w. j  ava  2  s .  com
 * 
 * @param id
 *            ID
 * @return Excel
 */
public String getModelExcel(String id) {
    List<ModelAttribute> list = ServiceManager.getModelService().getModelAttributesByModelId(id);

    // 
    String[] IGNORATTR = new String[] { "id", "create_time", "contract_start", "searchcode", "last_update_time",
            "contract_end", "task_link", "order_id", "customer_id", "status", "last_update_by" };
    List<String> l = new ArrayList<String>(Arrays.asList(IGNORATTR));

    Workbook wb = new HSSFWorkbook();
    // Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet(id);
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setColor(HSSFColor.RED.index);
    style.setFont(font);
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    int i = 0;
    Cell cell = null;
    HSSFRichTextString textString;
    for (ModelAttribute ma : list) {
        if (l.contains(ma.getAttrId()))
            continue;
        cell = row.createCell(i);
        textString = new HSSFRichTextString(ma.getName() + "/" + ma.getAttrId());
        cell.setCellValue(textString);
        if (!ma.isNullable())
            cell.setCellStyle(style);
        sheet.autoSizeColumn(i);
        i++;
    }
    for (int k = 0; k < list.size(); k++) {
        sheet.autoSizeColumn(k);
    }
    // Write the output to a file
    FileOutputStream fileOut;
    String file;
    try {
        file = System.getProperty("user.dir") + "/../temp/" + id + new Random().nextLong() + ".xls";
        fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        return "";
    } catch (IOException e) {
        e.printStackTrace();
        return "";
    }
    return file;
}