Groovy script to write XML data to xls(Excel)

Question

I have a XML structure like below. How to write a groovy script to write XML data to xls(Excel)? Could someone help me on this?

<Record>
  <Action>create</Action>
  <ID KEMASAN>DUS, 1 BOTOL PLASTIK @ 28 TABLET</ID KEMASAN>
  <Barcode>(01)00372626270113(21)200000022671(17)(10)ICS_DECOM</Barcode>
  <Barcode>(01)00372626270113(21)200000022670(17)(10)ICS_DECOM</Barcode>
  <Barcode>(01)00372626270113(21)200000000052(17)(10)ICS_DECOM</Barcode>
  <NIE>DKI1844200317A1</NIE>
  <LOT NO>ICS_DECOM</LOT NO>
  <Exp Date>31/12/2024</Exp Date>
  <Batch No>ICS_DECOM</Batch No>
  <GTIN>00372626270113</GTIN>
  <IS_ACTIVE>TRUE</IS_ACTIVE>
  <IS_SAMPLE>FALSE</IS_SAMPLE>
  <IS_REJECT>FALSE</IS_REJECT>
  <MFG DATE>01/01/2023</MFG DATE>
  <Case SGTIN>urn:epc:id:sgtin:0372626.527011.200000000052</Case SGTIN>
  <Pallet SGTIN>urn:epc:id:sscc:0361958.0001046962</Pallet SGTIN>
</Record>

Answer 1

I'd suggest using spreadsheet.dsl.builders for this. Your XML isn't valid because it contains spaces for the tag names (ie <ID KEMASAN>, <Batch No>, etc) so I added an underscore where the space was and was able to run the following:

@Grab('builders.dsl:spreadsheet-builder-poi:3.0.1')
@Grab('builders.dsl:spreadsheet-builder-groovy:3.0.1')
import builders.dsl.spreadsheet.builder.poi.PoiSpreadsheetBuilder

def xml = new XmlSlurper().parseText('''
<root>
    <Record>
      <Action>create</Action>
      <ID_KEMASAN>DUS, 1 BOTOL PLASTIK @ 28 TABLET</ID_KEMASAN>
      <Barcode>(01)00372626270113(21)200000022671(17)(10)ICS_DECOM</Barcode>
      <Barcode>(01)00372626270113(21)200000022670(17)(10)ICS_DECOM</Barcode>
      <Barcode>(01)00372626270113(21)200000000052(17)(10)ICS_DECOM</Barcode>
      <NIE>DKI1844200317A1</NIE>
      <LOT_NO>ICS_DECOM</LOT_NO>
      <Exp_Date>31/12/2024</Exp_Date>
      <Batch_No>ICS_DECOM</Batch_No>
      <GTIN>00372626270113</GTIN>
      <IS_ACTIVE>TRUE</IS_ACTIVE>
      <IS_SAMPLE>FALSE</IS_SAMPLE>
      <IS_REJECT>FALSE</IS_REJECT>
      <MFG_DATE>01/01/2023</MFG_DATE>
      <Case_SGTIN>urn:epc:id:sgtin:0372626.527011.200000000052</Case_SGTIN>
      <Pallet_SGTIN>urn:epc:id:sscc:0361958.0001046962</Pallet_SGTIN>
    </Record>
</root>
''')

File output = new File("spreadsheet.xlsx")

PoiSpreadsheetBuilder.create(output).build {
    sheet("My Sheet") {
        xml.Record.each { r ->
            row {
                cell "Action"
                cell "ID"
                cell "Batch Number"
                cell "NIE"
            }
            row {
                cell r.Action.text()
                cell r.ID_KEMASAN.text()
                cell r.Batch_No.text()
                cell r.NIE.text()
            }
        }
    }
}
println("Done! ${output.absolutePath}")