static void Main(string[] args)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx");
Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1
Excel.Worksheet xlWsNew = (Excel.Worksheet)xlWb.Sheets.Add();
// find the data range
Excel.Range dataRange = getDataRange(ref xlWs);
// start by creating the PivotCache - this tells Excel that there is a data connection
// to data inside the workbook (could be used to get external data, too)
Excel.PivotCache pc = xlWb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase
, dataRange
, Excel.XlPivotTableVersionList.xlPivotTableVersion14);
// create the pivot table and set the destination to the new sheet at A1
Excel.PivotTable pt = pc.CreatePivotTable(xlWsNew.Range["A1"]);
// get the PivotField "Same" for easy referencing
Excel.PivotField pf = (Excel.PivotField)pt.PivotFields("Same");
// first add the count
pt.AddDataField(pf, "Count of Same", Excel.XlConsolidationFunction.xlCount);
// now add the row with the same field
pf.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pf.Position = 1;
// behold!!!
xlWsNew.Select();
xlApp.Visible = true;
}
private static void UnclosedProtocolsAddPivotTableDepartments(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"WorkTimePivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная по отделениям"];
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);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Подразделение").Position = 2;
pivotTable.PivotFields("ФИО врача").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО врача").Position = 3;
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("DCODE").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("DCODE").Position = 4;
pivotTable.PivotFields("DCODE").Subtotals =
new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("DCODE").LayoutForm = Excel.XlLayoutFormType.xlTabular;
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.xlSum);
pivotTable.CalculatedFields().Add("Процент неподписанных", "='Протокол не подписан' /'Всего протоколов'", true);
pivotTable.PivotFields("Процент неподписанных").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю Процент неподписанных").Caption = "Доля неподписанных протоколов";
pivotTable.PivotFields("Доля неподписанных протоколов").NumberFormat = "0,00%";
pivotTable.PivotFields("Филиал").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов");
pivotTable.PivotFields("Подразделение").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов");
pivotTable.PivotFields("ФИО врача").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов");
pivotTable.PivotFields("Статус сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Статус сотрудника").Position = 1;
pivotTable.PivotFields("Подразделение").ShowDetail = false;
pivotTable.PivotFields("Филиал").ShowDetail = false;
pivotTable.HasAutoFormat = false;
wsPivote.Columns[1].ColumnWidth = 60;
wsPivote.Columns[2].ColumnWidth = 12;
wb.ShowPivotTableFieldList = false;
}
private static void AddPivotTable(Excel.Workbook wb,
Excel.Worksheet ws,
Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"HalvaPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
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);
pivotTable.HasAutoFormat = false;
pivotTable.PivotFields("Совпадение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Совпадение").Position = 1;
pivotTable.PivotFields("Адрес Торговой точки").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Адрес Торговой точки").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Дата обработки транзакции Банком"),
"Количество совпадений", Excel.XlConsolidationFunction.xlCount);
wsPivote.Activate();
wsPivote.Columns["A:H"].Select();
xlApp.Selection.ColumnWidth = 15;
xlApp.Selection.WrapText = true;
xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop;
wsPivote.Range["A1"].Select();
pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
}
private static void TelemedicineAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"TelemedicinePivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int columnLast = ws.UsedRange.Columns.Count;
int rowLast = ws.UsedRange.Rows.Count;
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowLast + "C" + columnLast, 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("FILIAL_SHORTNAME").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("FILIAL_SHORTNAME").Position = 1;
pivotTable.PivotFields("SERVICE_TYPE").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("SERVICE_TYPE").Position = 2;
pivotTable.PivotFields("CLIENT_CATEGORY").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("CLIENT_CATEGORY").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("CLIENT_HITSNUM"), "Кол-во", Excel.XlConsolidationFunction.xlCount);
pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
pivotTable.ShowDrillIndicators = false;
}
internal Excel.PivotTable CreateSalesPivotTable(Excel.Range range, String filePath)
{
string fileDirectory = System.IO.Path.GetDirectoryName(filePath);
string fileName = System.IO.Path.GetFileName(filePath);
string pivotTableName = Properties.Resources.SalesAndProfitPivotTableName;
Excel.PivotCache cache = this.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing);
cache.Connection = String.Format(
CultureInfo.CurrentUICulture,
salesPivotTableConnectionTemplate,
fileDirectory);
cache.CommandType = Excel.XlCmdType.xlCmdSql;
cache.CommandText = String.Format(
CultureInfo.CurrentUICulture,
salesPivotTableQueryTemplate,
fileName);
Excel.PivotTable pivotTable = cache.CreatePivotTable(range, pivotTableName, missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);
// Adjusts the properties of the new PivotTable to
// format information the desired way.
pivotTable.ErrorString = " -- ";
pivotTable.DisplayErrorString = true;
pivotTable.NullString = " -- ";
return(pivotTable);
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"FrontOfficeClientsPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C4", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("ФИО сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО сотрудника").Position = 1;
pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность").Position = 2;
pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Кол-во клиентов"), "Кол-во клиентов ", Excel.XlConsolidationFunction.xlSum);
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("Дата").NumberFormat = "[$-ru-RU]Д МММ;@";
wb.ShowPivotTableFieldList = false;
pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private void PivotData()
{
index++;
Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches();
Excel.Range pivotData = (Excel.Range)osheet.UsedRange;
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index);
Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status");
rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status");
valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches;
//Month Slicer
string nameDateTime = "Slicer_DATETIME" + index.ToString();
string nameMonth = "Slicer_Month" + index.ToString();
Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline);
Excel.Slicers monthSlicers = monthSlicerCache.Slicers;
Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing,
nameMonth, "Date Range", 160, 10, 250, 150);
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"MisTimeSheetPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
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);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Полное имя доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Полное имя доктора").Position = 2;
pivotTable.PivotFields("Идентификатор доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Идентификатор доктора").Position = 3;
pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность").Position = 4;
pivotTable.PivotFields("Должность (справочник)").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность (справочник)").Position = 5;
pivotTable.PivotFields("Дата графика работ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата графика работ").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Кол-во часов (план)"), "Сумма кол-во часов (план)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма кол-во часов (план)").NumberFormat = "# ##0,00";
pivotTable.PivotFields("Дата графика работ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Должность (справочник)").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Идентификатор доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Полное имя доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
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("Должность (справочник)").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Идентификатор доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Полное имя доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Группа филиалов").ShowDetail = false;
//pivotTable.PivotFields("Название").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"RecordsFromInsuranceCompaniesPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
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);
pivotTable.PivotFields("Название страховой").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Название страховой").Position = 1;
pivotTable.PivotFields("Имя оператора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Имя оператора").Position = 2;
pivotTable.PivotFields("Пациент").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Пациент").Position = 3;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 4;
pivotTable.PivotFields("Доктор").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Доктор").Position = 5;
pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата назначения").Position = 1;
pivotTable.PivotFields("Название страховой").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Имя оператора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Пациент").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Отделение").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Доктор").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
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("Имя оператора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Пациент").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Отделение").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Доктор").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Дата назначения").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.AddDataField(
pivotTable.PivotFields("SCHEDID"),
"Кол-во",
Excel.XlConsolidationFunction.xlCount);
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"PivotTableScheduleRecords";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C16", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("ФИО пользователя").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО пользователя").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 2;
pivotTable.AddDataField(pivotTable.PivotFields("Дата и время записи"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во записей").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("По направлению?"), "По направлению", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("По направлению").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Прием состоялся?"), "Прием состоялся", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Прием состоялся").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Сумма, всего"), "Сумма оказанных услуг (руб)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма оказанных услуг (руб)").NumberFormat = "# ##0,00 ?";
//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("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//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;
//pivotTable.PivotFields("Филиал").ShowDetail = false;
pivotTable.PivotFields("ФИО пользователя").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"PivotTableEmployeesCovidTreat";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C14", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Диагноз МКБ").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Диагноз МКБ").Position = 2;
pivotTable.PivotFields("Диагноз").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Диагноз").Position = 3;
pivotTable.PivotFields("ФИО пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО пациента").Position = 4;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 5;
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("Диагноз").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Диагноз").LayoutForm = Excel.XlLayoutFormType.xlTabular;
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;
//pivotTable.PivotFields("Филиал").ShowDetail = false;
//pivotTable.PivotFields("ФИО пользователя").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
pivotTable.DisplayFieldCaptions = false;
wsPivote.Columns["B:B"].ColumnWidth = 60;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"FrontOfficeClientsPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C12", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Дата назначения").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 2;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 3;
pivotTable.PivotFields("Новый пациент?").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Новый пациент?").Position = 1;
wsPivote.Range["B1"].Value2 = "Новый пациент?";
wsPivote.Range["A2"].Value2 = "Филиал";
pivotTable.AddDataField(pivotTable.PivotFields("№ ИБ"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Филиал").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"PromoPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
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);
pivotTable.PivotFields("Название").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Название").Position = 1;
pivotTable.PivotFields("Группа филиалов").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Группа филиалов").Position = 2;
pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Услуга").Position = 3;
//pivotTable.PivotFields("Название").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Группа филиалов").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//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("Группа филиалов").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Услуга").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Группа филиалов").ShowDetail = false;
pivotTable.PivotFields("Название").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
private static void FssInfoAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[3, 1].Select();
string pivotTableName = @"FssInfoPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int wsRowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.Range["A2:BG" + wsRowsUsed], 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Год").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Год").Position = 1;
//pivotTable.PivotFields("Год").AutoSort(Excel.XlSortOrder.xlDescending, "Год");
pivotTable.PivotFields("Квартал").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Квартал").Position = 2;
//pivotTable.PivotFields("Квартал").AutoSort(Excel.XlSortOrder.xlDescending, "Квартал");
pivotTable.PivotFields("Месяц").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Месяц").Position = 3;
//pivotTable.PivotFields("Месяц").AutoSort(Excel.XlSortOrder.xlDescending, "Месяц");
pivotTable.PivotFields("Неделя").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Неделя").Position = 4;
//pivotTable.PivotFields("Неделя").AutoSort(Excel.XlSortOrder.xlDescending, "Неделя");
pivotTable.AddDataField(pivotTable.PivotFields("Номер ЛН"),
"Кол-во Номер ЛН", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Адрес").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Адрес").Position = 1;
pivotTable.HasAutoFormat = false;
int wsPivoteColumnUsed = wsPivote.UsedRange.Columns.Count;
wsPivote.Columns["B:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed)].Select();
xlApp.Selection.ColumnWidth = 6;
wsPivote.Range["B2:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed) + "5"].Select();
xlApp.Selection.HorizontalAlignment = Excel.Constants.xlGeneral;
xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop;
xlApp.Selection.WrapText = true;
pivotTable.PivotFields("Месяц").ShowDetail = false;
pivotTable.TableStyle2 = "PivotStyleMedium13";
pivotTable.PivotFields("Неделя").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Месяц").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Квартал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Год").PivotItems("2017").Visible = false;
//pivotTable.PivotFields("Год").PivotItems("2018").Visible = false;
//pivotTable.PivotFields("Адрес").PivotItems("г.Москва, Вадковский переулок, д.18").Visible = false;
pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
public void Create()
{
Excel.Worksheet PivotSheet = this.PivotSheet;
string Location = this.PivotTableLocation;
string TableName = this.PivotTableName;
char Status = this.Status;
ListingSheet.Select();
string LastRow = "";
string LastCol = "";
string LastCell = "";
long lRow = 0;
long lCol = 0;
////////////
//FIND THE LAST NON-BLANK CELL IN COLUMN A
lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
LastRow = "R" + lRow;
lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column;
LastCol = "C" + lCol;
LastCell = ListingSheet.Cells[lRow, lCol].Address;
Excel.Range PivotData = ListingSheet.Range["A1", LastCell];
PivotData.Select();
Excel.PivotCaches pch = ListingBook.PivotCaches();
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName);
PivotSheet.Select();
Excel.PivotField pvf = pvt.PivotFields("Status");
pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
switch ((ListingStatus)Status)
{
case ListingStatus.Active:
case ListingStatus.Sold:
pvf.CurrentPage = Status.ToString();
break;
case ListingStatus.OffMarket:
try { pvf.PivotItems(((char)ListingStatus.Active).ToString()).Visible = false; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Sold).ToString()).Visible = false; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Terminate).ToString()).Visible = true; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Cancel).ToString()).Visible = true; } catch (Exception e) { };
try { pvf.PivotItems(((char)ListingStatus.Expire).ToString()).Visible = true; } catch (Exception e) { };
pvf.EnableMultiplePageItems = true;
break;
}
//Group 1 S/A
pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("S/A").Name = "Neighborhood";
//Group 2 Complex
pvt.PivotFields("Complex/Subdivision Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Complex/Subdivision Name").Name = this.ReportType.ToString().IndexOf("Detached") < 0 ? "Complex" : "SubDivision";
//Group 3 Address
pvt.PivotFields("Address2").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Address2").Name = "Civic Address";
//Group 4 UnitNo
if (this.bShowUnitNo || this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.PivotFields("Unit#").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("Unit#").Name = "Unit No";
}
pvt.AddDataField(pvt.PivotFields("MLS"), "Count", Excel.XlConsolidationFunction.xlCount);
pvt.AddDataField(pvt.PivotFields("Price0"), "Price", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("CDOM"), "Days On Mkt", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("TotFlArea"), "Floor Area", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("PrcSqft"), "$PSF", Excel.XlConsolidationFunction.xlAverage);
//TEST Add Calculated Fields
//Excel.PivotField ptField;
//Excel.CalculatedFields cfField = pvt.CalculatedFields();
//ptField = cfField.Add("New PSF", "='PrcSqft' * 'Age'", true);
//pvt.AddDataField(ptField, " New PSF", Excel.XlConsolidationFunction.xlAverage);
//
pvt.AddDataField(pvt.PivotFields("Age"), "Building Age", Excel.XlConsolidationFunction.xlAverage);
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.AddDataField(pvt.PivotFields("StratMtFee"), "Monthly Fee", Excel.XlConsolidationFunction.xlAverage);
}
else
{
pvt.AddDataField(pvt.PivotFields("Lot Sz (Sq.Ft.)"), "Land Size", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("LandValue"), "Land Assess.", Excel.XlConsolidationFunction.xlAverage);
}
pvt.AddDataField(pvt.PivotFields("BCAValue"), "BC Assess.", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Change%"), "Chg% to BCA", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Lot$ PerSF"), "Lot$PSF", Excel.XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields("Improve$ PerSF"), "Improve$PSF", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Price").NumberFormat = "$#,##0";
pvt.PivotFields("Days On Mkt").NumberFormat = "0";
pvt.PivotFields("Floor Area").NumberFormat = "0";
pvt.PivotFields("$PSF").NumberFormat = "$#,##0";
pvt.PivotFields("Building Age").NumberFormat = "0";
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
pvt.PivotFields("Monthly Fee").NumberFormat = "$#,##0";
}
else
{
pvt.PivotFields("Land Size").NumberFormat = "0";
pvt.PivotFields("Land Assess.").NumberFormat = "$#,##0";
}
pvt.PivotFields("BC Assess.").NumberFormat = "$#,##0";
pvt.PivotFields("Chg% to BCA").NumberFormat = "0%";
pvt.PivotFields("Lot$PSF").NumberFormat = "$#,##0";
pvt.PivotFields("Improve$PSF").NumberFormat = "$#,##0";
pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow);
}
public void CreateCityPivotTable(Excel.Worksheet PivotSheet, string Location, string TableName, ReportType TableType)
{
ListingSheet.Select();
string LastRow = "";
string LastCol = "";
string LastCell = "";
long lRow = 0;
long lCol = 0;
string RankBaseField = "";
////////////
//FIND THE LAST NON-BLANK CELL IN COLUMN A
lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
LastRow = "R" + lRow;
lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column;
LastCol = "C" + lCol;
LastCell = ListingSheet.Cells[lRow, lCol].Address;
Excel.Range PivotData = ListingSheet.Range["A1", LastCell];
PivotData.Select();
Excel.PivotCaches pch = ListingBook.PivotCaches();
Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData);
Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName);
//pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels
PivotSheet.Select();
//Excel.PivotField pvf = pvt.PivotFields("Status");
//pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
//pvf.CurrentPage = Status;
//Group 1 S/A
switch (TableType)
{
case ReportType.MonthlyDetachedAllCities:
pvt.PivotFields("City").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
RankBaseField = "City";
break;
case ReportType.MonthlyDetachedAllCommunities:
pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvt.PivotFields("S/A").Caption = "Neighborhood";
RankBaseField = "Neighborhood";
break;
default:
break;
}
//pvt.PivotFields("S/A").Name = "Neighborhood";
//Sales Total Amount
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Rank", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Rank").BaseField = RankBaseField;
//Sort By Rank
pvt.PivotFields(RankBaseField).AutoSort(2, "Rank");
//Total Amount
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Total Sales Amount", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Total Sales Amount").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Market Share", Excel.XlConsolidationFunction.xlSum);
pvt.PivotFields("Market Share").Calculation = Excel.XlPivotFieldCalculation.xlPercentOfTotal;
//Sales Count
pvt.AddDataField(pvt.PivotFields("Status"), "Sales", Excel.XlConsolidationFunction.xlCount);
pvt.PivotFields("Sales").NumberFormat = "0";
//Ave Sold Price
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. Sold Price", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. Sold Price").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. S.Price Rank", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. S.Price Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Avg. S.Price Rank").BaseField = RankBaseField;
//Price Per SqFt
pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PerSQFT", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. $PerSQFT").NumberFormat = "$#,##0";
pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PSF Rank", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. $PSF Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending;
pvt.PivotFields("Avg. $PSF Rank").BaseField = RankBaseField;
//Days On Market
pvt.AddDataField(pvt.PivotFields("CDOM"), "Avg. Days OnMkt", Excel.XlConsolidationFunction.xlAverage);
pvt.PivotFields("Avg. Days OnMkt").NumberFormat = "0";
pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow);
}
private void GenerateReport()
{
#region Initialize
Excel.Workbook activeWorkBook = null;
Excel.Worksheet pivotWorkSheet = null;
Excel.PivotCaches pivotCaches = null;
Excel.PivotCache pivotCache = null;
Excel.PivotTable pivotTable = null;
Excel.PivotFields pivotFields = null;
Excel.PivotField monthPivotField = null;
Excel.PivotField statusPivotField = null;
Excel.PivotField resolvedPivotField = null;
Excel.PivotField threatIdPivotField = null;
Excel.PivotField threatIdCountPivotField = null;
Excel.SlicerCaches slicerCaches = null;
Excel.SlicerCache monthSlicerCache = null;
Excel.Slicers monthSlicers = null;
Excel.Slicer monthSlicer = null;
Excel.SlicerCache statusSlicerCache = null;
Excel.Slicers statusSlicers = null;
Excel.Slicer statusSlicer = null;
Excel.SlicerCache resolvedSlicerCache = null;
Excel.Slicers resolvedSlicers = null;
Excel.Slicer resolvedSlicer = null;
#endregion
try
{
activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook;
try
{
pivotWorkSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Threat Reports");
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet.Application.DisplayAlerts = false;
pivotWorkSheet.Delete();
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Agent Reports";
pivotWorkSheet.Activate();
}
catch
{
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet;
pivotWorkSheet.Name = "Agent Reports";
pivotWorkSheet.Activate();
}
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWindow.DisplayGridlines = false;
#region Create Headings
// Create headings
// =================================================================================================================
Excel.Range title = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "A1");
title.ClearFormats();
title.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
title.Font.Color = System.Drawing.Color.White;
title.InsertIndent(1);
title.Font.Size = 18;
title.VerticalAlignment = -4108; // xlCenter
Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "CA1");
titleRow.Select();
titleRow.RowHeight = 33;
titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color));
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "Threat Reports";
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 1] = "Generated by: " + userName;
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 1] = DateTime.Now.ToString("f");
Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", "CA3");
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); //
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous
rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick
#endregion
// Create the Pivot Table
pivotCaches = activeWorkBook.PivotCaches();
activeWorkBook.ShowPivotTableFieldList = false;
// pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, "Threats!$A$4:$" + ExcelColumnLetter(colCount) + "$" + rowCount);
// string rangeName = "Threats!$A$4:$T$100";
string rangeName = "'Agent Data'!$A$4:$" + eHelper.ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString();
pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName);
// pivotTable = pivotCache.CreatePivotTable("Reports!R3C1");
pivotTable = pivotCache.CreatePivotTable("'Agent Reports'!R7C1");
pivotTable.NullString = "0";
// Set the Pivot Fields
pivotFields = (Excel.PivotFields)pivotTable.PivotFields();
// Month Pivot Field
monthPivotField = (Excel.PivotField)pivotFields.Item("Created Date");
monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthPivotField.Position = 1;
monthPivotField.DataRange.Cells[1].Group(true, true, Type.Missing, new bool[] { false, false, false, false, true, true, true });
// Mitigation Status Pivot Field
statusPivotField = (Excel.PivotField)pivotFields.Item("Mitigation Status");
statusPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
// Resolved Pivot Field
resolvedPivotField = (Excel.PivotField)pivotFields.Item("Resolved");
resolvedPivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
// Threat ID Pivot Field
threatIdPivotField = (Excel.PivotField)pivotFields.Item("ID");
// Count of Threat ID Field
threatIdCountPivotField = pivotTable.AddDataField(threatIdPivotField, "# of Threats", Excel.XlConsolidationFunction.xlCount);
slicerCaches = activeWorkBook.SlicerCaches;
// Month Slicer
monthSlicerCache = slicerCaches.Add(pivotTable, "Created Date", "CreatedDate");
monthSlicers = monthSlicerCache.Slicers;
monthSlicer = monthSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Created Date", "Created Date", 80, 480, 144, 100);
// Mitigation Status Slicer
statusSlicerCache = slicerCaches.Add(pivotTable, "Mitigation Status", "MitigationStatus");
statusSlicers = statusSlicerCache.Slicers;
statusSlicer = statusSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Mitigation Status", "Mitigation Status", 80, 634, 144, 100);
// Resolved Slicer
resolvedSlicerCache = slicerCaches.Add(pivotTable, "Resolved", "Resolved");
resolvedSlicers = resolvedSlicerCache.Slicers;
resolvedSlicer = resolvedSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Resolved", "Resolved", 80, 788, 144, 100);
// Slicer original sizes top 15, width 144, height 200
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error generating report", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
#region Finally
if (resolvedSlicer != null)
{
Marshal.ReleaseComObject(resolvedSlicer);
}
if (resolvedSlicers != null)
{
Marshal.ReleaseComObject(resolvedSlicers);
}
if (resolvedSlicerCache != null)
{
Marshal.ReleaseComObject(resolvedSlicerCache);
}
if (statusSlicer != null)
{
Marshal.ReleaseComObject(statusSlicer);
}
if (statusSlicers != null)
{
Marshal.ReleaseComObject(statusSlicers);
}
if (statusSlicerCache != null)
{
Marshal.ReleaseComObject(statusSlicerCache);
}
if (monthSlicer != null)
{
Marshal.ReleaseComObject(monthSlicer);
}
if (monthSlicers != null)
{
Marshal.ReleaseComObject(monthSlicers);
}
if (monthSlicerCache != null)
{
Marshal.ReleaseComObject(monthSlicerCache);
}
if (slicerCaches != null)
{
Marshal.ReleaseComObject(slicerCaches);
}
if (threatIdCountPivotField != null)
{
Marshal.ReleaseComObject(threatIdCountPivotField);
}
if (threatIdPivotField != null)
{
Marshal.ReleaseComObject(threatIdPivotField);
}
if (resolvedPivotField != null)
{
Marshal.ReleaseComObject(resolvedPivotField);
}
if (statusPivotField != null)
{
Marshal.ReleaseComObject(statusPivotField);
}
if (monthPivotField != null)
{
Marshal.ReleaseComObject(monthPivotField);
}
if (pivotFields != null)
{
Marshal.ReleaseComObject(pivotFields);
}
if (pivotTable != null)
{
Marshal.ReleaseComObject(pivotTable);
}
if (pivotCache != null)
{
Marshal.ReleaseComObject(pivotCache);
}
if (pivotCaches != null)
{
Marshal.ReleaseComObject(pivotCaches);
}
if (pivotWorkSheet != null)
{
Marshal.ReleaseComObject(pivotWorkSheet);
}
if (activeWorkBook != null)
{
Marshal.ReleaseComObject(activeWorkBook);
}
#endregion
}
}
private static void AddPivotTableReferralUsage(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"RefUsage";
Excel.Worksheet wsPivote = wb.Sheets["Использование направлений"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
//pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//pivotTable.PivotFields("Дата").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 2;
pivotTable.PivotFields("ФИО Сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО Сотрудника").Position = 3;
pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Услуга").Position = 1;
pivotTable.PivotFields("Услуга").CurrentPage = "Итого по услугам";
//pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = true;
//pivotTable.PivotFields("Услуга").EnableMultiplePageItems = false;
pivotTable.AddDataField(pivotTable.PivotFields("ФИО Пациента"), "Кол-во приемов", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во приемов").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего"), "Сумма оказанных услуг", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма оказанных услуг").NumberFormat = "# ##0,00 ?";
pivotTable.CalculatedFields().Add("СрЧек", "='Стоимость, всего'/'Кол-во приемов, исходных'", true);
pivotTable.PivotFields("СрЧек").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю СрЧек").NumberFormat = "# ##0,00 ?";
pivotTable.PivotFields("Сумма по полю СрЧек").Caption = "СрЧек (по приемам)";
pivotTable.AddDataField(pivotTable.PivotFields("Refid"), "Кол-во созданных направлений", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во приемов").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Treatcode2"), "Кол-во приемов (по направлениям)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во приемов (по направлениям)").NumberFormat = "# ##0";
pivotTable.CalculatedFields().Add("% Исполнено направлений_", "=Treatcode2/Refid", true);
pivotTable.PivotFields("% Исполнено направлений_").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % Исполнено направлений_").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю % Исполнено направлений_").Caption = "% Исполнено направлений";
pivotTable.PivotFields("Отделение").ShowDetail = false;
pivotTable.PivotFields("Филиал").ShowDetail = false;
//pivotTable.PivotFields("Дата").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private static void WorkloadAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"WorkloadPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
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.PivotFields("ОТДЕЛЕНИЕ").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ОТДЕЛЕНИЕ").Position = 1;
pivotTable.PivotFields("ФИЛИАЛ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("ФИЛИАЛ").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Загрузка"),
"Средняя загрузка", Excel.XlConsolidationFunction.xlAverage);
pivotTable.PivotFields("Средняя загрузка").NumberFormat = "# ##0,00";
pivotTable.PivotFields("Не учитывать (=1)").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Не учитывать (=1)").Position = 1;
pivotTable.PivotFields("Не учитывать (=1)").ClearAllFilters();
pivotTable.PivotFields("Не учитывать (=1)").CurrentPage = "(blank)";
wsPivote.Activate();
wsPivote.Columns["B:N"].Select();
xlApp.Selection.ColumnWidth = 10;
wsPivote.Range["A1"].Select();
pivotTable.HasAutoFormat = false;
pivotTable.ShowTableStyleColumnStripes = true;
pivotTable.TableStyle2 = "PivotStyleMedium2";
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
foreach (Excel.PivotItem item in pivotTable.PivotFields("ОТДЕЛЕНИЕ").PivotItems())
{
if (!item.Name.Contains("ИТОГО"))
{
item.Visible = false;
}
}
wsPivote.Columns["B:N"].Select();
xlApp.Selection.FormatConditions.AddColorScale(3);
xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority();
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 0;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.TintAndShade = 0;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 70;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = 13562593;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor.TintAndShade = 0;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 150;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = 6870690;
xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor.TintAndShade = 0;
wsPivote.Range["C1"].Select();
wb.ShowPivotTableFieldList = false;
}
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) {}
}
private static void NonAppearanceAddPivotTablePatientsWithProblem(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"PatientsWithProblem";
Excel.Worksheet wsPivote = wb.Sheets["Пациенты с неявками"];
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);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Подразделение").Position = 2;
pivotTable.PivotFields("ФИО доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО доктора").Position = 3;
pivotTable.PivotFields("Дата лечения").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Дата лечения").Position = 4;
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("ФИО пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО пациента").Position = 5;
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("История болезни пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("История болезни пациента").Position = 6;
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("Номер телефона пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Номер телефона пациента").Position = 7;
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("Отметки без лечений + Без отметок и без лечений").Orientation =
Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Отметки без лечений + Без отметок и без лечений").Position = 1;
pivotTable.PivotFields("Отметки без лечений + Без отметок и без лечений").CurrentPage = "(ALL)";
pivotTable.PivotFields("Отметки без лечений + Без отметок и без лечений").PivotItems("0").Visible = false;
pivotTable.PivotFields("Отметки без лечений + Без отметок и без лечений").EnableMultiplePageItems = true;
pivotTable.HasAutoFormat = false;
//pivotTable.PivotFields("ФИО доктора").ShowDetail = false;
//pivotTable.PivotFields("Подразделение").ShowDetail = false;
pivotTable.PivotFields("Филиал").ShowDetail = false;
//pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
}
public void CreatePivot(string sheetName, int colCount, int rowCount, string pivotTableLocation, string pivotTableName, string rowField, string rowHeader, string columnField, string columnHeader)
{
#region Initialization
Excel.Workbook activeWorkBook = null;
Excel.Worksheet pivotWorkSheet = null;
Excel.PivotCaches pivotCaches = null;
Excel.PivotCache pivotCache = null;
Excel.PivotTable pivotTable = null;
Excel.PivotFields pivotFields = null;
Excel.PivotField rowPivotField = null;
Excel.PivotField columnPivotField = null;
Excel.PivotField countPivotField = null;
Excel.PivotItems pivotItems = null;
Excel.Range pivotRange = null;
Excel.PivotField hiddenPivotField = null;
#endregion
try
{
activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook;
pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
// Create the Pivot Table
pivotCaches = activeWorkBook.PivotCaches();
activeWorkBook.ShowPivotTableFieldList = false;
string rangeName = "'" + sheetName + "'!$A$4:$" + ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString();
pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName);
pivotTable = pivotCache.CreatePivotTable(pivotTableLocation, pivotTableName);
pivotTable.NullString = "0";
pivotTable.HasAutoFormat = false;
// Set the Pivot Fields
pivotFields = (Excel.PivotFields)pivotTable.PivotFields();
// Row Pivot Field
rowPivotField = (Excel.PivotField)pivotFields.Item(rowField);
rowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
rowPivotField.Position = 1;
pivotTable.CompactLayoutRowHeader = rowHeader;
// Column Pivot Field
columnPivotField = (Excel.PivotField)pivotFields.Item(columnField);
// Causes error and not needed
// columnPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
// Count Field
countPivotField = pivotTable.AddDataField(columnPivotField, columnHeader, Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields(rowField).AutoSort(Excel.XlSortOrder.xlDescending, columnHeader);
// Show only Top 10 results for the pivot table
int top10 = 10;
// pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount,
// DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10);
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount,
DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10);
}
else
{
pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add(Type: Excel.XlPivotFilterType.xlTopCount,
DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10);
}
string cellValue = "";
pivotRange = pivotTable.RowRange;
int itemCount = pivotRange.Count - 2; // Minus 2 because of title and total rows
if (itemCount > top10)
{
pivotItems = pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotItems;
for (int i = itemCount - 1; i >= top10; i--)
{
cellValue = (string)(pivotWorkSheet.Cells[pivotRange.Row + i + 1, pivotRange.Column] as Excel.Range).Value.ToString();
pivotItems.Item(cellValue).Visible = false;
}
}
if (pivotTableName == "PivotTableAtRiskUsers")
{
Globals.MostAtRiskUser = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString();
}
else if (pivotTableName == "PivotTableAtRiskGroups")
{
Globals.MostAtRiskGroup = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString();
}
else if (pivotTableName == "PivotTableAtRiskEndpoints")
{
Globals.MostAtRiskEndpoint = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString();
}
string reportTable = "";
string reportLabel = "\"";
string reportValue = "";
reportTable = "<table id=\"newspaper-a\" class=\"sortable\">";
string head1 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column] as Excel.Range).Value.ToString();
string head2 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column + 1] as Excel.Range).Value.ToString();
reportTable = reportTable +
"<thead><tr><th scope=\"col\">" + head1 + "</th><th style=\"text-align:right;\" scope=\"col\" nowrap>" + head2 + "</th></tr></thead><tbody>";
string col1 = "";
string col2 = "";
string labelShorted = "";
int tableItems = itemCount > 10 ? 10 : itemCount;
for (int i = 1; i <= tableItems; i++)
{
col1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column] as Excel.Range).Value.ToString();
col2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column + 1] as Excel.Range).Value.ToString();
reportTable = reportTable + "<tr><td>" + col1 + "</td><td style=\"text-align:right;\">" + col2 + "</td></tr>";
if (tableItems > 5 && col1.Length > 10)
{
labelShorted = col1.Substring(0, 10) + "..";
}
else if (col1.Length > 20)
{
labelShorted = col1.Substring(0, 20) + "..";
}
else
{
labelShorted = col1;
}
reportLabel = reportLabel + labelShorted + "\",\"";
reportValue = reportValue + col2 + ",";
}
string foot1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column] as Excel.Range).Value.ToString();
string foot2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column + 1] as Excel.Range).Value.ToString();
reportTable = reportTable +
"</tbody><tfoot><tr><td>" + foot1 + "</td><td style=\"text-align:right;\">" + foot2 + "</td></tr></tfoot></table>";
reportValue = reportValue.TrimEnd(',');
reportLabel = reportLabel.TrimEnd('\"');
reportLabel = reportLabel.TrimEnd(',');
if (pivotTableName == "PivotTableClassifier")
{
Globals.DetectionEngine = reportTable;
Globals.DetectionEnginesLabel = reportLabel;
Globals.DetectionEnginesValue = reportValue;
}
else if (pivotTableName == "PivotTableFileDisplayName")
{
Globals.InfectedFiles = reportTable;
Globals.InfectedFilesLabel = reportLabel;
Globals.InfectedFilesValue = reportValue;
}
else if (pivotTableName == "PivotTableAtRiskGroups")
{
Globals.MostAtRiskGroups = reportTable;
Globals.MostAtRiskGroupsLabel = reportLabel;
Globals.MostAtRiskGroupsValue = reportValue;
}
else if (pivotTableName == "PivotTableAtRiskUsers")
{
Globals.MostAtRiskUsers = reportTable;
Globals.MostAtRiskUsersLabel = reportLabel;
Globals.MostAtRiskUsersValue = reportValue;
}
else if (pivotTableName == "PivotTableAtRiskEndpoints")
{
Globals.MostAtRiskEndpoints = reportTable;
Globals.MostAtRiskEndpointsLabel = reportLabel;
Globals.MostAtRiskEndpointsValue = reportValue;
}
else if (pivotTableName == "PivotTableIsActive")
{
Globals.NetworkStatus = reportTable;
Globals.NetworkStatusLabel = reportLabel;
Globals.NetworkStatusValue = reportValue;
}
else if (pivotTableName == "PivotTableOs")
{
Globals.EndpointOS = reportTable;
Globals.EndpointOSLabel = reportLabel;
Globals.EndpointOSValue = reportValue;
}
else if (pivotTableName == "PivotTableAgent")
{
Globals.EndpointVersion = reportTable;
Globals.EndpointVersionLabel = reportLabel;
Globals.EndpointVersionValue = reportValue;
}
else if (pivotTableName == "PivotTableApplicationName")
{
Globals.TopApplications = reportTable;
Globals.TopApplicationsLabel = reportLabel;
Globals.TopApplicationsValue = reportValue;
}
Excel.Range colToFormat = pivotWorkSheet.get_Range("K:Q", System.Type.Missing);
colToFormat.EntireColumn.AutoFit();
Excel.Range colToEdit = pivotWorkSheet.get_Range("K:K", System.Type.Missing);
// Was 28, changed 11/9/2017
if (pivotTableName == "PivotTableApplicationName")
{
colToEdit.EntireColumn.ColumnWidth = 70;
}
else if (colToEdit.EntireColumn.ColumnWidth > 35)
{
colToEdit.EntireColumn.ColumnWidth = 35;
}
// Customizing the pivot table style
pivotWorkSheet.PivotTables(pivotTableName).TableStyle2 = "PivotStyleMedium9";
// Remembers the bottom of the pivot table so that the next one will not overlap
Globals.PivotBottom = pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Row + pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count;
Excel.Range rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + Globals.PivotBottom.ToString(), "A" + Globals.PivotBottom.ToString());
// Globals.ChartBottom = (int)rng.Top + (int)rng.Height;
}
catch (Exception ex)
{
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 3] = ex.Message;
/*
* Excel.PivotTable pt = pivotWorkSheet.PivotTables(pivotTableName);
* Excel.Range pr = pt.TableRange2;
* pr.Clear();
*/
}
finally
{
#region Finally
if (countPivotField != null)
{
Marshal.ReleaseComObject(countPivotField);
}
if (columnPivotField != null)
{
Marshal.ReleaseComObject(columnPivotField);
}
if (rowPivotField != null)
{
Marshal.ReleaseComObject(rowPivotField);
}
if (pivotFields != null)
{
Marshal.ReleaseComObject(pivotFields);
}
if (pivotTable != null)
{
Marshal.ReleaseComObject(pivotTable);
}
if (pivotCache != null)
{
Marshal.ReleaseComObject(pivotCache);
}
if (pivotCaches != null)
{
Marshal.ReleaseComObject(pivotCaches);
}
if (activeWorkBook != null)
{
Marshal.ReleaseComObject(activeWorkBook);
}
if (pivotWorkSheet != null)
{
Marshal.ReleaseComObject(pivotWorkSheet);
}
#endregion
}
}
private static void AddPivotTableScheduleResult(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"SchedResult";
Excel.Worksheet wsPivote = wb.Sheets["Кол-во записей, итог по сумме"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
//pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//pivotTable.PivotFields("Дата").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Запись, Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Запись, Должность").Position = 2;
pivotTable.PivotFields("Запись, Автор").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Запись, Автор").Position = 3;
pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Услуга").Position = 1;
pivotTable.PivotFields("Услуга").CurrentPage = "All";
pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = false;
pivotTable.PivotFields("Услуга").EnableMultiplePageItems = true;
pivotTable.AddDataField(pivotTable.PivotFields("Кол-во"), "Планируемое кол-во услуг", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Планируемое кол-во услуг").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Планируемая стоимость, всего"), "Планируемая стоимость (всего)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Планируемая стоимость (всего)").NumberFormat = "# ##0,00 ?";
pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего2"), "Сумма оказанных услуг (по направлениям)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма оказанных услуг (по направлениям)").NumberFormat = "# ##0,00 ?";
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("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular;
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;
pivotTable.PivotFields("Филиал").ShowDetail = false;
//pivotTable.PivotFields("Дата").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private static void MesUsageAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"MesUsagePivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
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);
pivotTable.PivotFields("Тип приема").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Тип приема").Position = 1;
pivotTable.PivotFields("Тип оплаты приема").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Тип оплаты приема").Position = 2;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Подразделение").Position = 2;
pivotTable.PivotFields("ФИО врача").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО врача").Position = 3;
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.PivotFields(" % приемов с направлением, созданным с использованием МЭС").NumberFormat = "0,00%";
pivotTable.AddDataField(pivotTable.PivotFields("Есть направление, созданное самостоятельно"),
"Кол-во приемов с направлениями, созданными самостоятельно", Excel.XlConsolidationFunction.xlSum);
pivotTable.CalculatedFields().Add("% приемов с направлениями, соответствующими МЭС, но созданных самостоятельно",
"='Есть направление, созданное самостоятельно' /Прием", true);
pivotTable.PivotFields("% приемов с направлениями, соответствующими МЭС, но созданных самостоятельно").Orientation =
Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % приемов с направлениями, соответствующими МЭС, но созданных самостоятельно").Caption =
" % приемов с направлениями, соответствующими МЭС, но созданных самостоятельно";
pivotTable.PivotFields(" % приемов с направлениями, соответствующими МЭС, но созданных самостоятельно").NumberFormat = "0,00%";
pivotTable.AddDataField(pivotTable.PivotFields("Услуги из всех направлений соответсвуют МЭС на 100%"),
"Кол-во приемов, обязательные услуги МЭС соответствуют в направлениях на 100%", Excel.XlConsolidationFunction.xlSum);
pivotTable.CalculatedFields().Add("% приемов, обязательные услуги МЭС в направлениях соответствуют на 100%",
"='Услуги из всех направлений соответсвуют МЭС на 100%' /Прием", true);
pivotTable.PivotFields("% приемов, обязательные услуги МЭС в направлениях соответствуют на 100%").Orientation =
Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % приемов, обязательные услуги МЭС в направлениях соответствуют на 100%").Caption =
" % приемов, обязательные услуги МЭС в направлениях соответствуют на 100%";
pivotTable.PivotFields(" % приемов, обязательные услуги МЭС в направлениях соответствуют на 100%").NumberFormat = "0,00%";
pivotTable.AddDataField(pivotTable.PivotFields("% Соответствия МЭС"),
"Средний % соответствия обязательных услуг МЭС услугам в направлениях", Excel.XlConsolidationFunction.xlAverage);
pivotTable.PivotFields("Средний % соответствия обязательных услуг МЭС услугам в направлениях").NumberFormat = "0,00%";
wsPivote.Activate();
wsPivote.Columns["B:I"].Select();
xlApp.Selection.ColumnWidth = 20;
wsPivote.Range["B4:I4"].Select();
xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop;
xlApp.Selection.WrapText = true;
pivotTable.PivotFields("ФИО врача").AutoSort(Excel.XlSortOrder.xlDescending,
"Средний % соответствия обязательных услуг МЭС услугам в направлениях");
pivotTable.PivotFields("Подразделение").AutoSort(Excel.XlSortOrder.xlDescending,
"Средний % соответствия обязательных услуг МЭС услугам в направлениях");
pivotTable.PivotFields("Филиал").AutoSort(Excel.XlSortOrder.xlDescending,
"Средний % соответствия обязательных услуг МЭС услугам в направлениях");
int rowCount = wsPivote.UsedRange.Rows.Count;
AddInteriorColor(wsPivote.Range["C4:D" + rowCount], Excel.XlThemeColor.xlThemeColorAccent4);
AddInteriorColor(wsPivote.Range["E4:F" + rowCount], Excel.XlThemeColor.xlThemeColorAccent5);
AddInteriorColor(wsPivote.Range["G4:H" + rowCount], Excel.XlThemeColor.xlThemeColorAccent6);
wsPivote.Range["A1"].Select();
pivotTable.HasAutoFormat = false;
pivotTable.PivotFields("Подразделение").ShowDetail = false;
pivotTable.PivotFields("Филиал").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
}
private static void AddPivotTableReferralResult(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"RefResult";
Excel.Worksheet wsPivote = wb.Sheets["Реализация направлений"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
//pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
//pivotTable.PivotFields("Дата").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Refid"), "Кол-во направлений (всего)", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во направлений (всего)").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0";
pivotTable.CalculatedFields().Add("% Записей_", "='Кол-во записей'/'Кол-во приемов, исходных'", true);
pivotTable.PivotFields("% Записей_").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % Записей_").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю % Записей_").Caption = "% Записей";
pivotTable.AddDataField(pivotTable.PivotFields("Treatcode2"), "Кол-во приемов (по направлениям)", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во приемов (по направлениям)").NumberFormat = "# ##0";
pivotTable.CalculatedFields().Add("% Приемов (по направлениям)_", "='Кол-во приемов, по направлениям'/'Кол-во приемов, исходных'", true);
pivotTable.PivotFields("% Приемов (по направлениям)_").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % Приемов (по направлениям)_").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю % Приемов (по направлениям)_").Caption = "% Приемов (по направлениям)";
pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего2"), "Сумма оказанных услуг (по направлениям)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма оказанных услуг (по направлениям)").NumberFormat = "# ##0,00 ?";
pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, исходный прием"), "Кол-во уникальных пациентов (всего)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во уникальных пациентов (всего)").NumberFormat = "# ##0 ?";
pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, запись"), "Кол-во уникальных пациентов (запись)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во уникальных пациентов (запись)").NumberFormat = "# ##0";
pivotTable.CalculatedFields().Add("% Записанных пациентов_", "='Уникальность пациента, запись'/'Уникальность пациента, исходный прием'", true);
pivotTable.PivotFields("% Записанных пациентов_").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % Записанных пациентов_").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю % Записанных пациентов_").Caption = "% Записанных пациентов";
pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, прием по направлению"), "Кол-во уникальных пациентов (прием по направлениям)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Кол-во уникальных пациентов (прием по направлениям)").NumberFormat = "# ##0";
pivotTable.CalculatedFields().Add("% Принятых по направлению пациентов_", "='Уникальность пациента, прием по направлению'/'Уникальность пациента, исходный прием'", true);
pivotTable.PivotFields("% Принятых по направлению пациентов_").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю % Принятых по направлению пациентов_").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю % Принятых по направлению пациентов_").Caption = "% Принятых по направлению пациентов";
//ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Записей", "='Кол-во записей'/'Кол-во приемов, исходных'", True
//ActiveSheet.PivotTables("RefResult").PivotFields("% Записей").Orientation = xlDataField
//ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Приемов (по направлениям)", "='Кол-во приемов, по направлениям'/'Кол-во приемов, исходных'", True
//ActiveSheet.PivotTables("RefResult").PivotFields("% Приемов (по направлениям)").Orientation = xlDataField
//ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Записанных пациентов", "='Уникальность пациента, запись'/'Уникальность пациента, исходный прием'", True
//ActiveSheet.PivotTables("RefResult").PivotFields("% Записанных пациентов").Orientation = xlDataField
//ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Принятых по направлению пациентов", "='Уникальность пациента, прием по направлению'/'Уникальность пациента, исходный прием'", True
//ActiveSheet.PivotTables("RefResult").PivotFields("% Принятых по направлению пациентов").Orientation = xlDataField
pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("Услуга").Position = 1;
pivotTable.PivotFields("Услуга").CurrentPage = "All";
pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = false;
pivotTable.PivotFields("Услуга").EnableMultiplePageItems = true;
//pivotTable.AddDataField(pivotTable.PivotFields("№ ИБ"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount);
//pivotTable.PivotFields("Дата").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
pivotTable.HasAutoFormat = false;
wsPivote.Columns["B:L"].ColumnWidth = 15;
wsPivote.Range["B3:L3"].VerticalAlignment = Excel.Constants.xlTop;
wsPivote.Range["B3:L3"].WrapText = true;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Columns["A:A"].Select();
xlApp.Selection.Copy();
ws.Columns["B:B"].Select();
xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
ws.Cells[1, 2].Value = "ФИО";
ws.Columns[2].ColumnWidth = 1;
Excel.Range range = ws.Columns["G:G"];
range.NumberFormat = "m/d/yyyy";
ws.Columns["H:H"].NumberFormat = "[$-x-systime]ч:мм:сс AM/PM";
ws.Columns["I:I"].NumberFormat = "[$-x-systime]ч:мм:сс AM/PM";
ws.Columns["J:J"].NumberFormat = "[ч]:мм:сс;@";
ws.Cells[1, 1].Select();
string pivotTableName = @"WorkTimePivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Pivot"];
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);
pivotTable.PivotFields("Город").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Город").Position = 1;
pivotTable.PivotFields("Компания").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Компания").Position = 2;
pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Подразделение").Position = 3;
pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность").Position = 4;
pivotTable.PivotFields("Физическое лицо").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Физическое лицо").Position = 5;
pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Дата").Position = 6;
pivotTable.PivotFields("ФИО").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
pivotTable.PivotFields("ФИО").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Начало рабочего дня"), "Среднее по полю Начало рабочего дня", Excel.XlConsolidationFunction.xlAverage);
pivotTable.AddDataField(pivotTable.PivotFields("Окончание рабочего дня"), "Среднее по полю Окончание рабочего дня", Excel.XlConsolidationFunction.xlAverage);
pivotTable.AddDataField(pivotTable.PivotFields("Длительность"), "Сумма по полю Длительность", Excel.XlConsolidationFunction.xlSum);
pivotTable.AddDataField(pivotTable.PivotFields("Длительность"), "Среднее по полю Длительность", Excel.XlConsolidationFunction.xlAverage);
pivotTable.AddDataField(pivotTable.PivotFields("Начало рабочего дня"), "Количество по полю Начало рабочего дня", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Среднее по полю Начало рабочего дня").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM";
pivotTable.PivotFields("Среднее по полю Окончание рабочего дня").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM";
pivotTable.PivotFields("Сумма по полю Длительность").NumberFormat = "[ч]:мм:сс;@";
pivotTable.PivotFields("Среднее по полю Длительность").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM";
pivotTable.PivotFields("Город").ShowDetail = false;
pivotTable.PivotFields("Компания").ShowDetail = false;
pivotTable.PivotFields("Подразделение").ShowDetail = false;
pivotTable.PivotFields("Должность").ShowDetail = false;
pivotTable.PivotFields("Физическое лицо").ShowDetail = false;
pivotTable.HasAutoFormat = false;
wsPivote.Columns[1].ColumnWidth = 80;
wsPivote.Columns[2].ColumnWidth = 15;
wsPivote.Columns[3].ColumnWidth = 15;
wsPivote.Columns[4].ColumnWidth = 15;
wsPivote.Columns[5].ColumnWidth = 15;
wsPivote.Columns[6].ColumnWidth = 15;
wsPivote.Rows[3].WrapText = true;
wsPivote.Activate();
wb.ShowPivotTableFieldList = false;
}
private static void NonAppearanceAddPivotTableGeneral(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
ws.Cells[1, 1].Select();
string pivotTableName = @"NonAppearancePivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
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);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Подразделение").Position = 2;
pivotTable.PivotFields("ФИО доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО доктора").Position = 3;
pivotTable.PivotFields("Дата лечения").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Дата лечения").Position = 4;
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("Сумма по полю Общий % Неявок - Отметки без лечений").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю Общий % Неявок - Отметки без лечений").Caption =
"% Неявок - Отметки без лечений (регистратура +, врач – )";
pivotTable.AddDataField(pivotTable.PivotFields("Без отметок и без лечений"),
"Без отметок и лечения (регистратура -, врач -)", Excel.XlConsolidationFunction.xlSum);
pivotTable.CalculatedFields().Add("Общий % Неявок - Без отметок и без лечений",
"= 'Без отметок и без лечений'/'Записано пациентов'", true);
pivotTable.PivotFields("Общий % Неявок - Без отметок и без лечений").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю Общий % Неявок - Без отметок и без лечений").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю Общий % Неявок - Без отметок и без лечений").Caption =
"% Неявок - Без отметок и без лечений (регистратура -, врач -)";
pivotTable.CalculatedFields().Add("Общий % Неявки",
"= ('Отметки без лечений' +'Без отметок и без лечений' )/'Записано пациентов'", true);
pivotTable.PivotFields("Общий % Неявки").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
pivotTable.PivotFields("Сумма по полю Общий % Неявки").NumberFormat = "0,00%";
pivotTable.PivotFields("Сумма по полю Общий % Неявки").Caption = "% Неявки";
pivotTable.HasAutoFormat = false;
pivotTable.PivotFields("ФИО доктора").ShowDetail = false;
pivotTable.PivotFields("Подразделение").ShowDetail = false;
pivotTable.PivotFields("Филиал").ShowDetail = false;
pivotTable.DisplayFieldCaptions = false;
wb.ShowPivotTableFieldList = false;
}