Click here to Skip to main content
14,979,508 members
Please Sign up or sign in to vote.
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
Updated 9-Mar-18 8:16am
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

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.
   
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.
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);
   
Comments
Amr Mohammad Rashad 9-Mar-18 14:19pm
   
Thanks a lot your solution worked for me perfectly! :) Thank you for your time and consideration. Thanks for all people tried to help! giving me positive or negative help!
Maciej Los 9-Mar-18 14:58pm
   
You're very welcome.
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.
   
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?
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).
   
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!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900