XML to Excel.

I have a XML file with 14 sections.The xml file must converted to Excel sheet with each section as individual sheet in Excel.I want to do this in Java.Are there any apis which will take the XML file as input gives back Excel sheet. This is very urgent and needs to be solved.I can give Mime type as Excel while transforming but it not very useful for me, because the all the sections are coming in one sheet.
TIA
Raja
LVL 5
rajaamirapuAsked:
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:
This uses Jakarta POI:

/**
 * SAX-reads an xml file and enters values into a
 * newly-created Excel worksheet
 */
import org.xml.sax.*;
import org.xml.sax.helpers.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;

public class JavaExcel extends DefaultHandler {
      HSSFWorkbook wb;
      StringBuffer chars;
      String currentElement;
      HSSFRow currentRow;
      HSSFSheet sheet;
      short rowCount;


      public JavaExcel(){
            // For the xml
            chars = new StringBuffer();
      }

      public void createWorkbook(){
            // create a new workbook
            wb = new HSSFWorkbook();
            // create a new sheet
            sheet = wb.createSheet("Product data");
            // create headings
            HSSFRow row = sheet.createRow((short)0);
            rowCount++;

            HSSFFont font = wb.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            HSSFCellStyle boldStyle = wb.createCellStyle();
            boldStyle.setFillForegroundColor(HSSFCellStyle.ROSE);
            boldStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

            // Apply the style
            boldStyle.setFillPattern((short)1);
            boldStyle.setFont(font);

            HSSFCell cell = row.createCell((short)0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("Product code");
            cell.setCellStyle(boldStyle);

            cell = row.createCell((short)1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("Name");
            cell.setCellStyle(boldStyle);

            cell = row.createCell((short)2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("Price");
            cell.setCellStyle(boldStyle);
      }


      public static void main(String[] args){
            JavaExcel je = new JavaExcel();
            je.createWorkbook();
            je.readXml();
            je.saveWorkbook();
      }

      void saveWorkbook(){
            try {
                  BufferedOutputStream out = new BufferedOutputStream( new FileOutputStream("workbook.xls"));
                  // write the workbook to the output stream
                  // close our file (don't blow out our file handles
                  wb.write(out);
                  out.close();
            }
            catch(Exception e){
                  e.printStackTrace();
            }
      }


      void readXml(){
            try {
                  // Create SAX 2 parser...
                  XMLReader xr = XMLReaderFactory.createXMLReader();
                  xr.setContentHandler(this);

                  // Parse the file...
                  xr.parse(new InputSource(new FileReader("example1.xml")));
            }
            catch ( Exception e )      {
                   e.printStackTrace();
            }
      }

      public void characters (char buf [], int offset, int length) throws SAXException {
            chars.append (buf,offset,length);
      }

      public void startElement(String namespaceURI,
                                                                               String localName,
                                                                               String qName,
                                                                               Attributes attr ) throws SAXException {
            //System.out.println(localName);
            currentElement = localName;
            if(currentElement.equals("Item")){
                  currentRow = sheet.createRow(rowCount);
                  rowCount++;
            }

      }

      public void endElement(String namespaceURI,
                                                                         String localName,
                                                                         String qName ) throws SAXException {
            HSSFCell cell = null;
            String nodeValue = null;
            if (currentElement != null){
                  if (currentElement.equals("ProductCode")){
                        nodeValue = getCharacters().trim();
                        cell = currentRow.createCell((short)0);
                        System.out.println(nodeValue);
                  }
                  else if (currentElement.equals("Name")){
                        nodeValue = getCharacters().trim();
                        cell = currentRow.createCell((short)1);
                  }
                  else if (currentElement.equals("Price")){
                        nodeValue = getCharacters().trim();
                        cell = currentRow.createCell((short)2);
                  }
                  else {
                        // Empty the 'stack'
                        getCharacters();
                  }
                  if (cell != null){
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(nodeValue);
                  }
            }
            currentElement = null;
      }

      String getCharacters(){
            String retval = chars.toString ();
            chars.setLength (0);
            return retval;
      }

}

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
girionisCommented:
 AFAIK there aren't. You will need to parse the XML manually and then convert it to XML using some API. Take a look at POI (http://jakarta.apache.org/poi/) it helps you manipulate MS Format FIles. Also take a look at XML parsers such as SAX or DOM implementations (http://xml.apache.org/xerces-j/)
0
rajaamirapuAuthor Commented:
I am searching for a Direct way i know this way of doing.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

girionisCommented:
 There is not a more direct way that POI!
0
rajaamirapuAuthor Commented:
Ok CEHJ I will see your Solution and get back.
0
jimmackCommented:
If you're talking about MIME types, are you providing the data from a web server?

 Try this one:

http://www.tek-tips.com/gviewthread.cfm/pid/426/qid/536425

Scroll down to May 15 2003.  Tarwn explains how to do this using ASP, but apart from changing:

<%
Response.ContentType = "application/vnd.ms-excel"
%>

to

<%
response.setContentType("application/vnd.ms-excel");
%>
0
rajaamirapuAuthor Commented:
jimmack,I think it is for Office XP.
0
jimmackCommented:
Oh well.  It was just an idea ;-)

I assume this means that you're not using XP then.

If you can't use the information in that link, then you're probably going to have to go with CEHJs solution.  It seems fairly complete ;-)
0
rajaamirapuAuthor Commented:
Hi CEHJ
My XML structure is like this
Sections Contain Section
Sections has 14 Sections
Each Section should come into a Individual sheet.
Is nessary that i should parse the XML and write the excel sheet using Jkartha's POI or anything any other tool is there.
Regards
Raja Sekhar
0
CEHJCommented:
If you're sending it from a server to a web client, then you can simply specify the mime type as Excel and send it in csv format. The Excel plugin will load the input as a spreadsheet.
0
CEHJCommented:
(we don't know if you're talking client/server Web or not)
0
rajaamirapuAuthor Commented:
How do i get the individual sections into a individual sheets
Thanks for help
0
rajaamirapuAuthor Commented:
Yes the application is present in  JSP
0
CEHJCommented:
Well I'm not sure that's possible in one web page. For instance, when did you see more than one sheet in Excel that was not in a separate tab of a tabbed pane? You'd have to return it broken up in different ways.
0
TallBoyCommented:
As CEHJ pointed out, your answer lies with using POI.

http://jakarta.apache.org/poi/index.html
0
rajaamirapuAuthor Commented:
Hi,
I think my problem is almost solved with the use of Saxon Parser.I think i can make work with it.I will let you know when i am sucessful.
Regards
Raja Sekhar

0
CEHJCommented:
Yes - xml parser can output as csv as I suggested
0
jimmackCommented:
>> jimmack,I think it is for Office XP.

I the XML in the link I posted works in Office 2000.
0
jimmackCommented:
That should be:

I understand that the XML in the link I posted works in Office 2000.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.