//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); object[,] sourceData = new object[,] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" }, { 1, "Bose 785593-0050", "Consumer Electronics", 4270, new DateTime(2011, 1, 6), "United States" }, { 2, "Canon EOS 1500D", "Consumer Electronics", 8239, new DateTime(2011, 1, 7), "United Kingdom" }, { 3, "Haier 394L 4Star", "Consumer Electronics", 617, new DateTime(2011, 6, 8), "United States" }, { 4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new DateTime(2012, 1, 10), "Canada" }, { 5, "Mi LED 40inch", "Consumer Electronics", 2626, new DateTime(2012, 2, 10), "Germany" }, { 6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new DateTime(2012, 1, 11), "United States" }, { 7, "Iphone XR", "Mobile", 9062, new DateTime(2011, 1, 11), "Australia" }, { 8, "OnePlus 7Pro", "Mobile", 6906, new DateTime(2012, 5, 16), "New Zealand" }, { 9, "Redmi 7", "Mobile", 2417, new DateTime(2012, 1, 16), "France" }, { 10, "Samsung S9", "Mobile", 7431, new DateTime(2012, 1, 16), "Canada" }, { 11, "OnePlus 7Pro", "Mobile", 8250, new DateTime(2013, 1, 16), "Germany" }, { 12, "Redmi 7", "Mobile", 7012, new DateTime(2012, 3, 18), "United States" }, { 13, "Bose 785593-0050", "Consumer Electronics", 1903, new DateTime(2013, 1, 20), "Germany" }, { 14, "Canon EOS 1500D", "Consumer Electronics", 2824, new DateTime(2012, 4, 22), "Canada" }, { 15, "Haier 394L 4Star", "Consumer Electronics", 6946, new DateTime(2013, 1, 24), "France" }, }; IWorksheet worksheet = workbook.Worksheets[0]; // Set not to group date/time fileds in PivotTable automatically. workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = false; worksheet.Range["Q1:V16"].Value = sourceData; IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["Q1:V16"]); IPivotTable pivotTable = worksheet.PivotTables.Add(pivotCache, worksheet.Range["A1"], "pivottable1"); pivotTable.PivotFields["Category"].Orientation = PivotFieldOrientation.RowField; pivotTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; pivotTable.PivotFields["Date"].Orientation = PivotFieldOrientation.ColumnField; pivotTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; worksheet.Range["A:P"].AutoFit(); // Save to an excel file workbook.Save("ConfigAutomaticGroupDateTimeInPivotTable.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim sourceData(,) As Object = { {"Order ID", "Product", "Category", "Amount", "Date", "Country"}, {1, "Bose 785593-0050", "Consumer Electronics", 4270, #2011-1-6#, "United States"}, {2, "Canon EOS 1500D", "Consumer Electronics", 8239, #2011-1-7#, "United Kingdom"}, {3, "Haier 394L 4Star", "Consumer Electronics", 617, #2011-1-8#, "United States"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, #2012-1-10#, "Canada"}, {5, "Mi LED 40inch", "Consumer Electronics", 2626, #2012-2-10#, "Germany"}, {6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, #2012-1-11#, "United States"}, {7, "Iphone XR", "Mobile", 9062, #2011-1-11#, "Australia"}, {8, "OnePlus 7Pro", "Mobile", 6906, #2012-5-16#, "New Zealand"}, {9, "Redmi 7", "Mobile", 2417, #2012-1-16#, "France"}, {10, "Samsung S9", "Mobile", 7431, #2012-1-16#, "Canada"}, {11, "OnePlus 7Pro", "Mobile", 8250, #2013-1-16#, "Germany"}, {12, "Redmi 7", "Mobile", 7012, #2012-3-18#, "United States"}, {13, "Bose 785593-0050", "Consumer Electronics", 1903, #2013-1-20#, "Germany"}, {14, "Canon EOS 1500D", "Consumer Electronics", 2824, #2012-4-22#, "Canada"}, {15, "Haier 394L 4Star", "Consumer Electronics", 6946, #2013-1-24#, "France"} } Dim worksheet As IWorksheet = workbook.Worksheets(0) ' Set not to group date/time fileds in PivotTable automatically. workbook.Options.Data.AutomaticGroupDateTimeInPivotTable = False worksheet.Range("Q1:V16").Value = sourceData Dim pivotcache = workbook.PivotCaches.Create(worksheet.Range("Q1:V16")) Dim pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range!A1, "pivottable1") pivottable.PivotFields!Category.Orientation = PivotFieldOrientation.RowField pivottable.PivotFields!Product.Orientation = PivotFieldOrientation.RowField pivottable.PivotFields!Date.Orientation = PivotFieldOrientation.ColumnField pivottable.PivotFields!Amount.Orientation = PivotFieldOrientation.DataField worksheet.Range("A:P").AutoFit() ' save to an excel file workbook.Save("ConfigAutomaticGroupDateTimeInPivotTable.xlsx")