/// <summary>
/// 查找重复值(多列)
/// </summary>
/// <param name="colNums"></param>
/// <returns></returns>
public string FindRepeat(int[] colNums)
{
List<string> ls = new List<string>();
Excel.Range rng = Sht.UsedRange;
int startLine = rng.CurrentRegion.Rows[1].Row;
int endLine = rng.CurrentRegion.Rows[rng.CurrentRegion.Rows.Count].Row;
if (endLine > startLine)
{
List<object[,]> listObjs = new List<object[,]>();
foreach (int col in colNums)
{
object[,] objs = Sht.Range[Sht.Cells[startLine, col], Sht.Cells[endLine, col]].Value2;
listObjs.Add(objs);
}
Dictionary<string, List<int>> dict = new Dictionary<string, List<int>>();
for (int i = listObjs[0].GetLowerBound(0); i <= listObjs[0].GetUpperBound(0); i++)
{
List<string> lsLine = new List<string>();
for (int j = 0; j < listObjs.Count; j++)
{
string c = (null == listObjs[j][i, 1]) ? "" : listObjs[j][i, 1].ToString().Trim();
lsLine.Add(string.Format("[{0}{1}]{2}", colNums[j], MyCommon.IntToLetter(colNums[j]),c));
}
string k = string.Join("/",lsLine.ToArray());
if (!dict.ContainsKey(k))
{
List<int> rows = new List<int>();
rows.Add(startLine - 1 + i);
dict.Add(k, rows);
}
else
{
dict[k].Add(startLine - 1 + i);
}
}
foreach (var item in dict)
{
if (item.Value.Count > 1)
{
ls.Add(string.Format("【{0}】:【{1}】", item.Key, string.Join("/", item.Value.ToArray())));
}
}
}
//ls.Add(string.Format("所在列:{0},开始行号:{1} 结束行号:{2}",col,startLine,endLine));
return string.Join(Environment.NewLine, ls.ToArray());
}
调用:
/// <summary>
/// 查找重复项
/// </summary>
/// <param name="control"></param>
public void FindRepeat(IRibbonControl control)
{
Excel.Application app = ExcelDnaUtil.Application as Excel.Application;
if (!Information.TypeName(app.Selection).Equals("Range"))
return;
Excel.Range rngSelection = app.Selection;
HashSet<int> colNums = new HashSet<int>();
foreach (Excel.Range item in rngSelection.Areas)
{
colNums.Add(item.Column);
}
List<int> colNumList = new List<int>(colNums); colNumList.Sort();
//string msg = string.Format("{0}",string.Join("/", colNumList.ToArray()));
//MessageBox.Show(msg);
string msg = string.Empty;
MyAided myAided = new MyAided(app.ActiveSheet as Excel.Worksheet);
if (1 == colNumList.Count)
{
msg = myAided.FindRepeat();
}
else
{
msg = myAided.FindRepeat(colNumList.ToArray());
}
if (!string.IsNullOrEmpty(msg))
{
MessageBox.Show(msg, "重复项", MessageBoxButtons.OK);
MyCommon.WriteLog(msg, "d:\\Log", true);
}
}