.net导出excel

string date1 = this.txtStartTime.Text.Trim();
            string date2 = this.txtEndTime.Text.Trim();
            string strDate = string.Empty;
            string titledate = string.Empty;
            if (date1 != "" && date2 != "")
            {
                strDate = " and (senddate between '" + date1 + "' and Dateadd(d,1,'" + date2 + "') ) ";
                titledate = "(" + date1 + "至" + date2 + ")";
            }
            string tmpdepid = e.CommandArgument.ToString();
            string tmpsql = "select a.tf,s.senddate,s.isly from tb_send s,tb_article a where s.sendArticleId=a.qwbs and s.senddepid='" + tmpdepid + "' " + strDate + " order by senddate desc";
            DataTable dt = CHWEB.DBUtility.DbHelperSQL.Query(tmpsql).Tables[0];            

            if (dt != null && dt.Rows.Count > 0)
            {
                Excel.Application excel = new Excel.Application();
                if (excel == null)
                {
                    return;
                }
                Excel.Workbooks workbooks = excel.Workbooks;
                Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
                Excel.Range range = worksheet.Cells;
                //设置表格样式
                Excel.PageSetup ps = worksheet.PageSetup;
                //ps.Orientation = Excel.XlPageOrientation.xlLandscape
                ps.CenterHorizontally = true;
                ps.LeftMargin = ps.RightMargin = excel.Application.InchesToPoints(0.551181102362205);
                ps.TopMargin =ps.BottomMargin= excel.Application.InchesToPoints(0.590551181102362);
                ps.HeaderMargin = ps.FooterMargin = excel.Application.InchesToPoints(0.511811023622047);
                ps.CenterFooter = "第 &P 页，共 &N 页";


                //导出内容如下--------------
                Excel.Range r1 = worksheet.get_Range(range[1, 1], range[1, 4]) as Excel.Range;
                r1.Merge(true);


                r1.Value2 = getDepShortName(tmpdepid) + " 的报送数据" + titledate;
                r1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                r1.Font.Name = "黑体";
                r1.RowHeight = 50;
                r1.Font.Size = 20;
                r1.Font.Bold = true;

                string[] titles = new string[] { "序号", "报送标题", "报送日期", "状态" };
                for (int i = 0; i < titles.Length; i++)
                {
                    Excel.Range r = (Excel.Range)worksheet.Cells[2, i+1];
                    r.Value2 = titles[i];
                    r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    r.RowHeight = 20;
                    r.Font.Bold = true;
                    switch(i.ToString())
                    {
                        case "0":
                            r.ColumnWidth = 8;
                            break;
                        case "2":
                            r.ColumnWidth = 18;
                            break;
                        case "3":
                            r.ColumnWidth = 8;
                            break;
                        default:
                            r.ColumnWidth = 48;
                            break;
                    }
                }

                int rowindex = 3;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    Excel.Range rr1 ;
                    rr1 = (Excel.Range)worksheet.Cells[rowindex, 1];
                    rr1.Value2 = (i + 1).ToString();
                    rr1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                    Excel.Range rr2;
                    rr2 = (Excel.Range)worksheet.Cells[rowindex, 2];
                    rr2.Value2 = dr[0].ToString();
                    rr2.WrapText = true;//自动换行
                    if (dr[2].ToString() == "1")
                    {
                        rr2.Font.ColorIndex = 5;
                        
                    }

                    Excel.Range rr3;
                    rr3 = (Excel.Range)worksheet.Cells[rowindex, 3];
                    rr3.Value2 = dr[1].ToString();
                    rr3.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                    Excel.Range rr4;
                    rr4 = (Excel.Range)worksheet.Cells[rowindex, 4];
                    rr4.Value2 = getInfoStatus(dr[2].ToString());
                    rr4.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                    rowindex++;
                }

                Excel.Range rAll = (Excel.Range)worksheet.get_Range(range[2, 1], range[rowindex-1, 4]);
                rAll.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

                //合计行
                Excel.Range rFooter = (Excel.Range)worksheet.get_Range(range[rowindex, 1], range[rowindex, 4]);
                rFooter.Merge(true);
                rFooter.Value2 = "合计：报送总量" + dt.Rows.Count.ToString() + "，被采用量" + getDepUse(tmpdepid).ToString();
                rFooter.RowHeight = 40;
                rFooter.Font.Bold = true;

                

                //-------------------------------
                string filepath = Server.MapPath("export.xls");


                excel.Visible=false;
                workbook.SaveCopyAs(filepath);
                workbook.Close(false, null, null);
                excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                workbook = null;
                excel = null;
                worksheet = null;

                GC.Collect();

                System.IO.FileInfo file = new System.IO.FileInfo(filepath);
                Response.Clear();
                Response.Charset = "GB2312";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
                Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(getDepShortName(tmpdepid)+".xls"));
                // 添加头信息，指定文件大小，让浏览器能够显示下载进度
                Response.AddHeader("Content-Length", file.Length.ToString());

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

                // 把文件流发送到客户端
                //Response.WriteFile(file.FullName);
                Response.TransmitFile(filepath);
                // 停止页面的执行

                Response.End();