protected void Page_Load(object sender, EventArgs e)

        {

            ZHLSExcel();

        }

        HSSFWorkbook workbook = new HSSFWorkbook();

        //设置最初的列

        protected List<string> ListZM = new List<string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", };

        public void ZHLSExcel()

        {

            DataTable dt = new DataTable();

            string strWhere = "  BankID=0";

            //一下是其他js传过来的参数

            if (Request.Params["opstate"] != null)

            {

                string opstate = Request.Params["opstate"].ToString().Trim();

                if (opstate.Trim().Length >  && opstate != "")

                {

                    strWhere += " and ops='" + Request.Params["opstate"].ToString().Trim() + "'";

                }

            }

            string sql = " select hu as oney, df as money from table  where" + strWhere;

            classDBOP dbop = new classDBOP();

            dt = dbop.ExcSql(sql, ); //sql插叙结果集。可以自己写

            //  日期  金额  经办人  财务  出纳  对方账户

            string title = "流水表";//标题

            string[] biaoTou = new string[] { "日期", "收", "支", "经办人", "财务", "出纳", "对方账户" };//表头

            try

            {

                MemoryStream ms = new MemoryStream();

                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("newSheet1");

                string fileName = "流水表.xls";//文件名

                string filePath = Server.MapPath("~/TMFile/OAFile/BB_Report/CDay/" + fileName);//文件路径

                #region 填充标题

                IRow row = sheet.CreateRow();//第几行

                row.HeightInPoints = ;//行高

                ICell cell = row.CreateCell();//在行中添加一列

                cell.SetCellValue(title);//设置列的内容

                ICellStyle style = workbook.CreateCellStyle();

                style.Alignment = HorizontalAlignment.CENTER;

                style.VerticalAlignment = VerticalAlignment.CENTER;

                IFont font = workbook.CreateFont();

                font.FontHeightInPoints = ;

                style.SetFont(font);

                cell.CellStyle = style;

                int cellRows = dt.Rows.Count;

                int cellNo = dt.Columns.Count;

                sheet.AddMergedRegion(new CellRangeAddress(, , , cellNo - ));

                CellRangeAddress cellRangeAddress = new CellRangeAddress(, , , cellNo - );

                sheet.AddMergedRegion(cellRangeAddress);

                #endregion

                #region 填充表头

                IRow row1 = sheet.CreateRow();

                for (int j = ; j < biaoTou.Length; j++)

                {

                    //吧biaoTou表头得类容写入

                    row1.CreateCell(j).SetCellValue(biaoTou[j]);

                }

                #endregion

                #region 填充内容

                decimal allshou = , allzhi = ;

                for (int r = ; r < dt.Rows.Count; r++)

                {

                    IRow rows = sheet.CreateRow(r + );//cellRows + 2

                    for (int c = ; c < dt.Columns.Count; c++)

                    {

                        rows.CreateCell(c).SetCellValue(dt.Rows[r][c].ToString());

                        //读取dt里面的数据。

                    }

                    allshou += Convert.IsDBNull(dt.Rows[r]["oney"]) ?  : decimal.Parse(dt.Rows[r]["oney"].ToString());

                    allzhi += Convert.IsDBNull(dt.Rows[r]["money"]) ?  : decimal.Parse(dt.Rows[r]["money"].ToString());

                }

                #endregion

                #region 尾部

                IRow rowM = sheet.CreateRow(cellRows + );

                rowM.HeightInPoints = ;

                ICell ceeM = rowM.CreateCell();

                ceeM.SetCellValue("收入：" + allshou.ToString());

                ceeM = rowM.CreateCell();

                ceeM.SetCellValue("支出：" + allzhi.ToString());

                IRow rowAdd = sheet.CreateRow(cellRows + );

                rowAdd.HeightInPoints = ;

                ICell ceeAdd = rowAdd.CreateCell();

                ceeAdd.SetCellValue("制单人：" + Session["Name"].ToString().Trim() + "  日期：" + DateTime.Now.ToShortDateString());

                IRow rowEnd = sheet.CreateRow(cellRows + );

                rowEnd.HeightInPoints = ;

                ICell cellEnd = rowEnd.CreateCell();

                cellEnd.SetCellValue("此表由XXXXX" + DateTime.Now.ToString("yyyy年MM月dd日") + "导出    ");

                ICellStyle styleEnd = workbook.CreateCellStyle();

                styleEnd.Alignment = HorizontalAlignment.RIGHT;

                styleEnd.VerticalAlignment = VerticalAlignment.CENTER;

                IFont fontEnd = workbook.CreateFont();

                fontEnd.FontHeightInPoints = ;

                fontEnd.Color = HSSFColor.GREY_50_PERCENT.index;

                fontEnd.Boldweight = (short)FontBoldWeight.BOLD;

                styleEnd.SetFont(fontEnd);

                cellEnd.CellStyle = styleEnd;

                sheet.AddMergedRegion(new CellRangeAddress(cellRows + , cellRows + , , cellNo - ));//起始行号，终止行号， 起始列号，终止列号

                #endregion

                #region 保存

                workbook.Write(ms);

                using (FileStream stream = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))

                {

                    //创建文件，将workbook写入文件

                    workbook.Write(stream);

                }

                workbook = null;

                ms.Close();

                ms.Dispose();

                FileInfo file = new FileInfo(filePath);

                Response.Clear();

                Response.Charset = "GB2312";

                Response.ContentEncoding = System.Text.Encoding.UTF8;

                //添加头信息，为"文件下载/另存为"对话框指定默认文件名

                Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

                Response.AddHeader("Content-Length", file.Length.ToString());//添加头信息，指定文件大小，让浏览器能够显示下载进度

                Response.ContentType = "application/ms-excel";//指定返回的是一个不能被客户端读取的流，必须被下载

                Response.WriteFile(file.FullName);//把文件流发送到客户端

                Response.End();//停止页面的执行

                #endregion

            }

            catch (System.Exception ex)

            {

            }

        }