/// <summary>C:\Users\User\source\repos\ExcelWorkVariances\ExcelDataHandler\packages.config
/// Method creates Pivot table
/// </summary>
/// <param name="worksheet"></param>
public static void CreateMyPivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
Excel.Worksheet secondWorksheet = workbook.Sheets.Add();
try
{
secondWorksheet.Name = "pivot_table";
app.ActiveWindow.DisplayGridlines = false;
Excel.Range oRange = worksheet.UsedRange;
Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = workbook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = secondWorksheet.PivotTables("Confidence");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("ID"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PLACE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NAME"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PRICE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("UNITS"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
secondWorksheet.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
app.DisplayAlerts = false;
secondWorksheet.Activate();
secondWorksheet.get_Range("B1", "B1").Select();
worksheet.Activate();
}
catch (Exception)
{
}
}
public static void CreatePivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet)
{
Excel.Worksheet secondWorksheet = (Worksheet)workbook.Sheets.Add();
try
{
Excel.Range last = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range range = worksheet.get_Range("G1", last);
int lastUsedRow = last.Row;
int lastUsedColumn = last.Column;
Excel.Range oResizeRange = worksheet.Range[worksheet.Cells[1, 7],
worksheet.Cells[lastUsedRow, lastUsedColumn]];
secondWorksheet.Name = "pivot_table";
app.ActiveWindow.DisplayGridlines = false;
//Excel.Range oRange = worksheet.UsedRange;
Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = workbook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence and Latency", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = (Excel.PivotTable)secondWorksheet.PivotTables("Labels");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Confidence"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("Latency"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
secondWorksheet.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
app.DisplayAlerts = false;
secondWorksheet.Activate();
secondWorksheet.get_Range("B1", "B1").Select();
}
catch (Exception)
{
// ignored
}
//worksheet.Select();
worksheet.Activate();
}
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)
{
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();
}
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 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;
}
void frmdrill_FormClosed(object sender, FormClosedEventArgs e)
{
if (frmdrill.DialogResult == System.Windows.Forms.DialogResult.OK)
{
System.Data.DataTable dt = frmdrill._dataTable;
Microsoft.Office.Interop.Excel.DataTable dtEx;
Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook;
_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[1, i + 1];
_range.Font.Bold = true;
_range.set_Value(Type.Missing, dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
Excel.Range _range = (Excel.Range)_wsheet.Cells[i + 2, j + 1];
_range.set_Value(Type.Missing, dt.Rows[i][j]);
}
}
string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count;
_wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet;
string des = _wpivotsheet.Name + "!R3C1";
_wbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10);
}
}
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 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();
}
public void RefreshDataSourcePivotTables(KeyValuePair <string, DataTable> query)
{
foreach (MSExcel.Worksheet sheet in _view.Sheets)
{
foreach (MSExcel.PivotTable pivotTable in sheet.PivotTables())
{
_app.EnableEvents = false;
try
{
string sourceSheet = pivotTable.Name.Substring(0, pivotTable.Name.IndexOf("__"));
if (sourceSheet.ToLower().Equals(query.Key.ToLower()))
{
MSExcel.Range startRange;
if (query.Key.Equals("main"))
{
startRange = _app.Range["StartCell"];
}
else
{
startRange = _app.Worksheets[query.Key].Range["A2"];
}
var rows = query.Value.Rows.Count;
var columns = query.Value.Columns.Count;
MSExcel.Range dataSource = startRange.Offset[-1, 0].Resize[rows + 1, columns]; // Offsest and resize +1 because of header row.
MSExcel.PivotCache cache = _view.PivotCaches().Create(SourceType: MSExcel.XlPivotTableSourceType.xlDatabase,
SourceData: dataSource);
pivotTable.ChangePivotCache(cache);
pivotTable.RefreshTable();
}
}
catch (ArgumentOutOfRangeException)
{
// Catches ArgumentOutOfRangeException that is raised when IndexOf() does not find "__" in pivottable name and returns -1.
// Substring will raise a ArgumentOutOfRangeException because length parameter is -1 and is therefore less than 0.
// Continue execution, because this simply means the pivot table is not tied to the data source we are looking for.
_app.EnableEvents = true;
continue;
}
catch (Exception ex)
{
_app.EnableEvents = true;
MessageBox.Show(ex.Message, "Pivot Table Refresh Exception", MessageBoxButtons.OK);
}
_app.EnableEvents = true;
}
}
}
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();
}
public string ChangeSourceDataPivotTable(string FilePath, string PivotSheetName, string SourceSheetName, string SourceDataRange, string PivotTableName)
{
string result = "";
try
{
string srcData = SourceSheetName + "!" + Ws.Range[SourceDataRange].Address[XlReferenceStyle.xlR1C1];
result = OpenExcel(FilePath, PivotSheetName);
if (result == "")
{
Excel.PivotTable pivot = Ws.PivotTables(PivotTableName);
pivot.ChangePivotCache(Wb.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, srcData));
result = "";
}
}
catch (Exception e)
{
return("Exception caught - " + e.Message);
}
return(result);
}
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 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 outputReport()
{
int row = 0, col = 0;
string formatString;
Excel.Application excelApp = new Excel.Application();
if (excelApp == null)
{
MessageBox.Show("WANRNING!\n<< TRIED TO CREATE REPORT BUT EXCEL IS NOT INSTALLED! >>");
}
else
{
Excel.Workbook workbook = excelApp.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Reports.xlsx");
Excel.Worksheet DataWorksheet = workbook.Worksheets[workbook.Worksheets.Count];
try
{
DataWorksheet.Name = "Data";
DataTable data = DatabaseTools.getReportData();
foreach (DataColumn dataColumn in data.Columns)
{
DataWorksheet.Range["A1"].Offset[0, col].Value = dataColumn.ColumnName;
switch (dataColumn.DataType.ToString())
{
case "System.Decimal":
formatString = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* \" - \"??_-;_-@_-";
break;
case "System.Int32":
formatString = "0";
break;
case "System.DateTime":
formatString = "dd/mm/yyyy";
break;
default:
formatString = "@";
break;
}
DataWorksheet.Range["A1"].Offset[0, col].EntireColumn.NumberFormat = formatString;
col++;
}
row = 2;
foreach (DataRow dataRow in data.Rows)
{
for (col = 0; col < data.Columns.Count; col++)
{
DataWorksheet.Cells[row, col + 1] = dataRow[col] == DBNull.Value ? string.Empty : dataRow[col].ToString();
}
row++;
}
DataWorksheet.Columns.AutoFit();
Excel.Worksheet PivotSheet = workbook.Worksheets.Add();
PivotSheet.Name = DateTime.Now.ToLongDateString();
Excel.Range dataRange = DataWorksheet.UsedRange;
Excel.PivotCache cache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange);
Excel.PivotCaches pivotCaches = workbook.PivotCaches();
pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange).CreatePivotTable(PivotSheet.Cells[1, 1], "Table");
Excel.PivotTable pivotTable = PivotSheet.PivotTables("Table");
Excel.PivotField field = pivotTable.PivotFields("Job Name");
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
field.LayoutCompactRow = false;
field.Subtotals = new bool[12] {
false, false, false, false, false, false, false, false, false, false, false, false
};
field = pivotTable.PivotFields("Order Number");
field.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
field.Subtotals = new bool[12] {
false, false, false, false, false, false, false, false, false, false, false, false
};
field = pivotTable.PivotFields("Total Value");
field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
field.Caption = "Total Values";
field = pivotTable.PivotFields("Average Value");
field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
field.Caption = "Average Values";
PivotSheet.UsedRange.Columns.AutoFit();
pivotTable.MergeLabels = true;
pivotTable.ShowValuesRow = false;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
}
catch (Exception e)
{
MessageBox.Show(string.Format(e.Message + "\n" + e.InnerException + "\n" + e.Data));
}
finally
{
workbook.Save();
workbook.Close();
excelApp.Quit();
}
}
}
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 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 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) {}
}
//성공 1 실패 0 만든 기존파일에 내용추가 피벗 경간
static int makePivotPosition(Excel.Workbook wb, Dictionary <string, List <readVO> > lists, String Start, String End)
{
try
{
Excel.Worksheet copyws = null;
Excel.Worksheet pivotws = null;
foreach (KeyValuePair <string, List <readVO> > data in lists)
{
string sheet_name = null;
copyws = wb.Worksheets[data.Key] as Excel.Worksheet;
sheet_name = copyws.Name;
pivotws = wb.Sheets.Add(After: wb.Sheets[wb.Sheets.Count]);
pivotws.Name = sheet_name + "_피벗테이블(경간)";
Excel.Range oRange = copyws.get_Range(Start, End);
// specify first cell for pivot table
Excel.Range oRange2 = pivotws.get_Range("B2", "B2");
// create Pivot Cache and Pivot Table
Excel.PivotCache oPivotCache = (Excel.PivotCache)wb.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange);
// I have error on this line
Excel.PivotTable oPivotTable = (Excel.PivotTable)pivotws.PivotTables().Add(oPivotCache, oRange2, "Summary");
// create Pivot Field, note that name will be the same as column name on sheet one
Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields(1);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
int count = oPivotTable.PivotFields(1).PivotItems.Count;
oPivotField.PivotItems(count).visible = false;
oPivotField.Subtotals[1] = true;
oPivotField.Subtotals[1] = false;
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(3);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
count = oPivotTable.PivotFields(3).PivotItems.Count;
oPivotField.PivotItems(count).visible = false;
oPivotField.Subtotals[3] = true;
oPivotField.Subtotals[3] = false;
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(2);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
count = oPivotTable.PivotFields(2).PivotItems.Count;
oPivotField.PivotItems(count).visible = false;
oPivotField.Subtotals[2] = true;
oPivotField.Subtotals[2] = false;
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(5);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
count = oPivotTable.PivotFields(5).PivotItems.Count;
oPivotField.PivotItems(count).visible = false;
oPivotField.Subtotals[5] = true;
oPivotField.Subtotals[5] = false;
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(11);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
count = oPivotTable.PivotFields(11).PivotItems.Count;
oPivotField.PivotItems(count).visible = false;
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(9);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
oPivotField.Value = "합계:개소";
oPivotField = (Excel.PivotField)oPivotTable.PivotFields(10);
oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
oPivotField.Function = Excel.XlConsolidationFunction.xlSum;
oPivotField.Value = "합계:물량";
Excel.PivotField dataField = oPivotTable.DataPivotField;
dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
Excel.Range rs = pivotws.UsedRange;
rs.Columns.AutoFit();
oPivotTable.SubtotalHiddenPageItems = true;
}
return(1);
}
catch (Exception ex)
{
Console.WriteLine(ex);
ReleaseExcelProcess(excelApp);
MessageBox.Show(ex.ToString(), "오류발생", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(0);
}
}
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 btnAceptar_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
libro = app.Workbooks.Add();
libro.DefaultPivotTableStyle = "PivotStyleLight26";
Excel.PivotCache pivotCache = libro.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal);
//string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcel"].ConnectionString;
string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcelLocal"].ConnectionString;
//string MyConString = ConfigurationManager.ConnectionStrings["ODBCMinusculas"].ConnectionString;
string strFecha = dateTimePicker1.Value.ToString("yyyy-MM-dd");
string command = "SELECT * FROM ventash WHERE Fecha >='" + strFecha + "'";
pivotCache.Connection = MyConString;
pivotCache.CommandText = command;
#region ValorAgregado
Excel.Worksheet sheetAgregado = libro.Sheets.Add();
libro.Sheets[libro.ActiveSheet.Name].Select();
libro.Sheets[libro.ActiveSheet.Name].Name = "Valor agregado";
Excel.PivotTables pivotTablesAgregado = sheetAgregado.PivotTables();
Excel.PivotTable pivotTableAgregado = pivotTablesAgregado.Add(pivotCache, app.Range["A4"], "Valor agregado");
sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Position = 1;
sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Position = 2;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
app.Range["B6"].Select();
sheetAgregado.PivotTables("Valor agregado").ColumnGrand = false;
sheetAgregado.PivotTables("Valor agregado").RowGrand = false;
sheetAgregado.PivotTables("Valor agregado").TableStyle2 = "PivotStyleLight26";
sheetAgregado.PivotTables("Valor agregado").CalculatedFields.Add("ValorAgregado", "=IF(TotalPublico>0,(TotalPublico/TotalCosto)-1)", true);
sheetAgregado.PivotTables("Valor agregado").PivotFields("ValorAgregado").Orientation = Excel.XlPivotFieldOrientation.xlDataField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Suma de ValorAgregado").Caption = "Valor agregado";
sheetAgregado.PivotTables("Valor agregado").PivotFields("Valor agregado").NumberFormat = "0,00%";
sheetAgregado.Cells[6, 1].Select();
object[] periodosValor = { false, false, false, false, true, false, true };
Excel.Range rangeValor = sheetAgregado.get_Range("a6");
rangeValor.Group(true, true, 1, periodosValor);
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGrafico = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Valor agregado";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGrafico);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Valor agregado"].Left = 0;
app.ActiveSheet.Shapes["Valor agregado"].Top = 300;
app.ActiveSheet.Shapes["Valor agregado"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Valor agregado"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Valor agregado"].ThreeD.FieldOfView = 10;
app.ActiveChart.ChartTitle.Text = "Valor agregado";
app.Range["A1"].Select();
#endregion
#region Prendas
Excel.Worksheet sheetPrendas = libro.Sheets.Add();
string hojaPrendas = libro.ActiveSheet.Name;
libro.Sheets[hojaPrendas].Select();
libro.Sheets[hojaPrendas].Name = "Prendas";
Excel.PivotTables pivotTablesPrendas = sheetPrendas.PivotTables();
Excel.PivotTable pivotTablePrendas = pivotTablesPrendas.Add(pivotCache, app.Range["A4"], "Prendas");
sheetPrendas.PivotTables("Prendas").TableStyle2 = "PivotStyleLight26";
sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Position = 1;
sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Position = 2;
sheetPrendas.PivotTables("Prendas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalPrendas = pivotTablePrendas.PivotFields("Prendas");
fldTotalPrendas.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPrendas.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPrendas.Name = " Prendas";
sheetPrendas.Cells[6, 1].Select();
object[] periodosPrendas = { false, false, false, false, true, false, true };
Excel.Range rangePrendas = sheetPrendas.get_Range("a6");
rangePrendas.Group(true, true, 1, periodosPrendas);
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoPrendas = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Prendas";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoPrendas);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Prendas"].Left = 0;
app.ActiveSheet.Shapes["Prendas"].Top = 300;
app.ActiveSheet.Shapes["Prendas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Prendas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Prendas"].ThreeD.FieldOfView = 10;
app.ActiveSheet.PivotTables("Prendas").RowGrand = false;
app.ActiveChart.ChartTitle.Text = "Unidades vendidas";
app.Range["A1"].Select();
#endregion
#region Periodos
Excel.Worksheet sheetDiferenciaPeriodos = libro.Sheets.Add();
libro.Sheets[libro.ActiveSheet.Name].Select();
libro.Sheets[libro.ActiveSheet.Name].Name = "Diferencia períodos";
Excel.PivotTables pivotTablesPeriodos = sheetDiferenciaPeriodos.PivotTables();
Excel.PivotTable pivotTableDif = pivotTablesPeriodos.Add(pivotCache, app.Range["A4"], "Diferencia periodos");
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Position = 2;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalPeriodo = pivotTableDif.PivotFields("TotalPublico");
fldTotalPeriodo.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPeriodo.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPeriodo.Name = " Ventas períodos";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.Range["B6"].Select();
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").ColumnGrand = false;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").RowGrand = false;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").TableStyle2 = "PivotStyleLight26";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%";
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoPeriodos = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Diferencia periodos";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoPeriodos);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Diferencia periodos"].Left = 0;
app.ActiveSheet.Shapes["Diferencia periodos"].Top = 300;
app.ActiveSheet.Shapes["Diferencia periodos"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Diferencia periodos"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.FieldOfView = 10;
app.Range["A1"].Select();
#endregion
#region Ventas2
Excel.Worksheet sheetVentas2 = libro.Sheets.Add();
string hojaVentas2 = libro.ActiveSheet.Name;
libro.Sheets[hojaVentas2].Select();
libro.Sheets[hojaVentas2].Name = "Ventas2";
Excel.PivotTables pivotTablesVentas2 = sheetVentas2.PivotTables();
Excel.PivotTable pivotTableVentas2 = pivotTablesVentas2.Add(pivotCache, app.Range["A4"], "Ventas2");
sheetVentas2.PivotTables("Ventas2").TableStyle2 = "PivotStyleLight26";
sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Position = 1;
sheetVentas2.PivotTables("Ventas2").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
sheetVentas2.PivotTables("Ventas2").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
Excel.PivotField fldTotalVentas2 = pivotTableVentas2.PivotFields("TotalPublico");
fldTotalVentas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalVentas2.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalVentas2.Name = " Ventas";
Excel.PivotField fldTotalPrendas2 = pivotTableVentas2.PivotFields("Prendas");
fldTotalPrendas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalPrendas2.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalPrendas2.Name = " Prendas";
sheetVentas2.Cells[6, 1].Select();
object[] periodosVentas2 = { false, false, false, false, true, false, true };
Excel.Range rangeVentas2 = sheetVentas2.get_Range("a6");
rangeVentas2.Group(true, true, 1, periodosVentas2);
sheetVentas2.PivotTables("Ventas2").PivotFields(" Ventas").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.ActiveSheet.PivotTables("Ventas2").RowGrand = false;
app.Range["A1"].Select();
#endregion
#region Ventas
Excel.Worksheet sheetVentas = libro.Sheets.Add();
string hojaVentas = libro.ActiveSheet.Name;
libro.Sheets[hojaVentas].Select();
libro.Sheets[hojaVentas].Name = "Ventas";
Excel.PivotTables pivotTablesVentas = sheetVentas.PivotTables();
Excel.PivotTable pivotTableVentas = pivotTablesVentas.Add(pivotCache, app.Range["A4"], "Ventas");
sheetVentas.PivotTables("Ventas").TableStyle2 = "PivotStyleLight26";
sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Position = 1;
sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Position = 2;
sheetVentas.PivotTables("Ventas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Excel.PivotField fldTotalVentas = pivotTableVentas.PivotFields("TotalPublico");
fldTotalVentas.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
fldTotalVentas.Function = Excel.XlConsolidationFunction.xlSum;
fldTotalVentas.Name = " Ventas";
sheetVentas.Cells[6, 1].Select();
object[] periodosVentas = { false, false, false, false, true, false, true };
Excel.Range rangeVentas = sheetVentas.get_Range("a6");
rangeVentas.Group(true, true, 1, periodosVentas);
sheetVentas.PivotTables("Ventas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetVentas.PivotTables("Ventas").PivotFields("Años").Position = 1;
sheetVentas.PivotTables("Ventas").PivotFields(" Ventas").NumberFormat = "$ #.##0";
libro.ShowPivotTableFieldList = false;
app.Range["A5"].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select();
app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select();
Excel.Range rangoGraficoVentas = app.Selection;
app.ActiveSheet.Shapes.AddChart.Select();
app.ActiveSheet.Shapes(1).Name = "Ventas";
app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn;
app.ActiveChart.SetSourceData(Source: rangoGraficoVentas);
app.ActiveChart.ClearToMatchStyle();
app.ActiveChart.ChartStyle = 42;
app.ActiveChart.ClearToMatchStyle();
app.ActiveSheet.Shapes["Ventas"].Left = 0;
app.ActiveSheet.Shapes["Ventas"].Top = 300;
app.ActiveSheet.Shapes["Ventas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Ventas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse
, Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft);
app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationX = -30;
app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationY = 100;
app.ActiveSheet.Shapes["Ventas"].ThreeD.FieldOfView = 10;
app.ActiveSheet.PivotTables("Ventas").RowGrand = false;
app.Range["A1"].Select();
#endregion
sheetPrendas.PivotTables("Prendas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetPrendas.PivotTables("Prendas").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom;
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)";
sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%";
sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Position = 1;
sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlPageField;
sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Position = 2;
app.ActiveWorkbook.Connections["Conexión"].ODBCConnection.Connection = "ODBC;DATABASE";
libro.Sheets["Hoja1"].Select();
app.ActiveWindow.SelectedSheets.Delete();
libro.Sheets["Ventas"].Select();
libro.Saved = true;
app.Visible = true;
Cursor.Current = Cursors.Arrow;
}
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);
}
//this method will create pivot table in excel file
public string OfficeDll()
{
string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Content/ProductReport.xlsx");
int rows = 0;
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(filepath);
Excel.Worksheet excelworksheet = excelWorkBook.ActiveSheet;
Excel.Worksheet sheet2 = excelWorkBook.Sheets.Add();
try
{
sheet2.Name = "Pivot Table";
excelApp.ActiveWindow.DisplayGridlines = false;
Excel.Range oRange = excelworksheet.UsedRange;
Excel.PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet
Excel.PivotCaches pch = excelWorkBook.PivotCaches();
pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[3, 3], "PivTbl_2", Type.Missing, Type.Missing); // Create Pivot table
Excel.PivotTable pvt = sheet2.PivotTables("PivTbl_2");
pvt.ShowDrillIndicators = true;
pvt.InGridDropZones = false;
Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PLACE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NAME"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("PRICE"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
fld.set_Subtotals(1, false);
fld = ((Excel.PivotField)pvt.PivotFields("NoOfUnits"));
fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
sheet2.UsedRange.Columns.AutoFit();
pvt.ColumnGrand = true;
pvt.RowGrand = true;
excelApp.DisplayAlerts = false;
excelworksheet.Delete();
sheet2.Activate();
sheet2.get_Range("B1", "B1").Select();
excelWorkBook.SaveAs(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelApp.DisplayAlerts = false;
excelWorkBook.Close(0);
excelApp.Quit();
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApp);
}
catch (Exception ex)
{
excelWorkBook.Close(0);
excelApp.Quit();
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApp);
return(ex.Message);
}
return(filepath);
}
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();
}
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;
}
static void makeGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.Worksheet chartSheet = null;
Excel.Range range = null;
Excel.Range dataR = null;
int rowC = 0;
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "table";
chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
chartSheet.Name = "graph";
Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
dataR = dataSheet.UsedRange;
rowC = dataR.Rows.Count;
range = newSheet.get_Range("A1");
PivotCaches pCs = workbook.PivotCaches();
PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
PivotField fA = pT.PivotFields("Time");
PivotField fB = pT.PivotFields("Command");
fA.Orientation = XlPivotFieldOrientation.xlRowField;
fA.Position = 1;
fB.Orientation = XlPivotFieldOrientation.xlColumnField;
fB.Position = 1;
pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);
ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
Chart chartP = pChart.Chart;
chartP.SetSourceData(pT.TableRange1, Type.Missing);
chartP.ChartType = XlChartType.xlLine;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
using (StreamWriter outfile = File.AppendText("output.txt")) {
outfile.WriteLine("Did have issues interacting with Excel on " + file);
}
} finally {
/*Excel.Application excelApp = null;
* Excel.Workbook workbook = null;
* Excel.Sheets sheets = null;
* Excel.Worksheet dataSheet = null;
* Excel.Worksheet newSheet = null;
* Excel.Worksheet chartSheet = null;
* Excel.Range range = null;
* Excel.Range dataR = null;*/
releaseObject(dataR);
releaseObject(range);
releaseObject(chartSheet);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
}
