/// <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);
}
}
/// <summary>
/// 价格偏离清理
/// </summary>
/// <param name="col1"></param>
/// <param name="col2"></param>
/// <param name="col3"></param>
/// <param name="col4"></param>
/// <param name="col5"></param>
/// <param name="col6"></param>
/// <param name="pl"></param>
private void ClearPrice(string col1, string col2, string col3, string col4, string col5, string col6, int pl)
{
FxtAddIn.GoToSheet(1);
string sheetName = FxtAddIn.FxtWorkSheet.Name;
FxtAddIn.FxtWorkSheet.AutoFilterMode = false;
int rowCount = FxtRibbon.GetLastRow();
int colCount = FxtRibbon.GetLastColumn();
//辅助列，楼盘+行政区+用途
Range rg = FxtAddIn.FxtWorkSheet.Cells[2, colCount + 1];
if (col3 != null)
{
rg.Value = string.Format("=CONCATENATE({0}2,{1}2,{2}2)", col2, col3, col4);
}
else
{
rg.Value = string.Format("=if({1}2=\"别墅\",\"\",{0}2)", col2, col4);
}
rg = rg.get_Resize(rowCount - 1, 1);
rg.FillDown();
string targetCol = rg.get_Address().Split(new char[] { '$' })[1].ToString();
dynamic[,] val = rg.Value2;
//开始算总价
FxtAddIn.GoToSheet("testdb");
rg = FxtAddIn.FxtWorkSheet.get_Range("A1", "A" + (rowCount - 1).ToString());
rg.Value2 = val;
//去重
rg.RemoveDuplicates(1, XlYesNoGuess.xlNo);
Range useRg = FxtRibbon.GetUsedRange();
//计算总价
rg = FxtAddIn.FxtWorkSheet.get_Range("B1");
rg.Value = string.Format("=SUMIF({0}!${1}$2:${1}${2},testdb!A1,{0}!${3}$2:${3}${2})", sheetName, targetCol, rowCount, col5);
rg = rg.get_Resize(useRg.Rows.Count, 1);
rg.FillDown();
rg.Value = rg.Value;
//计算总面积
rg = FxtAddIn.FxtWorkSheet.get_Range("C1");
rg.Value = string.Format("=SUMIF({0}!${1}$2:${1}${2},testdb!A1,{0}!${3}$2:${3}${2})", sheetName, targetCol, rowCount, col6);
rg = rg.get_Resize(useRg.Rows.Count, 1);
rg.FillDown();
rg.Value = rg.Value;
//计算均价
rg = FxtAddIn.FxtWorkSheet.get_Range("D1");
rg.Value = "=B1/C1";
rg = rg.get_Resize(useRg.Rows.Count, 1);
rg.FillDown();
rg.Value = rg.Value;
FxtAddIn.GoToSheet(1);
//辅助列，均价
rg = FxtAddIn.FxtWorkSheet.Cells[2, colCount + 2];
if (col3 != null)
{
rg.Value = string.Format("=VLOOKUP(CONCATENATE({0}2,{1}2,{2}2),testdb!A:D,4,FALSE)", col2, col3, col4);
}
else
{
rg.Value = string.Format("=VLOOKUP({0}2,testdb!A:D,4,FALSE)", col2);
}
rg = rg.get_Resize(rowCount - 1, 1);
rg.FillDown();
rg.Value = rg.Value;
targetCol = rg.get_Address().Split(new char[] { '$' })[1];
//辅助列，价差数
rg = FxtAddIn.FxtWorkSheet.Cells[2, rg.Column + 1];
rg.Value = string.Format("=ROUND(ABS(({0}2-{1}2)/{1}2),2)", col1, targetCol);
rg = rg.get_Resize(rowCount - 1, 1);
rg.FillDown();
rg.Value = rg.Value;
targetCol = rg.get_Address().Split(new char[] { '$' })[1];
//辅助列，价差标记
string formula = (pl / 100.00).ToString();
rg = FxtAddIn.FxtWorkSheet.Cells[2, rg.Column + 1];
rg.Value = string.Format("=if({0}2>{1},1,0)", targetCol, formula);
rg.get_Resize(rowCount - 1, 1).FillDown();
//筛选
useRg = FxtAddIn.FxtWorkSheet.UsedRange;
//不能用useRg来filter，报异常：类 Range 的 AutoFilter 方法无效
FxtAddIn.FxtWorkSheet.UsedRange.AutoFilter(rg.Column, "=1");
//删除,可见单元格数量要大于零，这里无法判断是否有可见单元格，只能TRY
try
{
FxtAddIn.FxtWorkSheet.get_Range("A2").get_Resize(useRg.Rows.Count - 1, useRg.Columns.Count).SpecialCells(XlCellType.xlCellTypeVisible).Delete();
}
catch { }
FxtAddIn.FxtWorkSheet.AutoFilterMode = false;
//删除辅助列
FxtAddIn.FxtWorkSheet.Cells[1, colCount + 4].EntireColumn.Delete();
FxtAddIn.FxtWorkSheet.Cells[1, colCount + 3].EntireColumn.Delete();
FxtAddIn.FxtWorkSheet.Cells[1, colCount + 2].EntireColumn.Delete();
FxtAddIn.FxtWorkSheet.Cells[1, colCount + 1].EntireColumn.Delete();
//FxtAddIn.FxtWorkSheet.UsedRange.Select();
FxtAddIn.GoToSheet("testdb");
FxtAddIn.FxtWorkSheet.Delete();
}
/// <summary>
/// 标准化楼盘
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnClear_Click(object sender, EventArgs e)
{
EnableControls(false);
FxtAddIn.EnableAppWindow(false);
//标准化行政区
FxtAddIn.GoToSheet(_tempAreaSheetName);
int areaCount = FxtRibbon.GetLastRow();
Range rg = FxtAddIn.FxtWorkSheet.get_Range("a1", "a" + areaCount.ToString());
//用数组代替range循环，性能更高，注意这样的数组下标是从1,1开始的
//如果用数组转置方法统一下标从1开始，有65535的下标限制
var areas = rg.Value;
if (areas != null)
{
//处理，回到sheet1
FxtAddIn.GoToSheet(1);
//回到sheet1才能调用取消锁定，这里一定要注意
FxtAddIn.FxtApp.ActiveWindow.FreezePanes = false;
//取消筛选，显示全部数据
FxtAddIn.FxtWorkSheet.AutoFilterMode = false;
int caseCount = FxtRibbon.GetLastRow();
//开始替换
SetTimeStatus(txtStatus, "标准化行政区", true);
Dictionary <string, string> cols = FxtRibbon.FindCol();
rg = FxtAddIn.FxtWorkSheet.get_Range(cols["行政区"] + "1");
rg = rg.get_Resize(caseCount, 1);
for (int i = 1; i <= areaCount; i++)
{
string sKey = areas[i, 1];
if (sKey == null || sKey.Trim().Length == 0)
{
continue;
}
rg.Replace("*" + sKey.Replace("区", "").Replace("县", "") + "*", sKey, Type.Missing, Type.Missing, false, Type.Missing, false, false);
}
rg = FxtRibbon.GetUsedRange();
//在锁定前要处理ScreenUpdating=true，否则会出现锁定错误，一定要注意
FxtAddIn.EnableAppWindow(true);
SetStatus(EnumHelper.LabelStatus.Success, "标准化行政区完成");
SetTimeStatus(txtStatus, "标准化行政区", false);
}
//取楼盘数据源
FxtAddIn.GoToSheet(_tempProjectsSheetName);
_projectCount = FxtRibbon.GetLastRow();
if (_projectCount <= 0)
{
MessageBox.Show("无楼盘数据");
return;
}
rg = FxtAddIn.FxtWorkSheet.get_Range("a1", "a" + _projectCount.ToString());
//用数组代替range循环，性能更高，注意这样的数组下标是从1,1开始的
//如果用数组转置方法统一下标从1开始，有65535的下标限制
var projects = rg.Value;
if (projects != null)
{
//处理，回到sheet1
FxtAddIn.GoToSheet(1);
//回到sheet1才能调用取消锁定，这里一定要注意
FxtAddIn.FxtApp.ActiveWindow.FreezePanes = false;
//取消筛选，显示全部数据
//if(FxtWorkSheet.FilterMode)
// FxtWorkSheet.ShowAllData();
FxtAddIn.FxtWorkSheet.AutoFilterMode = false;
int caseCount = FxtRibbon.GetLastRow();
//COPY一列备用
Range rg1 = FxtAddIn.FxtWorkSheet.get_Range("A1", "A" + caseCount.ToString());
rg1.EntireColumn.Insert(XlInsertShiftDirection.xlShiftDown, false);
rg1.Copy(FxtAddIn.FxtWorkSheet.get_Range("A1"));
FxtAddIn.FxtWorkSheet.Cells[1, 1].Value2 = "原楼盘名称";
//开始替换
SetTimeStatus(txtStatus, "标准化楼盘", true);
var cases = rg1.Value;
//使用新数组代替辅助列处理，测试10万数据速度一样
dynamic[,] newCases = new dynamic[caseCount, 1];
newCases[0, 0] = "楼盘名称";
//多线程处理
for (int i = 1; i <= _projectCount; i++)
{
string sKey = projects[i, 1];
if (sKey == null || sKey.Trim().Length == 0)
{
continue;
}
//findnext方法效率太低，放弃
//Range f = rg1.Find("*" + sKey + "*");
//while (f != null)
//{
// f.Value2 = sKey;
// f = rg1.FindNext(f);
//}
//rg1.Replace("*" + sKey + "*", "OK_" + sKey, Type.Missing, Type.Missing, false, Type.Missing, false, false);
//使用数组，效率高，且灵活
//下标从2开始（第二行）
for (int j = 2; j <= caseCount; j++)
{
if (cases[j, 1].ToString().IndexOf("OK_") < 0 &&
cases[j, 1].ToString().IndexOf(sKey) >= 0)
{
cases[j, 1] = "OK_" + sKey;
newCases[j - 1, 0] = sKey;
}
}
}
rg1.Value2 = newCases;
/*//使用新数组代替辅助列处理，测试10万数据速度一样
* rg1.Value2 = cases;
* //添加辅助列，设定公式
* rg1.EntireColumn.Insert(XlInsertShiftDirection.xlShiftDown, false);
* FxtAddIn.FxtWorkSheet.Cells[1, 2].Formula = "=IF(ISNUMBER(FIND(\"OK_\",C1)),REPLACE(C1,1,3,\"\"),\"\")";
* Range rg2 = FxtAddIn.FxtWorkSheet.get_Range("B1", "B" + caseCount.ToString());
* rg2.FillDown();
* //去掉公式
* rg2.Value = rg2.Value;
* //删除过渡列
* FxtAddIn.FxtWorkSheet.Columns[3].Delete();
* */
rg = FxtRibbon.GetUsedRange();
//自动列宽
//FxtAddIn.FxtWorkSheet.Columns.AutoFit();
//在锁定前要处理ScreenUpdating=true，否则会出现锁定错误，一定要注意
FxtAddIn.EnableAppWindow(true);
//锁定行头
FxtAddIn.FxtWorkSheet.get_Range("B2").Select();//.Cells[2, 2].Select();
//FxtAddIn.FxtApp.ActiveWindow.SplitRow = 1;
//FxtAddIn.FxtApp.ActiveWindow.SplitColumn = 1;
FxtAddIn.FxtApp.ActiveWindow.FreezePanes = true;
//筛选为空的，需要再次人工处理
rg.AutoFilter(2, "=");
rg = FxtAddIn.FxtWorkSheet.get_Range("a2", "a" + caseCount.ToString());
int leftRows = rg.SpecialCells(XlCellType.xlCellTypeVisible).Count;
string status = string.Format("共处理{0}条数据\n匹配成功{1}条\n待处理{2}条",
caseCount - 1, caseCount - leftRows - 1, leftRows);
SetStatus(EnumHelper.LabelStatus.Success, status);
SetTimeStatus(txtStatus, "标准化楼盘", false);
lblBZHProject.Visible = true;
FxtAddIn.FxtApp.ActiveWindow.SmallScroll(Type.Missing, FxtAddIn.FxtApp.ActiveWindow.ScrollRow);
}
//恢复控件使用
EnableControls(true);
MessageBox.Show("处理完成！");
}