这两天用Aspose.Cells构建一个Excel报表,感觉这个组件还比较好用.记录一下常用的使用知识:
1.创建Workbook和Worksheet
workbook&worksheet1 Workbook wb = new Workbook();wb.Worksheets.Clear();wb.Worksheets.Add("New Worksheet1");//New Worksheet1是Worksheet的nameWorksheet ws = wb.Worksheets[0];
如果直接用下边两句则直接使用默认的第一个Worksheet:
workbook&worksheet2 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;
自定义格式:
cell2 cell.Style.Custom = "ddd, dd mmmm 'yy";
旋转字体:
cell3 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列的RangeRange 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:
formula1 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.插入图片:
pictures1 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 typevalidation.Type = ValidationType.WholeNumber;//Set the operator for the data validationvalidation.Operator = OperatorType.Between;//Set the value or expression associated with the data validationvalidation.Formula1 = "0";//the value or expression associated with the second part of the data validationvalidation.Formula2 = "10";validation.ShowError = true;//Set the validation alert stylevalidation.AlertStyle = ValidationAlertType.Information;//Set the title of the data-validation error dialog boxvalidation.ErrorTitle = "Error";//Set the data validation error messagevalidation.ErrorMessage = " Enter value between 0 to 10";//Set the data validation input messagevalidation.InputMessage = "Data Validation using Condition for Numbers";validation.IgnoreBlank = true;validation.ShowInput = true;validation.ShowError = true;//设置Validations的区域,因为现在要Validations的位置是12,1,所以下面设置对应的也要是12,1CellArea cellArea;cellArea.StartRow = 12;cellArea.EndRow = 12;cellArea.StartColumn = 1;cellArea.EndColumn = 1;validation.AreaList.Add(cellArea);/*要注意 的地方Validations 也是和Range的Style一样,要新增的,否则不生效*/
上边不过是基础中的基础,要了解更多就要自己不断的尝试了.下边奉上帮助文档和DLL:
附送Excel支持的56种颜色: