uploadfy导入Excel内容到数据库中4

最新推荐文章于 2016-08-30 10:41:05 发布

原创最新推荐文章于 2016-08-30 10:41:05 发布 · 95 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种使用C#从Excel文件中读取指定工作表的数据，并填充到System.Data.DataTable的方法。此方法利用了OleDb连接和适配器来实现数据的高效读取。

public int ReadFromExcel(string fileName, string sheetName, System.Data.DataTable dataTable)
{
OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
try
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
string sql = "SELECT * FROM [" + sheetName + "$]";

connection = new OleDbConnection(connectionString);
connection.Open();

OleDbCommand command = new OleDbCommand(sql, connection);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
DataSet excelOriginalData = new DataSet();

dataTable.Columns.Clear();
dataTable.Clear();

dataAdapter.Fill(dataTable);
}
catch (Exception e)
{
m_sMsg = e.Message;
return -1;
}
finally
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
return 1;
}