We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Generate XML from xls in java

expertsit
expertsit asked
on
Medium Priority
502 Views
Last Modified: 2012-08-13
I need to generate an XML file from a xls file in Java. I need some code samples to do this.

thanks,
Anu
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:

Author

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
CERTIFIED EXPERT
Top Expert 2016

Commented:
xml source + xsl stylesheet = xml output

Author

Commented:
But where does it take the excel(xls) file as the input?
CERTIFIED EXPERT
Top Expert 2016

Commented:
The parameters are clearly marked:

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

Author

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,
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>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 ...
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

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.
Mick BarryJava Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
CERTIFIED EXPERT
Top Expert 2016

Commented:
Following shows Excel converted to XMLwith Java:

http://tinyurl.com/ekvrg

Author

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

Author

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
CERTIFIED EXPERT
Top Expert 2016

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

Author

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



CERTIFIED EXPERT
Top Expert 2016
Commented:
>> final StringWriter sw = new StringWriter();

Use a FileWriter instead


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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
You are awesome. That worked. Thanks for all your help.
CERTIFIED EXPERT
Top Expert 2016

Commented:
:-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.