【Java】------- Java execl 导出列表表格中添加背景色主要代码

骑猪看日落 2021-12-10 07:31 645阅读 0赞

for (int i = 0; i < dataList.size(); i++) {
                    HSSFRow dataRow = sheet.createRow(1 + i);
                    Map<String, Object> data = dataList.get(i);
                    HSSFCellStyle style = wb.createCellStyle();
                    for (int j = 0; j < columnList.size(); j++) {
                        ColSetting column = columnList.get(j);
    
    
    
                        //边框
                        style.setBorderBottom(BorderStyle.THIN); //下边框
                        style.setBorderLeft(BorderStyle.THIN);//左边框
                        style.setBorderTop(BorderStyle.THIN);//上边框
                        style.setBorderRight(BorderStyle.THIN);//右边框
    
                        HSSFCell cell = dataRow.createCell(j);
                        //取色板
                        if(column.getFileCol().equals("风险等级") || column.getFileCol().equals("隐患等级")){
    
                            HSSFPalette palette = wb.getCustomPalette();
                            //默认颜色
                            style.setFillForegroundColor(HSSFColor.WHITE.index);
    
                            String dbname=data.get(column.getDbCol()).toString();
                            String color="";
                            for(int p=0;p<list.size();p++){
                                if(list.get(p).get("CNAME").equals(dbname)){
                                    color=list.get(p).get("COLOR").toString();
                                }
                            }
                            String c;
                            if(color.equals("")){
                                c = "#f1c40f";
                            }else{
                                c=color;
                            }
    
                            if (StringUtils.isNotBlank(c)) {
                                //获得RGB三种 #e6b8e6
                                String r = c.substring(1, 3);
                                String g = c.substring(3, 5);
                                String b = c.substring(5, 7);
    
                                int r2 = Integer.parseInt(r, 16);
                                int g2 = Integer.parseInt(g, 16);
                                int b2 = Integer.parseInt(b, 16);
                                //find方法会出现找不到颜色的异常，所以
                                HSSFColor hssfColor = palette.findSimilarColor(r2, g2, b2);
                                //设置颜色
                                style.setFillForegroundColor(hssfColor.getIndex());
                            }
                            //填充颜色
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            //  HSSFCell cell = dataRow.createCell(2);
                            cell.setCellStyle(style);
    
                        }
    
    
    
                        __Str= getValue(data, column.getDbCol());
    
                        if (__Str!=null)
                        {
                            if (tool.ConvertHelper.ToString(__Str).toLowerCase().equals("true")) {
                                __Str = "是";
                            }
    
                            if (tool.ConvertHelper.ToString(__Str).toLowerCase().equals("false")) {
                                __Str = "否";
                            }
    
                        }
    
    
                        if (column.getisAutoWidth())
                        {
                            if (__Str!=null)
                            {
                                __size = tool.ConvertHelper.ToString(__Str).length();
    
                                if (column.getmaxCharSize() < __size) {
    
                                    column.setmaxCharSize(__size);
                                }
                            }
                        }
                        if (__Str instanceof Double)
                        {
                            cell.setCellValue(tool.ConvertHelper.ToDouble(__Str));
    
                        }else if (__Str instanceof Float)
                        {
                            cell.setCellValue(tool.ConvertHelper.ToDouble(__Str));
    
                        } else if (__Str instanceof Number)
                        {
                            cell.setCellValue(tool.ConvertHelper.ToDouble(__Str));
                        }
                        else {
                            cell.setCellValue(tool.ConvertHelper.ToString(__Str));
                        }
    
    
    
                        ;
                        // cell.setCellStyle(cellStyle);// 设置单元格样式
                    }
                }
    
    
    
                for (int j = 0; j < columnList.size(); j++)
                {
                    ColSetting column = columnList.get(j);
    
                    int __onecharSize=600;
    
                    if (column.getisAutoWidth())
                    {
                        int __EVAL_SIZE=(int)(column.getmaxCharSize()*__onecharSize);
    
                        if (__EVAL_SIZE<__onecharSize*6)
                            __EVAL_SIZE=__onecharSize*6;
    
    
                        if (__EVAL_SIZE>__onecharSize*20)
                            __EVAL_SIZE=__onecharSize*20;
    
                        sheet.setColumnWidth(j,__EVAL_SIZE); // 调整第一列宽度
                       // sheet.autoSizeColumn((short) j); // 调整第一列宽度
                    }
                    else
                    {
                        sheet.setColumnWidth(j,column.getwidth()); // 调整第一列宽度
                    }
    
                }