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);
}
}