Thursday, February 4, 2010

HOW TO RETURN RECORDS FROM EXCEL SHEET aND HOW TO USE FINALLY BLOCK

DataSet ds = GetExcel(path + "\\" + strFile);
string chk_reg = "";
int rowCount = ds.Tables[0].Rows.Count;
if (rowCount > 0)
{
for (int z = 0; z < 1; z++)
{
}

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)
{
return null;
}
finally
{
Dispose();
System.Diagnostics.Process[] prs = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process proces in prs)
{
if(proces.ProcessName.ToUpper()=="EXCEL")
{
proces.Refresh();
if (!proces.HasExited)
proces.Kill();
}
}
}
}

No comments:

Post a Comment