jxl报表2

这一段时间搞报表，头都搞晕了，最后还是整理了一下

在javascript里面导出Excel

这个是基本方法

function toExcel(){
var docWin=window.open("","_blank","left=-1,top=-1,width=100,height=100,resizable=no");
var AllData=document.getElementById("AllData");//"alldata"为一个表格或一个层(最好)的名字
docWin.document.write(AllData.innerHTML);
docWin.document.execCommand("SelectAll",false);
docWin.document.execCommand("Copy",false);
docWin.close();

var objExcelApp=new ActiveXObject("Excel.Application");
if (objExcelApp==null){
  alert("你的机器上没有安装Excel软件");
}else{
  var erl="";
  try{
      objExcelApp.WindowState = 1;
      objExcelApp.Visible = 1;
   var objWorkBook=objExcelApp.Workbooks.Add();
   erl="Create WorkBook";
   var objSheet=objWorkBook.ActiveSheet;
   erl="Get ActiveSheet";
   objSheet.Columns("A").columnwidth="5";
   objSheet.Columns("B").columnwidth="5";
   objSheet.Columns("C").columnwidth="15";
   objSheet.Columns("D").columnwidth="15";
   objSheet.Columns("E").columnwidth="35";
   objSheet.Columns("F").columnwidth="15";
   objSheet.Columns("G").columnwidth="5";
   objSheet.Columns("H").columnwidth="25";
   objSheet.Paste();
   erl="Copy";
     // 保存表格。
      //objSheet.SaveAs("C:\\TEST.XLS");
       // 用 Application 对象用 Quit 方法关闭 Excel。
       /objSheet.Application.Quit();

  }catch(e){
   alert("对Excel操作时出错了:"+erl);
  }
}
}

objSheet.cells.rang['A3','B3'].merge('true');可以合并两列
objSheet.cells.rang['A3','B3'].merge(False);可以合并两行



下面这些可以对Excel对象的属性进行操作


1.创建一个新Excel表格

    var XLObj = new ActiveXObject("Excel.Application");
    var xlBook = XLObj.Workbooks.Add;                         //新增工作簿
    var ExcelSheet = xlBook.Worksheets(1);                   //创建工作表

2.保存表格
    ExcelSheet.SaveAs("C:\\TEST.XLS");

3.使 Excel 通过 Application 对象可见
    ExcelSheet.Application.Visible = true;

4.打印
    xlBook.PrintOut;
    或者:
    ExcelSheet.PrintOut;

5.关闭
    xlBook.Close(savechanges=false);
    或者:
    ExcelSheet.Close(savechanges=false);

6.结束进程
    ExcelSheet.Application.Quit();
    或者:
    XLObj.Quit();
    XLObj=null;

7.页面设置
    ExcelSheet.ActiveSheet.PageSetup.LeftMargin= 2/0.035;         //页边距 左2厘米
    ExcelSheet.ActiveSheet.PageSetup.RightMargin = 3/0.035;      //页边距 右3厘米，
    ExcelSheet.ActiveSheet.PageSetup.TopMargin = 4/0.035;        //页边距 上4厘米，
    ExcelSheet.ActiveSheet.PageSetup.BottomMargin = 5/0.035;   //页边距 下5厘米
    ExcelSheet.ActiveSheet.PageSetup.HeaderMargin = 1/0.035;   //页边距 页眉1厘米
    ExcelSheet.ActiveSheet.PageSetup.FooterMargin = 2/0.035;    //页边距 页脚2厘米
    ExcelSheet.ActiveSheet.PageSetup.CenterHeader = "页眉中部内容";
    ExcelSheet.ActiveSheet.PageSetup.LeftHeader = "页眉左部内容";
    ExcelSheet.ActiveSheet.PageSetup.RightHeader = "页眉右部内容";
    ExcelSheet.ActiveSheet.PageSetup.CenterFooter = "页脚中部内容";
    ExcelSheet.ActiveSheet.PageSetup.LeftFooter = "页脚左部内容";
    ExcelSheet.ActiveSheet.PageSetup.RightFooter = "页脚右部内容";

8.对单元格操作，带*部分对于行，列，区域都有相应属性
    ExcelSheet.ActiveSheet.Cells(row,col).Value = "内容";                //设置单元格内容
    ExcelSheet.ActiveSheet.Cells(row,col).Borders.Weight = 1;        //设置单元格边框*()
    ExcelSheet.ActiveSheet.Cells(row,col).Interior.ColorIndex = 1;    //设置单元格底色*(1-黑色，
        2-白色，3-红色，4-绿色，5-蓝色，6-黄色，7-粉红色，8-天蓝色，9-酱土色..可以多做尝试)
    ExcelSheet.ActiveSheet.Cells(row,col).Interior.Pattern = 1;         //设置单元格背景样式*(1-无，
                                                     2-细网格，3-粗网格，4-斑点，5-横线，6-竖线..可以多做尝试)
    ExcelSheet.ActiveSheet.Cells(row,col).Font.ColorIndex = 1;        //设置字体颜色*(与上相同)
    ExcelSheet.ActiveSheet.Cells(row,col).Font.Size = 10;                //设置为10号字*
    ExcelSheet.ActiveSheet.Cells(row,col).Font.Name = "黑体";        //设置为黑体*
    ExcelSheet.ActiveSheet.Cells(row,col).Font.Italic = true;             //设置为斜体*
    ExcelSheet.ActiveSheet.Cells(row,col).Font.Bold = true;             //设置为粗体*
    ExcelSheet.ActiveSheet.Cells(row,col).ClearContents;                //清除内容*
    ExcelSheet.ActiveSheet.Cells(row,col).WrapText=true;               //设置为自动换行*
    ExcelSheet.ActiveSheet.Cells(row,col).HorizontalAlignment = 3;  //水平对齐方式枚举* (1-常规，
                                    2-靠左，3-居中，4-靠右，5-填充 6-两端对齐，7-跨列居中，8-分散对齐)
    ExcelSheet.ActiveSheet.Cells(row,col).VerticalAlignment = 2;      //垂直对齐方式枚举*(1-靠上，
                                                                                   2-居中，3-靠下，4-两端对齐，5-分散对齐)
    //行，列有相应操作:
    ExcelSheet.ActiveSheet.Rows(row).
    ExcelSheet.ActiveSheet.Columns(col).
    ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow).                   //如Rows("1:5")即1到5行
    ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol).                //如Columns("1:5")即1到5列
    //区域有相应操作:
    XLObj.Range(startcell+":"+endcell).Select;
        //如Range("A2:H8")即A列第2格至H列第8格的整个区域
    XLObj.Selection.
    //合并单元格
    XLObj.Range(startcell+":"+endcell).MergeCells = true;
        //如Range("A2:H8")即将A列第2格至H列第8格的整个区域合并为一个单元格
    或者:
    XLObj.Range("A2",XLObj.Cells(8,).MergeCells = true;

9.设置行高与列宽
    ExcelSheet.ActiveSheet.Columns(startcol+":"+endcol).ColumnWidth = 22;
        //设置从firstcol到stopcol列的宽度为22
    ExcelSheet.ActiveSheet.Rows(startrow+":"+endrow).RowHeight = 22;
        //设置从firstrow到stoprow行的宽度为22



这里有一个在网上搜索的小实例。

<HTML>  
  <BODY>   
  <SCRIPT   LANGUAGE="VBScript">  
  Function   CreateNamesArray()  
  '   Create   an   array   to   set   multiple   values   at   once.  
      Dim   saNames(5,   2)  
      saNames(0,   0)   =   "John"  
      saNames(0,   1)   =   "Smith"  
      saNames(1,   0)   =   "Tom"  
      saNames(1,   1)   =   "Brown"  
      saNames(2,   0)   =   "Sue"  
      saNames(2,   1)   =   "Thomas"  
      saNames(3,   0)   =   "Jane"  
      saNames(3,   1)   =   "Jones"  
      saNames(4,   0)   =   "Adam"  
      saNames(4,   1)   =   "Johnson"  
      CreateNamesArray   =   saNames  
  End   Function  
  </SCRIPT>  
   
  <SCRIPT   LANGUAGE="JScript">    
  function   AutomateExcel()  
  {  
        //   Start   Excel   and   get   Application   object.  
              var   oXL   =   new   ActiveXObject("Excel.Application");  
                 
              oXL.Visible   =   true;  
               
        //   Get   a   new   workbook.  
              var   oWB   =   oXL.Workbooks.Add();  
              var   oSheet   =   oWB.ActiveSheet;  
                     
        //   Add   table   headers   going   cell   by   cell.  
              oSheet.Cells(1,   1).Value   =   "First   Name";  
              oSheet.Cells(1,   2).Value   =   "Last   Name";  
              oSheet.Cells(1,   3).Value   =   "Full   Name";  
              oSheet.Cells(1,   4).Value   =   "Salary";  
                     
        //   Format   A1:D1   as   bold,   vertical   alignment   =   center.  
              oSheet.Range("A1",   "D1").Font.Bold   =   true;  
              oSheet.Range("A1",   "D1").VerticalAlignment   =     -4108;   //xlVAlignCenter  
                     
        //   Create   an   array   to   set   multiple   values   at   once.  
   
        //   Fill   A2:B6   with   an   array   of   values   (from   VBScript).  
              oSheet.Range("A2",   "B6").Value   =   CreateNamesArray();  
             
        //   Fill   C2:C6   with   a   relative   formula   (=A2   &   "   "   &   B2).  
              var   oRng   =   oSheet.Range("C2",   "C6");  
              oRng.Formula   =   "=A2   &   \"   \"   &   B2";  
                     
        //   Fill   D2:D6   with   a   formula(=RAND()*100000)   and   apply   format.  
              oRng   =   oSheet.Range("D2",   "D6");  
              oRng.Formula   =   "=RAND()*100000";  
              oRng.NumberFormat   =   "$0.00";  
                     
        //   AutoFit   columns   A:D.  
              oRng   =   oSheet.Range("A1",   "D1");  
              oRng.EntireColumn.AutoFit();  
                     
        //   Manipulate   a   variable   number   of   columns   for   Quarterly   Sales   Data.  
              DispalyQuarterlySales(oSheet);

     //保存到本地路径
     oSheet.SaveAs("C:\\TEST.XLS");
                   
        //   Make   sure   Excel   is   visible   and   give   the   user   control  
        //   of   Excel's   lifetime.  
 
              oXL.Visible   =   true;  
              oXL.UserControl   =   true;  
  }  
               
  function   DispalyQuarterlySales(oWS)  
  {  
              var   iNumQtrs,   sMsg,   iRet;  
   
          //   Number   of   quarters   to   display   data   for.  
              iNumQtrs   =   4;  
                                 
          //   Starting   at   E1,   fill   headers   for   the   number   of   columns   selected.  
              var   oResizeRange   =   oWS.Range("E1",   "E1").Resize(1,iNumQtrs);  
              oResizeRange.Formula   =   "=\"Q\"   &   COLUMN()-4   &   CHAR(10)   &   \"Sales\"";  
                     
          //   Change   the   Orientation   and   WrapText   properties   for   the   headers.  
              oResizeRange.Orientation   =   38;  
              oResizeRange.WrapText   =   true;  
                     
          //   Fill   the   interior   color   of   the   headers.  
              oResizeRange.Interior.ColorIndex   =   36;  
                     
          //   Fill   the   columns   with   a   formula   and   apply   a   number   format.  
              oResizeRange   =   oWS.Range("E2",   "E6").Resize(5,iNumQtrs);  
              oResizeRange.Formula   =   "=RAND()*100";  
              oResizeRange.NumberFormat   =   "$0.00";  
                     
          //   Apply   borders   to   the   Sales   data   and   headers.  
              oResizeRange   =   oWS.Range("E1",   "E6").Resize(6,iNumQtrs);  
              oResizeRange.Borders.Weight   =   2;     //   xlThin  
                     
          //   Add   a   Totals   formula   for   the   sales   data   and   apply   a   border.  
              oResizeRange   =   oWS.Range("E8",   "E8").Resize(1,iNumQtrs);  
              oResizeRange.Formula   =   "=SUM(E2:E6)";  
          //   9   =   xlEdgeBottom              
              oResizeRange.Borders(9).LineStyle   =   -4119;   //xlDouble  
              oResizeRange.Borders(9).Weight   =   4;   //xlThick  
                     
          //   Add   a   Chart   for   the   selected   data.  
              oResizeRange   =   oWS.Range("E2:E6").Resize(5,iNumQtrs);  
              var   oChart   =   oWS.Parent.Charts.Add();  
              oChart.ChartWizard(oResizeRange,   -4100,   null,   2);     //   -4100   =   xl3dColumn  
              oChart.SeriesCollection(1).XValues   =   oWS.Range("A2",   "A6");  
              for   (iRet   =   1;   iRet   <=   iNumQtrs;   iRet++)   {  
                    oChart.SeriesCollection(iRet).Name   =   "=\"Q"   +   iRet   +   "\"";  
              }  
              oChart.Location(2,   oWS.Name);   //   2   =   xlLocationAsObject  
                     
          //   Move   the   chart   so   as   not   to   cover   your   data.  
              oWS.Shapes("Chart   1").Top   =   oWS.Rows(10).Top;  
              oWS.Shapes("Chart   1").Left   =   oWS.Columns(2).Left;  
  }  
  </SCRIPT>  
  <P><INPUT   id=button1   type=button   value="创建Excel表格和柱状图"   onclick="AutomateExcel"></P>  
  </BODY>  
  </HTML>



这里是在java类里面的方法

public void generateXls()
    {
        try
        {
            /** *//** **********创建工作簿************ */
            WritableWorkbook workbook = Workbook.createWorkbook(new File("d:/test.xls"));
            /** *//** **********创建工作表************ */
            WritableSheet sheet = workbook.createSheet("工作表名称", 0);

            /** *//** *********设置列宽**************** */
            sheet.setColumnView(0, 15); // 第1列
            sheet.setColumnView(1, 18); // 第2列
            sheet.setColumnView(2, 13);
            sheet.setColumnView(3, 13);
            sheet.setColumnView(4, 15);
            sheet.setColumnView(5, 15);
            //设置行高
            sheet.setRowView(0, 600, false);
            sheet.setRowView(1, 400, false);
            sheet.setRowView(7, 400, false);
            //设置页边距
            sheet.getSettings().setRightMargin(0.5);
            //设置页脚
            sheet.setFooter("", "", "测试页脚");
            /** *//** ************设置单元格字体************** */
            //字体
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 14,
                    WritableFont.BOLD);
            WritableFont tableFont = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.NO_BOLD);
            WritableFont baodanFont = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.BOLD);

            /** *//** ************以下设置几种格式的单元格************ */
            // 用于标题
            WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
            wcf_title.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_title.setAlignment(Alignment.CENTRE); // 水平对齐
            wcf_title.setWrap(true); // 是否换行

            // 用于表格标题
            WritableCellFormat wcf_tabletitle = new WritableCellFormat(
                    tableFont);
            wcf_tabletitle.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            wcf_tabletitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_tabletitle.setAlignment(Alignment.CENTRE); // 水平对齐
            wcf_tabletitle.setWrap(true); // 是否换行

            // 用于正文左
            WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
            wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_left.setAlignment(Alignment.LEFT);
            wcf_left.setWrap(true); // 是否换行

            // 用于正文左
            WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
            wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_center.setAlignment(Alignment.CENTRE);
            wcf_center.setWrap(true); // 是否换行

            // 用于正文右
            WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
            wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_right.setAlignment(Alignment.RIGHT);
            wcf_right.setWrap(false); // 是否换行

            // 用于跨行
            WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
            wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
            wcf_merge.setAlignment(Alignment.LEFT);
            wcf_merge.setWrap(true); // 是否换行

            WritableCellFormat wcf_table = new WritableCellFormat(NormalFont);
            wcf_table.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
            wcf_table.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
            wcf_table.setAlignment(Alignment.CENTRE);
            wcf_table.setBackground(Colour.GRAY_25);
            wcf_table.setWrap(true); // 是否换行

            /** *//** ************单元格格式设置完成****************** */
            //合并单元格,注意mergeCells(col0,row0,col1,row1) --列从0开始,col1为你要合并到第几列,行也一样
            sheet.mergeCells(0, 0, 5, 0);

            sheet.addCell(new Label(0, 0, "这里是大标题,自定义格式",
                    wcf_title));

            sheet.mergeCells(0, 1, 1, 1);
            sheet.mergeCells(2, 1, 5, 1);

            sheet.addCell(new Label(0, 1, "", wcf_center));
            sheet.addCell(new Label(2, 1, "姓名:" + "supercrsky",
                    wcf_center));

            sheet.mergeCells(0, 2, 1, 2);
            sheet.mergeCells(2, 2, 3, 2);

            sheet.addCell(new Label(0, 2, "单位:", wcf_center));
            sheet.addCell(new Label(2, 2, "ChinaLong", wcf_center));
            sheet.addCell(new Label(4, 2, "薪水", wcf_center));
            sheet.addCell(new Label(5, 2, "5000", wcf_center));

            sheet.mergeCells(0, 3, 1, 3);
            sheet.mergeCells(2, 3, 3, 3);

            sheet.addCell(new Label(0, 3, "性别:", wcf_center));
            sheet.addCell(new Label(2, 3, "男", wcf_center));
            sheet.addCell(new Label(4, 3, "婚否:", wcf_center));
            sheet.addCell(new Label(5, 3, "否", wcf_center));

            sheet.mergeCells(0, 4, 1, 4);
            sheet.mergeCells(2, 4, 3, 4);

            sheet.addCell(new Label(0, 4, "是否在职:", wcf_center));
            sheet.addCell(new Label(2, 4,"是",
                    wcf_center));
            sheet.addCell(new Label(4, 4,"工作经验:", wcf_center));
            sheet.addCell(new Label(5, 4, "4",wcf_center));

            sheet.mergeCells(0, 5, 1, 5);
            sheet.mergeCells(2, 5, 3, 5);

            sheet.addCell(new Label(0, 5, "保险费:", wcf_center));
            sheet.addCell(new Label(2, 5,"50",
                    wcf_center));
            sheet.addCell(new Label(4, 5, "保险金额:", wcf_center));
            sheet.addCell(new Label(5, 5, "50000",
                    wcf_center));

            sheet.mergeCells(0, 6, 1, 6);
            sheet.mergeCells(2, 6, 3, 6);

            sheet.addCell(new Label(0, 6, "工作地点:", wcf_center));
            sheet.addCell(new Label(2, 6, "北京", wcf_center));
            sheet.addCell(new Label(4, 6, "开心度：", wcf_center));
            sheet.addCell(new Label(5, 6, "一般", wcf_center));

            // 另起一table
            sheet.mergeCells(0, 7, 5, 7);
            sheet.addCell(new Label(0, 7, "详细数据", wcf_tabletitle));
            // table标题
            sheet.addCell(new Label(0, 8, "序号", wcf_table));
            sheet.addCell(new Label(1, 8, "姓名", wcf_table));
            sheet.addCell(new Label(2, 8, "年龄", wcf_table));
            sheet.addCell(new Label(3, 8, "性别", wcf_table));
            sheet.addCell(new Label(4, 8, "婚否", wcf_table));
            sheet.addCell(new Label(5, 8, "在职", wcf_table));
            // table内容
            //这里用你的dao
            TestDAO dao = new TestDAO();
            List list = dao.findBy(user.getUserId());
            System.out.println("此保单拥有防疫码数量:" + list.size());
            for (int i = 0; i < list.size(); i++)
            {
                //对应你的vo类
                User data = (User) list.get(i);

                sheet.addCell(new Label(0, 9 + i, String.valueOf(i + 1),
                        wcf_center));
                sheet.addCell(new Label(1, 9 + i, data.getDlEPCode(),
                        wcf_center));
                sheet
                        .addCell(new Label(2, 9 + i, data.getDlType(),
                                wcf_center));
                sheet.addCell(new Label(3, 9 + i, String.valueOf(data
                        .getDlPigAge()), wcf_center));
                sheet.addCell(new Label(4, 9 + i, "", wcf_center));
                sheet.addCell(new Label(5, 9 + i, "", wcf_center));
            }
            /** *//** **********以上所写的内容都是写在缓存中的，下一句将缓存的内容写到文件中******** */
            workbook.write();
            /** *//** *********关闭文件************* */
            workbook.close();
            System.out.println("导出成功");
            // 存放url地址
        } catch (Exception e)
        {
            System.out.println("在输出到EXCEL的过程中出现错误，错误原因：" + e.toString());
        }
    }


外加一段读取流的代码：  呵呵非常有用哦！！
HttpServletResponse  response = ServletActionContext.getResponse();
   String fileChName="项目积分综合报表.xls";
   response.reset(); 
   response.setCharacterEncoding("ISO8859-1"); 
   response.setHeader("Content-Disposition", "attachment; filename=" 
   + new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 
   response.setHeader("Connection", "close"); 
   response.setHeader("Content-Type", "application/octet-stream"); 

   OutputStream out = response.getOutputStream(); 
  
   WritableWorkbook wwb = Workbook.createWorkbook(out);