Extracting Data from Excel Sheet returns Empty When File is kept Open in Excel

Question

I'm using the following code to extract data from sheets within a workbook using EPPlus. The code works fine, except when the workbook is open in Excel. The code returns no data when the Excel file is kept open by the user. How can I get over this issue?

var fileInfo = new FileInfo(@file);

using (var package = new ExcelPackage(fileInfo))
                {
                    label_snum.Text = package.Workbook.Worksheets.Count.ToString();
                    // Itterate through workbook sheets
                    foreach (var sheet in package.Workbook.Worksheets)
                    {
                        label_csheet.Text = sheet.Name;
                        // Itterate through each column until final column
                        for (int i = 1; i <= sheet.Dimension.End.Column; i++)
                        {
                            for (int j = 1; j <= sheet.Dimension.End.Row; j++)
                            {
                                if (sheet.Cells[j, i].Text.Length != 0)
                                {

                                }
                             }    
                    }  
                }

@singularhum works perfect. Thanks :).why dont you add it as an answer. — techno, May 08 '18 at 17:20

score 0 · Answer 1 · answered May 07 '18 at 21:35

0

Have you thought about trying to connect to Excel file using a connection string instead of trying to access the opened file?

ConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PathtoYourExcelFile.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Then you can use Linq/SQL queries to pull in the data.

"SELECT * FROM [sheet1$]"

answered May 07 '18 at 21:35

RCleveland

3
3

Thanks,but i dont want to redo the same stuff already done in EPPlus. – techno May 08 '18 at 16:22
No problem. I haven't looked into it but have you marked the XLSX file for collaboration? – RCleveland May 08 '18 at 21:00

score 0 · Answer 2 · answered May 08 '18 at 22:29

Refer to the answer here that I originally linked as a comment.

new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)

To further explain, a file cannot be read when passing a FileInfo to ExcelPackage if it is being used by another process.

Prior to v4.5.1 of EPPlus, ExcelPackage uses System.IO.File.ReadAllBytes to load a file. ReadAllBytes uses the FileShare.Read setting which is incorrect because Excel opens files using the FileShare.ReadWrite setting.

When you open an already opened file, you also need to use the same FileShare setting. So by using a FileStream, you can supply the correct FileShare.

From v4.5.1, ExcelPackage now does this for you now when passing a FileInfo.

Extracting Data from Excel Sheet returns Empty When File is kept Open in Excel

2 Answers2