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;

}

调用方法:

asp.net实现数据从DataTable导入到Excel文件并创建表的方法

相关文章

快网idc优惠网

相关文章

微信

快网idc优惠网

QQ交流群