コード例 #1
0
ファイル: Styles.cs プロジェクト: ferrinsp/kbellfireapp
 private void initHyperlinkFont()
 {
     lock (this)
     {
         hyperlinkFont = new WritableFont(WritableWorkbook.HYPERLINK_FONT);
     }
 }
コード例 #2
0
ファイル: Styles.cs プロジェクト: ferrinsp/kbellfireapp
 private void initArial10Pt()
 {
     lock (this)
     {
         arial10pt = new WritableFont(WritableWorkbook.ARIAL_10_PT);
     }
 }
コード例 #3
0
ファイル: Styles.cs プロジェクト: ferrinsp/kbellfireapp
 /**
  * Constructor
  */
 public Styles()
 {
     arial10pt      = null;
     hyperlinkFont  = null;
     normalStyle    = null;
     hyperlinkStyle = null;
     hiddenStyle    = null;
 }
コード例 #4
0
        /**
         * 取得粗體設定
         * @param fontName
         * @return
         */

        private WritableFont.FontName getFont(string fontName)
        {
            if (StringUtil.NotEmpty(fontName))
            {
                return(WritableFont.createFont(fontName));
            }
            return(null);
        }
コード例 #5
0
        /**
         * Constructor.  Creates the predetermined list of fonts
         */
        public WritableFonts(WritableWorkbookImpl w)
            : base()
        {
            addFont(w.getStyles().getArial10Pt());

            // Create the default fonts
            WritableFont f = new WritableFont(WritableFont.ARIAL);

            addFont(f);

            f = new WritableFont(WritableFont.ARIAL);
            addFont(f);

            f = new WritableFont(WritableFont.ARIAL);
            addFont(f);
        }
コード例 #6
0
        private WritableCellFormat GetWritableFormat(TextStyle ts)
        {
            WritableCellFormat cellFormat = new WritableCellFormat();

            WritableFont font = new WritableFont(
                new WritableFont.FontName(ts.FontFamily),
                (int)ts.FontSize.points * _fontHeight,
                BoldStyle(ts),
                (ts.FontStyle == Engine.Style.FontStyleEnum.Italic),
                (ts.TextDecoration == Engine.Style.TextDecorationEnum.Underline) ? UnderlineStyle.SINGLE : UnderlineStyle.NO_UNDERLINE,
                (ts.Color == null) ? Colour.BLACK : getXlsColor(ts.Color, Colour.BLACK),
                ScriptStyle.NORMAL_SCRIPT);

            cellFormat.setFont(font);
            cellFormat.setAlignment(ExcelAlignmentFromRdlAlignment(ts.TextAlign));


            if (ts.BorderStyle != null && ts.BorderStyle != null)
            {
                BorderStyle bs = ts.BorderStyle;
                BorderColor bc = ts.BorderColor;

                cellFormat.setBorder(Border.TOP, ExcelBorderStyleFromRdlBorderStyle(bs.Top),
                                     (bc == null) ?  Colour.BLACK : getXlsColor(ts.BorderColor.Top, Colour.BLACK));
                cellFormat.setBorder(Border.BOTTOM, ExcelBorderStyleFromRdlBorderStyle(bs.Bottom),
                                     (bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Bottom, Colour.BLACK));
                cellFormat.setBorder(Border.LEFT, ExcelBorderStyleFromRdlBorderStyle(bs.Left),
                                     (bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Left, Colour.BLACK));
                cellFormat.setBorder(Border.RIGHT, ExcelBorderStyleFromRdlBorderStyle(bs.Right),
                                     (bc == null) ? Colour.BLACK : getXlsColor(ts.BorderColor.Right, Colour.BLACK));
            }
            if (ts.BackgroundColor != null)
            {
                cellFormat.setBackground(getXlsColor(ts.BackgroundColor, Colour.WHITE), Pattern.SOLID);
            }
            return(cellFormat);
        }
コード例 #7
0
        /// <summary>
        ///     設定 Cell 格式 (Detal時, 沒有 tr 和 td, 此時兩個參數傳入同一個物件, 不影響判斷)
        /// </summary>
        /// <param name="trInfo" />
        /// <param name="tdInfo" />
        private WritableCellFormat getCellFormat(AbstractStyleInfo trInfo, AbstractStyleInfo tdInfo)
        {
            //style 設定
            StyleInfo styleInfo = ((ExportConfigInfo)configInfo).StyleInfo;

            // 字體名稱
            WritableFont.FontName font = styleInfo.Font;
            if (tdInfo.Font != null)
            {
                font = tdInfo.Font;
            }
            else if (trInfo.Font != null)
            {
                font = trInfo.Font;
            }

            // 字體大小
            int size = 0;

            if (ExcelStringUtil.NotEmpty(tdInfo.Size))
            {
                size = Convert.ToInt32(ExcelStringUtil.SafeTrim(tdInfo.Size, "0"));
            }
            else if (ExcelStringUtil.NotEmpty(trInfo.Size))
            {
                size = Convert.ToInt32(ExcelStringUtil.SafeTrim(trInfo.Size, "0"));
            }
            if (size == 0)
            {
                size = Convert.ToInt32(styleInfo.Size);
            }

            // 粗體
            bool isBold = ("true".Equals(styleInfo.Bold, StringComparison.CurrentCultureIgnoreCase));

            if (ExcelStringUtil.NotEmpty(tdInfo.Bold))
            {
                isBold = ("true".Equals(tdInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
            }
            else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
            {
                isBold = ("true".Equals(trInfo.Bold, StringComparison.CurrentCultureIgnoreCase));
            }

            // 斜體
            bool isItalic = ("true".Equals(styleInfo.Italic, StringComparison.CurrentCultureIgnoreCase));

            if (ExcelStringUtil.NotEmpty(tdInfo.Italic))
            {
                isItalic = ("true".Equals(tdInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
            }
            else if (ExcelStringUtil.NotEmpty(trInfo.Bold))
            {
                isItalic = ("true".Equals(trInfo.Italic, StringComparison.CurrentCultureIgnoreCase));
            }

            // 底線
            UnderlineStyle underlineStyle = styleInfo.Underline;

            if (tdInfo.Underline != null)
            {
                underlineStyle = tdInfo.Underline;
            }
            else if (trInfo.Underline != null)
            {
                underlineStyle = trInfo.Underline;
            }

            // 字體顏色
            Colour color = styleInfo.Color;

            if (tdInfo.Color != null)
            {
                color = tdInfo.Color;
            }
            else if (trInfo.Color != null)
            {
                color = trInfo.Color;
            }

            // 水平位置
            Alignment align = styleInfo.Align;

            if (tdInfo.Align != null)
            {
                align = tdInfo.Align;
            }
            else if (trInfo.Align != null)
            {
                align = trInfo.Align;
            }

            // 垂直位置
            VerticalAlignment valign = styleInfo.Valign;

            if (tdInfo.Valign != null)
            {
                valign = tdInfo.Valign;
            }
            else if (trInfo.Valign != null)
            {
                valign = trInfo.Valign;
            }

            // 文字換行
            bool isTextWrap = ("true".Equals(styleInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));

            if (ExcelStringUtil.NotEmpty(tdInfo.Wrap))
            {
                isTextWrap = ("true".Equals(tdInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
            }
            else if (ExcelStringUtil.NotEmpty(trInfo.Wrap))
            {
                isTextWrap = ("true".Equals(trInfo.Wrap, StringComparison.CurrentCultureIgnoreCase));
            }

            // 邊線位置
            Border borderSide = styleInfo.BorderSide;

            if (tdInfo.BorderSide != null)
            {
                borderSide = tdInfo.BorderSide;
            }
            else if (trInfo.BorderSide != null)
            {
                borderSide = trInfo.BorderSide;
            }

            // 邊線樣式
            BorderLineStyle borderStyle = styleInfo.BorderStyle;

            if (tdInfo.BorderStyle != null)
            {
                borderStyle = tdInfo.BorderStyle;
            }
            else if (trInfo.Valign != null)
            {
                borderStyle = trInfo.BorderStyle;
            }

            // 背景顏色
            Colour background = styleInfo.Background;

            if (tdInfo.Background != null)
            {
                background = tdInfo.Background;
            }
            else if (trInfo.Background != null)
            {
                background = trInfo.Background;
            }

            // 產生字型設定
            var writableFont = new WritableFont(font, size, (isBold) ? WritableFont.BOLD : WritableFont.NO_BOLD,
                                                isItalic, underlineStyle, color);

            // 資料列cell格式
            var writableCellFormat = new WritableCellFormat(writableFont);

            // 水平置中
            writableCellFormat.setAlignment(align);
            // 垂直置中
            writableCellFormat.setVerticalAlignment(valign);
            // 換行
            writableCellFormat.setWrap(isTextWrap);
            // 背景顏色
            writableCellFormat.setBackground(background);
            // 邊線
            writableCellFormat.setBorder(borderSide, borderStyle);

            return(writableCellFormat);
        }
コード例 #8
0
        /**
         * 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());
        }