/**
  * A constructor which specifies the font and date/number format for cells
  * which wish to use this format
  *
  * @param font the font
  * @param format the date/number format
  */
 public WritableCellFormat(WritableFont font, DisplayFormat format)
     : base(font, format)
 {
 }
 /**
  * A CellFormat which specifies the font for cells with this format
  *
  * @param font the font
  */
 public WritableCellFormat(WritableFont font)
     : this(font, NumberFormats.DEFAULT)
 {
 }
Example #3
0
        /**
         * Adds cells to the specified sheet which test the various label formatting
         * styles, such as different fonts, different sizes and bold, underline etc.
         *
         * @param s1
         */
        private void writeLabelFormatSheet(WritableSheet s1)
        {
            s1.setColumnView(0, 60);

            Label lr = new Label(0, 0, "Arial Fonts");
            s1.addCell(lr);

            lr = new Label(1, 0, "10pt");
            s1.addCell(lr);

            lr = new Label(2, 0, "Normal");
            s1.addCell(lr);

            lr = new Label(3, 0, "12pt");
            s1.addCell(lr);

            WritableFont arial12pt = new WritableFont(WritableFont.ARIAL, 12);
            WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
            arial12format.setWrap(true);
            lr = new Label(4, 0, "Normal", arial12format);
            s1.addCell(lr);

            WritableFont arial10ptBold = new WritableFont
              (WritableFont.ARIAL, 10, WritableFont.BOLD);
            WritableCellFormat arial10BoldFormat = new WritableCellFormat
              (arial10ptBold);
            lr = new Label(2, 2, "BOLD", arial10BoldFormat);
            s1.addCell(lr);

            WritableFont arial12ptBold = new WritableFont
              (WritableFont.ARIAL, 12, WritableFont.BOLD);
            WritableCellFormat arial12BoldFormat = new WritableCellFormat
              (arial12ptBold);
            lr = new Label(4, 2, "BOLD", arial12BoldFormat);
            s1.addCell(lr);

            WritableFont arial10ptItalic = new WritableFont
              (WritableFont.ARIAL, 10, WritableFont.NO_BOLD, true);
            WritableCellFormat arial10ItalicFormat = new WritableCellFormat
              (arial10ptItalic);
            lr = new Label(2, 4, "Italic", arial10ItalicFormat);
            s1.addCell(lr);

            WritableFont arial12ptItalic = new WritableFont
              (WritableFont.ARIAL, 12, WritableFont.NO_BOLD, true);
            WritableCellFormat arial12ptItalicFormat = new WritableCellFormat
              (arial12ptItalic);
            lr = new Label(4, 4, "Italic", arial12ptItalicFormat);
            s1.addCell(lr);

            WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
            WritableCellFormat times10format = new WritableCellFormat(times10pt);
            lr = new Label(0, 7, "Times Fonts", times10format);
            s1.addCell(lr);

            lr = new Label(1, 7, "10pt", times10format);
            s1.addCell(lr);

            lr = new Label(2, 7, "Normal", times10format);
            s1.addCell(lr);

            lr = new Label(3, 7, "12pt", times10format);
            s1.addCell(lr);

            WritableFont times12pt = new WritableFont(WritableFont.TIMES, 12);
            WritableCellFormat times12format = new WritableCellFormat(times12pt);
            lr = new Label(4, 7, "Normal", times12format);
            s1.addCell(lr);

            WritableFont times10ptBold = new WritableFont
              (WritableFont.TIMES, 10, WritableFont.BOLD);
            WritableCellFormat times10BoldFormat = new WritableCellFormat
              (times10ptBold);
            lr = new Label(2, 9, "BOLD", times10BoldFormat);
            s1.addCell(lr);

            WritableFont times12ptBold = new WritableFont
              (WritableFont.TIMES, 12, WritableFont.BOLD);
            WritableCellFormat times12BoldFormat = new WritableCellFormat
              (times12ptBold);
            lr = new Label(4, 9, "BOLD", times12BoldFormat);
            s1.addCell(lr);

            // The underline styles
            s1.setColumnView(6, 22);
            s1.setColumnView(7, 22);
            s1.setColumnView(8, 22);
            s1.setColumnView(9, 22);

            lr = new Label(0, 11, "Underlining");
            s1.addCell(lr);

            WritableFont arial10ptUnderline = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.SINGLE);
            WritableCellFormat arialUnderline = new WritableCellFormat
              (arial10ptUnderline);
            lr = new Label(6, 11, "Underline", arialUnderline);
            s1.addCell(lr);

            WritableFont arial10ptDoubleUnderline = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.DOUBLE);
            WritableCellFormat arialDoubleUnderline = new WritableCellFormat
              (arial10ptDoubleUnderline);
            lr = new Label(7, 11, "Double Underline", arialDoubleUnderline);
            s1.addCell(lr);

            WritableFont arial10ptSingleAcc = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.SINGLE_ACCOUNTING);
            WritableCellFormat arialSingleAcc = new WritableCellFormat
              (arial10ptSingleAcc);
            lr = new Label(8, 11, "Single Accounting Underline", arialSingleAcc);
            s1.addCell(lr);

            WritableFont arial10ptDoubleAcc = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.DOUBLE_ACCOUNTING);
            WritableCellFormat arialDoubleAcc = new WritableCellFormat
              (arial10ptDoubleAcc);
            lr = new Label(9, 11, "Double Accounting Underline", arialDoubleAcc);
            s1.addCell(lr);

            WritableFont times14ptBoldUnderline = new WritableFont
              (WritableFont.TIMES,
               14,
               WritableFont.BOLD,
               false,
               UnderlineStyle.SINGLE);
            WritableCellFormat timesBoldUnderline = new WritableCellFormat
              (times14ptBoldUnderline);
            lr = new Label(6, 12, "Times 14 Bold Underline", timesBoldUnderline);
            s1.addCell(lr);

            WritableFont arial18ptBoldItalicUnderline = new WritableFont
              (WritableFont.ARIAL,
               18,
               WritableFont.BOLD,
               true,
               UnderlineStyle.SINGLE);
            WritableCellFormat arialBoldItalicUnderline = new WritableCellFormat
              (arial18ptBoldItalicUnderline);
            lr = new Label(6, 13, "Arial 18 Bold Italic Underline",
                           arialBoldItalicUnderline);
            s1.addCell(lr);

            lr = new Label(0, 15, "Script styles");
            s1.addCell(lr);

            WritableFont superscript = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.NO_UNDERLINE,
               Colour.BLACK,
               ScriptStyle.SUPERSCRIPT);
            WritableCellFormat superscriptFormat = new WritableCellFormat
              (superscript);
            lr = new Label(1, 15, "superscript", superscriptFormat);
            s1.addCell(lr);

            WritableFont subscript = new WritableFont
              (WritableFont.ARIAL,
               WritableFont.DEFAULT_POINT_SIZE,
               WritableFont.NO_BOLD,
               false,
               UnderlineStyle.NO_UNDERLINE,
               Colour.BLACK,
               ScriptStyle.SUBSCRIPT);
            WritableCellFormat subscriptFormat = new WritableCellFormat
              (subscript);
            lr = new Label(2, 15, "subscript", subscriptFormat);
            s1.addCell(lr);

            lr = new Label(0, 17, "Colours");
            s1.addCell(lr);

            WritableFont red = new WritableFont(WritableFont.ARIAL,
                                                WritableFont.DEFAULT_POINT_SIZE,
                                                WritableFont.NO_BOLD,
                                                false,
                                                UnderlineStyle.NO_UNDERLINE,
                                                Colour.RED);
            WritableCellFormat redFormat = new WritableCellFormat(red);
            lr = new Label(2, 17, "Red", redFormat);
            s1.addCell(lr);

            WritableFont blue = new WritableFont(WritableFont.ARIAL,
                                                 WritableFont.DEFAULT_POINT_SIZE,
                                                 WritableFont.NO_BOLD,
                                                 false,
                                                 UnderlineStyle.NO_UNDERLINE,
                                                 Colour.BLUE);
            WritableCellFormat blueFormat = new WritableCellFormat(blue);
            lr = new Label(2, 18, "Blue", blueFormat);
            s1.addCell(lr);

            WritableFont lime = new WritableFont(WritableFont.ARIAL);
            lime.setColour(Colour.LIME);
            WritableCellFormat limeFormat = new WritableCellFormat(lime);
            limeFormat.setWrap(true);
            lr = new Label(4, 18, "Modified palette - was lime, now red", limeFormat);
            s1.addCell(lr);

            WritableCellFormat greyBackground = new WritableCellFormat();
            greyBackground.setWrap(true);
            greyBackground.setBackground(Colour.GRAY_50);
            lr = new Label(2, 19, "Grey background", greyBackground);
            s1.addCell(lr);

            WritableFont yellow = new WritableFont(WritableFont.ARIAL,
                                                   WritableFont.DEFAULT_POINT_SIZE,
                                                   WritableFont.NO_BOLD,
                                                   false,
                                                   UnderlineStyle.NO_UNDERLINE,
                                                   Colour.YELLOW);
            WritableCellFormat yellowOnBlue = new WritableCellFormat(yellow);
            yellowOnBlue.setWrap(true);
            yellowOnBlue.setBackground(Colour.BLUE);
            lr = new Label(2, 20, "Blue background, yellow foreground", yellowOnBlue);
            s1.addCell(lr);

            WritableCellFormat yellowOnBlack = new WritableCellFormat(yellow);
            yellowOnBlack.setWrap(true);
            yellowOnBlack.setBackground(Colour.PALETTE_BLACK);
            lr = new Label(3, 20, "Black background, yellow foreground",
                           yellowOnBlack);
            s1.addCell(lr);

            lr = new Label(0, 22, "Null label");
            s1.addCell(lr);

            lr = new Label(2, 22, null);
            s1.addCell(lr);

            lr = new Label(0, 24,
                           "A very long label, more than 255 characters\012" +
                           "Rejoice O shores\012" +
                           "Sing O bells\012" +
                           "But I with mournful tread\012" +
                           "Walk the deck my captain lies\012" +
                           "Fallen cold and dead\012" +
                           "Summer surprised, coming over the Starnbergersee\012" +
                           "With a shower of rain. We stopped in the Colonnade\012" +
                           "A very long label, more than 255 characters\012" +
                           "Rejoice O shores\012" +
                           "Sing O bells\012" +
                           "But I with mournful tread\012" +
                           "Walk the deck my captain lies\012" +
                           "Fallen cold and dead\012" +
                           "Summer surprised, coming over the Starnbergersee\012" +
                           "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
                           "Rejoice O shores\012" +
                           "Sing O bells\012" +
                           "But I with mournful tread\012" +
                           "Walk the deck my captain lies\012" +
                           "Fallen cold and dead\012" +
                           "Summer surprised, coming over the Starnbergersee\012" +
                           "With a shower of rain. We stopped in the Colonnade\012" + "A very long label, more than 255 characters\012" +
                           "Rejoice O shores\012" +
                           "Sing O bells\012" +
                           "But I with mournful tread\012" +
                           "Walk the deck my captain lies\012" +
                           "Fallen cold and dead\012" +
                           "Summer surprised, coming over the Starnbergersee\012" +
                           "With a shower of rain. We stopped in the Colonnade\012" +
                           "And sat and drank coffee an talked for an hour\012",
                           arial12format);
            s1.addCell(lr);

            WritableCellFormat vertical = new WritableCellFormat();
            vertical.setOrientation(Orientation.VERTICAL);
            lr = new Label(0, 26, "Vertical orientation", vertical);
            s1.addCell(lr);

            WritableCellFormat plus_90 = new WritableCellFormat();
            plus_90.setOrientation(Orientation.PLUS_90);
            lr = new Label(1, 26, "Plus 90", plus_90);
            s1.addCell(lr);

            WritableCellFormat minus_90 = new WritableCellFormat();
            minus_90.setOrientation(Orientation.MINUS_90);
            lr = new Label(2, 26, "Minus 90", minus_90);
            s1.addCell(lr);

            lr = new Label(0, 28, "Modified row height");
            s1.addCell(lr);
            s1.setRowView(28, 24 * 20);

            lr = new Label(0, 29, "Collapsed row");
            s1.addCell(lr);
            s1.setRowView(29, true);

            // Write hyperlinks
            Label l;
            try
                {
                l = new Label(0, 30, "Hyperlink to home page");
                s1.addCell(l);

                Uri url = new Uri("http://www.andykhan.com/jexcelapi");
                WritableHyperlink wh = new WritableHyperlink(0, 30, 8, 31, url);
                s1.addHyperlink(wh);

                // The below hyperlink clashes with above
                WritableHyperlink wh2 = new WritableHyperlink(7, 30, 9, 31, url);
                s1.addHyperlink(wh2);

                l = new Label(4, 2, "File hyperlink to documentation");
                s1.addCell(l);

                FileInfo file = new FileInfo("../jexcelapi/docs/index.html");
                wh = new WritableHyperlink(0, 32, 8, 32, file, "JExcelApi Documentation");
                s1.addHyperlink(wh);

                // Add a hyperlink to another cell on this sheet
                wh = new WritableHyperlink(0, 34, 8, 34,
                                           "Link to another cell",
                                           s1,
                                           0, 180, 1, 181);
                s1.addHyperlink(wh);

                file = new FileInfo("\\\\localhost\\file.txt");
                wh = new WritableHyperlink(0, 36, 8, 36, file);
                s1.addHyperlink(wh);

                // Add a very long hyperlink
                url = new Uri("http://www.amazon.co.uk/exec/obidos/ASIN/0571058086" +
                             "/qid=1099836249/sr=1-3/ref=sr_1_11_3/202-6017285-1620664");
                wh = new WritableHyperlink(0, 38, 0, 38, url);
                s1.addHyperlink(wh);
                }
            catch (Exception e)
                {
                Console.WriteLine(e);
                }

            // Write out some merged cells
            l = new Label(5, 35, "Merged cells", timesBoldUnderline);
            s1.mergeCells(5, 35, 8, 37);
            s1.addCell(l);

            l = new Label(5, 38, "More merged cells");
            s1.addCell(l);
            Range r = s1.mergeCells(5, 38, 8, 41);
            s1.insertRow(40);
            s1.removeRow(39);
            s1.unmergeCells(r);

            // Merge cells and centre across them
            WritableCellFormat wcf = new WritableCellFormat();
            wcf.setAlignment(Alignment.CENTRE);
            l = new Label(5, 42, "Centred across merged cells", wcf);
            s1.addCell(l);
            s1.mergeCells(5, 42, 10, 42);

            wcf = new WritableCellFormat();
            wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
            wcf.setBackground(Colour.GRAY_25);
            l = new Label(3, 44, "Merged with border", wcf);
            s1.addCell(l);
            s1.mergeCells(3, 44, 4, 46);

            // Clash some ranges - the second range will not be added
            // Also merge some cells with two data items in the - the second data
            // item will not be merged
            /*
            l = new Label(5, 16, "merged cells");
            s1.addCell(l);

            Label l5 = new Label(7, 17, "this label won't appear");
            s1.addCell(l5);
            s1.mergeCells(5, 16, 8, 18);

            s1.mergeCells(5, 19, 6, 24);
            s1.mergeCells(6, 18, 10, 19);
            */

            WritableFont courier10ptFont = new WritableFont(WritableFont.COURIER, 10);
            WritableCellFormat courier10pt = new WritableCellFormat(courier10ptFont);
            l = new Label(0, 49, "Courier fonts", courier10pt);
            s1.addCell(l);

            WritableFont tahoma12ptFont = new WritableFont(WritableFont.TAHOMA, 12);
            WritableCellFormat tahoma12pt = new WritableCellFormat(tahoma12ptFont);
            l = new Label(0, 50, "Tahoma fonts", tahoma12pt);
            s1.addCell(l);

            WritableFont.FontName wingdingsFont =
              WritableFont.createFont("Wingdings 2");
            WritableFont wingdings210ptFont = new WritableFont(wingdingsFont, 10);
            WritableCellFormat wingdings210pt = new WritableCellFormat
              (wingdings210ptFont);
            l = new Label(0, 51, "Bespoke Windgdings 2", wingdings210pt);
            s1.addCell(l);

            WritableCellFormat shrinkToFit = new WritableCellFormat(times12pt);
            shrinkToFit.setShrinkToFit(true);
            l = new Label(3, 53, "Shrunk to fit", shrinkToFit);
            s1.addCell(l);

            l = new Label(3, 55, "Some long wrapped text in a merged cell",
                          arial12format);
            s1.addCell(l);
            s1.mergeCells(3, 55, 4, 55);

            l = new Label(0, 57, "A cell with a comment");
            WritableCellFeatures cellFeatures = new WritableCellFeatures();
            cellFeatures.setComment("the cell comment");
            l.setCellFeatures(cellFeatures);
            s1.addCell(l);

            l = new Label(0, 59,
                          "A cell with a long comment");
            cellFeatures = new WritableCellFeatures();
            cellFeatures.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, 6);
            l.setCellFeatures(cellFeatures);
            s1.addCell(l);

            WritableCellFormat indented = new WritableCellFormat(times12pt);
            indented.setIndentation(4);
            l = new Label(0, 61, "Some indented text", indented);
            s1.addCell(l);

            l = new Label(0, 63, "Data validation:  list");
            s1.addCell(l);

            Blank b = new Blank(1, 63);
            cellFeatures = new WritableCellFeatures();
            ArrayList al = new ArrayList();
            al.Add("bagpuss");
            al.Add("clangers");
            al.Add("ivor the engine");
            al.Add("noggin the nog");
            cellFeatures.setDataValidationList(al);
            b.setCellFeatures(cellFeatures);
            s1.addCell(b);

            l = new Label(0, 64, "Data validation:  number > 4.5");
            s1.addCell(l);

            b = new Blank(1, 64);
            cellFeatures = new WritableCellFeatures();
            cellFeatures.setNumberValidation(4.5, WritableCellFeatures.GREATER_THAN);
            b.setCellFeatures(cellFeatures);
            s1.addCell(b);

            l = new Label(0, 65, "Data validation:  named range");
            s1.addCell(l);

            l = new Label(4, 65, "tiger");
            s1.addCell(l);
            l = new Label(5, 65, "sword");
            s1.addCell(l);
            l = new Label(6, 65, "honour");
            s1.addCell(l);
            l = new Label(7, 65, "company");
            s1.addCell(l);
            l = new Label(8, 65, "victory");
            s1.addCell(l);
            l = new Label(9, 65, "fortress");
            s1.addCell(l);

            b = new Blank(1, 65);
            cellFeatures = new WritableCellFeatures();
            cellFeatures.setDataValidationRange("validation_range");
            b.setCellFeatures(cellFeatures);
            s1.addCell(b);

            // Set the row grouping
            s1.setRowGroup(39, 45, false);
            // s1.setRowGroup(72, 74, true);

            l = new Label(0, 66, "Block of cells B67-F71 with data validation");
            s1.addCell(l);

            al = new ArrayList();
            al.Add("Achilles");
            al.Add("Agamemnon");
            al.Add("Hector");
            al.Add("Odysseus");
            al.Add("Patroclus");
            al.Add("Nestor");

            b = new Blank(1, 66);
            cellFeatures = new WritableCellFeatures();
            cellFeatures.setDataValidationList(al);
            b.setCellFeatures(cellFeatures);
            s1.addCell(b);
            s1.applySharedDataValidation(b, 4, 4);

            cellFeatures = new WritableCellFeatures();
            cellFeatures.setDataValidationRange("");
            l = new Label(0, 71, "Read only cell using empty data validation");
            l.setCellFeatures(cellFeatures);
            s1.addCell(l);

            // Set the row grouping
            s1.setRowGroup(39, 45, false);
            // s1.setRowGroup(72, 74, true);
        }
        /**
         * If the inputFile was the test spreadsheet, then it modifies certain fields
         * of the writable copy
         *
         * @param w
         */
        private void modify(WritableWorkbook w)
        {
            Console.WriteLine("Modifying...");

            WritableSheet sheet = w.getSheet("modified");

            WritableCell cell = null;
            CellFormat cf = null;
            Label l = null;
            WritableCellFeatures wcf = null;

            // Change the format of cell B4 to be emboldened
            cell = sheet.getWritableCell(1, 3);
            WritableFont bold = new WritableFont(WritableFont.ARIAL,
                                                 WritableFont.DEFAULT_POINT_SIZE,
                                                 WritableFont.BOLD);
            cf = new WritableCellFormat(bold);
            cell.setCellFormat(cf);

            // Change the format of cell B5 to be underlined
            cell = sheet.getWritableCell(1, 4);
            WritableFont underline = new WritableFont(WritableFont.ARIAL,
                                                      WritableFont.DEFAULT_POINT_SIZE,
                                                      WritableFont.NO_BOLD,
                                                      false,
                                                      UnderlineStyle.SINGLE);
            cf = new WritableCellFormat(underline);
            cell.setCellFormat(cf);

            // Change the point size of cell B6 to be 10 point
            cell = sheet.getWritableCell(1, 5);
            WritableFont tenpoint = new WritableFont(WritableFont.ARIAL, 10);
            cf = new WritableCellFormat(tenpoint);
            cell.setCellFormat(cf);

            // Change the contents of cell B7 to read "Label - mod"
            cell = sheet.getWritableCell(1, 6);
            if (cell.getType() == CellType.LABEL)
                {
                Label lc = (Label)cell;
                lc.setString(lc.getString() + " - mod");
                }

            // Change cell B10 to display 7 dps
            cell = sheet.getWritableCell(1, 9);
            NumberFormat sevendps = new NumberFormat("#.0000000");
            cf = new WritableCellFormat(sevendps);
            cell.setCellFormat(cf);

            // Change cell B11 to display in the format 1e4
            cell = sheet.getWritableCell(1, 10);
            NumberFormat exp4 = new NumberFormat("0.####E0");
            cf = new WritableCellFormat(exp4);
            cell.setCellFormat(cf);

            // Change cell B12 to be normal display
            cell = sheet.getWritableCell(1, 11);
            cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);

            // Change the contents of cell B13 to 42
            cell = sheet.getWritableCell(1, 12);
            if (cell.getType() == CellType.NUMBER)
                {
                Number n2 = (Number)cell;
                n2.setValue(42);
                }

            // Add 0.1 to the contents of cell B14
            cell = sheet.getWritableCell(1, 13);
            if (cell.getType() == CellType.NUMBER)
                {
                Number n3 = (Number)cell;
                n3.setValue(n3.getValue() + 0.1);
                }

            // Change the date format of cell B17 to be a custom format
            cell = sheet.getWritableCell(1, 16);
            DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
            cf = new WritableCellFormat(df);
            cell.setCellFormat(cf);

            // Change the date format of cell B18 to be a standard format
            cell = sheet.getWritableCell(1, 17);
            cf = new WritableCellFormat(DateFormats.FORMAT9);
            cell.setCellFormat(cf);

            // Change the date in cell B19 to be 18 Feb 1998, 11:23:28
            cell = sheet.getWritableCell(1, 18);
            if (cell.getType() == CellType.DATE)
                {
                // TODO: fix this....
                //DateTime dt = (DateTime)cell;
                //Calendar cal = Calendar.getInstance();
                //cal.set(1998, 1, 18, 11, 23, 28);
                //Date d = cal.getTime();
                //dt.setDate(d);
                }

            // Change the value in B23 to be 6.8.  This should recalculate the
            // formula
            cell = sheet.getWritableCell(1, 22);
            if (cell.getType() == CellType.NUMBER)
                {
                Number n1 = (Number)cell;
                n1.setValue(6.8);
                }

            // Change the label in B30.  This will have the effect of making
            // the original string unreferenced
            cell = sheet.getWritableCell(1, 29);
            if (cell.getType() == CellType.LABEL)
                {
                l = (Label)cell;
                l.setString("Modified string contents");
                }
            // Insert a new row (number 35)
            sheet.insertRow(34);

            // Delete row 38 (39 after row has been inserted)
            sheet.removeRow(38);

            // Insert a new column (J)
            sheet.insertColumn(9);

            // Remove a column (L - M after column has been inserted)
            sheet.removeColumn(11);

            // Remove row 44 (contains a hyperlink), and then insert an empty
            // row just to keep the numbers consistent
            sheet.removeRow(43);
            sheet.insertRow(43);

            // Modify the hyperlinks
            WritableHyperlink[] hyperlinks = sheet.getWritableHyperlinks();

            for (int i = 0; i < hyperlinks.Length; i++)
                {
                WritableHyperlink wh = hyperlinks[i];
                if (wh.getColumn() == 1 && wh.getRow() == 39)
                    {
                    try
                        {
                        // Change the hyperlink that begins in cell B40 to be a different API
                        wh.setURL(new Uri("http://www.andykhan.com/jexcelapi/index.html"));
                        }
                    catch (Exception e)
                        {
                        Console.WriteLine(e);
                        }
                    }
                else if (wh.getColumn() == 1 && wh.getRow() == 40)
                    {
                    wh.setFile(new FileInfo("../jexcelapi/docs/overview-summary.html"));
                    }
                else if (wh.getColumn() == 1 && wh.getRow() == 41)
                    {
                    wh.setFile(new FileInfo("d:/home/jexcelapi/docs/jxl/package-summary.html"));
                    }
                else if (wh.getColumn() == 1 && wh.getRow() == 44)
                    {
                    // Remove the hyperlink at B45
                    sheet.removeHyperlink(wh);
                    }
                }

            // Change the background of cell F31 from blue to red
            WritableCell c = sheet.getWritableCell(5, 30);
            WritableCellFormat newFormat = new WritableCellFormat(c.getCellFormat());
            newFormat.setBackground(Colour.RED);
            c.setCellFormat(newFormat);

            // Modify the contents of the merged cell
            l = new Label(0, 49, "Modified merged cells");
            sheet.addCell(l);

            // Modify the chart data
            Number n = (Number)sheet.getWritableCell(0, 70);
            n.setValue(9);

            n = (Number)sheet.getWritableCell(0, 71);
            n.setValue(10);

            n = (Number)sheet.getWritableCell(0, 73);
            n.setValue(4);

            // Add in a cross sheet formula
            Formula f = new Formula(1, 80, "ROUND(COS(original!B10),2)");
            sheet.addCell(f);

            // Add in a formula from the named cells
            f = new Formula(1, 83, "value1+value2");
            sheet.addCell(f);

            // Add in a function formula using named cells
            f = new Formula(1, 84, "AVERAGE(value1,value1*4,value2)");
            sheet.addCell(f);

            // Copy sheet 1 to sheet 3
            //     w.copySheet(0, "copy", 2);

            // Use the cell deep copy feature
            Label label = new Label(0, 88, "Some copied cells", cf);
            sheet.addCell(label);

            label = new Label(0, 89, "Number from B9");
            sheet.addCell(label);

            WritableCell wc = sheet.getWritableCell(1, 9).copyTo(1, 89);
            sheet.addCell(wc);

            label = new Label(0, 90, "Label from B4 (modified format)");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 3).copyTo(1, 90);
            sheet.addCell(wc);

            label = new Label(0, 91, "Date from B17");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 16).copyTo(1, 91);
            sheet.addCell(wc);

            label = new Label(0, 92, "Boolean from E16");
            sheet.addCell(label);

            wc = sheet.getWritableCell(4, 15).copyTo(1, 92);
            sheet.addCell(wc);

            label = new Label(0, 93, "URL from B40");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 39).copyTo(1, 93);
            sheet.addCell(wc);

            // Add some numbers for the formula copy
            for (int i = 0; i < 6; i++)
                {
                Number number = new Number(1, 94 + i, i + 1 + i / 8.0);
                sheet.addCell(number);
                }

            label = new Label(0, 100, "Formula from B27");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 26).copyTo(1, 100);
            sheet.addCell(wc);

            label = new Label(0, 101, "A brand new formula");
            sheet.addCell(label);

            Formula formula = new Formula(1, 101, "SUM(B94:B96)");
            sheet.addCell(formula);

            label = new Label(0, 102, "A copy of it");
            sheet.addCell(label);

            wc = sheet.getWritableCell(1, 101).copyTo(1, 102);
            sheet.addCell(wc);

            // Remove the second image from the sheet
            WritableImage wi = sheet.getImage(1);
            sheet.removeImage(wi);

            wi = new WritableImage(1, 116, 2, 9, new FileInfo("resources/littlemoretonhall.png"));
            sheet.addImage(wi);

            // Add a list data validations
            label = new Label(0, 151, "Added drop down validation");
            sheet.addCell(label);

            Blank b = new Blank(1, 151);
            wcf = new WritableCellFeatures();
            ArrayList al = new ArrayList();
            al.Add("The Fellowship of the Ring");
            al.Add("The Two Towers");
            al.Add("The Return of the King");
            wcf.setDataValidationList(al);
            b.setCellFeatures(wcf);
            sheet.addCell(b);

            // Add a number data validation
            label = new Label(0, 152, "Added number validation 2.718 < x < 3.142");
            sheet.addCell(label);
            b = new Blank(1, 152);
            wcf = new WritableCellFeatures();
            wcf.setNumberValidation(2.718, 3.142, WritableCellFeatures.BETWEEN);
            b.setCellFeatures(wcf);
            sheet.addCell(b);

            // Modify the text in the first cell with a comment
            cell = sheet.getWritableCell(0, 156);
            l = (Label)cell;
            l.setString("Label text modified");

            cell = sheet.getWritableCell(0, 157);
            wcf = cell.getWritableCellFeatures();
            wcf.setComment("modified comment text");

            cell = sheet.getWritableCell(0, 158);
            wcf = cell.getWritableCellFeatures();
            wcf.removeComment();

            // Modify the validation contents of the row 173
            cell = sheet.getWritableCell(0, 172);
            wcf = cell.getWritableCellFeatures();
            Range r = wcf.getSharedDataValidationRange();
            Cell botright = r.getBottomRight();
            sheet.removeSharedDataValidation(cell);
            al = new ArrayList();
            al.Add("Stanley Featherstonehaugh Ukridge");
            al.Add("Major Plank");
            al.Add("Earl of Ickenham");
            al.Add("Sir Gregory Parsloe-Parsloe");
            al.Add("Honoria Glossop");
            al.Add("Stiffy Byng");
            al.Add("Bingo Little");
            wcf.setDataValidationList(al);
            cell.setCellFeatures(wcf);
            sheet.applySharedDataValidation(cell, botright.getColumn() - cell.getColumn(), 1);//botright.getRow() - cell.getRow());
        }
Example #5
0
        /**
         * Write out loads of labels, in order to test the shared string table
         */
        private void writeLabelsSheet(WritableSheet ws)
        {
            ws.getSettings().setProtected(true);
            ws.getSettings().setPassword("jxl");
            ws.getSettings().setVerticalFreeze(5);
            ws.getSettings().setDefaultRowHeight(25 * 20);

            WritableFont wf = new WritableFont(WritableFont.ARIAL, 12);
            wf.setItalic(true);

            WritableCellFormat wcf = new WritableCellFormat(wf);

            CellView cv = new CellView();
            cv.setSize(25 * 256);
            cv.setFormat(wcf);
            ws.setColumnView(0, cv);
            ws.setColumnView(1, 15);

            for (int i = 0; i < 61; i++)
                {
                Label l1 = new Label(0, i, "Common Label");
                Label l2 = new Label(1, i, "Distinct label number " + i);
                ws.addCell(l1);
                ws.addCell(l2);
                }

            // Frig this test record - it appears exactly on the boundary of an SST
            // continue record

            Label l3 = new Label(0, 61, "Common Label", wcf);
            Label l4 = new Label(1, 61, "1-1234567890", wcf);
            Label l5 = new Label(2, 61, "2-1234567890", wcf);
            ws.addCell(l3);
            ws.addCell(l4);
            ws.addCell(l5);

            for (int i = 62; i < 200; i++)
                {
                Label l1 = new Label(0, i, "Common Label");
                Label l2 = new Label(1, i, "Distinct label number " + i);
                ws.addCell(l1);
                ws.addCell(l2);
                }

            // Add in a last label which doesn't take the jxl.common.format
            wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);
            wf.setColour(Colour.RED);
            WritableCellFormat wcf2 = new WritableCellFormat(wf);
            wcf2.setWrap(true);
            Label l = new Label(0, 205, "Different format", wcf2);
            ws.addCell(l);

            // Add some labels to column 5 for autosizing
            Label l6 = new Label(5, 2, "A column for autosizing", wcf2);
            ws.addCell(l6);
            l6 = new Label(5, 4, "Another label, longer this time and " +
                           "in a different font");
            ws.addCell(l6);

            CellView cf = new CellView();
            cf.setAutosize(true);
            ws.setColumnView(5, cf);
        }