Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

Not able to write Excel Sheets in the different XML format OR with the different root element  RSS feed

 
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a Excel Sheet(.xls) having 4 sheet in a workbook. Now I am trying to read it using a java program.

I am able to read it in a loop and witing this data in XML file, but the all the data (9*4= 36 lines) are written in a Root Element i.e.






Now my requirement is to write this XML with the separte seprate root element and Header as per Sheet.

I am getting an exception if I try it in a loop:--

Reading successfully all the sheet code is here:-




ANd :




Can any one help me to resolve it:

Thanks in Advanced!!!

 
Rancher
Posts: 42974
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Now my requirement is to write this XML with the separte seprate root element and Header as per Sheet.


Maybe I don't understand what you're asking, but XML will always have a single root element. If it has more than root element, then it's not XML. Please post a short high-level extract of the output XML as you are envisioning it; omit all irrelevant details.

I am getting an exception if I try it in a loop


You did not post the full stack trace of the exception.
 
Rd Dari
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dittmer,



Please ignore that line

I am getting an exception if I try it in a loop

.

because if I am trying to add the in for loop .

then getting the below Exception






But its not right. As you said in the above reply , in a XML only one root element can be used.

Actually the requirement is that from all the sheets (1,2,3,4...) I want to read data which i am reading successfully and writing in XML file.

But in this file with the one root element data not identifiable their respective Sheet , so how I can create the different different XML here.

Thanks for reply!!!
 
Ulf Dittmer
Rancher
Posts: 42974
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
While you didn't say what you would want the output XML to be like, it looks like it's Java code above that treats all rows in all sheets identically. If you want an additional layer "<sheet> ... </sheet>" between "<products>" and "<product>", or to introduce an attribute "<product sheet="1">" etc. then that should be easy to do. The details depend on how you want to distinguish data from the various sheets, and you haven't told us about that.
 
Rd Dari
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dittmer,

I think i couldn't explain properly, Ok now i want to do show all sheet's data in XML format but it should be identifiable to the respective sheet like <Sheet1><products><product></product></products></Sheet1> , <Sheet2><products><product></product></products></Sheet2>

could you help me to do it with the above code.

I have 10 record in each sheet including the Header. so after first 9 record the element should be closed , again it should start to write the Sheet 2 data.




Thanks for the reply!!!

 
Ulf Dittmer
Rancher
Posts: 42974
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That won't work because it would result in multiple root elements. This would:

I recommend against using varying element names like Sheet1, Sheet2 etc. - it's generally better to differentiate them using an attribute.
 
Rd Dari
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok,

Could you please give me Any idea to create different-different XML while reading from Excel as per separate sheet.

Thanks!!


 
Ulf Dittmer
Rancher
Posts: 42974
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, your code already creates the "products" and "product" elements at appropriate places - what stops you from adding a "sheet" element at the start of each sheet?

I don't understand what you mean by "different-different XML".
 
Rd Dari
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey,

As you are able to see my XML file format, there 36 records from all the sheets excuding header.


I want to show the data for first sheet 9 sub element for the first sheet.

for example i have only one record in my first sheet.. then second sheet data should like this

but this should an identity to identify the data that which sheets is belongs to


Now its mixing all the four sheet's data in root element <products><product></product><products>

Thaks for your all efforts to replying me.
 
Ulf Dittmer
Rancher
Posts: 42974
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As I've said several times by now, you can't have more than one root element. If you want to establish an additional element hierarchy "sheet" between "products" and "product", then that should be easy to do. That's what my last 3 posts are about.

Is that what you want to do? If so, tell us in detail what difficulty you're having implementing that. The code you posted already adds the "products" and "product" tags at appropriate places; adding an additional level "sheets" whenever a new sheet is started does not sound all that hard. What have you tried so far?
 
Rd Dari
Ranch Hand
Posts: 214
Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dittmer,

I got your point and tried it and now its working fine.
Result:-

I am able to identify the data as per sheet with the <products><Sheet1><product></product></Sheet1><Sheet2><product></product></Sheet2> and so on..........</products>

I applied the below change within the above code:



Thanks once again for your all efforts !!!
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!