正在查看： Harvest v2.32 应用的 Write.java JAVA 源代码文件
本页面展示 JAVA 反编译生成的源代码文件，支持语法高亮显示。 仅供安全研究与技术分析使用，严禁用于任何非法用途。请遵守相关法律法规。
页面标题
页面副标题
Harvest v2.32 - Write.java 源代码
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.PageOrder;
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.WritableCell;
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;
public class Write {
private String filename;
private WritableWorkbook workbook;
public Write(String str) {
this.filename = str;
}
public void write() throws IOException, WriteException {
WorkbookSettings workbookSettings = new WorkbookSettings();
workbookSettings.setLocale(new Locale("en", "EN"));
WritableWorkbook createWorkbook = Workbook.createWorkbook(new File(this.filename), workbookSettings);
this.workbook = createWorkbook;
WritableSheet createSheet = createWorkbook.createSheet("Number Formats", 0);
WritableSheet createSheet2 = this.workbook.createSheet("Date Formats", 1);
WritableSheet createSheet3 = this.workbook.createSheet("Label Formats", 2);
WritableSheet createSheet4 = this.workbook.createSheet("Borders", 3);
WritableSheet createSheet5 = this.workbook.createSheet("Labels", 4);
WritableSheet createSheet6 = this.workbook.createSheet("Formulas", 5);
WritableSheet createSheet7 = this.workbook.createSheet("Images", 6);
this.workbook.setColourRGB(Colour.LIME, 255, 0, 0);
this.workbook.addNameArea("namedrange", createSheet4, 1, 11, 5, 14);
this.workbook.addNameArea("validation_range", createSheet3, 4, 65, 9, 65);
this.workbook.addNameArea("formulavalue", createSheet6, 1, 45, 1, 45);
createSheet5.getSettings().setPrintArea(4, 4, 15, 35);
writeLabelFormatSheet(createSheet3);
writeNumberFormatSheet(createSheet);
writeDateFormatSheet(createSheet2);
writeBordersSheet(createSheet4);
writeLabelsSheet(createSheet5);
writeFormulaSheet(createSheet6);
writeImageSheet(createSheet7);
this.workbook.write();
this.workbook.close();
}
private void writeNumberFormatSheet(WritableSheet writableSheet) throws WriteException {
WritableCellFormat writableCellFormat = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
writableCellFormat.setWrap(true);
writableSheet.setColumnView(0, 20);
writableSheet.setColumnView(4, 20);
writableSheet.setColumnView(5, 20);
writableSheet.setColumnView(6, 20);
writableSheet.addCell(new Label(0, 0, "+/- Pi - default format", writableCellFormat));
writableSheet.addCell(new Number(1, 0, 3.1415926535d));
writableSheet.addCell(new Number(2, 0, -3.1415926535d));
writableSheet.addCell(new Label(0, 1, "+/- Pi - integer format", writableCellFormat));
WritableCellFormat writableCellFormat2 = new WritableCellFormat(NumberFormats.INTEGER);
writableSheet.addCell(new Number(1, 1, 3.1415926535d, writableCellFormat2));
writableSheet.addCell(new Number(2, 1, -3.1415926535d, writableCellFormat2));
writableSheet.addCell(new Label(0, 2, "+/- Pi - float 2dps", writableCellFormat));
WritableCellFormat writableCellFormat3 = new WritableCellFormat(NumberFormats.FLOAT);
writableSheet.addCell(new Number(1, 2, 3.1415926535d, writableCellFormat3));
writableSheet.addCell(new Number(2, 2, -3.1415926535d, writableCellFormat3));
writableSheet.addCell(new Label(0, 3, "+/- Pi - custom 3dps", writableCellFormat));
WritableCellFormat writableCellFormat4 = new WritableCellFormat(new NumberFormat("#.###"));
writableSheet.addCell(new Number(1, 3, 3.1415926535d, writableCellFormat4));
writableSheet.addCell(new Number(2, 3, -3.1415926535d, writableCellFormat4));
writableSheet.addCell(new Label(0, 4, "+/- Pi - custom &3.14", writableCellFormat));
WritableCellFormat writableCellFormat5 = new WritableCellFormat(new NumberFormat("&#.00"));
writableSheet.addCell(new Number(1, 4, 3.1415926535d, writableCellFormat5));
writableSheet.addCell(new Number(2, 4, -3.1415926535d, writableCellFormat5));
writableSheet.addCell(new Label(0, 5, "+/- Pi - custom Text #.### Text", writableCellFormat));
WritableCellFormat writableCellFormat6 = new WritableCellFormat(new NumberFormat("Text#.####Text"));
writableSheet.addCell(new Number(1, 5, 3.1415926535d, writableCellFormat6));
writableSheet.addCell(new Number(2, 5, -3.1415926535d, writableCellFormat6));
writableSheet.addCell(new Label(4, 0, "+/- Bilko default format"));
writableSheet.addCell(new Number(5, 0, 1.5042699E7d));
writableSheet.addCell(new Number(6, 0, -1.5042699E7d));
writableSheet.addCell(new Label(4, 1, "+/- Bilko float format"));
WritableCellFormat writableCellFormat7 = new WritableCellFormat(NumberFormats.FLOAT);
writableSheet.addCell(new Number(5, 1, 1.5042699E7d, writableCellFormat7));
writableSheet.addCell(new Number(6, 1, -1.5042699E7d, writableCellFormat7));
writableSheet.addCell(new Label(4, 2, "+/- Thousands separator"));
WritableCellFormat writableCellFormat8 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
writableSheet.addCell(new Number(5, 2, 1.5042699E7d, writableCellFormat8));
writableSheet.addCell(new Number(6, 2, -1.5042699E7d, writableCellFormat8));
writableSheet.addCell(new Label(4, 3, "+/- Accounting red - added 0.01"));
WritableCellFormat writableCellFormat9 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
writableSheet.addCell(new Number(5, 3, 1.504269901E7d, writableCellFormat9));
writableSheet.addCell(new Number(6, 3, -1.504269901E7d, writableCellFormat9));
writableSheet.addCell(new Label(4, 4, "+/- Percent"));
WritableCellFormat writableCellFormat10 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
writableSheet.addCell(new Number(5, 4, 1.5042699E7d, writableCellFormat10));
writableSheet.addCell(new Number(6, 4, -1.5042699E7d, writableCellFormat10));
writableSheet.addCell(new Label(4, 5, "+/- Exponential - 2dps"));
WritableCellFormat writableCellFormat11 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
writableSheet.addCell(new Number(5, 5, 1.5042699E7d, writableCellFormat11));
writableSheet.addCell(new Number(6, 5, -1.5042699E7d, writableCellFormat11));
writableSheet.addCell(new Label(4, 6, "+/- Custom exponentional - 3dps", writableCellFormat));
WritableCellFormat writableCellFormat12 = new WritableCellFormat(new NumberFormat("0.000E0"));
writableSheet.addCell(new Number(5, 6, 1.5042699E7d, writableCellFormat12));
writableSheet.addCell(new Number(6, 6, -1.5042699E7d, writableCellFormat12));
writableSheet.addCell(new Label(4, 7, "Custom neg brackets", writableCellFormat));
WritableCellFormat writableCellFormat13 = new WritableCellFormat(new NumberFormat("#,##0;(#,##0)"));
writableSheet.addCell(new Number(5, 7, 1.5042699E7d, writableCellFormat13));
writableSheet.addCell(new Number(6, 7, -1.5042699E7d, writableCellFormat13));
writableSheet.addCell(new Label(4, 8, "Custom neg brackets 2", writableCellFormat));
WritableCellFormat writableCellFormat14 = new WritableCellFormat(new NumberFormat("#,##0;(#,##0)a"));
writableSheet.addCell(new Number(5, 8, 1.5042699E7d, writableCellFormat14));
writableSheet.addCell(new Number(6, 8, -1.5042699E7d, writableCellFormat14));
writableSheet.addCell(new Label(4, 9, "Custom percent", writableCellFormat));
writableSheet.addCell(new Number(5, 9, 3.14159265d, new WritableCellFormat(new NumberFormat("0.0%"))));
writableSheet.addCell(new Label(0, 10, "Boolean - TRUE"));
writableSheet.addCell(new Boolean(1, 10, true));
writableSheet.addCell(new Label(0, 11, "Boolean - FALSE"));
writableSheet.addCell(new Boolean(1, 11, false));
writableSheet.addCell(new Label(0, 12, "A hidden cell->"));
writableSheet.addCell(new Number(1, 12, 17.0d, WritableWorkbook.HIDDEN_STYLE));
writableSheet.addCell(new Label(4, 19, "Currency formats"));
writableSheet.addCell(new Label(4, 21, "UK Pound"));
writableSheet.addCell(new Number(5, 21, 12345.0d, new WritableCellFormat(new NumberFormat("� #,###.00", NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 22, "Euro 1"));
writableSheet.addCell(new Number(5, 22, 12345.0d, new WritableCellFormat(new NumberFormat("[$�-2] #,###.00", NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 23, "Euro 2"));
writableSheet.addCell(new Number(5, 23, 12345.0d, new WritableCellFormat(new NumberFormat("#,###.00[$�-1]", NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 24, "Dollar"));
writableSheet.addCell(new Number(5, 24, 12345.0d, new WritableCellFormat(new NumberFormat("[$$-409] #,###.00", NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 25, "Japanese Yen"));
writableSheet.addCell(new Number(5, 25, 12345.0d, new WritableCellFormat(new NumberFormat("[$�-411] #,###.00", NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 30, "Fraction formats"));
writableSheet.addCell(new Label(4, 32, "One digit fraction format", writableCellFormat));
writableSheet.addCell(new Number(5, 32, 3.18279d, new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT)));
writableSheet.addCell(new Label(4, 33, "Two digit fraction format", writableCellFormat));
writableSheet.addCell(new Number(5, 33, 3.18279d, new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS)));
writableSheet.addCell(new Label(4, 34, "Three digit fraction format (improper)", writableCellFormat));
writableSheet.addCell(new Number(5, 34, 3.18927d, new WritableCellFormat(new NumberFormat(NumberFormat.FRACTION_THREE_DIGITS, NumberFormat.COMPLEX_FORMAT))));
writableSheet.addCell(new Label(4, 35, "Three digit fraction format (proper)", writableCellFormat));
writableSheet.addCell(new Number(5, 35, 3.18927d, new WritableCellFormat(new NumberFormat("# ???/???", NumberFormat.COMPLEX_FORMAT))));
for (int i = 0; i < 100; i++) {
for (int i2 = 8; i2 < 108; i2++) {
writableSheet.addCell(new Number(i2, i, i2 + i));
}
}
for (int i3 = 101; i3 < 3000; i3++) {
for (int i4 = 0; i4 < 25; i4++) {
writableSheet.addCell(new Number(i4, i3, i4 + i3));
}
}
}
private void writeDateFormatSheet(WritableSheet writableSheet) throws WriteException {
WritableCellFormat writableCellFormat = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
writableCellFormat.setWrap(true);
writableSheet.setColumnView(0, 20);
writableSheet.setColumnView(2, 20);
writableSheet.setColumnView(3, 20);
writableSheet.setColumnView(4, 20);
writableSheet.getSettings().setFitWidth(2);
writableSheet.getSettings().setFitHeight(2);
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
calendar.set(1975, 4, 31, 15, 21, 45);
calendar.set(14, 660);
Date time = calendar.getTime();
calendar.set(1900, 0, 1, 0, 0, 0);
calendar.set(14, 0);
Date time2 = calendar.getTime();
calendar.set(1970, 0, 1, 0, 0, 0);
Date time3 = calendar.getTime();
calendar.set(1918, 10, 11, 11, 0, 0);
Date time4 = calendar.getTime();
calendar.set(1900, 0, 2, 0, 0, 0);
Date time5 = calendar.getTime();
calendar.set(1901, 0, 1, 0, 0, 0);
Date time6 = calendar.getTime();
calendar.set(1900, 4, 31, 0, 0, 0);
Date time7 = calendar.getTime();
calendar.set(1900, 1, 1, 0, 0, 0);
Date time8 = calendar.getTime();
calendar.set(1900, 0, 31, 0, 0, 0);
Date time9 = calendar.getTime();
calendar.set(1900, 2, 1, 0, 0, 0);
Date time10 = calendar.getTime();
calendar.set(1900, 1, 27, 0, 0, 0);
Date time11 = calendar.getTime();
calendar.set(1900, 1, 28, 0, 0, 0);
Date time12 = calendar.getTime();
calendar.set(1980, 5, 31, 12, 0, 0);
Date time13 = calendar.getTime();
calendar.set(1066, 9, 14, 0, 0, 0);
Date time14 = calendar.getTime();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd MMM yyyy HH:mm:ss.SSS");
simpleDateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
writableSheet.addCell(new Label(0, 0, "All dates are " + simpleDateFormat.format(time), writableCellFormat));
writableSheet.addCell(new Label(0, 1, "Built in formats", writableCellFormat));
writableSheet.addCell(new Label(2, 1, "Custom formats"));
writableSheet.addCell(new DateTime(0, 2, time, new WritableCellFormat(DateFormats.FORMAT1), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 3, time, new WritableCellFormat(DateFormats.FORMAT2), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 4, time, new WritableCellFormat(DateFormats.FORMAT3)));
writableSheet.addCell(new DateTime(0, 5, time, new WritableCellFormat(DateFormats.FORMAT4)));
writableSheet.addCell(new DateTime(0, 6, time, new WritableCellFormat(DateFormats.FORMAT5)));
writableSheet.addCell(new DateTime(0, 7, time, new WritableCellFormat(DateFormats.FORMAT6)));
writableSheet.addCell(new DateTime(0, 8, time, new WritableCellFormat(DateFormats.FORMAT7), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 9, time, new WritableCellFormat(DateFormats.FORMAT8), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 10, time, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 11, time, new WritableCellFormat(DateFormats.FORMAT10), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 12, time, new WritableCellFormat(DateFormats.FORMAT11), DateTime.GMT));
writableSheet.addCell(new DateTime(0, 13, time, new WritableCellFormat(DateFormats.FORMAT12), DateTime.GMT));
WritableCellFormat writableCellFormat2 = new WritableCellFormat(new DateFormat("dd MM yyyy"));
writableSheet.addCell(new Label(2, 2, "dd MM yyyy"));
writableSheet.addCell(new DateTime(3, 2, time, writableCellFormat2, DateTime.GMT));
WritableCellFormat writableCellFormat3 = new WritableCellFormat(new DateFormat("dd MMM yyyy"));
writableSheet.addCell(new Label(2, 3, "dd MMM yyyy"));
writableSheet.addCell(new DateTime(3, 3, time, writableCellFormat3, DateTime.GMT));
WritableCellFormat writableCellFormat4 = new WritableCellFormat(new DateFormat("hh:mm"));
writableSheet.addCell(new Label(2, 4, "hh:mm"));
writableSheet.addCell(new DateTime(3, 4, time, writableCellFormat4, DateTime.GMT));
WritableCellFormat writableCellFormat5 = new WritableCellFormat(new DateFormat("hh:mm:ss"));
writableSheet.addCell(new Label(2, 5, "hh:mm:ss"));
writableSheet.addCell(new DateTime(3, 5, time, writableCellFormat5, DateTime.GMT));
WritableCellFormat writableCellFormat6 = new WritableCellFormat(new DateFormat("H:mm:ss a"));
writableSheet.addCell(new Label(2, 5, "H:mm:ss a"));
writableSheet.addCell(new DateTime(3, 5, time, writableCellFormat6, DateTime.GMT));
writableSheet.addCell(new DateTime(4, 5, time13, writableCellFormat6, DateTime.GMT));
WritableCellFormat writableCellFormat7 = new WritableCellFormat(new DateFormat("mm:ss.SSS"));
writableSheet.addCell(new Label(2, 6, "mm:ss.SSS"));
writableSheet.addCell(new DateTime(3, 6, time, writableCellFormat7, DateTime.GMT));
WritableCellFormat writableCellFormat8 = new WritableCellFormat(new DateFormat("hh:mm:ss a"));
writableSheet.addCell(new Label(2, 7, "hh:mm:ss a"));
writableSheet.addCell(new DateTime(4, 7, time13, writableCellFormat8, DateTime.GMT));
writableSheet.addCell(new Label(0, 16, "Zero date " + simpleDateFormat.format(time2), writableCellFormat));
writableSheet.addCell(new DateTime(0, 17, time2, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 16, "Zero date + 1 " + simpleDateFormat.format(time5), writableCellFormat));
writableSheet.addCell(new DateTime(3, 17, time5, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 19, simpleDateFormat.format(time6), writableCellFormat));
writableSheet.addCell(new DateTime(3, 20, time6, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 22, simpleDateFormat.format(time7), writableCellFormat));
writableSheet.addCell(new DateTime(3, 23, time7, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 25, simpleDateFormat.format(time8), writableCellFormat));
writableSheet.addCell(new DateTime(3, 26, time8, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 28, simpleDateFormat.format(time9), writableCellFormat));
writableSheet.addCell(new DateTime(3, 29, time9, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 28, simpleDateFormat.format(time9), writableCellFormat));
writableSheet.addCell(new DateTime(3, 29, time9, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 31, simpleDateFormat.format(time10), writableCellFormat));
writableSheet.addCell(new DateTime(3, 32, time10, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 34, simpleDateFormat.format(time11), writableCellFormat));
writableSheet.addCell(new DateTime(3, 35, time11, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(3, 37, simpleDateFormat.format(time12), writableCellFormat));
writableSheet.addCell(new DateTime(3, 38, time12, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(0, 19, "Zero UTC date " + simpleDateFormat.format(time3), writableCellFormat));
writableSheet.addCell(new DateTime(0, 20, time3, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(0, 22, "Armistice date " + simpleDateFormat.format(time4), writableCellFormat));
writableSheet.addCell(new DateTime(0, 23, time4, new WritableCellFormat(DateFormats.FORMAT9), DateTime.GMT));
writableSheet.addCell(new Label(0, 25, "Battle of Hastings " + simpleDateFormat.format(time14), writableCellFormat));
writableSheet.addCell(new DateTime(0, 26, time14, new WritableCellFormat(DateFormats.FORMAT2), DateTime.GMT));
}
private void writeLabelFormatSheet(WritableSheet writableSheet) throws WriteException {
WritableFont writableFont;
WritableCellFormat writableCellFormat;
int i;
int i2;
writableSheet.setColumnView(0, 60);
writableSheet.addCell(new Label(0, 0, "Arial Fonts"));
writableSheet.addCell(new Label(1, 0, "10pt"));
writableSheet.addCell(new Label(2, 0, "Normal"));
writableSheet.addCell(new Label(3, 0, "12pt"));
WritableCellFormat writableCellFormat2 = new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 12));
writableCellFormat2.setWrap(true);
writableSheet.addCell(new Label(4, 0, "Normal", writableCellFormat2));
writableSheet.addCell(new Label(2, 2, "BOLD", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD))));
writableSheet.addCell(new Label(4, 2, "BOLD", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD))));
writableSheet.addCell(new Label(2, 4, "Italic", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true))));
writableSheet.addCell(new Label(4, 4, "Italic", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true))));
WritableCellFormat writableCellFormat3 = new WritableCellFormat(new WritableFont(WritableFont.TIMES, 10));
writableSheet.addCell(new Label(0, 7, "Times Fonts", writableCellFormat3));
writableSheet.addCell(new Label(1, 7, "10pt", writableCellFormat3));
writableSheet.addCell(new Label(2, 7, "Normal", writableCellFormat3));
writableSheet.addCell(new Label(3, 7, "12pt", writableCellFormat3));
WritableFont writableFont2 = new WritableFont(WritableFont.TIMES, 12);
writableSheet.addCell(new Label(4, 7, "Normal", new WritableCellFormat(writableFont2)));
writableSheet.addCell(new Label(2, 9, "BOLD", new WritableCellFormat(new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD))));
writableSheet.addCell(new Label(4, 9, "BOLD", new WritableCellFormat(new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD))));
writableSheet.setColumnView(6, 22);
writableSheet.setColumnView(7, 22);
writableSheet.setColumnView(8, 22);
writableSheet.setColumnView(9, 22);
writableSheet.addCell(new Label(0, 11, "Underlining"));
writableSheet.addCell(new Label(6, 11, "Underline", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE))));
writableSheet.addCell(new Label(7, 11, "Double Underline", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE))));
writableSheet.addCell(new Label(8, 11, "Single Accounting Underline", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE_ACCOUNTING))));
writableSheet.addCell(new Label(9, 11, "Double Accounting Underline", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.DOUBLE_ACCOUNTING))));
WritableCellFormat writableCellFormat4 = new WritableCellFormat(new WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false, UnderlineStyle.SINGLE));
writableSheet.addCell(new Label(6, 12, "Times 14 Bold Underline", writableCellFormat4));
writableSheet.addCell(new Label(6, 13, "Arial 18 Bold Italic Underline", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, true, UnderlineStyle.SINGLE))));
writableSheet.addCell(new Label(0, 15, "Script styles"));
writableSheet.addCell(new Label(1, 15, "superscript", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUPERSCRIPT))));
writableSheet.addCell(new Label(2, 15, "subscript", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK, ScriptStyle.SUBSCRIPT))));
writableSheet.addCell(new Label(0, 17, "Colours"));
writableSheet.addCell(new Label(2, 17, "Red", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED))));
writableSheet.addCell(new Label(2, 18, "Blue", new WritableCellFormat(new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLUE))));
WritableFont writableFont3 = new WritableFont(WritableFont.ARIAL);
writableFont3.setColour(Colour.LIME);
WritableCellFormat writableCellFormat5 = new WritableCellFormat(writableFont3);
writableCellFormat5.setWrap(true);
writableSheet.addCell(new Label(4, 18, "Modified palette - was lime, now red", writableCellFormat5));
WritableCellFormat writableCellFormat6 = new WritableCellFormat();
writableCellFormat6.setWrap(true);
writableCellFormat6.setBackground(Colour.GRAY_50);
writableSheet.addCell(new Label(2, 19, "Grey background", writableCellFormat6));
WritableFont writableFont4 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.YELLOW);
WritableCellFormat writableCellFormat7 = new WritableCellFormat(writableFont4);
writableCellFormat7.setWrap(true);
writableCellFormat7.setBackground(Colour.BLUE);
writableSheet.addCell(new Label(2, 20, "Blue background, yellow foreground", writableCellFormat7));
WritableCellFormat writableCellFormat8 = new WritableCellFormat(writableFont4);
writableCellFormat8.setWrap(true);
writableCellFormat8.setBackground(Colour.PALETTE_BLACK);
writableSheet.addCell(new Label(3, 20, "Black background, yellow foreground", writableCellFormat8));
writableSheet.addCell(new Label(0, 22, "Null label"));
writableSheet.addCell(new Label(2, 22, (String) null));
writableSheet.addCell(new Label(0, 24, "A very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nA very long label, more than 255 characters\nRejoice O shores\nSing O bells\nBut I with mournful tread\nWalk the deck my captain lies\nFallen cold and dead\nSummer surprised, coming over the Starnbergersee\nWith a shower of rain. We stopped in the Colonnade\nAnd sat and drank coffee an talked for an hour\n", writableCellFormat2));
WritableCellFormat writableCellFormat9 = new WritableCellFormat();
writableCellFormat9.setOrientation(Orientation.VERTICAL);
writableSheet.addCell(new Label(0, 26, "Vertical orientation", writableCellFormat9));
WritableCellFormat writableCellFormat10 = new WritableCellFormat();
writableCellFormat10.setOrientation(Orientation.PLUS_90);
writableSheet.addCell(new Label(1, 26, "Plus 90", writableCellFormat10));
WritableCellFormat writableCellFormat11 = new WritableCellFormat();
writableCellFormat11.setOrientation(Orientation.MINUS_90);
writableSheet.addCell(new Label(2, 26, "Minus 90", writableCellFormat11));
writableSheet.addCell(new Label(0, 28, "Modified row height"));
writableSheet.setRowView(28, 480);
writableSheet.addCell(new Label(0, 29, "Collapsed row"));
writableSheet.setRowView(29, true);
try {
writableSheet.addCell(new Label(0, 30, "Hyperlink to home page"));
URL url = new URL("http://www.andykhan.com/jexcelapi");
writableSheet.addHyperlink(new WritableHyperlink(0, 30, 8, 31, url));
writableSheet.addHyperlink(new WritableHyperlink(7, 30, 9, 31, url));
writableSheet.addCell(new Label(4, 2, "File hyperlink to documentation"));
writableSheet.addHyperlink(new WritableHyperlink(0, 32, 8, 32, new File("../jexcelapi/docs/index.html"), "JExcelApi Documentation"));
i2 = 8;
writableFont = writableFont2;
i = 10;
writableCellFormat = writableCellFormat2;
} catch (MalformedURLException e) {
e = e;
writableFont = writableFont2;
writableCellFormat = writableCellFormat2;
i = 10;
i2 = 8;
}
try {
writableSheet.addHyperlink(new WritableHyperlink(0, 34, 8, 34, "Link to another cell", writableSheet, 0, 180, 1, 181));
writableSheet.addHyperlink(new WritableHyperlink(0, 36, 8, 36, new File("\\\\localhost\\file.txt")));
writableSheet.addHyperlink(new WritableHyperlink(0, 38, 0, 38, new URL("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664")));
} catch (MalformedURLException e2) {
e = e2;
System.err.println(e.toString());
WritableCell label = new Label(5, 35, "Merged cells", writableCellFormat4);
writableSheet.mergeCells(5, 35, i2, 37);
writableSheet.addCell(label);
writableSheet.addCell(new Label(5, 38, "More merged cells"));
Range mergeCells = writableSheet.mergeCells(5, 38, i2, 41);
writableSheet.insertRow(40);
writableSheet.removeRow(39);
writableSheet.unmergeCells(mergeCells);
WritableCellFormat writableCellFormat12 = new WritableCellFormat();
writableCellFormat12.setAlignment(Alignment.CENTRE);
writableSheet.addCell(new Label(5, 42, "Centred across merged cells", writableCellFormat12));
writableSheet.mergeCells(5, 42, i, 42);
WritableCellFormat writableCellFormat13 = new WritableCellFormat();
writableCellFormat13.setBorder(Border.ALL, BorderLineStyle.THIN);
writableCellFormat13.setBackground(Colour.GRAY_25);
writableSheet.addCell(new Label(3, 44, "Merged with border", writableCellFormat13));
writableSheet.mergeCells(3, 44, 4, 46);
writableSheet.addCell(new Label(0, 49, "Courier fonts", new WritableCellFormat(new WritableFont(WritableFont.COURIER, i))));
writableSheet.addCell(new Label(0, 50, "Tahoma fonts", new WritableCellFormat(new WritableFont(WritableFont.TAHOMA, 12))));
writableSheet.addCell(new Label(0, 51, "Bespoke Windgdings 2", new WritableCellFormat(new WritableFont(WritableFont.createFont("Wingdings 2"), i))));
WritableFont writableFont5 = writableFont;
WritableCellFormat writableCellFormat14 = new WritableCellFormat(writableFont5);
writableCellFormat14.setShrinkToFit(true);
writableSheet.addCell(new Label(3, 53, "Shrunk to fit", writableCellFormat14));
writableSheet.addCell(new Label(3, 55, "Some long wrapped text in a merged cell", writableCellFormat));
writableSheet.mergeCells(3, 55, 4, 55);
WritableCell label2 = new Label(0, 57, "A cell with a comment");
WritableCellFeatures writableCellFeatures = new WritableCellFeatures();
writableCellFeatures.setComment("the cell comment");
label2.setCellFeatures(writableCellFeatures);
writableSheet.addCell(label2);
WritableCell label3 = new Label(0, 59, "A cell with a long comment");
WritableCellFeatures writableCellFeatures2 = new WritableCellFeatures();
writableCellFeatures2.setComment("a very long cell comment indeed that won't fit inside a standard comment box, so a larger comment box is used instead", 5.0d, 6.0d);
label3.setCellFeatures(writableCellFeatures2);
writableSheet.addCell(label3);
WritableCellFormat writableCellFormat15 = new WritableCellFormat(writableFont5);
writableCellFormat15.setIndentation(4);
writableSheet.addCell(new Label(0, 61, "Some indented text", writableCellFormat15));
writableSheet.addCell(new Label(0, 63, "Data validation: list"));
WritableCell blank = new Blank(1, 63);
WritableCellFeatures writableCellFeatures3 = new WritableCellFeatures();
ArrayList arrayList = new ArrayList();
arrayList.add("bagpuss");
arrayList.add("clangers");
arrayList.add("ivor the engine");
arrayList.add("noggin the nog");
writableCellFeatures3.setDataValidationList(arrayList);
blank.setCellFeatures(writableCellFeatures3);
writableSheet.addCell(blank);
writableSheet.addCell(new Label(0, 64, "Data validation: number > 4.5"));
WritableCell blank2 = new Blank(1, 64);
WritableCellFeatures writableCellFeatures4 = new WritableCellFeatures();
writableCellFeatures4.setNumberValidation(4.5d, WritableCellFeatures.GREATER_THAN);
blank2.setCellFeatures(writableCellFeatures4);
writableSheet.addCell(blank2);
writableSheet.addCell(new Label(0, 65, "Data validation: named range"));
writableSheet.addCell(new Label(4, 65, "tiger"));
writableSheet.addCell(new Label(5, 65, "sword"));
writableSheet.addCell(new Label(6, 65, "honour"));
writableSheet.addCell(new Label(7, 65, "company"));
writableSheet.addCell(new Label(i2, 65, "victory"));
writableSheet.addCell(new Label(9, 65, "fortress"));
WritableCell blank3 = new Blank(1, 65);
WritableCellFeatures writableCellFeatures5 = new WritableCellFeatures();
writableCellFeatures5.setDataValidationRange("validation_range");
blank3.setCellFeatures(writableCellFeatures5);
writableSheet.addCell(blank3);
writableSheet.setRowGroup(39, 45, false);
writableSheet.addCell(new Label(0, 66, "Block of cells B67-F71 with data validation"));
ArrayList arrayList2 = new ArrayList();
arrayList2.add("Achilles");
arrayList2.add("Agamemnon");
arrayList2.add("Hector");
arrayList2.add("Odysseus");
arrayList2.add("Patroclus");
arrayList2.add("Nestor");
WritableCell blank4 = new Blank(1, 66);
WritableCellFeatures writableCellFeatures6 = new WritableCellFeatures();
writableCellFeatures6.setDataValidationList(arrayList2);
blank4.setCellFeatures(writableCellFeatures6);
writableSheet.addCell(blank4);
writableSheet.applySharedDataValidation(blank4, 4, 4);
WritableCellFeatures writableCellFeatures7 = new WritableCellFeatures();
writableCellFeatures7.setDataValidationRange("");
WritableCell label4 = new Label(0, 71, "Read only cell using empty data validation");
label4.setCellFeatures(writableCellFeatures7);
writableSheet.addCell(label4);
writableSheet.setRowGroup(39, 45, false);
}
WritableCell label5 = new Label(5, 35, "Merged cells", writableCellFormat4);
writableSheet.mergeCells(5, 35, i2, 37);
writableSheet.addCell(label5);
writableSheet.addCell(new Label(5, 38, "More merged cells"));
Range mergeCells2 = writableSheet.mergeCells(5, 38, i2, 41);
writableSheet.insertRow(40);
writableSheet.removeRow(39);
writableSheet.unmergeCells(mergeCells2);
WritableCellFormat writableCellFormat122 = new WritableCellFormat();
writableCellFormat122.setAlignment(Alignment.CENTRE);
writableSheet.addCell(new Label(5, 42, "Centred across merged cells", writableCellFormat122));
writableSheet.mergeCells(5, 42, i, 42);
WritableCellFormat writableCellFormat132 = new WritableCellFormat();
writableCellFormat132.setBorder(Border.ALL, BorderLineStyle.THIN);
writableCellFormat132.setBackground(Colour.GRAY_25);
writableSheet.addCell(new Label(3, 44, "Merged with border", writableCellFormat132));
writableSheet.mergeCells(3, 44, 4, 46);
writableSheet.addCell(new Label(0, 49, "Courier fonts", new WritableCellFormat(new WritableFont(WritableFont.COURIER, i))));
writableSheet.addCell(new Label(0, 50, "Tahoma fonts", new WritableCellFormat(new WritableFont(WritableFont.TAHOMA, 12))));
writableSheet.addCell(new Label(0, 51, "Bespoke Windgdings 2", new WritableCellFormat(new WritableFont(WritableFont.createFont("Wingdings 2"), i))));
WritableFont writableFont52 = writableFont;
WritableCellFormat writableCellFormat142 = new WritableCellFormat(writableFont52);
writableCellFormat142.setShrinkToFit(true);
writableSheet.addCell(new Label(3, 53, "Shrunk to fit", writableCellFormat142));
writableSheet.addCell(new Label(3, 55, "Some long wrapped text in a merged cell", writableCellFormat));
writableSheet.mergeCells(3, 55, 4, 55);
WritableCell label22 = new Label(0, 57, "A cell with a comment");
WritableCellFeatures writableCellFeatures8 = new WritableCellFeatures();
writableCellFeatures8.setComment("the cell comment");
label22.setCellFeatures(writableCellFeatures8);
writableSheet.addCell(label22);
WritableCell label32 = new Label(0, 59, "A cell with a long comment");
WritableCellFeatures writableCellFeatures22 = new WritableCellFeatures();
writableCellFeatures22.setComment("a very long cell comment indeed that won't fit inside a standard comment box, so a larger comment box is used instead", 5.0d, 6.0d);
label32.setCellFeatures(writableCellFeatures22);
writableSheet.addCell(label32);
WritableCellFormat writableCellFormat152 = new WritableCellFormat(writableFont52);
writableCellFormat152.setIndentation(4);
writableSheet.addCell(new Label(0, 61, "Some indented text", writableCellFormat152));
writableSheet.addCell(new Label(0, 63, "Data validation: list"));
WritableCell blank5 = new Blank(1, 63);
WritableCellFeatures writableCellFeatures32 = new WritableCellFeatures();
ArrayList arrayList3 = new ArrayList();
arrayList3.add("bagpuss");
arrayList3.add("clangers");
arrayList3.add("ivor the engine");
arrayList3.add("noggin the nog");
writableCellFeatures32.setDataValidationList(arrayList3);
blank5.setCellFeatures(writableCellFeatures32);
writableSheet.addCell(blank5);
writableSheet.addCell(new Label(0, 64, "Data validation: number > 4.5"));
WritableCell blank22 = new Blank(1, 64);
WritableCellFeatures writableCellFeatures42 = new WritableCellFeatures();
writableCellFeatures42.setNumberValidation(4.5d, WritableCellFeatures.GREATER_THAN);
blank22.setCellFeatures(writableCellFeatures42);
writableSheet.addCell(blank22);
writableSheet.addCell(new Label(0, 65, "Data validation: named range"));
writableSheet.addCell(new Label(4, 65, "tiger"));
writableSheet.addCell(new Label(5, 65, "sword"));
writableSheet.addCell(new Label(6, 65, "honour"));
writableSheet.addCell(new Label(7, 65, "company"));
writableSheet.addCell(new Label(i2, 65, "victory"));
writableSheet.addCell(new Label(9, 65, "fortress"));
WritableCell blank32 = new Blank(1, 65);
WritableCellFeatures writableCellFeatures52 = new WritableCellFeatures();
writableCellFeatures52.setDataValidationRange("validation_range");
blank32.setCellFeatures(writableCellFeatures52);
writableSheet.addCell(blank32);
writableSheet.setRowGroup(39, 45, false);
writableSheet.addCell(new Label(0, 66, "Block of cells B67-F71 with data validation"));
ArrayList arrayList22 = new ArrayList();
arrayList22.add("Achilles");
arrayList22.add("Agamemnon");
arrayList22.add("Hector");
arrayList22.add("Odysseus");
arrayList22.add("Patroclus");
arrayList22.add("Nestor");
WritableCell blank42 = new Blank(1, 66);
WritableCellFeatures writableCellFeatures62 = new WritableCellFeatures();
writableCellFeatures62.setDataValidationList(arrayList22);
blank42.setCellFeatures(writableCellFeatures62);
writableSheet.addCell(blank42);
writableSheet.applySharedDataValidation(blank42, 4, 4);
WritableCellFeatures writableCellFeatures72 = new WritableCellFeatures();
writableCellFeatures72.setDataValidationRange("");
WritableCell label42 = new Label(0, 71, "Read only cell using empty data validation");
label42.setCellFeatures(writableCellFeatures72);
writableSheet.addCell(label42);
writableSheet.setRowGroup(39, 45, false);
}
private void writeBordersSheet(WritableSheet writableSheet) throws WriteException {
writableSheet.getSettings().setProtected(true);
writableSheet.setColumnView(1, 15);
writableSheet.setColumnView(2, 15);
writableSheet.setColumnView(4, 15);
WritableCellFormat writableCellFormat = new WritableCellFormat();
writableCellFormat.setBorder(Border.LEFT, BorderLineStyle.THICK);
writableSheet.addCell(new Label(1, 0, "Thick left", writableCellFormat));
WritableCellFormat writableCellFormat2 = new WritableCellFormat();
writableCellFormat2.setBorder(Border.RIGHT, BorderLineStyle.DASHED);
writableSheet.addCell(new Label(2, 0, "Dashed right", writableCellFormat2));
WritableCellFormat writableCellFormat3 = new WritableCellFormat();
writableCellFormat3.setBorder(Border.TOP, BorderLineStyle.DOUBLE);
writableSheet.addCell(new Label(1, 2, "Double top", writableCellFormat3));
WritableCellFormat writableCellFormat4 = new WritableCellFormat();
writableCellFormat4.setBorder(Border.BOTTOM, BorderLineStyle.HAIR);
writableSheet.addCell(new Label(2, 2, "Hair bottom", writableCellFormat4));
WritableCellFormat writableCellFormat5 = new WritableCellFormat();
writableCellFormat5.setBorder(Border.ALL, BorderLineStyle.THIN);
writableSheet.addCell(new Label(4, 2, "All thin", writableCellFormat5));
WritableCellFormat writableCellFormat6 = new WritableCellFormat();
writableCellFormat6.setBorder(Border.TOP, BorderLineStyle.THICK);
writableCellFormat6.setBorder(Border.LEFT, BorderLineStyle.THICK);
writableSheet.addCell(new Label(6, 2, "Two borders", writableCellFormat6));
writableSheet.addCell(new Label(20, 20, "Dislocated cell - after a page break"));
writableSheet.getSettings().setPaperSize(PaperSize.A3);
writableSheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);
writableSheet.getSettings().setPageOrder(PageOrder.DOWN_THEN_RIGHT);
writableSheet.getSettings().setHeaderMargin(2.0d);
writableSheet.getSettings().setFooterMargin(2.0d);
writableSheet.getSettings().setTopMargin(3.0d);
writableSheet.getSettings().setBottomMargin(3.0d);
HeaderFooter headerFooter = new HeaderFooter();
headerFooter.getCentre().append("Page Header");
writableSheet.getSettings().setHeader(headerFooter);
HeaderFooter headerFooter2 = new HeaderFooter();
headerFooter2.getRight().append("page ");
headerFooter2.getRight().appendPageNumber();
writableSheet.getSettings().setFooter(headerFooter2);
writableSheet.addRowPageBreak(18);
writableSheet.insertRow(17);
writableSheet.insertRow(17);
writableSheet.removeRow(17);
writableSheet.addRowPageBreak(30);
writableSheet.addCell(new Label(10, 1, "Hidden column"));
writableSheet.addCell(new Label(3, 8, "Hidden row"));
writableSheet.setRowView(8, true);
WritableCellFormat writableCellFormat7 = new WritableCellFormat();
writableCellFormat7.setBorder(Border.ALL, BorderLineStyle.THICK, Colour.RED);
writableSheet.addCell(new Label(1, 5, "All thick red", writableCellFormat7));
WritableCellFormat writableCellFormat8 = new WritableCellFormat();
writableCellFormat8.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLUE);
writableCellFormat8.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLUE);
writableSheet.addCell(new Label(4, 5, "Top and bottom blue", writableCellFormat8));
}
private void writeLabelsSheet(WritableSheet writableSheet) throws WriteException {
writableSheet.getSettings().setProtected(true);
writableSheet.getSettings().setPassword("jxl");
writableSheet.getSettings().setVerticalFreeze(5);
writableSheet.getSettings().setDefaultRowHeight(500);
WritableFont writableFont = new WritableFont(WritableFont.ARIAL, 12);
writableFont.setItalic(true);
WritableCellFormat writableCellFormat = new WritableCellFormat(writableFont);
CellView cellView = new CellView();
cellView.setSize(6400);
cellView.setFormat(writableCellFormat);
writableSheet.setColumnView(0, cellView);
writableSheet.setColumnView(1, 15);
for (int i = 0; i < 61; i++) {
Label label = new Label(0, i, "Common Label");
Label label2 = new Label(1, i, "Distinct label number " + i);
writableSheet.addCell(label);
writableSheet.addCell(label2);
}
Label label3 = new Label(0, 61, "Common Label", writableCellFormat);
Label label4 = new Label(1, 61, "1-1234567890", writableCellFormat);
Label label5 = new Label(2, 61, "2-1234567890", writableCellFormat);
writableSheet.addCell(label3);
writableSheet.addCell(label4);
writableSheet.addCell(label5);
for (int i2 = 62; i2 < 200; i2++) {
Label label6 = new Label(0, i2, "Common Label");
Label label7 = new Label(1, i2, "Distinct label number " + i2);
writableSheet.addCell(label6);
writableSheet.addCell(label7);
}
WritableFont writableFont2 = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
writableFont2.setColour(Colour.RED);
WritableCellFormat writableCellFormat2 = new WritableCellFormat(writableFont2);
writableCellFormat2.setWrap(true);
writableSheet.addCell(new Label(0, 205, "Different format", writableCellFormat2));
writableSheet.addCell(new Label(5, 2, "A column for autosizing", writableCellFormat2));
writableSheet.addCell(new Label(5, 4, "Another label, longer this time and in a different font"));
CellView cellView2 = new CellView();
cellView2.setAutosize(true);
writableSheet.setColumnView(5, cellView2);
}
private void writeFormulaSheet(WritableSheet writableSheet) throws WriteException {
writableSheet.addCell(new Number(0, 0, 15.0d));
writableSheet.addCell(new Number(0, 1, 16.0d));
writableSheet.addCell(new Number(0, 2, 10.0d));
writableSheet.addCell(new Number(0, 3, 12.0d));
writableSheet.setColumnView(2, 20);
WritableCellFormat writableCellFormat = new WritableCellFormat();
writableCellFormat.setAlignment(Alignment.RIGHT);
writableCellFormat.setWrap(true);
CellView cellView = new CellView();
cellView.setSize(6400);
cellView.setFormat(writableCellFormat);
writableSheet.setColumnView(3, cellView);
writableSheet.addCell(new Formula(2, 0, "A1+A2"));
writableSheet.addCell(new Label(3, 0, "a1+a2"));
writableSheet.addCell(new Formula(2, 1, "A2 * 3"));
writableSheet.addCell(new Label(3, 1, "A2 * 3"));
writableSheet.addCell(new Formula(2, 2, "A2+A1/2.5"));
writableSheet.addCell(new Label(3, 2, "A2+A1/2.5"));
writableSheet.addCell(new Formula(2, 3, "3+(a1+a2)/2.5"));
writableSheet.addCell(new Label(3, 3, "3+(a1+a2)/2.5"));
writableSheet.addCell(new Formula(2, 4, "(a1+a2)/2.5"));
writableSheet.addCell(new Label(3, 4, "(a1+a2)/2.5"));
writableSheet.addCell(new Formula(2, 5, "15+((a1+a2)/2.5)*17"));
writableSheet.addCell(new Label(3, 5, "15+((a1+a2)/2.5)*17"));
writableSheet.addCell(new Formula(2, 6, "SUM(a1:a4)"));
writableSheet.addCell(new Label(3, 6, "SUM(a1:a4)"));
writableSheet.addCell(new Formula(2, 7, "SUM(a1:a4)/4"));
writableSheet.addCell(new Label(3, 7, "SUM(a1:a4)/4"));
writableSheet.addCell(new Formula(2, 8, "AVERAGE(A1:A4)"));
writableSheet.addCell(new Label(3, 8, "AVERAGE(a1:a4)"));
writableSheet.addCell(new Formula(2, 9, "MIN(5,4,1,2,3)"));
writableSheet.addCell(new Label(3, 9, "MIN(5,4,1,2,3)"));
writableSheet.addCell(new Formula(2, 10, "ROUND(3.14159265, 3)"));
writableSheet.addCell(new Label(3, 10, "ROUND(3.14159265, 3)"));
writableSheet.addCell(new Formula(2, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))"));
writableSheet.addCell(new Label(3, 11, "MAX(SUM(A1:A2), A1*A2, POWER(A1, 2))"));
writableSheet.addCell(new Formula(2, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")"));
writableSheet.addCell(new Label(3, 12, "IF(A2>A1, \"A2 bigger\", \"A1 bigger\")"));
writableSheet.addCell(new Formula(2, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")"));
writableSheet.addCell(new Label(3, 13, "IF(A2<=A1, \"A2 smaller\", \"A1 smaller\")"));
writableSheet.addCell(new Formula(2, 14, "IF(A3<=10, \"<= 10\")"));
writableSheet.addCell(new Label(3, 14, "IF(A3<=10, \"<= 10\")"));
writableSheet.addCell(new Formula(2, 15, "SUM(1,2,3,4,5)"));
writableSheet.addCell(new Label(3, 15, "SUM(1,2,3,4,5)"));
writableSheet.addCell(new Formula(2, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")"));
writableSheet.addCell(new Label(3, 16, "HYPERLINK(\"http://www.andykhan.com/jexcelapi\", \"JExcelApi Home Page\")"));
writableSheet.addCell(new Formula(2, 17, "3*4+5"));
writableSheet.addCell(new Label(3, 17, "3*4+5"));
writableSheet.addCell(new Formula(2, 18, "\"Plain text formula\""));
writableSheet.addCell(new Label(3, 18, "Plain text formula"));
writableSheet.addCell(new Formula(2, 19, "SUM(a1,a2,-a3,a4)"));
writableSheet.addCell(new Label(3, 19, "SUM(a1,a2,-a3,a4)"));
writableSheet.addCell(new Formula(2, 20, "2*-(a1+a2)"));
writableSheet.addCell(new Label(3, 20, "2*-(a1+a2)"));
writableSheet.addCell(new Formula(2, 21, "'Number Formats'!B1/2"));
writableSheet.addCell(new Label(3, 21, "'Number Formats'!B1/2"));
writableSheet.addCell(new Formula(2, 22, "IF(F22=0, 0, F21/F22)"));
writableSheet.addCell(new Label(3, 22, "IF(F22=0, 0, F21/F22)"));
writableSheet.addCell(new Formula(2, 23, "RAND()"));
writableSheet.addCell(new Label(3, 23, "RAND()"));
StringBuffer stringBuffer = new StringBuffer("'");
stringBuffer.append(this.workbook.getSheet(0).getName());
stringBuffer.append("'!");
stringBuffer.append(CellReferenceHelper.getCellReference(9, 18));
stringBuffer.append("*25");
writableSheet.addCell(new Formula(2, 24, stringBuffer.toString()));
writableSheet.addCell(new Label(3, 24, stringBuffer.toString()));
writableSheet.addCell(new Formula(2, 25, "NOW()", new WritableCellFormat(DateFormats.DEFAULT)));
writableSheet.addCell(new Label(3, 25, "NOW()"));
writableSheet.addCell(new Formula(2, 26, "$A$2+A3"));
writableSheet.addCell(new Label(3, 26, "$A$2+A3"));
writableSheet.addCell(new Formula(2, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))"));
writableSheet.addCell(new Label(3, 27, "IF(COUNT(A1:A9,B1:B9)=0,\"\",COUNT(A1:A9,B1:B9))"));
writableSheet.addCell(new Formula(2, 28, "SUM(A1,A2,A3,A4)"));
writableSheet.addCell(new Label(3, 28, "SUM(A1,A2,A3,A4)"));
writableSheet.addCell(new Label(1, 29, "a1"));
writableSheet.addCell(new Formula(2, 29, "SUM(INDIRECT(ADDRESS(2,29)):A4)"));
writableSheet.addCell(new Label(3, 29, "SUM(INDIRECT(ADDRESS(2,29):A4)"));
writableSheet.addCell(new Formula(2, 30, "COUNTIF(A1:A4, \">=12\")"));
writableSheet.addCell(new Label(3, 30, "COUNTIF(A1:A4, \">=12\")"));
writableSheet.addCell(new Formula(2, 31, "MAX($A$1:$A$4)"));
writableSheet.addCell(new Label(3, 31, "MAX($A$1:$A$4)"));
writableSheet.addCell(new Formula(2, 32, "OR(A1,TRUE)"));
writableSheet.addCell(new Label(3, 32, "OR(A1,TRUE)"));
writableSheet.addCell(new Formula(2, 33, "ROWS(A1:C14)"));
writableSheet.addCell(new Label(3, 33, "ROWS(A1:C14)"));
writableSheet.addCell(new Formula(2, 34, "COUNTBLANK(A1:C14)"));
writableSheet.addCell(new Label(3, 34, "COUNTBLANK(A1:C14)"));
writableSheet.addCell(new Formula(2, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)"));
writableSheet.addCell(new Label(3, 35, "IF(((F1=\"Not Found\")*(F2=\"Not Found\")*(F3=\"\")*(F4=\"\")*(F5=\"\")),1,0)"));
writableSheet.addCell(new Formula(2, 36, "HYPERLINK(\"http://www.amazon.co.uk/exec/obidos/ASIN/0571058086qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664\", \"Long hyperlink\")"));
writableSheet.addCell(new Formula(2, 37, "1234567+2699"));
writableSheet.addCell(new Label(3, 37, "1234567+2699"));
writableSheet.addCell(new Formula(2, 38, "IF(ISERROR(G25/G29),0,-1)"));
writableSheet.addCell(new Label(3, 38, "IF(ISERROR(G25/G29),0,-1)"));
writableSheet.addCell(new Formula(2, 39, "SEARCH(\"C\",D40)"));
writableSheet.addCell(new Label(3, 39, "SEARCH(\"C\",D40)"));
writableSheet.addCell(new Formula(2, 40, "#REF!"));
writableSheet.addCell(new Label(3, 40, "#REF!"));
writableSheet.addCell(new Number(1, 41, 79.0d));
writableSheet.addCell(new Formula(2, 41, "--B42"));
writableSheet.addCell(new Label(3, 41, "--B42"));
writableSheet.addCell(new Formula(2, 42, "CHOOSE(3,A1,A2,A3,A4"));
writableSheet.addCell(new Label(3, 42, "CHOOSE(3,A1,A2,A3,A4"));
writableSheet.addCell(new Formula(2, 43, "A4-A3-A2"));
writableSheet.addCell(new Label(3, 43, "A4-A3-A2"));
writableSheet.addCell(new Formula(2, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308"));
writableSheet.addCell(new Label(3, 44, "F29+F34+F41+F48+F55+F62+F69+F76+F83+F90+F97+F104+F111+F118+F125+F132+F139+F146+F153+F160+F167+F174+F181+F188+F195+F202+F209+F216+F223+F230+F237+F244+F251+F258+F265+F272+F279+F286+F293+F300+F305+F308"));
writableSheet.addCell(new Number(1, 45, 17.0d));
writableSheet.addCell(new Formula(2, 45, "formulavalue+5"));
writableSheet.addCell(new Label(3, 45, "formulavalue+5"));
}
private void writeImageSheet(WritableSheet writableSheet) throws WriteException {
writableSheet.addCell(new Label(0, 0, "Weald & Downland Open Air Museum, Sussex"));
writableSheet.addImage(new WritableImage(0.0d, 3.0d, 5.0d, 7.0d, new File("resources/wealdanddownland.png")));
writableSheet.addCell(new Label(0, 12, "Merchant Adventurers Hall, York"));
writableSheet.addImage(new WritableImage(5.0d, 12.0d, 4.0d, 10.0d, new File("resources/merchantadventurers.png")));
}
}