其實一般要用C#讀取EXCEL可以用oledb，但是我發現Github有人撰寫的.net dll更方便於是紀錄一下。
Excel DataReader
public void loadEachXls(string areaTB, int sheet)
{
// Excel 檔案位置
String tmpfilePath = ConfigurationManager.AppSettings["tmpfilePath"];
string filePath = tmpfilePath;
// 你要抓取 Excel檔裡的工作表名稱
//string set = "工作表1";
// 讀取 Excel檔案
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
// 創建讀取 Excel檔
IExcelDataReader excelRead = ExcelReaderFactory.CreateOpenXmlReader(stream);
// 將讀取到 Excel檔暫存至內存
DataSet result = excelRead.AsDataSet();
// 獲得 Excel檔的行與列的數目
int columns = result.Tables[sheet].Columns.Count;
int rows = result.Tables[sheet].Rows.Count;
using (SqlConnection cn = new SqlConnection("server=10.10.0.x;Connection Timeout=10;MultipleActiveResultSets=True;database=CMTS;uid=xxxxx;pwd=xxxxxxxxxx"))
{
cn.Open();
String deleteMac = "truncate table CMTS.[dbo]." + areaTB;
SqlCommand cmd = new SqlCommand(deleteMac, cn);
cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into CMTS.dbo." + areaTB + " (sid,bsr,node,area,penetration) VALUES (@v1,@v2,@v3,@v4,@v5)";
// 將資料讀取出來
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
string data = result.Tables[sheet].Rows[i][j].ToString();
}
cmd.Parameters.AddWithValue("@v1", result.Tables[sheet].Rows[i][0].ToString());
cmd.Parameters.AddWithValue("@v2", result.Tables[sheet].Rows[i][1].ToString());
cmd.Parameters.AddWithValue("@v3", result.Tables[sheet].Rows[i][2].ToString());
cmd.Parameters.AddWithValue("@v4", result.Tables[sheet].Rows[i][3].ToString());
cmd.Parameters.AddWithValue("@v5", "");
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
// 讀取完後一定要關閉
excelRead.Close();
}
}
