1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| public void DisplayPivotTableInformation()
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wk;
wk = app.Workbooks.Add(Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)wk.Sheets[1];//Select sheet 7x3 and 7x4
// Specify values for the PivotTable.
ws.get_Range("A1", Type.Missing).Value2 = "Date";
ws.get_Range("A2", Type.Missing).Value2 = "March 1";
ws.get_Range("A3", Type.Missing).Value2 = "March 8";
ws.get_Range("A4", Type.Missing).Value2 = "March 15";
ws.get_Range("B1", Type.Missing).Value2 = "Customer";
ws.get_Range("B2", Type.Missing).Value2 = "Smith";
ws.get_Range("B3", Type.Missing).Value2 = "Jones";
ws.get_Range("B4", Type.Missing).Value2 = "James";
ws.get_Range("C1", Type.Missing).Value2 = "Sales";
ws.get_Range("C2", Type.Missing).Value2 = "23";
ws.get_Range("C3", Type.Missing).Value2 = "17";
ws.get_Range("C4", Type.Missing).Value2 = "39";
// Create and populate the PivotTable.
Excel.PivotTable table1 = new Excel.PivotTable();
table1.PivotTableWizard(
Excel.XlPivotTableSourceType.xlDatabase,
ws.get_Range("A1", "C4"),
ws.get_Range("A10", Type.Missing), "Sales Table", false,
false, true, false, Type.Missing, Type.Missing, false, false,
Excel.XlOrder.xlDownThenOver, Type.Missing, Type.Missing, Type.Missing);
Excel.PivotField customerField =
(Excel.PivotField)table1.PivotFields("Customer");
customerField.Orientation =
Excel.XlPivotFieldOrientation.xlRowField;
customerField.Position = 1;
Excel.PivotField dateField =
(Excel.PivotField)table1.PivotFields("Date");
dateField.Orientation =
Excel.XlPivotFieldOrientation.xlColumnField;
dateField.Position = 1;
table1.AddDataField(table1.PivotFields("Sales"),
"Sales Summary", Excel.XlConsolidationFunction.xlSum);
// Create a NamedRange in the PivotTable and display the
// location.
Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
this.Controls.AddNamedRange(
this.Range["B11", missing], "namedRange1");
namedRange1.Select();
MessageBox.Show("The NamedRange is in the PivotTable report '" +
namedRange1.PivotTable.Name + "' at the location '" +
namedRange1.LocationInTable.ToString() + "'.");
MessageBox.Show("The NamedRange has a PivotCell type of: " +
namedRange1.PivotCell.PivotCellType.ToString());
MessageBox.Show("The NamedRange is in the PivotTable field: " +
namedRange1.PivotField.Name);
MessageBox.Show("The NamedRange is in the PivotTable item: " +
namedRange1.PivotItem.Name);
namedRange1.Group(true, missing, missing, missing);
} |
Partager