//======================================================================
//
// Copyright (C) 2007-2008 RaySoft
// All rights reserved
// guid1: 4b887cc8-ac8c-4397-bff3-628761c59f48
// guid2: 80f46765-0f16-44c4-a542-2b7eb38522f7
// guid3: 923c4a79-98ea-4148-8d1a-e13993a55545
// guid4: da27be46-708a-453f-aa4d-25e96d116917
// guid5: 05158992-6744-44d1-9bcb-cb422e35afbf
// CLR版本: 2.0.50727.5448
// 新建项输入的名称: SpreadHelper
// 机器名称: RAY-PC
// 注册组织名:
// 命名空间名称: RaySoft.Common
// 文件名: SpreadHelper
// 当前系统时间: 2011/11/15 12:39:08
// 当前登录用户名: Administrator
// 创建年份: 2011
//
// created by Ray at 2011/11/15 12:39:08
//
//======================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using FarPoint.Win;
using FarPoint.Win.Spread;
using System.Windows.Forms;
namespace Raysoft.Framework.Spread
{
public class SpreadHelper
{
#region 设置单元格字体样式
/// <summary>
/// 设置单元格字体样式
/// </summary>
/// <param name="fpSpread"></param>
/// <param name="cellrange"></param>
/// <param name="fontFamliy"></param>
/// <param name="fontStyle"></param>
/// <param name="fontSize"></param>
/// <returns></returns>
public static bool SetFont(FpSpread fpSpread, FarPoint.Win.Spread.Model.CellRange[] cellrange, string fontFamliy, FontStyle fontStyle, float fontSize)
{
foreach (FarPoint.Win.Spread.Model.CellRange range in cellrange)
{
for (int i = range.Row; i < range.RowCount + range.Row; i++)
{
for (int j = range.Column; j < range.ColumnCount + range.Column; j++)
{
Font font = new Font(fontFamliy, fontSize, fontStyle);
fpSpread.ActiveSheet.Cells[i, j].Font = font;
}
}
}
return true;
}
#endregion
#region 设置单元格表格线
/// <summary>
/// 设置单元格表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="leftBorder"></param>
/// <param name="topBorder"></param>
/// <param name="rightBorder"></param>
/// <param name="bottomBorder"></param>
/// <returns></returns>
public static bool SetComplexSingeCellBorders(FpSpread fps, int row, int col, FarPoint.Win.ComplexBorderSide leftBorder, FarPoint.Win.ComplexBorderSide topBorder, FarPoint.Win.ComplexBorderSide rightBorder, FarPoint.Win.ComplexBorderSide bottomBorder)
{
if (leftBorder != null & topBorder == null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, null, null);
}//Left
else if (leftBorder == null & topBorder != null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, null, null);
}//Top
else if (leftBorder == null & topBorder == null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, rightBorder, null);
}//Right
else if (leftBorder == null & topBorder == null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, null, bottomBorder);
}//Bottom
else if (leftBorder != null & topBorder != null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, null, null);
}//Left + Top
else if (leftBorder != null & topBorder == null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, rightBorder, null);
}//Left + Right
else if (leftBorder != null & topBorder == null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, null, bottomBorder);
}//Left + Bottom
else if (leftBorder == null & topBorder != null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, rightBorder, null);
}//Top + Right
else if (leftBorder == null & topBorder != null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, null, bottomBorder);
}//Top + Bottom
else if (leftBorder == null & topBorder == null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, rightBorder, bottomBorder);
}//Right + Bottom
else if (leftBorder != null & topBorder != null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, rightBorder, null);
}//Left + Top + Right
else if (leftBorder != null & topBorder != null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, null, bottomBorder);
}//Left + Top + Bottom
else if (leftBorder != null & topBorder == null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, null, bottomBorder);
}//Left + Right + Bottom
else if (leftBorder == null & topBorder != null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, rightBorder, bottomBorder);
}//Top + Right + Bottom 原版中没有这一项.
else if (leftBorder != null & topBorder != null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, leftBorder, bottomBorder);
}//Left + Top + Right + Bottom
else
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, null, null);
}//All Null
return true;
}
#endregion
#region 清除表格线
/// <summary>
/// 清除表格线
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool ClearAllCellBoders(FpSpread fps)
{
fps.ActiveSheet.HorizontalGridLine = new GridLine(FarPoint.Win.Spread.GridLineType.None);
fps.ActiveSheet.VerticalGridLine = new GridLine(FarPoint.Win.Spread.GridLineType.None);
return true;
}
#endregion
#region 设置单元格对齐
/// <summary>
/// 设置单元格对齐
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="HorizontalAlignment"></param>
/// <param name="VerticalAlignment"></param>
/// <returns></returns>
public static bool setCellAlignmentForRange(FpSpread fps, int row, int col, int row2, int col2, CellHorizontalAlignment HorizontalAlignment, CellVerticalAlignment VerticalAlignment)
{
fps.ActiveSheet.Cells[row, col, row2, col2].HorizontalAlignment = HorizontalAlignment;
fps.ActiveSheet.Cells[row, col, row2, col2].VerticalAlignment = VerticalAlignment;
return true;
}
#endregion
#region 设置单元格的值
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="textVal"></param>
/// <returns></returns>
public static bool setValue(FpSpread fps, int row, int col, string textVal)
{
fps.ActiveSheet.SetValue(row, col, textVal);
return true;
}
#endregion
#region 设置单元格背景颜色
/// <summary>
/// 设置单元格背景颜色
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="row2"></param>
/// <param name="col"></param>
/// <param name="col2"></param>
/// <param name="theBackColor"></param>
/// <returns></returns>
public static bool setBackColorForRange(FpSpread fps, int row, int row2, int col, int col2, Color theBackColor)
{
fps.ActiveSheet.Cells[row, col, row2, col2].BackColor = theBackColor;
return true;
}
#endregion
#region 设置单元格前景颜色
/// <summary>
/// 设置单元格前景颜色
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="theForceColor"></param>
/// <returns></returns>
public static bool setForceColorForRange(FpSpread fps, int row, int col, int row2, int col2, Color theForceColor)
{
fps.ActiveSheet.Cells[row, col, row2, col2].ForeColor = theForceColor;
return true;
}
#endregion
#region 设置单元格文本
/// <summary>
/// 设置单元格的文本
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="sText"></param>
/// <returns></returns>
public static bool setText(FpSpread fps, int row, int col, string sText)
{
fps.ActiveSheet.SetValue(row, col, sText);
return true;
}
#endregion
#region 设置指定行行高
public static bool setRowHeightForRange(FpSpread fps, int row, int col, int row2, int col2, Single rowHeightInPixels)
{
fps.ActiveSheet.Cells[row, col, row2, col2].Row.Height = rowHeightInPixels;
return true;
}
#endregion
#region 设置指定列列宽
public static bool setColumnWidthForRange(FpSpread fps, int row, int col, int row2, int col2, Single columnWidthInPixels)
{
fps.ActiveSheet.Cells[row, col, row2, col2].Column.Width = columnWidthInPixels;
return true;
}
#endregion
#region 设置单元格简单表格线
/// <summary>
/// 设置单元格简单表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setSingleCellBorders(FpSpread fps, int row, int col, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide topBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col].Border = new ComplexBorder(leftBorder, topBorder, rightBorder, bottomBorder);
return true;
}
#endregion
#region 设置指定区域简单表格线
/// <summary>
/// 设置指定区域简单表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="?"></param>
/// <param name="?"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool SetSingleCellBordersForRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide topBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(leftBorder, topBorder, rightBorder, bottomBorder);
return true;
}
#endregion
#region 设置指定区域简单左边线
/// <summary>
/// 设置指定区域简单左边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setLeftBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(leftBorder, null, null, null);
return true;
}
#endregion
#region 设置指定区域简单上边线
/// <summary>
/// 设置指定区域简单上边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setTopBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide TopBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, TopBorder, null, null);
return true;
}
#endregion
#region 设置指定区域简单右边线
/// <summary>
/// 设置指定区域简单右边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setRightBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, null, rightBorder, null);
return true;
}
#endregion
#region 设置指定区域简单下边线
/// <summary>
/// 设置指定区域简单下边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setBottomBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, null, null, bottomBorder);
return true;
}
#endregion
#region 设置指定单元格标签
/// <summary>
/// 设置指定单元格标签
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="tag"></param>
/// <returns></returns>
public static bool setCellTag(FpSpread fps, int row, int col, string tag)
{
fps.ActiveSheet.Models.Data.SetTag(row, col, tag);
return true;
}
#endregion
#region 得到指定单元格标签
/// <summary>
/// 得到指定单元格标签
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="tag"></param>
/// <returns></returns>
public static bool getCellTag(FpSpread fps, int row, int col)
{
fps.ActiveSheet.Models.Data.GetTag(row,col);
return true;
}
#endregion
private SpreadHelper() { }
/// <summary>
/// 通过单元格名称返回当前列号
/// </summary>
/// <param name="CellName">单元格名称</param>
/// <returns></returns>
public static int getColumnNum(string CellName)
{
int retVal = 0;
string rowNum = "";
Regex r = new Regex(@"[a-zA-Z]+");
Match m = r.Match(CellName);
rowNum = m.Value.ToUpper();
char[] ch = rowNum.ToCharArray();
for (int i = 0; i < rowNum.Length; i++)
{
retVal += ((ch[i] - 'A') + 1) * (int)Math.Pow(26, rowNum.Length - 1 - i);
}
retVal = retVal - 1;
return retVal;
}
/// <summary>
/// 通过单元格名称返回当前行号
/// </summary>
/// <param name="CellName">单元格名称</param>
/// <returns></returns>
public static int getRowNum(string CellName)
{
int retVal = 0;
Regex r = new Regex(@"[0-9]+");
Match m = r.Match(CellName);
retVal = int.Parse(m.Value) - 1;
return retVal;
}
/// <summary>
/// 没有使用
/// </summary>
/// <param name="CellName"></param>
/// <returns></returns>
public static int[] GetCellID(string CellName)
{
int[] retVal = new int[2];
string rowNum = "";
Regex rRow = new Regex(@"[a-zA-Z]+");
Match mRow = rRow.Match(CellName);
rowNum = mRow.Value.ToUpper();
char[] ch = rowNum.ToCharArray();
for (int i = 0; i < rowNum.Length; i++)
{
retVal[0] += ((ch[i] - 'A') + 1) * (int)Math.Pow(26, rowNum.Length - 1 - i);
}
retVal[0] = retVal[0] - 1;
Regex rCol = new Regex(@"[0-9]+");
Match mCol = rCol.Match(CellName);
retVal[1] = int.Parse(mCol.Value.ToString()) - 1;
return retVal;
}
//public static bool addSheets(FpSpread fps, int numOfSheets, int startingNumberOfColumns, int startingNumberOfRows, bool protectionLikeExcelTF, Color sheetBackColor, HeaderAutoText rowHeaderAutoText, HeaderAutoText ColumnHeaderAutoText)
//{
// //'Adds a Sheet for Each Question and Sets the number of Rows and Column
// int x, y, z;
// fps.Sheets.Count = 0;
// //'Create the Sheets
// for int x=0,
//}
//Public Function AddSheets(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal NumberOfSheets As Integer, ByVal StartingNumberOfColumns As Integer, ByVal StartingNumberOfRows As Integer, ByVal ProtectionLikeExcelTF As Boolean, ByVal SheetBackColor As System.Drawing.Color, ByVal RowHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText, ByVal ColumnHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText) As Boolean
//Dim x As Integer
//Dim y As Integer
//Dim z As Integer
//SSControlToUse.Sheets.Count = 0
//'Create the Sheets
//For x = 0 To NumberOfSheets
//Dim s As New FarPoint.Win.Spread.SheetView
//s.ColumnCount = StartingNumberOfColumns
//s.RowCount = StartingNumberOfRows
//SSControlToUse.Sheets.Add(s)
//Dim info As New FarPoint.Win.Spread.StyleInfo
//'Set the BackColor
//info.BackColor = SheetBackColor
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).DefaultStyle = info
//'Set Protection Scheme to be like Excels for each sheet
//For y = 0 To (StartingNumberOfRows - 1)
//For z = 0 To (StartingNumberOfColumns - 1)
//s.Cells(y, z).Locked = ProtectionLikeExcelTF
//Next z
//Next y
//SSControlToUse.Sheets(x).RowHeaderAutoText = RowHeaderAutoText
//SSControlToUse.Sheets(x).ColumnHeaderAutoText = ColumnHeaderAutoText
//Next x
//AddSheets = True
//End Function 'AddSheets
//Public Function AddSingleSheet(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal StartingNumberOfColumns As Integer, ByVal StartingNumberOfRows As Integer, ByVal ProtectionLikeExcelTF As Boolean, ByVal SheetBackColor As System.Drawing.Color, ByVal RowHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText, ByVal ColumnHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText) As Boolean
//'Adds a Sheet for Each Question and Sets the number of Rows and Column
//Dim x As Integer
//Dim y As Integer
//'Create the Sheets
//Dim s As New FarPoint.Win.Spread.SheetView
//s.ColumnCount = StartingNumberOfColumns
//s.RowCount = StartingNumberOfRows
//SSControlToUse.Sheets.Add(s)
//Dim info As New FarPoint.Win.Spread.StyleInfo
//'Set the BackColor
//info.BackColor = SheetBackColor
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).DefaultStyle = info
//'Set Protection Scheme to be like Excels for each sheet
//For x = 0 To (StartingNumberOfRows - 1)
//For y = 0 To (StartingNumberOfColumns - 1)
//s.Cells(x, y).Locked = ProtectionLikeExcelTF
//Next y
//Next x
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).RowHeaderAutoText = RowHeaderAutoText
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).ColumnHeaderAutoText = ColumnHeaderAutoText
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).GrayAreaBackColor = Color.SlateGray
//' Set operation mode and let users select multiple blocks of cells
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).OperationMode = FarPoint.Win.Spread.OperationMode.Normal
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).SelectionPolicy = FarPoint.Win.Spread.Model.SelectionPolicy.MultiRange
//AddSingleSheet = True
//End Function 'AddSingleSheet
//Public Function SSControlGlobals(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal EditModePermanentEntireWorkBookTF As Boolean, ByVal AllowCellOverFlowEntireWorkBookTF As Boolean, ByVal ControlsTabStripPolicy As FarPoint.Win.Spread.TabStripPolicy, ByVal TabStripPercentToShow As Single) As Boolean
//' Set the sheet tabs policy
//SSControlToUse.TabStripPolicy = ControlsTabStripPolicy
//' Set the Tab Strip width to: .??
//SSControlToUse.TabStripRatio = TabStripPercentToShow
//'Eliminate the focus rectangle for the ENTIRE Control
//SSControlToUse.EditModePermanent = EditModePermanentEntireWorkBookTF
//'Allow Cell Text Overflow for the ENTIRE Control
//SSControlToUse.AllowCellOverflow = AllowCellOverFlowEntireWorkBookTF
//SSControlToUse.SelectionBlockOptions = FarPoint.Win.Spread.SelectionBlockOptions.Cells
//End Function
//Public Function TurnProtectionOnOffForAllSheetsInControl(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal TurnProtectionOn As Boolean) As Boolean
//Dim x As Integer
//Dim NumberOfSheets As Int16 = SSControlToUse.Sheets.Count
//For x = 0 To NumberOfSheets - 1
//SSControlToUse.Sheets(x).Protect = TurnProtectionOn
//Next x
//End Function
//Public Function SetFont(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle) As Boolean
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SetFont = True
//End Function 'SetFontBold
//Public Function SetCellSpan(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal StartRow As Integer, ByVal StartCol As Integer, ByVal RowsToSpanCount As Integer, ByVal ColsToSpanCount As Integer) As Boolean
//SSControlToUse.ActiveSheet.AddSpanCell(StartRow, StartCol, RowsToSpanCount, ColsToSpanCount)
//SetCellSpan = True
//End Function 'SetCellSpan
//Public Function SetCellTypeTextForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal MultilineTF As Boolean, ByVal WordWrapTF As Boolean, ByVal ReadOnlyTF As Boolean, ByVal TypeOfScrollBars As System.Windows.Forms.ScrollBars, ByVal MaximumNumberOfCharacters As Int16, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.TextCellType
//ct.Multiline = MultilineTF
//ct.WordWrap = WordWrapTF
//ct.ReadOnly = ReadOnlyTF
//ct.ScrollBars = TypeOfScrollBars
//ct.MaxLength = MaximumNumberOfCharacters
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeTextForRange = True
//End Function 'SetCellTypeTextForRange
//Public Function SetCellTypeGeneralForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal WordWrapTF As Boolean, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.GeneralCellType
//ct.WordWrap = WordWrapTF
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeGeneralForRange = True
//End Function 'SetCellTypeGeneralForRange
//Public Function SetCellTypeButtonForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ButtonColor As Color, ByVal ButtonText As String, ByVal ButtonTextColor As Color, ByVal WordWrapTF As Boolean, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ButtonCellType
//ct.ButtonColor = ButtonColor
//ct.Text = ButtonText
//ct.TextColor = ButtonTextColor
//ct.WordWrap = WordWrapTF
//ct.TwoState = False
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeButtonForRange = True
//End Function 'SetCellTypeButtonForRange
//Public Function SetCellTypeRadioButtonForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal RadioButtonItemsTextVBTabbed As String, ByVal RadioBtnOrientation As FarPoint.Win.RadioOrientation, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.MultiOptionCellType
//'MUST Send In Item List as vbTab separated text
//Dim ItemList() As String = Split(RadioButtonItemsTextVBTabbed, vbTab)
//ct.Items = ItemList
//ct.Orientation = RadioBtnOrientation
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//'SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = RadioBtnBackColor
//'SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = RadioBtnForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeRadioButtonForRange = True
//End Function 'SetCellTypeRadioButtonForRange
//Public Function SetCellTypeCheckBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ChkBoxTextAlgnment As FarPoint.Win.ButtonTextAlign, ByVal ChkBoxTextIfTrue As String, ByVal ChkBoxTextIfFalse As String, ByVal ChkBoxBackColor As Color, ByVal ChkBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.CheckBoxCellType
//ct.TextAlign = ChkBoxTextAlgnment
//ct.TextTrue = ChkBoxTextIfTrue
//ct.TextFalse = ChkBoxTextIfFalse
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ChkBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ChkBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeCheckBoxForRange = True
//End Function 'SetCellTypeCheckBoxForRange
//Public Function SetCellTypeComboBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ComboTextEditable As Boolean, ByVal ComboBoxItemsTextVBTabbed As String, ByVal ComboListAlignment As FarPoint.Win.ListAlignment, ByVal PixelsToOffsetListFromAttachedCell As Integer, ByVal ListWidthInPixels As Integer, ByVal NumbeofItemsToDisplayInDropdown As Integer, ByVal MaxLengthOfCharactersToDisplayInCell As Integer, ByVal ComboBoxBackColor As Color, ByVal ComboBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//'MUST Send In Item List as vbTab separated text
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//'MUST Send In Item List as vbTab separated text
//Dim ItemList() As String = Split(ComboBoxItemsTextVBTabbed, vbTab)
//ct.Editable = ComboTextEditable
//ct.Items = ItemList
//ct.ListAlignment = ComboListAlignment
//ct.ListOffset = PixelsToOffsetListFromAttachedCell
//ct.ListWidth = ListWidthInPixels
//ct.MaxDrop = NumbeofItemsToDisplayInDropdown
//ct.MaxLength = MaxLengthOfCharactersToDisplayInCell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ComboBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ComboBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeComboBoxForRange = True
//End Function 'SetCellTypeComboBoxForRange
//Public Function SetCellTypeComboBoxForRangeFor1DArray(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ComboTextEditable As Boolean, ByVal ComboBoxItemsText1DArray As String(), ByVal ComboListAlignment As FarPoint.Win.ListAlignment, ByVal PixelsToOffsetListFromAttachedCell As Integer, ByVal ListWidthInPixels As Integer, ByVal NumbeofItemsToDisplayInDropdown As Integer, ByVal MaxLengthOfCharactersToDisplayInCell As Integer, ByVal ComboBoxBackColor As Color, ByVal ComboBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//ct.Editable = ComboTextEditable
//ct.Items = ComboBoxItemsText1DArray
//ct.ListAlignment = ComboListAlignment
//ct.ListOffset = PixelsToOffsetListFromAttachedCell
//ct.ListWidth = ListWidthInPixels
//ct.MaxDrop = NumbeofItemsToDisplayInDropdown
//ct.MaxLength = MaxLengthOfCharactersToDisplayInCell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ComboBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ComboBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeComboBoxForRangeFor1DArray = True
//End Function 'SetCellTypeComboBoxForRangeFor1DArray
//Public Function SetCellTypeMaskForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal Mask As String, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim maskcell As New FarPoint.Win.Spread.CellType.MaskCellType
//maskcell.Mask = Mask
//'maskcell.StringTrim = StringTrimming.Character
//'maskcell.MaskChar = ""
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = maskcell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeMaskForRange = True
//End Function 'SetCellTypeMaskForRange
//Public Function SetCellTypeNumberForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal DecimalSeparator As String, ByVal DecimalPlaces As Integer, ByVal LeadingZero As FarPoint.Win.Spread.CellType.LeadingZero, ByVal MaximumValue As Double, ByVal MinimumValue As Double, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim nmbrcell As New FarPoint.Win.Spread.CellType.NumberCellType
//nmbrcell.DecimalSeparator = DecimalSeparator
//nmbrcell.DecimalPlaces = DecimalPlaces
//nmbrcell.LeadingZero = LeadingZero ' FarPoint.Win.Spread.CellType.LeadingZero.UseRegional
//nmbrcell.MaximumValue = MaximumValue
//nmbrcell.MinimumValue = MinimumValue
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = nmbrcell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeNumberForRange = True
//End Function 'SetCellTypeNumberForRange
//Public Function SetRowHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal HorizontalAlignment As FarPoint.Win.spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal LabelText As String) As Boolean
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).HorizontalAlignment = HorizontalAlignment
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).VerticalAlignment = VerticalAlignment
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).Label = LabelText
//SetRowHeaderText = True
//End Function 'SetRowHeaderText
//Public Function SetRowHeaderColumnCount(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal ColumnCount As Integer) As Boolean
//Dim r As FarPoint.Win.Spread.RowHeader
//r = SSControlToUse.ActiveSheet.RowHeader
//r.ColumnCount = ColumnCount
//SetRowHeaderColumnCount = True
//End Function 'SetRowHeaderColumnCount
//Public Function SetRowHeaderBackColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal BackColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).BackColor = BackColor
//SetRowHeaderBackColor = True
//End Function 'SetRowHeaderBackColor
//Public Function SetSpecialRowHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal HorizontalAlignment As FarPoint.Win.Spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.Spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal BackColor As System.Drawing.Color, ByVal foreColor As System.Drawing.Color, ByVal TheText As String) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).HorizontalAlignment = HorizontalAlignment
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).VerticalAlignment = VerticalAlignment
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).Font = New Font(FontFamily, FontSize, FontStyle)
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).BackColor = BackColor
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).ForeColor = foreColor
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).Text = TheText
//SetSpecialRowHeaderText = True
//End Function 'SetSpecialRowHeaderText
//Public Function SetSpecialColumnHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal HorizontalAlignment As FarPoint.Win.Spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.Spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal BackColor As System.Drawing.Color, ByVal foreColor As System.Drawing.Color, ByVal TheText As String) As Boolean
//Dim ch As FarPoint.Win.Spread.ColumnHeader
//ch = SSControlToUse.ActiveSheet.ColumnHeader
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).HorizontalAlignment = HorizontalAlignment
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).VerticalAlignment = VerticalAlignment
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).Font = New Font(FontFamily, FontSize, FontStyle)
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).BackColor = BackColor
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).ForeColor = foreColor
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).Text = TheText
//SetSpecialColumnHeaderText = True
//End Function 'SetSpecialColumnHeaderText
//Public Function SetRowHeaderForeColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal ForeColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).ForeColor = ForeColor
//SetRowHeaderForeColor = True
//End Function 'SetRowHeaderBackColor
//Public Function SetColumnHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Col As Integer, ByVal HorizontalAlignment As FarPoint.Win.spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal LabelText As String) As Boolean
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).HorizontalAlignment = HorizontalAlignment
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).VerticalAlignment = VerticalAlignment
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).Label = LabelText
//SetColumnHeaderText = True
//End Function 'SetColumnHeaderText
//Public Function SetColumnHeaderRowCount(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowCount As Integer) As Boolean
//Dim r As FarPoint.Win.Spread.ColumnHeader
//r = SSControlToUse.ActiveSheet.ColumnHeader
//r.RowCount = RowCount
//SetColumnHeaderRowCount = True
//End Function 'SetRowHeaderColumnCount
//Public Function SetColumnHeaderSpan(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Column As Integer, ByVal RowCount As Integer, ByVal ColumnCount As Integer) As Boolean
//SSControlToUse.ActiveSheet.AddColumnHeaderSpanCell(Row, Column, RowCount, ColumnCount)
//End Function
//Public Function SetColumnHeaderBackColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal BackColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.ColumnHeader
//rh = SSControlToUse.ActiveSheet.ColumnHeader
//rh.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).BackColor = BackColor
//SetColumnHeaderBackColor = True
//End Function 'SetColumnHeaderBackColor
//Public Function SetColumnHeaderForeColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal ForeColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.ColumnHeader
//rh = SSControlToUse.ActiveSheet.ColumnHeader
//rh.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).ForeColor = ForeColor
//SetColumnHeaderForeColor = True
//End Function 'SetColumnHeaderForeColor
//Public Function ClearComboBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//ct.Clear()
//ClearComboBoxForRange = True
//End Function 'ClearComboBoxForRange
}
}
New Version
//======================================================================
//
// Copyright (C) 2007-2008 RaySoft
// All rights reserved
// guid1: 4b887cc8-ac8c-4397-bff3-628761c59f48
// guid2: 80f46765-0f16-44c4-a542-2b7eb38522f7
// guid3: 923c4a79-98ea-4148-8d1a-e13993a55545
// guid4: da27be46-708a-453f-aa4d-25e96d116917
// guid5: 05158992-6744-44d1-9bcb-cb422e35afbf
// CLR版本: 2.0.50727.5448
// 新建项输入的名称: SpreadHelper
// 机器名称: RAY-PC
// 注册组织名:
// 命名空间名称: Ray.Spread
// 文件名: SpreadHelper
// 当前系统时间: 2013/07/24
// 当前登录用户名: Administrator
// 创建年份: 2013
//
//======================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using FarPoint.Win;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.Model;
using FarPoint.Win.Spread.Design;
namespace Ray.Spread
{
public class SpreadHelper
{
public SpreadHelper() { }
#region 设置单元格字体
/// <summary>
/// 设置单元格字体名称
/// </summary>
/// <param name="fpSpread" >表格控件</param>
/// <param name="cellRange">范围</param>
/// <param name="FontName">字体名称</param>
public static void SetFontFamilyForRange(FpSpread fpSpread, CellRange[] cellRanges, string FontName)
{
foreach (CellRange cellRange in cellRanges)
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int column = cellRange.Column; column < cellRange.Column + cellRange.ColumnCount; column++)
{
Font font = fpSpread.ActiveSheet.Cells[row, column].Font == null ? fpSpread.ActiveSheet.DefaultStyle.Font : fpSpread.ActiveSheet.Cells[row, column].Font;
fpSpread.ActiveSheet.Cells[row, column].Font = new Font(FontName, font.Size, font.Style);
}
}
}
}
/// <summary>
/// 设置单元格字体大小
/// </summary>
/// <param name="fpSpread" >表格控件</param>
/// <param name="cellRange">范围</param>
/// <param name="FontSize">字体大小</param>
public static void SetFontSizeForRange(FpSpread fpSpread, CellRange[] cellRanges, float fontSize)
{
foreach (CellRange cellRange in cellRanges)
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int column = cellRange.Column; column < cellRange.Column + cellRange.ColumnCount; column++)
{
Font font = fpSpread.ActiveSheet.Cells[row, column].Font == null ? fpSpread.ActiveSheet.DefaultStyle.Font : fpSpread.ActiveSheet.Cells[row, column].Font;
fpSpread.ActiveSheet.Cells[row, column].Font = new Font(font.FontFamily.Name, fontSize, font.Style);
}
}
}
}
/// <summary>
/// 设置单元格字体样式
/// </summary>
/// <param name="fpSpread" >表格控件</param>
/// <param name="cellRange">范围</param>
/// <param name="FontStyle">字休样式</param>
/// <param name="style" ></param>
/// <param name="val" ></param>
public static void SetFontStyleForRange(FpSpread fpSpread, CellRange[] cellRanges,string style, bool val)
{
string fs;
foreach (CellRange cellRange in cellRanges)
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int column = cellRange.Column; column < cellRange.Column + cellRange.ColumnCount; column++)
{
Font font = fpSpread.ActiveSheet.Cells[row, column].Font == null ? fpSpread.ActiveSheet.DefaultStyle.Font : fpSpread.ActiveSheet.Cells[row, column].Font;
fs = "";
if (style == "Bold")
{
if (font.Italic)
{
fs += "Italic,";
}
if (font.Underline)
{
fs += "Underline,";
}
if (font.Strikeout)
{
fs += "Strikeout,";
}
if (val == true)
{
fs += "Bold,";
}
}
else if (style == "Italic")
{
if (font.Bold)
{
fs += "Bold,";
}
if (font.Underline)
{
fs += "Underline,";
}
if (font.Strikeout)
{
fs += "Strikeout,";
}
if (val == true)
{
fs += "Italic,";
}
}
else if (style == "Underline")
{
if (font.Bold)
{
fs += "Bold,";
}
if (font.Italic )
{
fs += "Italic,";
}
if (font.Strikeout)
{
fs += "Strikeout,";
}
if (val == true)
{
fs += "Underline,";
}
}
else if (style == "Strikeout")
{
if (font.Bold)
{
fs += "Bold,";
}
if (font.Italic)
{
fs += "Italic,";
}
if (font.Underline )
{
fs += "Underline,";
}
if (val == true)
{
fs += "Strikeout,";
}
}
else
{
}
if (fs.Length > 0)
{
fpSpread.ActiveSheet.Cells[row, column].Font = new Font(font.FontFamily.Name, font.Size, (FontStyle)Enum.Parse(typeof(FontStyle), fs.Substring(0, fs.Length - 1)));
}
else
{
fpSpread.ActiveSheet.Cells[row, column].Font = new Font(font.FontFamily.Name, font.Size, (FontStyle)Enum.Parse(typeof(FontStyle), "Regular"));
}
}
}
}
}
/// <summary>
/// 设置单元格字体样式
/// </summary>
/// <param name="fpSpread">表格控件</param>
/// <param name="cellrange">范围</param>
/// <param name="fontFamily">字体名称</param>
/// <param name="fontStyle">字体大小</param>
/// <param name="fontSize">字休样式</param>
/// <returns></returns>
public static void SetFontForRange(FpSpread fpSpread, CellRange[] cellRanges, string fontFamily, FontStyle fontStyle, float fontSize)
{
foreach (FarPoint.Win.Spread.Model.CellRange cellRange in cellRanges)
{
for (int i = cellRange.Row; i < cellRange.RowCount + cellRange.Row; i++)
{
for (int j = cellRange.Column; j < cellRange.ColumnCount + cellRange.Column; j++)
{
Font font = new Font(fontFamily, fontSize, fontStyle);
fpSpread.ActiveSheet.Cells[i, j].Font = font;
}
}
}
}
#endregion
#region 设置单元格对齐
/// <summary>
/// 设置单元格横向对齐
/// </summary>
/// <param name="fpSpread" >表格控件</param>
/// <param name="cellRange">范围</param>
/// <param name="horizontalAlignment">横向对齐</param>
public static void SetHorizontalAlignmentForRange(FpSpread fpSpread, CellRange[] cellRanges, FarPoint.Win.Spread.CellHorizontalAlignment horizontalAlignment)
{
foreach (CellRange cellRange in cellRanges)
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int column = cellRange.Column; column < cellRange.Column + cellRange.ColumnCount; column++)
{
fpSpread.ActiveSheet.Cells[row, column].HorizontalAlignment = horizontalAlignment;
}
}
}
}
/// <summary>
/// 设置单元格纵向对齐
/// </summary>
/// <param name="fpSpread">表格控件</param>
/// <param name="cellRange">范围</param>
/// <param name="verticalAlignment">横向对齐</param>
public static void SetVerticalAlignmentForRange(FpSpread fpSpread, CellRange[] cellRanges, CellVerticalAlignment verticalAlignment)
{
foreach (CellRange cellRange in cellRanges)
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int column = cellRange.Column; column < cellRange.Column + cellRange.ColumnCount; column++)
{
fpSpread.ActiveSheet.Cells[row, column].VerticalAlignment = verticalAlignment;
}
}
}
}
/// <summary>
/// 设置单元格对齐
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="HorizontalAlignment"></param>
/// <param name="VerticalAlignment"></param>
/// <returns></returns>
public static void setCellAlignmentForRange(FpSpread fpSpread, int row, int col, int row2, int col2, CellHorizontalAlignment HorizontalAlignment, CellVerticalAlignment VerticalAlignment)
{
fpSpread.ActiveSheet.Cells[row, col, row2, col2].HorizontalAlignment = HorizontalAlignment;
fpSpread.ActiveSheet.Cells[row, col, row2, col2].VerticalAlignment = VerticalAlignment;
}
#endregion
#region 设置单元格表格线
/// <summary>
///
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool SetBorderOutSideBold(FpSpread fps)
{
//停止重绘界面
fps.SuspendLayout();
//得到用户选择区域
CellRange[] cellRanges = fps.ActiveSheet.GetSelections();
//定义一个边框
ComplexBorderSide Border = new FarPoint.Win.ComplexBorderSide(Color.Black, 3, System.Drawing.Drawing2D.DashStyle.Solid);
if (cellRanges != null)
{
foreach (CellRange cellRange in cellRanges)
{
if (cellRange.RowCount == 1 && cellRange.ColumnCount == 1)//一个单元格
{
SetComplexSingeCellBorders(fps, cellRange.Row, cellRange.Column, Border, Border, Border, Border);
}
else if (cellRange.RowCount == 1)//一行
{
for (int col = cellRange.Column; col < cellRange.Column + cellRange.ColumnCount; col++)
{
if (col == cellRange.Column)
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, Border, Border, null, Border);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, null, Border, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, null, Border, null, Border);
}
}
}
else if (cellRange.ColumnCount == 1) //一列
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
if (row == cellRange.Row)
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, Border, Border, null);
}
else if (row == cellRange.Row + cellRange.RowCount - 1)
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, null, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, null, Border, null);
}
}
}
else//多行多列
{
//循环设置单元格上边框
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int col = cellRange.Column; col < cellRange.ColumnCount + cellRange.Column; col++)
{
if (row == cellRange.Row)//第一行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, Border, null, null);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, Border, Border, null);
}
else//其它列
{
SetComplexSingeCellBorders(fps, row, col, null, Border, null, null);
}
}
else if (row == cellRange.Row + cellRange.RowCount - 1)//最后一行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, null, null, Border);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, null, Border, Border);
}
else//其它列
{
SetComplexSingeCellBorders(fps, row, col, null, null, null, Border);
}
}
else//其它行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, null, null, null);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, null, Border, null);
}
else
{
}
}
}
}
}
}
}
else
{
throw new Exception("未能得到用户选择的区域！");
}
//恢复重绘界面
fps.ResumeLayout();
return true;
}
/// <summary>
///
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool SetBorderOutSide(FpSpread fps)
{
//停止重绘界面
fps.SuspendLayout();
//得到用户选择区域
CellRange[] cellRanges = fps.ActiveSheet.GetSelections();
//定义一个边框
ComplexBorderSide Border = new FarPoint.Win.ComplexBorderSide(Color.Black, 1, System.Drawing.Drawing2D.DashStyle.Solid);
if (cellRanges != null)
{
foreach (CellRange cellRange in cellRanges)
{
if (cellRange.RowCount == 1 && cellRange.ColumnCount == 1)//一个单元格
{
SetComplexSingeCellBorders(fps, cellRange.Row, cellRange.Column, Border, Border, Border, Border);
}
else if (cellRange.RowCount == 1)//一行
{
for (int col = cellRange.Column; col < cellRange.Column + cellRange.ColumnCount; col++)
{
if (col == cellRange.Column)
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, Border, Border, null, Border);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, null, Border, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, null, Border, null, Border);
}
}
}
else if (cellRange.ColumnCount == 1) //一列
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
if (row == cellRange.Row)
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, Border, Border, null);
}
else if (row == cellRange.Row + cellRange.RowCount - 1)
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, null, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, null, Border, null);
}
}
}
else//多行多列
{
//循环设置单元格上边框
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int col = cellRange.Column; col < cellRange.ColumnCount + cellRange.Column; col++)
{
if (row == cellRange.Row)//第一行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, Border, null, null);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, Border, Border, null);
}
else//其它列
{
SetComplexSingeCellBorders(fps, row, col, null, Border, null, null);
}
}
else if (row == cellRange.Row + cellRange.RowCount - 1)//最后一行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, null, null, Border);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, null, Border, Border);
}
else//其它列
{
SetComplexSingeCellBorders(fps, row, col, null, null, null, Border);
}
}
else//其它行
{
if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, null, null, null);
}
else if (col == cellRange.Column + cellRange.ColumnCount - 1)//最后一列
{
SetComplexSingeCellBorders(fps, row, col, null, null, Border, null);
}
}
}
}
}
}
}
else
{
throw new Exception("未能得到用户选择的区域！");
}
//恢复重绘界面
fps.ResumeLayout();
return true;
}
/// <summary>
/// 所有边框
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool SetBorderFull(FpSpread fps)
{
//停止重绘界面
fps.SuspendLayout();
//得到用户选择区域法
CellRange[] cellRanges = fps.ActiveSheet.GetSelections();
//定义一个边框
ComplexBorderSide Border = new FarPoint.Win.ComplexBorderSide(Color.Black, 1, System.Drawing.Drawing2D.DashStyle.Solid);
if (cellRanges != null)
{
foreach (CellRange cellRange in cellRanges)
{
if (cellRange.RowCount == 1 && cellRange.ColumnCount == 1)//一个单元格
{
SetComplexSingeCellBorders(fps, cellRange.Row, cellRange.Column, Border, Border, Border, Border);
}
else if (cellRange.RowCount == 1)//一行
{
for (int col = cellRange.Column; col < cellRange.Column + cellRange.ColumnCount; col++)
{
if (col == cellRange.Column)
{
SetComplexSingeCellBorders(fps, cellRange.Row, cellRange.Column, Border, Border, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, cellRange.Row, col, null, Border, Border, Border);
}
}
}
else if (cellRange.ColumnCount == 1) //一列
{
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
if (row == cellRange.Row)
{
SetComplexSingeCellBorders(fps, cellRange.Row, cellRange.Column, Border, Border, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, row, cellRange.Column, Border, null, Border, Border);
}
}
}
else//多行多列
{
//循环设置单元格上边框
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int col = cellRange.Column; col < cellRange.ColumnCount + cellRange.Column; col++)
{
if (row == cellRange.Row && col == cellRange.Column)
{
SetComplexSingeCellBorders(fps, row, col, Border, Border, Border, Border);
}
else if (row == cellRange.Row)//第一行
{
SetComplexSingeCellBorders(fps, row, col, null, Border, Border, Border);
}
else if (col == cellRange.Column)//第一列
{
SetComplexSingeCellBorders(fps, row, col, Border, null, Border, Border);
}
else
{
SetComplexSingeCellBorders(fps, row, col, null, null, Border, Border);
}
}
}
}
}
}
else
{
throw new Exception("未能得到用户选择的区域！");
}
//恢复重绘界面
fps.ResumeLayout();
return true;
}
/// <summary>
/// 无边框
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool SetBorderNone(FpSpread fps)
{
//停止重绘界面
fps.SuspendLayout();
//得到用户选择区域
CellRange[] cellRanges = fps.ActiveSheet.GetSelections();
if (cellRanges != null)
{
foreach (CellRange cellRange in cellRanges)
{
//循环设置单元格上边框
for (int row = cellRange.Row; row < cellRange.Row + cellRange.RowCount; row++)
{
for (int col = cellRange.Column; col < cellRange.ColumnCount + cellRange.Column; col++)
{
SetComplexSingeCellBorders(fps, row, col, null, null, null, null);
}
}
}
}
else
{
throw new Exception("未能得到用户选择的区域！");
}
//恢复重绘界面
fps.ResumeLayout();
return true;
}
/// <summary>
/// 设置表格边框
/// </summary>
/// <param name="fps"></param>
/// <param name="cellRanges"></param>
/// <returns></returns>
public static bool SetBorderSide(FpSpread fps, BorderStyle borderStyle)
{
//停止重绘界面
fps.SuspendLayout();
CellRange[] cellRanges = fps.ActiveSheet.GetSelections();
if (cellRanges != null)
{
foreach (CellRange cellRange in cellRanges)
{
int row0 = cellRange.Row;
int row1 = cellRange.Row + cellRange.RowCount;
int col0 = cellRange.Column;
int col1 = cellRange.Column + cellRange.ColumnCount;
switch (borderStyle)
{
case BorderStyle .Bottom :
row0 = cellRange.Row + cellRange.RowCount - 1;
break;
case BorderStyle.Top:
row1 = cellRange.Row + 1;
break;
case BorderStyle.Right :
col0 = cellRange.Column + cellRange.ColumnCount - 1;
break;
case BorderStyle.Left :
col1 = cellRange.Column + 1;
break;
default:
break;
}
//循环设置单元格边框
for (int row = row0; row < row1; row++)
{
for (int col = col0; col < col1; col++)
{
//取得单元格边框
ComplexBorder border = (FarPoint.Win.ComplexBorder)fps.ActiveSheet.Cells[row, col].Border;
ComplexBorderSide topBorder = null;
ComplexBorderSide rightBorder = null;
ComplexBorderSide bottomBorder = null;
ComplexBorderSide leftBorder = null;
if (border != null)
{
if (border.Top.Style.ToString().ToLower() != "none") topBorder = border.Top;
if (border.Bottom.Style.ToString().ToLower() != "none") bottomBorder = border.Bottom;
if (border.Left.Style.ToString().ToLower() != "none") leftBorder = border.Left;
if (border.Right.Style.ToString().ToLower() != "none") rightBorder = border.Right;
}
//设置要改的单元格边框
ComplexBorderSide borderSide = new ComplexBorderSide(Color.Black, 1, System.Drawing.Drawing2D.DashStyle.Solid);
switch (borderStyle )
{
case BorderStyle.Bottom :
bottomBorder = borderSide;
break;
case BorderStyle.Top :
topBorder = borderSide;
break;
case BorderStyle.Left:
leftBorder = borderSide;
break;
case BorderStyle.Right :
rightBorder = borderSide;
break;
default:
throw new Exception("边框参数错误！");
}
SetComplexSingeCellBorders(fps, row, col, leftBorder, topBorder, rightBorder, bottomBorder);
}
}
}
}
else
{
throw new Exception("未能得到用户选择的区域！");
}
//恢复重绘界面
fps.ResumeLayout();
return true;
}
/// <summary>
/// 打开边框编辑器
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool ShowBorderEditor(FpSpread fps)
{
fps.SuspendLayout();
FarPoint.Win.Spread.Design.BorderEditor borderedit = new FarPoint.Win.Spread.Design.BorderEditor(fps);
borderedit.Name = "边框设置";
if (fps.ActiveSheet.SelectionCount == 0)
{
borderedit.StartColumn = fps.ActiveSheet.ActiveColumnIndex;
borderedit.ColumnCount = 1;
borderedit.StartRow = fps.ActiveSheet.ActiveRowIndex;
borderedit.RowCount = 1;
}
else
{
FarPoint.Win.Spread.Model.CellRange range = fps.ActiveSheet.GetSelection(0); //选中区域
borderedit.StartColumn = range.Column;
borderedit.ColumnCount = range.ColumnCount;
borderedit.StartRow = range.Row;
borderedit.RowCount = range.RowCount;
}
borderedit.ShowDialog();
fps.ResumeLayout();
return true;
}
/// <summary>
/// 设置单元格表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="leftBorder"></param>
/// <param name="topBorder"></param>
/// <param name="rightBorder"></param>
/// <param name="bottomBorder"></param>
/// <returns></returns>
public static bool SetComplexSingeCellBorders(FpSpread fps, int row, int col, FarPoint.Win.ComplexBorderSide leftBorder, FarPoint.Win.ComplexBorderSide topBorder, FarPoint.Win.ComplexBorderSide rightBorder, FarPoint.Win.ComplexBorderSide bottomBorder)
{
if (leftBorder != null & topBorder == null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, null, null);
}//Left
else if (leftBorder == null & topBorder != null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, null, null);
}//Top
else if (leftBorder == null & topBorder == null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, rightBorder, null);
}//Right
else if (leftBorder == null & topBorder == null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, null, bottomBorder);
}//Bottom
else if (leftBorder != null & topBorder != null & rightBorder == null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, null, null);
}//Left + Top
else if (leftBorder != null & topBorder == null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, rightBorder, null);
}//Left + Right
else if (leftBorder != null & topBorder == null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null, null, bottomBorder);
}//Left + Bottom
else if (leftBorder == null & topBorder != null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, rightBorder, null);
}//Top + Right
else if (leftBorder == null & topBorder != null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, null, bottomBorder);
}//Top + Bottom
else if (leftBorder == null & topBorder == null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, rightBorder, bottomBorder);
}//Right + Bottom
else if (leftBorder != null & topBorder != null & rightBorder != null & bottomBorder == null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, rightBorder, null);
}//Left + Top + Right
else if (leftBorder != null & topBorder != null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, null, bottomBorder);
}//Left + Top + Bottom
else if (leftBorder != null & topBorder == null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, null,rightBorder , bottomBorder);
}//Left + Right + Bottom
else if (leftBorder == null & topBorder != null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, topBorder, rightBorder, bottomBorder);
}//Top + Right + Bottom 原版中没有这一项.
else if (leftBorder != null & topBorder != null & rightBorder == null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder , topBorder, null, bottomBorder);
}//Top + left + Bottom 原版中没有这一项.
else if (leftBorder != null & topBorder != null & rightBorder != null & bottomBorder != null)
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(leftBorder, topBorder, leftBorder, bottomBorder);
}//Left + Top + Right + Bottom
else
{
fps.ActiveSheet.Cells[row, col].Border = new FarPoint.Win.ComplexBorder(null, null, null, null);
}//All Null
return true;
}
#endregion
#region 清除表格线
/// <summary>
/// 清除表格线
/// </summary>
/// <param name="fps"></param>
/// <returns></returns>
public static bool ClearAllCellBoders(FpSpread fps)
{
fps.ActiveSheet.HorizontalGridLine = new GridLine(FarPoint.Win.Spread.GridLineType.None);
fps.ActiveSheet.VerticalGridLine = new GridLine(FarPoint.Win.Spread.GridLineType.None);
return true;
}
#endregion
#region 设置单元格的值
/// <summary>
/// 设置单元格的值
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="textVal"></param>
/// <returns></returns>
public static bool setValue(FpSpread fps, int row, int col, string textVal)
{
fps.ActiveSheet.SetValue(row, col, textVal);
return true;
}
#endregion
#region 设置单元格背景颜色
/// <summary>
/// 设置单元格背景颜色
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="theBackColor"></param>
/// <returns></returns>
public static bool setBackColorForRange(FpSpread fps, int row, int col, int row2, int col2, Color theBackColor)
{
fps.ActiveSheet.Cells[row, col, row2, col2].BackColor = theBackColor;
return true;
}
#endregion
#region 设置单元格前景颜色
/// <summary>
/// 设置单元格前景颜色
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="theForceColor"></param>
/// <returns></returns>
public static bool setForceColorForRange(FpSpread fps, int row, int col, int row2, int col2, Color theForceColor)
{
fps.ActiveSheet.Cells[row, col, row2, col2].ForeColor = theForceColor;
return true;
}
#endregion
#region 设置单元格文本
/// <summary>
/// 设置单元格的文本
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="sText"></param>
/// <returns></returns>
public static bool setText(FpSpread fps, int row, int col, string sText)
{
fps.ActiveSheet.SetValue(row, col, sText);
return true;
}
#endregion
#region 设置指定行行高
public static bool setRowHeightForRange(FpSpread fps, int row, int col, int row2, int col2, Single rowHeightInPixels)
{
fps.ActiveSheet.Cells[row, col, row2, col2].Row.Height = rowHeightInPixels;
return true;
}
#endregion
#region 设置指定列列宽
public static bool setColumnWidthForRange(FpSpread fps, int row, int col, int row2, int col2, Single columnWidthInPixels)
{
fps.ActiveSheet.Cells[row, col, row2, col2].Column.Width = columnWidthInPixels;
return true;
}
#endregion
#region 设置单元格简单表格线
/// <summary>
/// 设置单元格简单表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setSingleCellBorders(FpSpread fps, int row, int col, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide topBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col].Border = new ComplexBorder(leftBorder, topBorder, rightBorder, bottomBorder);
return true;
}
#endregion
#region 设置指定区域简单表格线
/// <summary>
/// 设置指定区域简单表格线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="?"></param>
/// <param name="?"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool SetSingleCellBordersForRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide topBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(leftBorder, topBorder, rightBorder, bottomBorder);
return true;
}
#endregion
#region 设置指定区域简单左边线
/// <summary>
/// 设置指定区域简单左边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setLeftBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide leftBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(leftBorder, null, null, null);
return true;
}
#endregion
#region 设置指定区域简单上边线
/// <summary>
/// 设置指定区域简单上边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setTopBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide TopBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, TopBorder, null, null);
return true;
}
#endregion
#region 设置指定区域简单右边线
/// <summary>
/// 设置指定区域简单右边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setRightBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide rightBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, null, rightBorder, null);
return true;
}
#endregion
#region 设置指定区域简单下边线
/// <summary>
/// 设置指定区域简单下边线
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="row2"></param>
/// <param name="col2"></param>
/// <param name="lineWidthInPixels"></param>
/// <returns></returns>
public static bool setBottomBorderOfCellRange(FpSpread fps, int row, int col, int row2, int col2, int lineWidthInPixels)
{
ComplexBorderSide bottomBorder = new ComplexBorderSide(Color.Black, lineWidthInPixels);
fps.ActiveSheet.Cells[row, col, row2, col2].Border = new ComplexBorder(null, null, null, bottomBorder);
return true;
}
#endregion
#region 设置指定单元格标签
/// <summary>
/// 设置指定单元格标签
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="tag"></param>
/// <returns></returns>
public static bool setCellTag(FpSpread fps, int row, int col, string tag)
{
fps.ActiveSheet.Models.Data.SetTag(row, col, tag);
return true;
}
#endregion
#region 得到指定单元格标签
/// <summary>
/// 得到指定单元格标签
/// </summary>
/// <param name="fps"></param>
/// <param name="row"></param>
/// <param name="col"></param>
/// <param name="tag"></param>
/// <returns></returns>
public static bool getCellTag(FpSpread fps, int row, int col)
{
fps.ActiveSheet.Models.Data.GetTag(row,col);
return true;
}
#endregion
/// <summary>
/// 通过单元格名称返回当前列号
/// </summary>
/// <param name="CellName">单元格名称</param>
/// <returns></returns>
public static int getColumnNum(string CellName)
{
int retVal = 0;
string rowNum = "";
Regex r = new Regex(@"[a-zA-Z]+");
Match m = r.Match(CellName);
rowNum = m.Value.ToUpper();
char[] ch = rowNum.ToCharArray();
for (int i = 0; i < rowNum.Length; i++)
{
retVal += ((ch[i] - 'A') + 1) * (int)Math.Pow(26, rowNum.Length - 1 - i);
}
retVal = retVal - 1;
return retVal;
}
/// <summary>
/// 通过单元格名称返回当前行号
/// </summary>
/// <param name="CellName">单元格名称</param>
/// <returns></returns>
public static int getRowNum(string CellName)
{
int retVal = 0;
Regex r = new Regex(@"[0-9]+");
Match m = r.Match(CellName);
retVal = int.Parse(m.Value) - 1;
return retVal;
}
/// <summary>
/// 没有使用
/// </summary>
/// <param name="CellName"></param>
/// <returns></returns>
public static int[] GetCellID(string CellName)
{
int[] retVal = new int[2];
string rowNum = "";
Regex rRow = new Regex(@"[a-zA-Z]+");
Match mRow = rRow.Match(CellName);
rowNum = mRow.Value.ToUpper();
char[] ch = rowNum.ToCharArray();
for (int i = 0; i < rowNum.Length; i++)
{
retVal[0] += ((ch[i] - 'A') + 1) * (int)Math.Pow(26, rowNum.Length - 1 - i);
}
retVal[0] = retVal[0] - 1;
Regex rCol = new Regex(@"[0-9]+");
Match mCol = rCol.Match(CellName);
retVal[1] = int.Parse(mCol.Value.ToString()) - 1;
return retVal;
}
//public static bool addSheets(FpSpread fps, int numOfSheets, int startingNumberOfColumns, int startingNumberOfRows, bool protectionLikeExcelTF, Color sheetBackColor, HeaderAutoText rowHeaderAutoText, HeaderAutoText ColumnHeaderAutoText)
//{
// //'Adds a Sheet for Each Question and Sets the number of Rows and Column
// int x, y, z;
// fps.Sheets.Count = 0;
// //'Create the Sheets
// for int x=0,
//}
//Public Function AddSheets(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal NumberOfSheets As Integer, ByVal StartingNumberOfColumns As Integer, ByVal StartingNumberOfRows As Integer, ByVal ProtectionLikeExcelTF As Boolean, ByVal SheetBackColor As System.Drawing.Color, ByVal RowHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText, ByVal ColumnHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText) As Boolean
//Dim x As Integer
//Dim y As Integer
//Dim z As Integer
//SSControlToUse.Sheets.Count = 0
//'Create the Sheets
//For x = 0 To NumberOfSheets
//Dim s As New FarPoint.Win.Spread.SheetView
//s.ColumnCount = StartingNumberOfColumns
//s.RowCount = StartingNumberOfRows
//SSControlToUse.Sheets.Add(s)
//Dim info As New FarPoint.Win.Spread.StyleInfo
//'Set the BackColor
//info.BackColor = SheetBackColor
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).DefaultStyle = info
//'Set Protection Scheme to be like Excels for each sheet
//For y = 0 To (StartingNumberOfRows - 1)
//For z = 0 To (StartingNumberOfColumns - 1)
//s.Cells(y, z).Locked = ProtectionLikeExcelTF
//Next z
//Next y
//SSControlToUse.Sheets(x).RowHeaderAutoText = RowHeaderAutoText
//SSControlToUse.Sheets(x).ColumnHeaderAutoText = ColumnHeaderAutoText
//Next x
//AddSheets = True
//End Function 'AddSheets
//Public Function AddSingleSheet(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal StartingNumberOfColumns As Integer, ByVal StartingNumberOfRows As Integer, ByVal ProtectionLikeExcelTF As Boolean, ByVal SheetBackColor As System.Drawing.Color, ByVal RowHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText, ByVal ColumnHeaderAutoText As FarPoint.Win.Spread.HeaderAutoText) As Boolean
//'Adds a Sheet for Each Question and Sets the number of Rows and Column
//Dim x As Integer
//Dim y As Integer
//'Create the Sheets
//Dim s As New FarPoint.Win.Spread.SheetView
//s.ColumnCount = StartingNumberOfColumns
//s.RowCount = StartingNumberOfRows
//SSControlToUse.Sheets.Add(s)
//Dim info As New FarPoint.Win.Spread.StyleInfo
//'Set the BackColor
//info.BackColor = SheetBackColor
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).DefaultStyle = info
//'Set Protection Scheme to be like Excels for each sheet
//For x = 0 To (StartingNumberOfRows - 1)
//For y = 0 To (StartingNumberOfColumns - 1)
//s.Cells(x, y).Locked = ProtectionLikeExcelTF
//Next y
//Next x
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).RowHeaderAutoText = RowHeaderAutoText
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).ColumnHeaderAutoText = ColumnHeaderAutoText
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).GrayAreaBackColor = Color.SlateGray
//' Set operation mode and let users select multiple blocks of cells
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).OperationMode = FarPoint.Win.Spread.OperationMode.Normal
//SSControlToUse.Sheets(SSControlToUse.Sheets.Count - 1).SelectionPolicy = FarPoint.Win.Spread.Model.SelectionPolicy.MultiRange
//AddSingleSheet = True
//End Function 'AddSingleSheet
//Public Function SSControlGlobals(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal EditModePermanentEntireWorkBookTF As Boolean, ByVal AllowCellOverFlowEntireWorkBookTF As Boolean, ByVal ControlsTabStripPolicy As FarPoint.Win.Spread.TabStripPolicy, ByVal TabStripPercentToShow As Single) As Boolean
//' Set the sheet tabs policy
//SSControlToUse.TabStripPolicy = ControlsTabStripPolicy
//' Set the Tab Strip width to: .??
//SSControlToUse.TabStripRatio = TabStripPercentToShow
//'Eliminate the focus rectangle for the ENTIRE Control
//SSControlToUse.EditModePermanent = EditModePermanentEntireWorkBookTF
//'Allow Cell Text Overflow for the ENTIRE Control
//SSControlToUse.AllowCellOverflow = AllowCellOverFlowEntireWorkBookTF
//SSControlToUse.SelectionBlockOptions = FarPoint.Win.Spread.SelectionBlockOptions.Cells
//End Function
//Public Function TurnProtectionOnOffForAllSheetsInControl(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal TurnProtectionOn As Boolean) As Boolean
//Dim x As Integer
//Dim NumberOfSheets As Int16 = SSControlToUse.Sheets.Count
//For x = 0 To NumberOfSheets - 1
//SSControlToUse.Sheets(x).Protect = TurnProtectionOn
//Next x
//End Function
//Public Function SetFont(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle) As Boolean
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SetFont = True
//End Function 'SetFontBold
//Public Function SetCellSpan(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal StartRow As Integer, ByVal StartCol As Integer, ByVal RowsToSpanCount As Integer, ByVal ColsToSpanCount As Integer) As Boolean
//SSControlToUse.ActiveSheet.AddSpanCell(StartRow, StartCol, RowsToSpanCount, ColsToSpanCount)
//SetCellSpan = True
//End Function 'SetCellSpan
//Public Function SetCellTypeTextForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal MultilineTF As Boolean, ByVal WordWrapTF As Boolean, ByVal ReadOnlyTF As Boolean, ByVal TypeOfScrollBars As System.Windows.Forms.ScrollBars, ByVal MaximumNumberOfCharacters As Int16, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.TextCellType
//ct.Multiline = MultilineTF
//ct.WordWrap = WordWrapTF
//ct.ReadOnly = ReadOnlyTF
//ct.ScrollBars = TypeOfScrollBars
//ct.MaxLength = MaximumNumberOfCharacters
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeTextForRange = True
//End Function 'SetCellTypeTextForRange
//Public Function SetCellTypeGeneralForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal WordWrapTF As Boolean, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.GeneralCellType
//ct.WordWrap = WordWrapTF
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeGeneralForRange = True
//End Function 'SetCellTypeGeneralForRange
//Public Function SetCellTypeButtonForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ButtonColor As Color, ByVal ButtonText As String, ByVal ButtonTextColor As Color, ByVal WordWrapTF As Boolean, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ButtonCellType
//ct.ButtonColor = ButtonColor
//ct.Text = ButtonText
//ct.TextColor = ButtonTextColor
//ct.WordWrap = WordWrapTF
//ct.TwoState = False
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeButtonForRange = True
//End Function 'SetCellTypeButtonForRange
//Public Function SetCellTypeRadioButtonForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal RadioButtonItemsTextVBTabbed As String, ByVal RadioBtnOrientation As FarPoint.Win.RadioOrientation, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.MultiOptionCellType
//'MUST Send In Item List as vbTab separated text
//Dim ItemList() As String = Split(RadioButtonItemsTextVBTabbed, vbTab)
//ct.Items = ItemList
//ct.Orientation = RadioBtnOrientation
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//'SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = RadioBtnBackColor
//'SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = RadioBtnForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeRadioButtonForRange = True
//End Function 'SetCellTypeRadioButtonForRange
//Public Function SetCellTypeCheckBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ChkBoxTextAlgnment As FarPoint.Win.ButtonTextAlign, ByVal ChkBoxTextIfTrue As String, ByVal ChkBoxTextIfFalse As String, ByVal ChkBoxBackColor As Color, ByVal ChkBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.CheckBoxCellType
//ct.TextAlign = ChkBoxTextAlgnment
//ct.TextTrue = ChkBoxTextIfTrue
//ct.TextFalse = ChkBoxTextIfFalse
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ChkBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ChkBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeCheckBoxForRange = True
//End Function 'SetCellTypeCheckBoxForRange
//Public Function SetCellTypeComboBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ComboTextEditable As Boolean, ByVal ComboBoxItemsTextVBTabbed As String, ByVal ComboListAlignment As FarPoint.Win.ListAlignment, ByVal PixelsToOffsetListFromAttachedCell As Integer, ByVal ListWidthInPixels As Integer, ByVal NumbeofItemsToDisplayInDropdown As Integer, ByVal MaxLengthOfCharactersToDisplayInCell As Integer, ByVal ComboBoxBackColor As Color, ByVal ComboBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//'MUST Send In Item List as vbTab separated text
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//'MUST Send In Item List as vbTab separated text
//Dim ItemList() As String = Split(ComboBoxItemsTextVBTabbed, vbTab)
//ct.Editable = ComboTextEditable
//ct.Items = ItemList
//ct.ListAlignment = ComboListAlignment
//ct.ListOffset = PixelsToOffsetListFromAttachedCell
//ct.ListWidth = ListWidthInPixels
//ct.MaxDrop = NumbeofItemsToDisplayInDropdown
//ct.MaxLength = MaxLengthOfCharactersToDisplayInCell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ComboBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ComboBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeComboBoxForRange = True
//End Function 'SetCellTypeComboBoxForRange
//Public Function SetCellTypeComboBoxForRangeFor1DArray(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal ComboTextEditable As Boolean, ByVal ComboBoxItemsText1DArray As String(), ByVal ComboListAlignment As FarPoint.Win.ListAlignment, ByVal PixelsToOffsetListFromAttachedCell As Integer, ByVal ListWidthInPixels As Integer, ByVal NumbeofItemsToDisplayInDropdown As Integer, ByVal MaxLengthOfCharactersToDisplayInCell As Integer, ByVal ComboBoxBackColor As Color, ByVal ComboBoxForeColor As Color, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//ct.Editable = ComboTextEditable
//ct.Items = ComboBoxItemsText1DArray
//ct.ListAlignment = ComboListAlignment
//ct.ListOffset = PixelsToOffsetListFromAttachedCell
//ct.ListWidth = ListWidthInPixels
//ct.MaxDrop = NumbeofItemsToDisplayInDropdown
//ct.MaxLength = MaxLengthOfCharactersToDisplayInCell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).BackColor = ComboBoxBackColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).ForeColor = ComboBoxForeColor
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeComboBoxForRangeFor1DArray = True
//End Function 'SetCellTypeComboBoxForRangeFor1DArray
//Public Function SetCellTypeMaskForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal Mask As String, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim maskcell As New FarPoint.Win.Spread.CellType.MaskCellType
//maskcell.Mask = Mask
//'maskcell.StringTrim = StringTrimming.Character
//'maskcell.MaskChar = ""
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = maskcell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeMaskForRange = True
//End Function 'SetCellTypeMaskForRange
//Public Function SetCellTypeNumberForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer, ByVal DecimalSeparator As String, ByVal DecimalPlaces As Integer, ByVal LeadingZero As FarPoint.Win.Spread.CellType.LeadingZero, ByVal MaximumValue As Double, ByVal MinimumValue As Double, ByVal IsLockWhenProtected As Boolean) As Boolean
//Dim nmbrcell As New FarPoint.Win.Spread.CellType.NumberCellType
//nmbrcell.DecimalSeparator = DecimalSeparator
//nmbrcell.DecimalPlaces = DecimalPlaces
//nmbrcell.LeadingZero = LeadingZero ' FarPoint.Win.Spread.CellType.LeadingZero.UseRegional
//nmbrcell.MaximumValue = MaximumValue
//nmbrcell.MinimumValue = MinimumValue
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = nmbrcell
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).Locked = IsLockWhenProtected
//SetCellTypeNumberForRange = True
//End Function 'SetCellTypeNumberForRange
//Public Function SetRowHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal HorizontalAlignment As FarPoint.Win.spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal LabelText As String) As Boolean
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).HorizontalAlignment = HorizontalAlignment
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).VerticalAlignment = VerticalAlignment
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.RowHeader.Rows(Row).Label = LabelText
//SetRowHeaderText = True
//End Function 'SetRowHeaderText
//Public Function SetRowHeaderColumnCount(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal ColumnCount As Integer) As Boolean
//Dim r As FarPoint.Win.Spread.RowHeader
//r = SSControlToUse.ActiveSheet.RowHeader
//r.ColumnCount = ColumnCount
//SetRowHeaderColumnCount = True
//End Function 'SetRowHeaderColumnCount
//Public Function SetRowHeaderBackColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal BackColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).BackColor = BackColor
//SetRowHeaderBackColor = True
//End Function 'SetRowHeaderBackColor
//Public Function SetSpecialRowHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal HorizontalAlignment As FarPoint.Win.Spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.Spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal BackColor As System.Drawing.Color, ByVal foreColor As System.Drawing.Color, ByVal TheText As String) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).HorizontalAlignment = HorizontalAlignment
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).VerticalAlignment = VerticalAlignment
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).Font = New Font(FontFamily, FontSize, FontStyle)
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).BackColor = BackColor
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).ForeColor = foreColor
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).Text = TheText
//SetSpecialRowHeaderText = True
//End Function 'SetSpecialRowHeaderText
//Public Function SetSpecialColumnHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal HorizontalAlignment As FarPoint.Win.Spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.Spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal BackColor As System.Drawing.Color, ByVal foreColor As System.Drawing.Color, ByVal TheText As String) As Boolean
//Dim ch As FarPoint.Win.Spread.ColumnHeader
//ch = SSControlToUse.ActiveSheet.ColumnHeader
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).HorizontalAlignment = HorizontalAlignment
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).VerticalAlignment = VerticalAlignment
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).Font = New Font(FontFamily, FontSize, FontStyle)
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).BackColor = BackColor
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).ForeColor = foreColor
//ch.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).Text = TheText
//SetSpecialColumnHeaderText = True
//End Function 'SetSpecialColumnHeaderText
//Public Function SetRowHeaderForeColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStart As Integer, ByVal ColumnStartWithinHeader As Integer, ByVal RowEnd As Integer, ByVal ColumnEndWithinHeader As Integer, ByVal ForeColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.RowHeader
//rh = SSControlToUse.ActiveSheet.RowHeader
//rh.Cells(RowStart, ColumnStartWithinHeader, RowEnd, ColumnEndWithinHeader).ForeColor = ForeColor
//SetRowHeaderForeColor = True
//End Function 'SetRowHeaderBackColor
//Public Function SetColumnHeaderText(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Col As Integer, ByVal HorizontalAlignment As FarPoint.Win.spread.CellHorizontalAlignment, ByVal VerticalAlignment As FarPoint.Win.spread.CellVerticalAlignment, ByVal FontFamily As String, ByVal FontSize As Integer, ByRef FontStyle As FontStyle, ByVal LabelText As String) As Boolean
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).HorizontalAlignment = HorizontalAlignment
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).VerticalAlignment = VerticalAlignment
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).Font = New Font(FontFamily, FontSize, FontStyle)
//SSControlToUse.ActiveSheet.ColumnHeader.Columns(Col).Label = LabelText
//SetColumnHeaderText = True
//End Function 'SetColumnHeaderText
//Public Function SetColumnHeaderRowCount(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowCount As Integer) As Boolean
//Dim r As FarPoint.Win.Spread.ColumnHeader
//r = SSControlToUse.ActiveSheet.ColumnHeader
//r.RowCount = RowCount
//SetColumnHeaderRowCount = True
//End Function 'SetRowHeaderColumnCount
//Public Function SetColumnHeaderSpan(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Column As Integer, ByVal RowCount As Integer, ByVal ColumnCount As Integer) As Boolean
//SSControlToUse.ActiveSheet.AddColumnHeaderSpanCell(Row, Column, RowCount, ColumnCount)
//End Function
//Public Function SetColumnHeaderBackColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal BackColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.ColumnHeader
//rh = SSControlToUse.ActiveSheet.ColumnHeader
//rh.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).BackColor = BackColor
//SetColumnHeaderBackColor = True
//End Function 'SetColumnHeaderBackColor
//Public Function SetColumnHeaderForeColor(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal RowStartWithinHeader As Integer, ByVal ColumnStart As Integer, ByVal RowEndWithinHeader As Integer, ByVal ColumnEnd As Integer, ByVal ForeColor As System.Drawing.Color) As Boolean
//Dim rh As FarPoint.Win.Spread.ColumnHeader
//rh = SSControlToUse.ActiveSheet.ColumnHeader
//rh.Cells(RowStartWithinHeader, ColumnStart, RowEndWithinHeader, ColumnEnd).ForeColor = ForeColor
//SetColumnHeaderForeColor = True
//End Function 'SetColumnHeaderForeColor
//Public Function ClearComboBoxForRange(ByRef SSControlToUse As FarPoint.Win.Spread.FpSpread, ByVal Row As Integer, ByVal Col As Integer, ByVal Row2 As Integer, ByVal Col2 As Integer) As Boolean
//Dim ct As New FarPoint.Win.Spread.CellType.ComboBoxCellType
//SSControlToUse.ActiveSheet.Cells(Row, Col, Row2, Col2).CellType = ct
//ct.Clear()
//ClearComboBoxForRange = True
//End Function 'ClearComboBoxForRange
public enum BorderStyle
{
Left = 0,
Top = 1,
Right = 2,
Bottom = 3,
None = 4,
Builder = 5,
Full = 6,
OutSide= 7,
OutSideBold = 8
}
}
}
扫一扫
1225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
