/// <summary> /// 导出非防汛责任人的数据到Excel /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExcelOut_Click(object sender, EventArgs e) { string strSQL = string.Empty; DataSet dataSet = new DataSet(); strSQL = string.Format("SELECT [Name] as 姓名,Telephone as 手机,UnitName as 单位,Remark as 备注 FROM A03_NO_FXZRR_SMS_REP WHERE GroupName LIKE '%{0}%'", managename.Trim()); dataSet = db.RunProcReturn(strSQL, managename); string toFileName = "~/Manage/非防汛责任人.xls"; doExport(dataSet.Tables[0],toFileName,"Sheet1"); DownFiles(toFileName); } /// <summary> /// 执行导出 /// </summary> /// <param name="ds">要导出的DataTable </param> /// <param name="toFileName">要导出到的execl文件路径+文件名</param> /// <param name="strExcelFileName">导出到的execl的Sheet名</param> private void doExport(DataTable dt, string toFileName, string strSheetName) { string fromFileName = "NullExcel.xls"; toFileName = Server.MapPath(toFileName); Excel.Application excel = new Excel.Application(); //Execl的操作类 //读取保存目标的对象 Excel.Workbook bookDest = excel.Workbooks._Open(Server.MapPath(fromFileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value, Missing.Value);//打开要导出到的Execl文件的工作薄。 Excel.Worksheet sheetDest = bookDest.Worksheets.get_Item(1) as Excel.Worksheet; //sheetDest.Name = strSheetName; int rowIndex = 1; int colIndex = 0; excel.Application.Workbooks.Add(true);//这句不写不知道会不会报错 foreach (DataColumn col in dt.Columns) { colIndex++; sheetDest.Cells[1, colIndex] = col.ColumnName;//Execl中的第一列,把DataTable的列名先导进去 } //导入数据行 foreach (DataRow row in dt.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dt.Columns) { colIndex++; sheetDest.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString(); } } bookDest.SaveAs(toFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value , Missing.Value,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value , Missing.Value, Missing.Value, Missing.Value); excel = null; bookDest = null; GC.Collect();//垃圾回收 } /// <summary> /// 下载文件,新建一个down.aspx页面 /// </summary> /// <param name="fileUrl">下载文件的路径或文件名</param> public void DownFiles(string fileUrl) { string path = Server.MapPath(fileUrl); System.IO.FileInfo file = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); FileStream fs = File.OpenRead(path); byte[] bytes = new Byte[file.Length]; Response.BinaryWrite(bytes); Response.End(); }