java jxl导出数据，单元格加上颜色

我就是我 2022-12-04 08:50 148阅读 0赞

> 某个需求导出的excel数据有等级，需要加上等级颜色

*  颜色转换工具类
    
        public class ColourUtil { 
        
        
          /** * 将颜色编码转为Colour * * @param strColor 颜色编码 * @return Colour * @author dyc * date: 2020/9/7 11:17 */
          public static Colour getNearestColour(String strColor) { 
              Color cl = Color.decode(strColor);
              Colour color = null;
              Colour[] colors = Colour.getAllColours();
              if ((colors != null) && (colors.length > 0)) { 
                  int[] rgb = null;
                  int diff = 0;
                  int minDiff = 999;
                  for (Colour crtColor:colors) { 
                      rgb = new int[3];
                      rgb[0] = crtColor.getDefaultRGB().getRed();
                      rgb[1] = crtColor.getDefaultRGB().getGreen();
                      rgb[2] = crtColor.getDefaultRGB().getBlue();
        
                      diff = Math.abs(rgb[0] - cl.getRed())
                              + Math.abs(rgb[1] - cl.getGreen())
                              + Math.abs(rgb[2] - cl.getBlue());
                      if (diff < minDiff) { 
                          minDiff = diff;
                          color = crtColor;
                      }
                  }
              }
              if (color == null)
                  color = Colour.BLACK;
              return color;
          }
        
          public static void main(String[] args){ 
              System.out.print(getNearestColour("#FFFFFF"));
          }
        }
 *  加颜色核心代码

if (!StringUtil.isInvalid(item.getRankColor())){ 
           // 设置自定义颜色，通过java.awt.Color中decode方法提取16进制颜色值
           WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                                   10, WritableFont.NO_BOLD);// 字体样式
    
             WritableCellFormat wcf1 = new WritableCellFormat(font);// 单元格样式
              wcf1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.GREY_50_PERCENT);
              wcf1.setBackground(ColourUtil.getNearestColour(item.getRankColor()));// 调用工具类，#FFFFFF 也可以直接赋值
                ws.addCell(new jxl.write.Label(14, count, "", wcf1));
       }

*  完整代码
    
        public void exportDeptEvaluateData(EvaDeptEvaluateQuery query, HttpServletResponse response, HttpServletRequest httpServletRequest) { 
              String excelName = "责任单位评价统计表";
              if (query.getObjType() == 2){ 
                  excelName = "区域评价统计表";
              }
              String startTime = TimeUtil.formatDateyyyyMMdd(query.getStartTime());
              String endTime = TimeUtil.formatDateyyyyMMdd(query.getEndTime());
              excelName = excelName+startTime+"至"+endTime;
              List<EvaDeptEvaluateFcgVO> list = calculateDeptEvaluateData(query);
              if (list != null && !list.isEmpty()) { 
                  String excel = excelName +".xls";
                  try { 
                      response.reset();
                      response.setCharacterEncoding("utf-8");
                      response.setContentType("application/vnd.ms-excel");
                      response.setHeader("Content-Disposition", "attachment;fileName=" + KeHttpRequestUtil.fileNameEncoding(excel,httpServletRequest));
                      WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream());
                      jxl.write.Label lb = null;
                      jxl.write.WritableSheet ws = wwb.createSheet("第1页", 0);// 工作薄
                      jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);// 样式
                      jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
                      wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                      ws.mergeCells(0, 0, 8, 0);// 合并每一行的1-9列
                      lb = new jxl.write.Label(0, 0, excelName, wcfF);
                      wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);// 样式
                      wcfF = new jxl.write.WritableCellFormat(wf);
                      wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 设置为居中
                      ws.addCell(lb);
                      String[] title = { "责任单位", "应处置数", "按时处置数","超时处置数", "返工处置数", "处置率", "得分", "督办处置数",
                              "督办未处置数","督办整改率", "得分","工作量考核得分","专项扣分","综合得分","等级颜色"};
                      for (int i = 0; i < title.length; i++) { // 设置标题
                          ws.setColumnView(i, 20);// 设置列的宽度
                          lb = new jxl.write.Label(i, 1, title[i], wcfF);
                          ws.addCell(lb);
                      }
                      int count = 2;
                      for (EvaDeptEvaluateFcgVO item : list) { // 数据
                          lb = new jxl.write.Label(0, count, item.getObjName());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(1, count, item.getShouldDisposedNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(2, count, item.getOntimeDisposedNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(3, count, item.getOvertimeDisposedNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(4, count, item.getReworkNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(5, count, item.getDisposedPercent().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(6, count, item.getDisposedScore().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(7, count, item.getShouldDisposedNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(8, count, item.getSuperviseUndisposedNum().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(9, count, item.getSuperviseAbarbeitungRate().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(10, count, item.getSuperviseScore().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(11, count, item.getWorkloadScore().toString());
                          ws.addCell(lb);
                          lb = new jxl.write.Label(12, count, String.valueOf(item.getSpecialPoint()));
                          ws.addCell(lb);
                          lb = new jxl.write.Label(13, count, item.getTotalScore().toString());
                          ws.addCell(lb);
        
                          if (!StringUtil.isInvalid(item.getRankColor())){ 
                              // 设置自定义颜色，通过java.awt.Color中decode方法提取16进制颜色值
                              WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                                      10, WritableFont.NO_BOLD);// 字体样式
        
                              WritableCellFormat wcf1 = new WritableCellFormat(font);// 单元格样式
                              wcf1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN,jxl.format.Colour.GREY_50_PERCENT);
                              wcf1.setBackground(ColourUtil.getNearestColour(item.getRankColor()));// #FFFFFF
                              ws.addCell(new jxl.write.Label(14, count, "", wcf1));
                          }
        
                          count++;
                      }
                      // 写入Exel工作表
                      wwb.write();
                      response.getOutputStream().flush();
                      // 关闭Excel工作薄对象
                      wwb.close();
                  } catch (Exception e) { 
                      e.printStackTrace();
                  }
              }
        
          }
 *  效果图  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/e6fdc687b4604904b52897ed6f08aa42.png