CODEWITHSUNDEEP
excelxmlexcel-addins
Andrew Tyson
Andrew Tyson

Reputation: 139

MS 365 Excel Add-in worksheet XML

I am looking at developing a MS 365 Excel Add-in that will be able to source rows from a worksheet to compose test cases for execution against a webservice that accepts a XML request payload, and processes a XML response creating a row in a separate worksheet. The sequence would be as follows:

  1. Load worksheet A rows into XML request document
  2. Apply XSL-T to transform into webservice request format
  3. Call webservice
  4. Process webservice response and write XML to worksheet B
  5. Compare worksheet B to expected result in worksheet C

For example to following source worksheet data

First Name, Last Name, DOB
John, Doe, 01/01/1970
Fred, Smith, 01/01/1980

would be converted into

<Worksheet>
  <Row>
     <First Name>John</First Name>
     <Last Name>Doe</Last Name>
     <DOB>01/01/1970</DOB>
  </Row>
  <Row>
     <First Name>Fred</First Name>
     <Last Name>Smith</Last Name>
     <DOB>01/01/1980</DOB>
  </Row>
</Worksheet>

I would also like to convert the response using a XSL-T into a format that can easily be used to create rows in a response worksheet.

My question is whether there is any OOB support in the Excel libraries to automatically convert from the worksheet to XML and vice versa

Thanks in advance

Upvotes: 0

Views: 43

Answers (1)

Andrew Tyson
Andrew Tyson

Reputation: 139

The following works

export async function convertSheet(){
  await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("vp4c");

    let range = sheet.getRange("A1:V19");
    range.load("values");
    range.load("rowCount");
    range.load("columnCount");
    await context.sync();

    var xml = '<Worksheet>';
    const rowCount = range.rowCount;
    const columnCount = range.columnCount;
    for ( var r = 1; r < rowCount; r++){
      xml += '<Row>';
        for ( var c = 0; c < columnCount; c++){
           const fieldName = range.values[0][c];
           const fieldValue = range.values[r][c];
           console.log(fieldName + " = " + fieldValue);
           xml += '<' + fieldName + '>' +  fieldValue + '</' + fieldName + '>';
        }
      xml += '</Row>';
    }
    xml += "</Worksheet>" 
    console.log(xml);
  });
}

Upvotes: 0

Related Questions