Conquer Club

Creating XML from Excel

So, I figured that XML is essentially a very repetitious process, involving some standard formatting and dumping in a lot of data... Yes?

And I know a good way to store a lot of data is in a spreadsheet, easy to link, easy to connect and link various parts (co-ordinates, bonuses, attacks, bombards) etc to the territory by putting them on the same line, so I thinks to myself... If I could combine the two, I could generate all of my XML from an excel spreadsheet..

With a little bit of playing I've been successful, and have generated perfectly structured, perfectly tested and applied XML for the Hypercube map using it...

If you are interested, I'd be happy to show you what I've done and talk you through the steps and processes, so you can speed up and simplify your XML creation, as well as make it stronger and more resilient. Of course, I've still gone through the whole thing line by line checking it against the map, but it saved me 50,000 character presses.

Here's the excel file I used for generating the XML for

http://www.sendspace.com/file/rkdqjm

Which generated most of the bulky XML.

The rest I added in in notepad...

XML can be found below

http://www.sendspace.com/file/rd9x4y

Have a look through it, and if you've got any questions, post away

Nice work...

Essentially using Excel to help with Copy N Paste!!

C.

upload the file and put some instructions so we can take a look.

Trying to but my internet is being well slow and sendspace seems to be down

man nice idea really great addition i guess

Tieryn is you managed to upload the file and give a good explanation in the first post ill stick this in the map making tools.

If I don't get it done tonight, I'll do it tomorrow night gimil, my internet's just being poop.

i cant figure excel out >_<

how come?

Excel is pretty much how I've always done it...

nvm i think i figured it out...


but i dont know if im doing it right

Well look, I can't get the file up at the moment cause some major internet link has gone down and I can't get the rest of the web. (for some reason, I -can- get conquer club! Thank god!!!)

A basic territory would look like this...

Code: Select all

<territory>      
   =CONCATENATE("<name>",Sheet1!A5,"</name>")   
   <borders>   
      =IF(Sheet1!B5="","",CONCATENATE("<border>",Sheet1!B5,"</border>"))
      =IF(Sheet1!C5="","",CONCATENATE("<border>",Sheet1!C5,"</border>"))
      =IF(Sheet1!D5="","",CONCATENATE("<border>",Sheet1!D5,"</border>"))
      =IF(Sheet1!E5="","",CONCATENATE("<border>",Sheet1!E5,"</border>"))
      =IF(Sheet1!F5="","",CONCATENATE("<border>",Sheet1!F5,"</border>"))
      =IF(Sheet1!G5="","",CONCATENATE("<border>",Sheet1!G5,"</border>"))
   </borders>   
   <coordinates>   
      =CONCATENATE("<smallx>",Sheet1!H5,"</smallx>")
      =CONCATENATE("<smally>",Sheet1!I5,"</smally>")
      =CONCATENATE("<largex>",Sheet1!J5,"</largex>")
      =CONCATENATE("<largey>",Sheet1!K5,"</largey>")
   </coordinates>   
</territory>      

Note, those "IF"s were there because I had some blank spaces, so that just didn't put a <border></border> when there was a blank.. It just left it blank.

A continent like this

Code: Select all

<continent>      
   =CONCATENATE("<name>",Sheet1!M3,"</name>")   
   =CONCATENATE("<bonus>",Sheet1!R3,"</bonus>")   
   <components>   
      =CONCATENATE("<territory>",Sheet1!N3,"</territory>")
      =CONCATENATE("<territory>",Sheet1!O3,"</territory>")
      =CONCATENATE("<territory>",Sheet1!P3,"</territory>")
      =CONCATENATE("<territory>",Sheet1!Q3,"</territory>")
   </components>   
</continent>      


and an override continent like this

Code: Select all

<continent>      
   =CONCATENATE("<name>",Sheet1!AH41,"</name>")   
   =CONCATENATE("<bonus>",Sheet1!AQ41,"</bonus>")   
   <components>   
      =CONCATENATE("<territory>",Sheet1!AI41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AJ41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AK41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AL41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AM41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AN41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AO41,"</territory>")
      =CONCATENATE("<territory>",Sheet1!AP41,"</territory>")
   </components>   
   <overrides>   
      =CONCATENATE("<override>",Sheet1!AR41,"</override>")
      =CONCATENATE("<override>",Sheet1!AS41,"</override>")
      =CONCATENATE("<override>",Sheet1!AT41,"</override>")
      =CONCATENATE("<override>",Sheet1!AU41,"</override>")
      =CONCATENATE("<override>",Sheet1!AV41,"</override>")
      =CONCATENATE("<override>",Sheet1!AW41,"</override>")
   </overrides>   
</continent>      


Basically, since the hypercube map is so patterned, all I would do is copy that into another worksheet (at the same row/col position), then copy it and move it down one row, then paste. All the links would jump down one row. Then I cut that and put it back into my "XML Generation" sheet.

Then I copied the -two- continents, did the same process, but moved down 2, and cut them back. then I did 4 and moved down 4, etc, until I got my 64 territories or whatever.

I assume it'll be slightly more difficult on a less logical/patterned map, but the principle is the same, and the copy/pasting of dynamic links will allow you to move them about to where you need them..

The basic rules you need to know are if you copy something and paste it somewhere else, the distance the cell moved, all its references will move the same. If I copy and move down 3, I'll be referring to the cell 3 lower than I was before.

If I -CUT- and paste, then the link does not change, it still refers to the same cell.

By creative use of these two, you can easily create your XML. And more, easily edit it. I left out the co-ordinates, cause I didn't have them. When I got them, I entered them -ONCE- into my original document, and the entire XML updated itself automatically.

sounds like a good idea


after it's verified to be awesome, we're going to ask you to work on a 'point and click' way to do centering of coordinates.

chop chop

edbeard wrote:sounds like a good idea


after it's verified to be awesome, we're going to ask you to work on a 'point and click' way to do centering of coordinates.

chop chop

jota already made one. i use it for all of my maps.

http://www.conquerclub.com/forum/viewtopic.php?t=1085

WM

Okay, so my Internet is no longer up the pooch, and I can access the rest of the interweb, so...

Here's the excel file I used for generating the XML for

http://www.sendspace.com/file/rkdqjm

Which generated most of the bulky XML.

The rest I added in in notepad...

XML can be found below

[url]http://www.sendspace.com/file/rd9x4y[/xml]

Have a look through it, and if you've got any questions, feel free to ask

Okay, so my Internet is no longer up the pooch, and I can access the rest of the interweb, so...

Here's the excel file I used for generating the XML for

http://www.sendspace.com/file/rkdqjm

Which generated most of the bulky XML.

The rest I added in in notepad...

XML can be found below

http://www.sendspace.com/file/rd9x4y

Have a look through it, and if you've got any questions, feel free to ask

WidowMakers wrote:

edbeard wrote:sounds like a good idea


after it's verified to be awesome, we're going to ask you to work on a 'point and click' way to do centering of coordinates.

chop chop

jota already made one. i use it for all of my maps.

http://www.conquerclub.com/forum/viewtopic.php?t=1085

WM

yep great work on that by jota.
i also use it for all my maps and then i just make very small adjustments.

Also, I put the links in the first post, so if people want to download, use and modify, feel free

Discuss here if you have any questions on what I've done or how to adapt it.