Excel API Library for .NET - Sample Browser | Document Solutions | Export Pivot Table
[
フレーム]
Document Solutions for Excel can export a spreadsheet with pivot tables to PDF.
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
object[,] sourceData = new object[,] {
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Broccoli", "Vegetables", 8239, new DateTime(2018, 1, 7), "United Kingdom" },
{ 2, "Banana", "Fruit", 617, new DateTime(2018, 1, 8), "United States" },
{ 3, "Banana", "Fruit", 8384, new DateTime(2018, 1, 10), "Canada" },
{ 4, "Beans", "Vegetables", 2626, new DateTime(2018, 1, 10), "Germany" },
{ 5, "Orange", "Fruit", 3610, new DateTime(2018, 1, 11), "United States" },
{ 6, "Broccoli", "Vegetables", 9062, new DateTime(2018, 1, 11), "Australia" },
{ 7, "Banana", "Fruit", 6906, new DateTime(2018, 1, 16), "New Zealand" },
{ 8, "Apple", "Fruit", 2417, new DateTime(2018, 1, 16), "France" },
{ 9, "Apple", "Fruit", 7431, new DateTime(2018, 1, 16), "Canada" },
{ 10, "Banana", "Fruit", 8250, new DateTime(2018, 1, 16), "Germany" },
{ 11, "Broccoli", "Vegetables", 7012, new DateTime(2018, 1, 18), "United States" },
{ 12, "Broccoli", "Vegetables", 2824, new DateTime(2018, 1, 22), "Canada" },
{ 13, "Apple", "Fruit", 6946, new DateTime(2018, 1, 24), "France" },
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["K20:P33"].Value = sourceData;
worksheet.Range["K:P"].ColumnWidth = 15;
// Add pivot table
var pivotcache = workbook.PivotCaches.Create(worksheet.Range["K20:P33"]);
var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1");
worksheet.Range["N21:N35"].NumberFormat = "$#,##0.00";
worksheet.Range["A:G"].ColumnWidth = 12;
//config pivot table's fields
var field_Date = pivottable.PivotFields["Date"];
field_Date.Orientation = PivotFieldOrientation.PageField;
var field_Category = pivottable.PivotFields["Category"];
field_Category.Orientation = PivotFieldOrientation.RowField;
var field_Product = pivottable.PivotFields["Product"];
field_Product.Orientation = PivotFieldOrientation.ColumnField;
var field_Amount = pivottable.PivotFields["Amount"];
field_Amount.Orientation = PivotFieldOrientation.DataField;
field_Amount.NumberFormat = "$#,##0.00";
var field_Country = pivottable.PivotFields["Country"];
field_Country.Orientation = PivotFieldOrientation.RowField;
// Set pivot style
pivottable.TableStyle = "PivotStyleMedium28";
// Save to a pdf file
workbook.Save("SavePivotTable.pdf");
' Create a new Workbook
Dim workbook As New Workbook
Dim sourceData(,) As Object = {
{"Order ID", "Product", "Category", "Amount", "Date", "Country"},
{1, "Broccoli", "Vegetables", 8239, #2018年1月7日#, "United Kingdom"},
{2, "Banana", "Fruit", 617, #2018年1月8日#, "United States"},
{3, "Banana", "Fruit", 8384, #2018年1月10日#, "Canada"},
{4, "Beans", "Vegetables", 2626, #2018年1月10日#, "Germany"},
{5, "Orange", "Fruit", 3610, #2018年1月11日#, "United States"},
{6, "Broccoli", "Vegetables", 9062, #2018年1月11日#, "Australia"},
{7, "Banana", "Fruit", 6906, #2018年1月16日#, "New Zealand"},
{8, "Apple", "Fruit", 2417, #2018年1月16日#, "France"},
{9, "Apple", "Fruit", 7431, #2018年1月16日#, "Canada"},
{10, "Banana", "Fruit", 8250, #2018年1月16日#, "Germany"},
{11, "Broccoli", "Vegetables", 7012, #2018年1月18日#, "United States"},
{12, "Broccoli", "Vegetables", 2824, #2018年1月22日#, "Canada"},
{13, "Apple", "Fruit", 6946, #2018年1月24日#, "France"}
}
Dim worksheet As IWorksheet = workbook.Worksheets(0)
worksheet.Range("K20:P33").Value = sourceData
worksheet.Range("K:P").ColumnWidth = 15
' Add pivot table
Dim pivotcache = workbook.PivotCaches.Create(worksheet.Range("K20:P33"))
Dim pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range!A1, "pivottable1")
worksheet.Range("N21:N35").NumberFormat = "$#,##0.00"
worksheet.Range("A:G").ColumnWidth = 12
'config pivot table's fields
Dim field_Date = pivottable.PivotFields!Date
field_Date.Orientation = PivotFieldOrientation.PageField
Dim field_Category = pivottable.PivotFields!Category
field_Category.Orientation = PivotFieldOrientation.RowField
Dim field_Product = pivottable.PivotFields!Product
field_Product.Orientation = PivotFieldOrientation.ColumnField
Dim field_Amount = pivottable.PivotFields!Amount
field_Amount.Orientation = PivotFieldOrientation.DataField
field_Amount.NumberFormat = "$#,##0.00"
Dim field_Country = pivottable.PivotFields!Country
field_Country.Orientation = PivotFieldOrientation.RowField
' Set pivot style
pivottable.TableStyle = "PivotStyleMedium28"
' save to a pdf file
workbook.Save("SavePivotTable.pdf")