#########################################################################################################################
### Begin of script of XML conversion ###################################################################################
#########################################################################################################################

Voting_Info="input.xml"

## (1) - Get cases id's between double quotes e.g. "<case id="No. 3 Div. 870">" and store them in varible array ###
oldIFS=$IFS
IFS=$'\n'
Cases=($(awk -F "[\"]" '/id=/{print $2}' "$Voting_Info"))
IFS=$oldIFS

## (2) - Count "cases" blocks
CasesNumber=($(awk -F "[\"]" '/id=/{print $2}' "$Voting_Info" | wc -l))
let "CasesNumber=$CasesNumber+1"  # Add "1" to set the value in "ExpandedColumnCount"

## (3) - Get unique names between ">" and "</j>", e.g. ">Mary</j>" and store them in varible array ###
UniqNames=($(awk -F "[><]" '/v=/{a[$3];} END{for (i in a) print i;}' "$Voting_Info" | sort))

## (4) Print first lines of output
echo "  <Table ss:ExpandedColumnCount=\""$CasesNumber"\" ss:ExpandedRowCount=\"6\" x:FullColumns=\"1\""
echo "   x:FullRows=\"1\" ss:DefaultColumnWidth=\"60\" ss:DefaultRowHeight=\"15\">"
echo "   <Column ss:Width=\"30.75\"/>"
echo "   <Column ss:Width=\"67.5\" ss:Span=\"1\"/>"

## (5) - Print first block, that is the "Cases" names block.
    echo "   <Row ss:AutoFitHeight=\"0\">"
    echo "    <Cell ss:Index=\"2\"><Data ss:Type=\"String\">""${Cases[0]}""</Data></Cell>"
#for i in "${Cases[@]}"
for ((i=1;i<${#Cases[*]};i++))
   do
    echo "    <Cell><Data ss:Type=\"String\">""${Cases[$i]}""</Data></Cell>"
done

## (6) - Loop to get values of each name within all cases blocks
for j in "${UniqNames[@]}"
   do
    echo "   </Row>"
    echo "    <Row ss:AutoFitHeight=\"0\">"
    echo "    <Cell><Data ss:Type=\"String\">"$j"</Data></Cell>"
    awk -v Z=$j -F"[\"><]+" '/case id/{v=0}/\/case/{print "    <Cell><Data ss:Type=\"Number\">" v "</Data></Cell>"}$0 ~ Z{v=$3}' "$Voting_Info"
done
## (7) - Print last lines to complete output
echo "   </Row>"
echo "  </Table>"