Generate XML from xls in java

You need a source xml file first:

http://javaalmanac.com/egs/javax.xml.transform/BasicXsl.html

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

xml source + xsl stylesheet = xml output

But where does it take the excel(xls) file as the input?

The parameters are clearly marked:

>>public static void xsl(String inFilename, String outFilename, String xslFilename)

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,

>>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 ...

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

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.

some good code samples in here:

http://www.onjava.com/pub/a/onjava/2003/04/16/poi_excel.html
http://www.devx.com/Java/Article/17301
http://www.javaworld.com/javaworld/javaqa/2002-05/01-qa-0503-excel3.html

You can also use JDBC to read the excel file, see the links in the last link I posted

also woth a look:

http://www-128.ibm.com/developerworks/xml/library/x-tipexc.html

Following shows Excel converted to XMLwith Java:

http://tinyurl.com/ekvrg

Thanks for the links CEHJ and Objects.  I will look into those.

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

You can actually work around those classes - they're not really important

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();
            }
            
      }

You are awesome. That worked. Thanks for all your help.

:-)