Reading in Delphi generated XML in Excel

Question

Reading in Delphi generated XML in Excel

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (1 votes)

4

I am generating an XML by Excel itself (file of type XML Spreadsheet 2003).

The generated file has the following XML data pattern:

<Row>
<Cell><Data ss:Type="String">Williams</Data></Cell>
<Cell ss:StyleID="s63"><Datass:Type="Number">10644</Data></Cell>
<Cell><Data ss:Type="String">UK</Data></Cell>
<Cell><Data ss:Type="String">Qtr 2</Data></Cell>
</Row>

Creating a project in Delphi, using a DataSet and a DataSource, and doing the default configuration, Delphi can not read the fields of this XML, as it happens when we have a file in the default below. What do I do to convert the Excel spreadsheet into an XML standard that Delphi understands, or how do I interpret this XML pattern?

<ROW NOME="PAMELA" DT_NASCIMENTO="19521002" DT_CADASTRO="20031020" TELEFONE="2548874"
CELULAR="93712225" EMAIL="[email protected]"/>

delphi xml

asked by anonymous 30.06.2014 / 15:49

3 answers

How to apply a function to multiple objects? (Pure Javascript) Login using Entity with password encrypted as varbinary

score 3 · Answer 1

This model mentioned

<ROW NOME="PAMELA" DT_NASCIMENTO="19521002" DT_CADASTRO="20031020" TELEFONE="2548874"
CELULAR="93712225" EMAIL="[email protected]"/>

is the ClientDataSet data schema and differs from the template quoted above, which comes from Excel.

To read Excel Schema, you should use XMLDocument and not TClientDataSet

As in the following example:

var
  XMLDocument: IXMLDocument;
  MainNode, RowNode: TXMLNode;
  I, J: Integer;
begin
  XMLDocument := TXMLDocument.Create(nil);
  try
    XMLDocument.FileName := 'C:\Caminho\Para\Seu\Arquivo.xml';
    XMLDocument.Active := True;

    MainNode := XMLDocument.DocumentElement;

    for I := 0 to MainNode.ChildNodes.Count - 1 do
    begin
      RowNode := MainNode.ChildNodes[I];

      for J := 0 to RowNode.ChildNodes.Count - 1 do
      begin
        //Cada item aqui é uma célula e vc pode obter o valor
      end;
    end;

    XMLDocument.Active := False;
  finally
    XMLDocument := nil; //Ele é uma interface e é liberado automaticamente
  end;

score 1 · Answer 2

1

You can use the XMLMapper utility to map the data of the XML file to a TClientDataset. From there you can import the data into the TClientDataset and manipulate it.

07.07.2014 / 23:04

score 1 · Answer 3

I was able to make the delphi correctly read the xml generated by Excel:

vXMLDoc := TXMLDocument.Create(self);
vXMLDoc.LoadFromFile('Exemplo.xml');

J := vXMLDoc.DocumentElement.ChildNodes.Count;
Memo1.Clear;

for K := 0 to j -1 do
begin
  NodeRec := vXMLDoc.DocumentElement.ChildNodes[K];  
  Memo1.Lines.Add(NodeRec.ChildNodes[0].text + ' -' + 
  NodeRec.ChildNodes[1].text  + '-' 
  + NodeRec.ChildNodes[2].text+ ' -' + NodeRec.ChildNodes[3].text);     
end;