CODEWITHSUNDEEP
excelapache-poijxlsjexl
zitot
zitot

Reputation: 31

jxls/jexl/apache POI excel template: insert an extra total row for rows of same id

I'm exporting this from ReportServer, it goes SQL->Excel Template, and I use jxls to style and format. And JXLS relies on JEXL scripting syntax, which I am not familiar with.

Now, I have rows like below, and I want to add a total row after each name/id, totaling the number of credits a person took across all terms. I'm totally perplexed on what to do.

id name term credits
1 taz 2010 7
1 taz 2011 6
1 taz 2012 11
2 bob 2022 30
2 bob 2022 26

My goal is this:

id name term credits
1 taz 2010 7
- 2011 6
- 2012 11
- taz Total 24
2 bob 2022 30
- - 2022 26
- bob Total 56 

<table style="width: 50%; border-collapse: collapse; margin: 20px 0; font-size: 18px; text-align: left;">
    <tr>
        <th style="border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;">id</th>
        <th style="border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;">name</th>
        <th style="border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;">term</th>
        <th style="border: 1px solid #ddd; padding: 8px; background-color: #f2f2f2;">credits</th>
    </tr>
    <tr>
        <td style="border: 1px solid #ddd; padding: 8px;">1</td>
        <td style="border: 1px solid #ddd; padding: 8px;">taz</td>
        <td style="border: 1px solid #ddd; padding: 8px;">2010</td>
        <td style="border: 1px solid #ddd; padding: 8px;">7</td>
    </tr>
    <tr>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">2011</td>
        <td style="border: 1px solid #ddd; padding: 8px;">6</td>
    </tr>
    <tr>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">2012</td>
        <td style="border: 1px solid #ddd; padding: 8px;">11</td>
    </tr>
    <tr style="background-color: #ffff99;">
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">taz Total</td>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">24</td>
    </tr>
    <tr>
        <td style="border: 1px solid #ddd; padding: 8px;">2</td>
        <td style="border: 1px solid #ddd; padding: 8px;">bob</td>
        <td style="border: 1px solid #ddd; padding: 8px;">2022</td>
        <td style="border: 1px solid #ddd; padding: 8px;">30</td>
    </tr>
    <tr>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">2022</td>
        <td style="border: 1px solid #ddd; padding: 8px;">26</td>
    </tr>
    <tr style="background-color: #ffff99;">
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">bob Total</td>
        <td style="border: 1px solid #ddd; padding: 8px;"></td>
        <td style="border: 1px solid #ddd; padding: 8px;">56</td>
    </tr>
</table>

I am using the JX comment markup inside of excel comments and cells. My code right now is about as simple as it gets.

On my 1st sheet that puts the raw data (it's called 'Dynamic list'):
A1=ID, A1.Comment=jx:area(lastCell="C2")
A2=${row.ID}, A2.Comment=jx:each(items="data" var="row" lastCell="C2")
..

On my second sheet that formats the data:
A1=ID,
 A1.Comment=jx:area(lastCell="C2")
A2.formula=IF(COUNTIF('Dynamic list'!$A$2:A2, 'Dynamic list'!A2) > 1, "",  'Dynamic list'!A2),
 A2.Comment=jx:each (items="data" var="row" lastCell="C2")
..

Upvotes: 0

Views: 46

Answers (0)

Related Questions