Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XML to Excel.

Posted on 2003-11-20
19
Medium Priority
?
928 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:rajaamirapu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 4
  • +2
19 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 1500 total points
ID: 9786280
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
 
LVL 35

Expert Comment

by:girionis
ID: 9786291
 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
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9786399
I am searching for a Direct way i know this way of doing.
0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 
LVL 35

Expert Comment

by:girionis
ID: 9786418
 There is not a more direct way that POI!
0
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9786473
Ok CEHJ I will see your Solution and get back.
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9786708
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
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9786928
jimmack,I think it is for Office XP.
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9787171
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
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9787396
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9787567
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9787579
(we don't know if you're talking client/server Web or not)
0
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9787583
How do i get the individual sections into a individual sheets
Thanks for help
0
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9787595
Yes the application is present in  JSP
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9787643
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
 
LVL 2

Expert Comment

by:TallBoy
ID: 9788452
As CEHJ pointed out, your answer lies with using POI.

http://jakarta.apache.org/poi/index.html
0
 
LVL 5

Author Comment

by:rajaamirapu
ID: 9788693
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 9790380
Yes - xml parser can output as csv as I suggested
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9796813
>> jimmack,I think it is for Office XP.

I the XML in the link I posted works in Office 2000.
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9796822
That should be:

I understand that the XML in the link I posted works in Office 2000.
0

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
The viewer will learn how to implement Singleton Design Pattern in Java.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question