Excel (Driving me crazy) 1

[ronin2307]

so I decided it would be ok to learn a bit of C# and now I am about to go ona rampage. Why does it seem that VB is so much easier to use???
anyway here is the issue

Through my app I create an excel sheet and save it as such:

xlBook.SaveAs("C:\\Program Files\\Hawkeye Customs\\Ship Margin Reports\\Reports\\" + strFileName + ".xls",
XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

so far so good. Now I open it up again to do some formatting using this code:

Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Open(strFileName, 0, false, 5, "", "", false,
XlPlatform.xlWindows, "",true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Worksheet)xlBook.Worksheets.get_Item("Sheet1");

I am guessing all is good so far. At this point I would like to convert some cell from text to numeric values. in VB it was simple:

xlSheet.Cells(2, 5) = CDbl(xlSheet.Cells(2, 5).value)

It eludes me how to do this very same thing in C#. Any help would be great.

Also I am not sure what is going on with this as well, but when I do this:

for (int intR = 0; intR <= iRows - 1; intR++)
{
xlRange = xlSheet.get_Range("G" + (intR + 3).ToString(), "G" + (intR + 3).ToString());
Debug.WriteLine(xlRange.Cells.Value2);
}

I get 2 as a result for every row, yet the value is the particular cell in the excel sheet that is created is totally different.

Any help will be greatly appreciated
Thanx

 

[onpnt]

Why does it seem that VB is so much easier to use

Probably because that is what you used and this isn't. Kind of like learning Spanish when you can't even say K?

Are you referencing anything for C#.NET classes, methods ext..? You may have wanted to start with something more simple if you did not and build up to something like this.

Your first step to learn a new language should be to (sense it's so easy ;-)) write it in your comfort language. Then as long as you know exactly what is going on in your code and what every built in conversion function does along with the class and methods you should have no problem. Search a C#.NET reference for the same types of classes, methods etc... and then study the syntax and make your changes slowly.

are you refering to VB6 or VB.NET. If you are on VB.NET then the task should be easier. The classes are all there and you should be ahead of yourself. Things like CDbl() "Which should not be used in .NET anywhere" just go to .ToDouble

They did half the work for you
You should read the language spec's prior also

and help on your Excel read here


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[ronin2307]

First of all typecasting is not the problem. It's the fact that vb.Net has certain methods and properties related to the Excel COM that C# does not.
In particular the CELL.VALUE property. Second of all porting the code from an already existing app in VB to C#, specially with .Net should not be that difficult. That's the reason for the whole .Net platform anyway.
FInally, I have been looking through MSDN and various other sources and haven't found anybody who even remotly had the same problem.
So instead of giving me a lecture on how to learn a language, if you have any advice please point me in the right direction otherwise don't even bother replying

 

[misterstick]

</flame>

double d = double.Parse("1.111");
double d1;
if ( double.TryParse("1.111", d1) )
{
  // puts value in d1
}

mr s. <

 

[onpnt]

Wow. Well taht's cool, that's the best advice I have so I guess I'm not very technically apt to this type of thing.

Have fun with it and I hope one day you get it.

btw...CELL.VALUE. Yeah it pretty much does work the same way.


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[ronin2307]

nobody is questioning you technical ability. The only thing I don't like is the suggestion that I should take an "easier" way to learn a language.
What I am currently trying to do is not rocket science and I have done it numerous times with VB.
Once again this has less to do with C# in general and more with what the Excel COM exposes to various languages.

Once again VB (works):
xlSheet.Cells(2, 5) = CDbl(xlSheet.Cells(2, 5).value)

C# does not since there is not a .Value property (at least IntelliSense is not displaying it for me and the code doesn't compile error-free:

xlSheet.Cells[intR + 3, 7].Value = (decimal)xlSheet.Cells[intR + 3, 7].Value;

and for the sake of the argument ignore the typecasting

 

[ronin2307]

btw, I had read the codeproject link you sent already, but could not make use of it. As I said initially, the values that I am reading are always "2" for whatever reason for any given cell, and that is what I am trying to figure out how to fix. And it also does not say how to SET a cell value.

 

[Walid]

Hey man,

Setting the value of a Cell is very simple task

oSheet.Cells[1, "A"] = 2;
oSheet.Cells[1, 1] = 2;

Why you get the same 2 for every row, I simply don’t know but you can set a break point and see exactly what is the range object contains in each iteration. It works fine in this sample code

using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics; 

namespace ExcelAutomation
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;
            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
                
                
                oSheet.Cells[1, "G"] = "1";
                oSheet.Cells[2, "G"] = "2";
                oSheet.Cells[3, "G"] = "3";
   
                for (int intR = 1; intR <= 3; intR++)
                {
                    oRng = oSheet.get_Range("G" + (intR).ToString(), "G" + (intR ).ToString());
                    Debug.WriteLine(oRng.Cells.Value2);
                    [b]Debug.WriteLine(oRng.Value2);[/b]
                    // Then we can change the value of the cell to a numbers instead of string 
                    //oRng.NumberFormat =  "0.00";


                }
                Console.ReadLine();


            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.Message); 
            }
        }
     }
}

BTW, if you are not going to manipulate the cells inside a specific range (set color, format, etc) it is sufficient to get the value using

oRng.Value2

The reason the property Range.Value is not showing in the intellisense is that this property is parameterized, meaning you can send a parameter to it (the parameter is a member of an enumeration) so that that can return the value of the range in either an XML recordset format or Excel's XML format. C# doesn’t support that feature.

Regarding the casting you would like to make, I think the easiest way to do it in Excel is to format the range not to replace the values in it. For an example, just uncomment the following line

//oRng.NumberFormat =  "0.00";

In the previous code.

If you have any more questions, please don’t hesitate to ask. Good luck


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[ronin2307]

thank you very much for your very thorough explanation.
I'll give it a shot ans see how it works. I am fairly convinced that I tried that before and that it didn't work, but I'll try again.

Once again many thanx

 

[ronin2307]

well I think i finally have this one wrapped up.
For whatever reason I used 2 different functions in this whole mess ( one for export to excel and one for formatting the excell sheet)
Problem was somewhere in the formating function, so as soon as I put the code back in the export function everything started working as expected.

Your advice has been great, so many thanx again

 

[Walid]

You are very welcome. I am gald you got it to work and I respect your persistance.

Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch