private void FormatColumnWidth()
{
if (PivotSheet.PivotTables(1).RowRange.Columns.Count == 3)
{
PivotSheet.Columns["A"].ColumnWidth = 19.5;
PivotSheet.Columns["B"].ColumnWidth = 17;
PivotSheet.Columns["C"].ColumnWidth = 18.5;
}
else
{
PivotSheet.Columns["A"].ColumnWidth = 19.5;
PivotSheet.Columns["B"].ColumnWidth = 17;
PivotSheet.Columns["C"].ColumnWidth = 18.5;
PivotSheet.Columns["D"].ColumnWidth = 5;
}
int FirstCol = PivotSheet.PivotTables(1).ColumnRange.Column;
int TotalCols = PivotSheet.PivotTables(1).ColumnRange.Columns.Count;
PivotSheet.Columns[FirstCol].ColumnWidth = 6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 15.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 9.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 10.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.5;
}
private void FormatColumnWidth()
{
if (FormatColumnsWidthDone)
{
return;
}
if (PivotSheet.PivotTables(1).RowRange.Columns.Count == 3)
{
PivotSheet.Columns["A"].ColumnWidth = 20; //neighborhood
PivotSheet.Columns["B"].ColumnWidth = 17; //PlanNumb
PivotSheet.Columns["C"].ColumnWidth = 18.5; //Address
}
else
{
PivotSheet.Columns["A"].ColumnWidth = 20; //neighborhood
PivotSheet.Columns["B"].ColumnWidth = 17; //complex
PivotSheet.Columns["C"].ColumnWidth = 18.5; //address
PivotSheet.Columns["D"].ColumnWidth = 9; //Unit No
}
int FirstCol = PivotSheet.PivotTables(1).ColumnRange.Column;
int TotalCols = PivotSheet.PivotTables(1).ColumnRange.Columns.Count;
PivotSheet.Columns[FirstCol].ColumnWidth = 9; //count
PivotSheet.Columns[++FirstCol].ColumnWidth = 17; //Price
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5; //Days on Market
PivotSheet.Columns[++FirstCol].ColumnWidth = 9; //Floor Area
PivotSheet.Columns[++FirstCol].ColumnWidth = 9; //Price per SF
PivotSheet.Columns[++FirstCol].ColumnWidth = 12; //Building Age
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
PivotSheet.Columns[++FirstCol].ColumnWidth = 11; // Monthly Fee
PivotSheet.Columns[++FirstCol].ColumnWidth = 13; // BC Assessment
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5; //chg% to BCA
PivotSheet.Columns[++FirstCol].ColumnWidth = 11; //Lot $PSF
PivotSheet.Columns[++FirstCol].ColumnWidth = 11; //Improve $PSF
}
else
{
PivotSheet.Columns[++FirstCol].ColumnWidth = 9.5; //land size
PivotSheet.Columns[++FirstCol].ColumnWidth = 17; //Land Assess
PivotSheet.Columns[++FirstCol].ColumnWidth = 17; //BC Assess
PivotSheet.Columns[++FirstCol].ColumnWidth = 9; //Chg% to BCA
PivotSheet.Columns[++FirstCol].ColumnWidth = 11; //Lot $PSF
PivotSheet.Columns[++FirstCol].ColumnWidth = 11; //Improve $PSF
}
FormatColumnsWidthDone = 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;
}
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;
}
public static void CreatePivot(string tableSource, int[] pageFields, int[] rowFields, int[] dataFields, string pivotTableName = "Pivot Table", string[] slicerColumns = null)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
worksheet = workbook.Worksheets.Add(After: workbook.ActiveSheet);
worksheet.Name = "Pivot";
ListObject table = GetTable(tableSource);
Range rng = table.Range;
worksheet.PivotTableWizard(
XlPivotTableSourceType.xlDatabase,
rng,
worksheet.Range["A1"],
pivotTableName
);
PivotTable pivot = (PivotTable)worksheet.PivotTables(pivotTableName);
//pivot.HasAutoFormat = true;
pivot.ColumnGrand = true;
pivot.RowGrand = true;
for (int i = 0; i < pageFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(pageFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlPageField;
field1.Position = i + 1;
field1.CurrentPage = "(All)";
}
for (int i = 0; i < rowFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(rowFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlRowField;
field1.Position = i + 1;
}
PivotField columnField = pivot.PivotFields();
columnField.Orientation = XlPivotFieldOrientation.xlColumnField;
columnField.Position = 1;
for (int i = 0; i < dataFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(dataFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlDataField;
field1.Position = 1 + i;
field1.Function = XlConsolidationFunction.xlSum;
}
//Add Slicers
SlicerCaches caches = workbook.SlicerCaches;
int counter = 1;
if (slicerColumns != null)
{
foreach (string s in slicerColumns)
{
SlicerCache cache = caches.Add(pivot, s, s);
Slicers slicers = cache.Slicers;
Slicer slicer = slicers.Add(worksheet, Type.Missing, s, s, 160 * counter, 10, 144, 200);
counter++;
}
}
}
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();
}
private void HideComplexSubTotal(Excel.Worksheet Sheet, string TableName)
{
Excel.Range Cell = null;
foreach (Excel.Range row in Sheet.PivotTables(TableName).RowRange.Rows)
{
if (Sheet.Range["A" + row.Row].Value?.IndexOf("Total") > 0 && Sheet.Range["A" + row.Row].Value?.IndexOf("Grand Total") < 0)
{
Cell = Sheet.Range["A" + row.Row];
Cell.Select();
Cell.Value = "SubTotal";
Cell.RowHeight *= 1.3;
Cell.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
}
if (Sheet.Range["B" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
if (Sheet.Range["C" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
}
}
public static double GetMin(Excel.Worksheet WS, string TableName, long col)
{
Excel.Range c = null;
Excel.PivotTable PT = WS.PivotTables(TableName);
long i = 0;
long FirstRow = 0;
long LastRow = 0;
//FIND THE LAST NON-BLANK CELL IN COLUMNA
FirstRow = PT.TableRange1.Row + 2;
LastRow = FirstRow + PT.TableRange1.Rows.Count - 4;
double Min = 0;
i = FirstRow;
Min = (double)WS.Cells[i, col].Value;
for (i = FirstRow; i <= LastRow - 2; i++)
{
c = WS.Cells[i, col];
c.Select();
if (c.Value2 != null && i <= LastRow - 2 && !((bool)c.Rows.Hidden) && (double)c.Value < Min)
{
if (WS.Cells[i, 1].Value == null || WS.Cells[i, 1].Value != "SubTotal")
{
Min = (double)c.Value;
}
}
}
return(Min);
}
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();
}
public static bool ExportToExcel(RevisionData selected,
RevOrderMgr om)
{
List <List <string> > data =
AggregateData(selected, om, REV_SORT_ITEM_DESC);
int row = TITLE_ROW;
string outFile = SetUpOutputFile(Setg.TemplatePathAndFileName,
Setg.ExcelPathAndFileName);
X.Application excel = new X.Application();
if (excel == null)
{
return(false);
}
X.Workbook wb = excel.Workbooks.Open(outFile);
X.Worksheet wsData =
wb.Sheets[Setg.ExcelDataWorksheetName] as X.Worksheet;
if (wsData == null)
{
return(false);
}
excel.Visible = false;
ExportColumnTitles(wsData, row, om);
row++;
// startRow row count
X.Range range = GetRange(wsData, row, data.Count,
// startCol colCount
1, om.ColumnOrder.Count);
FormatDataCells(range);
ExportToExcel(data, row, wsData, om);
AdjustColumnWidthInRange(range.Columns, 1.5);
X.Worksheet wsPivot =
wb.Sheets[Setg.ExcelPivotWorksheetName] as X.Worksheet;
X.PivotTable pivotTable =
(X.PivotTable)wsPivot.PivotTables(Setg.ExcelPivotTableName);
pivotTable.RefreshTable();
excel.Visible = true;
return(true);
}
public void AddMedianSummary(Excel.Worksheet TableSheet, string TableName, ListingStatus Status)
{
int lastRow = 0;
int firstRow = 0;
int avgRow = 0;
int medianRow = 0;
int lastCol = 0;
double avgRowHeight = 38;
double medianRowHeight = avgRowHeight;
Excel.PivotTable pvt = null;
TableSheet.Select();
pvt = TableSheet.PivotTables(TableName);
firstRow = pvt.TableRange1.Row + 2;
lastRow = pvt.TableRange1.Row + pvt.TableRange1.Rows.Count - 2;
avgRow = lastRow + 1;
medianRow = avgRow + 1;
int rw = 0;
foreach (Excel.PivotField pvf in pvt.RowFields)
{
rw++;
}
Excel.Range Cell = TableSheet.Range["A" + avgRow];
Cell.Value2 = "Average Values";
Cell = TableSheet.Range["A" + medianRow];
Cell.Value2 = "Median Values";
TableSheet.Cells[medianRow, rw + 1].Value = Library.GetCount(ListingSheet, "B", Status, "", "");
TableSheet.Cells[medianRow, rw + 2].Value = Library.GetMedianValue(ListingSheet, "G", Status, "", "");
TableSheet.Cells[medianRow, rw + 3].Value = Library.GetMedianValue(ListingSheet, "I", Status, "", "");
TableSheet.Cells[medianRow, rw + 4].Value = Library.GetMedianValue(ListingSheet, "L", Status, "", "");
TableSheet.Cells[medianRow, rw + 5].Value = Library.GetMedianValue(ListingSheet, "M", Status, "", "");
TableSheet.Cells[medianRow, rw + 6].Value = Library.GetMedianValue(ListingSheet, "O", Status, "", "");
TableSheet.Cells[medianRow, rw + 7].Value = Library.GetMedianValue(ListingSheet, "P", Status, "", "");
TableSheet.Cells[medianRow, rw + 8].Value = Library.GetMedianValue(ListingSheet, "R", Status, "", "");
TableSheet.Cells[medianRow, rw + 9].Value = Library.GetMedianValue(ListingSheet, "S", Status, "", "");
TableSheet.Select();
lastCol = pvt.TableRange1.Columns.Count;
Excel.Range rng = TableSheet.Range[TableSheet.Cells[medianRow, 1], TableSheet.Cells[medianRow, lastCol]];
rng.RowHeight = medianRowHeight;
Excel.Range rngSource = TableSheet.Range[TableSheet.Cells[avgRow, 1], TableSheet.Cells[avgRow, lastCol]];
rngSource.Select();
rngSource.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rngSource.Copy();
rng.Select();
rng.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);
rng.Font.Bold = true;
rngSource.Select();
Globals.ThisAddIn.Application.CutCopyMode = 0;
}
/// <summary>
/// 更新数据透视表数据.
/// </summary>
/// <param name="dataSheerName">数据Sheet名</param>
/// <param name="pivotSheetName">报表Sheet名</param>
/// <param name="PivotName">数据透视表名</param>
public void AddPivotTable(string dataSheerName, string pivotSheetName, string PivotName = "数据透视表1")
{
// 首先定位到 数据的 Sheet. 设定 数据透视表的 的数据源.
xlSheet = null;
foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
{
if (dataSheerName == displayWorksheet.Name)
{
xlSheet = displayWorksheet;
break;
}
}
if (xlSheet != null)
{
// 取得数据的Sheet的行数与列数
int rowCount = xlSheet.UsedRange.Rows.Count;
int colCount = xlSheet.UsedRange.Columns.Count;
// 拼写好 数据源的名字，准备后面用于更新 数据透视表的数据源.
string sourceData = dataSheerName + "!R1C1:R" + rowCount + "C" + colCount;
// 然后定位到 数据透视表的 Sheet. 刷新数据.
xlSheet = null;
foreach (Excel.Worksheet displayWorksheet in xlBook.Sheets)
{
if (pivotSheetName == displayWorksheet.Name)
{
xlSheet = displayWorksheet;
break;
}
}
if (xlSheet != null)
{
// 修改 Excel 文件中 数据透视表的 数据源
((Excel.PivotTable)xlSheet.PivotTables(PivotName)).SourceData = sourceData;
// 刷新数据 : 重新计算 数据透视表数据
((Excel.PivotTable)xlSheet.PivotTables(PivotName)).Update();
}
}
}
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();
}
public string RefreshPivotTableByName(string FilePath, string SheetName, string PivotTableName)
{
string result = "";
try
{
result = OpenExcel(FilePath, SheetName);
if (result == "")
{
Ws.PivotTables(PivotTableName).RefreshTable();
Ws.PivotTables(PivotTableName).PivotCache().Refresh();
result = "";
}
}
catch (Exception e)
{
return("Exception caught - " + e.Message);
}
return(result);
}
private void FormatColumnWidth()
{
if (PivotSheet.PivotTables(1).RowRange.Columns.Count == 3)
{
PivotSheet.Columns["A"].ColumnWidth = 19.5;
PivotSheet.Columns["B"].ColumnWidth = 17;
PivotSheet.Columns["C"].ColumnWidth = 18.5;
}
else
{
PivotSheet.Columns["A"].ColumnWidth = 19.5;
PivotSheet.Columns["B"].ColumnWidth = 17;
PivotSheet.Columns["C"].ColumnWidth = 18.5;
PivotSheet.Columns["D"].ColumnWidth = 9;
}
int FirstCol = PivotSheet.PivotTables(1).ColumnRange.Column;
int TotalCols = PivotSheet.PivotTables(1).ColumnRange.Columns.Count;
PivotSheet.Columns[FirstCol].ColumnWidth = 6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 10.6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 8.8;
if (this.ReportType.ToString().IndexOf("Detached") < 0)
{
PivotSheet.Columns[++FirstCol].ColumnWidth = 9.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 10.6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5;
}
else
{
PivotSheet.Columns[++FirstCol].ColumnWidth = 9.5;
PivotSheet.Columns[++FirstCol].ColumnWidth = 10.6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 10.6;
PivotSheet.Columns[++FirstCol].ColumnWidth = 7.5;
}
}
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();
}
/// <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)
{
}
}
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 Format(Excel.Worksheet PivotSheet, string TableName, ListingStatus Status, string City)
{
Excel.PivotTable pvt = PivotSheet.PivotTables(TableName);
int FirstRow = 0;
int LastRow = 0;
int LastCol = 0;
int TitleRow = 0;
FirstRow = pvt.TableRange1.Row + 1;
LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2;
LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column;
TitleRow = pvt.TableRange2.Row - 1;
//Todo: Format Title
//Hide Values Row
Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)];
rng0.EntireRow.Hidden = true;
Excel.Range c1 = PivotSheet.Cells[FirstRow, 1];
Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol];
Excel.Range rng = PivotSheet.Range[c1, c2];
rng.Select();
//rng.Style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//rng.Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//rng.Style.Font.Name = "Roboto";
//rng.Style.Font.Size = 11;
//rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
rng.RowHeight = 38;
rng.WrapText = true;
rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
rng = PivotSheet.Range["A" + LastRow];
rng.Select();
rng.RowHeight = 36;
// Create the table style
//ListingBook.TableStyles.Add("Attached Report Style");
Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"];
//ptStyle.ShowAsAvailablePivotTableStyle = true;
// Set Table Style
pvt.TableStyle2 = ptStyle;
FormatColumnWidth();
HideComplexSubTotal(PivotSheet, TableName);
AddSubGroupBottomBorder(TableName);
FormatMaxCells();
FormatMinCells();
AddSectionTitle(PivotSheet, TableName, City + " " + Status + " Records:");
}
private void HideComplexSubTotal(Excel.Worksheet Sheet, string TableName)
{
Excel.Range Cell = null;
Excel.Range CountCell = null;
int subTotalCount = 0;
foreach (Excel.Range row in Sheet.PivotTables(TableName).RowRange.Rows)
{
if (Sheet.Range["A" + row.Row].Value?.IndexOf("Total") > 0 && Sheet.Range["A" + row.Row].Value?.IndexOf("Grand Total") < 0)
{
Cell = Sheet.Range["A" + row.Row];
Cell.Select();
Cell.Value = "SubTotal";
Cell.RowHeight = 19.5;
Cell.EntireRow.Font.Size = 11;
Cell.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
CountCell = Sheet.Range["D" + row.Row];
//subTotalCount++;
if (CountCell.Value == 1)
{
row.Select();
row.EntireRow.Hidden = true;
subTotalCount = 0;
}
}
subTotalCount++;
//if (subTotalCount == 1)
//{
// if (Sheet.Range["A" + row.Row].Value?.IndexOf("Total") > 0 && Sheet.Range["A" + row.Row].Value?.IndexOf("Grand Total") < 0)
// {
// row.Select();
// row.EntireRow.Hidden = true;
// subTotalCount = 0;
// }
//}
if (Sheet.Range["B" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
if (Sheet.Range["C" + row.Row].Value?.IndexOf("Total") > 0)
{
row.Select();
row.EntireRow.Hidden = true;
}
}
}
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();
}
public void AddSectionTitle(Excel.Worksheet WS, string PTName, string Title)
{
Excel.PivotTable PT = WS.PivotTables(PTName);
int FirstRow = PT.TableRange2.Row;
//ADD SECTION TITLE
Excel.Range cell = WS.Range["A" + (FirstRow + 1)];
cell.Value = Title;
cell.Font.Size = 24;
cell.Font.Color = System.Drawing.Color.Red.ToArgb();
cell.Font.Bold = true;
cell.Font.Italic = true;
cell.EntireRow.RowHeight = 32;
//HIDE PAGE GROUP FILTER
cell = WS.Range["A" + FirstRow];
cell.EntireRow.Hidden = 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();
}
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 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;
}
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
}
}
public Excel.Range IdentifyPivotRangesByName(string pivotTableName)
{
Excel.Worksheet activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
Excel.PivotTable pivotTable = activeSheet.PivotTables(pivotTableName);
return(pivotTable.TableRange2);
}
//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);
}
//성공 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);
}
}
