CODEWITHSUNDEEP
c#openxml-sdk
Satish
Satish

Reputation: 473

Multiple users writing concurrently to shared Excel file using OpenXML

I have a requirement to read and write to a shared excel(xlsx) file using open xml sdk in C#. I have updated the shared mode setting using the answer to this question on stack overflow and the setting is updated in the created excel file. I have wrote a small program to insert data to this generated Excel file based on this.

I have tested this with 3 different users trying to write data at the same time over LAN. I initially got an exception during open at the below statement.

using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelStream, true))

At this point of time only one user can write to the shared excel file even though the shareworkbook settings were enabled.

Later I changed the above statement to use Stream as below

 using (FileStream excelStream = new FileStream(filePath,FileMode.OpenOrCreate,FileAccess.ReadWrite,FileShare.ReadWrite))
                {
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelStream, true))

After this change the frequency of the initial exception is reduced but still comes sometimes. But, when this succeeds multiple users are able to write to the excel file.

I have observed two important behavior when multiple users write to a shared excel file.

  1. Even though the Users write the data concurrently, all the rows written by a user in one session are arranged in a sequence.
  2. When multiple users try to write to the same shared excel file, the open xml seem to have writing the data user after user in a sequence. This I have verified by inserting a timestamp while writing into the excel file for each user. Writing for user2 starts after end of user1.

Can anyone please guide me in finding the right approach to eliminate the exception during open and also do a concurrent write to the excel file using OpenXML Sdk.

Thanks in advance.

Upvotes: 0

Views: 2075

Answers (1)

fourwhey
fourwhey

Reputation: 530

You probably should consider using a database if you need multi-user access and expect it to work well. Even Access would be better than Excel. If you need the output to be in Excel, a report that can export to Excel could function for that.

If an exception is occurring while opening the file, maybe try handling that exception, and adding retry logic? You can use something like, https://github.com/App-vNext/Polly to make doing that easy.

The user sequencing is probably just how it's handling writes. I wouldn't expect this to be something you can work around since it's Excel, not a database.

Upvotes: 1

Related Questions