Solved

XML to Excel.

Posted on 2003-11-20
19
921 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now