Solved

XML to Excel.

Posted on 2003-11-20
19
927 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 500 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

623 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