Excel column name may contain trailing spaces which would hit exception if the column don't match due to spaces.
I am finding ways to handle trailing spaces in the column name in datatable.
foreach (DataRow row in caseTable.Rows)
{
foreach (DataColumn column in caseTable.Columns)
{
if (!(string.isNullOrEmpty(column.toString())))
{
//Cannot assign value to 'column' because it is in a 'foreach iteration variable'
column = column.ToString().TrimStart().TrimEnd();
trimmed = 1;
}
}
while (trimmed == 0) ;
}
...
//errored out due to 'Excel.firstName' value not existing in DataTable due to trailing spaces
if (row[Excel.firstName].ToString().Trim() != "")
{
caseEntity.Attributes[Case.firstName] = row[Excel.firstName];
}
There are two mistakes in your code:
Additionally, you are abusing ToString and, as already stated by Michal Turczyn, you are not using built-in String methods explicitly designed to test for empty or null strings.
You can try replacing the code inside the inner loop with
var oldName = column.ColumnName;
if (!string.IsNullOrEmpty(oldName))
{
var newName = oldName.Trim();
if (newName != oldName)
{
column.ColumnName = newName;
trimmed = 1;
}
}
From this replacement code You can see that:
ColumnName
property instead of using ToString
.TrimStart
and TrimEnd
one after another (in any order) by using Trim
.string.IsNullOrEmpty
instead of checking for null andempty string.oldName
and newName
, you can track a column name as trimmed only when that's really true.More over, if you are assigning only values 0 and 1 to trimmed
variable, then you should consider declaring it as bool
and assign false or true.
Otherwise you can keep trimmed
variable as a number, but increasing its value rather than assigning always the same constant (1).
If what you are trying to change is not the column name, but is the cell value in the current row for such column, then you are missing to get (and later set) the cell value (you are evaluating and trying to change only the column name). In that case, the inner loop code become:
var oldValue = row[column] as string;
if (!string.IsNullOrEmpty(oldValue))
{
var newValue = oldValue.Trim();
if (newValue != oldValue)
{
row[column] = newValue;
trimmed = 1;
}
}