某个需求导出的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(); } } }
- 效果图