asp.net实现数据从DataTable导入到Excel文件并创建表的方法（IIS必须启动32位）

本文实例讲述了asp.net实现数据从DataTable导入到Excel文件并创建表的方法。分享给大家供大家参考，具体如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

/// <summary> /// 把数据从DataTable导入到Excel文件里 /// </summary> /// <param name="dataTable">数据源</param> /// <param name="AbsoluteExcelFilePath">Excel文件的绝对路径</param> /// <param name="TblColName">TBL里对应的列名</param> /// <param name="ColumnName">Excel中对应的列名</param> /// <returns>操作成功返回True,失败返回False</returns> public static bool ExportDataToExcel(DataTable dataTable, string AbsoluteExcelFilePath, string [] TblColName, string [] ColumnName) {    int k = 0;    if (dataTable == null ) return false ;    OleDbConnection Conn = new OleDbConnection();    try    {     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AbsoluteExcelFilePath + ";Mode=Share Deny None;Extended Properties=Excel 8.0;Jet OLEDB:Create System Database=True" ;     Conn = new OleDbConnection(strConn);     Conn.Open();     OleDbCommand command = Conn.CreateCommand();     string strSQL = "" ;     if (dataTable.Columns != null )     {      //建表      strSQL = "CREATE TABLE " + dataTable.TableName + "(" ;      for ( int i = 0; i < ColumnName.Length; i++)      {       strSQL += ColumnName[i] + " TEXT," ;      }      strSQL = strSQL.Substring(0, strSQL.Length - 1);      strSQL += ")" ;      command.CommandText += strSQL;      command.ExecuteNonQuery();      if (dataTable.Rows.Count > 0)      {       //导入数据       foreach (DataRow row in dataTable.Rows)       {        strSQL = "insert into " + dataTable.TableName + "(" ;        for (k = 0; k < TblColName.Length; k++)        {         strSQL += ColumnName[k] + "," ;        }        strSQL = strSQL.Substring(0, strSQL.Length - 1);        strSQL += ") values( " ;        for (k = 0; k < TblColName.Length; k++)        {         strSQL += "'" + row[TblColName[k]] + "'," ;        }        strSQL = strSQL.Substring(0, strSQL.Length - 1);        strSQL += ")" ;        command.CommandText = strSQL;        command.ExecuteNonQuery();       }      }     }    }    catch (Exception ex)    {     Conn.Close();     throw new Exception(ex.Message);     return false ;    }    Conn.Close();    return true ; }

调用方法:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

DataSet ds = (DataSet)Session[ "listMobile" ]; //获得要导出的表格的值 if (ds.Tables[0].Rows.Count <= 0) {   Page.RegisterStartupScript( "" , "<mce:script type=" text/javascript"><!-- alert( '没有内容不能导出！' ) // --></mce:script>"); } else {   //EXCEL页面的名称   string [] tableName = { "[" +DateTime.Now.ToString( "yyyyMMddhhmmss" )+ "]" };   string fileName = tools.CreateID() + ".xls" ;   string filePath = Server.MapPath( "..//DownloadFiles//" + fileName);   if (tools.ExportDataToExcel(ds, filePath, tableName)== true )   {    Response.Clear();    Response.Buffer = true ;    Response.Charset = "GB2312" ;    Response.AppendHeader( "Content-Disposition" , "attachment;filename=" + fileName);    Response.ContentType = "application/vnd.ms-excel" ;    this .EnableViewState = false ;    Response.WriteFile(filePath);    Response.Flush();    if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath);    Response.Redirect( this .Request.UrlReferrer.AbsoluteUri, true );    Response.End();   } }

希望本文所述对大家asp.net程序设计有所帮助。