Sunday, September 25, 2011

how to read excel sheet cells using c#

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnupload_Click(object sender, EventArgs e)
{


Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
Excel.Range range ;

string str=string.Empty;
int rCnt = 0;
int cCnt = 0;

HttpPostedFile file = upload.PostedFile;
string fileExt = Path.GetExtension(file.FileName).ToLower();
string fileName = Path.GetFileName(file.FileName);
string filepath = Server.MapPath("./importedfiles/") + fileName;

if (fileExt == ".xls" || fileExt == ".xlsx")
{
if (File.Exists(filepath))
File.Delete(filepath);

file.SaveAs(Server.MapPath("./importedfiles/") + fileName);
//MessageBox.Show(" " + d + " Successfully Uploaded");
}

xlApp = new Excel.ApplicationClass();

xlWorkBook = xlApp.Workbooks.Open("" + filepath + "", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

range = xlWorkSheet.UsedRange;

for (rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{

if ((range.Cells[rCnt, cCnt] as Excel.Range).Value == null)
{
if (cCnt == 1)
{
str +=" ";
}
else
{
str +=","+" ";
}

}
else
{
if (cCnt == 1)
{
str += (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value.ToString();
}
else
{
str += "," + (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value.ToString();
}

}


}
}

xlWorkBook.Close(true, null, null);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
// MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();

}
}

}

No comments:

Post a Comment