append on existing excel file via microsoft Interop c#

Question

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.

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:

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 ?

Can you provide some more information, please: what is dateTime, firstTeam, secondTeam? What you could not accomplished? Any errors? — Nikita V
– Nikita V, Commented Sep 4, 2017 at 1:34
I also wrote what i am trying to accomplished top of the code — Kaan Taha Köken
– Kaan Taha Köken, Commented Sep 4, 2017 at 6:49

Kaan Taha Köken · Accepted Answer · 2017-09-04 10:46:38Z

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.

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();
}

Nikita V · Accepted Answer · 2017-09-04 08:33:37Z

0

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

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:

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.

edited Sep 4, 2017 at 8:33

answered Sep 4, 2017 at 8:08

Nikita V

4875 silver badges11 bronze badges

5 Comments

Kaan Taha Köken Over a year ago

my data types are strings. I tried your suggestion, but it did not work. I think the problem is right now program does not read the cell

Nikita V Over a year ago

Can you comment all of "if", and then inside of for loop just write: var cells = ws.Cells[i + 1, 1]; var dt = dateTime[dateTime.Count - i -1]; var ft = firstTeam[dateTime.Count - i - 1]; var st = secondTeam[dateTime.Count - i - 1]; and then put a breakpoint on closing "}" of the foor loop and debug your program. In Locals window you will see current types of that variables.

Kaan Taha Köken Over a year ago

var cells = ws.Cells[i + i]; gets null, it did not read it at all. After that i got this error : hresult 0x800a03ec

Nikita V Over a year ago

cells in this case is declared, but not initialized, thats why you see null at first, but when debugger try to evaluate the right side it provides an error. Check you typed "var cells = ws.Cells[i + 1, 1];" instead of "var cells = ws.Cells[i + i];"

Kaan Taha Köken Over a year ago

I guess ,It does not read cell from excel file. I need to figure it out how to solve

Collectives™ on Stack Overflow

append on existing excel file via microsoft Interop c#

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related