自已写的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();

       }

 

    }