C#将数据导出到l汇总.doc

C# 将数据导出到Execl汇总1、将整个html全部输出execl此法将html中所有的内容，如按钮，表格，图片等全部输出到Execl中。 Response.Clear(); Response.Buffer= true; Response.AppendHeader(Content-Disposition,attachment;filename=+DateTime.Now.ToString(yyyyMMdd)+.xls); Response.ContentEncoding=System.Text.Encoding.UTF8; Response.ContentType = application/vnd.ms-excel; this.EnableViewState = false; 利用了ContentType属性，它默认的属性为text/html，这时将输出为超文本，即我们常见的网页格式到客户端，如果改为ms-excel将将输出excel格式，也就是说以电子表格的格式输出到客户端，这时浏览器将提示你下载保存。ContentType的属性还包括：image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword我们也可以输出(导出)图片、word文档等。下面的方法，也均用了这个属性。2、将DataGrid控件中的数据导出ExeclSystem.Web.UI.Control ctl=this.DataGrid1;/DataGrid1是你在窗体中拖放的控件HttpContext.Current.Response.AppendHeader(Content-Disposition,attachment;filename=Excel.xls); HttpContext.Current.Response.Charset =UTF-8; HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default; HttpContext.Current.Response.ContentType =application/ms-excel;ctl.Page.EnableViewState =false; System.IO.StringWriter tw = new System.IO.StringWriter() ; System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString(); HttpContext.Current.Response.End(); 如果你的DataGrid用了分页，它导出的是当前页的信息，也就是它导出的是DataGrid中显示的信息。而不是你select语句的全部信息。为方便使用,写成方法如下：public void DGToExcel(System.Web.UI.Control ctl) HttpContext.Current.Response.AppendHeader(Content-Disposition,attachment;filename=Excel.xls); HttpContext.Current.Response.Charset =UTF-8; HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default; HttpContext.Current.Response.ContentType =application/ms-excel; ctl.Page.EnableViewState =false; System.IO.StringWriter tw = new System.IO.StringWriter() ; System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString(); HttpContext.Current.Response.End(); 用法：DGToExcel(datagrid1);3、将DataSet中的数据导出Execl把DataSet中的数据导出，也就是把DataSet中的表中的各行信息，以ms-excel的格式Response到http流，这样就OK了。说明：参数ds应为填充有数据表的DataSet，文件名是全名，包括后缀名，如execl2006.xls public void CreateExcel(DataSet ds,string FileName) HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding(GB2312); resp.AppendHeader(Content-Disposition, attachment;filename=+FileName); string colHeaders= , ls_item=; /定义表对象与行对象，同时用DataSet对其值进行初始化 DataTable dt=ds.Tables0; DataRow myRow=dt.Select();/可以类似dt.Select(id10)之形式达到数据筛选目的 int i=0; int cl=dt.Columns.Count; /取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for(i=0;icl;i+)if(i=(cl-1)/最后一列，加ncolHeaders +=dt.Columnsi.Caption.ToString() +n; elsecolHeaders+=dt.Columnsi.Caption.ToString()+t; resp.Write(colHeaders); /向HTTP输出流中写入取得的数据信息 /逐行处理数据 foreach(DataRow row in myRow) /当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for(i=0;icl;i+)if(i=(cl-1)/最后一列，加nls_item +=rowi.ToString()+n; elsels_item+=rowi.ToString()+t; resp.Write(ls_item); ls_item=; resp.End(); 4、将dataview导出execl若想实现更加富于变化或者行列不规则的execl导出时，可用本法。public void OutputExcel(DataView dv,string str) /dv为要输出到Excel的数据，str为标题名称 GC.Collect(); Application excel;/ = new Application(); int rowIndex=4; int colIndex=1; _Workbook xBk; _Worksheet xSt; excel= new ApplicationClass(); xBk = excel.Workbooks.Add(true); xSt = (_Worksheet)xBk.ActiveSheet; / /取得标题 / foreach(DataColumn col in dv.Table.Columns) colIndex+; excel.Cells4,colIndex = col.ColumnName; xSt.get_Range(excel.Cells4,colIndex,excel.Cells4,colIndex).HorizontalAlignment = XlVAlign.xlVAlignCenter;/设置标题格式为居中对齐 / /取得表格中的数据 / foreach(DataRowView row in dv) rowIndex +; colIndex = 1; foreach(DataColumn col in dv.Table.Columns) colIndex +; if(col.DataType = System.Type.GetType(System.DateTime) excel.CellsrowIndex,colIndex = (Convert.ToDateTime(rowcol.ColumnName.ToString().ToString(yyyy-MM-dd); xSt.get_Range(excel.CellsrowIndex,colIndex,excel.CellsrowIndex,colIndex).HorizontalAlignment = XlVAlign.xlVAlignCenter;/设置日期型的字段格式为居中对齐 else if(col.DataType = System.Type.GetType(System.String) excel.CellsrowIndex,colIndex = +rowcol.ColumnName.ToString(); xSt.get_Range(excel.CellsrowIndex,colIndex,excel.CellsrowIndex,colIndex).HorizontalAlignment = XlVAlign.xlVAlignCenter;/设置字符型的字段格式为居中对齐 else excel.CellsrowIndex,colIndex = rowcol.ColumnName.ToString(); / /加载一个合计行 / int rowSum = rowIndex + 1; int colSum = 2; excel.CellsrowSum,2 = 合计; xSt.get_Range(excel.CellsrowSum,2,excel.CellsrowSum,2).HorizontalAlignment = XlHAlign.xlHAlignCenter; / /设置选中的部分的颜色 / xSt.get_Range(excel.CellsrowSum,colSum,excel.CellsrowSum,colIndex).Select(); xSt.get_Range(excel.CellsrowSum,colSum,excel.CellsrowSum,colIndex).Interior.ColorIndex = 19;/设置为浅黄色，共计有56种 / /取得整个报表的标题 / excel.Cells2,2 = str; / /设置整个报表的标题格式 / xSt.get_Range(excel.Cells2,2,excel.Cells2,2).Font.Bold = true; xSt.get_Range(excel.Cells2,2,excel.Cells2,2).Font.Size = 22; / /设置报表表格为最适应宽度 / xSt.get_Range(excel.Cells4,2,excel.CellsrowSum,colIndex).Select(); xSt.get_Range(excel.Cells4,2,excel.CellsrowSum,colIndex).Columns.AutoFit(); / /设置整个报表的标题为跨列居中 / xSt.get_Range(excel.Cells2,2,excel.Cells2,colIndex).Select(); xSt.get_Range(excel.Cells2,2,excel.Cells2,colIndex).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; / /绘制边框 / xSt.get_Range(excel.Cells4,2,excel.CellsrowSum,colIndex).Borders.LineStyle = 1; xSt.get_Range(excel.Cells4,2,excel.CellsrowSum,2).BordersXlBordersIndex.xlEdgeLeft.Weight = XlBorderWeight.xlThick;/设置左边线加粗 xSt.get_Range(excel.Cells4,2,excel.Cells4,colIndex).BordersXlBordersIndex.xlEdgeTop.Weight = XlBorderWeight.xlThick;/设置上边线加粗 xSt.get_Range(excel.Cells4,colIndex,excel.CellsrowSum,colIndex).BordersXlBordersIndex.xlEdgeRight.Weight = XlBorderWeight.xlThick;/设置右边线加粗 xSt.get_Range(excel.CellsrowSum,2,excel.CellsrowSum,colIndex).BordersXlBordersIndex.xlEdgeBottom.Weight = XlBorderWeight.xlThick;/设置下边线加粗 / /显示效果 / excel.Visible=true; /xSt.Export(Server.MapPath(.)+this.xlfile.Text+.xls,SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML); xBk.SaveCopyAs(Server.MapPath(.)+this.xlfile.Text+.xls); ds = null; xBk.Close(false, null,null); excel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt); xBk = null; excel = null; xSt = null; GC.Collect(); string path = Server.MapPath(this.xlfile.Text+.xls); 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); / 停止页面的执行 Response.End(); winForm中导出Execl的方法：1、方法1SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettingsconn); SqlDataAdapter da=new SqlDataAdapter(select * from tb1,conn); DataSet ds=new DataSet(); da.Fill(ds,table1); DataTable dt=ds.Tablestable1; string name=System.Configuration.ConfigurationSettings.AppSettingsdownloadurl.ToString()+DateTime.Today.ToString(yyyyMMdd)+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+.csv;/存放到 web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数 FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding(gb2312); sw.WriteLine(自动编号,姓名,年龄); foreach(DataRow dr in dt.Rows) sw.WriteLine(drID+,+drvName+,+driAge); sw.Close(); Response.AddHeader(Content-Disposition, attachment; filename= + Server.UrlEncode(name); Response.ContentType = application/ms-excel;/ 指定返回的是一个不能被客户端读取的流，必须被下载 Response.WriteFile(name); / 把文件流发送到客户端 Response.End(); public void Out2Excel(string sTableName,string url)Excel.Application oExcel=new Excel.Application();Workbooks oBooks;Workbook oBook;Sheets oSheets;Worksheet oSheet;Range oCells;string sFile=,sTemplate=;/System.Data.DataTable dt=TableOut(sTableName).Tables0;sFile=url+myExcel.xls;sTemplate=url+MyTemplate.xls;/oExcel.Visible=false;oExcel.DisplayAlerts=false;/定义一个新的工作簿oBooks=oExcel.Workbooks;oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);oBook=oBooks.get_Item(1);oSheets=oBook.Worksheets;oSheet=(Worksheet)oSheets.get_Item(1);/命名该sheetoSheet.Name=Sheet1;oCells=oSheet.Cells;/调用dumpdata过程，将数据导入到Excel中去DumpData(dt,oCells);/保存oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);oBook.Close(false, Type.Missing,Type.Missing);/退出Excel，并且释放调用的COM资源oExcel.Quit();GC.Collect();KillProcess(Excel);private void KillProcess(string processName)System.Diagnostics.Process myproc= new System.Diagnostics.Process();/得到所有打开的进程tryforeach (Process thisproc in Process.GetProcessesByName(processName)if(!thisproc.CloseMainWindow()thisproc.Kill();catch(Exception Exc)throw new Exception(,Exc);2、方法2：protected void ExportExcel()gridbind(); if(ds1=null) return; string saveFileName=;/bool fileSaved=false;SaveFileDialog saveDialog=new SaveFileDialog();saveDialog.DefaultExt =xls;saveDialog.Filter=Excel文件|*.xls;saveDialog.FileName =Sheet1;saveDialog.ShowDialog();saveFileName=saveDialog.FileName;if(saveFileName.IndexOf(:)0) return; /被点了取消/excelapp.Workbooks.Open (App.path & 工程进度表.xls) Excel.Application xlApp=new Excel.Application();object missing=System.Reflection.Missing