Getting Excel Data with Merged Cell.
Hello All,
I'm trying to get data from excel with multi marge cell. I developed codes as below.
string filePath = @"D:\SGSO.xlsx";
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = true;
//open the workbook
Excel.Workbook workbook = excelApp.Workbooks.Open(filePath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
//select the first sheet
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
//find the used range in worksheet
Excel.Range excelRange = worksheet.UsedRange;
//get an object array of all of the cells in the worksheet (their values)
object[,] valueArray = (object[,])excelRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);
string item = string.Empty;
string poItem = string.Empty;
string boxNo = string.Empty;
string qry = string.Empty;
string uom = string.Empty;
string keurpi = string.Empty;
string description = string.Empty;
string sn = string.Empty;
string unitprice = string.Empty;
string totalprice = string.Empty;
string margecell = string.Empty;
string totalamount = string.Empty;
int detailInital = worksheet.UsedRange.Rows.Count;
for (int row = 1; row <= worksheet.UsedRange.Rows.Count; ++row)
{
/* For Item Detail Data */
if(valueArray[row,2] != null)
{
if(valueArray[row,2].ToString().ToUpper() == "ITEM")
{
//detailInital = row + 1;
detailInital = row;
}
if(row>detailInital && valueArray[row,2] != null)
{
excelRange =(Excel.Range) worksheet.Cells[row, 2];
if(!(bool)excelRange.MergeCells)
{
item = valueArray[row, 2].ToString();
poItem = valueArray[row, 3].ToString();
boxNo = valueArray[row, 4].ToString();
qry = valueArray[row, 5].ToString();
uom = valueArray[row, 6].ToString();
keurpi = valueArray[row, 7].ToString();
description = valueArray[row, 8].ToString();
sn = valueArray[row, 15].ToString();
unitprice = valueArray[row, 16].ToString();
totalprice = valueArray[row, 17].ToString();
}
else
{
margecell = valueArray[row, 2].ToString();
excelRange =(Excel.Range) worksheet.Cells[row, 17];
if(!(bool)excelRange.MergeCells)
{
totalamount = valueArray[row, 17].ToString();
row = worksheet.UsedRange.Rows.Count + 1;
}
}
}
}
}
//clean up stuffs
workbook.Close(false, Type.Missing, Type.Missing);
// Marshal.ReleaseComObject(workbook);
excelApp.Quit();
// Marshal.FinalReleaseComObject(excelApp);
These codes are ok in Visual Studio. But, In aras, I got the following 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)).
So,I tried configuration as Instruction as below link
waheedrous.wordpress.com/.../
But , Still ERROR!!! Please help.
Thanks.
Thiha Swe
Myanmar
