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();