通过microsoft Interop c＃附加到现有的excel文件

Folks! I am writing a program that takes data from a website via selenium web driver. I am trying to create football fixture for our projects. So far, I accomplished to take date and time, team names and scores from the website. One or two days ago, I asked Is there any way to write data to excel file. I tried epplus, but it is not easy to use, so I started to use Interop and I have a couple of questions.

伙计们！我正在编写一个程序，通过selenium web驱动程序从网站获取数据。我正在尝试为我们的项目创建足球装备。到目前为止，我完成了从网站上获取日期和时间，团队名称和分数。一两天前，我问有没有办法把数据写入excel文件。我试过epplus，但它不容易使用，所以我开始使用Interop，我有几个问题。

Firstly, I need to check my file exist or not. Also, I need to access the current worksheet. Then, I need to check whether the first rows items exists or not. If it does not exist, I need to insert a new row to the index 1. I am not sure that I am doing right:

首先，我需要检查我的文件是否存在。另外，我需要访问当前的工作表。然后，我需要检查第一行项是否存在。如果它不存在，我需要在索引1中插入一个新行。我不确定我做得对：

using Excel = Microsoft.Office.Interop.Excel; //namespace

var filePath = new FileInfo(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) +
                "\\Test" + "\\" + pathName + "\\" + subFile + "\\" + pathName + ".xlsx");

Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
object misValue = System.Reflection.Missing.Value;

string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Test" + "\\" + pathName + "\\" + subFile + "\\" + pathName + ".xlsx";

if (filePath.Exists)
{
      Excel.Workbook wb = xlApp.Workbooks.Open(path,0, false, misValue, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
      Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.Item["Sheet1"];
      for (int i = 0; i < dateTime.Count; i++)
      {
            if (string.Compare(ws.Cells[i + 1, 1], dateTime[dateTime.Count - i -1]) != 0 && string.Compare(ws.Cells[i + 1, 2], firstTeam[dateTime.Count - i - 1]) != 0 && string.Compare(ws.Cells[i + 1, 3], secondTeam[dateTime.Count - i - 1]) != 0)
            {
                        ws.Cells.Rows.Insert(0);
                        ws.Cells[1, 1] = dateTime[dateTime.Count - i - 1];
                        ws.Cells[1, 2] = firstTeam[dateTime.Count - i - 1];
                        ws.Cells[1, 3] = secondTeam[dateTime.Count - i - 1];
             }
       }
}

I am trying to check data on the row, but i could not accomplished. My if condition not working I get this error: best overloaded method match for 'string.Compare(string, string)' has some invalid arguments' How can I do it? Also, Is my code right ?

我试图检查行上的数据，但我无法完成。我的if条件不起作用我得到这个错误：'string.Compare（string，string）'的最佳重载方法匹配有一些无效的参数'我怎么能这样做？另外，我的代码是对的吗？

2 个解决方案

#1

String.Compare() has some overloaded methods, you can check it here: https://msdn.microsoft.com/en-us//library/system.string.compare(v=vs.110).aspx

String.Compare（）有一些重载方法，您可以在这里查看：https：//msdn.microsoft.com/en-us//library/system.string.compare(v=vs.110).aspx

So, you should pass two strings in it, but you passed Cells and dateTime, Cells and firstTeam and Cells and secondTeam. So, you need to bring them to the same type of System.String. I prefer String.Equals() (https://msdn.microsoft.com/en-us//library/1hkt4325(v=vs.110).aspx) , instead of String.Compare(), because String.Equals() return boolean, so it`s not necessary to compare it with 0. Assuming that I dont know which of type are dateTime, fisrtTeam and secondTeam objects, for example, your if condition may look like this:

所以，你应该在其中传递两个字符串，但是你传递了Cells和dateTime，Cells和firstTeam以及Cells和secondTeam。因此，您需要将它们带到相同类型的System.String。我更喜欢String.Equals（）（https://msdn.microsoft.com/en-us//library/1hkt4325(v=vs.110).aspx），而不是String.Compare（），因为String.Equals（）返回布尔值，所以没有必要将它与0进行比较。假设我不知道哪个类型是dateTime，fisrtTeam和secondTeam对象，例如，你的if条件可能如下所示：

if (string.Equals(ws.Cells[i + 1, 1].Value.ToString(), dateTime[dateTime.Count - i -1].ToString()) && string.Equals(ws.Cells[i + 1, 2].Value.ToString(), firstTeam[dateTime.Count - i - 1].ToString()) && string.Equals(ws.Cells[i + 1, 3].Value.ToString(), secondTeam[dateTime.Count - i - 1]).ToString())

Definitely, you should avoid to write long conditions like that, becuase you can easally miss something.

当然，你应该避免写出这样的长期条件，因为你可以轻易错过一些东西。

In addition: inside of if body you assigned Cells to dateTime, to firstTeam and to the secondTeam, which are likely different types. So, it may fail.

另外：在if体内，你将Cells分配给dateTime，firstTeam和secondTeam，它们可能是不同的类型。所以，它可能会失败。

#2

Okay, firstly, I could not see .Value while using header using Excel = Microsoft.Office.Interop.Excel; So, I thought, there was not any usage. However, when I tried to use it, and it did not occur any error and my code works perfectly.

好的，首先，我使用Excel = Microsoft.Office.Interop.Excel使用标题时看不到.Value;所以，我想，没有任何用处。但是，当我尝试使用它时，它没有发生任何错误，我的代码完美地工作。

UPDATED VERSION:

更新后的版本：

using Excel = Microsoft.Office.Interop.Excel; //namespace
var filePath = new 
FileInfo(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) +
            "\\Test" + "\\" + pathName + "\\" + subFile + "\\" + pathName + ".xlsx");

Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
object misValue = System.Reflection.Missing.Value;

string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Test" + "\\" + pathName + "\\" + subFile + "\\" + pathName + ".xlsx";

if (filePath.Exists) // checks whether file exist or not
{
      Excel.Workbook wb = xlApp.Workbooks.Open(path,0, false, misValue, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
      Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.Item["Sheet1"];
      for (int i = 0; i < dateTime.Count; i++)
      {
            // check element already there or not 
            if (ws.Cells[i + 1, 1].Value.ToString() != dateTime[dateTime.Count - i - 1])
            {
                  ws.Rows["1"].insert(); // add new row for new data
                  ws.Cells[1, 1] = dateTime[dateTime.Count - i - 1];
                  ws.Cells[1, 2] = firstTeam[dateTime.Count - i - 1];
                  ws.Cells[1, 3] = secondTeam[dateTime.Count - i - 1];
            }
      }
                wb.SaveAs(path, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlShared, misValue, misValue, misValue, misValue, misValue);
                wb.Close(true, misValue, misValue);
                xlApp.Quit();
}

#1