Problem saving excel file after inserting data

Question

Hi,

I want to write data to an existing excel file ( I do it easily ) But I can not save the changes on the excel file ( actually I see the changes on the excel file, but it seems opened and after all it occurs some problems such as "the file is already opened with same name and so on ... )

Excel.Application app= new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbook appbook= app.Workbooks.Open(appxls, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, Missing.Value, Missing.Value);
        Excel.Sheets pages= appbook.Worksheets;
        Excel.Worksheet page= (Excel.Worksheet)pages.get_Item(1);

//... i change some values on the excel file and want to save them : // appxls is a string holding the path

 appbook.SaveAs(appxls, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,false, Type.Missing, Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    appbook.Close(true, Missing.Value, Missing.Value);
    app.Quit();

Where is the problem, how can I solve it using Microsoft.interop.

Answer 1

You are opening the workbook using the file name named 'appxls' as read-only. (The ReadOnly parameter of the Workbooks.Open method is the third parameter, and you are passing in true.)

You are later using the Workbook.SaveAs method, but are attempting to save the file using the same exact file name held by your 'appxls' variable that you used to open the workbook. This is an attempt to overwrite the read-only file that you have open, and, therefore, is prevented.

I can see two possible solutions:

1. Pass in false for the ReadOnly parameter of the Workbooks.Open method so that you can save it later using Workbook.Save method. In your case, you could save it by calling appbook.Save().

2. Continue to open the file as read-only, but when you later use the Workbook.SaveAs method, you should save it under a different name.

Hope this helps!

Answer 2

I've tried to use Oledb and was succesful with creating any empty excel file with the code:

    OleDbConnection connection= new OleDbConnection(connectionstring);
    string addSheet= "CREATE TABLE Mus(id nvarchar(255), name nvarchar(5))";
    connection.Open();
    OleDbCommand sqlcommand = new OleDbCommand(addSheet,connection);
    sqlcommand.ExecuteNonQuery();
    connection.Close();

where connectionstring is :

string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myexcel.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

So I have another proble with adding with inserting and the mistake is :

"number of query values and destination fields are not the same"

I wrote my command carefully, and I can not find the mistake :( How can I solve it?

Thanks !

(it's important I'm working on aspx, the code above works at desktop applications, but for aspx save location should be different like responce.outstream as I have understood from my researches)