asp.net实现数据从DataTable导入到Excel文件并创建表的方法
本文实例讲述了asp.net实现数据从DataTable导入到Excel文件并创建表的方法。分享给大家供大家参考，具体如下：
///<summary>
///把数据从DataTable导入到Excel文件里
///</summary>
///<paramname="dataTable">数据源</param>
///<paramname="AbsoluteExcelFilePath">Excel文件的绝对路径</param>
///<paramname="TblColName">TBL里对应的列名</param>
///<paramname="ColumnName">Excel中对应的列名</param>
///<returns>操作成功返回True,失败返回False</returns>
publicstaticboolExportDataToExcel(DataTabledataTable,stringAbsoluteExcelFilePath,string[]TblColName,string[]ColumnName)
{
intk=0;
if(dataTable==null)returnfalse;
OleDbConnectionConn=newOleDbConnection();
try
{
stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+AbsoluteExcelFilePath+";Mode=ShareDenyNone;ExtendedProperties=Excel8.0;JetOLEDB:CreateSystemDatabase=True";
Conn=newOleDbConnection(strConn);
Conn.Open();
OleDbCommandcommand=Conn.CreateCommand();
stringstrSQL="";
if(dataTable.Columns!=null)
{
//建表
strSQL="CREATETABLE"+dataTable.TableName+"(";
for(inti=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(DataRowrowindataTable.Rows)
{
strSQL="insertinto"+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(Exceptionex)
{
Conn.Close();
thrownewException(ex.Message);
returnfalse;
}
Conn.Close();
returntrue;
}
调用方法:
DataSetds=(DataSet)Session["listMobile"];//获得要导出的表格的值
if(ds.Tables[0].Rows.Count<=0)
{
Page.RegisterStartupScript("","<mce:scripttype="text/javascript"><!--
alert('没有内容不能导出！')
//--></mce:script>");
}
else
{
//EXCEL页面的名称
string[]tableName={"["+DateTime.Now.ToString("yyyyMMddhhmmss")+"]"};
stringfileName=tools.CreateID()+".xls";
stringfilePath=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程序设计有所帮助。