dataset 导出到excel

protected void btnDelete_Click(object sender, EventArgs e)

        {

            DataSet ds = errorlog_bll.GetList(" dtDate <= DateAdd(Month,-3,getdate())");



            CreateExcel(ds.Tables[0], "D:\\Sysnote"+(int.Parse(DateTime.Now.Month.ToString())-2)+"月之前");



             //if (export)

             //{

             //    string strwhere = " datediff(m,dtdate,getdate())>3";



             //    if (errorlog_bll.Delete(strwhere))

             //    {

             //        FineUI.Alert.Show("删除成功");

             //    }



             //    BindGrid();

             //}

        }



        public void CreateExcel(DataTable dt, string fileName)

        {

            if (dt.Rows.Count == 0)

            {

                FineUI.Alert.Show("无数据可导出");

            }



            try

            {

                System.Diagnostics.Process[] arrProcesses;

                arrProcesses = System.Diagnostics.Process.GetProcessesByName("Excel");

                foreach (System.Diagnostics.Process myProcess in arrProcesses)

                {

                    myProcess.Kill();

                }



                Object missing = System.Reflection.Missing.Value;

                Microsoft.Office.Interop.Excel.Application m_objExcel =



                 new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks;

                Microsoft.Office.Interop.Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true);

                Microsoft.Office.Interop.Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ;

                Microsoft.Office.Interop.Excel.Worksheet m_objWorkSheet =



                 (Microsoft.Office.Interop.Excel.Worksheet)m_objWorkSheets[1];

                int intFeildCount = dt.Columns.Count;

                for (int col = 0; col < intFeildCount; col++)

                {

                    m_objWorkSheet.Cells[1, col + 1] = dt.Columns[col].ToString();

                }

                for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)

                {

                    for (int intCol = 0; intCol < dt.Columns.Count; intCol++)

                    {

                        m_objWorkSheet.Cells[intRowCount + 2, intCol + 1] = "'" + dt.Rows[intRowCount][intCol].ToString();

                    }

                }



                if (File.Exists(fileName))

                {

                    File.Delete(fileName);

                }

                //m_objWorkBook.SaveAs(fileName, missing, missing, missing, missing,



                // missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

                // missing, missing, missing, missing, missing);

                m_objWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel3,

                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, 

                     Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);



                m_objExcel = null;

            }

            catch (Exception ex)

            {

                string strEXMessage = ex.Message;



                FineUI.Alert.Show(strEXMessage);

            }

        }

using System;

using System.Data;

using System.Web;

using System.Collections;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.IO;



using System.Xml;

using System.Xml.Xsl;



namespace ASUS.Saber.SystemFramework

{

	/// <summary>

	/// TableToExcel 的摘要说明。

	/// </summary>

	public class TableToExcel

	{

		public TableToExcel()

		{

			//

			// TODO: 在此处添加构造函数逻辑

			//

		}



		public void CreatExcel(DataTable dt,string excel_file)

		{

			//提供下载

			HttpResponse response = HttpContext.Current.Response; 





			string urlPath = HttpContext.Current.Request.ApplicationPath + "/athena/Temp/";

			string physicPath = HttpContext.Current.Server.MapPath(urlPath);

			if (!File.Exists(physicPath))

			{

				Directory.CreateDirectory(physicPath);

			}

			string fileName = Guid.NewGuid() + ".Xls"; 



			string table_string=@"

				CREATE TABLE ProductSpecification

				(

			";



			table_string+=CreatSqlString(dt,0);

			table_string+=" )";



			response.Write(table_string);			



			string insert_string=@"

				INSERT INTO ProductSpecification

				(

			";

			insert_string+=CreatSqlString(dt,1);

			insert_string+=" )";

			insert_string+=@"

				VALUES (

			";

			insert_string+=CreatSqlString(dt,2);

			insert_string+=" )";



			response.Write(insert_string);



			

			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;



			//建立表结构

			objCmd.CommandText =table_string;

			objConn.Open();

			objCmd.ExecuteNonQuery();



			

			//建立插入动作的Command			

			objCmd.CommandText =insert_string;

			for (int i=0;i<dt.Columns.Count;i++)

			{

				objCmd.Parameters.Add(new OleDbParameter("@a_"+i, OleDbType.LongVarChar,8000));

			}



			string str_content="";

			//插入新数据

			foreach (DataRow row in dt.Rows)

			{    

				for (int i=0; i<objCmd.Parameters.Count; i++)

				{	

					

					str_content=row[i].ToString();

					if (str_content.Length>200)

					{

						str_content=str_content.Substring(0,200);

					}

					objCmd.Parameters[i].Value =str_content; 	

				}

				objCmd.ExecuteNonQuery();

			}			

			objConn.Close();



			

			response.Clear();

			response.WriteFile(physicPath + fileName);

			string httpHeader="attachment;filename="+excel_file;

			response.AppendHeader("Content-Disposition", httpHeader);

			response.Flush();



			System.IO.File.Delete(physicPath + fileName);//删除临时文件

			response.End();		

			

						

		}





		/// <summary>

		/// via dt generate SqlString

		/// 0 for creat a,b

		/// 1 for insert

		/// 2 for @a,@b

		/// </summary>

		/// <param name="dt"></param>

		/// <param name="type"></param>

		/// <returns></returns>



		private string CreatSqlString(DataTable dt,int type)

		{

			string temp="";

			for (int i=0;i<dt.Columns.Count;i++)

			{				

				if (type==0)		//for create table(a varchar)

				{

					temp+=FixColumName(dt.Columns[i].ColumnName);	

					temp+=" varchar";

				}

				else if (type==1)	//for insert table(a,b)

				{

					temp+=FixColumName(dt.Columns[i].ColumnName);	

				}

				else if (type==2)	//for @a,@b

				{

					temp+="@a_"+i;

				}



				if (i<dt.Columns.Count-1)

				{

					temp+=",\r\n";

				}

			}

			return temp;

		}



		public string FixColumName(string source)

		{

			string temp=source;

			ArrayList sp_array=SpecialArrayList();

			for (int i=0;i<sp_array.Count;i++)

			{

				if (source.IndexOf(sp_array[i].ToString())!=-1)

				{

					temp="["+source+"]";

					break;

				}

			}

			

			return temp;

		}



		private ArrayList SpecialArrayList()

		{

			ArrayList temp=new ArrayList();

			temp.Add(" ");

			temp.Add("INDEX");

			temp.Add("(");

			return temp;			

		}







        // you could have other overloads if you want to get creative...

        //public static string CreateWorkbook(DataSet ds)

        //{

        //    XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);

        //    XslTransform xt = new XslTransform();

        //    StreamReader reader = new

        //   StreamReader(typeof(WorkbookEngine).Assembly.GetManifestResourceStream(typeof(WorkbookEngine), "Excel.xsl"));

        //    XmlTextReader xRdr = new XmlTextReader(reader);

        //    xt.Load(xRdr, null, null);

        //    StringWriter sw = new StringWriter();

        //    xt.Transform(xmlDataDoc, null, sw, null);

        //    return sw.ToString();

        //}





	}

}

补充：.NET技术 ,  ASP.NET

上一个：MVC3自动封装实体更新数据的问题
下一个：gridview hyrperlinkfield 传值的参数带#问题