How to read data from Excel which is open and getting updated every seconds using C# ASP.NET?

Question

I am using Office.Interop.Excel to read data from Excel using C# ASP.Net & Dotnet 6. I can read the Data and everything seems to be working fine.

But I have a challenge here. The excel which I am reading data from would be updated every second. But I am seeing an error while trying to open it and update random data. The error says that the file is locked for editing.

Please have a look at the code below:

public double GetGoldPrice()
{
   string filename = @"D:\Test.xlsx";
   int row = 1;
   int column = 1;
   Application excelApplication = new Application();
   Workbook excelWorkBook = excelApplication.Workbooks.Open(filename);
   string workbookName = excelWorkBook.Name;
   int worksheetcount = excelWorkBook.Worksheets.Count;
    
   if (worksheetcount > 0)
   {
      Worksheet worksheet = (Worksheet)excelWorkBook.Worksheets[1];
      string firstworksheetname = worksheet.Name;
      var data = ((Microsoft.Office.Interop.Excel.Range) worksheet.Cells[row, column]).Value;
      excelApplication.Quit();
      return data;
    }
    else
    {
      Console.WriteLine("No worksheets available");
      excelApplication.Quit();
      return 0;
    }
}

My end goal is to get live data from Excel whenever I fire the function. The Excel would be open and can be editing any time.

Please help!

Answer 1

You said your file is xlsx so you would be better not using Interop but Open XML SDK 2.5. Then you can open the file in read only mode:

 using (SpreadsheetDocument spreadsheetDocument = 
        SpreadsheetDocument.Open(fileName, false))
    {
        // Code removed here.
    }

Check here to get familiar with Open XML SDK

Update as of December 2024: The link to "Open XML SDK topic:how to parse and read a large-spreadsheet" on microsoft learn updated