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

List of usage examples for org.apache.poi.ss.usermodel Workbook 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:jdbreport.model.io.xls.poi.Excel2007Writer.java

License:Apache License

protected CellStyle createStyle(jdbreport.model.CellStyle style, Workbook wb) {

    XSSFCellStyle newStyle = (XSSFCellStyle) wb.createCellStyle();
    newStyle.setAlignment(convertHorizontalAlign(style.getHorizontalAlignment()));
    newStyle.setVerticalAlignment(convertVerticalAlign(style.getVerticalAlignment()));

    Border border = style.getBorders(Border.LINE_BOTTOM);
    if (border != null) {
        newStyle.setBorderBottom(getBorder(border));
        newStyle.setBottomBorderColor(new XSSFColor(border.getColor()));
    }//from  w  w  w .j  ava 2s  . c  o m
    border = style.getBorders(Border.LINE_TOP);
    if (border != null) {
        newStyle.setBorderTop(getBorder(border));
        newStyle.setTopBorderColor(new XSSFColor(border.getColor()));
    }
    border = style.getBorders(Border.LINE_LEFT);
    if (border != null) {
        newStyle.setBorderLeft(getBorder(border));
        newStyle.setLeftBorderColor(new XSSFColor(border.getColor()));
    }
    border = style.getBorders(Border.LINE_RIGHT);
    if (border != null) {
        newStyle.setBorderRight(getBorder(border));
        newStyle.setRightBorderColor(new XSSFColor(border.getColor()));
    }

    Font font = wb.createFont();
    font.setFontName(style.getFamily());
    if (style.isBold()) {
        font.setBold(true);
    }
    font.setItalic(style.isItalic());
    if (style.isUnderline()) {
        font.setUnderline(Font.U_SINGLE);
    }
    if (style.isStrikethrough()) {
        font.setStrikeout(true);
    }
    font.setFontHeightInPoints((short) style.getSize());
    if (style.getForegroundColor() != null && !style.getForegroundColor().equals(Color.black)) {
        font.setColor(colorToIndex(wb, style.getForegroundColor()));
    }

    newStyle.setFont(font);

    if (style.getBackground() != null && !style.getBackground().equals(Color.white)) {
        newStyle.setFillForegroundColor(new XSSFColor(style.getBackground()));
        newStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    if (style.getAngle() != 0) {
        int angle = style.getAngle();
        if (angle > 90 && angle <= 180) {
            angle = 90;
        } else if (angle > 180 && angle <= 270) {
            angle = -90;
        } else if (angle > 270) {
            angle = -(360 - angle);
        }
        newStyle.setRotation((short) angle);
    }

    newStyle.setWrapText(style.isWrapLine());

    return newStyle;
}

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;

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

    if (extension.equals("xlsx")) {
        wb = new XSSFWorkbook();
    } else {//from  w w  w  .  ja va 2  s  .co  m
        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:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java

private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;//from  ww  w .ja  v a  2  s .  com
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

/**
 * Cell styles used for formatting the sheets
 *
 * @param wb/*from w w w. ja  v a 2 s  . c  o  m*/
 * @return Map<String, {@link CellStyle}>
 */
public static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();

    CellStyle style;
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = createBorderedStyle(wb);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", style);

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

    return styles;
}

From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java

License:Open Source License

/**
 *
 * @param wb/*from  ww  w  .  ja  v a2 s  . c o  m*/
 * @return CellStyle
 */
public static CellStyle createBorderedStyle(Workbook wb) {
    CellStyle style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    return style;
}

From source file:main.resources.FileExcel.java

public static void generaXlsx() throws IOException {

    //nombre del archivo de Excel
    String nombreArchivo = "quincena.xlsx";

    String nombreHoja1 = "fecha";//nombre de la hoja1

    Workbook libroTrabajo = new XSSFWorkbook();
    Sheet hoja1 = libroTrabajo.createSheet(nombreHoja1);

    Row row = hoja1.createRow((short) 1);
    //row.setHeightInPoints(10); //alto de celda

    Cell cell = row.createCell((short) 1);
    Cell cell1 = row.createCell((short) 1);
    cell.setCellValue("Asistencia fecha xxxxxx");
    CellStyle cellStyle = libroTrabajo.createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    //cellStyle.setFillBackgroundColor(IndexedColors.BLUE_GREY.getIndex());
    //cellStyle.setFillPattern(CellStyle.BIG_SPOTS);
    cellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());

    hoja1.addMergedRegion(new CellRangeAddress(1, // first row (0-based) primera fila
            1, //lasto row (0-based) ultima fila
            1, //first column (0-based) numero de columna inicial
            5 //last column (0-based) numero de columna final
    ));/*ww  w  . j a  v  a 2 s  .c  o  m*/
    cell.setCellStyle(cellStyle);
    cell1.setCellStyle(cellStyle);

    //escribir este libro en un OutputStream.
    try (FileOutputStream fileOut = new FileOutputStream(nombreArchivo)) {
        //escribir este libro en un OutputStream.
        libroTrabajo.write(fileOut);
        fileOut.flush();
    }
}

From source file:main.resources.FileExcel.java

public void excelDia() throws FileNotFoundException, IOException {
    String nombreFile = "quincena.xlsx";
    String nombreHoja = "dia x mes x ao x";

    Workbook libro = new XSSFWorkbook();
    Sheet hoja = libro.createSheet(nombreHoja);

    Font negrita = libro.createFont();
    negrita.setBoldweight(Font.BOLDWEIGHT_BOLD);

    CellStyle estilo = libro.createCellStyle();
    estilo.setAlignment(CellStyle.ALIGN_CENTER);
    estilo.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    estilo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    estilo.setBorderBottom(CellStyle.BORDER_THIN);
    estilo.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderLeft(CellStyle.BORDER_THIN);
    estilo.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderRight(CellStyle.BORDER_THIN);
    estilo.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setBorderTop(CellStyle.BORDER_THIN);
    estilo.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo.setFont(negrita);/*from w w w .j a  va2 s .  c om*/

    CellStyle bordes = libro.createCellStyle();
    bordes.setAlignment(CellStyle.ALIGN_LEFT);
    bordes.setBorderBottom(CellStyle.BORDER_THIN);
    bordes.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderLeft(CellStyle.BORDER_THIN);
    bordes.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderRight(CellStyle.BORDER_THIN);
    bordes.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    bordes.setBorderTop(CellStyle.BORDER_THIN);
    bordes.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());

    CellStyle estilo2 = libro.createCellStyle();
    estilo2.setAlignment(CellStyle.ALIGN_CENTER);
    estilo2.setBorderBottom(CellStyle.BORDER_THIN);
    estilo2.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderLeft(CellStyle.BORDER_THIN);
    estilo2.setLeftBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderRight(CellStyle.BORDER_THIN);
    estilo2.setRightBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setBorderTop(CellStyle.BORDER_THIN);
    estilo2.setTopBorderColor(IndexedColors.AUTOMATIC.getIndex());
    estilo2.setAlignment(CellStyle.ALIGN_CENTER);
    estilo2.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    estilo2.setFillPattern(CellStyle.SOLID_FOREGROUND);
    estilo2.setFont(negrita);

    CellStyle borderBot = libro.createCellStyle();
    borderBot.setBorderBottom(CellStyle.BORDER_THIN);
    borderBot.setBottomBorderColor(IndexedColors.AUTOMATIC.getIndex());

    Row row = hoja.createRow(1);

    //Row row1 = hoja.createRow(2);

    //empleados faltas
    Appi app = new Appi();
    Date Fecha = new Date();
    DateFormat formato = new SimpleDateFormat("YYYY-MM-dd");
    String fechaActual = formato.format(Fecha);
    ArrayList<Empleado> faltas = app.faltas(fechaActual);//obtengo listado de empleados
    String grupoBandera = "";
    String maestro = "";
    int pRow = 3;
    if (!faltas.isEmpty()) {
        Cell celda = row.createCell(3);
        Cell celda2 = row.createCell(4);
        Cell celda3 = row.createCell(5);
        Cell celda4 = row.createCell(6);
        Cell celda5 = row.createCell(7);
        combinarceldas(hoja, 1, 1, 3, 7);
        celda.setCellValue("Asistencia " + fechaActual);
        celda.setCellStyle(estilo);
        celda2.setCellStyle(estilo);
        celda3.setCellStyle(estilo);
        celda4.setCellStyle(estilo);
        celda5.setCellStyle(estilo);
        grupoBandera = faltas.get(0).getGrupo();

        //encabezados
        Row row2 = hoja.createRow(pRow);
        Cell cell = row2.createCell(1);
        cell.setCellValue("Nficha");
        cell.setCellStyle(estilo2);

        Cell cell1 = row2.createCell(2);
        cell1.setCellValue("1er Apellido");
        cell1.setCellStyle(estilo2);

        Cell cell2 = row2.createCell(3);
        cell2.setCellValue("2do Apellido");
        cell2.setCellStyle(estilo2);

        Cell cell3 = row2.createCell(4);
        cell3.setCellValue("1er Nombre");
        cell3.setCellStyle(estilo2);

        Cell cell4 = row2.createCell(5);
        cell4.setCellValue("2do Nombre");
        cell4.setCellStyle(estilo2);

        Cell cell5 = row2.createCell(6);
        cell5.setCellValue("Identificacion");
        cell5.setCellStyle(estilo2);

        Cell cell6 = row2.createCell(7);
        cell6.setCellValue("Da");
        cell6.setCellStyle(estilo2);

        Cell cell7 = row2.createCell(8);
        cell7.setCellValue("Cargo");
        cell7.setCellStyle(estilo2);

        Cell cell8 = row2.createCell(9);
        cell8.setCellValue("Grupo");
        cell8.setCellStyle(estilo2);
    }
    Empleado emp = null;
    for (int i = 0; i < faltas.size(); i++) {

        //datos
        emp = (Empleado) faltas.get(i);
        Grupo grupo = app.grupo(emp.getGrupo());

        if (!grupoBandera.equals(emp.getGrupo())) {
            grupoBandera = emp.getGrupo();
            pRow = pRow + 2;
            //frima maestro
            Row row4 = hoja.createRow(pRow);
            Cell celda9 = row4.createCell(1);
            combinarceldas(hoja, pRow, pRow, 1, 3);
            celda9.setCellValue("Maestro Grupo:");
            celda9.setCellStyle(estilo);
            Cell celda10 = row4.createCell(2);
            celda10.setCellStyle(bordes);
            Cell celda11 = row4.createCell(3);
            celda11.setCellStyle(bordes);
            Cell celda12 = row4.createCell(4);
            celda12.setCellStyle(borderBot);
            Cell celda13 = row4.createCell(5);
            celda13.setCellStyle(borderBot);
            Cell celda14 = row4.createCell(6);
            celda14.setCellStyle(borderBot);
            Cell celda15 = row4.createCell(7);
            celda15.setCellStyle(borderBot);
            Cell celda16 = row4.createCell(8);
            celda16.setCellStyle(borderBot);

            pRow++;

            Row row6 = hoja.createRow(pRow);
            Cell celda64 = row6.createCell(4);
            combinarceldas(hoja, pRow, pRow, 4, 8);
            celda64.setCellValue(maestro);

            pRow = pRow + 2;
            //encabexzados
            Row row2 = hoja.createRow(pRow);
            Cell cell = row2.createCell(1);
            cell.setCellValue("Nficha");
            cell.setCellStyle(estilo2);

            Cell cell1 = row2.createCell(2);
            cell1.setCellValue("1er Apellido");
            cell1.setCellStyle(estilo2);

            Cell cell2 = row2.createCell(3);
            cell2.setCellValue("2do Apellido");
            cell2.setCellStyle(estilo2);

            Cell cell3 = row2.createCell(4);
            cell3.setCellValue("1er Nombre");
            cell3.setCellStyle(estilo2);

            Cell cell4 = row2.createCell(5);
            cell4.setCellValue("2do Nombre");
            cell4.setCellStyle(estilo2);

            Cell cell5 = row2.createCell(6);
            cell5.setCellValue("Identificacion");
            cell5.setCellStyle(estilo2);

            Cell cell6 = row2.createCell(7);
            cell6.setCellValue("Da");
            cell6.setCellStyle(estilo2);

            Cell cell7 = row2.createCell(8);
            cell7.setCellValue("Cargo");
            cell7.setCellStyle(estilo2);

            Cell cell8 = row2.createCell(9);
            cell8.setCellValue("Grupo");
            cell8.setCellStyle(estilo2);
        }

        Row row5 = hoja.createRow(pRow + 1);
        Cell celda51 = row5.createCell(1);
        celda51.setCellStyle(bordes);
        celda51.setCellValue(emp.getnFicha());
        Cell celda52 = row5.createCell(2);
        celda52.setCellStyle(bordes);
        celda52.setCellValue(emp.getpApellido());
        Cell celda53 = row5.createCell(3);
        celda53.setCellStyle(bordes);
        celda53.setCellValue(emp.getsApellido());
        Cell celda54 = row5.createCell(4);
        celda54.setCellStyle(bordes);
        celda54.setCellValue(emp.getpNombre());
        Cell celda55 = row5.createCell(5);
        celda55.setCellStyle(bordes);
        celda55.setCellValue(emp.getsNombre());
        Cell celda56 = row5.createCell(6);
        celda56.setCellStyle(bordes);
        celda56.setCellValue(emp.getCedula());
        Cell celda57 = row5.createCell(7);
        celda57.setCellStyle(bordes);
        celda57.setCellValue(fechaActual);
        Cell celda58 = row5.createCell(8);
        celda58.setCellStyle(bordes);
        celda58.setCellValue(emp.getCargo());
        Cell celda59 = row5.createCell(9);
        celda59.setCellStyle(bordes);
        celda59.setCellValue(grupo.getNombre());
        pRow++;
        Empleado supervisor = app.empleado(grupo.getSupervisor());
        if (supervisor != null) {
            maestro = supervisor.getpNombre() + " " + supervisor.getsNombre() + " " + supervisor.getpApellido()
                    + " " + supervisor.getsApellido();
        } else {
            maestro = String.valueOf(grupo.getSupervisor());
        }

        //
        //String cedula = (String) e;
        //Empleado emp = app.empleado(cedula);

        System.out.println(emp.getCedula());

    }
    if (emp != null) {
        pRow = pRow + 2;
        //frima maestro
        Row row4 = hoja.createRow(pRow);
        Cell celda9 = row4.createCell(1);
        combinarceldas(hoja, pRow, pRow, 1, 3);
        celda9.setCellValue("Maestro Grupo:");
        celda9.setCellStyle(estilo);
        Cell celda10 = row4.createCell(2);
        celda10.setCellStyle(bordes);
        Cell celda11 = row4.createCell(3);
        celda11.setCellStyle(bordes);
        Cell celda12 = row4.createCell(4);
        celda12.setCellStyle(borderBot);
        Cell celda13 = row4.createCell(5);
        celda13.setCellStyle(borderBot);
        Cell celda14 = row4.createCell(6);
        celda14.setCellStyle(borderBot);
        Cell celda15 = row4.createCell(7);
        celda15.setCellStyle(borderBot);
        Cell celda16 = row4.createCell(8);
        celda16.setCellStyle(borderBot);

        pRow++;

        Row row6 = hoja.createRow(pRow);
        Cell celda64 = row6.createCell(4);
        combinarceldas(hoja, pRow, pRow, 4, 8);
        celda64.setCellValue(maestro);
    }

    //enmcabezados

    //Row row3 = hoja.createRow(1);
    // debe ejcutarse un loop de acuerdoa consaulta
    //datos

    //datos responsable firma

    //CellS
    //celda64.setCellStyle();

    try (FileOutputStream fileOut = new FileOutputStream(nombreFile)) {
        //escribir este libro en un OutputStream.
        libro.write(fileOut);
        fileOut.flush();
    }

}

From source file:model.Reports.java

private static Map<String, CellStyle> createStyles(Workbook wb) {

    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;//from   w  w w.  j  a v  a 2  s. c  o m
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
}

From source file:nc.noumea.mairie.appock.services.impl.ExportExcelServiceImpl.java

License:Open Source License

private CellStyle createCellWithBorderAndColor(Workbook wb, BorderStyle borderStyle,
        IndexedColors indexedColors, boolean bold) {
    CellStyle style = wb.createCellStyle();
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setAlignment(HorizontalAlignment.CENTER);
    if (borderStyle != null) {
        style.setBorderBottom(borderStyle);
        style.setBorderTop(borderStyle);
        style.setBorderLeft(borderStyle);
        style.setBorderRight(borderStyle);
    }/*from w  w w.  j a va 2  s.c  o  m*/

    if (indexedColors != null) {
        style.setFillForegroundColor(indexedColors.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    if (bold) {
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);
    }

    return style;
}

From source file:net.ceos.project.poi.annotated.core.CellStyleHandler.java

License:Apache License

/**
 * Initialize cell style.//from ww w .ja  va  2s. co m
 * 
 * @param wb
 *            the {@link Workbook} in use
 * @return the {@link CellStyle}.
 */
protected static CellStyle initializeCellStyle(final Workbook wb) {
    return wb.createCellStyle();
}