Read Excel In IIS7 Problem

Question

User1268492552 posted

Hi,

I want to read an Excel file and store it to a dataset. It work fine in asp.net development server and also IIS6, but when I place it in IIS7. I get an error(System.IndexOutOfRangeException: Cannot find table 0.).

Please guide what to do on this, thanks.

Below is my code to read excel to dataset:

if (FileUpload.HasFile)
{
        file = FileUpload.PostedFile.FileName;

        if (Path.GetExtension(file) == ".xlsx") // Excel 2007
        {
               strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0;";

               myCommand = new OleDbDataAdapter("SELECT * FROM [KWSP$]", strConn);
               myCommand.Fill(ds, "ExcelInfo");

               GridViewEPRSocso.DataSource = ds.Tables["ExcelInfo"].DefaultView;
               GridViewEPRSocso.DataBind();

         }

}

Answer 1

User1446927574 posted

Why dont you try to change connectiojn string according to following article:

http://stackoverflow.com/questions/3151551/asp-net-oledb-code-breaks-when-deployed-on-iis7

I hope that helps.

Answer 2

User1268492552 posted

Hi nobdy,

Thankyou for your reply, but after I changed the connection string with the one in article, I got this error: 

One or more errors occurred during processing of command. IErrorInfo.GetDescription failed with E_FAIL(0x80004005).

Answer 3

User-1500873099 posted

Try for following modificaton:

In ur second last line write Tables[0] in place of Tables["ExcelInfo"]

Query- What is the [KWSP$] . I think its ur sheet name. if yes, then can we hard code it?  I

t may be different accournding to excel file provided.

Answer 4

User1268492552 posted

Hi Rajani,

Thankyou for your reply. I got the same error message too (Cannot find table 0) as well after I changed it yo Tables[0].

Yup, you are right. [KWSP$] is the sheet name. I not familiar working with Excel. Beside hard code it, any better way can I can replace it?

Besides that, I don't think any mistake on my code as I said my code work fine in .net development server and IIS6. Only not working in IIS7.

Might be IIS7 setting or authencation problem but I cannot figure out. 

Anyone can help me? Thanks again

Answer 5

User-1694870838 posted

Hi,

Maybe it does not have privilege to read content from temp folder. Please try either of these options:

1. Copy the file to another folder within the web site before reading it.
2. Change the application pool in IIS.  http://learn.iis.net/page.aspx/624/application-pool-identities/

 Please try one of them in your local IIS.

Best Regards,

Damon