Example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern

List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFCellStyle
  6. setFillPattern

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern.

Prototype

@Override
public void setFillPattern(FillPatternType pattern)

Source Link

Document

This element is used to specify cell fill information for pattern and solid color cell fills.

Usage

From source file:com.pe.nisira.movil.view.action.MultitablaAction.java

public StreamedContent downFormatExcel() throws Exception {
    InputStream stream = null;/*from   ww w .j a  va  2s  . com*/
    StreamedContent arch = null;
    try {
        String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION";
        File ruta = new File(folder);
        if (!ruta.isDirectory()) {
            ruta.mkdirs();
        }
        String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx";
        File fileXls = new File(rutaArchivo);
        if (fileXls.exists()) {
            fileXls.delete();
        }
        fileXls.createNewFile();
        XSSFWorkbook libro = new XSSFWorkbook();
        FileOutputStream file = new FileOutputStream(fileXls);
        XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA");
        CreationHelper factory = libro.getCreationHelper();
        hoja = libro.getSheetAt(0);
        XSSFCellStyle style = libro.createCellStyle();
        Font font = libro.createFont();
        Font font1 = libro.createFont();
        Drawing drawing = hoja.createDrawingPatriarch();
        ClientAnchor anchor1 = factory.createClientAnchor();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font1.setFontHeightInPoints((short) 8);
        font1.setFontName("Arial");
        font.setFontName("Arial");
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);
        for (int f = 0; f < 1; f++) {
            XSSFRow fila = hoja.createRow(f);
            for (int c = 0; c < 4; c++) {
                XSSFCell celda = fila.createCell(c);
                celda.setCellStyle(style);
                anchor1.setCol1(celda.getColumnIndex());
                anchor1.setCol2(celda.getColumnIndex() + 4);
                anchor1.setRow1(fila.getRowNum());
                anchor1.setRow2(fila.getRowNum() + 3);
                Comment comment = drawing.createCellComment(anchor1);
                if (f == 0 && c == 0) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO).");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Es Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 1) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("Abreviatura Padre");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 2) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("DESCRIPCION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 3) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla.");
                    str.applyFont(font1);
                    str.applyFont(0, 29, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("ABREVIATURA");
                    celda.setCellComment(comment);
                }
            }
        }
        hoja.autoSizeColumn((short) 0);
        hoja.autoSizeColumn((short) 1);
        hoja.autoSizeColumn((short) 2);
        libro.write(file);
        file.close();
        stream = new FileInputStream(new File(rutaArchivo));
        arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx");
    } catch (FileNotFoundException ex) {
        System.out.println("Error al Descargar : " + ex.getMessage());
    }
    return arch;
}

From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java

public StreamedContent downFormatExcel() throws Exception {
    InputStream stream = null;/*from  ww w .j ava2  s.  co m*/
    StreamedContent arch = null;
    try {
        String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION";
        File ruta = new File(folder);
        if (!ruta.isDirectory()) {
            ruta.mkdirs();
        }
        String rutaArchivo = folder + "\\FI_REGISTROPALE.xlsx";
        File fileXls = new File(rutaArchivo);
        if (fileXls.exists()) {
            fileXls.delete();
        }
        fileXls.createNewFile();
        XSSFWorkbook libro = new XSSFWorkbook();
        FileOutputStream file2 = new FileOutputStream(fileXls);
        XSSFSheet hoja = libro.createSheet("IMPORTAR_PALETA");
        CreationHelper factory = libro.getCreationHelper();
        hoja = libro.getSheetAt(0);
        XSSFCellStyle style = libro.createCellStyle();
        Font font = libro.createFont();
        Font font1 = libro.createFont();
        Drawing drawing = hoja.createDrawingPatriarch();
        ClientAnchor anchor1 = factory.createClientAnchor();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 8);
        font1.setFontHeightInPoints((short) 8);
        font1.setFontName("Arial");
        font.setFontName("Arial");
        style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setAlignment(CellStyle.VERTICAL_CENTER);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);

        XSSFSheet hoja2 = libro.createSheet("IMPORTAR_DET_PALETA");
        CreationHelper factory2 = libro.getCreationHelper();
        hoja2 = libro.getSheetAt(1);
        XSSFCellStyle style2 = libro.createCellStyle();
        Font font2 = libro.createFont();
        Font font12 = libro.createFont();
        Drawing drawing2 = hoja2.createDrawingPatriarch();
        ClientAnchor anchor12 = factory2.createClientAnchor();
        font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font2.setFontHeightInPoints((short) 8);
        font12.setFontHeightInPoints((short) 8);
        font12.setFontName("Arial");
        font2.setFontName("Arial");
        style2.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70)));
        style2.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style2.setAlignment(CellStyle.VERTICAL_CENTER);
        style2.setAlignment(CellStyle.ALIGN_CENTER);
        style2.setFont(font);
        for (int f = 0; f < 1; f++) {
            XSSFRow fila = hoja.createRow(f);
            for (int c = 0; c < 29; c++) {
                XSSFCell celda = fila.createCell(c);
                celda.setCellStyle(style);
                anchor1.setCol1(celda.getColumnIndex());
                anchor1.setCol2(celda.getColumnIndex() + 5);
                anchor1.setRow1(fila.getRowNum());
                anchor1.setRow2(fila.getRowNum() + 3);
                Comment comment = drawing.createCellComment(anchor1);
                if (f == 0 && c == 0) {
                    RichTextString str = factory.createRichTextString("ADM:\nCampo Obligatorio \n - IDEMPRESA");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMPRESA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 1) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - IDREGISTROPALETA. \n Debe de tener (15) caracteres");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDREGISTROPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 2) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del emisor. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMISOR");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 3) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - id de la operacion.\n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDOPERACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 4) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Operacion.\n -Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NUMOPERACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 5) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del motivo de Paleta.\n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDMOTIVOPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 6) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id documento. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDDOCUMENTO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 7) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Serie del Documento. \n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("SERIE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 8) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Documento.\n -Debe tener 7 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NUMERO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 9) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("FECHA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 10) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Perido del ao \n - fromato YYYYMM.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("PERIODO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 11) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del estado \n -Debe tener 2 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDESTADO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 12) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del cliente o proveedor \n -Debe tener 11 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCLIEPROV");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 13) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Paleta \n -Debe tener 20 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NROPALETA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 14) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id de envase \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDENVASE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 15) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id la sucursal \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDSUCURSAL");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 16) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del almacen. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDALMACEN");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 17) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id del embalaje. \n -Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDEMBALAJE");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 18) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Id de cultivo. \n -Debe tener 4 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCULTIVO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 19) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - id de Variadd. \n -Debe tener 3 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDVARIEDAD");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 20) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Observaciones sobre la paleta \n -como maximo 240 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("OBSERVACIONES");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 21) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n -Nombre de la venta \n como maximo 50 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("VENTANA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 22) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Cantidad. \n - 15 numeros y 2 decimales como maximo.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("CANTIDAD");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 23) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Estado de la paleta \n- 1 = cerrado, 0 = Abierto.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("CERRADO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 24) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Sincroniza \n - N = no , S = si.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("SINCRONIZA");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 25) {
                    RichTextString str = factory
                            .createRichTextString("ADM:\nCampo Obligatorio \n - Formato YYYY/MM/DD.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("FECHACREACION");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 26) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Numero de Manural\n Debe tener 10 caracteres.");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("NROMANUAL");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 27) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - idcliepro-destino\n debe tener 11 caracteres");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("IDCLIEPROV_DESTINO");
                    celda.setCellComment(comment);
                } else if (f == 0 && c == 28) {
                    RichTextString str = factory.createRichTextString(
                            "ADM:\nCampo Obligatorio \n - Tipo de paleta\n debe tener 1 caraccter..");
                    str.applyFont(font1);
                    str.applyFont(0, 19, font);
                    comment.setString(str);
                    comment.setAuthor("ADM");
                    celda.setCellValue("TIPO");
                    celda.setCellComment(comment);
                }
            }
        }
        hoja.autoSizeColumn((short) 0);
        hoja.autoSizeColumn((short) 1);
        hoja.autoSizeColumn((short) 2);
        hoja.autoSizeColumn((short) 3);
        hoja.autoSizeColumn((short) 4);
        hoja.autoSizeColumn((short) 5);
        hoja.autoSizeColumn((short) 6);
        hoja.autoSizeColumn((short) 7);
        hoja.autoSizeColumn((short) 8);
        hoja.autoSizeColumn((short) 9);
        hoja.autoSizeColumn((short) 10);
        hoja.autoSizeColumn((short) 11);
        hoja.autoSizeColumn((short) 12);
        hoja.autoSizeColumn((short) 13);
        hoja.autoSizeColumn((short) 14);
        hoja.autoSizeColumn((short) 15);
        hoja.autoSizeColumn((short) 16);
        hoja.autoSizeColumn((short) 17);
        hoja.autoSizeColumn((short) 18);
        hoja.autoSizeColumn((short) 19);
        hoja.autoSizeColumn((short) 20);
        hoja.autoSizeColumn((short) 21);
        hoja.autoSizeColumn((short) 22);
        hoja.autoSizeColumn((short) 23);
        hoja.autoSizeColumn((short) 24);
        hoja.autoSizeColumn((short) 25);
        hoja.autoSizeColumn((short) 26);
        hoja.autoSizeColumn((short) 27);
        hoja.autoSizeColumn((short) 28);
        for (int f = 0; f < 2; f++) {
            XSSFRow fila2 = hoja2.createRow(f);
            if (f == 0) {
                for (int c = 0; c < 15; c++) {
                    XSSFCell celda2 = fila2.createCell(c);
                    anchor12.setCol1(celda2.getColumnIndex());
                    anchor12.setCol2(celda2.getColumnIndex() + 8);
                    anchor12.setRow1(fila2.getRowNum());
                    anchor12.setRow2(fila2.getRowNum() + 8);
                    Comment comment2 = drawing2.createCellComment(anchor12);
                    RichTextString str;
                    switch (c) {
                    case 0:
                        celda2.setCellStyle(style2);
                        str = factory2.createRichTextString(
                                "ADM:\nCampo Obligatorio \n - El Cdigo debe de ser nico.");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDEMPRESA");
                        celda2.setCellComment(comment2);
                        break;
                    case 1:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDREGISTROPALETA");
                        celda2.setCellComment(comment2);
                        break;
                    case 2:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("ITEM");
                        celda2.setCellComment(comment2);
                        break;
                    case 3:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio ");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCLIEPROV");
                        celda2.setCellComment(comment2);
                        break;
                    case 4:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTE");
                        celda2.setCellComment(comment2);
                        break;
                    case 5:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCONDICION");
                        celda2.setCellComment(comment2);
                        break;
                    case 6:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDTALLA");
                        celda2.setCellComment(comment2);
                        break;
                    case 7:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCOLOR");
                        celda2.setCellComment(comment2);
                        break;
                    case 8:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("CANTIDAD");
                        celda2.setCellComment(comment2);
                        break;
                    case 9:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDEMBALAJE");
                        celda2.setCellComment(comment2);
                        break;
                    case 10:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDPRODUCTO");
                        celda2.setCellComment(comment2);
                        break;
                    case 11:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTEP");
                        celda2.setCellComment(comment2);
                        break;
                    case 12:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDCONSUMIDOR");
                        celda2.setCellComment(comment2);
                        break;
                    case 13:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDLOTECAMPO");
                        celda2.setCellComment(comment2);
                        break;
                    case 14:
                        celda2.setCellStyle(style2);
                        str = factory.createRichTextString("ADM:\nCampo Obligatorio");
                        str.applyFont(font12);
                        str.applyFont(0, 19, font2);
                        comment2.setString(str);
                        comment2.setAuthor("ADM");
                        celda2.setCellValue("IDPRESENTACION");
                        celda2.setCellComment(comment2);
                        break;
                    }
                }
            }
        }
        hoja2.autoSizeColumn((short) 0);
        hoja2.autoSizeColumn((short) 1);
        hoja2.autoSizeColumn((short) 2);
        hoja2.autoSizeColumn((short) 3);
        hoja2.autoSizeColumn((short) 4);
        hoja2.autoSizeColumn((short) 5);
        hoja2.autoSizeColumn((short) 6);
        hoja2.autoSizeColumn((short) 7);
        hoja2.autoSizeColumn((short) 8);
        hoja2.autoSizeColumn((short) 9);
        hoja2.autoSizeColumn((short) 10);
        hoja2.autoSizeColumn((short) 11);
        hoja2.autoSizeColumn((short) 12);
        hoja2.autoSizeColumn((short) 13);
        hoja2.autoSizeColumn((short) 14);
        hoja2.autoSizeColumn((short) 15);
        libro.write(file2);
        file2.close();
        stream = new FileInputStream(new File(rutaArchivo));
        arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_REGISTROPALE.xlsx");
    } catch (FileNotFoundException ex) {
        System.out.println("Error al Descargar : " + ex.getMessage());
    }
    return arch;
}

From source file:com.philips.his.pixiu.cdr.poi.BigGridDemo.java

License:Apache License

/**
 * Create a library of cell styles.// w  ww.j  a  v a2  s. c  om
 */
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
    Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
    XSSFDataFormat fmt = wb.createDataFormat();

    XSSFCellStyle style1 = wb.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    style1.setDataFormat(fmt.getFormat("0.0%"));
    styles.put("percent", style1);

    XSSFCellStyle style2 = wb.createCellStyle();
    style2.setAlignment(HorizontalAlignment.CENTER);
    style2.setDataFormat(fmt.getFormat("0.0X"));
    styles.put("coeff", style2);

    XSSFCellStyle style3 = wb.createCellStyle();
    style3.setAlignment(HorizontalAlignment.RIGHT);
    style3.setDataFormat(fmt.getFormat("$#,##0.00"));
    styles.put("currency", style3);

    XSSFCellStyle style4 = wb.createCellStyle();
    style4.setAlignment(HorizontalAlignment.RIGHT);
    style4.setDataFormat(fmt.getFormat("mmm dd"));
    styles.put("date", style4);

    XSSFCellStyle style5 = wb.createCellStyle();
    XSSFFont headerFont = wb.createFont();
    headerFont.setBold(true);
    style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style5.setFont(headerFont);
    styles.put("header", style5);

    return styles;
}

From source file:com.sec.ose.osi.report.standard.CoverSheetTemplate.java

License:Open Source License

protected void createTitle() {
    short lineThickness = (short) (6 * BASE_HEIGHT);

    // Top Line/*from   w  w  w .j  a  v  a 2 s. co m*/
    Row row = sheet.createRow(ROW_4);
    row.setHeight(lineThickness);

    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);

    // Title
    row = sheet.createRow(ROW_5);
    row.setHeightInPoints(100);
    sheet.addMergedRegion(CellRangeAddress.valueOf("B5:G5"));

    Font font = wb.createFont();
    font.setFontHeightInPoints((short) 28);
    font.setFontName("Trebuchet MS");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    setDummyTitleStyle(row, style);
    row.getCell(COL_B).setCellValue("Open Source License Verification Report");

    // Bottom Line
    row = sheet.createRow(ROW_6);
    row.setHeight(lineThickness);

    style = wb.createCellStyle();
    style.setFillForegroundColor(DARK_BLUE);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    setDummyTitleStyle(row, style);
}

From source file:com.sec.ose.osi.report.standard.ISheetTemplate.java

License:Open Source License

/**
 * @param color//w  w w.  j ava 2 s.c  o m
 * @param font
 * @return CellStyle
 */
protected XSSFCellStyle getCellStyle(XSSFColor color, Font font) {
    XSSFCellStyle style = wb.createCellStyle();
    style.setFillForegroundColor(color);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // new line

    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    if (font != null)
        style.setFont(font);

    return style;
}

From source file:com.svi.main.logic.ExtractAndPrint.java

private void writeLogFile(List<Nodes> nodesHolder) {
    DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm");
    Date date = new Date();
    Sheet mainSheet;//from  w ww  .  j av a 2 s.co m
    Sheet dataSheet;
    Sheet elementSheet;
    Row dataSheetRow;
    Row elementSheetRow;
    InputStream fis;
    XSSFWorkbook workbook;
    File outputPath = new File(logPath + "\\Logs");
    File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name
    int dataSheetRowCount = 1;
    int elementSheetRowCount = 1;
    int totalElementException = 0;

    try {
        if (!Files.exists(outputPath.toPath())) {
            Files.createDirectories(outputPath.toPath());
        }
        fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx");
        workbook = new XSSFWorkbook(fis);

        //Style for exception sheet
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //Get data Sheet
        mainSheet = workbook.getSheetAt(0);
        writeProjectDetails(mainSheet);
        dataSheet = workbook.getSheetAt(4);
        dataSheetRow = dataSheet.createRow(0);

        elementSheet = workbook.getSheetAt(1);
        elementSheetRow = elementSheet.createRow(0);
        //Write excel headers
        writeDataSheetHeaders(dataSheetRow);
        writeElementSheetHeaders(elementSheetRow);

        //Set progress bar values
        progress = new AtomicInteger(0);
        total = new AtomicInteger(nodesHolder.size());
        mf.setJprogressValues(total, progress);
        // Sort the nodes per Node ID
        Collections.sort(nodesHolder, new Comparator<Nodes>() {
            public int compare(Nodes o1, Nodes o2) {
                return o1.getNodeId().compareTo(o2.getNodeId());
            }
        });
        //Write Data Sheet
        for (Nodes node : nodesHolder) {
            mf.loader();
            dataSheetRow = dataSheet.createRow(dataSheetRowCount++);
            writeDataSheet(node, dataSheetRow);
        }
        for (Nodes node : nodesHolder) {
            for (Elements e : node) {
                if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING"))
                        && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) {
                    totalElementException++;
                }
            }
        }
        progress = new AtomicInteger(0);
        total = new AtomicInteger(totalElementException);
        mf.setJprogressValues(total, progress);
        //Write exception sheet
        for (Nodes node : nodesHolder) {
            for (Elements e : node) {
                if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING"))
                        && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) {
                    elementSheetRow = elementSheet.createRow(elementSheetRowCount++);
                    writeElementSheet(e, elementSheetRow);
                    mf.elementLoader();
                }
            }
        }
        XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook);
        try (FileOutputStream outputStream = new FileOutputStream(outputFile)) {
            workbook.write(outputStream);//Write the Excel File
            outputStream.close();
        }
        workbook.close();
        fis.close();
        mf.generateMessage();
    } catch (Exception ex) {
        Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.tikal.tallerWeb.servicio.reporte.cliente.AbstractSeccionXLS.java

License:Apache License

public void addHeaderStyle(XSSFCellStyle cellStyle, XSSFWorkbook wb) {
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    //        cellStyle.setFillForegroundColor(new XSSFColor(new Color(85, 142, 213)));
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    XSSFFont font = wb.createFont();//from w  w w  .  j a  va  2  s .  c  o  m
    font.setColor(IndexedColors.WHITE.getIndex());
    font.setBold(true);
    cellStyle.setFont(font);
}

From source file:com.tutorial.excelreadwrite.excelFunctions.java

public void convertColor(int r, int g, int b, int numColors) {
        //Get the userDefinedColor and set the style
        userDefinedColor = new XSSFColor(new java.awt.Color(r, g, b));
        XSSFCellStyle userDefinedCS = workbook.createCellStyle();
        userDefinedCS.setFillForegroundColor(userDefinedColor);
        userDefinedCS.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //Create an arrayList and add foreground colors that will be converted and then remove them
        List<XSSFColor> listOfColors = new ArrayList();
        for (int i = 0; i < numColors; ++i) {
            try {
                //First row of excel document will be reserved for obtaining the colors of the foreground used
                listOfColors.add(sheet.getRow(0).getCell(i).getCellStyle().getFillForegroundXSSFColor());
                sheet.getRow(0).getCell(i).setCellStyle(null);
            } catch (NullPointerException ex) {
                throw new NullPointerException("Either incorrect # colors entered OR colors NOT SET.");
            }//from   w  w  w . ja v  a  2 s .com
        }

        //Set-up rowIterator and get Row
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //Set-up cellIterator and get Cell
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                //Null-Check for Cell
                if (cell != null) {
                    //Get the Cell Style, Null-Check for Cell Style
                    XSSFCellStyle currCellStyle = (XSSFCellStyle) cell.getCellStyle();
                    if (currCellStyle != null) {
                        //Get the fillForeground color
                        XSSFColor fgColor = currCellStyle.getFillForegroundXSSFColor();
                        //cycle through ArrayList and compare if any of the colors listed matches
                        for (XSSFColor col : listOfColors) {
                            if (col.equals(fgColor)) {
                                cell.setCellStyle(userDefinedCS);
                            }
                        }
                    }
                }

            }
        }
    }

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }//from   ww  w .ja va  2 s.c om
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:coverageqc.functions.MyExcelEditor.java

private static XSSFCellStyle getDefaultCellStyle(XSSFRow currentRow) {

    XSSFCellStyle cellStyle = currentRow.getSheet().getWorkbook().createCellStyle();

    cellStyle.setWrapText(true);//from   w w w . jav  a2 s.c o m
    cellStyle.setBorderBottom(cellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
    cellStyle.setBorderRight(cellStyle.BORDER_THIN);
    cellStyle.setBorderTop(cellStyle.BORDER_THIN);
    XSSFFont myFont = currentRow.getSheet().getWorkbook().createFont();
    myFont.setFontHeight(9);
    cellStyle.setFont(myFont);
    cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(new XSSFColor(Color.white));
    return cellStyle;
}