1

I am trying to parse an MSXML which is a response from a web services via XmlStreamReader.

The below code is the response from the web service:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"> 
    <Worksheet ss:Name="Report"> 
        <Table> 
            <Row> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project Name</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project Code</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Project URI Report Setting</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Entry Date</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Actual Billable Hours (Selected Dates)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Actual Non-Billable Hours (Selected Dates)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Name</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Default Billing Rate</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">User Default Billing Rate (BC)</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Timesheet Start Date</Data> 
                </Cell> 
                <Cell ss:StyleID="Default"> 
                    <Data ss:Type="String">Timesheet End Date</Data> 
                </Cell> 
            </Row>
        </Table>
    </Worksheet>
</Workbook>

I wanted to get all the cell(s) in every row

Here is my code on how do I parse the response (assume that reportXML is the response):

 class Cell {
      string data { get; set; }
 }

 XmlStreamReader xsr = new XmlStreamReader(reportXML);

 while(xsr.hasNext()) {
      if (xsr.getEventType() == XmlTag.START_ELEMENT) {
           if (xsr.getLocalName() == 'Row') {
                Cell cell = parseCell(xsr);
                system.debug(cell);
           }
      }
      xsr.next();
 }

 Cell parseCell(XmlStreamReader reader) {
      Cell cell = new Cell();

      while(reader.hasNext()) {
           if (reader.getEventType() == XmlTag.END_ELEMENT) {
                break;
           }
           else if (reader.getEventType() == XmlTag.CHARACTERS) {
                cell.data = reader.getText();
           }

           reader.next();
      }

      return cell;
 }

The problem is I am only getting a single cell and not all the cell. Cell:[data=Project Name] is the only data from the debug logs which I think the first column cell only.

Why? and what is the code that bugs the logic for displaying all cell data?

EDIT:

I changed the 'Row' into 'Cell' on the condition for comparing localName. And I am now getting all the cell but how can I check if this belongs to the specific row ?(e.g., row at index 2).

1

I figure out how can I do it.. I just wanted to share my answer, see full code below:

 class Cell {
      string data { get; set; }
 }

 Integer rowCounter = 0;
 List<Cell> cellDataList = new List<Cell>();
 Map<Integer, List<Cell>> rowDataMap = new Map<Integer, List<Cell>>();

 XmlStreamReader xsr = new XmlStreamReader(reportXML);

 while(xsr.hasNext()) {
      if (xsr.getEventType() == XmlTag.START_ELEMENT) {

           if(xsr.getLocalName() == 'Row') {
                rowCounter++;
                rowDataMap.put(rowCounter, cellDataMap);
                cellDataList = new List<Cell>();
           }
           if (xsr.getLocalName() == 'Cell') {
                Cell cell = parseCell(xsr);
                cellDataList.add(cell);
           }

      }
      xsr.next();
 }

 Cell parseCell(XmlStreamReader reader) {
      Cell cell = new Cell();

      while(reader.hasNext()) {
           if (reader.getEventType() == XmlTag.END_ELEMENT)
                break;

           else if (reader.getEventType() == XmlTag.CHARACTERS)
                cell.data = reader.getText();

           reader.next();
      }
      return cell;
 }

 for (Integer rowKey : rowDataMap.keySet()) {
      if (!rowDataMap.get(rowKey).isEmpty()) {
           system.debug('===============ROW Start====================');
      for (Cell cells : rowDataMap.get(rowKey)) {
                system.debug(cells);
           }
           system.debug('===============ROW End====================');
      }
 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.