none
IIS and OLE DB External table is not in the expected format RRS feed

  • Question

  • 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.

    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.

    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,


    • Moved by Chenchen Li Monday, May 29, 2017 2:30 AM OffTopic
    Tuesday, May 16, 2017 1:34 PM

All replies

  • Hello Lee,

    Sorry for the delay.

    This forum is for development issues when using Excel Object Model, so your issue is out of scope.

    According to your description, the code works when hosted in IIS Express and fails when hosted in IIS, i would suggest you post on IIS forum to troubleshoot the issue:

    https://forums.iis.net/

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 26, 2017 2:51 AM