Generate XML from xls in java

I need to generate an XML file from a xls file in Java. I need some code samples to do this.

thanks,
Anu
expertsitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
0
expertsitAuthor Commented:
I think the sample that you provided takes the XML as the input.

I need to convert an xls into an xml file.

thanks in advance
0
CEHJCommented:
xml source + xsl stylesheet = xml output
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

expertsitAuthor Commented:
But where does it take the excel(xls) file as the input?
0
CEHJCommented:
The parameters are clearly marked:

>>public static void xsl(String inFilename, String outFilename, String xslFilename)
0
expertsitAuthor Commented:
I am sorry , I am not sure if I am missing something here.

In the program it says 'xsl' not xls .

 I would like to have the xls (excel) file as the input and generate XML file as the output.

thanks,
0
CEHJCommented:
>>I am sorry , I am not sure if I am missing something here.

Doh - sorry - it's me. I must be getting dsylecix in my old age. Hang on ...
0
CEHJCommented:
Not sure if there's anything ready-made. I'd suggest POI and DOM

http://jakarta.apache.org/poi/hssf/index.html

http://javaalmanac.com/egs/org.w3c.dom/pkg.html
0
expertsitAuthor Commented:
Thanks for sending me the links. I would appreciate if I can get some code samples. That will help me get started.

thanks once again.
0
objectsCommented:
0
CEHJCommented:
Following shows Excel converted to XMLwith Java:

http://tinyurl.com/ekvrg
0
expertsitAuthor Commented:
Thanks for the links CEHJ and Objects.  I will look into those.
0
expertsitAuthor Commented:
For the program in the link
http://tinyurl.com/ekvrg

how do I get the jar files to include in my classpath.

For example , they imported a class com.canoo.webtest.engine.ContextHelper; How can I get the jar files which contains all these classes.

  I searched in the site but could not find it.

thanks in advance
0
CEHJCommented:
You can actually work around those classes - they're not really important
0
expertsitAuthor Commented:
Ok. I got the program working without any compilation errors. I have copied the content of the doExecute method in my main method. When I run my program it runs without any errors but I dont get any output. How can I make it physically create an xml file at a location?

Sorry if I am asking too much.


Here is my code.

public static void main(String [] args)
      {
            try
            {
                  InputStream input = GenerateXML.class.getResourceAsStream("Test.xls");
                  POIFSFileSystem fs = new POIFSFileSystem(input);
                  final HSSFWorkbook wb = new HSSFWorkbook(fs);
                  final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
                  final Document doc = builder.newDocument();
                  final Element root = doc.createElement("wb");
                  doc.appendChild(root);
                  root.setAttribute("backup", String.valueOf(wb.getBackupFlag()));
                  root.setAttribute("numberOfFonts", String.valueOf(wb.getNumberOfFonts()));
                  root.setAttribute("numberOfCellStyles", String.valueOf(wb.getNumCellStyles()));
                  root.setAttribute("numberOfNames", String.valueOf(wb.getNumberOfNames()));
                  
                  final Element sheets = doc.createElement("sheets");
                  for(int i=0;i<wb.getNumberOfSheets();i++)
                  {
                        final HSSFSheet sheetAt = wb.getSheetAt(i);
                        final Element sheetElement = doc.createElement("sheet");
                        sheetElement.setAttribute("index",String.valueOf(i));
                        sheetElement.setAttribute("name", wb.getSheetName(i));
                        sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum()));
                        sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum()));
                        sheetElement.setAttribute("physicalRows",String.valueOf(sheetAt.getPhysicalNumberOfRows()));
                        sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight()));
                  sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth()));
                  sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage()));
                  sheets.appendChild(sheetElement);
              }
                  root.appendChild(sheets);
                  final StringWriter sw = new StringWriter();
                  writeXmlFile(doc,sw);
                  
            }catch (IOException ex)
            {
                  ex.printStackTrace();
            }
            catch (ParserConfigurationException e)
            {
                  e.printStackTrace();
            }
      }
      
      protected static void writeXmlFile(final Document doc, final Writer writer)
      {
            final OutputFormat format = new OutputFormat(doc);
            format.setIndenting(true);
            format.setEncoding("ISO-8859-1");
            format.setLineWidth(50);
            
            try
            {
                  final XMLSerializer serializer = new XMLSerializer(writer, format);
                  serializer.asDOMSerializer();
                  serializer.serialize(doc.getDocumentElement());
                  writer.close();
            }catch(IOException e)
            {
                  e.printStackTrace();
            }
            
      }



0
CEHJCommented:
>> final StringWriter sw = new StringWriter();

Use a FileWriter instead


final Writer fw = new FileWriter("my.xml");
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
expertsitAuthor Commented:
You are awesome. That worked. Thanks for all your help.
0
CEHJCommented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.