[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

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
0
expertsit
Asked:
expertsit
  • 9
  • 8
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
expertsitAuthor Commented:
You are awesome. That worked. Thanks for all your help.
0
 
CEHJCommented:
:-)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now