How can I copy text from cells in excel 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

Does anyone have any advice?
Thank you all for any help

25.11. I've improved it a bit but it's still not it
I now have these errors:

Error CS0161 'Class1.ReadCell(int, int)': not all code paths return a value

What I have tried:

Form1:

C#

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:

C#

<pre>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.Range[coordinates].Value == "Z_KomSilnice_L (24200)/7 ")
                    {
                        return ws.Range[coordinates].Value;
                    }
                    else
                        return "";
                }
            }
        }
     }

Posted 2-Nov-20 2:54am

dejf111

Updated 24-Nov-20 23:19pm

v4

Add a Solution

Comments

Richard MacCutchan 2-Nov-20 9:32am

Step through your code with the debugger and see exactly what items are being found.

dejf111 3-Nov-20 4:20am

But I'd like public Excel to throw a bug

Richard MacCutchan 3-Nov-20 9:02am

Why not just do what I suggested and actually see what happens as each instruction executes? It will reveal so much more information, and you will learn more than one useful thing as you do it.

dejf111 4-Nov-20 3:30am

So I tried and I got into trouble that the program couldn't find the file

Richard MacCutchan 5-Nov-20 3:36am

Well that is easy to fix.

dejf111 6-Nov-20 3:01am

can I ask how? because the file is there and the path should be correct

Richard MacCutchan 6-Nov-20 3:03am

Obviously it is not, or the program would find it.

dejf111 11-Nov-20 4:40am

I'm still lost :D

Richard MacCutchan 11-Nov-20 5:06am

There is nothing anyone here can do to find a file that is on your system.

dejf111 11-Nov-20 5:14am

the search is no longer a problem, but that time is the problem with if (ws.Cells[i, j].Value2 = "Z_KomSilnice_L (24200)/7 ")

Richard MacCutchan 11-Nov-20 5:22am

And you expect us to guess what you mean by that?

dejf111 11-Nov-20 5:35am

Exception Unhandled
Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'Cannot implicitly convert type 'string' to 'bool'

Richard MacCutchan 11-Nov-20 6:17am

The message is clear you are trying to use a string where the system expects a bool. And a glance at the line of code where the error occurs will show you what is wrong:

if (ws.Cells[i, j].Value2 = "Z_KomSilnice_L (24200)/7 ")

A compare operation requires a double == to be valid.

dejf111 20-Nov-20 4:36am

now there is an error that == cannot be applied to a string

Richard MacCutchan 20-Nov-20 4:47am

While not recommended it works fine for strings. Please use the Improve question link above show the actual code and the exact error message that you receive.

dejf111 20-Nov-20 5:06am

qestion is improved, thank you so much
that you care about me :)

Richard MacCutchan 20-Nov-20 5:17am

The ws.Cells[i] reference does not return a String object so you need to call ToString on the Value property:

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

dejf111 20-Nov-20 5:21am

now is error ''System.__ComObject' does not contain a definition for 'Value''

Richard MacCutchan 20-Nov-20 5:23am

Try Value2.

dejf111 20-Nov-20 5:31am

unfortunately the same

Richard MacCutchan 20-Nov-20 5:41am

You are missing an index from your offset reference, it should be ws.Cells[i, j].Value2 as you have already used above.

dejf111 20-Nov-20 6:07am

even if I add, there will be no change

Richard MacCutchan 20-Nov-20 6:40am

Well I am afraid I am a bit lost now. I have tried similar code on my system which works. Try the following:

string cellvalue = ws.Cells[i, j].Value2 as String; // Value or Value2 should both work
if (cellvalue == "Z_KomSilnice_L (24200)/7 ")

dejf111 20-Nov-20 6:55am

thank you very much for your help unfortunately still the same error message

Richard MacCutchan 20-Nov-20 7:32am

I have just tried a few more things and discovered that I need the following using statement added to my project:

using Microsoft.CSharp.RuntimeBinder;

Richard MacCutchan 25-Nov-20 5:09am

If either of the loops in the ReadCell method completes without meeting the conditions in the if clauses then there is no return statement to deal with it. But the method must return a string in all cases. Just add a return ""; at the end.

dejf111 25-Nov-20 5:15am

you are right, thank you !!!

2 solutions

Solution 1

I have copied your code and with a couple of minor changes it works:

C#

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(@"C:\Users\info\Documents\Zkouska.xlsx", 1);
            string path = @"C:\Users\info\Documents\new.txt";
            TextWriter tw = new StreamWriter(path, true);
            String text = excel.ReadCell(0, 0); // capture the returned value
            tw.Write(text);
            tw.Close();  // close the output stream
        }
    }
}

And the Class1.cs file

C#

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;

        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)
        {
            i++;
            j++;
            if ((ws.Cells[i, j].Value2 == "Z_KomSilnice_L (24200)/7 "))
            {
                return ws.Cells[i, j++].Value2; // get the field value
            }
            else
                return "";
        }
    }
}

Posted 20-Nov-20 3:33am

Richard MacCutchan

Updated 20-Nov-20 3:38am

v2

Comments

CHill60 20-Nov-20 10:09am

5'd. You've put a lot of effort into this.

Richard MacCutchan 20-Nov-20 10:19am

You are very kind, but I had much of the code already. And creating a project from OP's code was not exactly difficult. It was just one of those itches I needed to scratch.

dejf111 23-Nov-20 3:04am

I don't know where the error is but for me it still writes the same error message, but thank you for your help. I will try to export xlxs file to string and then search the string

Richard MacCutchan 23-Nov-20 4:00am

Have you changed the code to be exactly as above? As I said, this code does exactly what it is supposed to do.

dejf111 23-Nov-20 4:12am

I changed everything according to you but the result is the same

Richard MacCutchan 23-Nov-20 4:19am

What is the error and where does it occur? What version of Excel are you using?

dejf111 23-Nov-20 5:24am

if ((ws.Cells[i, j].Value2 == "Z_KomSilnice_L (24200)/7 "))
error is
Error CS1061 'object' does not contain a definition for 'Value2' and no accessible extension method 'Value2' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)

Excel is from package microsoft 365
but my license probably expired

Richard MacCutchan 23-Nov-20 5:29am

If it was a licence issue then i would expect a problem earlier in the code. But I am at a loss to understand why it works for me and not for you.

dejf111 23-Nov-20 5:38am

but it has happened to me many times that the code worked for someone but not me

Richard MacCutchan 23-Nov-20 5:49am

Then there is something definitely wrong with your installation. Unfortunately it is impossible to tell what that could be. You need to check which version of the Interop.Excel reference you have in your application. On my system it is Microsoft Excel 16.0 Object Library, version 1.9.0.0.

dejf111 23-Nov-20 6:52am

I use the same as you

Richard MacCutchan 23-Nov-20 7:13am

Well I am totally at a loss then, sorry.

dejf111 23-Nov-20 7:28am

never mind, but thank you very much for your help and your time!!!

Richard MacCutchan 23-Nov-20 7:30am

You are welcome, and sorry we could not solve it.

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-24T23:19:00

Your ReadCell method is incorrect, you have the following:

C#

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.Range[coordinates].Value == "Z_KomSilnice_L (24200)/7 ")
            {
                return ws.Range[coordinates].Value;
            }
            else
                return "";
        }
    }
}

First, you are missing a return at the end for the case when both loops complete without finding the string you want.

Second, you are ignoring the values passed in which denote the cell to test.

And third, your first range value in the loop will be "A1", but if that does not contain the search string then the method will return the blank string, and not test any further.

You should change your code to:

C#

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.Range[coordinates].Value == "Z_KomSilnice_L (24200)/7 ")
            {
                return ws.Range[coordinates].Value;
            }
        }
    }
    return ""; // string not found in any cell A1:Z99
}

However, that is a very wasteful piece of code. You should use the values of i and j that are passed in to select the cell to test.

I am still at a loss to understand why the code that I tested successfully does not work on your system.