一个自己测试通过的操作EXCEL using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Reflection; namespace WebApplication3 { /**//// <summary> /// WebForm1 的摘要说明。 /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.WebControls.Button Button1; private void Page_Load(object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 } Web 窗体设计器生成的代码#region Web 窗体设计器生成的代码 override protected void OnInit(EventArgs e) { // // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 // InitializeComponent(); base.OnInit(e); } /**//// <summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 /// </summary> private void InitializeComponent() { this.Button1.Click += new System.EventHandler(this.Button1_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion private void Button1_Click(object sender, System.EventArgs e) { SaveToExcel(); } private string [,] myData= { {"车牌号","类型","品 牌","型 号","颜 色","附加费证号","车架号"}, {"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"}, {"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"}, {"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"}, {"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"}, {"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"}, {"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"}, {"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"}, {"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"}, {"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"}, {"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"} }; private void SaveToExcel() { //创建一个Excel文件 Excel.Application myExcel = new Excel.Application ( ) ; myExcel.Application.Workbooks.Add ( true ) ; //让Excel文件可见 myExcel.Visible=true; //第一行为报表名称 myExcel.Cells[1,4]="普通报表"; //逐行写入数据, for(int i=0;i<11;i++) { for(int j=0;j<7;j++) { //以单引号开头,表示该单元格为纯文本 myExcel.Cells[2+i,1+j]="'"+myData[i,j]; } } } }} 2 private string SaveToExcel(DataTable dtDetail) {// string urlPath = HttpContext.Current.Request.ApplicationPath ;// string physicPath = HttpContext.Current.Server.MapPath(urlPath); // string strFullName = System.IO.Path.Combine(physicPath,xmlFileName.P_TempXlsFile); //创建一个Excel文件 Excel.Application myExcel = new Excel.Application ( ) ;// myExcel.Application.Workbooks.Open(strFullName);// myExcel.Cells.Clear(); myExcel.Application.Workbooks.Add ( true ) ;// myExcel.ActiveWorkbook.Open(strFullName);//// myExcel.AutoRecover = true;// myExcel.ActiveWorkbook.Save(); //让Excel文件可见 //myExcel.Visible=true; //第一行为报表名称 myExcel.Cells[1,4]="材料统计"; //逐行写入数据, myExcel.Cells[2,1]= "材料编号"; myExcel.Cells[2,2]= "项目分类"; myExcel.Cells[2,3]= "期初数量"; myExcel.Cells[2,4]= "入库数量"; myExcel.Cells[2,5]= "出库数量"; myExcel.Cells[2,6]= "损益量"; myExcel.Cells[2,7]= "期末数量"; myExcel.Cells[2,8]= "订单损耗"; myExcel.Cells[2,9]= "损益比"; myExcel.Cells[2,10]= "备注"; DataView dv = dtDetail.DefaultView; dv.Sort = "NewSort,SortOrder"; for(int i = 0 ; i < dv.Count ; i++) { for(int j = 0; j < dv.Table.Columns.Count; j++) { //以单引号开头,表示该单元格为纯文本 //myExcel.Cells[2+i,1+j]="'" + dtDetail.Rows[i][j].ToString().Trim(); if (dv.Table.Columns[j].ColumnName == "MaterialNo") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,1]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "NewSort") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,2]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "PeriodStartAmount") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,3]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "InComeSumByMat") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,4]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "TheoryOut") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,5]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "Waste") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,6]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "PeriodEndAmount") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,7]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "WorkSumByMat") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,8]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "WasteRate") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,9]= dv[i][j].ToString().Trim(); } else if (dv.Table.Columns[j].ColumnName == "Memo") { if (!dv.Table.Rows[i].IsNull(j)) myExcel.Cells[3+i,10]= dv[i][j].ToString().Trim(); } } } string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/"; string physicPath = HttpContext.Current.Server.MapPath(urlPath); string fileName = Guid.NewGuid() + ".xls"; string strFullName = physicPath + fileName; //myExcel.ActiveWorkbook.SaveAs(strFullName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlShared, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing ); myExcel.SaveWorkspace(strFullName); myExcel.ActiveWorkbook.Close(true,strFullName,Type.Missing); myExcel.AlertBeforeOverwriting = false; //myExcel.Save(strFullName); myExcel.Quit(); return strFullName; } private void StartOutPort() { //获得从表 DataSet dsDetail = new DataSet(); GetDsForDetailTJ(ref dsDetail); if (dsDetail == null || dsDetail.Tables.Count <= 0) return; DataTable Detaildt1 = null; Detaildt1 = GetFullDt(ref dsDetail,"detail"); string strFullfileName = SaveToExcel(Detaildt1);// dsDetail.WriteXml(physicPath+fileName); HttpResponse response = HttpContext.Current.Response; response.Clear(); response.WriteFile(strFullfileName); string httpHeader="attachment;filename=newFile.xls"; response.AppendHeader("Content-Disposition", httpHeader); response.Flush(); System.IO.File.Delete(strFullfileName);//删除临时文件 response.End(); } 3 private void SaveToExcel() { string urlPath = HttpContext.Current.Request.ApplicationPath + "/Temp/"; string physicPath = HttpContext.Current.Server.MapPath(urlPath); string fileName = Guid.NewGuid() + ".Xls"; string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName +";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); OleDbCommand objCmd = new OleDbCommand(); objCmd.Connection = objConn; objConn.Open(); //建立表结构 objCmd.CommandText = @"CREATE TABLE TBL_Customer (CustomerName varchar,CustomerNo varchar)"; objCmd.ExecuteNonQuery(); objCmd.CommandText = "INSERT INTO TBL_Customer(CustomerName, CustomerNo) VALUES ('毛消化', 'good')"; objCmd.ExecuteNonQuery(); //提供下载 objCmd.Dispose(); objConn.Dispose(); HttpResponse response = HttpContext.Current.Response; response.Clear(); response.WriteFile(physicPath + fileName); string httpHeader="attachment;filename=backup.Xls"; response.AppendHeader("Content-Disposition", httpHeader); response.Flush(); System.IO.File.Delete(physicPath + fileName);//删除临时文件 response.End(); }