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
{
oXL = new ApplicationClass();
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);
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;
for (int j = 1; j <= jValue; j++)
{ dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
}
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