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)
{
//讀取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>");
}
}