Open Source Repository

Home /excel/jxl-2.6.12 | Repository Home



jxl/demo/XML.java
/*********************************************************************
*
*      Copyright (C) 2002 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/

package jxl.demo;

import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.Font;
import jxl.format.Pattern;

/**
 * Simple demo class which uses the api to present the contents
 * of an excel 97 spreadsheet as an XML document, using a workbook
 * and output stream of your choice
 */
public class XML
{
  /**
   * The output stream to write to
   */
  private OutputStream out;

  /** 
   * The encoding to write
   */
  private String encoding;

  /**
   * The workbook we are reading from
   */
  private Workbook workbook;
  
  /**
   * Constructor
   *
   @param w The workbook to interrogate
   @param out The output stream to which the XML values are written
   @param enc The encoding used by the output stream.  Null or 
   * unrecognized values cause the encoding to default to UTF8
   @param f Indicates whether the generated XML document should contain
   * the cell format information
   @exception java.io.IOException
   */
  public XML(Workbook w, OutputStream out, String enc, boolean f)
    throws IOException
  {
    encoding = enc;
    workbook = w;
    this.out = out;

    if (encoding == null || !encoding.equals("UnicodeBig"))
    {
      encoding = "UTF8";
    }

    if (f)
    {
      writeFormattedXML();
    }
    else
    {
      writeXML();
    }

  }

  /**
   * Writes out the workbook data as XML, without formatting information
   */
  private void writeXML() throws IOException
  {
    try
    {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);
      
      bw.write("<?xml version=\"1.0\" ?>");
      bw.newLine();
      bw.write("<!DOCTYPE workbook SYSTEM \"workbook.dtd\">");
      bw.newLine();
      bw.newLine();
      bw.write("<workbook>");
      bw.newLine();
      for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++)
      {
        Sheet s = workbook.getSheet(sheet);

        bw.write("  <sheet>");
        bw.newLine();
        bw.write("    <name><![CDATA["+s.getName()+"]]></name>");
        bw.newLine();
      
        Cell[] row = null;
      
        for (int i = ; i < s.getRows() ; i++)
        {
          bw.write("    <row number=\"" + i + "\">");
          bw.newLine();
          row = s.getRow(i);

          for (int j = ; j < row.length; j++)
          {
            if (row[j].getType() != CellType.EMPTY)
            {
              bw.write("      <col number=\"" + j + "\">");
              bw.write("<![CDATA["+row[j].getContents()+"]]>");
              bw.write("</col>");
              bw.newLine();
            }
          }
          bw.write("    </row>");
          bw.newLine();
        }
        bw.write("  </sheet>");
        bw.newLine();
      }
      
      bw.write("</workbook>");
      bw.newLine();

      bw.flush();
      bw.close();
    }
    catch (UnsupportedEncodingException e)
    {
      System.err.println(e.toString());
    }
  }

  /**
   * Writes out the workbook data as XML, with formatting information
   */
  private void writeFormattedXML() throws IOException
  {
    try
    {
      OutputStreamWriter osw = new OutputStreamWriter(out, encoding);
      BufferedWriter bw = new BufferedWriter(osw);
      
      bw.write("<?xml version=\"1.0\" ?>");
      bw.newLine();
      bw.write("<!DOCTYPE workbook SYSTEM \"formatworkbook.dtd\">");
      bw.newLine();
      bw.newLine();
      bw.write("<workbook>");
      bw.newLine();
      for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++)
      {
        Sheet s = workbook.getSheet(sheet);

        bw.write("  <sheet>");
        bw.newLine();
        bw.write("    <name><![CDATA["+s.getName()+"]]></name>");
        bw.newLine();
      
        Cell[] row = null;
        CellFormat format = null;
        Font font = null;
      
        for (int i = ; i < s.getRows() ; i++)
        {
          bw.write("    <row number=\"" + i + "\">");
          bw.newLine();
          row = s.getRow(i);

          for (int j = ; j < row.length; j++)
          {
            // Remember that empty cells can contain format information
            if ((row[j].getType() != CellType.EMPTY||
                (row[j].getCellFormat() != null))
            {
              format = row[j].getCellFormat();
              bw.write("      <col number=\"" + j + "\">");
              bw.newLine();
              bw.write("        <data>");
              bw.write("<![CDATA["+row[j].getContents()+"]]>");
              bw.write("</data>");
              bw.newLine();     

              if (row[j].getCellFormat() != null)
              {
                bw.write("        <format wrap=\"" + format.getWrap() "\"");
                bw.newLine();
                bw.write("                align=\"" 
                         format.getAlignment().getDescription() "\"");
                bw.newLine();
                bw.write("                valign=\"" 
                         format.getVerticalAlignment().getDescription() "\"");
                bw.newLine();
                bw.write("                orientation=\"" 
                         format.getOrientation().getDescription() "\"");
                bw.write(">");
                bw.newLine();

                // The font information
                font = format.getFont();
                bw.write("          <font name=\"" + font.getName() "\"");
                bw.newLine();
                bw.write("                point_size=\"" 
                         font.getPointSize() "\"");
                bw.newLine();
                bw.write("                bold_weight=\"" 
                         font.getBoldWeight() "\"");
                bw.newLine();
                bw.write("                italic=\"" + font.isItalic() "\"");
                bw.newLine();
                bw.write("                underline=\"" 
                         font.getUnderlineStyle().getDescription() "\"");
                bw.newLine();
                bw.write("                colour=\"" 
                         font.getColour().getDescription() "\"");
                bw.newLine();
                bw.write("                script=\"" 
                         font.getScriptStyle().getDescription() "\"");
                bw.write(" />");
                bw.newLine();


                // The cell background information
                if (format.getBackgroundColour() != Colour.DEFAULT_BACKGROUND ||
                    format.getPattern()          != Pattern.NONE)
                {
                  bw.write("          <background colour=\"" 
                           format.getBackgroundColour().getDescription() "\"");
                  bw.newLine();
                  bw.write("                      pattern=\"" 
                           format.getPattern().getDescription() "\"");
                  bw.write(" />");
                  bw.newLine();
                }


                // The cell border, if it has one
                if (format.getBorder(Border.TOP  )  != BorderLineStyle.NONE ||
                    format.getBorder(Border.BOTTOM!= BorderLineStyle.NONE ||
                    format.getBorder(Border.LEFT)   != BorderLineStyle.NONE ||
                    format.getBorder(Border.RIGHT)  != BorderLineStyle.NONE)
                {
                  
                  bw.write("          <border top=\"" 
                           format.getBorder(Border.TOP).getDescription() "\"");
                  bw.newLine();
                  bw.write("                  bottom=\"" 
                           format.getBorder(Border.BOTTOM).getDescription() 
                           "\"");
                  bw.newLine();
                  bw.write("                  left=\"" 
                           format.getBorder(Border.LEFT).getDescription() "\"");
                  bw.newLine();
                  bw.write("                  right=\"" 
                           format.getBorder(Border.RIGHT).getDescription() "\"");
                  bw.write(" />");
                  bw.newLine();
                }

                // The cell number/date format
                if (!format.getFormat().getFormatString().equals(""))
                {
                  bw.write("          <format_string string=\"");
                  bw.write(format.getFormat().getFormatString());
                  bw.write("\" />");
                  bw.newLine();
                }

                bw.write("        </format>");
                bw.newLine();
              }

              bw.write("      </col>");
              bw.newLine();
            }
          }
          bw.write("    </row>");
          bw.newLine();
        }
        bw.write("  </sheet>");
        bw.newLine();
      }
      
      bw.write("</workbook>");
      bw.newLine();

      bw.flush();
      bw.close();
    }
    catch (UnsupportedEncodingException e)
    {
      System.err.println(e.toString());
    }
  }

}