其实导入功能没啥搞头,就是insert,而且一般也不会直接insert,而是调用系统现有的方法导入。
主要是导出功能,在网上找了找,主要的方法都在System.Data.OleDb这个命名空间里写好了。
下面贴出我主要用的方法。
GetTableFromExcel#region GetTableFromExcel
/**//// <summary>
/// 从Excel获取指定的sheet名的表格数据
/// </summary>
/// <param name="sheetName"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public DataTable GetTableFromExcel(string sheetName, string filePath)
{
const string connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
DataTable dt = null;
if (!System.IO.File.Exists(filePath))
{
// don't find file
return null;
}
OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
try
{
conn.Open();
if (sheetName == null || sheetName.Trim().Length == 0)
{
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
}
else
{
sheetName += "$";
}
string strSQL = "Select * From [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
}
catch
{
}
return dt;
}
#endregion
/**//// <summary>
/// 从Excel获取指定的sheet名的表格数据
/// </summary>
/// <param name="sheetName"></param>
/// <param name="filePath"></param>
/// <returns></returns>
public DataTable GetTableFromExcel(string sheetName, string filePath)
{
const string connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
DataTable dt = null;
if (!System.IO.File.Exists(filePath))
{
// don't find file
return null;
}
OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
try
{
conn.Open();
if (sheetName == null || sheetName.Trim().Length == 0)
{
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
}
else
{
sheetName += "$";
}
string strSQL = "Select * From [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
}
catch
{
}
return dt;
}
#endregion
download