這兩天用Aspose.Cells構建一個Excel報表，感覺這個組件還比較好用.記錄一下常用的使用知識:

1.創建Workbook和Worksheet

Workbook wb = new Workbook();
wb.Worksheets.Clear();
wb.Worksheets.Add("New Worksheet1");//New Worksheet1是Worksheet的name
Worksheet ws = wb.Worksheets[0];

如果直接用下邊兩句則直接使用默認的第一個Worksheet:

Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];

2.給Cell賦值設置背景顏色并加背景色:

cell1

Cell cell = ws.Cells[0, 0];

cell.PutValue("填充"); //必須用PutValue方法賦值

cell.Style.ForegroundColor = Color.Yellow;

cell.Style.Pattern = BackgroundType.Solid;

cell.Style.Font.Size = 10;

cell.Style.Font.Color = Color.Blue;

自定義格式:

cell.Style.Custom = "ddd, dd mmmm 'yy";

旋轉字體:

cell.Style.Rotation = 90;

3.設置Range并賦值加Style

range1

int styleIndex = wb.Styles.Add();

Style style = wb.Styles[styleIndex];

style.ForegroundColor = Color.Yellow;

style.Pattern = BackgroundType.Solid;

style.Font.Size = 10;



//從Cells[0,0]開始創建一個2行3列的Range

Range range = ws.Cells.CreateRange(0, 0, 2, 3);

Cell cell = range[0, 0];

cell.Style.Font = 9;

range.Style = style;

range.Merge();

注意Range不能直接設置Style.必須先定義style再將style賦給Style.其他設置和Cell基本一致.Range的Style會覆蓋Cell定義的Style.另外必須先賦值再傳Style.否則可能不生效。

4.使用Formula:

ws.Cells[0,0].PutValue(1);
ws.Cells[1,0].PutValue(20);
ws.Cells[2,0].Formula="SUM(A1:B1)";
wb.CalculateFormula(true);

Save Excel文件的時候必須調用CalculateFormula方法計算結果。

5.插入圖片:

string imageUrl = System.Web.HttpContext.Current.Server.MapPath("~/images/log_topleft.gif");
ws.Pictures.Add(10, 10, imageUrl);

6.使用Validations:

validations1

Cells cells = ws.Cells;



cells[12, 0].PutValue("Please enter a number other than 0 to 10 in B1 to activate data validation:");

cells[12, 0].Style.IsTextWrapped = true;



cells[12, 1].PutValue(5);

Validations validations = totalSheet.Validations;



Validation validation = validations[validations.Add()];

//Set the data validation type

validation.Type = ValidationType.WholeNumber;

//Set the operator for the data validation

validation.Operator = OperatorType.Between;

//Set the value or expression associated with the data validation

validation.Formula1 = "0";

//the value or expression associated with the second part of the data validation

validation.Formula2 = "10";



validation.ShowError = true;

//Set the validation alert style

validation.AlertStyle = ValidationAlertType.Information;

//Set the title of the data-validation error dialog box

validation.ErrorTitle = "Error";

//Set the data validation error message

validation.ErrorMessage = " Enter value between 0 to 10";

//Set the data validation input message

validation.InputMessage = "Data Validation using Condition for Numbers";

validation.IgnoreBlank = true;

validation.ShowInput = true;

validation.ShowError = true;



//設置Validations的區域，因為現在要Validations的位置是12,1，所以下面設置對應的也要是12,1

CellArea cellArea;

cellArea.StartRow = 12;

cellArea.EndRow = 12;

cellArea.StartColumn = 1;

cellArea.EndColumn = 1;

validation.AreaList.Add(cellArea);



/*

要注意 的地方Validations 也是和Range的Style一樣，要新增的，否則不生效

*/