Changing contents of an individual column in an excel
Posted: Wed Nov 26, 2014 9:33 am
Based on a lookup, I am trying to change the contents of an individual column in an excel.
My approach is based on using a For Each loop and running iterations against the row set.
I want all the contents of this column to get replaced by particular values present in the if loop contained in the for.
The logic I am trying to use is
1. Read the input file and generate rowset
2. forEach row in the selected column,
{
3. compare if
{
the value equals 'X', if yes replace 'X' with 'A'
then write the output to an excel
}
3. compare if
{
the value equals 'Y', if yes replace 'Y' with 'B'
then write the output to an excel, if excel exists append
}
3...
} so on and so forth till the for loop completes all elements in the column
Here's the code :
In the output excel file -- the one that I am writing, I am getting only the values from the last 'if check'. My speculation is since the input file has those values earlier up in the row in that column,
Would there be a better approach so that I am getting all the values written in the excel file from all the 'if checks' if all values are present in the input file ?
My approach is based on using a For Each loop and running iterations against the row set.
I want all the contents of this column to get replaced by particular values present in the if loop contained in the for.
The logic I am trying to use is
1. Read the input file and generate rowset
2. forEach row in the selected column,
{
3. compare if
{
the value equals 'X', if yes replace 'X' with 'A'
then write the output to an excel
}
3. compare if
{
the value equals 'Y', if yes replace 'Y' with 'B'
then write the output to an excel, if excel exists append
}
3...
} so on and so forth till the for loop completes all elements in the column
Here's the code :
Code: Select all
<forEachLoop itemsVariable="${outPut}" currentItemVariable="thisRow" currentIterationVariable="i" logLevel="debug">
<if condition="${thisRow[2] == GB}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="gbTrade" excludeRow="${thisRow[2] <> GB}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="GBP" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${gbTrade}" outputFile="resource:smb://Copy of Desktop/april_mod3.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<if condition="${thisRow[2] == MC}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="mcTrade" excludeRow="${thisRow[2] <> MC}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="MCO" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${mcTrade}" outputFile="resource:smb://Copy of Desktop/april_mod2.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<if condition="${thisRow[2] == ES}">
<modifyRowSet inputRowSetVariable="${thisRow}" outputRowSetVariable="esTrade" excludeRow="${thisRow[2] <> ES}" startAtRow="2" version="1.0" logLevel="debug">
<modifyColumn index="2" value="EUR" />
</modifyRowSet>
<writeExcel inputRowSetVariable="${esTrade}" outputFile="resource:smb://Copy of Desktop/april_mod1.xls" whenFileExists="append file" excelFormat="excel2007" includeHeadings="true" version="2.0" logLevel="debug" />
</if>
<iterateLoop condition="${ i > 120}" />
</forEachLoop>
<deleteWorkspace version="1.0" />
</module>
<variable name="GB" value="GB" />
<variable name="MC" value="MC" />
<variable name="ES" value="ES" />
<variable name="NL" value="NL" />
</project>
In the output excel file -- the one that I am writing, I am getting only the values from the last 'if check'. My speculation is since the input file has those values earlier up in the row in that column,
Would there be a better approach so that I am getting all the values written in the excel file from all the 'if checks' if all values are present in the input file ?