protected void Page_Load(object sender, EventArgs e) { if (!string.IsNullOrEmpty(Request.QueryString["city"])) { /// 查詢縣市代碼 ct_db._C_Item = Request.QueryString["city"].ToString().Trim(); DataTable CodeDt = ct_db.getCommonCode("02"); if (CodeDt.Rows.Count > 0) { CityName = CodeDt.Rows[0]["C_Item_Cn"].ToString(); } } if (!string.IsNullOrEmpty(Request.QueryString["listname"])) { /// 查詢table類別 switch (Request.QueryString["listname"].ToString().Trim()) { case "Population": //人口土地 CityTableClass = "人口土地"; break; case "Travel": //觀光 CityTableClass = "觀光"; break; case "Traffic": //交通 CityTableClass = "交通"; break; case "Farming": //農業 CityTableClass = "農業"; break; case "Industry": //產業 CityTableClass = "產業"; break; case "Retail": //零售 CityTableClass = "零售"; break; case "Safety": //智慧安全、治理 CityTableClass = "智慧安全、治理"; break; case "Energy": //能源 CityTableClass = "能源"; break; case "Health": //健康 CityTableClass = "健康"; break; case "Education": //教育 CityTableClass = "教育"; break; } } }
private string GetCompetence(string item) { string rVal = string.Empty; CodeTable_DB c_db = new CodeTable_DB(); c_db._C_Item = item; DataTable dt = c_db.getCommonCode("03"); if (dt.Rows.Count > 0) { rVal = dt.Rows[0]["C_Item_Cn"].ToString(); } return(rVal); }
protected void Page_Load(object sender, EventArgs e) { compstr = LogInfo.competence; if (!string.IsNullOrEmpty(Request.QueryString["city"])) { /// 查詢縣市代碼 ct_db._C_Item = Request.QueryString["city"].ToString().Trim(); DataTable CodeDt = ct_db.getCommonCode("02"); if (CodeDt.Rows.Count > 0) { CityName = CodeDt.Rows[0]["C_Item_Cn"].ToString(); } } }
protected void Page_Load(object sender, EventArgs e) { ///----------------------------------------------------- ///功 能: 查詢代碼檔 ///說 明: /// * Request["group"]: CodeTable Group 代碼 /// * Request["item"]: CodeTable Item 代碼 ///----------------------------------------------------- XmlDocument xDoc = new XmlDocument(); try { string group = (string.IsNullOrEmpty(Request["group"])) ? "" : Request["group"].ToString().Trim(); string item = (string.IsNullOrEmpty(Request["item"])) ? "" : Request["item"].ToString().Trim(); string token = (string.IsNullOrEmpty(Request["Token"])) ? "" : Request["Token"].ToString().Trim(); if (Common.VeriftyToken(token)) { ct_db._C_Item = item; DataTable dt = ct_db.getCommonCode(group); string xmlstr = string.Empty; xmlstr = DataTableToXml.ConvertDatatableToXmlByAttribute(dt, "dataList", "data_item"); xmlstr = "<?xml version='1.0' encoding='utf-8'?><root>" + xmlstr + "</root>"; xDoc.LoadXml(xmlstr); } else { xDoc = ExceptionUtil.GetErrorMassageDocument("TokenFail"); } } catch (Exception ex) { xDoc = ExceptionUtil.GetExceptionDocument(ex); } Response.ContentType = System.Net.Mime.MediaTypeNames.Text.Xml; xDoc.Save(Response.Output); }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Re_CityNo", typeof(string)).MaxLength = 2;; dt.Columns.Add("Re_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Re_StreetStandYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetStand", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_Re_StreetVendorYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendor", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_StreetVendorIncomeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendorIncome", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_StreetVendorAvgIncomeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_StreetVendorAvgIncome", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_RetailBusinessSales", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesRateYearDesc", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_RetailBusinessSalesRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Re_RetailBusinessAvgSalesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Re_RetailBusinessAvgSales", typeof(string)).MaxLength = 20; dt.Columns.Add("Re_CreateDate", typeof(DateTime)); dt.Columns.Add("Re_CreateID", typeof(string)); dt.Columns.Add("Re_CreateName", typeof(string)); dt.Columns.Add("Re_Status", typeof(string)); dt.Columns.Add("Re_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是零售的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 8) { throw new Exception("請檢查是否為零售的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "攤販經營家數" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "攤販從業人數") { throw new Exception("請檢查是否為零售的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = RL_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Re_CityNo"] = cityNo; //縣市代碼 row["Re_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Re_StreetStandYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //攤販經營家數-資料年度(民國年) row["Re_StreetStand"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //攤販經營家數-家 row["Re_Re_StreetVendorYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //攤販從業人數-資料年度(民國年) row["Re_StreetVendor"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //攤販從業人數-人 row["Re_StreetVendorIncomeYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //攤販全年收入-資料年度(民國年) row["Re_StreetVendorIncome"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //攤販全年收入-千元 row["Re_StreetVendorAvgIncomeYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //攤販全年平均收入-資料年度(民國年) row["Re_StreetVendorAvgIncome"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //攤販全年平均收入-千元 row["Re_RetailBusinessSalesYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //零售業營利事業銷售額-資料年度(民國年) row["Re_RetailBusinessSales"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //零售業營利事業銷售額-千元 row["Re_RetailBusinessSalesRateYearDesc"] = sheet.GetRow(1).GetCell(6).ToString().Trim(); //零售業營利事業銷售額成長率-年度敘述 EX: 106-107年 row["Re_RetailBusinessSalesRate"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //零售業營利事業銷售額成長率 row["Re_RetailBusinessAvgSalesYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //零售業營利事業平均每家銷售額-資料年度(民國年) row["Re_RetailBusinessAvgSales"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //零售業營利事業平均每家銷售額-千元 row["Re_CreateDate"] = dtNow; row["Re_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Re_CreateName"] = LogInfo.name; //上傳者姓名 row["Re_Status"] = "A"; row["Re_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt, strErrorMsg); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:零售 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('零售匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:零售 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } else { strErrorMsg = "連線失敗請重新登入"; Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Sf_CityNo", typeof(string)).MaxLength = 2; dt.Columns.Add("Sf_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Sf_SoilAreaYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_SoilArea", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_UnderWaterAreaYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_UnderWaterArea", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_PM25QuantityYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_PM25Quantity", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_10KPeopleFireTimesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_10KPeopleFireTimes", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_100KPeopleBurglaryTimesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_100KPeopleBurglaryTimes", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_BurglaryClearanceRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_BurglaryClearanceRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Sf_100KPeopleCriminalCaseTimesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_100KPeopleCriminalCaseTimes", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_CriminalCaseClearanceRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_CriminalCaseClearanceRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Sf_100KPeopleViolentCrimesTimesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_100KPeopleViolentCrimesTimes", typeof(string)).MaxLength = 20; dt.Columns.Add("Sf_ViolentCrimesClearanceRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_ViolentCrimesClearanceRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Sf_AQI100DayRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_AQI100DayRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Sf_AirQualityAQIAverageValueYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Sf_AirQualityAQIAverageValue", typeof(string)).MaxLength = 50; dt.Columns.Add("Sf_CreateDate", typeof(DateTime)); dt.Columns.Add("Sf_CreateID", typeof(string)); dt.Columns.Add("Sf_CreateName", typeof(string)); dt.Columns.Add("Sf_Status", typeof(string)); dt.Columns.Add("Sf_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是安全的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 13) { throw new Exception("請檢查是否為安全的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "土壤污染控制場址面積" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "地下水受污染使用限制面積") { throw new Exception("請檢查是否為安全的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = SF_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 2; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Sf_CityNo"] = cityNo; //縣市代碼 row["Sf_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Sf_SoilAreaYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //土壤污染控制場址面積-資料年度(民國年) row["Sf_SoilArea"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //土壤污染控制場址面積-平方公尺 row["Sf_UnderWaterAreaYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //地下水受污染使用限制面積-資料年度(民國年) row["Sf_UnderWaterArea"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //地下水受污染使用限制面積-平方公尺 row["Sf_PM25QuantityYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //總懸浮微粒排放量-資料年度(民國年) row["Sf_PM25Quantity"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //總懸浮微粒排放量-公噸 row["Sf_10KPeopleFireTimesYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //每萬人火災發生次數-資料年度(民國年) row["Sf_10KPeopleFireTimes"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //每萬人火災發生次數-次 row["Sf_100KPeopleBurglaryTimesYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //每十萬人竊盜案發生數-資料年度(民國年) row["Sf_100KPeopleBurglaryTimes"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //每十萬人竊盜案發生數-件 row["Sf_BurglaryClearanceRateYear"] = sheet.GetRow(1).GetCell(6).ToString().Trim().Replace("年", ""); //竊盜案破獲率-資料年度(民國年) row["Sf_BurglaryClearanceRate"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //竊盜案破獲率-% row["Sf_100KPeopleCriminalCaseTimesYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //每十萬人刑案發生數-資料年度(民國年) row["Sf_100KPeopleCriminalCaseTimes"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //每十萬人刑案發生數-件 row["Sf_CriminalCaseClearanceRateYear"] = sheet.GetRow(1).GetCell(8).ToString().Trim().Replace("年", ""); //刑案破獲率-資料年度(民國年) row["Sf_CriminalCaseClearanceRate"] = sheet.GetRow(j).GetCell(8).ToString().Trim(); //刑案破獲率-% row["Sf_100KPeopleViolentCrimesTimesYear"] = sheet.GetRow(1).GetCell(9).ToString().Trim().Replace("年", ""); //每十萬人暴力犯罪發生數-資料年度(民國年) row["Sf_100KPeopleViolentCrimesTimes"] = sheet.GetRow(j).GetCell(9).ToString().Trim(); //每十萬人暴力犯罪發生數-件 row["Sf_ViolentCrimesClearanceRateYear"] = sheet.GetRow(1).GetCell(10).ToString().Trim().Replace("年", ""); //暴力犯罪破獲率-資料年度(民國年) row["Sf_ViolentCrimesClearanceRate"] = sheet.GetRow(j).GetCell(10).ToString().Trim(); //暴力犯罪破獲率-% row["Sf_AQI100DayRateYear"] = sheet.GetRow(1).GetCell(11).ToString().Trim().Replace("年", ""); //AQI>100日數比例(>100表示對健康已有影響)-資料年度(民國年) row["Sf_AQI100DayRate"] = sheet.GetRow(j).GetCell(11).ToString().Trim(); //AQI>100日數比例(>100表示對健康已有影響)-% row["Sf_AirQualityAQIAverageValueYear"] = sheet.GetRow(1).GetCell(12).ToString().Trim().Replace("年", ""); //空氣品質AQI平均值-資料年度(民國年) row["Sf_AirQualityAQIAverageValue"] = sheet.GetRow(j).GetCell(12).ToString().Trim(); //空氣品質AQI平均值 row["Sf_CreateDate"] = dtNow; row["Sf_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Sf_CreateName"] = LogInfo.name; //上傳者姓名 row["Sf_Status"] = "A"; row["Sf_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt, strErrorMsg); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:安全 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('安全匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:安全 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } else { strErrorMsg = "連線失敗請重新登入"; Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Hea_CityNo", typeof(string)).MaxLength = 2; dt.Columns.Add("Hea_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Hea_10KPeopleBedYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_10KPeopleBed", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_10KPeopleAcuteGeneralBedYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_10KPeopleAcuteGeneralBed", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_10KpeoplePractitionerYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_10KpeoplePractitioner", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_DisabledPersonOfCityRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_DisabledPersonOfCityRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Hea_LongTermPersonYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_LongTermPerson", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_LongTermPersonOfOldMenRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_LongTermPersonOfOldMenRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Hea_MedicalInstitutionsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_MedicalInstitutions", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_MedicalInstitutionsAvgPersonYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_MedicalInstitutionsAvgPerson", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_GOVPayOfNHIYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_GOVPayOfNHI", typeof(string)).MaxLength = 20; dt.Columns.Add("Hea_DementedPopulationYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_DementedPopulation", typeof(string)).MaxLength = 50; dt.Columns.Add("Hea_DiabetesDeathsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Hea_DiabetesDeaths", typeof(string)).MaxLength = 50; dt.Columns.Add("Hea_CreateDate", typeof(DateTime)); dt.Columns.Add("Hea_CreateID", typeof(string)); dt.Columns.Add("Hea_CreateName", typeof(string)); dt.Columns.Add("Hea_Status", typeof(string)); dt.Columns.Add("Hea_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是健康的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 12) { throw new Exception("請檢查是否為健康的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "每萬人口病床數" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "每萬人口急性一般病床數") { throw new Exception("請檢查是否為健康的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = HE_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Hea_CityNo"] = cityNo; //縣市代碼 row["Hea_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Hea_10KPeopleBedYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //每萬人口病床數-資料年度(民國年) row["Hea_10KPeopleBed"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //每萬人口病床數-床 row["Hea_10KPeopleAcuteGeneralBedYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //每萬人口急性一般病床數-資料年度(民國年) row["Hea_10KPeopleAcuteGeneralBed"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //每萬人口急性一般病床數-床 row["Hea_10KpeoplePractitionerYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //每萬人執業醫事人員數-資料年度(民國年) row["Hea_10KpeoplePractitioner"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //每萬人執業醫事人員數-人 row["Hea_DisabledPersonOfCityRateYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //身心障礙人口占全縣(市)總人口比率-資料年度(民國年) row["Hea_DisabledPersonOfCityRate"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //身心障礙人口占全縣(市)總人口比率-% row["Hea_LongTermPersonYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //長期照顧機構可供進駐人數-資料年度(民國年) row["Hea_LongTermPerson"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //長期照顧機構可供進駐人數-人 row["Hea_LongTermPersonOfOldMenRateYear"] = sheet.GetRow(1).GetCell(6).ToString().Trim().Replace("年", ""); //長期照顧機構可供進駐人數佔預估失能老人需求比例-資料年度(民國年) row["Hea_LongTermPersonOfOldMenRate"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //長期照顧機構可供進駐人數佔預估失能老人需求比例-% row["Hea_MedicalInstitutionsYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //醫療機構數-資料年度(民國年) row["Hea_MedicalInstitutions"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //醫療機構數-所 row["Hea_MedicalInstitutionsAvgPersonYear"] = sheet.GetRow(1).GetCell(8).ToString().Trim().Replace("年", ""); //平均每一醫療機構服務人數-資料年度(民國年) row["Hea_MedicalInstitutionsAvgPerson"] = sheet.GetRow(j).GetCell(8).ToString().Trim(); //平均每一醫療機構服務人數-人/所 row["Hea_GOVPayOfNHIYear"] = sheet.GetRow(1).GetCell(9).ToString().Trim().Replace("年", ""); //政府部門醫療保健支出-資料年度(民國年) row["Hea_GOVPayOfNHI"] = sheet.GetRow(j).GetCell(9).ToString().Trim(); //政府部門醫療保健支出-千元 row["Hea_DementedPopulationYear"] = sheet.GetRow(1).GetCell(10).ToString().Trim().Replace("年", ""); //失智人口-資料年度(民國年) row["Hea_DementedPopulation"] = sheet.GetRow(j).GetCell(10).ToString().Trim(); //失智人口-人 row["Hea_DiabetesDeathsYear"] = sheet.GetRow(1).GetCell(11).ToString().Trim().Replace("年", ""); //糖尿病死亡人數-資料年度(民國年) row["Hea_DiabetesDeaths"] = sheet.GetRow(j).GetCell(11).ToString().Trim(); //糖尿病死亡人數-人 row["Hea_CreateDate"] = dtNow; row["Hea_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Hea_CreateName"] = LogInfo.name; //上傳者姓名 row["Hea_Status"] = "A"; row["Hea_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:健康 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('健康匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:健康 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } else { strErrorMsg = "連線失敗請重新登入"; Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } }
protected void Page_Load(object sender, EventArgs e) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("MR_CityNo", typeof(string)).MaxLength = 2; dt.Columns.Add("MR_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("MR_MayorYear", typeof(string)).MaxLength = 3; dt.Columns.Add("MR_Mayor", typeof(string)).MaxLength = 50; dt.Columns.Add("MR_ViceMayorYear", typeof(string)).MaxLength = 3; dt.Columns.Add("MR_ViceMayor", typeof(string)).MaxLength = 50; dt.Columns.Add("MR_PoliticalPartyYear", typeof(string)).MaxLength = 3; dt.Columns.Add("MR_PoliticalParty", typeof(string)).MaxLength = 50; dt.Columns.Add("MR_AdAreaYear", typeof(string)).MaxLength = 3; dt.Columns.Add("MR_AdArea", typeof(string)).MaxLength = 10; dt.Columns.Add("MR_CreateDate", typeof(DateTime)); dt.Columns.Add("MR_CreateID", typeof(string)); dt.Columns.Add("MR_CreateName", typeof(string)); dt.Columns.Add("MR_Status", typeof(string)); dt.Columns.Add("MR_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是市長/副市長的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 5) { throw new Exception("請檢查是否為市長副市長的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "直轄市/縣市長" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "副縣/市長") { throw new Exception("請檢查是否為市長副市長的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = MR_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["MR_CityNo"] = cityNo; //縣市代碼 row["MR_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["MR_MayorYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //直轄市/縣市長-資料年度(民國年) row["MR_Mayor"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //直轄市/縣市長 row["MR_ViceMayorYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //副縣/市長-資料年度(民國年) row["MR_ViceMayor"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //副縣/市長 row["MR_PoliticalPartyYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //推薦政黨-資料年度(民國年) row["MR_PoliticalParty"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //推薦政黨 row["MR_AdAreaYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //行政區數-資料年度(民國年) row["MR_AdArea"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //行政區數 row["MR_CreateDate"] = dtNow; row["MR_CreateID"] = LogInfo.mGuid; //上傳者GUID row["MR_CreateName"] = LogInfo.name; //上傳者姓名 row["MR_Status"] = "A"; row["MR_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt, strErrorMsg); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:市長副市長 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('市長副市長匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:市長副市長 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Edu_CityNo", typeof(string)).MaxLength = 2; dt.Columns.Add("Edu_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Edu_15upJSDownRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_15upJSDownRate", typeof(string)).MaxLength = 7; dt.Columns.Add("Edu_15upHSRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_15upHSRate", typeof(string)).MaxLength = 7; dt.Columns.Add("Edu_15upUSUpRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_15upUSUpRate", typeof(string)).MaxLength = 7; dt.Columns.Add("Edu_ESStudentDropOutRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESStudentDropOutRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_JSStudentDropOutRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_JSStudentDropOutRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_ESStudentsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESStudents", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_JSStudentsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_JSStudents", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_HSStudentsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_HSStudents", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESToHSIndigenousYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESToHSIdigenous", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESToHSIndigenousRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESToHSIndigenousRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_ESJSNewInhabitantsYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESJSNewInhabitants", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESJSNewInhabitantsRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESToJSNewInhabitantsRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_ESJSTeachersYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESJSTeachers", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESJSTeachersOfStudentRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESJSTeachersOfStudentRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_BudgetYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_Budget", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_BudgetUpRateYearDesc", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_BudgetUpRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_ESToHSAvgBudgetYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESToHSAvgBudget", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESJSPCNumYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESJSPCNum", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_ESJSAvgPCNumYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_ESJSAvgPCNum", typeof(string)).MaxLength = 20; dt.Columns.Add("Edu_HighschoolDownRemoteAreasYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Edu_HighschoolDownRemoteAreas", typeof(string)).MaxLength = 50; dt.Columns.Add("Edu_CreateDate", typeof(DateTime)); dt.Columns.Add("Edu_CreateID", typeof(string)); dt.Columns.Add("Edu_CreateName", typeof(string)); dt.Columns.Add("Edu_Status", typeof(string)); dt.Columns.Add("Edu_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是教育的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 21) { throw new Exception("請檢查是否為教育的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "15歲以上民間人口之教育程度結構-國中及以下" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "15歲以上民間人口之教育程度結構-高中(職)") { throw new Exception("請檢查是否為教育的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = EN_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Edu_CityNo"] = cityNo; //縣市代碼 row["Edu_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Edu_15upJSDownRateYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-國中及以下-資料年度 row["Edu_15upJSDownRate"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //15歲以上民間人口之教育程度結構-國中及以下 row["Edu_15upHSRateYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-高中(職)-資料年度 row["Edu_15upHSRate"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //15歲以上民間人口之教育程度結構-高中(職) row["Edu_15upUSUpRateYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //15歲以上民間人口之教育程度結構-大專及以上-資料年度 row["Edu_15upUSUpRate"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //15歲以上民間人口之教育程度結構-大專及以上 row["Edu_ESStudentDropOutRateYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //國小學生輟學率-資料年度 row["Edu_ESStudentDropOutRate"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //國小學生輟學率 row["Edu_JSStudentDropOutRateYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //國中學生輟學率-資料年度 row["Edu_JSStudentDropOutRate"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //國中學生輟學率 row["Edu_ESStudentsYear"] = sheet.GetRow(1).GetCell(6).ToString().Trim().Replace("年", ""); //國小總學生數-資料年度 row["Edu_ESStudents"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //國小總學生數 row["Edu_JSStudentsYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //國中總學生數-資料年度 row["Edu_JSStudents"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //國中總學生數 row["Edu_HSStudentsYear"] = sheet.GetRow(1).GetCell(8).ToString().Trim().Replace("年", ""); //高中(職)總學生數-資料年度 row["Edu_HSStudents"] = sheet.GetRow(j).GetCell(8).ToString().Trim(); //高中(職)總學生數 row["Edu_ESToHSIndigenousYear"] = sheet.GetRow(1).GetCell(9).ToString().Trim().Replace("年", ""); //國小-高中(職)原住民學生數-資料年度 row["Edu_ESToHSIdigenous"] = sheet.GetRow(j).GetCell(9).ToString().Trim(); //國小-高中(職)原住民學生數 row["Edu_ESToHSIndigenousRateYear"] = sheet.GetRow(1).GetCell(10).ToString().Trim().Replace("年", ""); //國小-高中(職)原住民學生數比例-資料年度 row["Edu_ESToHSIndigenousRate"] = sheet.GetRow(j).GetCell(10).ToString().Trim(); //國小-高中(職)原住民學生數比例 row["Edu_ESJSNewInhabitantsYear"] = sheet.GetRow(1).GetCell(11).ToString().Trim().Replace("年", ""); //國中小新住民人數-資料年度 row["Edu_ESJSNewInhabitants"] = sheet.GetRow(j).GetCell(11).ToString().Trim(); //國中小新住民人數 row["Edu_ESJSNewInhabitantsRateYear"] = sheet.GetRow(1).GetCell(12).ToString().Trim().Replace("年", ""); //國中小新住民學生比例-資料年度 row["Edu_ESToJSNewInhabitantsRate"] = sheet.GetRow(j).GetCell(12).ToString().Trim(); //國中小新住民學生比例 row["Edu_ESJSTeachersYear"] = sheet.GetRow(1).GetCell(13).ToString().Trim().Replace("年", ""); //國中小教師數-資料年度 row["Edu_ESJSTeachers"] = sheet.GetRow(j).GetCell(13).ToString().Trim(); //國中小教師數 row["Edu_ESJSTeachersOfStudentRateYear"] = sheet.GetRow(1).GetCell(14).ToString().Trim().Replace("年", ""); //國中小生師比(平均每位教師教導學生數)-資料年度 row["Edu_ESJSTeachersOfStudentRate"] = sheet.GetRow(j).GetCell(14).ToString().Trim(); //國中小生師比(平均每位教師教導學生數) row["Edu_BudgetYear"] = sheet.GetRow(1).GetCell(15).ToString().Trim().Replace("年", ""); //教育預算-資料年度 row["Edu_Budget"] = sheet.GetRow(j).GetCell(15).ToString().Trim(); //教育預算 row["Edu_BudgetUpRateYearDesc"] = sheet.GetRow(1).GetCell(16).ToString().Trim().Replace("年", ""); //教育預算成長率-資料年度敘述 row["Edu_BudgetUpRate"] = sheet.GetRow(j).GetCell(16).ToString().Trim(); //教育預算成長率 row["Edu_ESToHSAvgBudgetYear"] = sheet.GetRow(1).GetCell(17).ToString().Trim().Replace("年", ""); //國小-高中(職)平均每人教育預算-資料年度 row["Edu_ESToHSAvgBudget"] = sheet.GetRow(j).GetCell(17).ToString().Trim(); //國小-高中(職)平均每人教育預算 row["Edu_ESJSPCNumYear"] = sheet.GetRow(1).GetCell(18).ToString().Trim().Replace("年", ""); //國中小教學電腦數-資料年度 row["Edu_ESJSPCNum"] = sheet.GetRow(j).GetCell(18).ToString().Trim(); //國中小教學電腦數 row["Edu_ESJSAvgPCNumYear"] = sheet.GetRow(1).GetCell(19).ToString().Trim().Replace("年", ""); //國中小平均每人教學電腦數-資料年度 row["Edu_ESJSAvgPCNum"] = sheet.GetRow(j).GetCell(19).ToString().Trim(); //國中小平均每人教學電腦數 row["Edu_HighschoolDownRemoteAreasYear"] = sheet.GetRow(1).GetCell(20).ToString().Trim().Replace("年", ""); //高級中等以下偏遠地區學校-資料年度 row["Edu_HighschoolDownRemoteAreas"] = sheet.GetRow(j).GetCell(20).ToString().Trim(); //高級中等以下偏遠地區學校 row["Edu_CreateDate"] = dtNow; row["Edu_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Edu_CreateName"] = LogInfo.name; //上傳者姓名 row["Edu_Status"] = "A"; row["Edu_Version"] = strMaxVersion; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt); //匯入 myTrans.Commit(); //最後再commit } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:教育 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('教育匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:教育 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } }
protected void Page_Load(object sender, EventArgs e) { //讀取Token值 string token = (string.IsNullOrEmpty(Request["InfoToken"])) ? "" : Request["InfoToken"].ToString().Trim(); if (VeriftyToken(token)) { //建立共用connection & transaction SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"].ToString()); oConn.Open(); SqlCommand oCmd = new SqlCommand(); oCmd.Connection = oConn; SqlTransaction myTrans = oConn.BeginTransaction(); oCmd.Transaction = myTrans; //建立DataTable Bulk Copy用 DataTable dt = new DataTable(); dt.Columns.Add("Tra_CityNo", typeof(string)).MaxLength = 2; dt.Columns.Add("Tra_CityName", typeof(string)).MaxLength = 10; dt.Columns.Add("Tra_PublicTransportRateYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_PublicTransportRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Tra_CarParkTimeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_CarParkTime", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_CarRoadsidParkSpaceYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_CarRoadsidParkSpace", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_CarRoadOutsideParkSpaceYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_CarRoadOutsideParkSpace", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_10KHaveCarParkYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_10KHaveCarPark", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_CarCountYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_CarCount", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_100HaveCarYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_100HaveCar", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_100HaveCarRateYearDec", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_100HaveCarRate", typeof(string)).MaxLength = 50; dt.Columns.Add("Tra_10KMotoIncidentsNumYear", typeof(string)); dt.Columns.Add("Tra_10KMotoIncidentsNum", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_100KNumberOfCasualtiesYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_100KNumberOfCasualties", typeof(string)).MaxLength = 20; dt.Columns.Add("Tra_PlaceCarParkTimeYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_PlaceCarParkTime", typeof(string)).MaxLength = 50; dt.Columns.Add("Tra_RoadTrafficAccidentYear", typeof(string)).MaxLength = 3; dt.Columns.Add("Tra_RoadTrafficAccident", typeof(string)).MaxLength = 50; dt.Columns.Add("Tra_CreateDate", typeof(DateTime)); dt.Columns.Add("Tra_CreateID", typeof(string)); dt.Columns.Add("Tra_CreateName", typeof(string)); dt.Columns.Add("Tra_Status", typeof(string)); dt.Columns.Add("Tra_Version", typeof(int)); try { HttpFileCollection uploadFiles = Request.Files;//檔案集合 HttpPostedFile aFile = uploadFiles[0]; //判斷有沒有檔案 if (uploadFiles.Count < 1 || aFile.FileName == "") { throw new Exception("請選擇檔案"); } //有檔案繼續往下做 if (uploadFiles.Count > 0) { string extension = (System.IO.Path.GetExtension(aFile.FileName) == "") ? "" : System.IO.Path.GetExtension(aFile.FileName); if (extension != ".xls" && extension != ".xlsx") { throw new Exception("請選擇xls或xlsx檔案上傳"); } IWorkbook workbook;// = new HSSFWorkbook();//创建Workbook对象 workbook = new XSSFWorkbook(aFile.InputStream); ISheet sheet = workbook.GetSheetAt(0);//當前sheet //簡易判斷這份Excel是不是交通的Excel int cellsCount = sheet.GetRow(0).Cells.Count; //1.判斷表頭欄位數 if (cellsCount != 13) { throw new Exception("請檢查是否為交通的匯入檔案"); } //2.檢查欄位名稱 if (sheet.GetRow(0).GetCell(1).ToString().Trim() != "通勤學民眾運具次數之公共運具市佔率" || sheet.GetRow(0).GetCell(2).ToString().Trim() != "自小客車在居家附近每次尋找停車位時間") { throw new Exception("請檢查是否為交通的匯入檔案"); } //取得當前最大版次 (+1變成現在版次) strMaxVersion = TC_DB.getMaxVersin() + 1; //取得代碼檔 CodeTable_DB code_db = new CodeTable_DB(); DataTable dtCode = code_db.getCommonCode("02"); string cityNo = string.Empty; //資料從第四筆開始 最後一筆是合計不進資料庫 for (int j = 3; j < sheet.PhysicalNumberOfRows - 1; j++) { if (sheet.GetRow(j).GetCell(0).ToString().Trim() != "" && sheet.GetRow(j).GetCell(0).ToString().Trim() != "全台平均") { DataRow row = dt.NewRow(); cityNo = Common.GetCityCodeItem(dtCode, sheet.GetRow(j).GetCell(0).ToString().Trim());//縣市代碼 if (cityNo == "") { throw new Exception("第" + (j + 1) + "筆資料:" + sheet.GetRow(j).GetCell(0).ToString().Trim() + "不是一個正確的縣市名稱"); } strErrorMsg = "行數:第" + (j + 1).ToString() + " 筆<br>"; row["Tra_CityNo"] = cityNo; //縣市代碼 row["Tra_CityName"] = sheet.GetRow(j).GetCell(0).ToString().Trim(); //縣市名稱 row["Tra_PublicTransportRateYear"] = sheet.GetRow(1).GetCell(1).ToString().Trim().Replace("年", ""); //通勤學民眾運具次數之公共運具市佔率-資料年度(民國年) row["Tra_PublicTransportRate"] = sheet.GetRow(j).GetCell(1).ToString().Trim(); //通勤學民眾運具次數之公共運具市佔率-% row["Tra_CarParkTimeYear"] = sheet.GetRow(1).GetCell(2).ToString().Trim().Replace("年", ""); //自小客車在居家附近每次尋找停車位時間-資料年度(民國年) row["Tra_CarParkTime"] = sheet.GetRow(j).GetCell(2).ToString().Trim(); //自小客車在居家附近每次尋找停車位時間-分鐘 row["Tra_CarRoadsidParkSpaceYear"] = sheet.GetRow(1).GetCell(3).ToString().Trim().Replace("年", ""); //小汽車路邊及路外停車位-資料年度(民國年) row["Tra_CarRoadsidParkSpace"] = sheet.GetRow(j).GetCell(3).ToString().Trim(); //小汽車路邊及路外停車位-個 row["Tra_CarRoadOutsideParkSpaceYear"] = sheet.GetRow(1).GetCell(4).ToString().Trim().Replace("年", ""); //小汽車路邊及路外停車位-資料年度(民國年) row["Tra_CarRoadOutsideParkSpace"] = sheet.GetRow(j).GetCell(4).ToString().Trim(); //小汽車路邊及路外停車位-個 row["Tra_10KHaveCarParkYear"] = sheet.GetRow(1).GetCell(5).ToString().Trim().Replace("年", ""); //每萬輛小型車擁有路外及路邊停車位數-資料年度(民國年) row["Tra_10KHaveCarPark"] = sheet.GetRow(j).GetCell(5).ToString().Trim(); //每萬輛小型車擁有路外及路邊停車位數-位/萬輛 row["Tra_CarCountYear"] = sheet.GetRow(1).GetCell(6).ToString().Trim().Replace("年", ""); //汽車登記數-資料年度(民國年) row["Tra_CarCount"] = sheet.GetRow(j).GetCell(6).ToString().Trim(); //汽車登記數-輛 row["Tra_100HaveCarYear"] = sheet.GetRow(1).GetCell(7).ToString().Trim().Replace("年", ""); //每百人擁有汽車數-資料年度(民國年) row["Tra_100HaveCar"] = sheet.GetRow(j).GetCell(7).ToString().Trim(); //每百人擁有汽車數-輛 row["Tra_100HaveCarRateYearDec"] = sheet.GetRow(1).GetCell(8).ToString().Trim(); //每百人擁有汽車數成長率- ex:105-106年成長率 row["Tra_100HaveCarRate"] = sheet.GetRow(j).GetCell(8).ToString().Trim(); //每百人擁有汽車數成長率-% row["Tra_10KMotoIncidentsNumYear"] = sheet.GetRow(1).GetCell(9).ToString().Trim().Replace("年", ""); //每萬輛機動車肇事數-資料年度(民國年) row["Tra_10KMotoIncidentsNum"] = sheet.GetRow(j).GetCell(9).ToString().Trim(); //每萬輛機動車肇事數-次 row["Tra_100KNumberOfCasualtiesYear"] = sheet.GetRow(1).GetCell(10).ToString().Trim().Replace("年", "");; //每十萬人道路交通事故死傷人數-資料年度(民國年) row["Tra_100KNumberOfCasualties"] = sheet.GetRow(j).GetCell(10).ToString().Trim(); //每十萬人道路交通事故死傷人數-人 row["Tra_PlaceCarParkTimeYear"] = sheet.GetRow(1).GetCell(11).ToString().Trim().Replace("年", "");; //自小客車工作(上學)場所附近平均每次尋找停車位時間-資料年度(民國年) row["Tra_PlaceCarParkTime"] = sheet.GetRow(j).GetCell(11).ToString().Trim(); //自小客車工作(上學)場所附近平均每次尋找停車位時間-分鐘 row["Tra_RoadTrafficAccidentYear"] = sheet.GetRow(1).GetCell(12).ToString().Trim().Replace("年", "");; //道路交通事故-資料年度(民國年) row["Tra_RoadTrafficAccident"] = sheet.GetRow(j).GetCell(12).ToString().Trim(); //道路交通事故-分鐘 row["Tra_CreateDate"] = dtNow; //建立時間 row["Tra_CreateID"] = LogInfo.mGuid; //上傳者GUID row["Tra_CreateName"] = LogInfo.name; //上傳者姓名 row["Tra_Status"] = "A"; //資料狀態 row["Tra_Version"] = strMaxVersion; //版次 dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { strErrorMsg = ""; BeforeBulkCopy(oConn, myTrans); //檢查資料表裡面是不是有該年的資料 DoBulkCopy(myTrans, dt); //匯入 //最後再commit myTrans.Commit(); } } } catch (Exception ex) { strErrorMsg += "錯誤訊息:" + ex.Message + "<br>"; strErrorMsg += "(欄位名稱請參考上傳範例檔)"; myTrans.Rollback(); } finally { oCmd.Connection.Close(); oConn.Close(); if (strErrorMsg == "") { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:交通 , 狀態:上傳成功"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('交通匯入成功');</script>"); } else { /// Log idl_db._IDL_Type = "ISTI"; idl_db._IDL_IP = Common.GetIPv4Address(); idl_db._IDL_Description = "檔案類別:交通 , 狀態:上傳失敗"; idl_db._IDL_ModId = LogInfo.mGuid; idl_db._IDL_ModName = LogInfo.name; idl_db.addLog(); Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } } } else { strErrorMsg = "連線失敗請重新登入"; Response.Write("<script type='text/JavaScript'>parent.feedbackFun('" + strErrorMsg.Replace("'", "") + "');</script>"); } }
private void DoCityPlanTable(IWorkbook workbook, SqlConnection oConn, SqlTransaction myTrans) { /// 查詢縣市代碼 DataTable CodeDt = ct_db.getCommonCode("02"); /// 建立縣市計畫 DataTable DataTable dt = CreateCityPlanTable(); for (int CitySheet = 5; CitySheet < 27; CitySheet++) { ISheet sheet = workbook.GetSheetAt(CitySheet); string CityCode = Common.GetCityCodeItem(CodeDt, sheet.SheetName.Trim()); /// 抓最大版次+1 int maxV = cpt_db.getMaxVersion(CityCode) + 1; /// 資料從第3筆開始 for (int i = 1; i < sheet.PhysicalNumberOfRows; i++) { if (sheet.GetRow(i).GetCell(1) == null) { continue; } else { if (sheet.GetRow(i).GetCell(1).ToString() == "") { continue; } } err = "Sheet Name:" + sheet.SheetName.Trim() + "<br>"; err += "行數:第 " + (i + 1).ToString() + " 筆"; DataRow row = dt.NewRow(); row["CP_City"] = sheet.SheetName.Trim(); row["CP_CityCode"] = CityCode; row["CP_PlanSchedule"] = (sheet.GetRow(i).GetCell(0) == null) ? "" : sheet.GetRow(i).GetCell(0).ToString().Trim(); row["CP_No"] = (sheet.GetRow(i).GetCell(1) == null) ? "" : sheet.GetRow(i).GetCell(1).ToString().Trim(); row["CP_PlanType"] = (sheet.GetRow(i).GetCell(2) == null) ? "" : sheet.GetRow(i).GetCell(2).ToString().Trim(); row["CP_PlanTypeDetail"] = (sheet.GetRow(i).GetCell(3) == null) ? "" : sheet.GetRow(i).GetCell(3).ToString().Trim(); row["CP_CaseNo"] = (sheet.GetRow(i).GetCell(4) == null) ? "" : sheet.GetRow(i).GetCell(4).ToString().Trim(); row["CP_HostCompany"] = (sheet.GetRow(i).GetCell(5) == null) ? "" : sheet.GetRow(i).GetCell(5).ToString().Trim(); row["CP_JointCompany"] = (sheet.GetRow(i).GetCell(6) == null) ? "" : sheet.GetRow(i).GetCell(6).ToString().Trim(); row["CP_PlanName"] = (sheet.GetRow(i).GetCell(7) == null) ? "" : sheet.GetRow(i).GetCell(7).ToString().Trim(); row["CP_PlanSummary"] = (sheet.GetRow(i).GetCell(8) == null) ? "" : sheet.GetRow(i).GetCell(8).ToString().Trim(); row["CP_PlanDefect"] = (sheet.GetRow(i).GetCell(9) == null) ? "" : sheet.GetRow(i).GetCell(9).ToString().Trim(); row["CP_NowResult"] = (sheet.GetRow(i).GetCell(10) == null) ? "" : sheet.GetRow(i).GetCell(10).ToString().Trim(); row["CP_DoneResult"] = (sheet.GetRow(i).GetCell(11) == null) ? "" : sheet.GetRow(i).GetCell(11).ToString().Trim(); row["CP_ServiceArea"] = (sheet.GetRow(i).GetCell(12) == null) ? "" : sheet.GetRow(i).GetCell(12).ToString().Trim(); row["CP_ServiceType"] = (sheet.GetRow(i).GetCell(13) == null) ? "" : sheet.GetRow(i).GetCell(13).ToString().Trim(); row["CP_AllArea"] = (sheet.GetRow(i).GetCell(14) == null) ? "" : sheet.GetRow(i).GetCell(14).ToString().Trim(); row["CP_CityArea"] = (sheet.GetRow(i).GetCell(15) == null) ? "" : sheet.GetRow(i).GetCell(15).ToString().Trim(); row["CP_CityAreaDetail"] = (sheet.GetRow(i).GetCell(16) == null) ? "" : sheet.GetRow(i).GetCell(16).ToString().Trim(); row["CP_PlanTotalMoney"] = (sheet.GetRow(i).GetCell(17) == null) ? "" : sheet.GetRow(i).GetCell(17).ToString().Trim(); row["CP_PlanSubMoney"] = (sheet.GetRow(i).GetCell(18) == null) ? "" : sheet.GetRow(i).GetCell(18).ToString().Trim(); row["CP_CityTotalMoney"] = (sheet.GetRow(i).GetCell(19) == null) ? "" : sheet.GetRow(i).GetCell(19).ToString().Trim(); row["CP_CitySubMoney"] = (sheet.GetRow(i).GetCell(20) == null) ? "" : sheet.GetRow(i).GetCell(20).ToString().Trim(); row["CP_CreateId"] = LogInfo.mGuid; row["CP_CreateName"] = LogInfo.name; row["CP_Version"] = maxV; row["CP_Status"] = "A"; dt.Rows.Add(row); } } if (dt.Rows.Count > 0) { cpt_db.BeforeBulkCopy(oConn, myTrans); // update old data set status='D' cpt_db.DoBulkCopy(dt, myTrans); } }