Setting excel specific cells values

Question

1.00/5 (2 votes)

See more:

Greetings,

I am trying to set an Excel sheet specific cells starting at column A and second Excel sheet row. I wrote the following code

C#

if (!File.Exists(AppConfiguration.FilePath))
            {
                throw new FileNotFoundException("File Not Found. The requested template.xlsx was not found on the server");
            }

            Microsoft.Office.Interop.Excel.Application xlsx = new Microsoft.Office.Interop.Excel.Application();
            Workbook workbook = null;

            try
            {
                workbook = xlsx.Workbooks.Open(AppConfiguration.FilePath, ReadOnly: false, Editable: true);
                Worksheet worksheet = workbook.Worksheets[1];
                Microsoft.Office.Interop.Excel.Range cells = worksheet.Cells["$A"];
                List<Analytics> list = (List<Analytics>)data;

                for (int i = 0; i < list.Count; i++)
                {
                    ((Microsoft.Office.Interop.Excel.Range)cells[i + 1, 0]).Value = list[i].ProductShare;
                    ((Microsoft.Office.Interop.Excel.Range)cells[i + 1, 1]).Value = list[i].MarketPotential;
                }

                workbook.Save();

            }
            catch(Exception e)
            {
                throw new Exception("Error while processing file");
            }
            finally
            {
                workbook.Close(SaveChanges: true);
                xlsx.Quit();
                Marshal.ReleaseComObject(workbook);
            }

However, I always got an Exception at

((Microsoft.Office.Interop.Excel.Range)cells[i + 1, 0]).Value = list[i].ProductShare;

What I have tried:

I searched the internet and I tried all solutions I found, though, all failed!

Posted 9-Mar-18 5:06am

Amr Mohammad Rashad

Updated 9-Mar-18 8:16am

Add a Solution

Comments

Maciej Los 9-Mar-18 11:21am

Well, you have to admit that "I searched the internet and I tried all solutions I found, though, all failed! " is not descriptive at all!

F-ES Sitecore 9-Mar-18 11:21am

If you are getting an exception then there is a problem with your code.

Amr Mohammad Rashad 9-Mar-18 11:26am

Yes there is an error within the code and I need to know how to set the value to the cell that is all what I need

Richard Deeming 9-Mar-18 13:24pm

If you want someone to help you fix an exception, then first you need to tell us what the exception is.

Click the green "Improve question" button, and add the full details of the exception to your question.

Amr Mohammad Rashad 9-Mar-18 14:31pm

You are right I should paste what the original exception is. However, I wanted people to help me correcting the setting value statement which is an incorrect

4 solutions

Solution 1

C#

catch(Exception e)
            {
                throw new Exception("Error while processing file");
            }

That is a really good way to hide the problem that your code has. You have masked the actual exception and thrown a new one that does not provide any information to help you diagnose the problem. Get rid of that code and display the original exception's type and details so you can find out what is wrong with your code.

Posted 9-Mar-18 5:41am

Richard MacCutchan

Comments

Maciej Los 9-Mar-18 12:00pm

Some obvious things aren't obvious for everyone...
5ed!

Amr Mohammad Rashad 9-Mar-18 14:01pm

As you can see I can catch the original exception. However, you cannot throw the actual exception to the user. I hope you not throw your real exceptions to the user :). I can pass the arg (e) to the inner exception property of the new Exception I am throwing

Richard MacCutchan 10-Mar-18 3:56am

You obviously do not understand how to handle caught exceptions. The Exception object that you catch contains valuable information about the error that occurred, but you just drop it in the trash.

Amr Mohammad Rashad 12-Mar-18 17:20pm

Again Mr. Richard during debugging for sure exception information is useful but you do not want to show the end user an exception telling them, index was out of range, for example, because he will not understand what means index out of range, instead, I should showing him an error message he can understand like the one I wrote within my thrown Exception "Error while processing file" but as you can see I wrote the catch block with Exception e so that when I debug I can watch the e variable and know the details of the exception.

Richard MacCutchan 13-Mar-18 4:33am

I never said that you should show the user some obscure message. I said you should handle the exception properly.

Amr Mohammad Rashad 14-Mar-18 6:55am

What do you mean by handling the exception properly? I am debugging which mean when I enter the catch block I will be able to watch the exception variable "e" and know exactly the exception.

Richard MacCutchan 14-Mar-18 9:45am

Well if you don't understand now, then you never will.

Solution 3

Your exception handling is hiding the actual exception that is being thrown. Change it to:

C#

throw new Exception("blah bah", e);

Beyond that, I *think* rows and columns are 1-indexed as opposed to 0-indexed.

Posted 9-Mar-18 6:09am

#realJSOP

Updated 9-Mar-18 7:12am

v3

Comments

Richard MacCutchan 9-Mar-18 12:31pm

Er, have you seen his catch block?

Amr Mohammad Rashad 9-Mar-18 14:18pm

Again real exception should not be thrown to user which is 100% bad user experience you should, instead, throw another message to the user which is acceptable to him and he can understand!
Am right or wrong?

Solution 4

Try

C#

for (int i = 0; i < list.Count; i++)
{
    ((Microsoft.Office.Interop.Excel.Range)cells[i + 1, 1]).Value = list[i].ProductShare;
    ((Microsoft.Office.Interop.Excel.Range)cells[i + 1, 2]).Value = list[i].MarketPotential;
}

Cell A1 is cells(1,1).

Posted 9-Mar-18 8:17am

Patrice T

Comments

Amr Mohammad Rashad 9-Mar-18 14:25pm

I tried cells[rowindex, columnindex] but did not work to! I find many solution having lots of methods on either workbook or worksheet objects which I do not find and also that involve the using of row/column index but it did not work too. "Maciej Los" solution above worked find for me!

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)

Maciej Los · Accepted Answer · 2018-03-09T05:59:00

First of all, please read this: How to automate Microsoft Excel from Microsoft Visual C#.NET[^]

You have to use Namespace alias[^] as a shorthand of name to avoid repeating entire namespace. See:

C#

Excel = Microsoft.Office.Interop.Excel;

Do not forget to clean up. It means, you have to relase all com objects you created in code! Take a look at below example:

C#

    //Using section:
    //Excel=Microsoft.Office.Interop.Excel;
    //System.Runtime.InteropServices;


Excel.Application xlApp = new Excel.Application();
string sFileName = @"D:\ConflictData.xls";
Excel.Workbook xlBook = (Excel.Workbook)xlApp.Workbooks.Open(sFileName);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[2];

for(int i =1; i<=10; i++)
{
    xlSheet.Range["A" + i.ToString()].Value = i;
}

xlApp.Visible = true;

//clean up
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(xlSheet);
Marshal.ReleaseComObject(xlBook);
Marshal.ReleaseComObject(xlApp);

Setting excel specific cells values

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us