自已写的excel报表生成

 

 

在ACTION类中加入方法:

public ActionForward equipExl(ActionMapping mapping, ActionForm form,

           HttpServletRequest request, HttpServletResponse response)

           throws IOException {

       List<Wrtcontract> list = wrtEquipReportManager.getList(request);

 

       String fname = "equipInfo";// Excel文件名

 

       OutputStream os = null;

       os = response.getOutputStream();// 取得输出流

 

       response.reset();// 清空输出流

 

       response.setHeader("Content-disposition", "attachment; filename="

              + fname + ".xls");// 设定输出文件头

 

       response.setContentType("application/msexcel");// 定义输出类型

       try {

           wrtEquipReportManager.createEquipExcel(os, request);

 

       } catch (Exception e) {

           System.out.println(e);

       }

 

       return null;

    }

 

 

 

在Manager类中加:

public void createEquipExcel(OutputStream os, HttpServletRequest request) {

this.TableHead(os, request);

 

    }

 

    public void TableHead(OutputStream os, HttpServletRequest request) {

 

       try {

           WritableWorkbook workbook = Workbook.createWorkbook(os);

           WritableSheet wsheet = workbook.createSheet("sheet1", 0);

           WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,

                  WritableFont.BOLD);

           WritableCellFormat wcf = new WritableCellFormat(wf);

           wcf.setAlignment(Alignment.CENTRE);

           Label label = null;

           int row = 4;

           int col = 40;

           String[][] head = new String[row][col];

           head[0] = new String[] { "", "自动柜员机", "", "", "", "", "", "", "",

                  "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",

                  "", "", "", "", "", "", "", "", "", "", "", "", "", "",

                  "自动柜员机", "TVM", };

           head[1] = new String[] { "", "停用", "营运", "试用", "", "", "", "", "",

                  "", "", "", "", "", "", "试用", "销售", "", "", "", "", "", "",

                  "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",

                  "销售", "", };

           head[2] = new String[] { "", "", "", "已签保", "保内不足一月", "", "", "",

                  "保内不足一月", "全月保内", "", "", "", "", "全月保内", "总计", "已签保",

                  "已过保", "", "已过保", "(从未签过保)保内不足一个月(当月出保)", "", "", "",

                  "(从未签过保)保内不足一个月(当月出保)", "保内", "保内-全月保内(当月出机)", "", "", "",

                  "", "保内-全月保内(当月出机)", "保内-全月在保", "", "", "", "", "保内-全月在保",

                  "总计", "", };

           head[3] = new String[] { "", "停用", "营运", "已签保", "ATM", "CRS/BCR",

                  "查询机", "单款存机", "总计", "ATM", "CRS/BCR", "查询机", "单款存机",

                  "售电机", "总计", "总计", "已签保", "曾经签过保", "从未签过保", "总计", "ATM",

                  "CRS/BCR", "查询机", "单款存机", "总计", "保内", "ATM", "CRS/BCR",

                  "查询机", "单款存机", "售电机", "总计", "ATM", "CRS/BCR", "查询机",

                  "单款存机", "售电机", "总计", "总计", "TVM" };

 

           // 列出表头

           for (int i = 0; i < row; i++) {

              for (int j = 0; j < col; j++) {

                  label = new Label(j, i, head[i][j], wcf);

                  wsheet.addCell(label);

 

              }

           }

           // 合并单元格

           for (int i = 0; i < row; i++) {

 

              for (int j = 0; j < col; j++) {

 

                  // 合并相同内容的行

                  for (int x = j + 1; x < col; x++) {

                     // 判断从该单元开始到这行的后面是否有相同内容的单元

                     if (head[i][j].toString().equals(head[i][x])) {

 

       // 判断从该单元开始到这行的后面的是否有其他不同内容的单元,如果内容是""的话,就合并,否的话就不执行合并单元

 

                         int a = j + 1;

                         for (; a <= x; a++) {

                            if (!head[i][a].equals("")) {

                                break;

 

                            }

 

                         }

                         if (a == x) {

                            wsheet.mergeCells(j, i, x, i);

                         }

                     }

                  }

                  // 合并相同内容的列

                  for (int y = i + 1; y < row; y++) {

                     // 判断从该单元相同的列的下面是否有相同内容的单元

                     if (head[i][j].toString().equals(head[y][j])) {

 

    // 判断从该单元开始到这列的下面的是否有其他不同内容的单元,如果内容是""的话,就合并,否的话就不执行合并单元

                         int b = i + 1;

                         for (; b <= y; b++) {

                            if (!head[b][j].equals("")) {

                                break;

 

                            }

 

                         }

                         if (b == y) {

                            wsheet.mergeCells(j, i, j, y);

                         }

                     }

 

                  }

              }

 

           }

 

           workbook.write();

           workbook.close();

           os.close();

       } catch (RowsExceededException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (WriteException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       } catch (IOException e) {

           // TODO Auto-generated catch block

           e.printStackTrace();

       }

 

    }