Solved

XML to Excel.

Posted on 2003-11-20
19
925 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Configure a Bean in an XML file 4 42
Eclipse Help Java EE 5,6,7 Documentation, why not Java EE 8 8 30
spring maven example issues 3 13
Java class and jar 3 25
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

828 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