I am having some very strange issues with OLE DB connecting to Excel workbooks.
Our system has a large Excel macro enabled template (we have Excel 2010 and Excel 2016). Occasionally a user will add pictures, charts, tabs, etc that give the error External table is not in the expected format when attempting to read a hidden tab in the workbook that the users do not access.
Normally, we have the user download a new template and redo the work without adding pictures.
Recently, there was many occurrences of this behavior and I attempted to investigate on my development computer. I found that I could not open a connection to the "corrupted" file via the website during the file upload process where the file is read into a byte stream and a temporary .xlsm file is created and opened using an OleDbConnection and data is read from a hidden tab.
Nothing I did to the file would open it to be opened via the code hosted in IIS. This includes the following:
- Remove all pictures
- Remove all tabs but the hidden tab
- Unhide the tab we read from
- save the workbook as an xlsx to remove macros
- save the workbook as a 2003 - 2007 workbook and then save back to either an xlsx or xlsm
The website is running under .NET Framework 4.0 and is running under IIS.
For my investigation, I wrote the following code and hosted it on the same development computer in a page in a test web application hosted in IIS Express under .NET Framework 4.0 and it successfully opened and read the data from the original "corrupted" file.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class ReadExcelTabToDataSet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string szSheetName = @"C:\Temp\Test.xlsm";
string szConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=\"Excel 12.0;HDR=YES\";Data Source=" + szSheetName;
string szQuery = "Select 'Configuration$' as Sheet, * From [Configuration$B1:S2]";
string szExcelTableName = "ValidateFlag";
DataSet ds;
using (OleDbConnection conn = new OleDbConnection(szConnection))
{
using (OleDbDataAdapter da = new OleDbDataAdapter(szQuery, conn))
{
conn.Open();
ds = new DataSet();
da.Fill(ds, szExcelTableName);
}
}
}
}
This raises lots of warning bells and has stumped me. This test seems to rule out everything but how OleDb performs when hosted in IIS. When this page is copied to our site, it fails on the conn.Open().
Does anyone understand why this is happening and how to fix it? I do not want to penalize our users for strange Microsoft issues like this.
Thanks,
EDIT 1
If the file is marked "read-only" and it is in a directory that has full access, then the file can be opened and the data may be read successfully.
This still presents an issue since we are opening the file, checking information and finally, making changes on the tab.