本文實例講述了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程序設計有所幫助。
