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) {}
}
