Reading Excel File with c# Not Working In IIS

Question

0.00/5 (No votes)

See more:

I want to import data from excel to data set.

I done it by using following code it works when i am runing in localhost.

But It is showing error while running in IIS or any Hosting.

Error:

System.InvalidOperationException: GetOleDbSchemaTable requires an open and available Connection. The connection's current state is closed. at System.Data.OleDb.OleDbConnection.CheckStateOpen(String method) at System.Data.OleDb.OleDbConnection.GetOleDbSchemaTable(Guid schema, Object[] restrictions)

My code as follows

public DataSet GetExcel(string fileName)
    {
        Application oXL;
        Workbook oWB;
        Worksheet oSheet;
        Range oRng;
        try
        {    
            //  creat a Application object    
            oXL = new ApplicationClass();    
            //   get   WorkBook  object    
            oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);    
            //   get   WorkSheet object   
            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];  
            System.Data.DataTable dt = new System.Data.DataTable("dtExcel");  
            DataSet ds = new DataSet();   
            ds.Tables.Add(dt);  
            DataRow dr;  
            StringBuilder sb = new StringBuilder();   
            int jValue = oSheet.UsedRange.Cells.Columns.Count;   
            int iValue = oSheet.UsedRange.Cells.Rows.Count;   
            //  get data columns   
            for (int j = 1; j <= jValue; j++)  
            {        dt.Columns.Add("column" + j, System.Type.GetType("System.String"));   
            }   
            //  get data in cell  
            for (int i = 1; i <= iValue; i++)   
            {        dr = ds.Tables["dtExcel"].NewRow();        
                for (int j = 1; j <= jValue; j++)   
                {          
                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];  
                    string strValue = oRng.Text.ToString();         
                    dr["column" + j] = strValue;    
                }      
                ds.Tables["dtExcel"].Rows.Add(dr); 
            }  
            return ds;
        }
        catch (Exception ex)
        {
            lbl_Result.Text = lbl_Result.Text + ex.ToString(); ;
                return null;
            }
        finally
        {   
            Dispose();
        }
    }

Please Tell me Where I am Doing Worng?
Thanks In advance

Posted 27-May-10 19:19pm

murali0808

Add a Solution

Comments

murali0808 29-May-10 1:35am

I am Getting the Following error while Running in webserver or IIS

It is Fine on My LocalHost

System.Runtime.InteropServices.COMException (0x800A03EC):

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GregWyatt · Answer 1 · 2010-05-28T06:26:00

The built in Excel Object that allows you to access and manipulate excel files requires that excel be installed on the machine you are using it on. If your server doesn't have excel installed on it then you won't be able to use the Excel object. Also, Microsoft doesn't recommend installing excel on the server for this use since it is single threaded and has other issues associated with it. I personally have had a lot of trouble trying to make a program like this work on the server.(It is possible though) The company I work for ended up buying an excel api that doesn't require excel to be installed on the machine.

If you would like to discuss this further I'd be more than glad to.

Abdulkader_Rai · Answer 2 · 2013-04-13T22:17:00

Solution 3

check your Application Pool -> Advanced Setting -> enable 32 Applications -> True

Posted 13-Apr-13 22:17pm

Abdulkader_Rai

Comments

umeshfaq 9-May-13 16:21pm

Thanks a lot ,It is work for me.

Member 14643468 3-Nov-19 23:50pm

Thank you so much, it work for me too.

sushils943 18-Nov-19 14:50pm

thanks brother, it saved my life.God bless :)

Narsimlu Keshagouni · Answer 3 · 2010-05-28T01:53:00

Solution 1

Murali,

You must be passing a wrong filename, check how you are sending the filename, is it a physical path or virtual path. Post the code from where you are calling this method.

Posted 28-May-10 1:53am

Narsimlu Keshagouni

Comments

Manjunath Nayak 12-Aug-13 3:29am

excel(office 2000) is not working in IIS 8..
protected void Expgo_Click(object sender, EventArgs e)
{
/*************************************
* On export click, select date range
* Generate weekley TimeSheet Report
************************************/

QueryRet Value = new QueryRet();
String StartCalDate;
String EndCalDate;
StartCalDate = ExpCalStart.SelectedDate.ToString();
EndCalDate = ExpCalEnd.SelectedDate.ToString();

if (StartCalDate == EndCalDate)
{
Weekview.SetActiveView(Blank);
lblerr.Text = "Please Select Range of Dates.";
lblerr.Visible = true;
}
else
{
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
ExcelWork = (Excel._Workbook)(ExcelApp.Workbooks.Add(Missing.Value));
string strCurrentDir = Server.MapPath("~/TimeSheets/");
RemoveFiles(strCurrentDir);//removes all .xls files in directory
DateTime Daylist;
String Query;

/********************************************************
* Select all WeekStartDates in the given range of dates
********************************************************/
Query = "SELECT DISTINCT WeekStartDate FROM TimeSheetBreakDown";
Query += " Where Date >= '" + StartCalDate + "' AND Date<='" + EndCalDate + "'";
ArrayList DistinctStart = Value.ExecuteQry(Query);
if (ExcelWork.Worksheets.Count <= DistinctStart.Count)
{
ExcelWork.Worksheets.Add(Missing.Value, Missing.Value, DistinctStart.Count - ExcelWork.Worksheets.Count + 1, Missing.Value);
}
for (int s = 0; s < DistinctStart.Count; s++)
{
/******************************************************
* For each of the week which come under date selected
* Generate One Excel Work Sheet
******************************************************/
try
{
Query = "SELECT distinct Date FROM TimeSheetBreakDown WHERE (WeekStartDate = '" + DistinctStart[s] + "' AND Day='1') order by date";
Daylist = DateTime.Parse(Value.SelectQry(Query));
/******************************************************************
* From the Distinct StartDate Get (Date of) Monday of that week
* From Monday Calculate other Days From TimeSpan
* ****************************************************************/
mon = Daylist;
tue = Daylist + new TimeSpan(1, 0, 0, 0);
wed = Daylist + new TimeSpan(2, 0, 0, 0);
thu = Daylist + new TimeSpan(3, 0, 0, 0);
fri = Daylist + new TimeSpan(4, 0, 0, 0);
sat = Daylist + new TimeSpan(5, 0, 0, 0);
sun = Daylist + new TimeSpan(6, 0, 0, 0);
}
catch (Exception X)
{
lblerr.Text = X.Message + " Looks like you are Exporting Blank TimeSheet";
lblerr.Visible = true;
}
finally
{
GC.Collect();
/**************************************************************
* Generates one Excel WorkSheet (Gives TimeSheet of that Week)
**************************************************************/
GenerateExcel(s + 1);
}
}
DistinctStartCount = DistinctStart.Count + 1;
SummarySheet();
ExcelApp.Visible = true;
ExcelApp.UserControl = true;
String FileName = "" + User.Identity.Name + ".xlsx";
ExcelWork.SaveC

murali0808 28-May-10 13:10pm

protected void btn_Uplaod_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(file_UploadXL.PostedFile.FileName))
{
string strFilePath;
// strFilePath ="C:\Documents and Settings\jmuralimohan\Desktop\DataSample2.xls";
strFilePath=file_UploadXL.PostedFile.FileName;
lbl_Result.Text = strFilePath;
DataSet ds = new DataSet();
try
{
ds = GetExcel(strFilePath);
grid_UpLogSheets.DataSource = ds.Tables[0];
grid_UpLogSheets.DataBind();
}
catch(Exception EX)
{
lbl_Result.Text = lbl_Result.Text + EX.ToString();
}
}
}

Reading Excel File with c# Not Working In IIS

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us