asked on
C#: How to deal with empty cells while reading excel spreadsheet
Hi I'm trying to read an excel spreadsheet I thought I cracked it until hitting empty cells
Ho do I stop this from erroring?
Error
Ho do I stop this from erroring?
Error
Error 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)
at CallSite.Target(Closure , CallSite , Object )
at .Program.Main(String[] args) in \Program.cs:line 109
string WorkBook = @"C:\Temp\MyFile.xls";
if (File.Exists(WorkBook))
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(WorkBook);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[3];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int PrefCount = 1;
//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 3; i <= rowCount; i++)
{
//. ...
string StrDescription = "";
try
{
if (xlRange.Cells[i, 2].Value2.ToString() != null || xlRange.Cells !=null)
{
StrDescription = xlRange.Cells[i, 2].Value2.ToString();
StrDescription = Regex.Replace(StrDescription, @"\r\n?|\n", " ");
}
else
{
Console.WriteLine("Description is Null");
}
Console.WriteLine("1 Description: {0}", StrDescription);
}
catch (Exception e)
{
Console.WriteLine("Error {0}", e);
continue;
}
.NET ProgrammingC#
Last Comment
AndyAinscow
ASKER
Hi
Nope It still give the same Error
as dose
if (xlRange.Cells[i, 2].Value2.ToString() != "")
Always the same cell(s) There could by just white space in it
Nope It still give the same Error
as dose
if (xlRange.Cells[i, 2].Value2.ToString() != "")
Always the same cell(s) There could by just white space in it
It is that line of code that errors ?
You shot of the error said line 109
You shot of the error said line 109
if ( ! xlRange.Cells[i, 2].Value2.ToString().IsNul
I think that is how it is spelt - also not the ! to negate
I think that is how it is spelt - also not the ! to negate
ASKER
Without the "try" block that the line that errors
I'm using the latest version of .NET framework but get
Microsoft.CSharp.RuntimeBi
HResult=0x80131500
Message='string' does not contain a definition for 'IsNullOrWhitespace'
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>
I'm using the latest version of .NET framework but get
Microsoft.CSharp.RuntimeBi
HResult=0x80131500
Message='string' does not contain a definition for 'IsNullOrWhitespace'
Source=<Cannot evaluate the exception source>
StackTrace:
<Cannot evaluate the exception stack trace>
Ahhh, different .net functions. (Framework vs non Framework versions of .net)
Does this compile
if (xlRange.Cells[i, 2].Value2.ToString().Trim(
Please confirm the run time exception is thrown on this line and not on another line of code
Does this compile
if (xlRange.Cells[i, 2].Value2.ToString().Trim(
Please confirm the run time exception is thrown on this line and not on another line of code
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
null (Nothing in VB) can not be convereting to string using .ToString() method. Check for null cell.Value and if not nul - use .ToString. I'm using folowing function (VB)
Public Function CellValue(cell As Excel.Range, Optional skipMerged As Boolean = False) As String
If skipMerged AndAlso cell.MergeCells Then Return ""
Dim v = cell.Value
If v Is Nothing Then Return ""
Return v.ToString
End Function
ASKER
Please confirm the run time exception is thrown on this line and not on another line of code
Yes it was that line
Kyle's suggestion worked not totally sure what the difference is
Thank you both and Stay safe
Kyles worked because I made a silly mistake. It is a static function of the string class which takes a variable as parameter, not a function the string variable uses.
if (xlRange.Cells[i, 2].Value2.ToString() != null || xlRange.Cells !=null)
does this help?
if (xlRange.Cells !=null && xlRange.Cells[i, 2].Value2.ToString() != null)
note changing the order and replacing the OR with AND