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.wantdo.stat.excel.poi_src.SSPerformanceTest.java

License:Apache License

static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/*  w  ww.j a  va  2 s  . c  o m*/

    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 14);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(headerFont);
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styles.put("header", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 12);
    monthFont.setColor(IndexedColors.RED.getIndex());
    monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    styles.put("red-bold", style);

    String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" };
    for (String fmt : nfmt) {
        style = wb.createCellStyle();
        style.setDataFormat(wb.createDataFormat().getFormat(fmt));
        styles.put(fmt, style);
    }

    return styles;
}

From source file:com.webbfontaine.valuewebb.report.utils.cellStyles.CellStyleUtils.java

License:Open Source License

public static CellStyle cellStyle(Workbook workBook, CellType cellType) {
    CellStyle cellStyle = workBook.createCellStyle();

    if (!Color.NO_COLOR.equals(cellType.getColor())) {
        Font font = workBook.createFont();
        font.setFontName("Arial");
        font.setColor(cellType.getColor().index());
        cellStyle.setFont(font);//from  ww  w.j av  a  2s. co  m
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getHorizontalAlignment())) {
        cellStyle.setAlignment(cellType.getHorizontalAlignment().index());
    }

    if (!Alignment.NO_ALIGNMENT.equals(cellType.getVerticalAlignment())) {
        cellStyle.setVerticalAlignment(cellType.getVerticalAlignment().index());
    }

    if (!Format.NO_FORMAT.equals(cellType.getFormat())) {
        CreationHelper creationHelper = workBook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(cellType.getFormat().text()));
    }

    setCellBorders(cellStyle);
    return cellStyle;
}

From source file:com.ykun.commons.utils.excel.ExcelUtils.java

License:Apache License

/**
 * ??// www  .java  2s. c  o m
 *
 * @param workbook Workbook
 * @return CellStyle
 */
private static CellStyle createHeaderStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    Font headerFont = workbook.createFont();
    headerFont.setBold(Boolean.TRUE);
    headerFont.setColor(IndexedColors.BLACK.getIndex());
    style.setFont(headerFont);
    return style;
}

From source file:com.ykun.commons.utils.excel.ExcelUtils.java

License:Apache License

/**
 * ????//w  w w  .ja  v  a2s  . c o m
 *
 * @param workbook
 * @return HSSFCellStyle
 */
private static CellStyle createNormalStyle(Workbook workbook) {
    return workbook.createCellStyle();
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?// w  w  w.  j  a  v  a  2  s. c om
 * @param FileName ???????excel
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (FileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // jsonkey
        String[] cellNames;
        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);
        // ???
        cellNames = new String[curCellNum];
        // ????JSONkey
        for (int m = 0; m < curCellNum; m++) {
            Cell cell = fisrtRow.getCell(m);
            // ?
            cell.setCellStyle(cellStyle);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            // ?
            cellNames[m] = getCellValue(cell);
        }
        for (String s : cellNames) {
            System.out.print("" + i + " sheet " + s + ",");
        }
        System.out.println();

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);

                cell.setCellStyle(cellStyle);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                // ???
                rowMap.put(cellNames[k], getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel???
 * @param inputStream ?urlurlinput?// w  ww. j  a v a 2s  . c o m
 * @param FileName ???????excel
 * @param headers list,String-->Arrays.asList();
 * @return Map  HashMapExcelsheet?sheetkeysheet?json?value
 * @throws IOException
 */
public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers)
        throws IOException {

    System.out.println("excel2json....");

    // map
    Map<String, String> excelMap = new LinkedHashMap<>();

    // Excel??Excel
    CellStyle cellStyle;
    // ?Excel?
    Workbook wb;
    // 2007??Workbook?CellStyle
    if (fileName.endsWith("xlsx")) {
        System.out.println("2007?  xlsx");
        wb = new XSSFWorkbook(inputStream);
        XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    } else {
        System.out.println("2007  xls");
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        wb = new HSSFWorkbook(fs);
        HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat();
        cellStyle = wb.createCellStyle();
        // Excel?
        cellStyle.setDataFormat(dataFormat.getFormat("@"));
    }

    // sheet
    int sheetsCounts = wb.getNumberOfSheets();
    // ???sheet
    for (int i = 0; i < sheetsCounts; i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println("" + i + "sheet:" + sheet.toString());

        // sheetList
        List list = new LinkedList();

        // ?key
        Row fisrtRow = sheet.getRow(0);
        // sheet
        if (null == fisrtRow) {
            continue;
        }
        // 
        int curCellNum = fisrtRow.getLastCellNum();
        System.out.println("" + curCellNum);

        // ???
        int rowNum = sheet.getLastRowNum();
        System.out.println(" " + rowNum + " ");
        for (int j = 1; j < rowNum; j++) {
            // ?Map
            LinkedHashMap rowMap = new LinkedHashMap();
            // ??
            Row row = sheet.getRow(j);
            int cellNum = row.getLastCellNum();
            // ???
            for (int k = 0; k < cellNum; k++) {
                Cell cell = row.getCell(k);
                // ???
                rowMap.put(headers.get(k), getCellValue(cell));
            }
            // ??List
            list.add(rowMap);
        }
        // sheet??keyListjson?Value
        excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list));
    }

    System.out.println("excel2json?....");

    return excelMap;
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

public static CellStyle createHeaderStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    // ?/*w ww. j  a  va2 s .c o  m*/
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    // ?
    Font font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    // ??
    style.setFont(font);
    return style;
}

From source file:com.zlfun.framework.excel.ExcelUtils.java

public static CellStyle createBodyStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    // ?// w  ww  . j  av  a  2  s .c  om
    style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    // ??
    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    // ??
    style.setFont(font);
    return style;
}

From source file:com.zxy.commons.poi.excel.ExcelUtils.java

License:Apache License

/**
 * export excel/*from   w w  w .  ja  va  2 s  .  c  o m*/
 * 
 * @param sheetName sheet name
 * @param table table
 * @return Workbook
*/
@SuppressWarnings("PMD.ShortVariable")
private static Workbook exportExcel(String sheetName, Table<Integer, String, String> table) {
    Set<Integer> tableRows = table.rowKeySet();
    Set<String> tableColumns = table.columnKeySet();
    // excel
    Workbook wb = new HSSFWorkbook();

    // sheet??
    Sheet sheet = wb.createSheet(sheetName);
    // ???n?
    /*for (int i = 0; i < keys.length; i++) {
    sheet.setColumnWidth((short) i, (short) (35.7 * 150));
    }*/

    // 
    Row row = sheet.createRow((short) 0);

    // ???
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();

    // ?
    Font f1 = wb.createFont();
    Font f2 = wb.createFont();

    // ????
    f1.setFontHeightInPoints((short) 10);
    f1.setColor(IndexedColors.BLACK.getIndex());
    f1.setBold(true);

    // ??
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.BLACK.getIndex());

    // Font f3=wb.createFont();
    // f3.setFontHeightInPoints((short) 10);
    // f3.setColor(IndexedColors.RED.getIndex());

    // ?????
    cs.setFont(f1);
    cs.setBorderLeft(BorderStyle.THIN);
    cs.setBorderRight(BorderStyle.THIN);
    cs.setBorderTop(BorderStyle.THIN);
    cs.setBorderBottom(BorderStyle.THIN);
    cs.setAlignment(HorizontalAlignment.CENTER);

    // ???
    cs2.setFont(f2);
    cs2.setBorderLeft(BorderStyle.THIN);
    cs2.setBorderRight(BorderStyle.THIN);
    cs2.setBorderTop(BorderStyle.THIN);
    cs2.setBorderBottom(BorderStyle.THIN);
    cs2.setAlignment(HorizontalAlignment.CENTER);
    // ??
    int i = 0;
    for (String tableColumn : tableColumns) {
        Cell cell = row.createCell(i);
        cell.setCellValue(tableColumn);
        cell.setCellStyle(cs);
        i++;
    }
    // ??
    for (Integer tableRow : tableRows) {
        // Row ,Cell  , Row  Cell 0
        // sheet
        checkArgument(tableRow > 0, "Row index must be greater than zero!");
        Row row1 = sheet.createRow(tableRow);
        // row
        Map<String, String> item = table.row(tableRow);
        int j = 0;
        for (Map.Entry<String, String> entry : item.entrySet()) {
            //            for(String v:item.keySet()){
            //                System.out.println(tableRow + "-" + v + "-" + item.get(v));
            Cell cell = row1.createCell(j);
            cell.setCellValue(entry.getValue());
            cell.setCellStyle(cs2);
            j++;
        }
    }
    return wb;
}

From source file:Compras.altaCompras.java

   private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed
    // TODO add your handling code here:
    javax.swing.JFileChooser jF1= new javax.swing.JFileChooser();
    jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    String ruta = null;/*from w  ww. j  a  va2  s.  c  o m*/
    if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION)
    {
        ruta = jF1.getSelectedFile().getAbsolutePath();
        File archivoXLS = new File(ruta+".xls");
        try
        {
            if(archivoXLS.exists())
            archivoXLS.delete();
            archivoXLS.createNewFile();
            Workbook libro = new HSSFWorkbook();
            FileOutputStream archivo = new FileOutputStream(archivoXLS);
            Sheet hoja = libro.createSheet("Muestras");
                
            org.apache.poi.ss.usermodel.Font font10 = libro.createFont();
            font10.setFontHeightInPoints((short)10);
            font10.setFontName("Arial");
            font10.setItalic(false);
            font10.setBold(false);
            font10.setColor(new HSSFColor.YELLOW().getIndex());
                
                
            CellStyle titulo = libro.createCellStyle();
            CellStyle contenido = libro.createCellStyle();
            CellStyle firma = libro.createCellStyle();
            CellStyle costado = libro.createCellStyle();
            CellStyle derecha = libro.createCellStyle();
            CellStyle derecha_borde = libro.createCellStyle();
                
            titulo.setFont(font10);
            titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex());
            titulo.setAlignment(CellStyle.ALIGN_CENTER);
                
            contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderTop(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderRight(HSSFCellStyle.BORDER_THIN);
            contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT);
                
            derecha.setAlignment(CellStyle.ALIGN_RIGHT);
                
            firma.setBorderTop(HSSFCellStyle.BORDER_THIN);
            firma.setAlignment(CellStyle.ALIGN_CENTER);
                
            //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                
            hoja.setColumnWidth(0, 3000);
            hoja.setColumnWidth(2, 3000);
            hoja.setColumnWidth(3, 8000);
            hoja.setColumnWidth(4, 5000);
            try
            {
                InputStream is = new FileInputStream("imagenes/grande300115.jpg");
                byte[] bytes = IOUtils.toByteArray(is);
                int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                is.close();
                CreationHelper helper = libro.getCreationHelper();
                Drawing drawing = hoja.createDrawingPatriarch();
                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(0);
                anchor.setRow1(0);
                Picture pict = drawing.createPicture(anchor, pictureIdx);
                pict.resize();
            }catch(Exception e){e.printStackTrace();}
            Row r7 = hoja.createRow(7);
            r7.createCell(0).setCellValue("ORDEN:");
            r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden()));
            r7.createCell(2).setCellValue("Hrs. Entrega:");
            r7.createCell(3).setCellValue("");
            hoja.addMergedRegion(new CellRangeAddress(7,7,4,7));
            r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS");
            r7.getCell(4).setCellStyle(derecha);
                
            Date fecha = new Date();
            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS
            String valor=dateFormat.format(fecha);
            Row r8 = hoja.createRow(8);
            r8.createCell(0).setCellValue("Supervisor:");
            hoja.addMergedRegion(new CellRangeAddress(8,8,1,3));
            r8.createCell(1).setCellValue("");
            r8.createCell(4).setCellValue("F. Elaboracin:");
            r8.createCell(5).setCellValue(valor);
                
            Row r9 = hoja.createRow(9);
            r9.createCell(0).setCellValue("Comprador:");
            hoja.addMergedRegion(new CellRangeAddress(9,9,1,3));
            r9.createCell(1).setCellValue("");
            r9.createCell(4).setCellValue("F. Entrega:");
            r9.createCell(5).setCellValue("");
                
            Row r10 = hoja.createRow(10);
            r10.createCell(0).setCellValue("Cantidad");
            r10.getCell(0).setCellStyle(titulo);
            hoja.addMergedRegion(new CellRangeAddress(10,10,1,7));
            r10.createCell(1).setCellValue("Descripcin");
            r10.getCell(1).setCellStyle(titulo);
                
            int ren=11;
            for(int r=0;r<(t_datos.getRowCount());r++)
            {
                if((boolean)t_titulos.getValueAt(r, 3)==true)
                {
                    Row fila = hoja.createRow(ren);
                    Cell celda = fila.createCell(0);
                    celda.setCellStyle(derecha_borde);
                    Cell celda1 = fila.createCell(1);
                    celda1.setCellStyle(contenido);
                    fila.createCell(2).setCellStyle(contenido);
                    fila.createCell(3).setCellStyle(contenido);
                    fila.createCell(4).setCellStyle(contenido);
                    fila.createCell(5).setCellStyle(contenido);
                    fila.createCell(6).setCellStyle(contenido);
                    fila.createCell(7).setCellStyle(contenido);
                    //Cell celda8 = fila.createCell(8);
                    //celda8.setCellStyle(costado);
                    try{
                        celda.setCellValue(t_datos.getValueAt(r, 14).toString());
                        hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7));
                        celda1.setCellValue(t_titulos.getValueAt(r, 2).toString());
                        //celda8.setCellValue("");
                    }catch(Exception e){
                        celda.setCellValue("");
                    }
                    ren++;
                }
            }
                
            Row rx = hoja.createRow(ren+5);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2));
            rx.createCell(0).setCellValue("Recibe Muestras");
            rx.getCell(0).setCellStyle(firma);
            rx.createCell(1).setCellStyle(firma);
            rx.createCell(2).setCellStyle(firma);
            hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7));
            rx.createCell(5).setCellValue("Entrega Muestras");
            rx.getCell(5).setCellStyle(firma);
            rx.createCell(6).setCellStyle(firma);
            rx.createCell(7).setCellStyle(firma);
                
            libro.write(archivo);
            archivo.close();
            Desktop.getDesktop().open(archivoXLS);
        }catch(Exception e)
        {
            e.printStackTrace();
            JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto");
        }
    }
}