Hi,
I have an excelsheet wich containes >3000 records and I need to read that excelsheet each cell by cell. it is giving below error.
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).
Could you please suggest and also give the C# code to read excel cell by cell.. ASAP
Thanks
CODE:
protected void btnImport_Click(object sender, EventArgs e)
{
int CreatedBy = Convert.ToInt32(Session["UsID"].ToString());
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string errordesc = null;
lblError.Text = "";
try
{
//Loc & DEPt & PAyperiod
DateTime payFDate1;
string PayFrom1, PayTo1;
payFDate1 = Convert.ToDateTime(cmbpayperiod.SelectedValue.ToString().Trim());
PayFrom1 = payFDate1.ToShortDateString().ToString().Trim();
PayTo1 = payFDate1.AddDays(13).ToShortDateString().Trim();
string LocID = ddlLocation.SelectedValue;
string DIVID = ddlDivision.SelectedValue;
fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
if ((fyle.PostedFile != null) && (fyle.PostedFile.ContentLength > 0))
{
string fn = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
string[] ext = fn.Split(new char[] { '.' });
int count = ext.Length;
string strExt = ext[count - 1].ToString();
if (strExt.Trim() == "xls" || strExt.Trim() == "xlsx")
{
fn2 = ext[0].ToString().Trim() + "-" + String.Format("{0:ddMMyyyyHHmmss}", DateTime.Now) + "." + ext[count - 1].ToString().Trim();
fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
//File Save
string SaveLocation = Server.MapPath("Incentives") + "\\" + fn2;
path = SaveLocation;
fyle.PostedFile.SaveAs(SaveLocation);
//Bulk Import
DataTable dt = new DataTable();
//DataRow row;
dt = GetDataTableFromExcel(path, ext[count - 1].ToString());
//Read each cell by cell
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
int rCnt = 0;
//int cCnt = 0;
string strPeriod = "";
string StrName = "";
try
{
//opn excel
// CallWithTimeout(FiveSecondMethod, 6000);
xlApp = new Excel.ApplicationClass();
//xlWorkBook = xlApp.Workbooks.Open(SaveLocation, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkBook = xlApp.Workbooks.Open(SaveLocation, 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);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
range = xlWorkSheet.UsedRange;
for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
if (rCnt == 7) { strPeriod = (string)(range.Cells[7, 1] as Excel.Range).Text; } //Period
if (rCnt > 7)
{
//Name
string strVAL = (string)(range.Cells[rCnt, 2] as Excel.Range).Text;
if (strVAL.Trim() == "") { strVAL = StrName; } else { StrName = strVAL; }
dt.Rows[rCnt - 2][1] = strVAL;
//Date
string strDate = (string)(range.Cells[rCnt, 4] as Excel.Range).Text;
dt.Rows[rCnt - 2][3] = strDate;
}
}
//Close excel
xlWorkBook.Close(true, null, null);
xlApp.Quit();
//clear memory
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
//Remove last and first
dt.Columns.RemoveAt(17);
dt.Columns.RemoveAt(0);