private static void TelemedicineAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"TelemedicinePivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int columnLast = ws.UsedRange.Columns.Count; int rowLast = ws.UsedRange.Rows.Count; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowLast + "C" + columnLast, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("FILIAL_SHORTNAME").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("FILIAL_SHORTNAME").Position = 1; pivotTable.PivotFields("SERVICE_TYPE").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("SERVICE_TYPE").Position = 2; pivotTable.PivotFields("CLIENT_CATEGORY").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("CLIENT_CATEGORY").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("CLIENT_HITSNUM"), "Кол-во", Excel.XlConsolidationFunction.xlCount); pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; pivotTable.ShowDrillIndicators = false; }
internal Excel.PivotTable CreateSalesPivotTable(Excel.Range range, String filePath) { string fileDirectory = System.IO.Path.GetDirectoryName(filePath); string fileName = System.IO.Path.GetFileName(filePath); string pivotTableName = Properties.Resources.SalesAndProfitPivotTableName; Excel.PivotCache cache = this.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, missing); cache.Connection = String.Format( CultureInfo.CurrentUICulture, salesPivotTableConnectionTemplate, fileDirectory); cache.CommandType = Excel.XlCmdType.xlCmdSql; cache.CommandText = String.Format( CultureInfo.CurrentUICulture, salesPivotTableQueryTemplate, fileName); Excel.PivotTable pivotTable = cache.CreatePivotTable(range, pivotTableName, missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent); // Adjusts the properties of the new PivotTable to // format information the desired way. pivotTable.ErrorString = " -- "; pivotTable.DisplayErrorString = true; pivotTable.NullString = " -- "; return(pivotTable); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"HalvaPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"]; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.HasAutoFormat = false; pivotTable.PivotFields("Совпадение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Совпадение").Position = 1; pivotTable.PivotFields("Адрес Торговой точки").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Адрес Торговой точки").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Дата обработки транзакции Банком"), "Количество совпадений", Excel.XlConsolidationFunction.xlCount); wsPivote.Activate(); wsPivote.Columns["A:H"].Select(); xlApp.Selection.ColumnWidth = 15; xlApp.Selection.WrapText = true; xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop; wsPivote.Range["A1"].Select(); pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; }
private static void 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 PivotData() { index++; Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches(); Excel.Range pivotData = (Excel.Range)osheet.UsedRange; Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData); Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index); Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW"); pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status"); rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status"); valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches; //Month Slicer string nameDateTime = "Slicer_DATETIME" + index.ToString(); string nameMonth = "Slicer_Month" + index.ToString(); Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline); Excel.Slicers monthSlicers = monthSlicerCache.Slicers; Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing, nameMonth, "Date Range", 160, 10, 250, 150); }
// Parametre olarak aktif çalışma sayfası, aktif uygulama, aktf çalışma kitabını alan ve sonucunda bir pivot tablo çizen metod. public Worksheet createPivotTable(Worksheet currentSheet, Application currentApp, Workbook currentBook, int rowCount) { // Pivot tablo yeni bir excel çalışma sayfası açılarak yazdırılıyor. Excel.Range oRange = currentSheet.Range["C1:D" + rowCount]; if (currentApp.Application.Sheets.Count < 3) { currentSheet = (Excel.Worksheet)currentBook.Worksheets.Add(); } else { currentSheet = currentApp.Worksheets[3]; } currentSheet.Name = "Apriori Pivot Table"; Excel.Range oRange2 = currentSheet.Cells[1, 1]; Excel.PivotCache oPivotCache = (Excel.PivotCache)currentBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); Excel.PivotTable oPivotTable = (Excel.PivotTable)currentSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Apriori Pivot Table"); // Çizilecek pivot tablo referans olarak işlem numarası ve ürünleri alıyor. // işlem numarasına göre hangi ürünler o işlem içerisinde varsa tabloda işaretleme yapıyor. Excel.PivotField oPivotField1 = (Excel.PivotField)oPivotTable.PivotFields("Transaction"); oPivotField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField2.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlCount; return(currentSheet); }
static void Main(string[] args) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx"); Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1 Excel.Worksheet xlWsNew = (Excel.Worksheet)xlWb.Sheets.Add(); // find the data range Excel.Range dataRange = getDataRange(ref xlWs); // start by creating the PivotCache - this tells Excel that there is a data connection // to data inside the workbook (could be used to get external data, too) Excel.PivotCache pc = xlWb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase , dataRange , Excel.XlPivotTableVersionList.xlPivotTableVersion14); // create the pivot table and set the destination to the new sheet at A1 Excel.PivotTable pt = pc.CreatePivotTable(xlWsNew.Range["A1"]); // get the PivotField "Same" for easy referencing Excel.PivotField pf = (Excel.PivotField)pt.PivotFields("Same"); // first add the count pt.AddDataField(pf, "Count of Same", Excel.XlConsolidationFunction.xlCount); // now add the row with the same field pf.Orientation = Excel.XlPivotFieldOrientation.xlRowField; pf.Position = 1; // behold!!! xlWsNew.Select(); xlApp.Visible = true; }
public static bool IsDrillThroughEnabled(Excel.Range rngCell) { try { Excel.PivotTable pt = rngCell.PivotTable; // throws error if selected cell is not pivot cell Excel.PivotCache cache = pt.PivotCache(); Excel.Workbook workbook = rngCell.Worksheet.Parent; if (!cache.OLAP) { return(false); } if (ExcelHelper.CountXmlNamespace(workbook, Constants.DaxDrillXmlSchemaSpace) == 0) { return(false); } // check compatibility of Tabular database var queryClient = new QueryClient(rngCell); var connString = ExcelHelper.GetConnectionString(rngCell); if (!queryClient.IsDatabaseCompatible(connString)) { return(false); } return(true); } catch { return(false); } }
public QueryClient(Excel.Range rngCell, PivotCellDictionary pivotCellDic, IEnumerable <string> pivotFieldNames) { this.rngCell = rngCell; this.pivotTable = rngCell.PivotTable; this.pcache = pivotTable.PivotCache(); this.connectionString = pcache.Connection; this.pivotFieldNames = pivotFieldNames ?? PivotCellHelper.GetPivotFieldNames(rngCell); this.pivotCellDic = pivotCellDic ?? PivotCellHelper.GetPivotCellQuery(rngCell); }
public static Excel.WorkbookConnection GetWorkbookConnection(Excel.Range rngCell) { Excel.PivotTable pt = null; Excel.PivotCache cache = null; Excel.WorkbookConnection wbcnn = null; pt = rngCell.PivotTable; cache = pt.PivotCache(); wbcnn = cache.WorkbookConnection; return(wbcnn); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"MisTimeSheetPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Полное имя доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Полное имя доктора").Position = 2; pivotTable.PivotFields("Идентификатор доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Идентификатор доктора").Position = 3; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = 4; pivotTable.PivotFields("Должность (справочник)").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность (справочник)").Position = 5; pivotTable.PivotFields("Дата графика работ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата графика работ").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Кол-во часов (план)"), "Сумма кол-во часов (план)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма кол-во часов (план)").NumberFormat = "# ##0,00"; pivotTable.PivotFields("Дата графика работ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Должность (справочник)").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Идентификатор доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Полное имя доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Дата графика работ").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Должность (справочник)").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Идентификатор доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Полное имя доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Группа филиалов").ShowDetail = false; //pivotTable.PivotFields("Название").ShowDetail = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"RecordsFromInsuranceCompaniesPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Название страховой").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Название страховой").Position = 1; pivotTable.PivotFields("Имя оператора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Имя оператора").Position = 2; pivotTable.PivotFields("Пациент").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Пациент").Position = 3; pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = 4; pivotTable.PivotFields("Доктор").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Доктор").Position = 5; pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата назначения").Position = 1; pivotTable.PivotFields("Название страховой").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Имя оператора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Пациент").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Отделение").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Доктор").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Дата назначения").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Название страховой").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Имя оператора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Пациент").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Отделение").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Доктор").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Дата назначения").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.AddDataField( pivotTable.PivotFields("SCHEDID"), "Кол-во", Excel.XlConsolidationFunction.xlCount); wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
public static string GetConnectionString(Excel.Range rngCell) { Excel.PivotTable pt = null; Excel.PivotCache cache = null; string connString; pt = rngCell.PivotTable; cache = pt.PivotCache(); connString = cache.Connection; return(connString); }
public void CreatePivots(Excel.Range DataRange) { Excel.Worksheet pSheet = xlWrkBk.Worksheets.Add(); pSheet.Name = "Pivot Tables Errors"; Excel.Range pRange = pSheet.Range["A1", "A1"]; //Excel.PivotCache pivotCache = (Excel.PivotCache)xlWrkBk.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, DataRange); Excel.PivotCache pivotCache = (Excel.PivotCache)Form1.XlWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, DataRange); var pivotTables = (Excel.PivotTables)xlErrorSheet.PivotTables(); var pivotTable = pivotTables.Add(pivotCache, pRange, "PivotTable1"); }
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 RefreshDataSourcePivotTables(KeyValuePair <string, DataTable> query) { foreach (MSExcel.Worksheet sheet in _view.Sheets) { foreach (MSExcel.PivotTable pivotTable in sheet.PivotTables()) { _app.EnableEvents = false; try { string sourceSheet = pivotTable.Name.Substring(0, pivotTable.Name.IndexOf("__")); if (sourceSheet.ToLower().Equals(query.Key.ToLower())) { MSExcel.Range startRange; if (query.Key.Equals("main")) { startRange = _app.Range["StartCell"]; } else { startRange = _app.Worksheets[query.Key].Range["A2"]; } var rows = query.Value.Rows.Count; var columns = query.Value.Columns.Count; MSExcel.Range dataSource = startRange.Offset[-1, 0].Resize[rows + 1, columns]; // Offsest and resize +1 because of header row. MSExcel.PivotCache cache = _view.PivotCaches().Create(SourceType: MSExcel.XlPivotTableSourceType.xlDatabase, SourceData: dataSource); pivotTable.ChangePivotCache(cache); pivotTable.RefreshTable(); } } catch (ArgumentOutOfRangeException) { // Catches ArgumentOutOfRangeException that is raised when IndexOf() does not find "__" in pivottable name and returns -1. // Substring will raise a ArgumentOutOfRangeException because length parameter is -1 and is therefore less than 0. // Continue execution, because this simply means the pivot table is not tied to the data source we are looking for. _app.EnableEvents = true; continue; } catch (Exception ex) { _app.EnableEvents = true; MessageBox.Show(ex.Message, "Pivot Table Refresh Exception", MessageBoxButtons.OK); } _app.EnableEvents = true; } } }
public static void CreatePivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet) { Excel.Worksheet secondWorksheet = (Worksheet)workbook.Sheets.Add(); try { Excel.Range last = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = worksheet.get_Range("G1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; Excel.Range oResizeRange = worksheet.Range[worksheet.Cells[1, 7], worksheet.Cells[lastUsedRow, lastUsedColumn]]; secondWorksheet.Name = "pivot_table"; app.ActiveWindow.DisplayGridlines = false; //Excel.Range oRange = worksheet.UsedRange; Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange); // Set the Source data range from First sheet Excel.PivotCaches pch = workbook.PivotCaches(); pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence and Latency", Type.Missing, Type.Missing); // Create Pivot table Excel.PivotTable pvt = (Excel.PivotTable)secondWorksheet.PivotTables("Labels"); pvt.ShowDrillIndicators = true; pvt.InGridDropZones = false; Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Confidence")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("Latency")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); secondWorksheet.UsedRange.Columns.AutoFit(); pvt.ColumnGrand = true; pvt.RowGrand = true; app.DisplayAlerts = false; secondWorksheet.Activate(); secondWorksheet.get_Range("B1", "B1").Select(); } catch (Exception) { // ignored } //worksheet.Select(); worksheet.Activate(); }
private static void 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(); }
//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); } }
private static void FssInfoAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[3, 1].Select(); string pivotTableName = @"FssInfoPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int wsRowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.Range["A2:BG" + wsRowsUsed], 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Год").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Год").Position = 1; //pivotTable.PivotFields("Год").AutoSort(Excel.XlSortOrder.xlDescending, "Год"); pivotTable.PivotFields("Квартал").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Квартал").Position = 2; //pivotTable.PivotFields("Квартал").AutoSort(Excel.XlSortOrder.xlDescending, "Квартал"); pivotTable.PivotFields("Месяц").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Месяц").Position = 3; //pivotTable.PivotFields("Месяц").AutoSort(Excel.XlSortOrder.xlDescending, "Месяц"); pivotTable.PivotFields("Неделя").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Неделя").Position = 4; //pivotTable.PivotFields("Неделя").AutoSort(Excel.XlSortOrder.xlDescending, "Неделя"); pivotTable.AddDataField(pivotTable.PivotFields("Номер ЛН"), "Кол-во Номер ЛН", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Адрес").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Адрес").Position = 1; pivotTable.HasAutoFormat = false; int wsPivoteColumnUsed = wsPivote.UsedRange.Columns.Count; wsPivote.Columns["B:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed)].Select(); xlApp.Selection.ColumnWidth = 6; wsPivote.Range["B2:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed) + "5"].Select(); xlApp.Selection.HorizontalAlignment = Excel.Constants.xlGeneral; xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop; xlApp.Selection.WrapText = true; pivotTable.PivotFields("Месяц").ShowDetail = false; pivotTable.TableStyle2 = "PivotStyleMedium13"; pivotTable.PivotFields("Неделя").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Месяц").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Квартал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Год").PivotItems("2017").Visible = false; //pivotTable.PivotFields("Год").PivotItems("2018").Visible = false; //pivotTable.PivotFields("Адрес").PivotItems("г.Москва, Вадковский переулок, д.18").Visible = false; pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
public void CreateCityPivotTable(Excel.Worksheet PivotSheet, string Location, string TableName, ReportType TableType) { ListingSheet.Select(); string LastRow = ""; string LastCol = ""; string LastCell = ""; long lRow = 0; long lCol = 0; string RankBaseField = ""; //////////// //FIND THE LAST NON-BLANK CELL IN COLUMN A lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row; LastRow = "R" + lRow; lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column; LastCol = "C" + lCol; LastCell = ListingSheet.Cells[lRow, lCol].Address; Excel.Range PivotData = ListingSheet.Range["A1", LastCell]; PivotData.Select(); Excel.PivotCaches pch = ListingBook.PivotCaches(); Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData); Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName); //pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels PivotSheet.Select(); //Excel.PivotField pvf = pvt.PivotFields("Status"); //pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField; //pvf.CurrentPage = Status; //Group 1 S/A switch (TableType) { case ReportType.MonthlyDetachedAllCities: pvt.PivotFields("City").Orientation = Excel.XlPivotFieldOrientation.xlRowField; RankBaseField = "City"; break; case ReportType.MonthlyDetachedAllCommunities: pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("S/A").Caption = "Neighborhood"; RankBaseField = "Neighborhood"; break; default: break; } //pvt.PivotFields("S/A").Name = "Neighborhood"; //Sales Total Amount pvt.AddDataField(pvt.PivotFields("Sold Price"), "Rank", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Rank").BaseField = RankBaseField; //Sort By Rank pvt.PivotFields(RankBaseField).AutoSort(2, "Rank"); //Total Amount pvt.AddDataField(pvt.PivotFields("Sold Price"), "Total Sales Amount", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Total Sales Amount").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("Sold Price"), "Market Share", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Market Share").Calculation = Excel.XlPivotFieldCalculation.xlPercentOfTotal; //Sales Count pvt.AddDataField(pvt.PivotFields("Status"), "Sales", Excel.XlConsolidationFunction.xlCount); pvt.PivotFields("Sales").NumberFormat = "0"; //Ave Sold Price pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. Sold Price", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. Sold Price").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. S.Price Rank", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. S.Price Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Avg. S.Price Rank").BaseField = RankBaseField; //Price Per SqFt pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PerSQFT", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. $PerSQFT").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PSF Rank", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. $PSF Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Avg. $PSF Rank").BaseField = RankBaseField; //Days On Market pvt.AddDataField(pvt.PivotFields("CDOM"), "Avg. Days OnMkt", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. Days OnMkt").NumberFormat = "0"; pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow); }
public void Create() { Excel.Worksheet PivotSheet = this.PivotSheet; string Location = this.PivotTableLocation; string TableName = this.PivotTableName; char Status = this.Status; ListingSheet.Select(); string LastRow = ""; string LastCol = ""; string LastCell = ""; long lRow = 0; long lCol = 0; //////////// //FIND THE LAST NON-BLANK CELL IN COLUMN A lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row; LastRow = "R" + lRow; lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column; LastCol = "C" + lCol; LastCell = ListingSheet.Cells[lRow, lCol].Address; Excel.Range PivotData = ListingSheet.Range["A1", LastCell]; PivotData.Select(); Excel.PivotCaches pch = ListingBook.PivotCaches(); Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData); Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName); PivotSheet.Select(); Excel.PivotField pvf = pvt.PivotFields("Status"); pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField; switch ((ListingStatus)Status) { case ListingStatus.Active: case ListingStatus.Sold: pvf.CurrentPage = Status.ToString(); break; case ListingStatus.OffMarket: try { pvf.PivotItems(((char)ListingStatus.Active).ToString()).Visible = false; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Sold).ToString()).Visible = false; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Terminate).ToString()).Visible = true; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Cancel).ToString()).Visible = true; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Expire).ToString()).Visible = true; } catch (Exception e) { }; pvf.EnableMultiplePageItems = true; break; } //Group 1 S/A pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("S/A").Name = "Neighborhood"; //Group 2 Complex pvt.PivotFields("Complex/Subdivision Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Complex/Subdivision Name").Name = this.ReportType.ToString().IndexOf("Detached") < 0 ? "Complex" : "SubDivision"; //Group 3 Address pvt.PivotFields("Address2").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Address2").Name = "Civic Address"; //Group 4 UnitNo if (this.bShowUnitNo || this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.PivotFields("Unit#").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Unit#").Name = "Unit No"; } pvt.AddDataField(pvt.PivotFields("MLS"), "Count", Excel.XlConsolidationFunction.xlCount); pvt.AddDataField(pvt.PivotFields("Price0"), "Price", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("CDOM"), "Days On Mkt", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("TotFlArea"), "Floor Area", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("PrcSqft"), "$PSF", Excel.XlConsolidationFunction.xlAverage); //TEST Add Calculated Fields //Excel.PivotField ptField; //Excel.CalculatedFields cfField = pvt.CalculatedFields(); //ptField = cfField.Add("New PSF", "='PrcSqft' * 'Age'", true); //pvt.AddDataField(ptField, " New PSF", Excel.XlConsolidationFunction.xlAverage); // pvt.AddDataField(pvt.PivotFields("Age"), "Building Age", Excel.XlConsolidationFunction.xlAverage); if (this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.AddDataField(pvt.PivotFields("StratMtFee"), "Monthly Fee", Excel.XlConsolidationFunction.xlAverage); } else { pvt.AddDataField(pvt.PivotFields("Lot Sz (Sq.Ft.)"), "Land Size", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("LandValue"), "Land Assess.", Excel.XlConsolidationFunction.xlAverage); } pvt.AddDataField(pvt.PivotFields("BCAValue"), "BC Assess.", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Change%"), "Chg% to BCA", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Lot$ PerSF"), "Lot$PSF", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Improve$ PerSF"), "Improve$PSF", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Price").NumberFormat = "$#,##0"; pvt.PivotFields("Days On Mkt").NumberFormat = "0"; pvt.PivotFields("Floor Area").NumberFormat = "0"; pvt.PivotFields("$PSF").NumberFormat = "$#,##0"; pvt.PivotFields("Building Age").NumberFormat = "0"; if (this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.PivotFields("Monthly Fee").NumberFormat = "$#,##0"; } else { pvt.PivotFields("Land Size").NumberFormat = "0"; pvt.PivotFields("Land Assess.").NumberFormat = "$#,##0"; } pvt.PivotFields("BC Assess.").NumberFormat = "$#,##0"; pvt.PivotFields("Chg% to BCA").NumberFormat = "0%"; pvt.PivotFields("Lot$PSF").NumberFormat = "$#,##0"; pvt.PivotFields("Improve$PSF").NumberFormat = "$#,##0"; pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow); }
public void CreatePivot(string sheetName, int colCount, int rowCount, string pivotTableLocation, string pivotTableName, string rowField, string rowHeader, string columnField, string columnHeader) { #region Initialization Excel.Workbook activeWorkBook = null; Excel.Worksheet pivotWorkSheet = null; Excel.PivotCaches pivotCaches = null; Excel.PivotCache pivotCache = null; Excel.PivotTable pivotTable = null; Excel.PivotFields pivotFields = null; Excel.PivotField rowPivotField = null; Excel.PivotField columnPivotField = null; Excel.PivotField countPivotField = null; Excel.PivotItems pivotItems = null; Excel.Range pivotRange = null; Excel.PivotField hiddenPivotField = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; string rangeName = "'" + sheetName + "'!$A$4:$" + ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); pivotTable = pivotCache.CreatePivotTable(pivotTableLocation, pivotTableName); pivotTable.NullString = "0"; pivotTable.HasAutoFormat = false; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Row Pivot Field rowPivotField = (Excel.PivotField)pivotFields.Item(rowField); rowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; rowPivotField.Position = 1; pivotTable.CompactLayoutRowHeader = rowHeader; // Column Pivot Field columnPivotField = (Excel.PivotField)pivotFields.Item(columnField); // Causes error and not needed // columnPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Count Field countPivotField = pivotTable.AddDataField(columnPivotField, columnHeader, Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields(rowField).AutoSort(Excel.XlSortOrder.xlDescending, columnHeader); // Show only Top 10 results for the pivot table int top10 = 10; // pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, // DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } else { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } string cellValue = ""; pivotRange = pivotTable.RowRange; int itemCount = pivotRange.Count - 2; // Minus 2 because of title and total rows if (itemCount > top10) { pivotItems = pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotItems; for (int i = itemCount - 1; i >= top10; i--) { cellValue = (string)(pivotWorkSheet.Cells[pivotRange.Row + i + 1, pivotRange.Column] as Excel.Range).Value.ToString(); pivotItems.Item(cellValue).Visible = false; } } if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUser = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroup = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoint = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } string reportTable = ""; string reportLabel = "\""; string reportValue = ""; reportTable = "<table id=\"newspaper-a\" class=\"sortable\">"; string head1 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column] as Excel.Range).Value.ToString(); string head2 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<thead><tr><th scope=\"col\">" + head1 + "</th><th style=\"text-align:right;\" scope=\"col\" nowrap>" + head2 + "</th></tr></thead><tbody>"; string col1 = ""; string col2 = ""; string labelShorted = ""; int tableItems = itemCount > 10 ? 10 : itemCount; for (int i = 1; i <= tableItems; i++) { col1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column] as Excel.Range).Value.ToString(); col2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<tr><td>" + col1 + "</td><td style=\"text-align:right;\">" + col2 + "</td></tr>"; if (tableItems > 5 && col1.Length > 10) { labelShorted = col1.Substring(0, 10) + ".."; } else if (col1.Length > 20) { labelShorted = col1.Substring(0, 20) + ".."; } else { labelShorted = col1; } reportLabel = reportLabel + labelShorted + "\",\""; reportValue = reportValue + col2 + ","; } string foot1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column] as Excel.Range).Value.ToString(); string foot2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "</tbody><tfoot><tr><td>" + foot1 + "</td><td style=\"text-align:right;\">" + foot2 + "</td></tr></tfoot></table>"; reportValue = reportValue.TrimEnd(','); reportLabel = reportLabel.TrimEnd('\"'); reportLabel = reportLabel.TrimEnd(','); if (pivotTableName == "PivotTableClassifier") { Globals.DetectionEngine = reportTable; Globals.DetectionEnginesLabel = reportLabel; Globals.DetectionEnginesValue = reportValue; } else if (pivotTableName == "PivotTableFileDisplayName") { Globals.InfectedFiles = reportTable; Globals.InfectedFilesLabel = reportLabel; Globals.InfectedFilesValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroups = reportTable; Globals.MostAtRiskGroupsLabel = reportLabel; Globals.MostAtRiskGroupsValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUsers = reportTable; Globals.MostAtRiskUsersLabel = reportLabel; Globals.MostAtRiskUsersValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoints = reportTable; Globals.MostAtRiskEndpointsLabel = reportLabel; Globals.MostAtRiskEndpointsValue = reportValue; } else if (pivotTableName == "PivotTableIsActive") { Globals.NetworkStatus = reportTable; Globals.NetworkStatusLabel = reportLabel; Globals.NetworkStatusValue = reportValue; } else if (pivotTableName == "PivotTableOs") { Globals.EndpointOS = reportTable; Globals.EndpointOSLabel = reportLabel; Globals.EndpointOSValue = reportValue; } else if (pivotTableName == "PivotTableAgent") { Globals.EndpointVersion = reportTable; Globals.EndpointVersionLabel = reportLabel; Globals.EndpointVersionValue = reportValue; } else if (pivotTableName == "PivotTableApplicationName") { Globals.TopApplications = reportTable; Globals.TopApplicationsLabel = reportLabel; Globals.TopApplicationsValue = reportValue; } Excel.Range colToFormat = pivotWorkSheet.get_Range("K:Q", System.Type.Missing); colToFormat.EntireColumn.AutoFit(); Excel.Range colToEdit = pivotWorkSheet.get_Range("K:K", System.Type.Missing); // Was 28, changed 11/9/2017 if (pivotTableName == "PivotTableApplicationName") { colToEdit.EntireColumn.ColumnWidth = 70; } else if (colToEdit.EntireColumn.ColumnWidth > 35) { colToEdit.EntireColumn.ColumnWidth = 35; } // Customizing the pivot table style pivotWorkSheet.PivotTables(pivotTableName).TableStyle2 = "PivotStyleMedium9"; // Remembers the bottom of the pivot table so that the next one will not overlap Globals.PivotBottom = pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Row + pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count; Excel.Range rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + Globals.PivotBottom.ToString(), "A" + Globals.PivotBottom.ToString()); // Globals.ChartBottom = (int)rng.Top + (int)rng.Height; } catch (Exception ex) { (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 3] = ex.Message; /* * Excel.PivotTable pt = pivotWorkSheet.PivotTables(pivotTableName); * Excel.Range pr = pt.TableRange2; * pr.Clear(); */ } finally { #region Finally if (countPivotField != null) { Marshal.ReleaseComObject(countPivotField); } if (columnPivotField != null) { Marshal.ReleaseComObject(columnPivotField); } if (rowPivotField != null) { Marshal.ReleaseComObject(rowPivotField); } if (pivotFields != null) { Marshal.ReleaseComObject(pivotFields); } if (pivotTable != null) { Marshal.ReleaseComObject(pivotTable); } if (pivotCache != null) { Marshal.ReleaseComObject(pivotCache); } if (pivotCaches != null) { Marshal.ReleaseComObject(pivotCaches); } if (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } if (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } #endregion } }
private static void 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 generarReporte(DataGridView tabla, string nombreHojaReporte, string tituloReporte, string celdaInicioTitulo, string celdaFinTitulo, int indexInicioTitulo, int indexFinTitulo) { ////Para futura referencia, esta es una forma probable de obtener un rango de celdas basado en indices ////Excel.Range range = hoja.Ranges(hoja.Cells[1, 1], hoja.Cells[1, 2]); string columnaOrdenamiento = "Filtro"; try { Cursor.Current = Cursors.WaitCursor; exportar.Enabled = false; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet hojaDatos = wb.ActiveSheet; int IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) // Columnas { IndiceColumna++; hojaDatos.Cells[1, IndiceColumna] = col.Name; } //agregar campo de ordenamiento hojaDatos.Cells[1, IndiceColumna + 1] = columnaOrdenamiento; int IndiceFila = 0; foreach (DataGridViewRow row in tabla.Rows) // Filas { IndiceFila++; IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) { IndiceColumna++; hojaDatos.Cells[IndiceFila + 1, IndiceColumna] = "'" + row.Cells[col.Name].Value; } hojaDatos.Cells[IndiceFila + 1, IndiceColumna + 1] = columnaOrdenamiento; } Excel.Worksheet hojaReporte = excel.Sheets.Add(); hojaReporte.Name = nombreHojaReporte; hojaReporte.Activate(); Excel.Range oRange = hojaDatos.UsedRange; Excel.PivotCache oPivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange, Type.Missing); Excel.Range oRange2 = hojaReporte.Cells[5, 2]; Excel.PivotCaches pch = wb.PivotCaches(); pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(oRange2, "reportePersonas", Type.Missing, Type.Missing); Excel.PivotTable pvt = hojaReporte.PivotTables("reportePersonas") as Excel.PivotTable; //configuracion de la tabla dinamica pvt.RowGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.ColumnGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.EnableFieldList = false; //desactivar la opcion para apagar o encender campos en la tabla dinamica pvt.ShowDrillIndicators = false; //quitar los simbolos de + en cada celda pvt.EnableDrilldown = false; //no permitir minimizar las filas pvt.InGridDropZones = false; //no permitir drag&drop de las columnas pvt.ShowTableStyleRowHeaders = false; //no mostrar columna de por medio en negrita/otro color, segun el estilo de tabla pvt.TableStyle2 = "PivotStyleMedium9"; //settear estilo de tabla foreach (DataGridViewColumn col in tabla.Columns) // Columnas { Excel.PivotField field = (Excel.PivotField)pvt.PivotFields(col.Name); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; field.Subtotals[1] = false; } //agregar el PivotField para el campo de ordenamiento Excel.PivotField f = (Excel.PivotField)pvt.PivotFields(columnaOrdenamiento); f.Orientation = Excel.XlPivotFieldOrientation.xlDataField; f.Name = "No remover, ocultar solamente"; //hacer que las columnas tengan el tamaño adecuado hojaReporte.UsedRange.Columns.AutoFit(); //int startIndex = indexColumnaOrdenamiento.IndexOfAny("0123456789".ToCharArray()); //string indicatedColumnLetter = indexColumnaOrdenamiento.Substring(0, startIndex); string column = obtenerNombreColExcel(tabla.Columns.Count + 2); // se agregan mas dos por la posicion inicial de la tabla y la columna de ordenamiento extra hojaReporte.Range[column + "1"].EntireColumn.Hidden = true; //ocultando la columna de sort //agregar el dato de encabezado hojaReporte.Cells[2, 3] = tituloReporte; Excel.Range titulo = hojaReporte.Range[celdaInicioTitulo, celdaFinTitulo]; titulo.Merge(); titulo.Font.Bold = true; titulo.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; titulo.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black; hojaReporte.Cells[3, indexInicioTitulo] = "Fecha:"; hojaReporte.Cells[3, indexInicioTitulo + 1] = DateTime.Today; hojaReporte.Cells[3, indexFinTitulo - 1] = "Hora:"; hojaReporte.Cells[3, indexFinTitulo] = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString(); //eliminar la hoja de datos excel.DisplayAlerts = false; //bypass del bug que evita que se elimine la hoja hojaDatos.Activate(); hojaDatos.Delete(); hojaReporte.Activate(); excel.DisplayAlerts = true; //retornar la propiedad al valor original MessageBox.Show("Infome generado exitosamente.", "Operación completa", MessageBoxButtons.OK, MessageBoxIcon.Information); exportar.Enabled = true; excel.Visible = true; Cursor.Current = Cursors.Default; } catch (Exception e) { Console.WriteLine(e.StackTrace); Cursor.Current = Cursors.Default; exportar.Enabled = true; MessageBox.Show("Ha ocurrido un error en la creación del documento, póngase en contacto con los desarrolladores del sistema.", "Error - AlbergueHN", MessageBoxButtons.OK, MessageBoxIcon.Error); } }