private List <Area> DownloadAreas() { System.Net.WebClient wc = new System.Net.WebClient(); JSONHelper.JsonData data = new JSONHelper.JsonData(); data.sinfo = new SecurityInfo(FxtCommon.SignName, "1003104", "108746028", "855190548"); data.sinfo.functionname = "garealist"; data.info.funinfo = new { fxtcompanyid = 25, typecode = 1003302, cityid = FxtAddIn.CityID, }; string str = data.GetJsonString(); List <Area> areas = new List <Area>(); try { string list = FxtCommon.APIPostBack(FxtCommon.API_Datacenter, str); JSONHelper.ReturnData rtn = JSONHelper.JSONToObject <JSONHelper.ReturnData>(list); if (rtn.returntype > 0) { //获取行政区列表 string strdata = rtn.data.ToString(); areas = JSONHelper.JSONStringToList <Area>(strdata); } } catch (Exception ex) { SetStatus(EnumHelper.LabelStatus.Faild, ex.Message); } return(areas); }
private void GetProvinces() { EnableControls(false); btnGetProvine.Visible = false; //初始化省份列表 cbProvince.DropDownStyle = ComboBoxStyle.DropDownList; System.Net.WebClient wc = new System.Net.WebClient(); JSONHelper.JsonData data = new JSONHelper.JsonData(); data.sinfo = new SecurityInfo(FxtCommon.SignName, "1003104", "108746028", "855190548"); data.sinfo.functionname = "provincelist"; data.info.funinfo = new { fxtcompanyid = 25, typecode = 1003302 }; string str = data.GetJsonString(); try { string provincelist = FxtCommon.APIPostBack(FxtCommon.API_Datacenter, str); JSONHelper.ReturnData rtn = JSONHelper.JSONToObject <JSONHelper.ReturnData>(provincelist); if (rtn.returntype > 0) { EnableControls(true); List <Province> provinces = JSONHelper.JSONStringToList <Province>(rtn.data.ToString()); foreach (Province item in provinces) { item.provincename = FxtCommon.GetPinyinFirst(item.provincename.Replace("省", "").Replace("自治区", "").Replace("壮族", "")) + "_" + item.provincename; } provinces.Sort((x, y) => x.provincename.CompareTo(y.provincename)); cbProvince.DropDownStyle = ComboBoxStyle.DropDownList; cbProvince.DataSource = provinces; cbProvince.ValueMember = "provinceid"; cbProvince.DisplayMember = "provincename"; SetStatus(EnumHelper.LabelStatus.Success, "获取省份城市成功"); } else { SetStatus(EnumHelper.LabelStatus.Faild, rtn.returntext.ToString()); btnGetProvine.Visible = true; btnGetProvine.Enabled = true; } } catch (Exception ex) { SetStatus(EnumHelper.LabelStatus.Faild, ex.Message); btnGetProvine.Visible = true; btnGetProvine.Enabled = true; } }
/// <summary> /// 下载城市楼盘 /// </summary> /// <param name="pageindex"></param> /// <param name="pagerecords"></param> /// <param name="projects"></param> /// <param name="done"></param> private void DownLoadProjects(int pageindex, int pagerecords, List <Project> projects, out bool done) { done = false; JSONHelper.JsonData data = new JSONHelper.JsonData(); data.sinfo = new SecurityInfo(FxtCommon.SignName, "1003104", "108746028", "855190548"); data.sinfo.functionname = "plist";// "splist";"allplist" data.info.funinfo = new { fxtcompanyid = 25, typecode = 1003302, cityid = FxtAddIn.CityID, key = "", pageindex = pageindex, pagerecords = pagerecords }; string str = data.GetJsonString(); try { string list = FxtCommon.APIPostBack(FxtCommon.API_Datacenter, str); JSONHelper.ReturnData rtn = JSONHelper.JSONToObject <JSONHelper.ReturnData>(list); if (rtn.returntype > 0) { //获取楼盘列表 string strdata = rtn.data.ToString(); //StreamWriter write = File.CreateText(@"c:\windows\temp\temp_projects.txt"); //write.Write(strdata); //write.Close(); List <Project> cp = JSONHelper.JSONStringToList <Project>(strdata); if (cp.Count > 0) { projects.AddRange(cp);// = projects.Concat(cp).ToList(); } if (cp.Count < pagerecords) { done = true; } else { pageindex++; DownLoadProjects(pageindex, pagerecords, projects, out done); } } } catch (Exception ex) { SetStatus(EnumHelper.LabelStatus.Faild, ex.Message); } }
private void GetCitys() { EnableControls(false); btnGetCity.Visible = false; System.Net.WebClient wc = new System.Net.WebClient(); JSONHelper.JsonData data = new JSONHelper.JsonData(); data.sinfo = new SecurityInfo(FxtCommon.SignName, "1003104", "108746028", "855190548"); data.sinfo.functionname = "citylist"; data.info.funinfo = new { fxtcompanyid = 25, typecode = 1003302, provinceid = cbProvince.SelectedValue }; string str = data.GetJsonString(); try { string citylist = FxtCommon.APIPostBack(FxtCommon.API_Datacenter, str); JSONHelper.ReturnData rtn = JSONHelper.JSONToObject <JSONHelper.ReturnData>(citylist); if (rtn.returntype > 0) { EnableControls(true); List <City> citys = JSONHelper.JSONStringToList <City>(rtn.data.ToString()); foreach (City item in citys) { item.cityname = FxtCommon.GetPinyinFirst(item.cityname.Replace("市", "").Replace("直辖", "")) + "_" + item.cityname; } citys.Sort((x, y) => x.cityname.CompareTo(y.cityname)); cbCity.DropDownStyle = ComboBoxStyle.DropDownList; cbCity.DataSource = citys; cbCity.ValueMember = "cityid"; cbCity.DisplayMember = "cityname"; SetStatus(EnumHelper.LabelStatus.Success, "获取城市成功"); } else { SetStatus(EnumHelper.LabelStatus.Faild, "获取城市失败"); btnGetCity.Visible = true; btnGetCity.Enabled = true; } } catch (Exception ex) { SetStatus(EnumHelper.LabelStatus.Faild, ex.Message); btnGetCity.Visible = true; btnGetCity.Enabled = true; } }
/// <summary> /// 清理用途、建筑类型偏差 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnClearUse_Click(object sender, EventArgs e) { try { SetTimeStatus(txtStatus, "清理价格偏离", true); Dictionary <string, string> dict = FxtRibbon.FindCol(); //FxtAddIn.FxtWorkSheet.get_Range(dict.Last().Value + "1") //取消筛选,显示全部数据 FxtAddIn.FxtWorkSheet.AutoFilterMode = false; string col1 = dict["单价"]; string col2 = dict["楼盘名称"]; string col3 = dict["行政区"]; string col4 = dict["用途"]; string col5 = dict["总价"]; string col6 = dict["建筑面积"]; string col7 = dict["建筑类型"]; FxtAddIn.EnableAppWindow(false); //均价 /*OLEDB引起打开新文件的问题,暂时抛弃 * ADODB.Connection conn = new ADODB.Connection(); * conn.Open("provider=Microsoft.ACE.OLEDB.12.0;extended properties='Excel 12.0 Xml;HDR=YES; IMEX=1';Data Source = " + FxtAddIn.FxtWorkBook.FullName); * //当已经有EXCEL文件打开时,会打开一个新文件,进程会归在之前打开的文件,应该是改了多进程后,只读打开文件会找进程ID,然后打开 * //string sql = "select 楼盘名称+行政区+用途,sum(val(总价)) / sum(val(建筑面积)) as price from [sheet1$] group by 楼盘名称+行政区+用途"; * string sql = "select 楼盘名称,sum(val(总价)) as price from [sheet1$] group by 楼盘名称"; * FxtAddIn.GoToSheet("testdb"); * object rows;//记录数,得0,不知道原因 * //ADODB.Recordset records = conn.Execute(sql, out rows,1); * FxtAddIn.FxtWorkSheet.get_Range("A1").CopyFromRecordset(conn.Execute(sql,out rows,1)); * conn.Close(); * return; */ //文本转数字,用数组效率高,union不能连续,这里要改 FxtAddIn.GoToSheet(1); string sheetName = FxtAddIn.FxtWorkSheet.Name; int rowCount = FxtRibbon.GetLastRow(); int colCount = FxtRibbon.GetLastColumn(); //= FxtAddIn.FxtWorkSheet.get_Range(col5 + "2", col5 + rowCount); Range rg = FxtAddIn.FxtApp.Application.Union(FxtAddIn.FxtWorkSheet.get_Range(col6 + "2", col6 + rowCount.ToString()), FxtAddIn.FxtWorkSheet.get_Range(col1 + "2", col1 + rowCount.ToString()), FxtAddIn.FxtWorkSheet.get_Range(col5 + "2", col5 + rowCount.ToString())); dynamic[,] val = rg.Value2; for (int i = 1; i < rowCount; i++) { if (FxtCommon.IsNumeric(val[i, 1].ToString())) { val[i, 1] = Convert.ToDecimal(val[i, 1].ToString()); } else { val[i, 1] = 0; } if (FxtCommon.IsNumeric(val[i, 2].ToString())) { val[i, 2] = Convert.ToDecimal(val[i, 2].ToString()); } else { val[i, 2] = 0; } if (FxtCommon.IsNumeric(val[i, 3].ToString())) { val[i, 3] = Convert.ToDecimal(val[i, 3].ToString()); } else { val[i, 3] = 0; } } rg.Value2 = val; int pl = Convert.ToInt32(txtPrice.Text); //用途清理 ClearPrice(col1, col2, col3, col4, col5, col6, pl); FxtAddIn.GoToSheet(1); //lblStatus.Text += FxtAddIn.FxtWorkSheet.UsedRange.Rows.Count.ToString() + "\n"; //建筑类型清理 ClearPrice(col1, col2, col3, col7, col5, col6, pl); FxtAddIn.GoToSheet(1); //lblStatus.Text += FxtAddIn.FxtWorkSheet.UsedRange.Rows.Count.ToString() + "\n"; //楼盘清理 pl = Convert.ToInt32(txtPrice1.Text); ClearPrice(col1, col2, null, col4, col5, col6, pl); FxtAddIn.GoToSheet(1); //lblStatus.Text += FxtAddIn.FxtWorkSheet.UsedRange.Rows.Count.ToString() + "\n"; SetStatus(EnumHelper.LabelStatus.Success, string.Format("共清理{0}条单价偏离数据", rowCount - FxtRibbon.GetLastRow())); lblClearUse.Visible = true; FxtAddIn.EnableAppWindow(true); FxtAddIn.FxtApp.ActiveWindow.SmallScroll(Type.Missing, FxtAddIn.FxtApp.ActiveWindow.ScrollRow); FxtRibbon.GetUsedRange().Select(); SetTimeStatus(txtStatus, "清理价格偏离", false); /*透视表 * //数据源 * Range dataRangeForPivot = FxtAddIn.FxtWorkSheet.UsedRange; * Excel.PivotCache pivotCache = FxtAddIn.FxtWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRangeForPivot); * * FxtAddIn.GoToSheet(2); * Range dataRangeForPivotTable = FxtAddIn.FxtWorkSheet.get_Range("A1"); * Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(dataRangeForPivotTable, @"DTable", Type.Missing, Type.Missing); * * Excel.PivotField demoField = ((Excel.PivotField)pivotTable.PivotFields(1)); * demoField.Orientation =Excel.XlPivotFieldOrientation.xlRowField; * demoField = ((Excel.PivotField)pivotTable.PivotFields(col4)); * demoField.Orientation =Excel.XlPivotFieldOrientation.xlDataField; * demoField.Function = Excel.XlConsolidationFunction.xlCount; */ /* * Range rg = FxtAddIn.FxtWorkSheet.get_Range(col + "2"); * rg.Value = formula; * rg.get_Resize(FxtAddIn.FxtWorkSheet.UsedRange.Rows.Count - 1, 1).FillDown(); * Range rg = FxtAddIn.FxtApp.Application.Union(FxtAddIn.FxtWorkSheet.get_Range("A1", "A20"), * FxtAddIn.FxtWorkSheet.get_Range("d1", "d20")); * rg.Select(); * */ } catch (Exception ex) { SetStatus(EnumHelper.LabelStatus.Faild, ex.Message); } }