C# 讀取Excel File並導入SQL

其實一般要用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();
}

}