How to read a empty cell value using EXCEL interop api in C#.net?
28,623
Add another check
if (mWSheet1.Cells[z, y + 3].Value2 != null)
or convert to string using the following code, because it would not fail if Value2 is null
orderPc.ManufactureDate = Convert.ToString(mWSheet1.Cells[z, y + 3].Value2);
Author by
Sulekha
Updated on March 16, 2021Comments
-
Sulekha about 3 years
I am getting a System.com_object error if I am trying to read the EXCEL cell which is empty. My code is :
public static List<OrderPC> getFilters(string fileCheckout) { List<OrderPC> orderPCs = new List<OrderPC>(); XLDoc sldoc = new XLDoc(); string localPath = @"C:\Temp\PCs.xlsx"; Microsoft.Office.Interop.Excel.Application oXL=null; Microsoft.Office.Interop.Excel.Workbook mWorkBook=null; Microsoft.Office.Interop.Excel.Worksheet mWSheet1=null; Microsoft.Office.Interop.Excel.Range xlRange=null; try { oXL = new Microsoft.Office.Interop.Excel.Application(); mWorkBook = oXL.Workbooks.Open(localPath); mWSheet1 = mWorkBook.Sheets[1]; xlRange = mWSheet1.UsedRange; foreach (Microsoft.Office.Interop.Excel.Hyperlink hl in xlRange.Hyperlinks) { int y = hl.Range.Column; int z = hl.Range.Row; string vFilter = mWSheet1.Cells[z, y + 1].Value2.Trim(); if (vFilter.CompareTo("Weekly") == 0) { String baseUri = "http://xxx.yyy.net?"; int followUpIndex = baseUri.Length; OrderPC orderPc = new OrderPC(); orderPc.ProductClass = hl.TextToDisplay.Trim(); orderPc.HyperLink = hl.Address.Trim().Substring(followUpIndex); orderPc.SpecType = mWSheet1.Cells[z, y - 1].Value2.Trim(); if (mWSheet1.Rows[z].Cells[y + 3] != null || mWSheet1.Rows[z].Cells[y + 3].Value2 != string.Empty) { orderPc.ManufactureDate = mWSheet1.Cells[z, y + 3].Value2.ToString(); //Here is the error** } //Console.WriteLine(orderPc.ProductClass+"----"+orderPc.HyperLink); orderPCs.Add(orderPc); } } } catch (Exception ex) { } finally { GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(xlRange); Marshal.FinalReleaseComObject(mWSheet1); mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); Marshal.FinalReleaseComObject(mWorkBook); oXL.Quit(); Marshal.FinalReleaseComObject(oXL); } return orderPCs; }
This excel file has 10 columns and I suppose I'm reading a valid cell. The error is **"
{Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot perform runtime binding on a null reference
at CallSite.Target(Closure , CallSite , Object )
at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
"** I don't have any clue since its COM. Help is much appreciated.
-
Sulekha over 11 yearsIt worked..i also tried orderPc.ManufactureDate = ""+mWSheet1.Cells[z, y + 3].Value2; and its working..thanks though..
-
Taylor Brown over 9 yearsConvert.ToString instead of .ToString() worked for me. This will actually solve this issue for me in many other places since I was unaware of this. Thanks!