Excel数据透视表是汇总、分析、浏览和呈现数据的好方法，它具有容易操作和灵活度高等优点，用户只需单击几下即可使用数据透视表创建一个日常的报表。本文将介绍如何使用Spire.XLS组件设置Excel数据透视表的边框和填充。

//加载Excel文件
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"测试文档.xlsx");

//获取第一个工作表
Worksheet sheet = workbook.Worksheets[0];

//将第一个数据透视表转换为XlsPivotTable对象
XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

//透视表的边框和填充都是通过样式来设置的
//先自定义一个样式“ptstyle”
string styleName = "ptstyle";
pt.CustomTableStyleName = styleName;
PivotTableStyle pivotTableStyle = new PivotTableStyle(styleName);
PivotStyle pivotStyle = workbook.CreatePivotStyle();

//给透视表设置填充颜色
pivotStyle.Fill.PatternColorObject.SetKnownColor(ExcelColors.Color19);

//分别设置上下左右的边框样式和颜色
pivotStyle.Borders[BordersLineType.EdgeLeft].Color = Color.Blue;
pivotStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Medium;
pivotStyle.Borders[BordersLineType.EdgeRight].Color = Color.Red;
pivotStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Hair;
pivotStyle.Borders[BordersLineType.EdgeTop].Color = Color.Green;
pivotStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
pivotStyle.Borders[BordersLineType.EdgeBottom].Color = Color.Yellow;
pivotStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thick; 

//将这个样式应用到整个透视表
pivotTableStyle.Styles.Add(PivotTableElement.wholeTable, pivotStyle);
workbook.AddPivotTableStyle(pivotTableStyle);          

//保存excel文件
workbook.SaveToFile("结果文档.xlsx", ExcelVersion.Version2010);

'加载Excel文件 Dim workbook As New Workbook() workbook.LoadFromFile("测试文档.xlsx") '获取第一个工作表
Dim sheet As Worksheet = workbook.Worksheets(0)

'将第一个数据透视表转换为XlsPivotTable对象 Dim pt As XlsPivotTable = TryCast(sheet.PivotTables(0),XlsPivotTable) '透视表的边框和填充都是通过样式来设置的
'先自定义一个样式“ptstyle” Dim styleName As String = "ptstyle" pt.CustomTableStyleName = styleName Dim pivotTableStyle As New PivotTableStyle(styleName) Dim pivotStyle As PivotStyle = workbook.CreatePivotStyle() '给透视表设置填充颜色
pivotStyle.Fill.PatternColorObject.SetKnownColor(ExcelColors.Color19)

'分别设置上下左右的边框样式和颜色 pivotStyle.Borders(BordersLineType.EdgeLeft).Color = Color.Blue pivotStyle.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Medium pivotStyle.Borders(BordersLineType.EdgeRight).Color = Color.Red pivotStyle.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Hair pivotStyle.Borders(BordersLineType.EdgeTop).Color = Color.Green pivotStyle.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin pivotStyle.Borders(BordersLineType.EdgeBottom).Color = Color.Yellow pivotStyle.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thick '将这个样式应用到整个透视表
pivotTableStyle.Styles.Add(PivotTableElement.wholeTable, pivotStyle)
workbook.AddPivotTableStyle(pivotTableStyle)

'保存excel文件 workbook.SaveToFile("结果文档.xlsx",ExcelVersion.Version2010)

效果如下：