Пример #1
0
        public bool CanCheckSingleSheet(ref ExcelRedSheetTable S, ref ExcelRedSheetTable L, ref string cmptablename, bool bmultiskip = false)
        {
            int skipCol = 0, SBCol = 0, SECol = 0, LBCol = 0, LECol = 0;

            if (!bmultiskip)
            {
                ComputeLSBECol(ref skipCol, ref SBCol, ref SECol, ref LBCol, ref LECol);
            }
            else
            {
                cmptablename =
                    ComputeLSBECol_Skip(ref skipCol, ref SBCol, ref SECol, ref LBCol, ref LECol);
            }

            if (SECol > SBCol && SBCol > 0)
            {
                List <Cells> Titles = CollectRedTitles(SBCol, SECol);
                S = new ExcelRedSheetTable(worksheet, Titles, SBCol, SECol);
            }
            if (LECol > LBCol && LBCol - 1 > SECol)
            {
                List <Cells> Titles = CollectRedTitles(LBCol, LECol);
                L = new ExcelRedSheetTable(worksheet, Titles, LBCol, LECol);
            }
            if (S == null || L == null)
            {
                return(false);
            }


            return(true);
        }
Пример #2
0
        private void CompareTable(ExcelRedSheetTable S, ExcelRedSheetTable L)
        {
            if (!CompareListString(S.Titles.Select(r => r.Text).ToList(), L.Titles.Select(r => r.Text).ToList()))
            {
                textBoxOut.Text = "两张表的表头不一致,无法比较";
                return;
            }
            ClearData();
            string        str = "", strL = "", strS = "", strLS = "";
            StringBuilder strb = new StringBuilder();
            StringBuilder strT = new StringBuilder();
            Dictionary <string, Cells> LDic = ListToDictionary(L.Names, ref strL);
            Dictionary <string, Cells> SDic = ListToDictionary(S.Names, ref strS);

            Dictionary <string, Cells> LDicSpecial = new Dictionary <string, Cells>();
            Dictionary <string, Cells> SDicSpecial = new Dictionary <string, Cells>();

            /////////////////////////////////////////////////////  Duplication
            if (checkBoxCopyDuplicationL.Checked)
            {
                LDicDuplication = FindDuplication(L.Names);
            }
            if (checkBoxCopyDuplicationS.Checked)
            {
                SDicDuplication = FindDuplication(S.Names);
            }
            if (strS != "")
            {
                str += "\r\n============小表中以下值存在重复============\r\n" + strS;
            }
            if (strL != "")
            {
                str += "\r\n============大表中以下值存在重复============\r\n" + strL;
            }

            foreach (Cells c in S.Names)
            {
                if (!LDic.ContainsKey(c.Text))
                {
                    strLS += c + "\t";
                }
            }

            ////////////////////////////////////////////////  Special
            if (checkBoxTagS.Checked || checkBoxSkipDuplicate.Checked)  //排除重复
            {
                foreach (Cells c in S.Names)
                {
                    if (!LDic.ContainsKey(c.Text) && !SDicDuplication.ContainsKey(c.Text))
                    {
                        SSpecial.Add(new Point(c.Row, c.Col));
                        if (!SDicSpecial.ContainsKey(c.Text))
                        {
                            SDicSpecial[c.Text] = c;
                        }
                    }
                }
            }
            if (checkBoxTagL.Checked || checkBoxSkipDuplicate.Checked)
            {
                foreach (Cells c in L.Names)
                {
                    if (!SDic.ContainsKey(c.Text) && !LDicDuplication.ContainsKey(c.Text))
                    {
                        LSpecial.Add(new Point(c.Row, c.Col));
                        if (!LDicSpecial.ContainsKey(c.Text))
                        {
                            LDicSpecial[c.Text] = c;
                        }
                    }
                }
            }
            if (strLS != "")
            {
                strLS = "\r\n============小表中的以下值在大表中不存在============\r\n" + strLS;
            }
            if (str != "")
            {
                textBoxOut.Text = str + strLS;
                if (!checkBoxSkipDuplicate.Checked)
                {
                    MessageBox.Show("存在重复值,没有进一步比对,可选择忽略之后,继续比对");
                    return;
                }
            }

            if (SDicDuplication.Count > 0 || SDicSpecial.Count > 0)
            {
                if (str == "")
                {
                    str = " ";
                }
            }
            ///////////////////////// DIF
            SP    = new List <Point>();
            LP    = new List <Point>();
            _Same = new List <List <Cells> >();

            foreach (Cells c1 in S.Names)
            {
                if (str != "" &&
                    (SDicDuplication.ContainsKey(c1.Text) ||
                     SDicSpecial.ContainsKey(c1.Text)))
                {
                    continue;
                }
                Cells c2 = LDic[c1.Text];
                for (int i = 1; i < S.Titles.Count; i++)
                {
                    if (S.CellValue(c1.Row, S.Titles[i].Col) != L.CellValue(c2.Row, L.Titles[i].Col))
                    {
                        SP.Add(new Point(c1.Row, S.Titles[i].Col));
                        LP.Add(new Point(c2.Row, L.Titles[i].Col));
                        strb.AppendLine(c1.Text + " " + PointTostring(new Point(c1.Row, S.Titles[i].Col)) + "=" + S.CellValue(c1.Row, S.Titles[i].Col) + "\t"
                                        + PointTostring(new Point(c2.Row, L.Titles[i].Col)) + "=" + L.CellValue(c2.Row, L.Titles[i].Col));
                    }
                }
                _Same.Add(new List <Cells>()
                {
                    c1, c2
                });
            }
            string str1 = strb.ToString();

            if (str1 == "")
            {
                MessageBox.Show("除忽略项外,待比较项目完全相同");
            }
            else
            {
                str1 = "\r\n============以下单元格内容两表不一致 (行号,列号)============\r\n" + str1;
            }
            textBoxOut.Text = str + strLS + str1;
        }
Пример #3
0
        private void CompareRedSingleSheetClick()
        {
            DataTable DL = (DataTable)dgvL.DataSource;
            DataTable DS = (DataTable)dgvS.DataSource;

            if (DL == null || DS == null || DL.Columns.Count != DS.Columns.Count)
            {
                return;
            }
            for (int i = 0; i < DL.Columns.Count; i++)
            {
                if (DL.Columns[i].ColumnName != DS.Columns[i].ColumnName)
                {
                    MessageBox.Show("两边待比较的表头不一致");
                    return;
                }
            }

            ExcelBook           E = new ExcelBook(m_cfg.XlsName);
            ExcelSingleRedSheet ES = new ExcelSingleRedSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
            ExcelRedSheetTable  S = null, L = null;

            string cmptablename = "";

            try
            {
                if (!ES.CanCheckSingleSheet(ref S, ref L, ref cmptablename, checkBoxMultiBlackSkip.Checked))
                {
                    MessageBox.Show("S=" + (S != null) + " " + "L=" + (L != null) + " SL两表之一可能未完成");
                    return;
                }
                if (checkBoxClearBackColor.Checked)
                {
                    S.ClearBackColor();
                    L.ClearBackColor();
                }
                CompareTable(S, L);
                if (checkBoxTagL.Checked || checkBoxTagS.Checked)
                {
                    if (LSpecial.Count > 0 || SSpecial.Count > 0)
                    {
                        ES.FormatExcelRange(SSpecial, _colorSpe, "特有", S.TagCol);
                        ES.FormatExcelRange(LSpecial, _colorSpe, "特有", L.TagCol);
                    }
                }
                if (checkBoxCopyDuplicationL.Checked || checkBoxCopyDuplicationS.Checked)
                {
                    if (LDicDuplication.Count > 0 || SDicDuplication.Count > 0)
                    {
                        List <int> LDuplication = new List <int>();
                        List <int> SDuplication = new List <int>();
                        foreach (List <int> l in LDicDuplication.Values)
                        {
                            LDuplication.AddRange(l);
                        }
                        foreach (List <int> l in SDicDuplication.Values)
                        {
                            SDuplication.AddRange(l);
                        }

                        List <Point> SD = SDuplication.Select(r => new Point(r, S.NameCol)).ToList();
                        List <Point> LD = LDuplication.Select(r => new Point(r, L.NameCol)).ToList();
                        ES.FormatExcelRange(SD, _colorDup, "重复", S.TagCol);
                        ES.FormatExcelRange(LD, _colorDup, "重复", L.TagCol);
                    }
                }
                if (checkBoxDifL.Checked || checkBoxDifS.Checked)
                {
                    if (LP.Count > 0 || SP.Count > 0)
                    {
                        ES.FormatExcelRange(SP, _colorDif, "不同", S.TagCol + 1);
                        ES.FormatExcelRange(LP, _colorDif, "不同", L.TagCol + 1);
                    }
                }

                for (int i = 0; i < _Same.Count; i++)
                {
                    if (_Same[i].Count == 2)
                    {
                        Cells c = _Same[i][0];
                        ES.SortExcelRange(new Point(c.Row, c.Col), cmptablename + i, S.TagCol);
                        c = _Same[i][1];
                        ES.SortExcelRange(new Point(c.Row, c.Col), cmptablename + i, L.TagCol);
                    }
                }
                /////
                ES.SetExcelRangeTag(1, cmptablename + "比较结果", S.TagCol);
                ES.SetExcelRangeTag(1, cmptablename + "比较结果", L.TagCol);
            }
            catch (Exception ex)
            {
                MessageBox.Show("程序有点小问题\r\n" + ex.Message);
            }
            finally
            {
                S = null;
                L = null;
                ExcelBook.excel.ActiveWorkbook.Save();
                ExcelBook.excel.Workbooks.Close();
            }
        }