Master Master -4 years ago 215
C# Question

How to work with excel file in memory

at the moment my current process is as followed. Query database - > Save file locally -> Open Workbook using Excel Interop Dll, Make Changes To Work Book, Save As using Excel Interop Dll. The reason for save as is because I require some addition settings so the file isn't set to read only.

The issue I'm coming across is that it's saving locally twice. First time is fine, second time a prompt will appear asking if I would like to override. I'm wondering how can I remove the Save File Locally process and have it in memory to work with? If I am able to work with the file in memory, I would have the prompt on

Save As
asking me if I would like to override the previous file.


//Save File Locally
System.IO.File.WriteAllBytes(saveFileDialog.FileName, Report.FileArray);

var fileLocation = saveFileDialog.InitialDirectory + saveFileDialog.FileName;

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

// Open Workbook Using Excel Interop Dll
Workbook wb = excel.Workbooks.Open(fileLocation);

Worksheet ws1 = wb.Worksheets.get_Item("English");

//Make Changes To WorkBook
ws1.Range["E5"].Value = StartDate;
ws1.Range["G5"].Value = EndDate;

// Save AS Using Excel Interop With shared settings to remove read only access
wb.SaveAs(fileLocation, AccessMode: XlSaveAsAccessMode.xlShared);


Answer Source

You'd better disable the prompt, to what I remember this is possible but it imply a lot of umnaged code...

Try this

Microsoft.Office.Interop.MSProject.Application msProjectApp = new Microsoft.Office.Interop.MSProject.Application();
msProjectApp.DisplayAlerts = false;


Microsoft.Office.Interop.Excel.Application msProjectApp = new Microsoft.Office.Interop.Excel.Application();
    msProjectApp.Visible = true; //show the application and not need to start a process
    msProjectApp.DisplayAlerts = false;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download