| 
| 
excel数据透视表是汇总、分析、浏览和呈现数据的好方法，它具有容易操作和灵活度高等优点，用户只需单击几下即可使用数据透视表创建一个日常的报表。本文将介绍如何使用spire.xls组件设置excel数据透视表的边框和填充。
c#
//加载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);vb.net
'加载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)效果如下：
