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