Writing to Excel file when it's opened by another process

Question

I'm wondering if it is possible to write to an Excel file using C#/EPPlus while I have the file open. I continue to get exceptions while trying to write using my program and I can't find anything online.

Here is the code I have to append to an existing worksheet which works fine when the spreadsheet isn't opened

    public static void AppendExistingMailingWorkbook(string workSheet, string filePath, IList<MailingReportItem> reportData)
    {
        //create a fileinfo object of an excel file on the disk
        FileInfo file = new FileInfo(filePath);
        Object thisLock = new Object();

        lock (thisLock)
        {
            //create a new Excel package from the file
            using (ExcelPackage excelPackage = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[workSheet];

                var rowToAppend = worksheet.Dimension.End.Row + 1;
                for (int i = 0; i < reportData.Count; i++, rowToAppend++)
                {
                    worksheet.Cells[rowToAppend, 1].Value = reportData[i].BatchDate.Date.ToString("MM/dd/yyyy");
                    worksheet.Cells[rowToAppend, 2].Value = reportData[i].BatchId;
                    worksheet.Cells[rowToAppend, 3].Value = reportData[i].FileName;
                    worksheet.Cells[rowToAppend, 4].Value = reportData[i].PageCount;
                    worksheet.Cells[rowToAppend, 5].Value = reportData[i].MailDate;
                }

                //save the changes
                excelPackage.Save();
            }
        }
    }

I suspect there's no way around a file lock, except by stopping the process that holds the file lock. — Peter B
– Peter B, Commented Sep 5, 2018 at 12:58
What's the reason for the file being open? I don't think there is a way around writing to a locked file. — Jimenemex
– Jimenemex, Commented Sep 5, 2018 at 13:01
I can't guarantee every time I want to write, some user isn't using the document. I suppose the next best thing I can do is make a hidden copy of the spreadsheet and every time I update it, copy it to a path where the users can read it. I can't think of anything else that would work. But even then, I'm not sure a copy and replace would work if the file is open in Excel either. — Roka
– Roka, Commented Sep 5, 2018 at 13:07

Ian · Accepted Answer · 2018-09-05 13:28:59Z

2

In Excel, set the workbook to be shared. From the office help:

Open workbook in Excel
Click Review > Share Workbook
On the Editing tab, select the Allow changes by more than one user ... check box.
On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK.
If this is a new workbook, type a name in the File name box. Or, if this is an existing workbook, click OK to save the workbook.
If the workbook contains links to other workbooks or documents, verify the links and update any links that are broken.
Click File > Save.
When you're done, - Shared will appear at the top of the Excel window, next to the filename.

The file will then be opened non-exclusively, allowing others to edit it while Excel has it open.

answered Sep 5, 2018 at 13:28

Ian

1,2511 gold badge20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing to Excel file when it's opened by another process

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related