public static Excel.PivotTable CopyPivotTable(Excel.PivotTable pt) { Excel.Application excelApp = pt.Application; var worksheet = (Excel.Worksheet)pt.Parent; worksheet.Select(); pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true); Excel.Range rng = (Excel.Range)excelApp.Selection; rng.Copy(); Excel.Worksheet ws = (Excel.Worksheet)excelApp.Sheets.Add(); ws.Paste(); return(ws.Range["A1"].PivotTable); }
public static Excel.Range CopyPivotTable(Excel.PivotTable pt) { Excel.Application XlApp = pt.Application; Excel.Worksheet sourceSheet = (Excel.Worksheet)pt.Parent; sourceSheet.Select(); pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true); Excel.Range sourceRange = (Excel.Range)XlApp.Selection; sourceRange.Copy(); Excel.Worksheets sheets = (Excel.Worksheets)XlApp.Sheets; Excel.Worksheet destSheet = (Excel.Worksheet)sheets.Add(); destSheet.Paste(); return(destSheet.Range["A1"]); }
private void PivotGroupingWindowOkButton_Click(object sender, EventArgs e) { if (groups.Count <= 1) // groups are empty { return; } groups.ForEach(groupItem => { if (groupItem.Item2.Count <= 1) { return; } Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable; Excel.PivotFields pfs = pt.RowFields; string selectedFields = pfs.Item(0).Name + "["; groupItem.Item2.ForEach(item => selectedFields += item + ","); selectedFields = selectedFields.Substring(0, selectedFields.Length - 1); selectedFields += "]"; pt.PivotSelect(selectedFields, Excel.XlPTSelectionMode.xlLabelOnly); var selection = Globals.PivotGroupingAddIn.Application.Selection; selection.Group(); foreach (Excel.PivotField pf in pt.PivotFields()) { foreach (Excel.PivotItem rowItem in pf.PivotItems()) { if (rowItem.Value.StartsWith("Group")) { rowItem.Value = groupItem.Item1; } } } }); this.Close(); }
private static void AddPivotTableFreeCells(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp, bool isMonth, DateTime date, DateTime?dateMonthEnd = null, bool removeCrossing = false) { ws.Cells[1, 1].Select(); string sheetName; if (isMonth) { sheetName = "Сводная таблица текущий месяц"; } else { sheetName = "Сводная таблица"; } string pivotTableName = @"PivotTable"; Excel.Worksheet wsPivote = wb.Sheets[sheetName]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); int position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = position; position++; if (!removeCrossing) { pivotTable.PivotFields("Пересечение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Пересечение").Position = position; position++; } pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = position; position++; pivotTable.PivotFields("Врач").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Врач").Position = position; position++; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = position; position++; pivotTable.AddDataField(pivotTable.PivotFields("Всего"), "(Всего)", Excel.XlConsolidationFunction.xlSum); pivotTable.AddDataField(pivotTable.PivotFields("Занято"), "(Занято)", Excel.XlConsolidationFunction.xlSum); pivotTable.CalculatedFields().Add("% занятых слотов", "='Занято'/'Всего'", true); pivotTable.PivotFields("% занятых слотов").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % занятых слотов").Caption = " % занятых слотов"; //pivotTable.AddDataField(pivotTable.PivotFields("% занятых слотов"), "(% занятых слотов)", Excel.XlConsolidationFunction.xlAverage); pivotTable.PivotFields(" % занятых слотов").NumberFormat = "0,00%"; // ActiveSheet.PivotTables("PivotTable").CalculatedFields.Add "Поле2", _ // "=Занято/Всего", True //ActiveSheet.PivotTables("PivotTable").PivotFields("Поле2").Orientation = _ // xlDataField //Columns("F:F").Select // Selection.Style = "Percent" // Range("F3").Select if (isMonth) { CultureInfo cultureInfoOriginal = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); for (DateTime dateToHide = date; dateToHide.Date <= dateMonthEnd.Value.Date; dateToHide = dateToHide.AddDays(1)) { string pivotItem = dateToHide.ToShortDateString(); Console.WriteLine("pivotItem: " + pivotItem); pivotTable.PivotFields("Дата").PivotItems(pivotItem).Visible = false; } Thread.CurrentThread.CurrentCulture = cultureInfoOriginal; } else { pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата").Position = 1; pivotTable.PivotFields("Дата").AutoGroup(); pivotTable.PivotFields("Дата").PivotFilters.Add2(Excel.XlPivotFilterType.xlAfter, null, date.AddDays(-1).ToShortDateString(), null, null, null, null, null, true); try { pivotTable.PivotFields("Месяцы").Orientation = Excel.XlPivotFieldOrientation.xlHidden; } catch (Exception) { } } pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; pivotTable.DisplayFieldCaptions = false; pivotTable.PivotFields("(Всего)").NumberFormat = "0,00"; pivotTable.PivotFields("(Занято)").NumberFormat = "0,00"; //pivotTable.PivotFields("(% занятых слотов)").NumberFormat = "0,0%"; pivotTable.PivotSelect("' % занятых слотов'", Excel.XlPTSelectionMode.xlDataAndLabel, true); xlApp.Selection.FormatConditions.AddColorScale(3); xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority(); xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].Type = Excel.XlConditionValueTypes.xlConditionValueLowestValue; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].FormatColor.Color = 5287936; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].Type = Excel.XlConditionValueTypes.xlConditionValuePercentile; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].Value = 65; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].FormatColor.Color = 8711167; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].Type = Excel.XlConditionValueTypes.xlConditionValueHighestValue; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].FormatColor.Color = 255; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ScopeType = Excel.XlPivotConditionScope.xlDataFieldScope; pivotTable.PivotFields("Порядок сортировки").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Порядок сортировки").Position = 1; pivotTable.PivotFields("Порядок сортировки").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Порядок сортировки").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Отделение").ShowDetail = false; if (!removeCrossing) { pivotTable.PivotFields("Пересечение").ShowDetail = false; } pivotTable.PivotFields("Филиал").ShowDetail = false; pivotTable.DisplayErrorString = true; wsPivote.Range["A1"].Select(); wb.ShowPivotTableFieldList = false; wsPivote.Range["C1"].Select(); try { xlApp.Selection.Ungroup(); } catch (Exception) {} }