<?xml version="1.0" encoding="UTF-8"?>
<RevenueReport>
<Period>201901</Period>
<RevenueRecord>
<Country>Germany</Country>
<ActualRevenue>1300000</ActualRevenue>
<TargetRevenue>1400000</TargetRevenue>
<Currency>EUR</Currency>
</RevenueRecord>
<RevenueRecord>
<Country>France</Country>
<ActualRevenue>800000</ActualRevenue>
<TargetRevenue>700000</TargetRevenue>
<Currency>EUR</Currency>
</RevenueRecord>
[...]
</RevenueReport>
import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
def Message processData(Message message) {
def body = message.getBody(java.lang.String) as String
def dataGrid2 = [];
def xml = new XmlSlurper().parseText(body);
def dataGrid =
[
["Revenue Report"],
[],
["Period: ", xml.Period.toString()],
[],
["Country", "Actual", "Target", "Difference", "Currency"]
]
xml.RevenueRecord.each { RevenueRecord ->
dataGrid.add(
[
RevenueRecord.Country.toString(),
RevenueRecord.ActualRevenue,
RevenueRecord.TargetRevenue,
(RevenueRecord.ActualRevenue.toInteger()-RevenueRecord.TargetRevenue.toInteger()),
RevenueRecord.Currency.toString()
]
)
}
def dataGridString = dataGrid.inspect()
dataGridString = dataGridString.replaceAll("\'", "\"")
message.setProperty("data", dataGridString);
return message;
}
(RevenueRecord.ActualRevenue.toInteger()-RevenueRecord.TargetRevenue.toInteger()),
{
"requests": [
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"endRowIndex": 1,
"endColumnIndex": 1
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"fontSize": 14,
"bold": true
}
}
},
"fields": "userEnteredFormat.textFormat.bold"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 2,
"endRowIndex": 3,
"startColumnIndex": 1,
"endColumnIndex": 2
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
}
}
},
"fields": "userEnteredFormat.textFormat.bold"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 4,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 1
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
},
"backgroundColor": {
"red": 0,
"green": 0.7,
"blue": 0
}
}
},
"fields": "userEnteredFormat(backgroundColor,textFormat)"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 4,
"endRowIndex": 5,
"startColumnIndex": 1,
"endColumnIndex": 3
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
},
"backgroundColor": {
"red": 0,
"green": 0.5,
"blue": 0.9
}
}
},
"fields": "userEnteredFormat(backgroundColor,textFormat)"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 4,
"endRowIndex": 5,
"startColumnIndex": 3,
"endColumnIndex": 4
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
},
"backgroundColor": {
"red": 0.9,
"green": 0.5,
"blue": 0
}
}
},
"fields": "userEnteredFormat(backgroundColor,textFormat)"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 4,
"endRowIndex": 5,
"startColumnIndex": 4,
"endColumnIndex": 5
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
},
"backgroundColor": {
"red": 0.5,
"green": 0.5,
"blue": 0.5
}
}
},
"fields": "userEnteredFormat(backgroundColor,textFormat)"
}
},
{
"repeatCell": {
"range": {
"sheetId": ${property.worksheetID},
"startRowIndex": 5,
"startColumnIndex": 1,
"endColumnIndex": 4
},
"cell": {
"userEnteredFormat": {
"numberFormat": {
"type": "NUMBER",
"pattern": "#,##0"
}
}
},
"fields": "userEnteredFormat(numberFormat)"
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": ${property.worksheetID},
"startColumnIndex": 3,
"endColumnIndex": 4,
"startRowIndex": 5
}
],
"booleanRule": {
"condition": {
"type": "NUMBER_LESS",
"values": [
{
"userEnteredValue": "0"
}
]
},
"format": {
"backgroundColor": {
"green": 0.2,
"red": 0.8
}
}
}
},
"index": 0
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": ${property.worksheetID},
"startColumnIndex": 3,
"endColumnIndex": 4,
"startRowIndex": 5
}
],
"booleanRule": {
"condition": {
"type": "NUMBER_GREATER",
"values": [
{
"userEnteredValue": "0"
}
]
},
"format": {
"backgroundColor": {
"green": 0.8,
"red": 0.2
}
}
}
},
"index": 1
}
}
]
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
10 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |