/// <summary>C:\Users\User\source\repos\ExcelWorkVariances\ExcelDataHandler\packages.config /// Method creates Pivot table /// </summary> /// <param name="worksheet"></param> public static void CreateMyPivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet) { Excel.Worksheet secondWorksheet = workbook.Sheets.Add(); try { secondWorksheet.Name = "pivot_table"; app.ActiveWindow.DisplayGridlines = false; Excel.Range oRange = worksheet.UsedRange; Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet Excel.PivotCaches pch = workbook.PivotCaches(); pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence", Type.Missing, Type.Missing); // Create Pivot table Excel.PivotTable pvt = secondWorksheet.PivotTables("Confidence"); pvt.ShowDrillIndicators = true; pvt.InGridDropZones = false; Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("ID")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("PLACE")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("NAME")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("PRICE")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("UNITS")); fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField; secondWorksheet.UsedRange.Columns.AutoFit(); pvt.ColumnGrand = true; pvt.RowGrand = true; app.DisplayAlerts = false; secondWorksheet.Activate(); secondWorksheet.get_Range("B1", "B1").Select(); worksheet.Activate(); } catch (Exception) { } }
public static void CreatePivotTable(Excel.Application app, Excel.Workbook workbook, Excel.Worksheet worksheet) { Excel.Worksheet secondWorksheet = (Worksheet)workbook.Sheets.Add(); try { Excel.Range last = worksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = worksheet.get_Range("G1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; Excel.Range oResizeRange = worksheet.Range[worksheet.Cells[1, 7], worksheet.Cells[lastUsedRow, lastUsedColumn]]; secondWorksheet.Name = "pivot_table"; app.ActiveWindow.DisplayGridlines = false; //Excel.Range oRange = worksheet.UsedRange; Excel.PivotCache oPivotCache = workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange); // Set the Source data range from First sheet Excel.PivotCaches pch = workbook.PivotCaches(); pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oResizeRange).CreatePivotTable(secondWorksheet.Cells[1, 1], "Confidence and Latency", Type.Missing, Type.Missing); // Create Pivot table Excel.PivotTable pvt = (Excel.PivotTable)secondWorksheet.PivotTables("Labels"); pvt.ShowDrillIndicators = true; pvt.InGridDropZones = false; Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("Confidence")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("Latency")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); secondWorksheet.UsedRange.Columns.AutoFit(); pvt.ColumnGrand = true; pvt.RowGrand = true; app.DisplayAlerts = false; secondWorksheet.Activate(); secondWorksheet.get_Range("B1", "B1").Select(); } catch (Exception) { // ignored } //worksheet.Select(); worksheet.Activate(); }
private static void UnclosedProtocolsAddPivotTableDepartments(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"WorkTimePivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная по отделениям"]; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Подразделение").Position = 2; pivotTable.PivotFields("ФИО врача").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ФИО врача").Position = 3; pivotTable.PivotFields("ФИО врача").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("ФИО врача").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("DCODE").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("DCODE").Position = 4; pivotTable.PivotFields("DCODE").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("DCODE").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.AddDataField(pivotTable.PivotFields("Уникальное лечение"), "Кол-во лечений", Excel.XlConsolidationFunction.xlSum); pivotTable.CalculatedFields().Add("Всего протоколов", "='Протокол подписан' +'Протокол не подписан'", true); pivotTable.PivotFields("Всего протоколов").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю Всего протоколов").Caption = "Общее кол-во протоколов"; pivotTable.AddDataField(pivotTable.PivotFields("Протокол не подписан"), "Кол-во неподписанных протоколов", Excel.XlConsolidationFunction.xlSum); pivotTable.CalculatedFields().Add("Процент неподписанных", "='Протокол не подписан' /'Всего протоколов'", true); pivotTable.PivotFields("Процент неподписанных").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю Процент неподписанных").Caption = "Доля неподписанных протоколов"; pivotTable.PivotFields("Доля неподписанных протоколов").NumberFormat = "0,00%"; pivotTable.PivotFields("Филиал").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов"); pivotTable.PivotFields("Подразделение").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов"); pivotTable.PivotFields("ФИО врача").AutoSort(Excel.XlSortOrder.xlDescending, "Доля неподписанных протоколов"); pivotTable.PivotFields("Статус сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("Статус сотрудника").Position = 1; pivotTable.PivotFields("Подразделение").ShowDetail = false; pivotTable.PivotFields("Филиал").ShowDetail = false; pivotTable.HasAutoFormat = false; wsPivote.Columns[1].ColumnWidth = 60; wsPivote.Columns[2].ColumnWidth = 12; wb.ShowPivotTableFieldList = false; }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"FrontOfficeClientsPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C4", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("ФИО сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ФИО сотрудника").Position = 1; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = 2; pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Кол-во клиентов"), "Кол-во клиентов ", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("ФИО сотрудника").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("ФИО сотрудника").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Дата").NumberFormat = "[$-ru-RU]Д МММ;@"; wb.ShowPivotTableFieldList = false; pivotTable.DisplayFieldCaptions = false; wsPivote.Range["A1"].Select(); }
static void Main(string[] args) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx"); Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1 Excel.Worksheet xlWsNew = (Excel.Worksheet)xlWb.Sheets.Add(); // find the data range Excel.Range dataRange = getDataRange(ref xlWs); // start by creating the PivotCache - this tells Excel that there is a data connection // to data inside the workbook (could be used to get external data, too) Excel.PivotCache pc = xlWb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase , dataRange , Excel.XlPivotTableVersionList.xlPivotTableVersion14); // create the pivot table and set the destination to the new sheet at A1 Excel.PivotTable pt = pc.CreatePivotTable(xlWsNew.Range["A1"]); // get the PivotField "Same" for easy referencing Excel.PivotField pf = (Excel.PivotField)pt.PivotFields("Same"); // first add the count pt.AddDataField(pf, "Count of Same", Excel.XlConsolidationFunction.xlCount); // now add the row with the same field pf.Orientation = Excel.XlPivotFieldOrientation.xlRowField; pf.Position = 1; // behold!!! xlWsNew.Select(); xlApp.Visible = true; }
private static void TelemedicineAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"TelemedicinePivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int columnLast = ws.UsedRange.Columns.Count; int rowLast = ws.UsedRange.Rows.Count; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowLast + "C" + columnLast, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("FILIAL_SHORTNAME").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("FILIAL_SHORTNAME").Position = 1; pivotTable.PivotFields("SERVICE_TYPE").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("SERVICE_TYPE").Position = 2; pivotTable.PivotFields("CLIENT_CATEGORY").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("CLIENT_CATEGORY").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("CLIENT_HITSNUM"), "Кол-во", Excel.XlConsolidationFunction.xlCount); pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; pivotTable.ShowDrillIndicators = false; }
void frmdrill_FormClosed(object sender, FormClosedEventArgs e) { if (frmdrill.DialogResult == System.Windows.Forms.DialogResult.OK) { System.Data.DataTable dt = frmdrill._dataTable; Microsoft.Office.Interop.Excel.DataTable dtEx; Excel.Workbook _wbook = (Excel.Workbook)Application.ActiveWorkbook; _wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet _wsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet; for (int i = 0; i < dt.Columns.Count; i++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[1, i + 1]; _range.Font.Bold = true; _range.set_Value(Type.Missing, dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Excel.Range _range = (Excel.Range)_wsheet.Cells[i + 2, j + 1]; _range.set_Value(Type.Missing, dt.Rows[i][j]); } } string add = _wsheet.Name + "!R1C1:R" + (dt.Rows.Count + 1) + "C" + dt.Columns.Count; _wbook.Sheets.Add(Type.Missing, Type.Missing, 1, Type.Missing); Excel.Worksheet _wpivotsheet = (Excel.Worksheet)Application.ActiveWorkbook.ActiveSheet; string des = _wpivotsheet.Name + "!R3C1"; _wbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, add).CreatePivotTable(des, "PivotTable1", Type.Missing, Microsoft.Office.Interop.Excel.XlPivotTableVersionList.xlPivotTableVersion10); } }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"HalvaPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"]; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.HasAutoFormat = false; pivotTable.PivotFields("Совпадение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Совпадение").Position = 1; pivotTable.PivotFields("Адрес Торговой точки").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Адрес Торговой точки").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Дата обработки транзакции Банком"), "Количество совпадений", Excel.XlConsolidationFunction.xlCount); wsPivote.Activate(); wsPivote.Columns["A:H"].Select(); xlApp.Selection.ColumnWidth = 15; xlApp.Selection.WrapText = true; xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop; wsPivote.Range["A1"].Select(); pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"MisTimeSheetPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Полное имя доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Полное имя доктора").Position = 2; pivotTable.PivotFields("Идентификатор доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Идентификатор доктора").Position = 3; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = 4; pivotTable.PivotFields("Должность (справочник)").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность (справочник)").Position = 5; pivotTable.PivotFields("Дата графика работ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата графика работ").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Кол-во часов (план)"), "Сумма кол-во часов (план)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма кол-во часов (план)").NumberFormat = "# ##0,00"; pivotTable.PivotFields("Дата графика работ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Должность (справочник)").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Идентификатор доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Полное имя доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Дата графика работ").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Должность (справочник)").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Идентификатор доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Полное имя доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Группа филиалов").ShowDetail = false; //pivotTable.PivotFields("Название").ShowDetail = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"RecordsFromInsuranceCompaniesPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Название страховой").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Название страховой").Position = 1; pivotTable.PivotFields("Имя оператора").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Имя оператора").Position = 2; pivotTable.PivotFields("Пациент").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Пациент").Position = 3; pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = 4; pivotTable.PivotFields("Доктор").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Доктор").Position = 5; pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата назначения").Position = 1; pivotTable.PivotFields("Название страховой").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Имя оператора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Пациент").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Отделение").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Доктор").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Дата назначения").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Название страховой").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Имя оператора").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Пациент").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Отделение").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Доктор").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Дата назначения").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.AddDataField( pivotTable.PivotFields("SCHEDID"), "Кол-во", Excel.XlConsolidationFunction.xlCount); wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"PivotTableScheduleRecords"; Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C16", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("ФИО пользователя").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ФИО пользователя").Position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 2; pivotTable.AddDataField(pivotTable.PivotFields("Дата и время записи"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во записей").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("По направлению?"), "По направлению", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("По направлению").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Прием состоялся?"), "Прием состоялся", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Прием состоялся").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Сумма, всего"), "Сумма оказанных услуг (руб)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма оказанных услуг (руб)").NumberFormat = "# ##0,00 ?"; //pivotTable.PivotFields("Запись, Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Запись, Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Дата").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Дата").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Запись, Должность").ShowDetail = false; //pivotTable.PivotFields("Филиал").ShowDetail = false; pivotTable.PivotFields("ФИО пользователя").ShowDetail = false; wb.ShowPivotTableFieldList = false; //pivotTable.DisplayFieldCaptions = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"PivotTableEmployeesCovidTreat"; Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C14", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Диагноз МКБ").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Диагноз МКБ").Position = 2; pivotTable.PivotFields("Диагноз").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Диагноз").Position = 3; pivotTable.PivotFields("ФИО пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ФИО пациента").Position = 4; pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = 5; pivotTable.PivotFields("ФИО пациента").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("ФИО пациента").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Диагноз").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Диагноз").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Диагноз МКБ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Диагноз МКБ").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Запись, Должность").ShowDetail = false; //pivotTable.PivotFields("Филиал").ShowDetail = false; //pivotTable.PivotFields("ФИО пользователя").ShowDetail = false; wb.ShowPivotTableFieldList = false; pivotTable.DisplayFieldCaptions = false; wsPivote.Columns["B:B"].ColumnWidth = 60; wsPivote.Range["A1"].Select(); }
public void RefreshDataSourcePivotTables(KeyValuePair <string, DataTable> query) { foreach (MSExcel.Worksheet sheet in _view.Sheets) { foreach (MSExcel.PivotTable pivotTable in sheet.PivotTables()) { _app.EnableEvents = false; try { string sourceSheet = pivotTable.Name.Substring(0, pivotTable.Name.IndexOf("__")); if (sourceSheet.ToLower().Equals(query.Key.ToLower())) { MSExcel.Range startRange; if (query.Key.Equals("main")) { startRange = _app.Range["StartCell"]; } else { startRange = _app.Worksheets[query.Key].Range["A2"]; } var rows = query.Value.Rows.Count; var columns = query.Value.Columns.Count; MSExcel.Range dataSource = startRange.Offset[-1, 0].Resize[rows + 1, columns]; // Offsest and resize +1 because of header row. MSExcel.PivotCache cache = _view.PivotCaches().Create(SourceType: MSExcel.XlPivotTableSourceType.xlDatabase, SourceData: dataSource); pivotTable.ChangePivotCache(cache); pivotTable.RefreshTable(); } } catch (ArgumentOutOfRangeException) { // Catches ArgumentOutOfRangeException that is raised when IndexOf() does not find "__" in pivottable name and returns -1. // Substring will raise a ArgumentOutOfRangeException because length parameter is -1 and is therefore less than 0. // Continue execution, because this simply means the pivot table is not tied to the data source we are looking for. _app.EnableEvents = true; continue; } catch (Exception ex) { _app.EnableEvents = true; MessageBox.Show(ex.Message, "Pivot Table Refresh Exception", MessageBoxButtons.OK); } _app.EnableEvents = true; } } }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"FrontOfficeClientsPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C12", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Дата назначения").Position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 2; pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = 3; pivotTable.PivotFields("Новый пациент?").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Новый пациент?").Position = 1; wsPivote.Range["B1"].Value2 = "Новый пациент?"; wsPivote.Range["A2"].Value2 = "Филиал"; pivotTable.AddDataField(pivotTable.PivotFields("№ ИБ"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Филиал").ShowDetail = false; wb.ShowPivotTableFieldList = false; //pivotTable.DisplayFieldCaptions = false; wsPivote.Range["A1"].Select(); }
public string ChangeSourceDataPivotTable(string FilePath, string PivotSheetName, string SourceSheetName, string SourceDataRange, string PivotTableName) { string result = ""; try { string srcData = SourceSheetName + "!" + Ws.Range[SourceDataRange].Address[XlReferenceStyle.xlR1C1]; result = OpenExcel(FilePath, PivotSheetName); if (result == "") { Excel.PivotTable pivot = Ws.PivotTables(PivotTableName); pivot.ChangePivotCache(Wb.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, srcData)); result = ""; } } catch (Exception e) { return("Exception caught - " + e.Message); } return(result); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"PromoPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Название").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Название").Position = 1; pivotTable.PivotFields("Группа филиалов").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Группа филиалов").Position = 2; pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Услуга").Position = 3; //pivotTable.PivotFields("Название").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Группа филиалов").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Услуга").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Название").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Группа филиалов").LayoutForm = Excel.XlLayoutFormType.xlTabular; //pivotTable.PivotFields("Услуга").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Группа филиалов").ShowDetail = false; pivotTable.PivotFields("Название").ShowDetail = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTableScheduleResult(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"SchedResult"; Excel.Worksheet wsPivote = wb.Sheets["Кол-во записей, итог по сумме"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); //pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField; //pivotTable.PivotFields("Дата").Position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Запись, Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Запись, Должность").Position = 2; pivotTable.PivotFields("Запись, Автор").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Запись, Автор").Position = 3; pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("Услуга").Position = 1; pivotTable.PivotFields("Услуга").CurrentPage = "All"; pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = false; pivotTable.PivotFields("Услуга").EnableMultiplePageItems = true; pivotTable.AddDataField(pivotTable.PivotFields("Кол-во"), "Планируемое кол-во услуг", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Планируемое кол-во услуг").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Планируемая стоимость, всего"), "Планируемая стоимость (всего)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Планируемая стоимость (всего)").NumberFormat = "# ##0,00 ?"; pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего2"), "Сумма оказанных услуг (по направлениям)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма оказанных услуг (по направлениям)").NumberFormat = "# ##0,00 ?"; pivotTable.PivotFields("Запись, Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Запись, Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Дата").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Дата").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Запись, Должность").ShowDetail = false; pivotTable.PivotFields("Филиал").ShowDetail = false; //pivotTable.PivotFields("Дата").ShowDetail = false; wb.ShowPivotTableFieldList = false; //pivotTable.DisplayFieldCaptions = false; wsPivote.Range["A1"].Select(); }
private static void outputReport() { int row = 0, col = 0; string formatString; Excel.Application excelApp = new Excel.Application(); if (excelApp == null) { MessageBox.Show("WANRNING!\n<< TRIED TO CREATE REPORT BUT EXCEL IS NOT INSTALLED! >>"); } else { Excel.Workbook workbook = excelApp.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Reports.xlsx"); Excel.Worksheet DataWorksheet = workbook.Worksheets[workbook.Worksheets.Count]; try { DataWorksheet.Name = "Data"; DataTable data = DatabaseTools.getReportData(); foreach (DataColumn dataColumn in data.Columns) { DataWorksheet.Range["A1"].Offset[0, col].Value = dataColumn.ColumnName; switch (dataColumn.DataType.ToString()) { case "System.Decimal": formatString = "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* \" - \"??_-;_-@_-"; break; case "System.Int32": formatString = "0"; break; case "System.DateTime": formatString = "dd/mm/yyyy"; break; default: formatString = "@"; break; } DataWorksheet.Range["A1"].Offset[0, col].EntireColumn.NumberFormat = formatString; col++; } row = 2; foreach (DataRow dataRow in data.Rows) { for (col = 0; col < data.Columns.Count; col++) { DataWorksheet.Cells[row, col + 1] = dataRow[col] == DBNull.Value ? string.Empty : dataRow[col].ToString(); } row++; } DataWorksheet.Columns.AutoFit(); Excel.Worksheet PivotSheet = workbook.Worksheets.Add(); PivotSheet.Name = DateTime.Now.ToLongDateString(); Excel.Range dataRange = DataWorksheet.UsedRange; Excel.PivotCache cache = workbook.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange); Excel.PivotCaches pivotCaches = workbook.PivotCaches(); pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, dataRange).CreatePivotTable(PivotSheet.Cells[1, 1], "Table"); Excel.PivotTable pivotTable = PivotSheet.PivotTables("Table"); Excel.PivotField field = pivotTable.PivotFields("Job Name"); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; field.LayoutCompactRow = false; field.Subtotals = new bool[12] { false, false, false, false, false, false, false, false, false, false, false, false }; field = pivotTable.PivotFields("Order Number"); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; field.Subtotals = new bool[12] { false, false, false, false, false, false, false, false, false, false, false, false }; field = pivotTable.PivotFields("Total Value"); field.Orientation = Excel.XlPivotFieldOrientation.xlDataField; field.Caption = "Total Values"; field = pivotTable.PivotFields("Average Value"); field.Orientation = Excel.XlPivotFieldOrientation.xlDataField; field.Caption = "Average Values"; PivotSheet.UsedRange.Columns.AutoFit(); pivotTable.MergeLabels = true; pivotTable.ShowValuesRow = false; pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; } catch (Exception e) { MessageBox.Show(string.Format(e.Message + "\n" + e.InnerException + "\n" + e.Data)); } finally { workbook.Save(); workbook.Close(); excelApp.Quit(); } } }
public void CreatePivot(string sheetName, int colCount, int rowCount, string pivotTableLocation, string pivotTableName, string rowField, string rowHeader, string columnField, string columnHeader) { #region Initialization Excel.Workbook activeWorkBook = null; Excel.Worksheet pivotWorkSheet = null; Excel.PivotCaches pivotCaches = null; Excel.PivotCache pivotCache = null; Excel.PivotTable pivotTable = null; Excel.PivotFields pivotFields = null; Excel.PivotField rowPivotField = null; Excel.PivotField columnPivotField = null; Excel.PivotField countPivotField = null; Excel.PivotItems pivotItems = null; Excel.Range pivotRange = null; Excel.PivotField hiddenPivotField = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; string rangeName = "'" + sheetName + "'!$A$4:$" + ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); pivotTable = pivotCache.CreatePivotTable(pivotTableLocation, pivotTableName); pivotTable.NullString = "0"; pivotTable.HasAutoFormat = false; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Row Pivot Field rowPivotField = (Excel.PivotField)pivotFields.Item(rowField); rowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; rowPivotField.Position = 1; pivotTable.CompactLayoutRowHeader = rowHeader; // Column Pivot Field columnPivotField = (Excel.PivotField)pivotFields.Item(columnField); // Causes error and not needed // columnPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Count Field countPivotField = pivotTable.AddDataField(columnPivotField, columnHeader, Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields(rowField).AutoSort(Excel.XlSortOrder.xlDescending, columnHeader); // Show only Top 10 results for the pivot table int top10 = 10; // pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, // DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } else { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } string cellValue = ""; pivotRange = pivotTable.RowRange; int itemCount = pivotRange.Count - 2; // Minus 2 because of title and total rows if (itemCount > top10) { pivotItems = pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotItems; for (int i = itemCount - 1; i >= top10; i--) { cellValue = (string)(pivotWorkSheet.Cells[pivotRange.Row + i + 1, pivotRange.Column] as Excel.Range).Value.ToString(); pivotItems.Item(cellValue).Visible = false; } } if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUser = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroup = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoint = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } string reportTable = ""; string reportLabel = "\""; string reportValue = ""; reportTable = "<table id=\"newspaper-a\" class=\"sortable\">"; string head1 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column] as Excel.Range).Value.ToString(); string head2 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<thead><tr><th scope=\"col\">" + head1 + "</th><th style=\"text-align:right;\" scope=\"col\" nowrap>" + head2 + "</th></tr></thead><tbody>"; string col1 = ""; string col2 = ""; string labelShorted = ""; int tableItems = itemCount > 10 ? 10 : itemCount; for (int i = 1; i <= tableItems; i++) { col1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column] as Excel.Range).Value.ToString(); col2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<tr><td>" + col1 + "</td><td style=\"text-align:right;\">" + col2 + "</td></tr>"; if (tableItems > 5 && col1.Length > 10) { labelShorted = col1.Substring(0, 10) + ".."; } else if (col1.Length > 20) { labelShorted = col1.Substring(0, 20) + ".."; } else { labelShorted = col1; } reportLabel = reportLabel + labelShorted + "\",\""; reportValue = reportValue + col2 + ","; } string foot1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column] as Excel.Range).Value.ToString(); string foot2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "</tbody><tfoot><tr><td>" + foot1 + "</td><td style=\"text-align:right;\">" + foot2 + "</td></tr></tfoot></table>"; reportValue = reportValue.TrimEnd(','); reportLabel = reportLabel.TrimEnd('\"'); reportLabel = reportLabel.TrimEnd(','); if (pivotTableName == "PivotTableClassifier") { Globals.DetectionEngine = reportTable; Globals.DetectionEnginesLabel = reportLabel; Globals.DetectionEnginesValue = reportValue; } else if (pivotTableName == "PivotTableFileDisplayName") { Globals.InfectedFiles = reportTable; Globals.InfectedFilesLabel = reportLabel; Globals.InfectedFilesValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroups = reportTable; Globals.MostAtRiskGroupsLabel = reportLabel; Globals.MostAtRiskGroupsValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUsers = reportTable; Globals.MostAtRiskUsersLabel = reportLabel; Globals.MostAtRiskUsersValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoints = reportTable; Globals.MostAtRiskEndpointsLabel = reportLabel; Globals.MostAtRiskEndpointsValue = reportValue; } else if (pivotTableName == "PivotTableIsActive") { Globals.NetworkStatus = reportTable; Globals.NetworkStatusLabel = reportLabel; Globals.NetworkStatusValue = reportValue; } else if (pivotTableName == "PivotTableOs") { Globals.EndpointOS = reportTable; Globals.EndpointOSLabel = reportLabel; Globals.EndpointOSValue = reportValue; } else if (pivotTableName == "PivotTableAgent") { Globals.EndpointVersion = reportTable; Globals.EndpointVersionLabel = reportLabel; Globals.EndpointVersionValue = reportValue; } else if (pivotTableName == "PivotTableApplicationName") { Globals.TopApplications = reportTable; Globals.TopApplicationsLabel = reportLabel; Globals.TopApplicationsValue = reportValue; } Excel.Range colToFormat = pivotWorkSheet.get_Range("K:Q", System.Type.Missing); colToFormat.EntireColumn.AutoFit(); Excel.Range colToEdit = pivotWorkSheet.get_Range("K:K", System.Type.Missing); // Was 28, changed 11/9/2017 if (pivotTableName == "PivotTableApplicationName") { colToEdit.EntireColumn.ColumnWidth = 70; } else if (colToEdit.EntireColumn.ColumnWidth > 35) { colToEdit.EntireColumn.ColumnWidth = 35; } // Customizing the pivot table style pivotWorkSheet.PivotTables(pivotTableName).TableStyle2 = "PivotStyleMedium9"; // Remembers the bottom of the pivot table so that the next one will not overlap Globals.PivotBottom = pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Row + pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count; Excel.Range rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + Globals.PivotBottom.ToString(), "A" + Globals.PivotBottom.ToString()); // Globals.ChartBottom = (int)rng.Top + (int)rng.Height; } catch (Exception ex) { (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 3] = ex.Message; /* * Excel.PivotTable pt = pivotWorkSheet.PivotTables(pivotTableName); * Excel.Range pr = pt.TableRange2; * pr.Clear(); */ } finally { #region Finally if (countPivotField != null) { Marshal.ReleaseComObject(countPivotField); } if (columnPivotField != null) { Marshal.ReleaseComObject(columnPivotField); } if (rowPivotField != null) { Marshal.ReleaseComObject(rowPivotField); } if (pivotFields != null) { Marshal.ReleaseComObject(pivotFields); } if (pivotTable != null) { Marshal.ReleaseComObject(pivotTable); } if (pivotCache != null) { Marshal.ReleaseComObject(pivotCache); } if (pivotCaches != null) { Marshal.ReleaseComObject(pivotCaches); } if (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } if (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } #endregion } }
private static void AddPivotTableReferralResult(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"RefResult"; Excel.Worksheet wsPivote = wb.Sheets["Реализация направлений"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); //pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField; //pivotTable.PivotFields("Дата").Position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Refid"), "Кол-во направлений (всего)", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во направлений (всего)").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0"; pivotTable.CalculatedFields().Add("% Записей_", "='Кол-во записей'/'Кол-во приемов, исходных'", true); pivotTable.PivotFields("% Записей_").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % Записей_").NumberFormat = "0,00%"; pivotTable.PivotFields("Сумма по полю % Записей_").Caption = "% Записей"; pivotTable.AddDataField(pivotTable.PivotFields("Treatcode2"), "Кол-во приемов (по направлениям)", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во приемов (по направлениям)").NumberFormat = "# ##0"; pivotTable.CalculatedFields().Add("% Приемов (по направлениям)_", "='Кол-во приемов, по направлениям'/'Кол-во приемов, исходных'", true); pivotTable.PivotFields("% Приемов (по направлениям)_").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % Приемов (по направлениям)_").NumberFormat = "0,00%"; pivotTable.PivotFields("Сумма по полю % Приемов (по направлениям)_").Caption = "% Приемов (по направлениям)"; pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего2"), "Сумма оказанных услуг (по направлениям)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма оказанных услуг (по направлениям)").NumberFormat = "# ##0,00 ?"; pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, исходный прием"), "Кол-во уникальных пациентов (всего)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во уникальных пациентов (всего)").NumberFormat = "# ##0 ?"; pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, запись"), "Кол-во уникальных пациентов (запись)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во уникальных пациентов (запись)").NumberFormat = "# ##0"; pivotTable.CalculatedFields().Add("% Записанных пациентов_", "='Уникальность пациента, запись'/'Уникальность пациента, исходный прием'", true); pivotTable.PivotFields("% Записанных пациентов_").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % Записанных пациентов_").NumberFormat = "0,00%"; pivotTable.PivotFields("Сумма по полю % Записанных пациентов_").Caption = "% Записанных пациентов"; pivotTable.AddDataField(pivotTable.PivotFields("Уникальность пациента, прием по направлению"), "Кол-во уникальных пациентов (прием по направлениям)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во уникальных пациентов (прием по направлениям)").NumberFormat = "# ##0"; pivotTable.CalculatedFields().Add("% Принятых по направлению пациентов_", "='Уникальность пациента, прием по направлению'/'Уникальность пациента, исходный прием'", true); pivotTable.PivotFields("% Принятых по направлению пациентов_").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % Принятых по направлению пациентов_").NumberFormat = "0,00%"; pivotTable.PivotFields("Сумма по полю % Принятых по направлению пациентов_").Caption = "% Принятых по направлению пациентов"; //ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Записей", "='Кол-во записей'/'Кол-во приемов, исходных'", True //ActiveSheet.PivotTables("RefResult").PivotFields("% Записей").Orientation = xlDataField //ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Приемов (по направлениям)", "='Кол-во приемов, по направлениям'/'Кол-во приемов, исходных'", True //ActiveSheet.PivotTables("RefResult").PivotFields("% Приемов (по направлениям)").Orientation = xlDataField //ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Записанных пациентов", "='Уникальность пациента, запись'/'Уникальность пациента, исходный прием'", True //ActiveSheet.PivotTables("RefResult").PivotFields("% Записанных пациентов").Orientation = xlDataField //ActiveSheet.PivotTables("RefResult").CalculatedFields.Add "% Принятых по направлению пациентов", "='Уникальность пациента, прием по направлению'/'Уникальность пациента, исходный прием'", True //ActiveSheet.PivotTables("RefResult").PivotFields("% Принятых по направлению пациентов").Orientation = xlDataField pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("Услуга").Position = 1; pivotTable.PivotFields("Услуга").CurrentPage = "All"; pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = false; pivotTable.PivotFields("Услуга").EnableMultiplePageItems = true; //pivotTable.AddDataField(pivotTable.PivotFields("№ ИБ"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount); //pivotTable.PivotFields("Дата").ShowDetail = false; wb.ShowPivotTableFieldList = false; //pivotTable.DisplayFieldCaptions = false; pivotTable.HasAutoFormat = false; wsPivote.Columns["B:L"].ColumnWidth = 15; wsPivote.Range["B3:L3"].VerticalAlignment = Excel.Constants.xlTop; wsPivote.Range["B3:L3"].WrapText = true; wsPivote.Range["A1"].Select(); }
private static void AddPivotTableReferralUsage(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { string pivotTableName = @"RefUsage"; Excel.Worksheet wsPivote = wb.Sheets["Использование направлений"]; int rowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C44", 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); //pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField; //pivotTable.PivotFields("Дата").Position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = 1; pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = 2; pivotTable.PivotFields("ФИО Сотрудника").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ФИО Сотрудника").Position = 3; pivotTable.PivotFields("Услуга").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("Услуга").Position = 1; pivotTable.PivotFields("Услуга").CurrentPage = "Итого по услугам"; //pivotTable.PivotFields("Услуга").PivotItems("Итого по услугам").Visible = true; //pivotTable.PivotFields("Услуга").EnableMultiplePageItems = false; pivotTable.AddDataField(pivotTable.PivotFields("ФИО Пациента"), "Кол-во приемов", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Кол-во приемов").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Стоимость, всего"), "Сумма оказанных услуг", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Сумма оказанных услуг").NumberFormat = "# ##0,00 ?"; pivotTable.CalculatedFields().Add("СрЧек", "='Стоимость, всего'/'Кол-во приемов, исходных'", true); pivotTable.PivotFields("СрЧек").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю СрЧек").NumberFormat = "# ##0,00 ?"; pivotTable.PivotFields("Сумма по полю СрЧек").Caption = "СрЧек (по приемам)"; pivotTable.AddDataField(pivotTable.PivotFields("Refid"), "Кол-во созданных направлений", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во приемов").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Schedid"), "Кол-во записей (по направлениям)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во записей (по направлениям)").NumberFormat = "# ##0"; pivotTable.AddDataField(pivotTable.PivotFields("Treatcode2"), "Кол-во приемов (по направлениям)", Excel.XlConsolidationFunction.xlSum); pivotTable.PivotFields("Кол-во приемов (по направлениям)").NumberFormat = "# ##0"; pivotTable.CalculatedFields().Add("% Исполнено направлений_", "=Treatcode2/Refid", true); pivotTable.PivotFields("% Исполнено направлений_").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % Исполнено направлений_").NumberFormat = "0,00%"; pivotTable.PivotFields("Сумма по полю % Исполнено направлений_").Caption = "% Исполнено направлений"; pivotTable.PivotFields("Отделение").ShowDetail = false; pivotTable.PivotFields("Филиал").ShowDetail = false; //pivotTable.PivotFields("Дата").ShowDetail = false; wb.ShowPivotTableFieldList = false; //pivotTable.DisplayFieldCaptions = false; wsPivote.Range["A1"].Select(); }
private static void WorkloadAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[1, 1].Select(); string pivotTableName = @"WorkloadPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"]; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable.PivotFields("ОТДЕЛЕНИЕ").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("ОТДЕЛЕНИЕ").Position = 1; pivotTable.PivotFields("ФИЛИАЛ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("ФИЛИАЛ").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Загрузка"), "Средняя загрузка", Excel.XlConsolidationFunction.xlAverage); pivotTable.PivotFields("Средняя загрузка").NumberFormat = "# ##0,00"; pivotTable.PivotFields("Не учитывать (=1)").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("Не учитывать (=1)").Position = 1; pivotTable.PivotFields("Не учитывать (=1)").ClearAllFilters(); pivotTable.PivotFields("Не учитывать (=1)").CurrentPage = "(blank)"; wsPivote.Activate(); wsPivote.Columns["B:N"].Select(); xlApp.Selection.ColumnWidth = 10; wsPivote.Range["A1"].Select(); pivotTable.HasAutoFormat = false; pivotTable.ShowTableStyleColumnStripes = true; pivotTable.TableStyle2 = "PivotStyleMedium2"; pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; foreach (Excel.PivotItem item in pivotTable.PivotFields("ОТДЕЛЕНИЕ").PivotItems()) { if (!item.Name.Contains("ИТОГО")) { item.Visible = false; } } wsPivote.Columns["B:N"].Select(); xlApp.Selection.FormatConditions.AddColorScale(3); xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority(); xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).Type = Excel.XlConditionValueTypes.xlConditionValueNumber; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 0; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Type = Excel.XlConditionValueTypes.xlConditionValueNumber; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 70; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = 13562593; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).Type = Excel.XlConditionValueTypes.xlConditionValueNumber; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 150; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = 6870690; xlApp.Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor.TintAndShade = 0; wsPivote.Range["C1"].Select(); wb.ShowPivotTableFieldList = false; }
private static void AddPivotTableFreeCells(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp, bool isMonth, DateTime date, DateTime?dateMonthEnd = null, bool removeCrossing = false) { ws.Cells[1, 1].Select(); string sheetName; if (isMonth) { sheetName = "Сводная таблица текущий месяц"; } else { sheetName = "Сводная таблица"; } string pivotTableName = @"PivotTable"; Excel.Worksheet wsPivote = wb.Sheets[sheetName]; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); int position = 1; pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Филиал").Position = position; position++; if (!removeCrossing) { pivotTable.PivotFields("Пересечение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Пересечение").Position = position; position++; } pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Отделение").Position = position; position++; pivotTable.PivotFields("Врач").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Врач").Position = position; position++; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = position; position++; pivotTable.AddDataField(pivotTable.PivotFields("Всего"), "(Всего)", Excel.XlConsolidationFunction.xlSum); pivotTable.AddDataField(pivotTable.PivotFields("Занято"), "(Занято)", Excel.XlConsolidationFunction.xlSum); pivotTable.CalculatedFields().Add("% занятых слотов", "='Занято'/'Всего'", true); pivotTable.PivotFields("% занятых слотов").Orientation = Excel.XlPivotFieldOrientation.xlDataField; pivotTable.PivotFields("Сумма по полю % занятых слотов").Caption = " % занятых слотов"; //pivotTable.AddDataField(pivotTable.PivotFields("% занятых слотов"), "(% занятых слотов)", Excel.XlConsolidationFunction.xlAverage); pivotTable.PivotFields(" % занятых слотов").NumberFormat = "0,00%"; // ActiveSheet.PivotTables("PivotTable").CalculatedFields.Add "Поле2", _ // "=Занято/Всего", True //ActiveSheet.PivotTables("PivotTable").PivotFields("Поле2").Orientation = _ // xlDataField //Columns("F:F").Select // Selection.Style = "Percent" // Range("F3").Select if (isMonth) { CultureInfo cultureInfoOriginal = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); for (DateTime dateToHide = date; dateToHide.Date <= dateMonthEnd.Value.Date; dateToHide = dateToHide.AddDays(1)) { string pivotItem = dateToHide.ToShortDateString(); Console.WriteLine("pivotItem: " + pivotItem); pivotTable.PivotFields("Дата").PivotItems(pivotItem).Visible = false; } Thread.CurrentThread.CurrentCulture = cultureInfoOriginal; } else { pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Дата").Position = 1; pivotTable.PivotFields("Дата").AutoGroup(); pivotTable.PivotFields("Дата").PivotFilters.Add2(Excel.XlPivotFilterType.xlAfter, null, date.AddDays(-1).ToShortDateString(), null, null, null, null, null, true); try { pivotTable.PivotFields("Месяцы").Orientation = Excel.XlPivotFieldOrientation.xlHidden; } catch (Exception) { } } pivotTable.RowGrand = false; pivotTable.ColumnGrand = false; pivotTable.DisplayFieldCaptions = false; pivotTable.PivotFields("(Всего)").NumberFormat = "0,00"; pivotTable.PivotFields("(Занято)").NumberFormat = "0,00"; //pivotTable.PivotFields("(% занятых слотов)").NumberFormat = "0,0%"; pivotTable.PivotSelect("' % занятых слотов'", Excel.XlPTSelectionMode.xlDataAndLabel, true); xlApp.Selection.FormatConditions.AddColorScale(3); xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority(); xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].Type = Excel.XlConditionValueTypes.xlConditionValueLowestValue; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].FormatColor.Color = 5287936; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[1].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].Type = Excel.XlConditionValueTypes.xlConditionValuePercentile; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].Value = 65; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].FormatColor.Color = 8711167; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[2].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].Type = Excel.XlConditionValueTypes.xlConditionValueHighestValue; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].FormatColor.Color = 255; xlApp.Selection.FormatConditions[1].ColorScaleCriteria[3].FormatColor.TintAndShade = 0; xlApp.Selection.FormatConditions[1].ScopeType = Excel.XlPivotConditionScope.xlDataFieldScope; pivotTable.PivotFields("Порядок сортировки").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Порядок сортировки").Position = 1; pivotTable.PivotFields("Порядок сортировки").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Порядок сортировки").LayoutForm = Excel.XlLayoutFormType.xlTabular; pivotTable.PivotFields("Отделение").ShowDetail = false; if (!removeCrossing) { pivotTable.PivotFields("Пересечение").ShowDetail = false; } pivotTable.PivotFields("Филиал").ShowDetail = false; pivotTable.DisplayErrorString = true; wsPivote.Range["A1"].Select(); wb.ShowPivotTableFieldList = false; wsPivote.Range["C1"].Select(); try { xlApp.Selection.Ungroup(); } catch (Exception) {} }
//성공 1 실패 0 만든 기존파일에 내용추가 피벗 경간 static int makePivotPosition(Excel.Workbook wb, Dictionary <string, List <readVO> > lists, String Start, String End) { try { Excel.Worksheet copyws = null; Excel.Worksheet pivotws = null; foreach (KeyValuePair <string, List <readVO> > data in lists) { string sheet_name = null; copyws = wb.Worksheets[data.Key] as Excel.Worksheet; sheet_name = copyws.Name; pivotws = wb.Sheets.Add(After: wb.Sheets[wb.Sheets.Count]); pivotws.Name = sheet_name + "_피벗테이블(경간)"; Excel.Range oRange = copyws.get_Range(Start, End); // specify first cell for pivot table Excel.Range oRange2 = pivotws.get_Range("B2", "B2"); // create Pivot Cache and Pivot Table Excel.PivotCache oPivotCache = (Excel.PivotCache)wb.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // I have error on this line Excel.PivotTable oPivotTable = (Excel.PivotTable)pivotws.PivotTables().Add(oPivotCache, oRange2, "Summary"); // create Pivot Field, note that name will be the same as column name on sheet one Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields(1); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; int count = oPivotTable.PivotFields(1).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[1] = true; oPivotField.Subtotals[1] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(3); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(3).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[3] = true; oPivotField.Subtotals[3] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(2); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(2).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[2] = true; oPivotField.Subtotals[2] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(5); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(5).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[5] = true; oPivotField.Subtotals[5] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(11); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(11).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(9); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlSum; oPivotField.Value = "합계:개소"; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(10); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlSum; oPivotField.Value = "합계:물량"; Excel.PivotField dataField = oPivotTable.DataPivotField; dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.Range rs = pivotws.UsedRange; rs.Columns.AutoFit(); oPivotTable.SubtotalHiddenPageItems = true; } return(1); } catch (Exception ex) { Console.WriteLine(ex); ReleaseExcelProcess(excelApp); MessageBox.Show(ex.ToString(), "오류발생", MessageBoxButtons.OK, MessageBoxIcon.Error); return(0); } }
public void CreateCityPivotTable(Excel.Worksheet PivotSheet, string Location, string TableName, ReportType TableType) { ListingSheet.Select(); string LastRow = ""; string LastCol = ""; string LastCell = ""; long lRow = 0; long lCol = 0; string RankBaseField = ""; //////////// //FIND THE LAST NON-BLANK CELL IN COLUMN A lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row; LastRow = "R" + lRow; lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column; LastCol = "C" + lCol; LastCell = ListingSheet.Cells[lRow, lCol].Address; Excel.Range PivotData = ListingSheet.Range["A1", LastCell]; PivotData.Select(); Excel.PivotCaches pch = ListingBook.PivotCaches(); Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData); Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName); //pvt.MergeLabels = true; // The only thing I noticed this doing was centering the heading labels PivotSheet.Select(); //Excel.PivotField pvf = pvt.PivotFields("Status"); //pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField; //pvf.CurrentPage = Status; //Group 1 S/A switch (TableType) { case ReportType.MonthlyDetachedAllCities: pvt.PivotFields("City").Orientation = Excel.XlPivotFieldOrientation.xlRowField; RankBaseField = "City"; break; case ReportType.MonthlyDetachedAllCommunities: pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("S/A").Caption = "Neighborhood"; RankBaseField = "Neighborhood"; break; default: break; } //pvt.PivotFields("S/A").Name = "Neighborhood"; //Sales Total Amount pvt.AddDataField(pvt.PivotFields("Sold Price"), "Rank", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Rank").BaseField = RankBaseField; //Sort By Rank pvt.PivotFields(RankBaseField).AutoSort(2, "Rank"); //Total Amount pvt.AddDataField(pvt.PivotFields("Sold Price"), "Total Sales Amount", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Total Sales Amount").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("Sold Price"), "Market Share", Excel.XlConsolidationFunction.xlSum); pvt.PivotFields("Market Share").Calculation = Excel.XlPivotFieldCalculation.xlPercentOfTotal; //Sales Count pvt.AddDataField(pvt.PivotFields("Status"), "Sales", Excel.XlConsolidationFunction.xlCount); pvt.PivotFields("Sales").NumberFormat = "0"; //Ave Sold Price pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. Sold Price", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. Sold Price").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("Sold Price"), "Avg. S.Price Rank", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. S.Price Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Avg. S.Price Rank").BaseField = RankBaseField; //Price Per SqFt pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PerSQFT", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. $PerSQFT").NumberFormat = "$#,##0"; pvt.AddDataField(pvt.PivotFields("SP Sqft"), "Avg. $PSF Rank", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. $PSF Rank").Calculation = Excel.XlPivotFieldCalculation.xlRankDecending; pvt.PivotFields("Avg. $PSF Rank").BaseField = RankBaseField; //Days On Market pvt.AddDataField(pvt.PivotFields("CDOM"), "Avg. Days OnMkt", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Avg. Days OnMkt").NumberFormat = "0"; pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow); }
private void btnAceptar_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; libro = app.Workbooks.Add(); libro.DefaultPivotTableStyle = "PivotStyleLight26"; Excel.PivotCache pivotCache = libro.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal); //string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcel"].ConnectionString; string MyConString = ConfigurationManager.ConnectionStrings["ODBCExcelLocal"].ConnectionString; //string MyConString = ConfigurationManager.ConnectionStrings["ODBCMinusculas"].ConnectionString; string strFecha = dateTimePicker1.Value.ToString("yyyy-MM-dd"); string command = "SELECT * FROM ventash WHERE Fecha >='" + strFecha + "'"; pivotCache.Connection = MyConString; pivotCache.CommandText = command; #region ValorAgregado Excel.Worksheet sheetAgregado = libro.Sheets.Add(); libro.Sheets[libro.ActiveSheet.Name].Select(); libro.Sheets[libro.ActiveSheet.Name].Name = "Valor agregado"; Excel.PivotTables pivotTablesAgregado = sheetAgregado.PivotTables(); Excel.PivotTable pivotTableAgregado = pivotTablesAgregado.Add(pivotCache, app.Range["A4"], "Valor agregado"); sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetAgregado.PivotTables("Valor agregado").PivotFields("NombreLocal").Position = 1; sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetAgregado.PivotTables("Valor agregado").PivotFields("FormaPago").Position = 2; sheetAgregado.PivotTables("Valor agregado").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField; app.Range["B6"].Select(); sheetAgregado.PivotTables("Valor agregado").ColumnGrand = false; sheetAgregado.PivotTables("Valor agregado").RowGrand = false; sheetAgregado.PivotTables("Valor agregado").TableStyle2 = "PivotStyleLight26"; sheetAgregado.PivotTables("Valor agregado").CalculatedFields.Add("ValorAgregado", "=IF(TotalPublico>0,(TotalPublico/TotalCosto)-1)", true); sheetAgregado.PivotTables("Valor agregado").PivotFields("ValorAgregado").Orientation = Excel.XlPivotFieldOrientation.xlDataField; sheetAgregado.PivotTables("Valor agregado").PivotFields("Suma de ValorAgregado").Caption = "Valor agregado"; sheetAgregado.PivotTables("Valor agregado").PivotFields("Valor agregado").NumberFormat = "0,00%"; sheetAgregado.Cells[6, 1].Select(); object[] periodosValor = { false, false, false, false, true, false, true }; Excel.Range rangeValor = sheetAgregado.get_Range("a6"); rangeValor.Group(true, true, 1, periodosValor); libro.ShowPivotTableFieldList = false; app.Range["A5"].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select(); Excel.Range rangoGrafico = app.Selection; app.ActiveSheet.Shapes.AddChart.Select(); app.ActiveSheet.Shapes(1).Name = "Valor agregado"; app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn; app.ActiveChart.SetSourceData(Source: rangoGrafico); app.ActiveChart.ClearToMatchStyle(); app.ActiveChart.ChartStyle = 42; app.ActiveChart.ClearToMatchStyle(); app.ActiveSheet.Shapes["Valor agregado"].Left = 0; app.ActiveSheet.Shapes["Valor agregado"].Top = 300; app.ActiveSheet.Shapes["Valor agregado"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Valor agregado"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationX = -30; app.ActiveSheet.Shapes["Valor agregado"].ThreeD.RotationY = 100; app.ActiveSheet.Shapes["Valor agregado"].ThreeD.FieldOfView = 10; app.ActiveChart.ChartTitle.Text = "Valor agregado"; app.Range["A1"].Select(); #endregion #region Prendas Excel.Worksheet sheetPrendas = libro.Sheets.Add(); string hojaPrendas = libro.ActiveSheet.Name; libro.Sheets[hojaPrendas].Select(); libro.Sheets[hojaPrendas].Name = "Prendas"; Excel.PivotTables pivotTablesPrendas = sheetPrendas.PivotTables(); Excel.PivotTable pivotTablePrendas = pivotTablesPrendas.Add(pivotCache, app.Range["A4"], "Prendas"); sheetPrendas.PivotTables("Prendas").TableStyle2 = "PivotStyleLight26"; sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetPrendas.PivotTables("Prendas").PivotFields("NombreLocal").Position = 1; sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetPrendas.PivotTables("Prendas").PivotFields("FormaPago").Position = 2; sheetPrendas.PivotTables("Prendas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField fldTotalPrendas = pivotTablePrendas.PivotFields("Prendas"); fldTotalPrendas.Orientation = Excel.XlPivotFieldOrientation.xlDataField; fldTotalPrendas.Function = Excel.XlConsolidationFunction.xlSum; fldTotalPrendas.Name = " Prendas"; sheetPrendas.Cells[6, 1].Select(); object[] periodosPrendas = { false, false, false, false, true, false, true }; Excel.Range rangePrendas = sheetPrendas.get_Range("a6"); rangePrendas.Group(true, true, 1, periodosPrendas); libro.ShowPivotTableFieldList = false; app.Range["A5"].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select(); Excel.Range rangoGraficoPrendas = app.Selection; app.ActiveSheet.Shapes.AddChart.Select(); app.ActiveSheet.Shapes(1).Name = "Prendas"; app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn; app.ActiveChart.SetSourceData(Source: rangoGraficoPrendas); app.ActiveChart.ClearToMatchStyle(); app.ActiveChart.ChartStyle = 42; app.ActiveChart.ClearToMatchStyle(); app.ActiveSheet.Shapes["Prendas"].Left = 0; app.ActiveSheet.Shapes["Prendas"].Top = 300; app.ActiveSheet.Shapes["Prendas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Prendas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationX = -30; app.ActiveSheet.Shapes["Prendas"].ThreeD.RotationY = 100; app.ActiveSheet.Shapes["Prendas"].ThreeD.FieldOfView = 10; app.ActiveSheet.PivotTables("Prendas").RowGrand = false; app.ActiveChart.ChartTitle.Text = "Unidades vendidas"; app.Range["A1"].Select(); #endregion #region Periodos Excel.Worksheet sheetDiferenciaPeriodos = libro.Sheets.Add(); libro.Sheets[libro.ActiveSheet.Name].Select(); libro.Sheets[libro.ActiveSheet.Name].Name = "Diferencia períodos"; Excel.PivotTables pivotTablesPeriodos = sheetDiferenciaPeriodos.PivotTables(); Excel.PivotTable pivotTableDif = pivotTablesPeriodos.Add(pivotCache, app.Range["A4"], "Diferencia periodos"); sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("NombreLocal").Position = 1; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("FormaPago").Position = 2; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField fldTotalPeriodo = pivotTableDif.PivotFields("TotalPublico"); fldTotalPeriodo.Orientation = Excel.XlPivotFieldOrientation.xlDataField; fldTotalPeriodo.Function = Excel.XlConsolidationFunction.xlSum; fldTotalPeriodo.Name = " Ventas períodos"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "$ #.##0"; libro.ShowPivotTableFieldList = false; app.Range["B6"].Select(); sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").ColumnGrand = false; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").RowGrand = false; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").TableStyle2 = "PivotStyleLight26"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%"; app.Range["A5"].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select(); Excel.Range rangoGraficoPeriodos = app.Selection; app.ActiveSheet.Shapes.AddChart.Select(); app.ActiveSheet.Shapes(1).Name = "Diferencia periodos"; app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn; app.ActiveChart.SetSourceData(Source: rangoGraficoPeriodos); app.ActiveChart.ClearToMatchStyle(); app.ActiveChart.ChartStyle = 42; app.ActiveChart.ClearToMatchStyle(); app.ActiveSheet.Shapes["Diferencia periodos"].Left = 0; app.ActiveSheet.Shapes["Diferencia periodos"].Top = 300; app.ActiveSheet.Shapes["Diferencia periodos"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Diferencia periodos"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationX = -30; app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.RotationY = 100; app.ActiveSheet.Shapes["Diferencia periodos"].ThreeD.FieldOfView = 10; app.Range["A1"].Select(); #endregion #region Ventas2 Excel.Worksheet sheetVentas2 = libro.Sheets.Add(); string hojaVentas2 = libro.ActiveSheet.Name; libro.Sheets[hojaVentas2].Select(); libro.Sheets[hojaVentas2].Name = "Ventas2"; Excel.PivotTables pivotTablesVentas2 = sheetVentas2.PivotTables(); Excel.PivotTable pivotTableVentas2 = pivotTablesVentas2.Add(pivotCache, app.Range["A4"], "Ventas2"); sheetVentas2.PivotTables("Ventas2").TableStyle2 = "PivotStyleLight26"; sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetVentas2.PivotTables("Ventas2").PivotFields("FormaPago").Position = 1; sheetVentas2.PivotTables("Ventas2").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField; sheetVentas2.PivotTables("Ventas2").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.PivotField fldTotalVentas2 = pivotTableVentas2.PivotFields("TotalPublico"); fldTotalVentas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField; fldTotalVentas2.Function = Excel.XlConsolidationFunction.xlSum; fldTotalVentas2.Name = " Ventas"; Excel.PivotField fldTotalPrendas2 = pivotTableVentas2.PivotFields("Prendas"); fldTotalPrendas2.Orientation = Excel.XlPivotFieldOrientation.xlDataField; fldTotalPrendas2.Function = Excel.XlConsolidationFunction.xlSum; fldTotalPrendas2.Name = " Prendas"; sheetVentas2.Cells[6, 1].Select(); object[] periodosVentas2 = { false, false, false, false, true, false, true }; Excel.Range rangeVentas2 = sheetVentas2.get_Range("a6"); rangeVentas2.Group(true, true, 1, periodosVentas2); sheetVentas2.PivotTables("Ventas2").PivotFields(" Ventas").NumberFormat = "$ #.##0"; libro.ShowPivotTableFieldList = false; app.ActiveSheet.PivotTables("Ventas2").RowGrand = false; app.Range["A1"].Select(); #endregion #region Ventas Excel.Worksheet sheetVentas = libro.Sheets.Add(); string hojaVentas = libro.ActiveSheet.Name; libro.Sheets[hojaVentas].Select(); libro.Sheets[hojaVentas].Name = "Ventas"; Excel.PivotTables pivotTablesVentas = sheetVentas.PivotTables(); Excel.PivotTable pivotTableVentas = pivotTablesVentas.Add(pivotCache, app.Range["A4"], "Ventas"); sheetVentas.PivotTables("Ventas").TableStyle2 = "PivotStyleLight26"; sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetVentas.PivotTables("Ventas").PivotFields("NombreLocal").Position = 1; sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetVentas.PivotTables("Ventas").PivotFields("FormaPago").Position = 2; sheetVentas.PivotTables("Ventas").PivotFields("Fecha").Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField fldTotalVentas = pivotTableVentas.PivotFields("TotalPublico"); fldTotalVentas.Orientation = Excel.XlPivotFieldOrientation.xlDataField; fldTotalVentas.Function = Excel.XlConsolidationFunction.xlSum; fldTotalVentas.Name = " Ventas"; sheetVentas.Cells[6, 1].Select(); object[] periodosVentas = { false, false, false, false, true, false, true }; Excel.Range rangeVentas = sheetVentas.get_Range("a6"); rangeVentas.Group(true, true, 1, periodosVentas); sheetVentas.PivotTables("Ventas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; sheetVentas.PivotTables("Ventas").PivotFields("Años").Position = 1; sheetVentas.PivotTables("Ventas").PivotFields(" Ventas").NumberFormat = "$ #.##0"; libro.ShowPivotTableFieldList = false; app.Range["A5"].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown)].Select(); app.Range[app.Selection, app.Selection.End(Microsoft.Office.Interop.Excel.XlDirection.xlToRight)].Select(); Excel.Range rangoGraficoVentas = app.Selection; app.ActiveSheet.Shapes.AddChart.Select(); app.ActiveSheet.Shapes(1).Name = "Ventas"; app.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn; app.ActiveChart.SetSourceData(Source: rangoGraficoVentas); app.ActiveChart.ClearToMatchStyle(); app.ActiveChart.ChartStyle = 42; app.ActiveChart.ClearToMatchStyle(); app.ActiveSheet.Shapes["Ventas"].Left = 0; app.ActiveSheet.Shapes["Ventas"].Top = 300; app.ActiveSheet.Shapes["Ventas"].ScaleWidth(1.663541776, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Ventas"].ScaleHeight(1.2777777778, Microsoft.Office.Core.MsoTriState.msoFalse , Microsoft.Office.Core.MsoScaleFrom.msoScaleFromTopLeft); app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationX = -30; app.ActiveSheet.Shapes["Ventas"].ThreeD.RotationY = 100; app.ActiveSheet.Shapes["Ventas"].ThreeD.FieldOfView = 10; app.ActiveSheet.PivotTables("Ventas").RowGrand = false; app.Range["A1"].Select(); #endregion sheetPrendas.PivotTables("Prendas").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; sheetPrendas.PivotTables("Prendas").PivotFields("Años").Position = 1; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields("Años").Position = 1; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").Calculation = Excel.XlPivotFieldCalculation.xlPercentDifferenceFrom; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseField = "Años"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").BaseItem = "(anterior)"; sheetDiferenciaPeriodos.PivotTables("Diferencia periodos").PivotFields(" Ventas períodos").NumberFormat = "0,00%"; sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; sheetAgregado.PivotTables("Valor agregado").PivotFields("Años").Position = 1; sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Orientation = Excel.XlPivotFieldOrientation.xlPageField; sheetVentas2.PivotTables("Ventas2").PivotFields("Años").Position = 2; app.ActiveWorkbook.Connections["Conexión"].ODBCConnection.Connection = "ODBC;DATABASE"; libro.Sheets["Hoja1"].Select(); app.ActiveWindow.SelectedSheets.Delete(); libro.Sheets["Ventas"].Select(); libro.Saved = true; app.Visible = true; Cursor.Current = Cursors.Arrow; }
public void Create() { Excel.Worksheet PivotSheet = this.PivotSheet; string Location = this.PivotTableLocation; string TableName = this.PivotTableName; char Status = this.Status; ListingSheet.Select(); string LastRow = ""; string LastCol = ""; string LastCell = ""; long lRow = 0; long lCol = 0; //////////// //FIND THE LAST NON-BLANK CELL IN COLUMN A lRow = ListingSheet.Cells[ListingSheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row; LastRow = "R" + lRow; lCol = ListingSheet.Cells[1, ListingSheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column; LastCol = "C" + lCol; LastCell = ListingSheet.Cells[lRow, lCol].Address; Excel.Range PivotData = ListingSheet.Range["A1", LastCell]; PivotData.Select(); Excel.PivotCaches pch = ListingBook.PivotCaches(); Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, PivotData); Excel.PivotTable pvt = pc.CreatePivotTable(PivotSheet.Range[Location], TableName); PivotSheet.Select(); Excel.PivotField pvf = pvt.PivotFields("Status"); pvf.Orientation = Excel.XlPivotFieldOrientation.xlPageField; switch ((ListingStatus)Status) { case ListingStatus.Active: case ListingStatus.Sold: pvf.CurrentPage = Status.ToString(); break; case ListingStatus.OffMarket: try { pvf.PivotItems(((char)ListingStatus.Active).ToString()).Visible = false; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Sold).ToString()).Visible = false; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Terminate).ToString()).Visible = true; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Cancel).ToString()).Visible = true; } catch (Exception e) { }; try { pvf.PivotItems(((char)ListingStatus.Expire).ToString()).Visible = true; } catch (Exception e) { }; pvf.EnableMultiplePageItems = true; break; } //Group 1 S/A pvt.PivotFields("S/A").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("S/A").Name = "Neighborhood"; //Group 2 Complex pvt.PivotFields("Complex/Subdivision Name").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Complex/Subdivision Name").Name = this.ReportType.ToString().IndexOf("Detached") < 0 ? "Complex" : "SubDivision"; //Group 3 Address pvt.PivotFields("Address2").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Address2").Name = "Civic Address"; //Group 4 UnitNo if (this.bShowUnitNo || this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.PivotFields("Unit#").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pvt.PivotFields("Unit#").Name = "Unit No"; } pvt.AddDataField(pvt.PivotFields("MLS"), "Count", Excel.XlConsolidationFunction.xlCount); pvt.AddDataField(pvt.PivotFields("Price0"), "Price", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("CDOM"), "Days On Mkt", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("TotFlArea"), "Floor Area", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("PrcSqft"), "$PSF", Excel.XlConsolidationFunction.xlAverage); //TEST Add Calculated Fields //Excel.PivotField ptField; //Excel.CalculatedFields cfField = pvt.CalculatedFields(); //ptField = cfField.Add("New PSF", "='PrcSqft' * 'Age'", true); //pvt.AddDataField(ptField, " New PSF", Excel.XlConsolidationFunction.xlAverage); // pvt.AddDataField(pvt.PivotFields("Age"), "Building Age", Excel.XlConsolidationFunction.xlAverage); if (this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.AddDataField(pvt.PivotFields("StratMtFee"), "Monthly Fee", Excel.XlConsolidationFunction.xlAverage); } else { pvt.AddDataField(pvt.PivotFields("Lot Sz (Sq.Ft.)"), "Land Size", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("LandValue"), "Land Assess.", Excel.XlConsolidationFunction.xlAverage); } pvt.AddDataField(pvt.PivotFields("BCAValue"), "BC Assess.", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Change%"), "Chg% to BCA", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Lot$ PerSF"), "Lot$PSF", Excel.XlConsolidationFunction.xlAverage); pvt.AddDataField(pvt.PivotFields("Improve$ PerSF"), "Improve$PSF", Excel.XlConsolidationFunction.xlAverage); pvt.PivotFields("Price").NumberFormat = "$#,##0"; pvt.PivotFields("Days On Mkt").NumberFormat = "0"; pvt.PivotFields("Floor Area").NumberFormat = "0"; pvt.PivotFields("$PSF").NumberFormat = "$#,##0"; pvt.PivotFields("Building Age").NumberFormat = "0"; if (this.ReportType.ToString().IndexOf("Detached") < 0) { pvt.PivotFields("Monthly Fee").NumberFormat = "$#,##0"; } else { pvt.PivotFields("Land Size").NumberFormat = "0"; pvt.PivotFields("Land Assess.").NumberFormat = "$#,##0"; } pvt.PivotFields("BC Assess.").NumberFormat = "$#,##0"; pvt.PivotFields("Chg% to BCA").NumberFormat = "0%"; pvt.PivotFields("Lot$PSF").NumberFormat = "$#,##0"; pvt.PivotFields("Improve$PSF").NumberFormat = "$#,##0"; pvt.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow); }
//this method will create pivot table in excel file public string OfficeDll() { string filepath = System.Web.HttpContext.Current.Server.MapPath("~/Content/ProductReport.xlsx"); int rows = 0; Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(filepath); Excel.Worksheet excelworksheet = excelWorkBook.ActiveSheet; Excel.Worksheet sheet2 = excelWorkBook.Sheets.Add(); try { sheet2.Name = "Pivot Table"; excelApp.ActiveWindow.DisplayGridlines = false; Excel.Range oRange = excelworksheet.UsedRange; Excel.PivotCache oPivotCache = excelWorkBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // Set the Source data range from First sheet Excel.PivotCaches pch = excelWorkBook.PivotCaches(); pch.Add(Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(sheet2.Cells[3, 3], "PivTbl_2", Type.Missing, Type.Missing); // Create Pivot table Excel.PivotTable pvt = sheet2.PivotTables("PivTbl_2"); pvt.ShowDrillIndicators = true; pvt.InGridDropZones = false; Excel.PivotField fld = ((Excel.PivotField)pvt.PivotFields("CATEGORY")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("PLACE")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("NAME")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("PRICE")); fld.Orientation = Excel.XlPivotFieldOrientation.xlRowField; fld.set_Subtotals(1, false); fld = ((Excel.PivotField)pvt.PivotFields("NoOfUnits")); fld.Orientation = Excel.XlPivotFieldOrientation.xlDataField; sheet2.UsedRange.Columns.AutoFit(); pvt.ColumnGrand = true; pvt.RowGrand = true; excelApp.DisplayAlerts = false; excelworksheet.Delete(); sheet2.Activate(); sheet2.get_Range("B1", "B1").Select(); excelWorkBook.SaveAs(filepath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excelApp.DisplayAlerts = false; excelWorkBook.Close(0); excelApp.Quit(); Marshal.ReleaseComObject(excelWorkBook); Marshal.ReleaseComObject(excelApp); } catch (Exception ex) { excelWorkBook.Close(0); excelApp.Quit(); Marshal.ReleaseComObject(excelWorkBook); Marshal.ReleaseComObject(excelApp); return(ex.Message); } return(filepath); }
private static void FssInfoAddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Cells[3, 1].Select(); string pivotTableName = @"FssInfoPivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"]; int wsRowsUsed = ws.UsedRange.Rows.Count; wsPivote.Activate(); Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.Range["A2:BG" + wsRowsUsed], 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Год").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Год").Position = 1; //pivotTable.PivotFields("Год").AutoSort(Excel.XlSortOrder.xlDescending, "Год"); pivotTable.PivotFields("Квартал").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Квартал").Position = 2; //pivotTable.PivotFields("Квартал").AutoSort(Excel.XlSortOrder.xlDescending, "Квартал"); pivotTable.PivotFields("Месяц").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Месяц").Position = 3; //pivotTable.PivotFields("Месяц").AutoSort(Excel.XlSortOrder.xlDescending, "Месяц"); pivotTable.PivotFields("Неделя").Orientation = Excel.XlPivotFieldOrientation.xlColumnField; pivotTable.PivotFields("Неделя").Position = 4; //pivotTable.PivotFields("Неделя").AutoSort(Excel.XlSortOrder.xlDescending, "Неделя"); pivotTable.AddDataField(pivotTable.PivotFields("Номер ЛН"), "Кол-во Номер ЛН", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Адрес").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Адрес").Position = 1; pivotTable.HasAutoFormat = false; int wsPivoteColumnUsed = wsPivote.UsedRange.Columns.Count; wsPivote.Columns["B:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed)].Select(); xlApp.Selection.ColumnWidth = 6; wsPivote.Range["B2:" + ExcelGeneral.ColumnIndexToColumnLetter(wsPivoteColumnUsed) + "5"].Select(); xlApp.Selection.HorizontalAlignment = Excel.Constants.xlGeneral; xlApp.Selection.VerticalAlignment = Excel.Constants.xlTop; xlApp.Selection.WrapText = true; pivotTable.PivotFields("Месяц").ShowDetail = false; pivotTable.TableStyle2 = "PivotStyleMedium13"; pivotTable.PivotFields("Неделя").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Месяц").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; pivotTable.PivotFields("Квартал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false }; //pivotTable.PivotFields("Год").PivotItems("2017").Visible = false; //pivotTable.PivotFields("Год").PivotItems("2018").Visible = false; //pivotTable.PivotFields("Адрес").PivotItems("г.Москва, Вадковский переулок, д.18").Visible = false; pivotTable.DisplayFieldCaptions = false; wb.ShowPivotTableFieldList = false; wsPivote.Range["A1"].Select(); }
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp) { ws.Columns["A:A"].Select(); xlApp.Selection.Copy(); ws.Columns["B:B"].Select(); xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftToRight); ws.Cells[1, 2].Value = "ФИО"; ws.Columns[2].ColumnWidth = 1; Excel.Range range = ws.Columns["G:G"]; range.NumberFormat = "m/d/yyyy"; ws.Columns["H:H"].NumberFormat = "[$-x-systime]ч:мм:сс AM/PM"; ws.Columns["I:I"].NumberFormat = "[$-x-systime]ч:мм:сс AM/PM"; ws.Columns["J:J"].NumberFormat = "[ч]:мм:сс;@"; ws.Cells[1, 1].Select(); string pivotTableName = @"WorkTimePivotTable"; Excel.Worksheet wsPivote = wb.Sheets["Pivot"]; Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6); Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6); pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName); pivotTable.PivotFields("Город").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Город").Position = 1; pivotTable.PivotFields("Компания").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Компания").Position = 2; pivotTable.PivotFields("Подразделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Подразделение").Position = 3; pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Должность").Position = 4; pivotTable.PivotFields("Физическое лицо").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Физическое лицо").Position = 5; pivotTable.PivotFields("Дата").Orientation = Excel.XlPivotFieldOrientation.xlRowField; pivotTable.PivotFields("Дата").Position = 6; pivotTable.PivotFields("ФИО").Orientation = Excel.XlPivotFieldOrientation.xlPageField; pivotTable.PivotFields("ФИО").Position = 1; pivotTable.AddDataField(pivotTable.PivotFields("Начало рабочего дня"), "Среднее по полю Начало рабочего дня", Excel.XlConsolidationFunction.xlAverage); pivotTable.AddDataField(pivotTable.PivotFields("Окончание рабочего дня"), "Среднее по полю Окончание рабочего дня", Excel.XlConsolidationFunction.xlAverage); pivotTable.AddDataField(pivotTable.PivotFields("Длительность"), "Сумма по полю Длительность", Excel.XlConsolidationFunction.xlSum); pivotTable.AddDataField(pivotTable.PivotFields("Длительность"), "Среднее по полю Длительность", Excel.XlConsolidationFunction.xlAverage); pivotTable.AddDataField(pivotTable.PivotFields("Начало рабочего дня"), "Количество по полю Начало рабочего дня", Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields("Среднее по полю Начало рабочего дня").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM"; pivotTable.PivotFields("Среднее по полю Окончание рабочего дня").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM"; pivotTable.PivotFields("Сумма по полю Длительность").NumberFormat = "[ч]:мм:сс;@"; pivotTable.PivotFields("Среднее по полю Длительность").NumberFormat = "[$-x-systime]ч:мм:сс AM/PM"; pivotTable.PivotFields("Город").ShowDetail = false; pivotTable.PivotFields("Компания").ShowDetail = false; pivotTable.PivotFields("Подразделение").ShowDetail = false; pivotTable.PivotFields("Должность").ShowDetail = false; pivotTable.PivotFields("Физическое лицо").ShowDetail = false; pivotTable.HasAutoFormat = false; wsPivote.Columns[1].ColumnWidth = 80; wsPivote.Columns[2].ColumnWidth = 15; wsPivote.Columns[3].ColumnWidth = 15; wsPivote.Columns[4].ColumnWidth = 15; wsPivote.Columns[5].ColumnWidth = 15; wsPivote.Columns[6].ColumnWidth = 15; wsPivote.Rows[3].WrapText = true; wsPivote.Activate(); wb.ShowPivotTableFieldList = false; }
static void makeGraphs(string file) { Excel.Application excelApp = null; Excel.Workbook workbook = null; Excel.Sheets sheets = null; Excel.Worksheet dataSheet = null; Excel.Worksheet newSheet = null; Excel.Worksheet chartSheet = null; Excel.Range range = null; Excel.Range dataR = null; int rowC = 0; try { excelApp = new Excel.Application(); string dir = file.Substring(0, file.LastIndexOf("\\") + 1); string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1); workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",", true, false, 0, false, false, false); sheets = workbook.Sheets; dataSheet = sheets[1]; dataSheet.Name = "data"; newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing); newSheet.Name = "table"; chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing); chartSheet.Name = "graph"; Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing); dataR = dataSheet.UsedRange; rowC = dataR.Rows.Count; range = newSheet.get_Range("A1"); PivotCaches pCs = workbook.PivotCaches(); PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing); PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1"); PivotField fA = pT.PivotFields("Time"); PivotField fB = pT.PivotFields("Command"); fA.Orientation = XlPivotFieldOrientation.xlRowField; fA.Position = 1; fB.Orientation = XlPivotFieldOrientation.xlColumnField; fB.Position = 1; pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum); ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450); Chart chartP = pChart.Chart; chartP.SetSourceData(pT.TableRange1, Type.Missing); chartP.ChartType = XlChartType.xlLine; excelApp.DisplayAlerts = false; workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing); workbook.Close(true, Type.Missing, Type.Missing); excelApp.Quit(); } catch { Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?"); using (StreamWriter outfile = File.AppendText("output.txt")) { outfile.WriteLine("Did have issues interacting with Excel on " + file); } } finally { /*Excel.Application excelApp = null; * Excel.Workbook workbook = null; * Excel.Sheets sheets = null; * Excel.Worksheet dataSheet = null; * Excel.Worksheet newSheet = null; * Excel.Worksheet chartSheet = null; * Excel.Range range = null; * Excel.Range dataR = null;*/ releaseObject(dataR); releaseObject(range); releaseObject(chartSheet); releaseObject(newSheet); releaseObject(dataSheet); releaseObject(sheets); releaseObject(workbook); releaseObject(excelApp); } }