Data
e-iceblue
Option
Average Column: | Sum Column: | |||
Max Column: | Min Column: | |||
Excel Version: |
downloads
- Demo
- Java
- C# source
This demo shows you how to create a privot table in excel.
import com.spire.xls.*; import com.spire.xls.core.IPivotField; public class CreatePivotTableDemo { public void createPivotTableExcel(String filePath, String resultFilePath){ Workbook workbook = createPivotTable(filePath); workbook.saveToFile(resultFilePath); } public Workbook createPivotTable(String filePath){ Workbook workbook = new Workbook(); workbook.loadFromFile(filePath, ExcelVersion.Version2010); Worksheet worksheet = workbook.getWorksheets().get(0); worksheet.setName("Data Source"); Worksheet worksheet1 = workbook.createEmptySheet(); worksheet1.setName("Pivot Table"); CellRange dataRange = worksheet.getRange().get("A1:G17"); PivotCache cache = workbook.getPivotCaches().add(dataRange); PivotTable pt = worksheet1.getPivotTables().add("Pivot Table",worksheet.getRange().get("A1"),cache); IPivotField r1 = pt.getPivotFields().get("Vendor No"); r1.setAxis(AxisTypes.Row); pt.getOptions().setRowHeaderCaption("Vendor No"); IPivotField r2 = pt.getPivotFields().get("Name"); r2.setAxis(AxisTypes.Row); pt.getDataFields().add(pt.getPivotFields().get("Area"), "Average of Area", SubtotalTypes.Average); pt.getDataFields().add(pt.getPivotFields().get("Sales"), "SUM of Sales", SubtotalTypes.Sum); pt.getDataFields().add(pt.getPivotFields().get("OnHand"), "Max of OnHand", SubtotalTypes.Max); pt.getDataFields().add(pt.getPivotFields().get("OnOrder"), "Min of OnOrder", SubtotalTypes.Min); pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12); pt.calculateData(); worksheet1.getAllocatedRange().autoFitColumns(); worksheet1.getAllocatedRange().autoFitRows(); return workbook; } }
using Spire.Xls; namespace DemoOnlineCode { class CreatePivotTableInExcel { public void demoCreatePivotTableInExcel(string filePath, string resultFilePath) { Workbook workbook = CreatePivotTable(filePath); workbook.SaveToFile(resultFilePath); } public Workbook CreatePivotTable(string filePath) { Workbook workbook = new Workbook(); workbook.LoadFromFile(filePath, ExcelVersion.Version2007); Worksheet sheet = workbook.Worksheets[0]; sheet.Name = "Data Source"; Worksheet sheet2 = workbook.CreateEmptySheet(); sheet2.Name = "Pivot Table"; CellRange dataRange = sheet.Range["A1:G17"]; PivotCache cache = workbook.PivotCaches.Add(dataRange); PivotTable pt = sheet2.PivotTables.Add("Pivot Table", sheet.Range["A1"], cache); var r1 = pt.PivotFields["Vendor No"]; r1.Axis = AxisTypes.Row; pt.Options.RowHeaderCaption = "Vendor No"; var r2 = pt.PivotFields["Name"]; r2.Axis = AxisTypes.Row; pt.DataFields.Add(pt.PivotFields["Area"], "Average of Area", SubtotalTypes.Average); pt.DataFields.Add(pt.PivotFields["Sales"], "SUM of Sales", SubtotalTypes.Sum); pt.DataFields.Add(pt.PivotFields["OnHand"], "Max of OnHand", SubtotalTypes.Max); pt.DataFields.Add(pt.PivotFields["OnOrder"], "Min of OnOrder", SubtotalTypes.Min); pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12; pt.CalculateData(); sheet2.AllocatedRange.AutoFitColumns(); sheet2.AllocatedRange.AutoFitRows(); return workbook; } } }
No Matter How Big or Small Your Project is,
Any technical question related to our product, contact us at support@e-iceblue.com.
Any question related to the purchase of product, contact us at sales@e-iceblue.com.
If you don't find the function you want, please request a free demo from us.