本文實例講述了asp.net實現(xiàn)數(shù)據(jù)從DataTable導入到Excel文件并創(chuàng)建表的方法。分享給大家供大家參考，具體如下：
/// summary>
/// 把數(shù)據(jù)從DataTable導入到Excel文件里
/// /summary>
/// param name="dataTable">數(shù)據(jù)源/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)
{
//導入數(shù)據(jù)
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();
}
}
