实例中主要目的是解析jxl使用流程以及jxl绘制Excel的写法思路，代码掩去了项目中的真实数据，请根据需求酌情修改，如果有帮助到有需要的人，不胜欢喜。
Dao层为查询数据库,返回list数据，此处省略。
==============service
public void exportExcel(HttpServletResponse response){ try{ //列坐标 int columIndex[] = {0,1,2,3,4,5,6,}; //每一列宽度 int colwidth[] = {10,10,10,10,10,10}; //每一列字段key String[] dataKey = {"test1","test2","test3","test4","test5","test6"}; //列表数据 List<Map<String,Object>> dataList = tableTestDao.findTableData(); //合计每一列的key String[] countKey = {"test1","test2","test3","test4","test5","test6"}; //导出execl tableExcelService.exportExcel( FILE_NAME, response, "测试表格", columIndex, colwidth, dataKey, dataList, countKey, "three" ); }catch (Exception e) { throw new BusinessException(e, ExceptionEnum.NULL, e.getMessage()); } }
=================TableExcelService工具类方法(1)
/** * 导出excel * @param fileName文件名 * @param response * @param title标题名 * @param columIndex列坐标 * @param colwidth每一列宽度 * @param dataKey每一列字段key * @param dataList列表数据 * @throws Exception */ public void exportExcel(String fileName,HttpServletResponse response,String title, int[] columIndex,int[] colwidth,String[] dataKey,List<Map<String,Object>> dataList, String falg ) throws Exception{ OutputStream os=null; WritableWorkbook workbook=null; try{ os=response.getOutputStream(); fileName=URLEncoder.encode(fileName,"GB2312"); fileName=URLDecoder.decode(fileName, "ISO8859_1"); response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls"); response.setContentType("application/msexcel"); workbook = Workbook.createWorkbook(os); // 自定义的颜色 Color color = Color.decode("#CCFFCC"); workbook.setColourRGB(Colour.GREEN, color.getRed(),color.getGreen(), color.getBlue()); //创建excel第一个sheet页 WritableSheet sheet = workbook.createSheet("Sheet1", 0); //创建标题和表头 if("three".equals( falg )||"four".equals( falg )){ sheet = createTableHead(sheet,title,columIndex,colwidth); }else if("one".equals( falg )){ sheet = createTableHeadOne(sheet,title,columIndex,colwidth); }else if("two".equals( falg )){ sheet = createTableHeadTwo(sheet,title,columIndex,colwidth); } //表格样式 WritableCellFormat tdWcfF = new WritableCellFormat(); tdWcfF.setBorder(Border.ALL, BorderLineStyle.THIN); tdWcfF.setAlignment(Alignment.CENTRE); tdWcfF.setVerticalAlignment(VerticalAlignment.CENTRE); //填入列表数据 int startRow = 4;//从第五行开始填入值 Map<String,Object> dataMap = null; Object obj=null; for(int row = 0;row <dataList.size();row++){ dataMap = dataList.get( row ); for(int col=0;col<dataKey.length;col++){ obj = dataMap.get(dataKey[col]); sheet.addCell(new Label(col,row+startRow,obj==null?"0":obj.toString(),tdWcfF)); } } //合计 if("three".equals( falg )||"four".equals( falg )){ int numStartRow = startRow+dataList.size();//到第几行了 sheet.mergeCells(0, numStartRow, 1, numStartRow); sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF)); for(int col=0;col<countKey.length;col++){ obj = countMap.get(countKey[col]); sheet.addCell(new Label(col+2,numStartRow,obj==null?"0":obj.toString(),tdWcfF)); } }else if("one".equals( falg )){ int numStartRow = startRow+dataList.size();//到第几行了 sheet.mergeCells(0, numStartRow, 0, numStartRow); sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF)); for(int col=0;col<countKey.length;col++){ obj = countMap.get(countKey[col]); sheet.addCell(new Label(col+1,numStartRow,obj==null?"0":obj.toString(),tdWcfF)); } }else if("two".equals( falg )){ int numStartRow = startRow+dataList.size();//到第几行了 sheet.mergeCells(0, numStartRow, 1, numStartRow); sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF)); for(int col=0;col<countKey.length;col++){ obj = countMap.get(countKey[col]); sheet.addCell(new Label(col+2,numStartRow,obj==null?"0":obj.toString(),tdWcfF)); } } workbook.write(); }catch (Exception e) { throw new RuntimeException(e.getMessage(),e); }finally{ if(workbook != null) { workbook.close(); workbook=null; } if(os != null) { os.flush(); os.close(); os=null; } } }
===========TableExcelService工具类方法(2)
/** * 创建标题和表头 * @param sheet * @param title * @return * @throws Exception */ private WritableSheet createTableHead(WritableSheet sheet,String title, int[] columIndex,int[] colwidth) throws Exception{ /*****格式设置******/ //标题 WritableCellFormat titleWcfF = new WritableCellFormat(); //边线细线 titleWcfF.setBorder(Border.ALL, BorderLineStyle.THIN); //水平居中 titleWcfF.setAlignment(Alignment.CENTRE); //垂直居中 titleWcfF.setVerticalAlignment(VerticalAlignment.CENTRE); //设置显示的字体样式，字体，字号，是否粗体，字体颜色 titleWcfF.setFont(new WritableFont(WritableFont.createFont("楷体_GB2312"),14,WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK)); //表头 WritableCellFormat thWcfF = new WritableCellFormat(); thWcfF.setBorder(Border.ALL, BorderLineStyle.THIN); thWcfF.setAlignment(Alignment.CENTRE); thWcfF.setVerticalAlignment(VerticalAlignment.CENTRE); thWcfF.setBackground(jxl.format.Colour.GREEN); //创建标题,列，行，到第19列，到第0行 sheet.mergeCells(0, 0, 19, 0); sheet.addCell(new Label(0,0,title,titleWcfF)); //表头创建 sheet.mergeCells(0, 1, 1, 3);//名称 sheet.addCell(new Label(0,1,"测试1",thWcfF)); //业务数（单位：个）列,行,到第19列，到第1行 sheet.mergeCells(2, 1, 19, 1); sheet.addCell(new Label(2,1,"测试2",thWcfF)); sheet.mergeCells(2, 2, 10, 2); sheet.addCell(new Label(2,2,"测试3",thWcfF)); sheet.mergeCells(11, 2, 19, 2); sheet.addCell(new Label(11,2,"测试4",thWcfF)); sheet.mergeCells(2, 3, 3, 3); sheet.addCell(new Label(2, 3,"测试5",thWcfF)); sheet.mergeCells(4, 3, 5, 3); sheet.addCell(new Label(4, 3,"测试6",thWcfF)); //单个单元格表头第6列，第3行,单元格内容,单元格样式 sheet.addCell(new Label(6, 3,"测试7",thWcfF)); sheet.addCell(new Label(7, 3,"测试8",thWcfF)); sheet.addCell(new Label(8, 3,"测试9",thWcfF)); sheet.addCell(new Label(9, 3,"测试10",thWcfF)); sheet.addCell(new Label(10, 3,"测试11",thWcfF)); sheet.mergeCells(11, 3, 12, 3); //设置列宽 for(int i=0;i<columIndex.length;i++){ sheet.setColumnView(columIndex[i], colwidth[i]); } return sheet; }