jxl应用事例

实例中主要目的是解析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;
    }