write.java

JAVA读取excel表格里的数据,与jdbc功能相当,是一个包

/*********************************************************************
*
*      Copyright (C) 2001 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.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
import java.util.TimeZone;

import jxl.CellReferenceHelper;
import jxl.CellView;
import jxl.HeaderFooter;
import jxl.Range;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Blank;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


/**
 * Demo class which writes a spreadsheet.  This demo illustrates most of the
 * features of the JExcelAPI, such as text, numbers, fonts, number formats and
 * date formats
 */
public class Write
{
  /**
   * The filename
   */
  private String filename;

  /**
   * The workbook
   */
  private WritableWorkbook workbook;

  /**
   * Constructor
   * 
   * @param fn 
   */
  public Write(String fn)
  {
    filename = fn;
  }

  /**
   * Uses the JExcelAPI to create a spreadsheet
   * 
   * @exception IOException
   * @exception WriteException
   */
  public void write() throws IOException, WriteException
  {
    WorkbookSettings ws = new WorkbookSettings();
    ws.setLocale(new Locale("en", "EN"));
    workbook = Workbook.createWorkbook(new File(filename), ws);


    WritableSheet s2 = workbook.createSheet("Number Formats", 0);
    WritableSheet s3 = workbook.createSheet("Date Formats", 1);
    WritableSheet s1 = workbook.createSheet("Label Formats", 2);
    WritableSheet s4 = workbook.createSheet("Borders", 3);
    WritableSheet s5 = workbook.createSheet("Labels", 4);
    WritableSheet s6 = workbook.createSheet("Formulas", 5);
    WritableSheet s7 = workbook.createSheet("Images", 6);
    //    WritableSheet s8 = workbook.createSheet
    //      ("'Illegal chars in name !*%^?': which exceeds max name length",7);

    writeLabelFormatSheet(s1);
    writeNumberFormatSheet(s2);
    writeDateFormatSheet(s3);
    writeBordersSheet(s4);
    writeLabelsSheet(s5);
    writeFormulaSheet(s6);
    writeImageSheet(s7);

    // Modify the colour palette to bright red for the lime colour
    workbook.setColourRGB(Colour.LIME, 0xff, 0, 0);

    // Add a named range to the workbook
    workbook.addNameArea("namedrange", s4, 1, 11, 5, 14);

    workbook.write();
    workbook.close();
  }
  
  /**
   * Writes out a sheet containing the various numerical formats
   * 
   * @param s 
   */
  private void writeNumberFormatSheet(WritableSheet s) throws WriteException
  {
    WritableCellFormat wrappedText = new WritableCellFormat
      (WritableWorkbook.ARIAL_10_PT);
    wrappedText.setWrap(true);

    s.setColumnView(0,20);
    s.setColumnView(4,20);
    s.setColumnView(5,20);
    s.setColumnView(6,20);

    // Floats
    Label l = new Label(0,0,"+/- Pi - default format", wrappedText);
    s.addCell(l);

    Number n = new Number(1,0,3.1415926535);
    s.addCell(n);

    n = new Number(2,0,-3.1415926535);
    s.addCell(n);

    l = new Label(0,1,"+/- Pi - integer format", wrappedText);
    s.addCell(l);

    WritableCellFormat cf1 = new WritableCellFormat(NumberFormats.INTEGER);
    n = new Number(1,1,3.1415926535,cf1);
    s.addCell(n);

    n = new Number(2,1,-3.1415926535, cf1);
    s.addCell(n);

    l = new Label(0,2,"+/- Pi - float 2dps", wrappedText);
    s.addCell(l);

    WritableCellFormat cf2 = new WritableCellFormat(NumberFormats.FLOAT);
    n = new Number(1,2,3.1415926535,cf2);
    s.addCell(n);

    n = new Number(2,2,-3.1415926535, cf2);
    s.addCell(n);

    l = new Label(0,3,"+/- Pi - custom 3dps", 
                  wrappedText);
    s.addCell(l);

    NumberFormat dp3 = new NumberFormat("#.###");
    WritableCellFormat dp3cell = new WritableCellFormat(dp3);
    n = new Number(1,3,3.1415926535,dp3cell);
    s.addCell(n);

    n = new Number(2,3,-3.1415926535, dp3cell);
    s.addCell(n);

    l = new Label(0,4,"+/- Pi - custom &3.14", 
                  wrappedText);
    s.addCell(l);

    NumberFormat pounddp2 = new NumberFormat("&#.00");
    WritableCellFormat pounddp2cell = new WritableCellFormat(pounddp2);
    n = new Number(1,4,3.1415926535,pounddp2cell);
    s.addCell(n);

    n = new Number(2,4,-3.1415926535, pounddp2cell);
    s.addCell(n);

    l = new Label(0,5,"+/- Pi - custom Text #.### Text", 
                  wrappedText);
    s.addCell(l);

    NumberFormat textdp4 = new NumberFormat("Text#.####Text");
    WritableCellFormat textdp4cell = new WritableCellFormat(textdp4);
    n = new Number(1,5,3.1415926535, textdp4cell);
    s.addCell(n);

    n = new Number(2,5,-3.1415926535, textdp4cell);
    s.addCell(n);

    // Integers
    l = new Label(4,0,"+/- Bilko default format");
    s.addCell(l);
    n = new Number(5, 0, 15042699);
    s.addCell(n);
    n = new Number(6, 0, -15042699);
    s.addCell(n);

    l = new Label(4,1,"+/- Bilko float format");
    s.addCell(l);
    WritableCellFormat cfi1 = new WritableCellFormat(NumberFormats.FLOAT);
    n = new Number(5, 1, 15042699, cfi1);
    s.addCell(n);
    n = new Number(6, 1, -15042699, cfi1);
    s.addCell(n);

    l = new Label(4,2,"+/- Thousands separator");
    s.addCell(l);
    WritableCellFormat cfi2 = new WritableCellFormat
      (NumberFormats.THOUSANDS_INTEGER);
    n = new Number(5, 2, 15042699,cfi2 );
    s.addCell(n);
    n = new Number(6, 2, -15042699, cfi2);
    s.addCell(n);

    l = new Label(4,3,"+/- Accounting red - added 0.01");
    s.addCell(l);
    WritableCellFormat cfi3 = new WritableCellFormat
      (NumberFormats.ACCOUNTING_RED_FLOAT);
    n = new Number(5, 3, 15042699.01, cfi3);
    s.addCell(n);
    n = new Number(6, 3, -15042699.01, cfi3);
    s.addCell(n);

    l = new Label(4,4,"+/- Percent");
    s.addCell(l);
    WritableCellFormat cfi4 = new WritableCellFormat
      (NumberFormats.PERCENT_INTEGER);
    n = new Number(5, 4, 15042699, cfi4);
    s.addCell(n);
    n = new Number(6, 4, -15042699, cfi4);
    s.addCell(n);

    l = new Label(4,5,"+/- Exponential - 2dps");
    s.addCell(l);
    WritableCellFormat cfi5 = new WritableCellFormat
      (NumberFormats.EXPONENTIAL);
    n = new Number(5, 5, 15042699, cfi5);
    s.addCell(n);
    n = new Number(6, 5, -15042699, cfi5);
    s.addCell(n);

    l = new Label(4,6,"+/- Custom exponentional - 3dps", wrappedText);
    s.addCell(l);
    NumberFormat edp3 = new NumberFormat("0.000E0");
    WritableCellFormat edp3Cell = new WritableCellFormat(edp3);
    n = new Number(5,6,15042699,edp3Cell);
    s.addCell(n);
    n = new Number(6,6,-15042699,edp3Cell);
    s.addCell(n);

    l = new Label(4, 7, "Custom neg brackets", wrappedText);
    s.addCell(l);
    NumberFormat negbracks = new NumberFormat("#,##0;(#,##0)");
    WritableCellFormat negbrackscell = new WritableCellFormat(negbracks);
    n = new Number(5,7, 15042699, negbrackscell);
    s.addCell(n);
    n = new Number(6,7, -15042699, negbrackscell);
    s.addCell(n);

    l = new Label(4, 8, "Custom neg brackets 2", wrappedText);
    s.addCell(l);
    NumberFormat negbracks2 = new NumberFormat("#,##0;(#,##0)a");
    WritableCellFormat negbrackscell2 = new WritableCellFormat(negbracks2);
    n = new Number(5,8, 15042699, negbrackscell2);
    s.addCell(n);
    n = new Number(6,8, -15042699, negbrackscell2);
    s.addCell(n);

    l = new Label(4, 9, "Custom percent", wrappedText);
    s.addCell(l);
    NumberFormat cuspercent = new NumberFormat("0.0%");
    WritableCellFormat cuspercentf = new WritableCellFormat(cuspercent);
    n = new Number(5, 9, 3.14159265, cuspercentf);
    s.addCell(n);
    

    // Booleans
    l = new Label(0,10, "Boolean - TRUE");
    s.addCell(l);
    Boolean b = new Boolean(1,10, true);
    s.addCell(b);

    l = new Label(0,11, "Boolean - FALSE");
    s.addCell(l);
    b = new Boolean(1,11,false);
    s.addCell(b);

    l = new Label(0, 12, "A hidden cell->");
    s.addCell(l);
    n = new Number(1, 12, 17, WritableWorkbook.HIDDEN_STYLE);
    s.addCell(n);

    // Currencies
    l = new Label(4, 19, "Currency formats");
    s.addCell(l);

    l = new Label(4, 21, "UK Pound");
    s.addCell(l);
    NumberFormat poundCurrency = 
      new NumberFormat(NumberFormat.CURRENCY_POUND + " #,###.00", 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat poundFormat = new WritableCellFormat(poundCurrency);
    n = new Number(5, 21, 12345, poundFormat);
    s.addCell(n);

    l = new Label(4, 22, "Euro 1");
    s.addCell(l);
    NumberFormat euroPrefixCurrency = 
      new NumberFormat(NumberFormat.CURRENCY_EURO_PREFIX + " #,###.00", 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat euroPrefixFormat = 
      new WritableCellFormat(euroPrefixCurrency);
    n = new Number(5, 22, 12345, euroPrefixFormat);
    s.addCell(n);

    l = new Label(4, 23, "Euro 2");
    s.addCell(l);
    NumberFormat euroSuffixCurrency = 
      new NumberFormat("#,###.00" + NumberFormat.CURRENCY_EURO_SUFFIX, 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat euroSuffixFormat = 
      new WritableCellFormat(euroSuffixCurrency);
    n = new Number(5, 23, 12345, euroSuffixFormat);
    s.addCell(n);

    l = new Label(4, 24, "Dollar");
    s.addCell(l);
    NumberFormat dollarCurrency = 
      new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " #,###.00", 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat dollarFormat = 
      new WritableCellFormat(dollarCurrency);
    n = new Number(5, 24, 12345, dollarFormat);
    s.addCell(n);

    l = new Label(4, 25, "Japanese Yen");
    s.addCell(l);
    NumberFormat japaneseYenCurrency = 
      new NumberFormat(NumberFormat.CURRENCY_JAPANESE_YEN + " #,###.00", 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat japaneseYenFormat = 
      new WritableCellFormat(japaneseYenCurrency);
    n = new Number(5, 25, 12345, japaneseYenFormat);
    s.addCell(n);

    l = new Label(4, 30, "Fraction formats");
    s.addCell(l);

    l = new Label(4,32, "One digit fraction format", wrappedText);
    s.addCell(l);

    WritableCellFormat fraction1digitformat = 
      new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
    n = new Number(5, 32, 3.18279, fraction1digitformat);
    s.addCell(n);

    l = new Label(4,33, "Two digit fraction format", wrappedText);
    s.addCell(l);

    WritableCellFormat fraction2digitformat = 
      new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
    n = new Number(5, 33, 3.18279, fraction2digitformat);
    s.addCell(n);

    l = new Label(4,34, "Three digit fraction format (improper)", wrappedText);
    s.addCell(l);

    NumberFormat fraction3digit1 = 
      new NumberFormat(NumberFormat.FRACTION_THREE_DIGITS, 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat fraction3digitformat1 = 
      new WritableCellFormat(fraction3digit1);
    n = new Number(5, 34, 3.18927, fraction3digitformat1);
    s.addCell(n);

    l = new Label(4,35, "Three digit fraction format (proper)", wrappedText);
    s.addCell(l);

    NumberFormat fraction3digit2 = 
      new NumberFormat("# " + NumberFormat.FRACTION_THREE_DIGITS, 
                       NumberFormat.COMPLEX_FORMAT);
    WritableCellFormat fraction3digitformat2 = 
      new WritableCellFormat(fraction3digit2);
    n = new Number(5, 35, 3.18927, fraction3digitformat2);