asp.net實現資料從DataTable匯入到Excel檔案並建立表的方法_實用技巧

本文執行個體講述了asp.net實現資料從DataTable匯入到Excel檔案並建立表的方法。分享給大家供大家參考，具體如下：

/// <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;}

調用方法:

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程式設計有所協助。