NEW（ - ）BASE =结果Excel电子表格 [英] NEW (-) BASE = Result Excel spreadsheet

问题描述

所有我想要做的是打开2个Excel文件（Sheet1），具有相同的精确结构，但可能有不同的数值，读取它们，并创建一个第三个文件新减基。如果两者的值相同，则显示0或0.00或完全没有关系。

例如：新文件

 项目价格折扣
 A 10.00 1.00 
 B 9.00 0.00 
 
 
帐户
总销售额980000.00 
  

例如：基本文件

 项目价格折扣
 A 10.00 2.00 
 B 9.00 0.00 
 
 
帐户
总销售额980000.00 
  

例如：结果文件

 项目价格折扣
 A 0.00 -1.00 
 B 0.00 0.00 
 
 
帐户
总销售0.00 
  

有没有人有如何做到这一点的想法？还是参考，我可以找到类似的东西？谢谢。

我从上一个由同事创建的例子中得到了代码，他做了非常相似的事情。我试图添加他的代码，结果到目前为止：

基本文件：无论有没有数值都是红色的

新建文件：无论何处有数值都是红色的

最终文件：空，但相同的单元格用红色和黄色着色。

我希望有更好的清洁方式，以下代码的任何建议？

  public bool createReport_NewMinusBase（string currentWorkingDirectory，string Book1，string Book2，double tolerance）
 {
 myExcel.Application excelApp = new myExcel.Application（）; //创建一个新的Excel应用程序
 excelApp.Visible = true; //使用户可以看到Excel。 
 
 int row = 0; 
 int col = 0; 
 int maxR = 0; 
 int maxC = 0; 
 
 //对于COM对象交互有用
对象丢失= System.Reflection.Missing.Value; 
 
 //返回值
 bool wereDifferences = false; 
 
 //比较对象
对象objNew = null; 
 object objBase = null; 
 
 // source：http://www.codeproject.com/KB/office/csharp_excel.aspx 
 excelApp.Application.DisplayAlerts = false; 
 
 //打开BASE FILE 
 myExcel.Workbook excelWorkbook1 = excelApp.Workbooks.Open（currentWorkingDirectory + Book1，0，
丢失，缺少，缺少，缺少，缺少，缺少， 
失踪，失踪，失踪，失踪，失踪，失踪，缺失）; 
 // OPEN NEW FILE 
 myExcel.Workbook excelWorkbook2 = excelApp.Workbooks.Open（currentWorkingDirectory + Book2，0，
 missing，missing，missing，missing，missing，missing，
失踪，失踪，失踪，失踪，失踪，失踪，失踪）; 
 
 myExcel.Worksheet wsBase; 
 myExcel.Worksheet wsDiff; 
 myExcel.Worksheet wsNew; 
 
 
 //创建reportBaseMinusNew.xlsx 
 excelApp.Visible = true; 
 excelApp.WindowState = myExcel.XlWindowState.xlMinimized; 
 myExcel.Workbook report = excelApp.Workbooks.Add（myExcel.XlWBATemplate.xlWBATWorksheet）; 
 
 report.SaveAs（currentWorkingDirectory +testReport.xlsx，
 Type.Missing，Type.Missing，Type.Missing，false，false，myExcel.XlSaveAsAccessMode.xlNoChange，
类型型号，型号，型号，型号，型号，型号，型号等）; 
 
 try 
 {
 wsBase =（myExcel.Worksheet）excelApp.Workbooks [Book1] .Sheets [Sheet1]; 
 wsNew =（myExcel.Worksheet）excelApp.Workbooks [Book2] .Sheets [Sheet1]; 
 wsDiff =（myExcel.Worksheet）excelApp.Workbooks [testReport.xlsx]。表[Sheet1]; 
} 
 catch（异常e）
 {
抛出新异常（Excel文件不包含格式正确的工作表）; 
} 
 
 //确定工作区域
 int lr1 = 0; 
 int lr2 = 0; 
 int lc1 = 0; 
 int lc2 = 0; 
 {
 lr1 = wsNew.UsedRange.Rows.Count; 
 lc1 = wsNew.UsedRange.Columns.Count; 
} 
 
 {
 lr2 = wsBase.UsedRange.Rows.Count; 
 lc2 = wsBase.UsedRange.Columns.Count; 
} 
 
 maxR = lr1; 
 maxC = lc1; 
 
 if（maxR  if（maxC  
 //将报表中的新数据复制到标准Excel文件中
 
 myExcel.Worksheet reportBook2MinusBook1; 
 reportBook2MinusBook1 =（myExcel.Worksheet）excelApp.Workbooks [testReport.xlsx]。表[Sheet1]; 
 
 //reportBook2MinusBook.Name =New; 
 
 reportBook2MinusBook1.UsedRange.Copy（（myExcel.Range）wsNew.Cells [maxR，maxC]）; 
 
 //现在关闭新数据报告，我们通过它
 //excelApp.Workbooks[\"testReport.xlsx\"].Close(false，false，false）; 
 
 
 
 // ============================ =================== 
 //比较单元格
 // =============== ================================ 
 //脱色，否则标准化工作表中的视觉格式
 wsDiff.get_Range（（myExcel.Range）wsDiff.Cells [1，1]，（myExcel.Range）wsDiff.Cells [maxR，maxC]）。Interior.Pattern = myExcel.XlPattern.xlPatternNone; 
 wsBase.get_Range（（myExcel.Range）wsBase.Cells [1，1]，（myExcel.Range）wsBase.Cells [maxR，maxC]）。Interior.Pattern = myExcel.XlPattern.xlPatternNone; 
 wsNew.get_Range（（myExcel.Range）wsNew.Cells [1，1]，（myExcel.Range）wsNew.Cells [maxR，maxC]）。Interior.Pattern = myExcel.XlPattern.xlPatternNone;对于（row = 1; row< = maxR; row ++）
 {
 for（col = 1; col <= maxC; col ++）
 $ b $ 
 //获取单元格值
 objNew =（（myExcel.Range）wsNew.Cells [row，col]）。Value2; 
 objBase =（（myExcel.Range）wsBase.Cells [row，col]）。Value2; 
 
 if（！equiv（objNew，objBase，tolerance））
 {
 areDifferences = true; 
 
 //标记不同的单元格
 //MessageBox.Show(\"test 1）; 
（（myExcel.Range）wsNew.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
（（myExcel.Range）wsBase.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
 
 if（（objNew == null））
 {
 //MessageBox.Show(\"test 2）; 
（（myExcel.Range）wsDiff.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
} 
 else if（objNew.GetType（）。ToString（）==System.String）
 {
 //MessageBox.Show(\"test 3）; 
（（myExcel.Range）wsDiff.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
} 
 else 
 {
 //MessageBox.Show(\"test 4）; 
（（myExcel.Range）wsDiff.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Yellow）; 
} 
} 
} 
} 
 
 //现在关闭报告我们通过他们
 excelApp.Workbooks [testReport 。.XLSX]保存（）; 
 
 excelApp.Workbooks [testReport.xlsx]。关闭（false，false，false）; 
 
 return areDifferences; 
} 
 
 
 ///确定两个对象是否等效
 ///数字在指定的容限内相当
 ///字符串是等效的如果它们是相同的
 /// obj1和obj2是被比较的两个对象
 /// tolerance是两个数字之间的最大差异，被认为是等价的
 
 private bool equiv（object obj1，object obj2，double tolerance）
 {
 if（（obj1 == null）&&（obj2 == null））
 {
 return真正; 
} 
 else if（（obj1 == null）||（obj2 == null））
 {
 return false; 
} 
 
 //如果两者都是数字
 if（IsNumeric（obj1））
 {
 if（IsNumeric（obj2））
 {
 if（Math.Abs​​（Convert.ToDouble（obj2） -  Convert.ToDouble（obj1））< tolerance）
 {
 return true; //如果它们在容限内
} 
 else 
 {
 return false; //如果他们在外面容忍
} 
} 
 else 
 {
 return false; //如果只有一个是数字
} 
} 
 
 //现在假设两者都是随机字符串
 else 
 {
 if（ （string）obj1 ==（string）obj2）
 {
 return true; 
} 
 else 
 {
 return false; 
} 
} 
} 
 
 
 //测试给定对象是否代表一个数字
内部静态bool IsNumeric（Object ObjectToTest）
 {
 if（ObjectToTest == null）
 {
 return false; 
} 
 else 
 {
 double OutValue; 
 return double.TryParse（ObjectToTest.ToString（）。Trim（），
 System.Globalization.NumberStyles.Any，
 System.Globalization.CultureInfo.CurrentCulture，
 out OutValue） ; 
} 
} 
  

解决方案

它出来：

  public bool createReport_NewMinusBase（string currentWorkingDirectory，string Book1，string Book2，double tolerance）
 {
容忍度= 0.0001; 
 myExcel.Application excelApp = new myExcel.Application（）; //创建一个新的Excel应用程序
 excelApp.Visible = false; //使用户可以看到Excel。 
 excelApp.Application.DisplayAlerts = false; 
 
 //对于COM对象交互有用
对象丢失= System.Reflection.Missing.Value; 
 
 //返回值
 bool wereDifferences = false; 
 
 //比较对象
对象objNew = null; 
 object objBase = null; 
 
 
 // source：http://www.codeproject.com/KB/office/csharp_excel.aspx 
 //xlApp.Workbooks.Open(reportFolder + reportName，0 ，false，5，，，false，myExcel.XlPlatform.xlWindows，，true，false，0，true，false，false）; 
 
 //打开BASE FILE 
 myExcel.Workbook excelWorkbook1 = excelApp.Workbooks.Open（@currentWorkingDirectory + Book1，0，
丢失，缺少，缺少，缺少，缺少，缺少，
失踪，失踪，失踪，失踪，失踪，失踪，缺失）; 
 // OPEN NEW FILE 
 myExcel.Workbook excelWorkbook2 = excelApp.Workbooks.Open（@currentWorkingDirectory + Book2，0，
 missing，missing，missing，missing，missing，missing，
失踪，失踪，失踪，失踪，失踪，失踪，失踪）; 
 
 myExcel.Workbook excelWorkbook3 = excelApp.Application.Workbooks.Add（myExcel.XlWBATemplate.xlWBATWorksheet）; 
 
 myExcel.Worksheet wsBase; 
 myExcel.Worksheet wsDiff; 
 myExcel.Worksheet wsNew; 
 
 
 try 
 {
 wsBase =（myExcel.Worksheet）excelApp.Workbooks [Book1] .Sheets [Sheet1]; 
 wsNew =（myExcel.Worksheet）excelApp.Workbooks [Book2] .Sheets [Sheet1]; 
 wsDiff =（myExcel.Worksheet）excelWorkbook3.Worksheets.get_Item（1）; 
} 
 catch 
 {
抛出新异常（Excel文件不包含格式正确的工作表）; 
 
} 
 
 
 //将Excel中的新复制到NEW（ - ）BASE
 myExcel.Worksheet source_sheet; 
 source_sheet =（myExcel.Worksheet）excelApp.Workbooks [Book2] .Sheets [Sheet1]; 
 source_sheet.UsedRange.Copy（）; 
 wsDiff.Paste（）; 
 
 
 
 
 //确定工作区
 int row = 0; 
 int col = 0; 
 int maxR = 0; 
 int maxC = 0; 
 
 int lr1 = 0; 
 int lr2 = 0; 
 int lc1 = 0; 
 int lc2 = 0; 
 {
 lr1 = wsNew.UsedRange.Rows.Count; 
 lc1 = wsNew.UsedRange.Columns.Count; 
} 
 
 {
 lr2 = wsBase.UsedRange.Rows.Count; 
 lc2 = wsBase.UsedRange.Columns.Count; 
} 
 
 maxR = lr1; 
 maxC = lc1; 
 
 if（maxR  if（maxC  
 // ====================================== =========== 
 //比较单元格
 // ======================= （row = 1; row< = maxR; row ++）
 
 
 {
 for（col = 1; col <= maxC; col ++）
 {
 //获取单元格值
 objNew =（（myExcel.Range）wsNew.Cells [row，col]）。Value2; 
 objBase =（（myExcel.Range）wsBase.Cells [row，col]）。Value2; 
 
 
 //如果它们不是equivilante 
 if（！equiv（objNew，objBase，tolerance））
 {
 areDifferences = true; 
 
 //标记不同的单元格
（（myExcel.Range）wsNew.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color 。红）; 
（（myExcel.Range）wsBase.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
 
 if（（objNew == null））
 {
（（myExcel.Range）wsDiff.Cells [row，col]）。Interior.Color = System.Drawing。 ColorTranslator.ToOle（System.Drawing.Color.Red）; 
} 
 else if（objNew.GetType（）。ToString（）==System.String）
 {
（（myExcel.Range）wsDiff.Cells [row， col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing.Color.Red）; 
} 
 else 
 {
（（myExcel.Range）wsDiff.Cells [row，col]）。Interior.Color = System.Drawing.ColorTranslator.ToOle（System.Drawing .Color.Yellow）; 
（（myExcel.Range）wsDiff.Cells [row，col]）。Value2 =（（myExcel.Range）wsNew.Cells [row，col]）。Value2  - （（myExcel.Range）wsBase.Cells [ row，col]）。Value2; 
} 
} 
 else //他们是等价的
 {
如果（（objNew == null））
 {
} 
 else if（objNew.GetType（）。ToString（）==System.String）
 {
} 
 else 
 {
（（myExcel。范围）wsDiff.Cells [row，col]）。Value2 =（（myExcel.Range）wsNew.Cells [row，col]）。Value2  - （（myExcel.Range）wsBase.Cells [row，col]）。 
} 
} 
} 
} 
 
 
 //复制格式
 myExcel.Range range1 = wsBase.get_Range（（ myExcel.Range）wsBase.Cells [1，1]，（myExcel.Range）wsBase.Cells [maxR，maxC]）; 
 myExcel.Range range2 = wsDiff.get_Range（（myExcel.Range）wsDiff.Cells [1，1]，（myExcel.Range）wsDiff.Cells [maxR，maxC]）; 
 range1.Copy（）; 
 range2.PasteSpecial（myExcel.XlPasteType.xlPasteColumnWidths）; 
 
 excelApp.Workbooks [Book1] .Close（false，false，false）; 
 excelApp.Workbooks [Book2] .Close（false，false，false）; 
 
 string Book3 =reporttestpc.xlsx; //\"reportBaseMinusNew.xlsx
 if（File.Exists（currentWorkingDirectory + Book3））
 {
 File.Delete（currentWorkingDirectory + Book3）; 
} 
 
 excelWorkbook3.SaveAs（currentWorkingDirectory + Book3，Type.Missing，Type.Missing，
 Type.Missing，false，false，myExcel.XlSaveAsAccessMode.xlNoChange，
类型。型号，型号，型号，型号，型号，型号，型号，型号等）; 
 
 //excelApp.Workbooks[Book3].Close(false，false，false）; 
 
 excelApp.Visible = true; 
 return areDifferences; 
} 
 
 
 ///确定两个对象是否等效
 ///数字在指定的容限内相当
 ///字符串是等效的如果它们是相同的
 /// obj1和obj2是被比较的两个对象
 /// tolerance是两个数字之间的最大差异，被认为是等价的
 
 private bool equiv（object obj1，object obj2，double tolerance）
 {
 if（（obj1 == null）&&（obj2 == null））
 {
 return真正; 
} 
 else if（（obj1 == null）||（obj2 == null））
 {
 return false; 
} 
 
 //如果两者都是数字
 if（IsNumeric（obj1））
 {
 if（IsNumeric（obj2））
 {
 if（Math.Abs​​（Convert.ToDouble（obj2） -  Convert.ToDouble（obj1））< tolerance）
 {
 return true; //如果它们在容限内
} 
 else 
 {
 return false; //如果他们在外面容忍
} 
} 
 else 
 {
 return false; //如果只有一个是数字
} 
} 
 
 //现在假设两者都是随机字符串
 else 
 {
 if（ （string）obj1 ==（string）obj2）
 {
 return true; 
} 
 else 
 {
 return false; 
} 
} 
} 
 
 
 //测试给定对象是否代表一个数字
内部静态bool IsNumeric（Object ObjectToTest）
 {
 if（ObjectToTest == null）
 {
 return false; 
} 
 else 
 {
 double OutValue; 
 return double.TryParse（ObjectToTest.ToString（）。Trim（），
 System.Globalization.NumberStyles.Any，
 System.Globalization.CultureInfo.CurrentCulture，
 out OutValue） ; 
} 
} 
 /// 
  

All I am trying to do is Open 2 Excel Files (both Sheet1), that have the same exact structure, but may have different numeric values, read through them and create a third file with "New" minus "Base". If the values are the same in both it doesn't matter if displays a 0 or 0.00 or nothing at all.

Ex: New File

Item  Price Discount
 A    10.00  1.00
 B     9.00  0.00


Account
Gross Sales 980000.00

Ex: Base File

Item  Price Discount
 A    10.00  2.00
 B     9.00  0.00


Account
Gross Sales 980000.00

Ex: Result File

Item  Price Discount
 A     0.00  -1.00
 B     0.00   0.00


Account
Gross Sales 0.00

Does anyone have an idea on how to accomplish this? Or a reference where I can find something similar? Thank You.

I got the code bellow from a previous example created by co-worker where he does something very similar. I attempted to addapt his code, results so far:

Base File: wherever there is a numerical value is colored red

New File: wherever there is a numerical value is colored red

Final File: Empty but the same cells are colored with Red and Yellow.

I was hoping there was a better cleaner way of doing this, any recomendations for the following code?

 public bool createReport_NewMinusBase (string currentWorkingDirectory, string Book1, string Book2, double tolerance)
    {
        myExcel.Application excelApp = new myExcel.Application();  // Creates a new Excel Application
        excelApp.Visible = true;  // Makes Excel visible to the user.

        int row = 0;
        int col = 0;
        int maxR = 0;
        int maxC = 0;

        //useful for COM object interaction
        object missing = System.Reflection.Missing.Value;

        //Return value
        bool wereDifferences = false;

        //Comparison objects
        object objNew = null;
        object objBase = null;

        //source: http://www.codeproject.com/KB/office/csharp_excel.aspx
        excelApp.Application.DisplayAlerts = false;

        //Open BASE FILE
         myExcel.Workbook excelWorkbook1 = excelApp.Workbooks.Open(currentWorkingDirectory + Book1, 0,
                                           missing, missing, missing, missing, missing, missing,
                                           missing, missing, missing, missing, missing, missing, missing);
        //OPEN NEW FILE
         myExcel.Workbook excelWorkbook2 = excelApp.Workbooks.Open(currentWorkingDirectory + Book2, 0,
                                           missing, missing, missing, missing, missing, missing,
                                           missing, missing, missing, missing, missing, missing, missing);

         myExcel.Worksheet wsBase;
         myExcel.Worksheet wsDiff;
         myExcel.Worksheet wsNew;


         //create reportBaseMinusNew.xlsx
         excelApp.Visible = true;
         excelApp.WindowState = myExcel.XlWindowState.xlMinimized;
         myExcel.Workbook report = excelApp.Workbooks.Add(myExcel.XlWBATemplate.xlWBATWorksheet);

         report.SaveAs(currentWorkingDirectory+ "testReport.xlsx", 
                       Type.Missing, Type.Missing, Type.Missing,false, false, myExcel.XlSaveAsAccessMode.xlNoChange, 
                       Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        try
        {
            wsBase = (myExcel.Worksheet)excelApp.Workbooks[Book1].Sheets["Sheet1"];
            wsNew =  (myExcel.Worksheet)excelApp.Workbooks[Book2].Sheets["Sheet1"];
            wsDiff = (myExcel.Worksheet)excelApp.Workbooks["testReport.xlsx"].Sheets["Sheet1"];
        }
        catch (Exception e)
        {
            throw new Exception("Excel file does not contain properly formatted worksheets");
        }

        //Determine working area
        int lr1 = 0;
        int lr2 = 0;
        int lc1 = 0;
        int lc2 = 0;
        {
            lr1 = wsNew.UsedRange.Rows.Count;
            lc1 = wsNew.UsedRange.Columns.Count;
        }

        {
            lr2 = wsBase.UsedRange.Rows.Count;
            lc2 = wsBase.UsedRange.Columns.Count;
        }

        maxR = lr1;
        maxC = lc1;

        if (maxR < lr2) maxR = lr2;
        if (maxC < lc2) maxC = lc2;

        //Copy new data from report into the standard Excel file

        myExcel.Worksheet reportBook2MinusBook1;
        reportBook2MinusBook1 = (myExcel.Worksheet)excelApp.Workbooks["testReport.xlsx"].Sheets["Sheet1"];

        //reportBook2MinusBook.Name = "New";

        reportBook2MinusBook1.UsedRange.Copy((myExcel.Range)wsNew.Cells[maxR, maxC]);

        //Close new data report now that we're through with it
        //excelApp.Workbooks["testReport.xlsx"].Close(false, false, false);



        //===================================================
        //Compare Cells
        //===================================================
        //decolorize and otherwise standardize visual format across worksheets
        wsDiff.get_Range((myExcel.Range)wsDiff.Cells[1, 1], (myExcel.Range)wsDiff.Cells[maxR, maxC]).Interior.Pattern = myExcel.XlPattern.xlPatternNone;
        wsBase.get_Range((myExcel.Range)wsBase.Cells[1, 1], (myExcel.Range)wsBase.Cells[maxR, maxC]).Interior.Pattern = myExcel.XlPattern.xlPatternNone;
        wsNew.get_Range((myExcel.Range)wsNew.Cells[1, 1], (myExcel.Range)wsNew.Cells[maxR, maxC]).Interior.Pattern = myExcel.XlPattern.xlPatternNone;

        for (row = 1; row <= maxR; row++)
        {
            for (col = 1; col <= maxC; col++)
            {
                //Get cell values
                objNew = ((myExcel.Range)wsNew.Cells[row, col]).Value2;
                objBase = ((myExcel.Range)wsBase.Cells[row, col]).Value2;

                if (!equiv(objNew, objBase, tolerance))
                {
                    wereDifferences = true;

                    //Mark differing cells
                    //MessageBox.Show("test 1");
                    ((myExcel.Range)wsNew.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                    ((myExcel.Range)wsBase.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

                    if ((objNew == null))
                    {
                        //MessageBox.Show("test 2");
                        ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                    }
                    else if (objNew.GetType().ToString() == "System.String")
                    {
                        //MessageBox.Show("test 3");
                        ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                    }
                    else
                    {
                        //MessageBox.Show("test 4");
                        ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                    }
                }
            }
        }

        //Close reports now that we're through with them
        excelApp.Workbooks["testReport.xlsx"].Save();

        excelApp.Workbooks["testReport.xlsx"].Close(false, false, false);

        return wereDifferences;
    }


    /// Determines whether two objects are equivalent
    /// Numbers are equivalent within the specified tolerance
    /// Strings are equivalent if they are identical
    /// obj1 and obj2 are the two objects being compared
    /// tolerance is the maximum difference between two numbers for them to be deemed equivalent

    private bool equiv(object obj1, object obj2, double tolerance)
    {
        if ((obj1 == null) && (obj2 == null))
        {
            return true;
        }
        else if ((obj1 == null) || (obj2 == null))
        {
            return false;
        }

        //if both are numeric
        if (IsNumeric(obj1))
        {
            if (IsNumeric(obj2))
            {
                if (Math.Abs(Convert.ToDouble(obj2) - Convert.ToDouble(obj1)) < tolerance)
                {
                    return true;    //If they are within tolerance
                }
                else
                {
                    return false;   //If they are outside tolerance
                }
            }
            else
            {
                return false;       //If only one is numeric
            }
        }

        //Now assuming both are just random strings
        else
        {
            if ((string)obj1 == (string)obj2)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }


    // Test whether a given object represents a number
    internal static bool IsNumeric(object ObjectToTest)
    {
        if (ObjectToTest == null)
        {
            return false;
        }
        else
        {
            double OutValue;
            return double.TryParse(ObjectToTest.ToString().Trim(),
                System.Globalization.NumberStyles.Any,
                System.Globalization.CultureInfo.CurrentCulture,
                out OutValue);
        }
    }

解决方案

Figured it out:

public bool createReport_NewMinusBase(string currentWorkingDirectory, string Book1, string Book2, double tolerance)
{
    tolerance = 0.0001;
    myExcel.Application excelApp = new myExcel.Application();  // Creates a new Excel Application
    excelApp.Visible = false;  // Makes Excel visible to the user.
    excelApp.Application.DisplayAlerts = false;

    //useful for COM object interaction
    object missing = System.Reflection.Missing.Value;

    //Return value
    bool wereDifferences = false;

    //Comparison objects
    object objNew = null;
    object objBase = null;


    //source: http://www.codeproject.com/KB/office/csharp_excel.aspx
    //xlApp.Workbooks.Open(reportFolder + reportName, 0, false, 5, "", "", false, myExcel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

    //Open BASE FILE
    myExcel.Workbook excelWorkbook1 = excelApp.Workbooks.Open(@currentWorkingDirectory + Book1, 0,
                                      missing, missing, missing, missing, missing, missing, 
                                      missing,missing, missing, missing, missing, missing, missing);
    //OPEN NEW FILE
    myExcel.Workbook excelWorkbook2 = excelApp.Workbooks.Open(@currentWorkingDirectory + Book2, 0, 
                                      missing, missing, missing, missing, missing, missing, 
                                      missing, missing, missing, missing, missing, missing, missing);

    myExcel.Workbook excelWorkbook3 = excelApp.Application.Workbooks.Add(myExcel.XlWBATemplate.xlWBATWorksheet);

    myExcel.Worksheet wsBase;
    myExcel.Worksheet wsDiff;
    myExcel.Worksheet wsNew;


    try
    {
        wsBase = (myExcel.Worksheet)excelApp.Workbooks[Book1].Sheets["Sheet1"];
        wsNew = (myExcel.Worksheet)excelApp.Workbooks[Book2].Sheets["Sheet1"];
        wsDiff = (myExcel.Worksheet)excelWorkbook3.Worksheets.get_Item(1);
    }
    catch
    {
        throw new Exception("Excel file does not contain properly formatted worksheets");

    }


    //Copy Sheet from Excel Book "NEW" to "NEW(-)BASE"
    myExcel.Worksheet source_sheet;
    source_sheet = (myExcel.Worksheet)excelApp.Workbooks[Book2].Sheets["Sheet1"];
    source_sheet.UsedRange.Copy();
    wsDiff.Paste();




    //Determine working area
    int row = 0;
    int col = 0;
    int maxR = 0;
    int maxC = 0;

    int lr1 = 0;
    int lr2 = 0;
    int lc1 = 0;
    int lc2 = 0;
    {
        lr1 = wsNew.UsedRange.Rows.Count;
        lc1 = wsNew.UsedRange.Columns.Count;
    }

    {
        lr2 = wsBase.UsedRange.Rows.Count;
        lc2 = wsBase.UsedRange.Columns.Count;
    }

    maxR = lr1;
    maxC = lc1;

    if (maxR < lr2) maxR = lr2;
    if (maxC < lc2) maxC = lc2;

    //===================================================
    //Compare Cells
    //===================================================

    for (row = 1; row <= maxR; row++)
    {
        for (col = 1; col <= maxC; col++)
        {
            //Get cell values
            objNew = ((myExcel.Range)wsNew.Cells[row, col]).Value2;
            objBase = ((myExcel.Range)wsBase.Cells[row, col]).Value2;


            //If they are not equivilante
            if (!equiv(objNew, objBase, tolerance))
            {
                wereDifferences = true;

                //Mark differing cells
                ((myExcel.Range)wsNew.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                ((myExcel.Range)wsBase.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

                if ((objNew == null))
                {                            
                    ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                }
                else if (objNew.GetType().ToString() == "System.String")
                {
                    ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                }
                else
                {
                    ((myExcel.Range)wsDiff.Cells[row, col]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                    ((myExcel.Range)wsDiff.Cells[row, col]).Value2 = ((myExcel.Range)wsNew.Cells[row, col]).Value2 - ((myExcel.Range)wsBase.Cells[row, col]).Value2;
                }
            }
            else //They are equivalent
            {                        
                if ((objNew == null))
                {
                }
                else if (objNew.GetType().ToString() == "System.String")
                {
                }
                else
                {                            
                    ((myExcel.Range)wsDiff.Cells[row, col]).Value2 = ((myExcel.Range)wsNew.Cells[row, col]).Value2 - ((myExcel.Range)wsBase.Cells[row, col]).Value2;
                }
            }
        }
    }


    // Copy formatting
    myExcel.Range range1 = wsBase.get_Range((myExcel.Range)wsBase.Cells[1, 1], (myExcel.Range)wsBase.Cells[maxR, maxC]);
    myExcel.Range range2 = wsDiff.get_Range((myExcel.Range)wsDiff.Cells[1, 1], (myExcel.Range)wsDiff.Cells[maxR, maxC]);
    range1.Copy();
    range2.PasteSpecial(myExcel.XlPasteType.xlPasteColumnWidths);

    excelApp.Workbooks[Book1].Close(false, false, false);
    excelApp.Workbooks[Book2].Close(false, false, false);

    string Book3 = "reporttestpc.xlsx"; //"reportBaseMinusNew.xlsx"
    if (File.Exists(currentWorkingDirectory + Book3))
    {
        File.Delete(currentWorkingDirectory + Book3);
    }

    excelWorkbook3.SaveAs(currentWorkingDirectory + Book3, Type.Missing, Type.Missing,
                         Type.Missing, false, false, myExcel.XlSaveAsAccessMode.xlNoChange,
                         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    //excelApp.Workbooks[Book3].Close(false, false, false);

    excelApp.Visible = true;
    return wereDifferences;
}


/// Determines whether two objects are equivalent
/// Numbers are equivalent within the specified tolerance
/// Strings are equivalent if they are identical
/// obj1 and obj2 are the two objects being compared
/// tolerance is the maximum difference between two numbers for them to be deemed equivalent

private bool equiv(object obj1, object obj2, double tolerance)
{
    if ((obj1 == null) && (obj2 == null))
    {
        return true;
    }
    else if ((obj1 == null) || (obj2 == null))
    {
        return false;
    }

    //if both are numeric
    if (IsNumeric(obj1))
    {
        if (IsNumeric(obj2))
        {
            if (Math.Abs(Convert.ToDouble(obj2) - Convert.ToDouble(obj1)) < tolerance)
            {
                return true;    //If they are within tolerance
            }
            else
            {
                return false;   //If they are outside tolerance
            }
        }
        else
        {
            return false;       //If only one is numeric
        }
    }

    //Now assuming both are just random strings
    else
    {
        if ((string)obj1 == (string)obj2)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}


// Test whether a given object represents a number
internal static bool IsNumeric(object ObjectToTest)
{
    if (ObjectToTest == null)
    {
        return false;
    }
    else
    {
        double OutValue;
        return double.TryParse(ObjectToTest.ToString().Trim(),
            System.Globalization.NumberStyles.Any,
            System.Globalization.CultureInfo.CurrentCulture,
            out OutValue);
    }
}
///

这篇关于NEW（ - ）BASE =结果Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！