How to save excel file without GUI notifications?

Question

I have an app that is running on a server and that has to write some stuff to excel files. When I want to save and close files I'm getting trouble when the files are open by other machines and users. Excel opens dialogs to ask for filenames but it is running on a server there is no user to close the dialogs. So when the file is open and cannot be written to it should be skipped with no GUI asking for filenames.

Workbook book = excel.Workbooks.Open(filename);
Worksheet sheet = (Worksheet) book.Worksheets.get_Item(1);
// write stuff in cells
book.SaveAs(filename);
book.Close(false);

How can I make excel to try to save the file and then close is no matter what ?

(In my app there is no lost data, it can be written to the excel files later anyways)

file exists → overwrite
file open → don't save, just close

score 1 · Answer 1 · edited May 23 '17 at 12:05

1

Looks like this answers the question, check if the file is in use before you choose to save or skip.

Is there a way to check if a file is in use?

protected virtual bool IsFileLocked(FileInfo file)
{
    FileStream stream = null;

    try
    {
        stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None);
    }
    catch (IOException)
    {
        //the file is unavailable because it is:
        //still being written to
        //or being processed by another thread
        //or does not exist (has already been processed)
        return true;
    }
    finally
    {
        if (stream != null)
            stream.Close();
    }

    //file is not locked
    return false;
}

To check if a file exists you can use

if (File.Exists(filename)){
    // if the file exists
    book.Save();
}
else{
//if the file doesnt exist
    book.SaveAs(filename);
}

edited May 23 '17 at 12:05

Community

1
1

answered Oct 21 '13 at 11:31

Sam Leach

72
7

This helps if the file is just locked. How about the overwriting issue ? And there is still the chance that the file is opened between the checking and saving. Is there a way to lock the file for both checking and saving ? – Bitterblue Oct 21 '13 at 11:40
See the updated answer to check if the file exists and either save or saveas. if your file open check is between you opening the file and you closing it, nobody else can open/edit/lock it in that time – Sam Leach Oct 21 '13 at 11:47
Also this doesn't help while saving because I open the file with excel and it locks it already by itself. It might be useful when **opening** the excel file. – Bitterblue Oct 21 '13 at 13:27
if you apply the function in the correct place, you will know if the file is locked before you try to open it, then when you open it you lock it out so you know you can save. – Sam Leach Oct 21 '13 at 14:54

Bitterblue · Accepted Answer · 2013-10-21T13:33:11.000

All problems might be solved if I set the share options for the one file that I must update. So that multiple users can update the file simultaneously: Menu → Extra → ...

This doesn't solve the problem to 100% but better than nothing:

private static void saveAndClose(Workbook book, string filename)
{
    try
    {
        File.Delete(filename);
    }
    catch { }
    if (!File.Exists(filename))
        book.SaveAs(filename);
    book.Close(false);
}

How to save excel file without GUI notifications?

2 Answers2