/// <summary>
/// 将 Excel 文件转成 DataTable 后,再把 DataTable中的数据写入表Products
/// </summary>
/// <param name="serverMapPathExcelAndFileName"></param>
/// <param name="excelFileRid"></param>
/// <returns></returns>
public static int WriteExcelToDataBase(string excelFileName)
{
int rowsCount = 0;
OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName+ ";" + "Extended Properties=Excel 8.0;");
objConn.Open();
try
{
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
for (int j = 0; j < schemaTable.Rows.Count; j++)
{
sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
DataTable excelDataTable = DbHelperSQL.ExcelToDataTable(excelFileName, sheetName, true);
if (excelDataTable.Columns.Count > 1)
{
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "Products";//数据库中的表名
sqlbulkcopy.WriteToServer(excelDataTable);
sqlbulkcopy.Close();
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
objConn.Close();
objConn.Dispose();
}
return rowsCount;
}
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="Path"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet ExcelToDS(string Path, string sheetName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;"; //HDR=Yes;
DataSet ds = null;
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter myCommand = null;
try
{
conn.Open();
string strExcel = "";
strExcel = "select * from [" + sheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
}
catch (SqlException ex)
{
throw ex;
}
finally
{
myCommand.Dispose();
conn.Close();
}
return ds;
}
}
/// <summary>
/// 将 Excel 文件转成 DataTable
/// </summary>
/// <param name="serverMapPathExcel">Excel文件及其路径</param>
/// <param name="strSheetName">工作表名,如:Sheet1</param>
/// <param name="isTitleOrDataOfFirstRow">True 第一行是标题,False 第一行是数据</param>
/// <returns>DataTable</returns>
public static DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow)
{
string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
if (isTitleOrDataOfFirstRow)
{
HDR = "YES";//第一行是标题
}
else
{
HDR = "NO";//第一行是数据
}
//源的定义
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + serverMapPathExcel + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";
//Sql语句
//string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
string strExcel = "select * from [" + strSheetName + "]";
//定义存放的数据表
DataSet ds = new DataSet();
//连接数据源
using (OleDbConnection conn = new OleDbConnection(strConn))
{
try
{
conn.Open();
//适配到数据源
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
adapter.Fill(ds, strSheetName);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
return ds.Tables[strSheetName];
}
#endregion