CODEWITHSUNDEEP
c#oledbms-jet-ace
IntelligentCancer
IntelligentCancer

Reputation: 149

Excel upload not working on windows Server 2012

I have developed an application where the user have to upload a winzip archive file. The code then extracts an Excel file (in .xls format, 97-2003 workbook) from that archive and uploads the data within that Excel file into my SQL database table. I'm using ZipFile.ExtractToDirectory(zipPath, extractPath) to extract the file.

Below is the code I'm using for the excel upload.

using (OleDbConnection connExcel = new OleDbConnection(Excelconstring))
{
    using (OleDbCommand cmdExcel = new OleDbCommand())
    {
        using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
        {
            cmdExcel.Connection = connExcel;

            connExcel.Open();

            DataTable dtExcelSchema;
            string sheetName = "Chat Summary$";  
            //DateTime.Today.ToString("M.d.y$");

            // Read Data from First Sheet.
            cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
            odaExcel.SelectCommand = cmdExcel;
            odaExcel.Fill(dt);
            connExcel.Close();
        }
    }
}

The problem is this works absolutely fine on my local system 64-bit Windows 7 with Microsoft Office 2010. I'm using Microsoft.Jet.OLEDB.4.0 on my local system. The problem is when I deploy the same code on my server (Windows Server 2012 with Microsoft Office 2013), this doesn't work. I have installed Microsoft Access 2010 redistributable on the server and change the provider to Microsoft.ACE.OLEDB.12.0 but still it doesn't work. The error I get after changing the provider to ACE is

External table is not in the expected format

Also when I extract the file myself, open it once and then archive it again - it works fine. I have tried many solutions from different forums but none of them seems to work. I have changed the debug mode from "anycpu" to "x86" and "x64" but still it doesn't work.

The steps I have already tried are installing the Microsoft Access 2010 redistributable, changing the debug mode from "anycpu" to "x86", changing the connection string from Jet.OLEDB.4.0 to ACE.OLEDB.12.0.

Upvotes: 2

Views: 1561

Answers (1)

squillman
squillman

Reputation: 13641

Excel 2010 is still a 32-bit application. Therefore, try setting Enable 32-bit applications to True on your IIS App Pool.

enter image description here

Upvotes: 3

Related Questions