public ActionResult DownRainStatistics(string stid) { string ssql = @" select * from WeaRainStatistics where stid = @stid "; List <WeaRainStatistics> wrs = dbDapper.Query <WeaRainStatistics>(ssql, new { stid = stid }); List <dynamic> data = dbDapper.Query(ssql, new { stid = stid }); foreach (var LoopItem in data) { if (LoopItem.m01 == -99) { LoopItem.m01 = "*"; } if (LoopItem.m02 == -99) { LoopItem.m02 = "*"; } if (LoopItem.m03 == -99) { LoopItem.m03 = "*"; } if (LoopItem.m04 == -99) { LoopItem.m04 = "*"; } if (LoopItem.m05 == -99) { LoopItem.m05 = "*"; } if (LoopItem.m06 == -99) { LoopItem.m06 = "*"; } if (LoopItem.m07 == -99) { LoopItem.m07 = "*"; } if (LoopItem.m08 == -99) { LoopItem.m08 = "*"; } if (LoopItem.m09 == -99) { LoopItem.m09 = "*"; } if (LoopItem.m10 == -99) { LoopItem.m10 = "*"; } if (LoopItem.m11 == -99) { LoopItem.m11 = "*"; } if (LoopItem.m12 == -99) { LoopItem.m12 = "*"; } if (LoopItem.yearsum == -99) { LoopItem.yearsum = "*"; } } List <decimal> m01List = wrs.Select(c => c.m01).ToList <decimal>(); List <decimal> m02List = wrs.Select(c => c.m02).ToList <decimal>(); List <decimal> m03List = wrs.Select(c => c.m03).ToList <decimal>(); List <decimal> m04List = wrs.Select(c => c.m04).ToList <decimal>(); List <decimal> m05List = wrs.Select(c => c.m05).ToList <decimal>(); List <decimal> m06List = wrs.Select(c => c.m06).ToList <decimal>(); List <decimal> m07List = wrs.Select(c => c.m07).ToList <decimal>(); List <decimal> m08List = wrs.Select(c => c.m08).ToList <decimal>(); List <decimal> m09List = wrs.Select(c => c.m09).ToList <decimal>(); List <decimal> m10List = wrs.Select(c => c.m10).ToList <decimal>(); List <decimal> m11List = wrs.Select(c => c.m11).ToList <decimal>(); List <decimal> m12List = wrs.Select(c => c.m12).ToList <decimal>(); List <decimal> mavgList = wrs.Select(c => c.mavg).ToList <decimal>(); List <decimal> yearsumList = wrs.Select(c => c.yearsum).ToList <decimal>(); //平均雨量統計 WeaRainStatistics test = new WeaRainStatistics(); test.year = "平均雨量"; test.m01 = RainControlUtil.CalcRainAvg(m01List); test.m02 = RainControlUtil.CalcRainAvg(m02List); test.m03 = RainControlUtil.CalcRainAvg(m03List); test.m04 = RainControlUtil.CalcRainAvg(m04List); test.m05 = RainControlUtil.CalcRainAvg(m05List); test.m06 = RainControlUtil.CalcRainAvg(m06List); test.m07 = RainControlUtil.CalcRainAvg(m07List); test.m08 = RainControlUtil.CalcRainAvg(m08List); test.m09 = RainControlUtil.CalcRainAvg(m09List); test.m10 = RainControlUtil.CalcRainAvg(m10List); test.m11 = RainControlUtil.CalcRainAvg(m11List); test.m12 = RainControlUtil.CalcRainAvg(m12List); test.mavg = RainControlUtil.CalcRainAvg(mavgList); test.yearsum = RainControlUtil.CalcRainAvg(yearsumList); wrs.Add(test); List <string> head = new List <string>(); head.Add("年份"); head.Add("1月"); head.Add("2月"); head.Add("3月"); head.Add("4月"); head.Add("5月"); head.Add("6月"); head.Add("7月"); head.Add("8月"); head.Add("9月"); head.Add("10月"); head.Add("11月"); head.Add("12月"); head.Add("月平均"); head.Add("年雨量"); head.Add("最大1日雨量"); head.Add("最大1日發生日"); head.Add("最大2日雨量"); head.Add("最大2日發生日"); head.Add("最大3日雨量"); head.Add("最大3日發生日"); head.Add("降雨日數"); List <string[]> datas = new List <string[]>(); foreach (WeaRainStatistics item in wrs) { List <string> cols = new List <string>(); cols.Add(item.year); cols.Add(item.m01 == -99 ? "*" : item.m01.ToString()); cols.Add(item.m02 == -99 ? "*" : item.m02.ToString()); cols.Add(item.m03 == -99 ? "*" : item.m03.ToString()); cols.Add(item.m04 == -99 ? "*" : item.m04.ToString()); cols.Add(item.m05 == -99 ? "*" : item.m05.ToString()); cols.Add(item.m06 == -99 ? "*" : item.m06.ToString()); cols.Add(item.m07 == -99 ? "*" : item.m07.ToString()); cols.Add(item.m08 == -99 ? "*" : item.m08.ToString()); cols.Add(item.m09 == -99 ? "*" : item.m09.ToString()); cols.Add(item.m10 == -99 ? "*" : item.m10.ToString()); cols.Add(item.m11 == -99 ? "*" : item.m11.ToString()); cols.Add(item.m12 == -99 ? "*" : item.m12.ToString()); cols.Add(item.mavg.ToString()); cols.Add(item.yearsum == -99 ? "*" : item.yearsum.ToString()); cols.Add(item.max1.ToString()); cols.Add(item.max1date); cols.Add(item.max2.ToString()); cols.Add(item.max2date); cols.Add(item.max3.ToString()); cols.Add(item.max3date); cols.Add(item.raindatecount.ToString()); datas.Add(cols.ToArray()); } //產生檔案路徑 string sTempPath = Path.Combine(Server.MapPath("~/temp/"), DateTime.Now.ToString("yyyyMMdd")); //建立資料夾 Directory.CreateDirectory(sTempPath); string sFileName = "WeaRainStatistics_" + Guid.NewGuid().ToString() + ".xlsx"; string sSaveFilePath = Path.Combine(sTempPath, "WeaRainStatistics_" + Guid.NewGuid().ToString() + ".xlsx"); //DataTable dt = Utils.ConvertToDataTable<RainStation>(wrs); //DataTable dt = new DataTable(); DataExport de = new DataExport(); //Boolean bSuccess = de.ExportBigDataToCsv(sSaveFilePath, dt); Boolean bSuccess = de.ExportListToExcel(sSaveFilePath, head, datas); if (bSuccess) { string filename = string.Format("WeaRainStatistics_{0}_{1}.xlsx", stid, stid); //ASP.NET 回應大型檔案的注意事項 //http://blog.miniasp.com/post/2008/03/11/Caution-about-ASPNET-Response-a-Large-File.aspx //***** 下載檔案過大,使用特殊方法 ***** HttpContext context = System.Web.HttpContext.Current; context.Response.TransmitFile(sSaveFilePath); context.Response.ContentType = "application/vnd.ms-excel"; context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.End(); } return(null); }
public ActionResult DownSoilWaterRainHis(string sd, string ed, string Station) { DateTime dtStart; DateTime dtEnd; sd = sd.Replace(' ', '-'); ed = ed.Replace(' ', '-'); if (string.IsNullOrEmpty(sd) || string.IsNullOrEmpty(ed)) { dtStart = DateTime.Now; dtEnd = DateTime.Now; } else { string[] aSdt = sd.Split('-'); string[] aEdt = ed.Split('-'); dtStart = new DateTime(Convert.ToInt32(aSdt[0]), Convert.ToInt32(aSdt[1]), Convert.ToInt32(aSdt[2]), Convert.ToInt32(aSdt[3]), 0, 1); dtEnd = new DateTime(Convert.ToInt32(aEdt[0]), Convert.ToInt32(aEdt[1]), Convert.ToInt32(aEdt[2]), Convert.ToInt32(aEdt[3]), 59, 59); } string sStartDate = dtStart.ToString("yyyyMMddHH"); string sEndDate = dtEnd.ToString("yyyyMMddHH"); string ssql = @" select * from SoilWaterRainDataHis where 1=1 and STID = @STID and Time between @sd and @ed order by Time "; List <SoilWaterRainDataHis> DataList = new List <SoilWaterRainDataHis>(); DataList = dbDapper.Query <SoilWaterRainDataHis>(ssql, new { STID = Station, sd = sStartDate, ed = sEndDate }); //產生檔案路徑 string sTempPath = Path.Combine(Server.MapPath("~/temp/"), DateTime.Now.ToString("yyyyMMdd")); //建立資料夾 Directory.CreateDirectory(sTempPath); string sSaveFilePath = Path.Combine(sTempPath, "SoilWaterRainDataHisByStation_" + Guid.NewGuid().ToString() + ".xlsx"); DataTable dt = Utils.ConvertToDataTable <SoilWaterRainDataHis>(DataList); List <string> head = new List <string>(); head.Add("站號"); head.Add("時間"); head.Add("Hour"); head.Add("Hour3"); head.Add("Hour6"); head.Add("Hour12"); head.Add("Hour24"); head.Add("DayRainFall"); head.Add("RT"); List <string[]> datas = new List <string[]>(); foreach (SoilWaterRainDataHis item in DataList) { //日期格式化 DateTime MainTime = DateTime.ParseExact(item.Time, "yyyyMMddHH", null); List <string> cols = new List <string>(); cols.Add(item.Stid); cols.Add(MainTime.ToString("yyyy/MM/dd HH:00")); cols.Add(item.Hour.ToString()); cols.Add(item.Hour3.ToString()); cols.Add(item.Hour6.ToString()); cols.Add(item.Hour12.ToString()); cols.Add(item.Hour24.ToString()); cols.Add(item.DayRainfall.ToString()); cols.Add(item.RT.ToString()); datas.Add(cols.ToArray()); } DataExport de = new DataExport(); Boolean bSuccess = de.ExportListToExcel(sSaveFilePath, head, datas); if (bSuccess) { string filename = string.Format("SoilWaterRainDataHis_{0}_{1}_{2}.xlsx", Station, sd, ed).Replace("-", ""); //ASP.NET 回應大型檔案的注意事項 //http://blog.miniasp.com/post/2008/03/11/Caution-about-ASPNET-Response-a-Large-File.aspx //***** 下載檔案過大,使用特殊方法 ***** HttpContext context = System.Web.HttpContext.Current; context.Response.TransmitFile(sSaveFilePath); context.Response.ContentType = "application/vnd.ms-excel"; context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.End(); } return(null); }
public ActionResult DownRainStatistics(string Params) { MVCControlClass.ApiResult aResult = new MVCControlClass.ApiResult(); dynamic dParams = JsonConvert.DeserializeObject <dynamic>(Params); string station = dParams["station"]; string sdtstart = dParams["dtstart"]; string sdtend = dParams["dtend"]; DateTime dtstart = Utils.getStringToDateTime(sdtstart); DateTime dtend = Utils.getStringToDateTime(sdtend); string RG = dParams["RG"]; string TM = dParams["TM"]; string PM = dParams["PM"]; string GW = dParams["GW"]; string stimerange = dParams["timerange"]; List <string> lstSensor = new List <string>(); if (RG == "Y") { lstSensor.Add("RG"); } if (TM == "Y") { lstSensor.Add("TM"); //DS011_01該站特殊TM有兩個 lstSensor.Add("TM1"); lstSensor.Add("TM2"); } if (PM == "Y") { lstSensor.Add("PM"); } if (GW == "Y") { lstSensor.Add("GW"); } string sInSensor = string.Join("','", lstSensor.ToArray()); //轉換當天最大值 dtend = Utils.getStringToDateTime(dtend.ToString("yyyy-MM-dd 23:59:59")); // 20200429 只顯示小時數據 ssql = @" select distinct a.SiteID, a.StationID,a.DatetimeString,b.datavalue ,RG.value RGvalue,TM.value TMvalue,PM.value PMvalue,GW.value GWvalue,TM1.value TM1value,TM2.value TM2value from Result10MinData a left join BasM11Setting b on a.siteid = b.dataitem and b.datatype = 'SiteCName' left join Result10MinData RG on a.StationID = RG.StationID and RG.SensorID = 'RG' and a.DatetimeString = RG.DatetimeString left join Result10MinData TM on a.StationID = TM.StationID and TM.SensorID = 'TM' and a.DatetimeString = TM.DatetimeString left join Result10MinData PM on a.StationID = PM.StationID and PM.SensorID = 'PM' and a.DatetimeString = PM.DatetimeString left join Result10MinData GW on a.StationID = GW.StationID and GW.SensorID = 'GW' and a.DatetimeString = GW.DatetimeString left join Result10MinData TM1 on a.StationID = TM1.StationID and TM1.SensorID = 'TM1' and a.DatetimeString = TM1.DatetimeString left join Result10MinData TM2 on a.StationID = TM2.StationID and TM2.SensorID = 'TM2' and a.DatetimeString = TM2.DatetimeString where a.StationID = '{0}' and a.DatetimeString between '{2}' and '{3}' "; if (stimerange == "hr") { ssql = ssql + " and a.DatetimeString like '%:00:00' "; } ssql = string.Format(ssql, station, sInSensor, M11Utils.M11DatetimeToString(dtstart), M11Utils.M11DatetimeToString(dtend)); ssql = ssql + " order by a.StationID,a.DatetimeString desc "; //List<Result10MinData> Result10MinDatas = dbDapper.Query<Result10MinData>(ssql); List <dynamic> Result10MinDatas = dbDapper.Query <dynamic>(ssql); //建立Excel欄位 List <string> head = new List <string>(); head.Add("潛勢區"); head.Add("潛勢區名稱"); head.Add("測站"); head.Add("時間"); //head.Add("數據個數"); //[RG] head.Add("[RG]10分鐘累積雨量(mm)"); head.Add("[RG]1小時累積雨量(mm)"); head.Add("[RG]3小時累積雨量(mm)"); head.Add("[RG]6小時累積雨量(mm)"); head.Add("[RG]12小時累積雨量(mm)"); head.Add("[RG]24小時累積雨量(mm)"); head.Add("[RG]48小時累積雨量(mm)"); head.Add("[RG]72小時累積雨量(mm)"); //TM head.Add("[TM]方位一觀測值(秒)"); head.Add("[TM]方位二觀測值(秒)"); head.Add("[TM]方位一累積變位量(秒)"); head.Add("[TM]方位二累積變位量(秒)"); head.Add("[TM]方位一速率(秒/天)"); head.Add("[TM]方位二速率(秒/天)"); //PM head.Add("[PM]水位高(m)"); head.Add("[PM]相對水位高(m)"); //GW head.Add("[GW]水位高(m)"); head.Add("[GW]相對水位高(m)"); //TM1 head.Add("[TM1]方位一觀測值(秒)"); head.Add("[TM1]方位二觀測值(秒)"); head.Add("[TM1]方位一累積變位量(秒)"); head.Add("[TM1]方位二累積變位量(秒)"); head.Add("[TM1]方位一速率(秒/天)"); head.Add("[TM1]方位二速率(秒/天)"); //TM2 head.Add("[TM2]方位一觀測值(秒)"); head.Add("[TM2]方位二觀測值(秒)"); head.Add("[TM2]方位一累積變位量(秒)"); head.Add("[TM2]方位二累積變位量(秒)"); head.Add("[TM2]方位一速率(秒/天)"); head.Add("[TM2]方位二速率(秒/天)"); //Func<string,string,List<string>> SensorValuePaser List <string[]> datas = new List <string[]>(); foreach (dynamic item in Result10MinDatas) { List <string> cols = new List <string>(); cols.Add(item.SiteID); cols.Add(item.datavalue); cols.Add(item.StationID); //cols.Add(item.SensorID); cols.Add(item.DatetimeString); //cols.Add(item.observation_num); //[RG]數據拆開各自一個欄位 List <string> lstTmp = new List <string>(); for (int i = 0; i < 8; i++) { lstTmp.Add(""); } if (item.RGvalue != null) { string[] aValue = item.RGvalue.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } //[TM]數據拆開各自一個欄位 lstTmp.Clear(); for (int i = 0; i < 6; i++) { lstTmp.Add(""); } if (item.TMvalue != null) { string[] aValue = item.TMvalue.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } //[PM]數據拆開各自一個欄位 lstTmp.Clear(); for (int i = 0; i < 2; i++) { lstTmp.Add(""); } if (item.PMvalue != null) { string[] aValue = item.PMvalue.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } //[GW]數據拆開各自一個欄位 lstTmp.Clear(); for (int i = 0; i < 2; i++) { lstTmp.Add(""); } if (item.GWvalue != null) { string[] aValue = item.GWvalue.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } //[TM1]數據拆開各自一個欄位 lstTmp.Clear(); for (int i = 0; i < 6; i++) { lstTmp.Add(""); } if (item.TM1value != null) { string[] aValue = item.TM1value.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } //[TM2]數據拆開各自一個欄位 lstTmp.Clear(); for (int i = 0; i < 6; i++) { lstTmp.Add(""); } if (item.TM2value != null) { string[] aValue = item.TM2value.Split(' '); for (int i = 0; i < aValue.Length; i++) { lstTmp[i] = aValue[i]; } } foreach (string value in lstTmp) { cols.Add(value); } datas.Add(cols.ToArray()); } //產生檔案路徑 string sGUID = Guid.NewGuid().ToString(); string sTempPath = Path.Combine(Server.MapPath("~/temp/"), "QueryXmlResult", sGUID); //建立資料夾 Directory.CreateDirectory(sTempPath); string sFileName = string.Format("QueryXmlResult_{0}_{1}.xlsx", dtstart.ToString("yyyyMMdd"), dtend.ToString("yyyyMMdd")); string sSaveFilePath = Path.Combine(sTempPath, sFileName); //DataTable dt = Utils.ConvertToDataTable<RainStation>(wrs); //DataTable dt = new DataTable(); DataExport de = new DataExport(); //Boolean bSuccess = de.ExportBigDataToCsv(sSaveFilePath, dt); Boolean bSuccess = de.ExportListToExcel(sSaveFilePath, head, datas); //產生提供下載的路徑,到前端後,提供下載 string sFileDownUrl = Request.Url.Scheme + "://" + Request.Url.Authority + Url.Content("~/temp/QueryXmlResult/" + sGUID + "/") + sFileName; //if (bSuccess) //{ // string filename = string.Format("WeaRainStatistics_{0}_{1}.xlsx", "", ""); // //ASP.NET 回應大型檔案的注意事項 // //http://blog.miniasp.com/post/2008/03/11/Caution-about-ASPNET-Response-a-Large-File.aspx // //***** 下載檔案過大,使用特殊方法 ***** // HttpContext context = System.Web.HttpContext.Current; // context.Response.TransmitFile(sSaveFilePath); // context.Response.ContentType = "application/vnd.ms-excel"; // context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); // Response.End(); //} //dynamic dd = new { test1:sSaveFilePath}; aResult.ApiResultStauts = "Y"; aResult.Data = sFileDownUrl; return(this.Json(aResult, JsonRequestBehavior.AllowGet)); }