コード例 #1
0
ファイル: Form1.cs プロジェクト: ljhbeer/backup
        private void ExportSpecialExcel(ExcelBook E, string FileName = "")
        {
            ExcelBook N = new ExcelBook();

            try
            {
                ExcelSheet S = new ExcelSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
                ExcelSheet L = new ExcelSheet(E.ExcelSheet(comboBoxLSheetName.SelectedItem.ToString()));
                if (FileName != "")
                {
                    N.Create();
                    if (LSpecial.Count > 0)
                    {
                        new ExcelSheet(N.CopySheetAndDeleteOther(L)).FormatExcelRange(LSpecial, Color.Pink, "特有");
                    }
                    if (SSpecial.Count > 0)
                    {
                        if (LSpecial.Count > 0)
                        {
                            new ExcelSheet(N.CopySheet(S)).FormatExcelRange(SSpecial, Color.Pink, "特有");
                        }
                        else
                        {
                            new ExcelSheet(N.CopySheetAndDeleteOther(S)).FormatExcelRange(SSpecial, Color.Pink, "特有");
                        }
                    }
                    N.SaveAs(FileName);
                }
                else
                {
                    S.FormatExcelRange(SSpecial, _colorSpe, "特有");
                    L.FormatExcelRange(LSpecial, _colorSpe, "特有");
                }
            }
            finally
            {
                N = null;
            }
        }
コード例 #2
0
        public Worksheet CopySheetAndDeleteOther(ExcelSheet S)
        {
            Worksheet ws = (Worksheet)workbook.Sheets[1];

            S.WorkSheet.Copy(ws);
            //int trytimes = 0;
            //while (workbook.Sheets.Count >2 && trytimes++<10)
            string sheetname = "";

            foreach (Worksheet w in workbook.Worksheets)
            {
                sheetname += w.Name + "\t";
            }
            sheetname += "========\r\n";
            for (int i = 1; i < 4; i++)
            {
                Excel.Worksheet w = workbook.Sheets["Sheet" + i];
                sheetname += w.Name + "\t";
                w.Delete();
            }
            ws      = workbook.Worksheets[1];
            ws.Name = S.Name;
            return(ws);
        }
コード例 #3
0
ファイル: Form1.cs プロジェクト: ljhbeer/backup
        private void Compare(ExcelSheet S, ExcelSheet L)
        {
            if (!CompareListString(S, L))
            {
                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;
                }
            }
            ///////////////////////// DIF
            SP = new List <Point>();
            LP = new List <Point>();

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

            if (str1 == "")
            {
                MessageBox.Show("除忽略项外,待比较项目完全相同");
            }
            else
            {
                str1 = "\r\n============以下单元格内容两表不一致 (行号,列号)============\r\n" + str1;
            }
            textBoxOut.Text = str + strLS + str1;
        }