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