Re: Using Excel.WorksheetFunction in C# - C# Discussion Boards

20-Sep-04 1:00

I am trying to use Excel.WorksheetFunction.VLookup but cant use it. Can someone give me an example of how i should use it. I dont seem to be able to get up any of the WorksheetFunction members when using VS which leads me to think that i need to initialise something.

sreejith ss nair20-Sep-04 1:08

sreejith ss nair

20-Sep-04 1:08

hi,

Check this article.

http://www.codeproject.com/csharp/csharp_excel.asp
http://www.codeproject.com/aspnet/getsheetnames.asp

Wink | ;)

**************************
S r e e j i t h N a i r
**************************

itstime9820-Sep-04 1:39

20-Sep-04 1:39

Thanks for your input but they dont use any WorksheetFunctions so i still need help

itstime9820-Sep-04 1:50

20-Sep-04 1:50

Excel.WorksheetFunction wsf = ThisApplication.WorksheetFunction;
Thought i had found all my answers but my app doesnt have ThisApplication defined as i am not using the VS MS office templates. So what do i need to replace ThisApplication with to get this working?

ODS21-Sep-04 1:29

ODS

21-Sep-04 1:29

Hi,

I hope I'm understanding the problem correctly. You can get an reference to the app through:
Excel.Application app = new Excel.ApplicationClass();

Then there are two ways I can think of to evaluate functions:

object result = app.Evaluate("=LEN(\"ABC\")");<br />
double lnVal = app.WorksheetFunction.Ln(System.Math.E);

and don't forget to quit Excel:
app.Quit();

Hope this helps

itstime9821-Sep-04 18:46

21-Sep-04 18:46

Thankyou, that was the answer i was looking for.

Form.Close()

exhaulted20-Sep-04 0:43

20-Sep-04 0:43

Hi all,
I've wrote a scheduling application for someone and it needs to be running permanently in the system tray which is all done and works fine. During testing i came up with a problem though.

When Form.Close() is called i check for a password that has been hardcoded into the application. When the PC is shut down i want the program to close without user intervention as the machines will be used off site and the admins will be the only ones with the password.

In short i need to be able to check for a password when Form.Close() is called by the user and not check when it is called by the System shutting down.

Hope you can help.

Kev

sreejith ss nair20-Sep-04 0:58

sreejith ss nair

20-Sep-04 0:58

hi,

You can check the user name and password when you call form.close() method. If you want to do some additional work(checking user name and pwd) on the time of closing then you need to override the oncloseing event.

protected override void OnClosing(CancelEventArgs e)
{
try
{
if(MessageBox.Show("This Will Close your form. Do You Want To Continue ?","Conformation",MessageBoxButtons.YesNo,MessageBoxIcon.Information) == DialogResult.No)
{
e.Cancel = true;
//This block will execute if he refuse to close the form.

}
else
{
//This block will execute if he deside to close the form. So you can put your logic here for checking the credentials.
e.Cancel = false;
}
base.OnClosing (e);
}
catch(Exception Ex)
{
MessageBox.Show(Ex.Message.ToString(),"Information",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
}

This will help you to check the user name and password.

And i am not much sure about how handle the shutdown process.;)

**************************
S r e e j i t h N a i r
**************************

exhaulted20-Sep-04 1:32

20-Sep-04 1:32

OK, Lets try to make things a little clearer.
I can already check for the password using the Form.Closing event.

<br />
private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)<br />
{<br />
	//Write the details<br />
	SaveSchedules();<br />
<br />
	//Check for the password<br />
	if (!(CheckPassword()))<br />
            e.Cancel = true;<br />
	else<br />
	//Hide the notify icon<br />
	    notifyIcon.Visible = false;<br />
}

CheckPassword() returns a bool depending on the password entered.
The above bit is the easy part, what i need to do is to know wether
the user is closing the aplication by clicking on the cross on the form
or the system is closing the application because the user has tried to log
off / shut down the PC.

Kev
P.S. How do you get the Yellow background behind code samples??

mainly p.s.

yoaz20-Sep-04 2:01

yoaz

20-Sep-04 2:01

exhaulted wrote:
P.S. How do you get the Yellow background behind code samples??

using the menu below (if u reply a message) or using the "code" tag. Wink | ;)

to u'r question, i'm not sure u can, in managed code.

there are no facts, only interpretations

Re: mainly p.s.

exhaulted20-Sep-04 3:09

20-Sep-04 3:09

The code tag just makes things red.
I wondered how people put large code samples in with yellow backgrounds, like in Articles

Kev

Re: mainly p.s.

Charlie Williams20-Sep-04 5:00

Charlie Williams

20-Sep-04 5:00

Use <pre> tags.

Charlie

if(!curlies){ return; }

Re: mainly p.s.

exhaulted20-Sep-04 5:17

20-Sep-04 5:17

thanks

Kev

Salil Khedkar20-Sep-04 2:10

Salil Khedkar

20-Sep-04 2:10

Process the OnEndSession event. This occurs when the Windows session ends. Do what you need to do here and set a flag so that OnClose does what it needs (or dosen't need) to do. Wink | ;)

[Clarification: The events which I have described are MFC names for the corresponding Window events. Please find the .NET equivalents yourself. They must be something like: SessionEnding and Closing]

exhaulted20-Sep-04 3:15

20-Sep-04 3:15

All i can find in the help for OnEndSession relates to MFC and C++. Assuming i can use this in C#, what do i have to do?

Kev

Heath Stewart20-Sep-04 6:55

Heath Stewart

20-Sep-04 6:55

There is no OnEndSession method to override.

There is, however, the SystemEvents.SessionEnding event that you can handle. This works for all currently released versions of .NET, as well as the upcoming 2.0 Framework:

SystemEvents.SessionEnding += new SessionEndingEventHandler(OnSessionEnding);
// ...
private void OnSessionEnding(object sender, SessionEndingEventArgs e)
{
  // In your case, you probably don't care about the <code>e.Reason</code>
}

Implement your code in the handler, but do it fast. If you don't, your application will appear hung and Windows will prompt the user to kill it, which could leave your application data in an unsuitable state.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles]

exhaulted20-Sep-04 22:22

20-Sep-04 22:22

Well it sounded good but i still have the problem of the Close() method being called by windows when i attempt to log off or shut the PC down.

my code in the OnSessionEnding method is as follows

SaveSchedules();
Application.Exit(); //Bypasses the form.Close() method

Thats it, nice and quick. But, when the form is closing a call is made to CheckPassword() which is what is causing the problem. The end user will not know the password so they cant shut the scheduler down while the pc is running. If they log off / shut down the pc then i want the application to exit without checking the password.

Below is the offending event handler

private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
//Write the details
SaveSchedules();

//Check for the password
if (!(CheckPassword()))
e.Cancel = true;
else
//Hide the notify icon
notifyIcon.Visible = false;
}

Thanks for all your help

Kev

Salil Khedkar20-Sep-04 23:30

Salil Khedkar

20-Sep-04 23:30

Kev, I think I told you to use a flag. Application.Exit() will still try to close the window hense call the handler.

Let me elaborate:

In Form1_SessionEnding()
Here you will do a SaveSchedules() and set a flag called bShutdownStarted=true

In Form1_Closing()

Here you will first check <br />
if(bShutdownStarted==true)<br />
// Do nothing<br />
else<br />
// CheckPassword() and other stuff

I am assuming that Form1_SessionEnding() will always be called before Form1_Closing() because logically it should happen so. Correct me if I am wrong. Kudos to Heath for the delegate example.

[A look at SystemEvents.SessionEnding MSDN page tells me what gets called first is not deterministic unless you override the WndProc function as per the example on the page]

exhaulted21-Sep-04 4:11

21-Sep-04 4:11

Salil,

2 things, firstly, Application.Exit() doesn't fire the form_closing event.
I tried calling Application.Exit from a button click and it just shut the app down without asking me for the password.

Secondly, Session Ending is called after Form_Closing so i can't simply set a flag.

Cheers

Kev

Heath Stewart21-Sep-04 5:28

Heath Stewart

21-Sep-04 5:28

No, Application.Exit doesn't fire the Form.Closing event, nor should it. Calling Application.Exit is the same as sending WM_QUIT to your message pump. When you close a form, you're essentially sending WM_CLOSE.

The fact that the events happen in the wrong order means you're not doing something right. You add a handler for the SystemEvents.SessionEnding in your form's instance (not in Main - your default entry point - since Application.Run is a blocking call). Set a flag then call Close. This sends WM_CLOSE to your window proc and your Closing event handler is called.

The previous post is correct then, and recommended was I was going to: use state variables. A simple bool would do.

The SystemEvents.SessionEnding event (not to be confused with SystemEvents.SessionEnded; if that's fired there's nothing you can do, really, at least as far as prompting for user input) handles the WM_QUERYENDSESSION message which is sent to application message pumps. This has nothing to do with WM_CLOSE or WM_QUIT. It simply notifies the window proc that a request to end the interactive session has been posted.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles]

exhaulted21-Sep-04 6:23

21-Sep-04 6:23

Heath, just to check i've got things right and we are both going along the same lines of thought.

I have a bool called closing in the form instance.

My forms constructor

public Form1()
{
InitializeComponent();
closing = false;
SystemEvents.SessionEnding += new SessionEndingEventHandler(OnSessionEnding);
}

Form1_Closing

private void Form1_Closing(object sender, System.ComponentModel.CancelEventArgs e)
{
//Write the details
SaveSchedules();

//Check the state variable
if (!(closing))
{
//Check for the password
if (!(CheckPassword()))
e.Cancel = true;
}
}

OnSessionEnding

private void OnSessionEnding(object sender, SessionEndingEventArgs e)
{
//Save the schedules
SaveSchedules();

//Set the state variable
closing = true;
}

Thats it, all the relevant code as far as i'm aware.
If the user closes the form manually then i want checkpassword to be called.
This works OK.
If the user enters the correct password, CheckPassword returns true and
e.Cancel remains false, the application exits nicely.

If the user logs off of the machine or shuts it down i don't want checkpassword to be called. To accomplish this, as i was advised in the previous posts i am using the state variable closing.
The closing variable is set to true in the OnSessionEnding method.

This is what i believe has been suggested but the CheckPassword function is still called. If i've misunderstood whats been said in the previous posts then please let me know so i can make the relevant changes.

Thanks for all your help and patience

Kev

Heath Stewart21-Sep-04 6:37

Heath Stewart

21-Sep-04 6:37

Again, you should call Close in your SystemEvents.SessionEnding event handler, after setting the state variable.

The problem is that canceling the WM_CLOSING notification message (setting CancelEventArgs.Cancel to true in your Form.ClosingEvent) won't affect the WM_QUERYENDSESSION notification message (your SystemEvents.SessionEnding handler). I would recommend implementing the same logic into both handlers:

private void OnSessionEnding(object sender, SessionEndingEventArgs e)
{
  e.Cancel = !CheckPassword();
}

The big problem here is that prompting the user takes time (mostly for the user to type their password, unless you're authenticating the password across some wire format or against a slow database; hardcoding it into the assembly is a bad idea since even a tool that comes with the free SDK can read it easily) - more time than Windows is willing to give it when shutting down. The application will appear hung and the user will be prompted to end the task (process) or cancel the shutdown. Inevitably, the user will blame your program.

This posting is provided "AS IS" with no warranties, and confers no rights.

Software Design Engineer
Developer Division Sustained Engineering
Microsoft

[My Articles]

exhaulted21-Sep-04 22:44