User-852578096 posted
Hi,
I used the above function in a class, and compiled the class in the DLLs which I am using in my project. However the Dispose command in the finally section is giving an error.
And if I work without the dispose clause, it creates a multiple instances of Mcrosoft Excel on Server. I tried to write the dispose command on the page, but it didnt worked.
Class Definition :
public class Helpers
{
public DataSet GetExcel(string fileName)
{
try
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRng;
// creat a Application object
oXL = new Application();
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName);
// 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++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
string strValue = oRng.Text.ToString();
dt.Columns.Add(strValue, System.Type.GetType("System.String"));
}
//string colString = sb.ToString().Trim();
//string[] colArray = colString.Split(':');
// get data in cell
for (int i = 2; 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[j - 1] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
return ds;
}
catch (Exception ex)
{
Utilities.LogException(ex);
return null;
}
finally
{
//dispose the objects
}
}
}
public class Helpers
{
/// <summary>
/// DataSet ds = GetExcel("c:\\abcd.xls");
/// GridView1.DataSource = ds;
/// GridView1.DataBind();
/// </summary>
public DataSet GetExcel(string fileName)
{
try
{
Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRng;
// creat a Application object
oXL = new Application();
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName);
// 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++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
string strValue = oRng.Text.ToString();
dt.Columns.Add(strValue, System.Type.GetType("System.String"));
}
//string colString = sb.ToString().Trim();
//string[] colArray = colString.Split(':');
// get data in cell
for (int i = 2; 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[j - 1] = strValue;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}
return ds;
}
catch (Exception ex)
{
Utilities.LogException(ex);
return null;
}
finally
{
//dispose the objects
}
}
}
and on the page i am writing the following code:
protected void Button1_Click(object sender, EventArgs e)
{
Helpers hlp = new Helpers();
System.Data.DataSet ds = hlp.GetExcel(@"F:\Students.xls");
GridView1.DataSource = ds;
GridView1.DataBind();
Dispose();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
Helpers hlp = new Helpers();
System.Data.DataSet ds = hlp.GetExcel(@"F:\Students.xls");
GridView1.DataSource = ds;
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
Dispose();
}
