How can I find cell and copy it to txt.file?

Question

0.00/5 (No votes)

See more:

Hello everybody.
I'd like to copy the value from the cell that lies next to my wanted cell.I would then like to write my copied value in a text document.

My individual steps:
1.Open Excel file
2.Find the cells
3.Create new text file
4.Write my value from xlxs file

I have problem with this line:

C#

if(ws.Cells[i].Value.ToString() == "Z_KomSilnice_L (24200)/7 ")

this show me error: System.Reflection.TargetInvocationException: 'Exception has been thrown by the target of an invocation.
Does anyone have any advice?
Thank you all for any help

What I have tried:

Form1:

using System;
using System.IO;
using System.Windows.Forms;

namespace WindowsFormsApp2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFile();
        }

        public void OpenFile()
        {
            Class1 excel = new Class1(textBox1.Text, 1);
            string path = textBox1.Text + ".txt";
            TextWriter tw = new StreamWriter(path, true);

            tw.Write(excel.ReadCell(0, 0));
            tw.Close();
        }
    }
}

Class1:

using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApp2
{
    class Class1
    {
        string path = "";
        _Application excel = new _Excel.Application();
        Workbook wb;
        Worksheet ws;
        private object excelWorksheet;

        public Class1(string path, int Sheet)
        {
            this.path = path;
            wb = excel.Workbooks.Open(path);
            ws = (Worksheet)wb.Worksheets[Sheet];
        }
        public string ReadCell(int i, int j)
        {
            for (i = 0; i < 26; i++)
            {
                for (j = 1; j < 100; j++)
                {
                    int first = 65 + i;
                    string coordinates = ((char)first).ToString() + j;

                    if(ws.Cells[i].Value.ToString() == "Z_KomSilnice_L (24200)/7 ")
                    {
                        return ws.Range[coordinates].Value;
                    }
                }
            }
            return ""; 
        }
    }
}

Posted 25-Nov-20 0:55am

dejf111

Updated 25-Nov-20 4:09am

v2

Add a Solution

Comments

Richard MacCutchan 25-Nov-20 8:36am

As I have mentioned a number of times you cannot get a single cell from just one index value.
The reference ws.Cells[i] returns a range, not a single cell, so Value has no meaning. You must provide values for both row and column to get a single cell.

~~I would also refer you back to the working solution I provided in your original question.~~

2 solutions

Solution 1

Unless you are trying to run a VBA macro in the Excel sheet (which is unlikely) then there is a problem with your Excel installation.

Uninstall Office on that PC and re-install. Then try again.

More or less what was said in the comments to Solution 1 at How can I copy text from cells in excel file?[^]

Posted 25-Nov-20 1:16am

CHill60

Comments

Richard MacCutchan 25-Nov-20 10:13am

No the problem was (partly) my fault; see below.

CHill60 25-Nov-20 10:38am

Fair enough, but that's a strange error message for trying to access a range - I've usually associated that error with running VBA in the Excel instance.

Richard MacCutchan 25-Nov-20 11:13am

I think it was because the range address was not valid.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Accepted Answer · 2020-11-25T04:09:00

Solution 2

OK, after further testing it seems there was still a flaw in my previous solution, for which I apologise. Change your ReadCell method to the following:

C#

public string ReadCell(int row, int column)
{
    column += 1;  // adjust column to Excel range value
    do
    {
        row++;  // adjust row to next Excel required row number
        if (ws.Cells[row, column].Value == null)
            break; // break when encountering a null cell value
        // test the next cell in the column for the key phrase
        if (ws.Cells[row, column].Value.ToString() == "Z_KomSilnice_L (24200)/7 ")
        {
            // if the key is found, return the cell in the next column
            // use the ToString method in case the value is not a string
            return ws.Cells[row, column + 1].Value2.ToString();
        }
    } while (true); // repeat until the loop breaks out at the last row

    return ""; // if key was not found return the empty string
}

Posted 25-Nov-20 4:09am

Richard MacCutchan

Updated 25-Nov-20 4:14am

v2

Comments

dejf111 26-Nov-20 3:45am

Thank you very much!!!
but now the line: if (ws.Cells[row, column].Value == null) again show error: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: System.__ComObject' does not contain a definition for 'Value'

Richard MacCutchan 26-Nov-20 5:56am

Try Value2 which also works for me. I wonder if there is something special about your Excel file that is causing this?

dejf111 26-Nov-20 6:04am

I have no idea and it's slowly starting to p........
Value2 has the same result,
Too bad I can't try it on another computer

Richard MacCutchan 26-Nov-20 7:01am

I am afraid that once again I cannot make any suggestions. If I use Value or Value2 it works fine. If I try without any property it says the reference is not valid. So whatever is happening on your system must be caused by something in Microsoft's interop software that I cannot replicate. You could try one of the Microsoft forums to see if anyone there can help.

dejf111 26-Nov-20 7:11am

but despite all that, thank you very much, you are a master!!!

Richard MacCutchan 26-Nov-20 7:13am

No, just like you, I am an aspiring developer. Although more hobbyist these days.

Richard MacCutchan 26-Nov-20 9:33am

Take a look at Excel - cell values issue[^], which suggests modifying the code to be:

if (((_Excel.Range)ws.Cells[row, column]).Value2 == null)

It works for me, but then so does the unadorned code, but still maybe worth a shot for you.

dejf111 27-Nov-20 6:14am

there is a shift already to links the same error to another line:

if (ws.Cells[row, column].Value.ToString() == "Z_KomSilnice_L (24200)/7 ")

Richard MacCutchan 27-Nov-20 6:41am

Yes I think you need to do it everywhere. An easier way would be something like:

_Excel.Range range = (_Excel.Range)ws.Cells[row, column];
if (range.Value.ToString() == "Z_KomSilnice_L (24200)/7 ")
{
    range = (_Excel.Range)ws.Cells[row, column + 1];
    return range.Value.ToString();
}

I am not sure why your code requires this extra binding, but it may be the version of C#, the .NET framework or the Excel Interop library.

dejf111 27-Nov-20 7:22am

Thank you very much I am very grateful to you i was already desperate enough, now only my slow computer could handle it :D

Richard MacCutchan 27-Nov-20 7:48am

You are welcome, and good luck.