Forming an XML by extracting values from Excel in Java

Question

My requirement is search for a value (Message ID) in excel and take the other column values (Source and Target) of that row and form a XML.

Say my excel looks like below:

Message ID  Output  Source  Target
@A74104I  @A74104O  IPT     CRD
@A74101    @A74101  IAP     CRD
@A74101    @A74101  IAP     CRD
@A74104I  @A74104O  IAP     CRD

For e.g. for message ID A74104I extract Source and target and form an XML as below. This messageID repeats and there are 2 source and target which are appended in same XML.

<ApplicationParameters>
<Parms name="default" type="default">
    <SGHeader>
        <ServiceName>
            <TargetApplication>
                <IAP>CRD</IAP>
                <IPT>CRD</IPT>
            </TargetApplication>
        </ServiceName>
    </SGHeader>
</Parms>

For each messageID create different XML. If for a particular messageID Source repeats ( e.g. in above excel for A74101 Source IAP is the same) then put this messageID in an exception file which looks like<MessageID> <A74101/> </MessageID>

There may be other ways to do it (with Visual BASIC, for example), but this is an excellent task that could be automated (if you need it to be!) using bash, perl, python or powershell (will become much more easier if you are storing the excel in csv format). Do you want to automate it OR do you just need it as something like a button in excel that the user runs interactively? — blackpen, Sep 19 '16 at 08:54
@blackpen in the above scenario MessageId is passed from one more file as input and I am not familiar with other scripts. — karthik rao, Sep 19 '16 at 09:25
So, does that mean you want to automate it? When you say that you want to extract a MessageId from first file and then you want to search in excel for that particular MessageId and create an XML message (to possibly write the generated xml message into a third file), that sounds like automation to me. Do you have the luxury of storing the excel as csv file? If you can, you can automate the whole thing using a VBScript or powershell script. If you are on a non-Unix (non-linux) platform (Windows?), then that will be a good route to take. — blackpen, Sep 19 '16 at 09:42
@blackpen, Yes I want to automate this. I can't store the excel as csv file. And I use windows platform. — karthik rao, Sep 19 '16 at 09:45

score 0 · Answer 1 · edited May 23 '17 at 12:08

If you want to do it in Java, look here for code on how to parse Excel sheets.

Once that is done, you remain with extraction of MessageId from input file. You can do that in Java, using Regular Expressions. Look here or here for code on how to do it.

If you want to do it using powershell, look at this post. He has almost same requirement as you do (other than that he reads input from console).

You can search through rows/columns as shown in that post. Once a match is found, you extract relevant information and write-out the XML message to an external file.

Once you are able to do that, then you can worry about extracting MessageId from First Input File by coding it as shown in this post.

Does the procedure look like a good-fit for your need?

No this does not help, I need to create in Java. – karthik rao Sep 19 '16 at 10:59 — karthik rao, Sep 19 '16 at 10:59

Forming an XML by extracting values from Excel in Java

1 Answers1