farpoint [转]

FARPOINT 是一款模拟EXCEL的控件。 
可以根据用户的要求实现很大部份的EXCEL操作。包括多个子表、表格风格定义、公式计算、排序、分组等等都可以实现。

在设计时先将样式保存为***.xml格式，这样可以在代码中动态加载FarPoint。
cs代码：
private string strPath = Application.StartupPath + @"\AuditXml\";
   //表头(hs指的是在设计FarPoint时自己定义的SheetItem)
   private FarPoint.Win.Spread.NamedStyle fphStyle = new FarPoint.Win.Spread.NamedStyle("hs");
   //标题居中对齐(tcs指的是在设计FarPoint时自己定义的SheetItem)
   private FarPoint.Win.Spread.NamedStyle fptcStyle = new FarPoint.Win.Spread.NamedStyle("tcs");
   //标题居左对齐(tls指的是在设计FarPoint时自己定义的SheetItem)
   private FarPoint.Win.Spread.NamedStyle fptlStyle = new FarPoint.Win.Spread.NamedStyle("tls");
   //单元格样式(ds指的是在设计FarPoint时自己定义的SheetItem)
   private FarPoint.Win.Spread.NamedStyle fpdStyle = new FarPoint.Win.Spread.NamedStyle("ds");

public AuditingContent()
   {
    //
    // Windows 窗体设计器支持所必需的
    //
    InitializeComponent();

    //
    // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
    //

    SetHeadStyle( fphStyle );
    SetTitleCStyle( fptcStyle );
    SetTitleLStyle( fptlStyle );
    SetDataStyle( fpdStyle );
   }

#region 设置报表、单元格的样式
   /// <summary>
   /// 设置表头的样式
   /// </summary>
   /// <param name="ns"></param>
   private void SetHeadStyle( FarPoint.Win.Spread.NamedStyle ns )
   {
    FarPoint.Win.Spread.CellType.TextCellType cellType = new FarPoint.Win.Spread.CellType.TextCellType();
    cellType.WordWrap = true;
    ns.CellType = cellType;

    ns.BackColor = Color.LavenderBlush;
    ns.Locked = true;
    ns.Font = new Font( "黑体", 15 );
    ns.HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
    ns.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
   }

   /// <summary>
   /// 设置居中标题的样式
   /// </summary>
   /// <param name="ns"></param>
   private void SetTitleCStyle( FarPoint.Win.Spread.NamedStyle ns )
   {
    FarPoint.Win.Spread.CellType.TextCellType cellType = new FarPoint.Win.Spread.CellType.TextCellType();
    cellType.WordWrap = true;
    ns.CellType = cellType;

    ns.BackColor = Color.LavenderBlush;
    ns.Locked = true;
    ns.HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
    ns.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
   }

   /// <summary>
   /// 设置居左标题的样式
   /// </summary>
   /// <param name="ns"></param>
   private void SetTitleLStyle( FarPoint.Win.Spread.NamedStyle ns )
   {
    FarPoint.Win.Spread.CellType.TextCellType cellType = new FarPoint.Win.Spread.CellType.TextCellType();
    cellType.WordWrap = true;
    ns.CellType = cellType;

    ns.BackColor = Color.LavenderBlush;
    ns.Locked = true;
    ns.HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Left;
    ns.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
   }

   /// <summary>
   /// 设置数据单元格的样式
   /// </summary>
   /// <param name="ns"></param>
   private void SetDataStyle( FarPoint.Win.Spread.NamedStyle ns )
   {
    FarPoint.Win.Spread.CellType.TextCellType cellType = new FarPoint.Win.Spread.CellType.TextCellType();
    cellType.WordWrap = true;
    ns.CellType = cellType;

    ns.Locked = false;
    ns.HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Right;
    ns.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
   }

   private void SetSheetStyle( FarPoint.Win.Spread.SheetView sv )
   {
    sv.DefaultStyle.Border = new FarPoint.Win.BevelBorder( FarPoint.Win.BevelBorderType.Raised, Color.WhiteSmoke, Color.DarkGray, 1, true, true, true, true );
    sv.GrayAreaBackColor = Color.Lavender;
   }

   /// <summary>
   /// 显示报表
   /// </summary>
   /// <param name="path">XML文件名称</param>
   /// <param name="fps">报表显示控件</param>
   private void Show_Report( string XmlName, FarPoint.Win.Spread.FpSpread fps )
   {
    if( !File.Exists( strPath + XmlName ) )
    {
     MessageBox.Show("要加载的报表Xml文件样式不存在，加载报表失败！") ;
     return ;
    }
    try
    {  
     fps.Open( strPath + XmlName );       
   
     fps.NamedStyles.Add( fphStyle );
     fps.NamedStyles.Add( fptcStyle );
     fps.NamedStyles.Add( fptlStyle );
     fps.NamedStyles.Add( fpdStyle );

     SetSheetStyle( fps.ActiveSheet );

     fps.ActiveSheet.GrayAreaBackColor = Color.Lavender;
    }
    catch
    {
     MessageBox.Show("加载报表样式失败！") ;
    }
   }

   #endregion

FpSpread常用属性:

1.AllowCellOverflow 获取或设置单元格里的内容超出时,是否放在邻近的单元格.

2.AllowColumnMove获取或设置是否可以移动列.

3.AllowDragDrop获取或设置是否可以拖动选定的对象,如行,单元格,选择的区域,里面的值也会跟着变化.

4.AllowDragFill获取或设置是否可以进行填充,类似于excel按住鼠标,进行填充,但是没有发现序列的定义.

5.AllowEditOverflow返回或设置是否在编辑单元格时,显示超出的部分,AllowCellOverflow 不是编辑状态都显示.

6.AllowRowMove返回或设置是否可以移动行,AllowDragDrop属性也可以实现此要求,但是AllowDragDrop是针对所有的对象.

7.AllowSheetMove返回或设置是否可以移动工作表.

8.AllowUndo返回或设置是否启动撤消和重做功能.

9.AllowUserFormulas返回或设置用户是否可以输入公式.True输入 "=" 后,像EXCEL一样.False时,=A1+A2当作字符串来处理.

10.AllowUserZoom返回或设置是否允许用户用Ctrl+鼠标来进行缩放.

11.AutoClipboard返回或设置是否允许快捷键进行复制和粘贴.

12.EditModePermanent返回或设置单元格具有焦点时,直接进入输入状态,不过,我发现,如果设为True则,不能区域的选择了.

13.EditModeReplace返回或设置往单元格输入内容是替换模式,还是追加模式.

Sheet常用属性:

1.AllowGroup返回或设置是否可以进行分组,需要把GroupBarVisible,AllowColumnMove 属性设为True不过,我还没有发现这个功能,有什么特别的用处.

2.AllowNoteEdit返回或设置是否可以编辑批注.不过,我发现只能把NoteStyle = FarPoint.Win.Spread.NoteStyle.StickyNote这个样式才可以编辑.

  Me.FpSpread1.ActiveSheet.SetNote(Me.FpSpread1.ActiveSheet.ActiveCell.Row.Index, _

                        Me.FpSpread1.ActiveSheet.ActiveCell.Column.Index, _

                        System.Net.Dns.GetHostName & ":" & Now.ToString)

3.columnCount,RowCount列和行的总数.

4.DisplayZero返回或设置是否显示0值,不过,如果不显示,是字符串的0还不知道,怎么显示!

5.FrozenColumnCount ,FrozenRowCount有点类似于EXCEL的功能,在向下滚动或水平滚动时,指定数定的行或列,不会跟着动,有点像Excel里的功能.相当于表头吧,

6.FrozenTrailingColumnCount ,FrozenTrailingRowCount 与上面两个类似,只是固定的行或列位置相反而已.

7.Iteration返回或设置公式,循环引用时,是否提示为错误,也是就是在单元格是显示#value,注意默认是False

8.OperationMode返回或设置选择模式 farpoint web form 3.0　
分类：FpSpread 字号： 大大  中中  小小  设置行数列数 
        FpSpread1.ActiveSheetView.ColumnCount = 3;
        FpSpread1.ActiveSheetView.RowCount = 11;
分页
        FpSpread1.ActiveSheetView.AllowPage = false;
列宽
        FpSpread1.ActiveSheetView.Columns[0].Width = 160;
        FpSpread1.ActiveSheetView.Columns[1].Width = 200;
        FpSpread1.ActiveSheetView.Columns[2].Width = (int)(FpSpread1.Width.Value - 360 -                       FpSpread1.RowHeader.Width - 2);
指定单元格的读取和赋值
 
设置行数列数 
        FpSpread1.ActiveSheetView.ColumnCount = 3;
        FpSpread1.ActiveSheetView.RowCount = 11;
分页
        FpSpread1.ActiveSheetView.AllowPage = false;
列宽
        FpSpread1.ActiveSheetView.Columns[0].Width = 160;
        FpSpread1.ActiveSheetView.Columns[1].Width = 200;
        FpSpread1.ActiveSheetView.Columns[2].Width = (int)(FpSpread1.Width.Value - 360 -                       FpSpread1.RowHeader.Width - 2);
指定单元格的读取和赋值
         FpSpread1.ActiveSheetView.Cells[0, 1].Text = "Custom Cell Editor/Renderer";
         FpSpread1.ActiveSheetView.Cells[0, 2].Text = "Values from editor are RGB of BackColor";
指定控件区域大小
FpSpread1.Height = Unit.Pixel(300);
FpSpread1.Width = Unit.Pixel(763);
设置列的类型
FarPoint.Web.Spread.CheckBoxCellType objIntCell = new FarPoint.Web.Spread.CheckBoxCellType();
FpSpread1.Sheets[0].Columns[0].CellType = objIntCell;
定义边框部分
FpSpread1.BorderColor = Color.Green;
FpSpread1.BorderStyle = BorderStyle.Dashed;
FpSpread1.BorderWidth = System.Web.UI.WebControls.Unit.Pixel(4);
添加一个新的工作表
FarPoint.Web.Spread.SheetView newsheet = new FarPoint.Web.Spread.SheetView();
newsheet.SheetName = "North";
newsheet.ColumnCount = 10;
newsheet.RowCount = 100;
然后把刚才的工作表加到控件中
FpSpread1.Sheets.Add(newsheet);
移除一个工作表
FpSpread1.Sheets.Remove(FpSpread1.Sheets[1]);
指定一个工作表是否显示
FpSpread1.Sheets[1].Visible = false;
为指定的工作表设置背景
FpSpread1.Sheets[0].BackColor = Color.LightYellow;
设置页大小陈列15行 （不明白）
FpSpread1.ActiveSheetView.PageSize = 15;
指定网格线的样式
FpSpread1.Sheets[0].GridLineColor = Color.Red;
FpSpread1.Sheets[0].GridLines = GridLines.Horizontal;
定制工作表的角落 （不明白）
FpSpread1.Sheets[0].SheetCornerStyle.BorderColor = Color.Green;
FpSpread1.Sheets[0].SheetCornerStyle.BorderStyle = BorderStyle.Dashed;
FpSpread1.Sheets[0].SheetCornerStyle.BorderWidth = 4;
为工作表指定使用哪个皮肤
FarPoint.Web.Spread.DefaultSkins.Colorful2. Apply(FpSpread1.Sheets[0]); 
自定义皮肤，并赋予工作表
FarPoint.Web.Spread.SheetSkin myskin = new FarPoint.Web.Spread.SheetSkin("MySkin", Color.BlanchedAlmond, Color.Bisque, Color.Navy, 2, Color.Blue, GridLines.Both, Color.Beige, Color.BurlyWood, Color.AntiqueWhite, Color.Brown, Color.Bisque, Color.Bisque, true, true, true, true, false);
myskin.Apply(FpSpread1.Sheets[1]);
增加行列数
FpSpread1.Sheets[0].AddColumns(6,2);
移除行列
FpSpread1.Sheets[0].RemoveRows(6,2);
设置工作表中指定行列显示或者隐藏
FpSpread1.Sheets[0].SetRowVisible(1, false);
FpSpread1.Sheets[0].SetColumnVisible(2, false);
设置工作表默认行高列宽，以及单独指定行号列宽
FpSpread1.Sheets[0].DefaultColumnWidth = 50;
FpSpread1.Sheets[0].Columns[1].Width = 100;
设置TopRow 
SheetView.TopRow = 4;
创建多个不同的行模板
以下是设置行模板为3，第一行是默认样式 然后为第二 三行赋予新的样式 下面的行将重复此样式FpSpread1.Sheets[0].AlternatingRows.Count = 3;
FpSpread1.Sheets[0].AlternatingRows[1].BackColor = Color.LightBlue;
FpSpread1.Sheets[0].AlternatingRows[1].ForeColor = Color.DarkBlue;
FpSpread1.Sheets[0].AlternatingRows[2].BackColor = Color.Orange;
FpSpread1.Sheets[0].AlternatingRows[2].ForeColor = Color.DarkRed;
行扩充的处理 如果一行有有过多的数据 这些属性可以显示的样式
GetRowExpandable SetRowExpandable 
设置固定行的样式 首先定义一个样式
FarPoint.Web.Spread.StyleInfo darkstyle = new FarPoint.Web.Spread.StyleInfo();
darkstyle.BackColor = Color.Teal;
darkstyle.ForeColor = Color.Yellow;
然后将这个新样式设置到列头区域
FpSpread1.ActiveSheetView.ColumnHeader.DefaultStyle = darkstyle;
设置列头显示或者隐藏
FpSpread1.Sheets[0].ColumnHeader.Visible = false;
设置列头文本
FpSpread1.Sheets[0].ColumnHeader.Columns[0].Label = "North";
FpSpread1.Sheets[0].ColumnHeader.Columns[1].Label = "South";
定制行列头区域空的部分
FarPoint.Web.Spread.SheetView sv = FpSpread1.ActiveSheetView; 
sv.HeaderGrayAreaColor = Color.Red;
设置行列头数
FpSpread1.Sheets[0].ColumnHeader.RowCount = 3;
FpSpread1.Sheets[0].RowHeader.ColumnCount = 2;
合并
FpSpread1.Sheets[0].ColumnHeaderSpanModel.Add(0, 0, 1, 8);
FpSpread1.Sheets[0].RowHeaderSpanModel.Add(0,0,12,1);
表头文本赋值
FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "Fiscal Year 2005";
FpSpread1.Sheets[0].RowHeader.Cells[0, 0].Text = "Branch #";
FpSpread1.Sheets[0].ColumnHeader.Cells[1, 0].Text = "1st Quarter"; 
指定单元格背景
FpSpread1.Sheets[0].Cells[0,0].BackColor = Color.Azure;
FpSpread1.Sheets[0].Cells[2,2,3,3].BackColor = Color.Bisque;
对齐样式
FpSpread1.Sheets[0].Cells[0,0].HorizontalAlign = HorizontalAlign.Right;
FpSpread1.Sheets[0].Cells[0,0].VerticalAlign = VerticalAlign.Top;
FpSpread1.Sheets[0].Cells[1,1,2,2].HorizontalAlign = HorizontalAlign.Center; 
定义单元格边界
fpSpread1.Sheets[0].Cells[4, 3].Border = new FarPoint.Web.Spread.Border(System.Web.UI.WebControls.BorderStyle. Double, Color.DarkBlue, 2); 
定义单元格内外边距
FarPoint.Web.Spread.Cell mycell;
FarPoint.Web.Spread.Inset margin = new FarPoint.Web.Spread.Inset(20, 40, 50, 20);
mycell = FpSpread1.Cells[0, 0];
mycell.Value = "Margin";
mycell.Locked = true;
mycell.Margin = margin;
FpSpread1.ActiveSheetView.Rows[0].Height = 80;
自定义样式

FarPoint.Web.Spread.NamedStyle backstyle = new FarPoint.Web.Spread.NamedStyle("BlueBack");

backstyle.BackColor = Color.Blue;

FarPoint.Web.Spread.NamedStyle text1style = new FarPoint.Web.Spread.NamedStyle("OrangeText", "BlueBack");

text1style.ForeColor = Color.Orange;

FarPoint.Web.Spread.NamedStyle text2style = new FarPoint.Web.Spread.NamedStyle("YellowText", "BlueBack");

text2style.ForeColor = Color.Yellow;

FpSpread1.NamedStyles.Add(backstyle);

FpSpread1.NamedStyles.Add(text1style);

FpSpread1.NamedStyles.Add(text2style);

FpSpread1.ActiveSheetView.Cells[0,0,2,0].StyleName = "OrangeText";

FpSpread1.ActiveSheetView.Cells[0,1,2,1].StyleName = "YellowText";

使用css

FarPoint.Web.Spread.GeneralCellType mycelltype =       new FarPoint.Web.Spread.GeneralCellType();

myCellType.CssClass = "myCssClass";

FpSpread1.ColumnHeader.Cells[0, 0].CellType = myCellType;

FpSpread1.Cells[0, 1].CellType = myCellType;

创建一个区域

FarPoint.Web.Spread.Cell range1;

range1 = fpSpread1.ActiveSheetView.Cells[1, 1, 3, 3];

range1.Value = "Value Here";

range1.Note = "This is the note that describes the value.";

添加合并单元

FpSpread1.ActiveSheetView.Cells[1,1].Text = "These six cells are spanned.";

FpSpread1.ActiveSheetView.Cells[2,2].Text = "This is text in 2,2.";

FpSpread1.ActiveSheetView.AddSpanCell(1, 1, 2, 3);

自动合并相同行列

FpSpread1.Sheets[0].SetRowMerge(-1, FarPoint.Web.Spread.Model.MergePolicy.Always);

FpSpread1.Sheets[0].SetColumnMerge(-1, FarPoint.Web.Spread.Model.MergePolicy.Always);

设置滚动条

FpSpread1.HorizontalScrollBarPolicy = ScrollBarPolicy.Always;

FpSpread1.VerticalScrollBarPolicy = ScrollBarPolicy.AsNeeded;

设置滚动条的颜色

FpSpread1.ScrollBar3DLightColor = Color.Yellow;

FpSpread1.ScrollBarArrowColor = Color.Green;

FpSpread1.ScrollBarBaseColor = Color.Brown;

FpSpread1.ScrollBarDarkShadowColor = Color.Purple;

FpSpread1.ScrollBarFaceColor = Color.Orange;

FpSpread1.ScrollBarHighlightColor = Color.White;

FpSpread1.ScrollBarShadowColor = Color.Blue;

FpSpread1.ScrollBarTrackColor = Color.Pink;

展示AllowLoadOnDemand和LoadInitRowCount属性 
FpSpread1.Sheets[0].RowCount = 40;

FpSpread1.Sheets[0].AllowLoadOnDemand = True;

FpSpread1.Sheets(0).PageSize = 40

FpSpread1.Sheets[0].LoadInitRowCount = 10;

long i;

for (i = 1; i <= 20; i++)

{

FpSpread1.Sheets[0].Cells[i, 0].Value = i;

}

TabKey

FpSpread1.ProcessTab = false;

定义tab的样式

FpSpread1.Sheets.Count = 3;

FarPoint.Web.Spread.TabInfo().TabControlPolicy = FarPoint.Web.Spread.TabControlPolicy.Always;

FpSpread1.Tab.VisibleCount = 2;

FpSpread1.Tab.ScrollIncrement = 2;

FpSpread1.Tab.FirstVisibleTab = 1;

FpSpread1.Tab.TextColor = Color.Yellow;

FpSpread1.Tab.ActiveTabBackColor = Color.Green;

FpSpread1.Tab[0] = "First";

FpSpread1.Tab[1] = "Second";

FpSpread1.Tab[2] = "Third";

切换工作表是否ajax支持

FpSpread1.EnableAjaxCall = true;

FpSpread1.ClientAutoCalculation = true;

在工作表中搜索指定的内容
fpSpread1.Search(2,"Total",true,true,false,false,1,1,56,56,ref rowindx,ref colindx));