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 void PivotData() { index++; Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches(); Excel.Range pivotData = (Excel.Range)osheet.UsedRange; Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData); Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index); Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW"); pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status"); rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status"); valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches; //Month Slicer string nameDateTime = "Slicer_DATETIME" + index.ToString(); string nameMonth = "Slicer_Month" + index.ToString(); Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline); Excel.Slicers monthSlicers = monthSlicerCache.Slicers; Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing, nameMonth, "Date Range", 160, 10, 250, 150); }
// Parametre olarak aktif çalışma sayfası, aktif uygulama, aktf çalışma kitabını alan ve sonucunda bir pivot tablo çizen metod. public Worksheet createPivotTable(Worksheet currentSheet, Application currentApp, Workbook currentBook, int rowCount) { // Pivot tablo yeni bir excel çalışma sayfası açılarak yazdırılıyor. Excel.Range oRange = currentSheet.Range["C1:D" + rowCount]; if (currentApp.Application.Sheets.Count < 3) { currentSheet = (Excel.Worksheet)currentBook.Worksheets.Add(); } else { currentSheet = currentApp.Worksheets[3]; } currentSheet.Name = "Apriori Pivot Table"; Excel.Range oRange2 = currentSheet.Cells[1, 1]; Excel.PivotCache oPivotCache = (Excel.PivotCache)currentBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); Excel.PivotTable oPivotTable = (Excel.PivotTable)currentSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Apriori Pivot Table"); // Çizilecek pivot tablo referans olarak işlem numarası ve ürünleri alıyor. // işlem numarasına göre hangi ürünler o işlem içerisinde varsa tabloda işaretleme yapıyor. Excel.PivotField oPivotField1 = (Excel.PivotField)oPivotTable.PivotFields("Transaction"); oPivotField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField2.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlCount; return(currentSheet); }
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(); }
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(); }
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; }
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 static int Excel_PivotTable(Excel.Application excel_application, Excel.Worksheet bank_report_ws, string ws1_column, Excel.Worksheet worksheet_2, string ws2_column) { Excel.Range pivot_data = bank_pivot_ws.UsedRange; Excel.Range pivot_destination = bank_pivot_ws.get_Range("A46", "A46"); bank_pivot_wb.PivotTableWizard( Excel.XlPivotTableSourceType.xlDatabase, pivot_data, pivot_destination, "Исходные данные", true, true, true, true, Type.Missing, Type.Missing, false, false, Excel.XlOrder.xlDownThenOver, 0, Type.Missing, Type.Missing ); // Set variables used to manipulate the Pivot Table. Excel.PivotTable pivot_table = (Excel.PivotTable)bank_pivot_ws.PivotTables("Исходные данные"); Excel.PivotField Y = ((Excel.PivotField)pivot_table.PivotFields("Период")); Excel.PivotField M = ((Excel.PivotField)pivot_table.PivotFields("Подразделение")); Excel.PivotField sum_of_doc = ((Excel.PivotField)pivot_table.PivotFields("Сумма")); Y.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; M.Orientation = Excel.XlPivotFieldOrientation.xlRowField; sum_of_doc.Orientation = Excel.XlPivotFieldOrientation.xlDataField; sum_of_doc.Function = Excel.XlConsolidationFunction.xlSum; return(0); }
private static void AddMultiplePageFieldFilterToDic(Excel.PivotTable pt, PivotCellDictionary pivotCellDic) { var mdxString = pt.MDX; var pivotFields = pt.PivotFields(); var pivotFieldNames = new List <string>(); foreach (Excel.PivotField pf in pivotFields) { pivotFieldNames.Add(pf.Name); } AddMultiplePageFieldFilterToDic(pivotFieldNames, mdxString, pivotCellDic); }
private static List <string> ExtractItemsFromPivotRows() { List <string> items = new List <string>(); Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable; string selectedFields = pt.RowFields.Item(0).Name; foreach (Excel.PivotItem rowItem in pt.PivotFields(selectedFields).PivotItems) { items.Add(rowItem.Name); } return(items); }
/// <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) { } }
static void Main() { string filePath = @"C:\Users\stackoverflow\Desktop\Sample.xlsx"; Excel.Application excel = new Excel.Application(); excel.Visible = true; excel.EnableAnimations = true; Excel.Workbook wkb = Open(excel, filePath); foreach (Excel.Worksheet xlWorksheet in wkb.Worksheets) { Excel.PivotTables pivotTablesCollection = xlWorksheet.PivotTables(); if (pivotTablesCollection.Count > 0) { for (int i = 1; i <= pivotTablesCollection.Count; i++) { Excel.PivotTable currentPivotTable = pivotTablesCollection.Item(i); Console.WriteLine($"Table is named -> {currentPivotTable.Name}"); foreach (Excel.PivotField pivotField in currentPivotTable.PivotFields()) { Console.WriteLine($"\nField is named -> {pivotField.Name}"); foreach (Excel.PivotItem visibleItems in pivotField.VisibleItems) { Console.WriteLine($"Visible item name -> {visibleItems.Name}"); } foreach (Excel.PivotItem PivotItem in pivotField.PivotItems()) { Console.WriteLine($"Item is named -> {PivotItem.Name}"); Console.WriteLine(PivotItem.Visible); } } } } } excel.EnableAnimations = true; wkb.Close(true); excel.Quit(); Console.WriteLine("Finished!"); }
private void PivotGroupingWindowOkButton_Click(object sender, EventArgs e) { if (groups.Count <= 1) // groups are empty { return; } groups.ForEach(groupItem => { if (groupItem.Item2.Count <= 1) { return; } Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable; Excel.PivotFields pfs = pt.RowFields; string selectedFields = pfs.Item(0).Name + "["; groupItem.Item2.ForEach(item => selectedFields += item + ","); selectedFields = selectedFields.Substring(0, selectedFields.Length - 1); selectedFields += "]"; pt.PivotSelect(selectedFields, Excel.XlPTSelectionMode.xlLabelOnly); var selection = Globals.PivotGroupingAddIn.Application.Selection; selection.Group(); foreach (Excel.PivotField pf in pt.PivotFields()) { foreach (Excel.PivotItem rowItem in pf.PivotItems()) { if (rowItem.Value.StartsWith("Group")) { rowItem.Value = groupItem.Item1; } } } }); this.Close(); }
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(); }
//성공 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); } }
/// <summary> /// Create a PivotTable with data from a tab-delimiter text file. /// </summary> /// <param name="filePath">Text file location.</param> /// <returns>Created PivotTable.</returns> private Excel.PivotTable CreatePivotTable(string filePath) { // If the table is already there, // return the existing table. string tableName = Properties.Resources.AveragesPivotTableName; Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing); System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>(); if (tables != null) { int count = tables.Count; for (int i = 1; i <= count; i++) { Excel.PivotTable table = tables.Item(i); if (table.Name == tableName) { return(table); } } } try { // AddFields will resize the columns. Save the columns' widths // for restoring them after pivot fields are added foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells) { savedWidths.Enqueue((double)column.ColumnWidth); } // PivotTable creation requires that protection be off. Globals.ThisWorkbook.MakeReadWrite(); Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath); table.Name = tableName; // Adds the desired rows and columns within // the PivotTable. table.AddFields("Flavor", missing, missing, missing); Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage); // Sets the view of data desired within the PivotTable. // Format "0.0" - one decimal place. soldField.NumberFormat = "0.0"; Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage); // Sets the view of data desired within the PivotTable. // Format "0.0" - two decimal places. profitField.NumberFormat = "0.00"; // Hiding the two floating bars that get added when a PivotTable is created. Globals.ThisWorkbook.ShowPivotTableFieldList = false; Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false; return(table); } finally { // AddFields will have resized the columns. Restore the columns' widths. foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells) { column.ColumnWidth = savedWidths.Dequeue(); } Globals.ThisWorkbook.MakeReadOnly(); } }
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) {} }
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 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 } }
/// <summary> /// 创建一个数据透视表,其中包含的数据来自用制表符分隔的文本文件。 /// </summary> /// <param name="filePath">文本文件所在位置。</param> /// <returns>已创建的数据透视表。</returns> private Excel.PivotTable CreatePivotTable(string filePath) { // 如果该表已存在, // 则返回现有的表。 string tableName = Properties.Resources.AveragesPivotTableName; Excel.PivotTables tables = (Excel.PivotTables) this.PivotTables(missing); System.Collections.Generic.Queue <double> savedWidths = new System.Collections.Generic.Queue <double>(); if (tables != null) { int count = tables.Count; for (int i = 1; i <= count; i++) { Excel.PivotTable table = tables.Item(i); if (table.Name == tableName) { return(table); } } } try { // AddField 将调整列的大小。 // 保存列宽,以便在添加数据透视字段之后还原 foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells) { savedWidths.Enqueue((double)column.ColumnWidth); } // 创建数据透视表需要关闭保护功能。 Globals.ThisWorkbook.MakeReadWrite(); Excel.PivotTable table = Globals.ThisWorkbook.CreateSalesPivotTable(this.get_Range(pivotTableAddress, missing), filePath); table.Name = tableName; // 在数据透视表中,添加所需的 // 行和列。 table.AddFields("Flavor", missing, missing, missing); Excel.PivotField soldField = table.AddDataField(table.PivotFields("Sold"), Properties.Resources.AverageSold, Excel.XlConsolidationFunction.xlAverage); // 在数据透视表中设置所需数据的视图。 // 格式“0.0”- 一个小数位。 soldField.NumberFormat = "0.0"; Excel.PivotField profitField = table.AddDataField(table.PivotFields("Profit"), Properties.Resources.AverageProfit, Excel.XlConsolidationFunction.xlAverage); // 在数据透视表中设置所需数据的视图。 // 格式“0.00”- 两个小数位。 profitField.NumberFormat = "0.00"; // 隐藏创建数据透视表时添加的两个浮动栏。 Globals.ThisWorkbook.ShowPivotTableFieldList = false; Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false; return(table); } finally { // AddField 将调整列的大小。还原列宽。 foreach (Excel.Range column in DayInventory.HeaderRowRange.Cells) { column.ColumnWidth = savedWidths.Dequeue(); } Globals.ThisWorkbook.MakeReadOnly(); } }
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 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; }
public void ecxelPoster() { DateTime pReadDate = DateTime.Now; string pSheetName = "Poster"; _Excel.Application oXL = null;; _Excel._Workbook oWB; _Excel._Worksheet oSheetPoster; _Excel._Worksheet oSheetRegnskab; _Excel.Window oWindow; _Excel.Range oRng; var rec_regnskab = Program.qryAktivRegnskab(); string SaveAs = rec_regnskab.Eksportmappe + pSheetName + pReadDate.ToString("_yyyyMMdd_hhmmss") + ".xlsx"; var JournalPoster = from h in Program.karPosteringer join d1 in Program.karKontoplan on h.Konto equals d1.Kontonr into details1 from x in details1.DefaultIfEmpty() orderby h.Nr select new clsJournalposter { ds = (x.Type == "Drift") ? "D" : "S", k = IUAP(x.Type, x.DK), Konto = h.Konto.ToString() + "-" + x.Kontonavn, Dato = h.Dato, Bilag = h.Bilag, Nr = h.Nr, Id = h.Id, Tekst = h.Tekst, Beløb = h.Nettobeløb, }; using (new ExcelUILanguageHelper()) { try { //Start Excel and get Application object. oXL = new _Excel.Application(); //oXL.Visible = false; oXL.Visible = true; //For debug //Get a new workbook. oWB = oXL.Workbooks.Add((Missing.Value)); oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet; oWindow = oXL.ActiveWindow; if (pSheetName.Length > 0) { oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length); } int row = 1; Program.frmMain.MainformProgressBar.Value = 0; Program.frmMain.MainformProgressBar.Minimum = 0; Program.frmMain.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count(); Program.frmMain.MainformProgressBar.Step = 1; Program.frmMain.MainformProgressBar.Visible = true; foreach (clsJournalposter m in JournalPoster) { Program.frmMain.MainformProgressBar.PerformStep(); row++; Type objectType = m.GetType(); PropertyInfo[] properties = objectType.GetProperties(); int col = 0; foreach (PropertyInfo property in properties) { col++; string Name = property.Name; //string NamePropertyType = property.GetValue(m, null).GetType().ToString(); oSheetPoster.Cells[row, col] = property.GetValue(m, null); if (row == 2) { object[] CustomAttributes = property.GetCustomAttributes(false); foreach (var att in CustomAttributes) { Type tp = att.GetType(); if (tp.ToString() == "Trans2Summa3060.Fieldattr") { Fieldattr attr = (Fieldattr)att; string heading = attr.Heading; oSheetPoster.Cells[1, col] = heading; } } } } } oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value]; oRng.Font.Name = "Arial"; oRng.Font.Size = 12; oRng.Font.Strikethrough = false; oRng.Font.Superscript = false; oRng.Font.Subscript = false; oRng.Font.OutlineFont = false; oRng.Font.Shadow = false; oRng.Font.Bold = true; oRng.HorizontalAlignment = _Excel.Constants.xlCenter; oRng.VerticalAlignment = _Excel.Constants.xlBottom; oRng.WrapText = false; oRng.Orientation = 0; oRng.AddIndent = false; oRng.IndentLevel = 0; oRng.ShrinkToFit = false; oRng.MergeCells = false; string BottomRight = "D" + row.ToString(); oRng = oSheetPoster.get_Range("D2", BottomRight); oRng.NumberFormat = "dd-mm-yyyy"; oSheetPoster.Cells.EntireColumn.AutoFit(); oWindow.SplitRow = 1; oWindow.FreezePanes = true; oSheetPoster.get_Range("A1", Missing.Value).Select(); oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing); //oXL.Visible = true; //For debug _Excel.Range x1 = oSheetPoster.Cells[1, 1]; _Excel.Range x2 = oSheetPoster.Cells[row, 9]; _Excel.Range xx = oSheetPoster.get_Range(x1, x2); _Excel.PivotField _pvtField = null; _Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard( _Excel.XlPivotTableSourceType.xlDatabase, //SourceType xx, //SourceData oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination "PivotTable1", //TableName System.Type.Missing, //RowGrand System.Type.Missing, //CollumnGrand System.Type.Missing, //SaveData System.Type.Missing, //HasAutoformat System.Type.Missing, //AutoPage System.Type.Missing, //Reserved System.Type.Missing, //BackgroundQuery System.Type.Missing, //OptimizeCache System.Type.Missing, //PageFieldOrder System.Type.Missing, //PageFieldWrapCount System.Type.Missing, //ReadData System.Type.Missing); //Connection _pvtField = (_Excel.PivotField)_pivot.PivotFields("ds"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("k"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField; _pvtField.Function = _Excel.XlConsolidationFunction.xlSum; _pvtField.NumberFormat = "#,##0"; oSheetRegnskab.Name = "Regnskab"; oRng = oSheetRegnskab.get_Range("D3", Missing.Value); oRng.Select(); bool[] Periods = { false, false, false, false, true, false, true }; oRng.Group(true, true, Missing.Value, Periods); oRng = oSheetRegnskab.get_Range("D4", "P4"); oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight; oSheetRegnskab.PageSetup.LeftHeader = "&14" + rec_regnskab.Navn; oSheetRegnskab.PageSetup.CenterHeader = ""; oSheetRegnskab.PageSetup.RightHeader = "&P af &N"; oSheetRegnskab.PageSetup.LeftFooter = "&Z&F"; oSheetRegnskab.PageSetup.CenterFooter = ""; oSheetRegnskab.PageSetup.RightFooter = "&D&T"; oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.PrintHeadings = false; oSheetRegnskab.PageSetup.PrintGridlines = true; oSheetRegnskab.PageSetup.CenterHorizontally = false; oSheetRegnskab.PageSetup.CenterVertically = false; oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape; oSheetRegnskab.PageSetup.Draft = false; oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4; oSheetRegnskab.PageSetup.FirstPageNumber = 1; oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver; oSheetRegnskab.PageSetup.BlackAndWhite = false; oSheetRegnskab.PageSetup.Zoom = 100; oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed; oWB.ShowPivotTableFieldList = false; for (var i = oWB.Worksheets.Count; i > 0; i--) { _Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i); if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster")) { oSheetWrk.Delete(); } } oSheetRegnskab.get_Range("A1", Missing.Value).Select(); oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Saved = true; oXL.Visible = true; Program.frmMain.MainformProgressBar.Visible = false; //oXL.Quit(); //oXL = null; } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } } }
private void ecxelPoster(int regnskabsår) { var api = UCInitializer.GetBaseAPI; CompanyFinanceYear CompanyFinanceYear = null; var task1 = api.Query <CompanyFinanceYear>(); task1.Wait(); var cols1 = task1.Result; foreach (var col in cols1) { if (col.PeriodeStart(0).Year == regnskabsår) { CompanyFinanceYear = col; } } var task2a = api.Query <Debtor>(); task2a.Wait(); var karDebtor = task2a.Result; var task2b = api.Query <Creditor>(); task2b.Wait(); var karCreditor = task2b.Result; KarDebCred karDebCred = new KarDebCred(); foreach (var d in karDebtor) { RecDebCred recDebCred = new RecDebCred() { _Account = d._Account, _Name = d._Name }; karDebCred.Add(recDebCred); } foreach (var k in karCreditor) { RecDebCred recDebCred = new RecDebCred() { _Account = k._Account, _Name = k._Name }; karDebCred.Add(recDebCred); } var task3 = api.Query <GLAccount>(); task3.Wait(); var karGLAccount = task3.Result; var crit = new List <PropValuePair>(); string dateinterval = string.Format("{0}..{1}", CompanyFinanceYear._FromDate.ToShortDateString(), CompanyFinanceYear._ToDate.ToShortDateString()); var pair = PropValuePair.GenereteWhereElements("Date", typeof(DateTime), dateinterval); crit.Add(pair); var task4 = api.Query <GLTrans>(crit); task4.Wait(); var karGLTrans = task4.Result; DateTime pReadDate = DateTime.Now; string pSheetName = "Poster"; char[] dash = { '-' }; _Excel.Application oXL = null;; _Excel._Workbook oWB; _Excel._Worksheet oSheetPoster; _Excel._Worksheet oSheetRegnskab; _Excel._Worksheet oSheetRegnskab_puls3060 = null; _Excel.Window oWindow; _Excel.Range oRng; string rec_Regnskaber = @"Z:\Regnskaber"; rec_Regnskaber = Environment.ExpandEnvironmentVariables(rec_Regnskaber); string SaveAs = rec_Regnskaber + @"\" + pSheetName + pReadDate.ToString("_yyyyMMdd_HHmmss") + ".xlsx"; var JournalPoster = from h in karGLTrans join d1 in karGLAccount on h._Account equals d1._Account into details1 from x1 in details1.DefaultIfEmpty() join d2 in karDebCred on h._DCAccount equals d2._Account into details2 from x2 in details2.DefaultIfEmpty(new RecDebCred() { _Account = null, _Name = null }) orderby h._JournalPostedId, h._Voucher, h._VoucherLine select new clsJournalposter { ds = DS(x1.AccountTypeEnum), k = IUAP(x1.AccountTypeEnum), Konto = h._Account + "-" + x1._Name, DebKrd = x2._Name, //DebKrd = h._DCAccount, Udvalg = h._Dimension1, Aktivitet = h._Dimension2, Dato = h._Date, Klade = h._JournalPostedId, Serie = h._NumberSerie, Bilag = h._Voucher, Linie = h._VoucherLine, Tekst = h._Text, Beløb = h._Amount, }; var count = JournalPoster.Count(); using (new ExcelUILanguageHelper()) { try { //Start Excel and get Application object. oXL = new _Excel.Application(); oXL.Visible = true; //oXL.Visible = true; //For debug //Get a new workbook. oWB = oXL.Workbooks.Add((Missing.Value)); oSheetPoster = (_Excel._Worksheet)oWB.ActiveSheet; oWindow = oXL.ActiveWindow; if (pSheetName.Length > 0) { oSheetPoster.Name = pSheetName.Substring(0, pSheetName.Length > 34 ? 34 : pSheetName.Length); } this.MainformProgressBar.Value = 0; this.MainformProgressBar.Minimum = 0; this.MainformProgressBar.Maximum = (from h in karGLTrans select h).Count(); this.MainformProgressBar.Step = 1; this.MainformProgressBar.Visible = true; //////////////////////////////////////////////////////////////////////// oSheetPoster.Name = "Poster"; int row = 1; foreach (clsJournalposter m in JournalPoster) { this.MainformProgressBar.PerformStep(); row++; //if (row > 500) break; //<---------------------------------------------- Type objectType = m.GetType(); PropertyInfo[] properties = objectType.GetProperties(); int col = 0; foreach (PropertyInfo property in properties) { col++; string Name = property.Name; //string NamePropertyType = property.GetValue(m, null).GetType().ToString(); oSheetPoster.Cells[row, col] = property.GetValue(m, null); if (row == 2) { object[] CustomAttributes = property.GetCustomAttributes(false); foreach (var att in CustomAttributes) { Type tp = att.GetType(); if (tp.ToString() == "Medlem3060uc.Fieldattr") { Fieldattr attr = (Fieldattr)att; string heading = attr.Heading; oSheetPoster.Cells[1, col] = heading; } } } } } oRng = (_Excel.Range)oSheetPoster.Rows[1, Missing.Value]; oRng.Font.Name = "Arial"; oRng.Font.Size = 12; oRng.Font.Strikethrough = false; oRng.Font.Superscript = false; oRng.Font.Subscript = false; oRng.Font.OutlineFont = false; oRng.Font.Shadow = false; oRng.Font.Bold = true; oRng.HorizontalAlignment = _Excel.Constants.xlCenter; oRng.VerticalAlignment = _Excel.Constants.xlBottom; oRng.WrapText = false; oRng.Orientation = 0; oRng.AddIndent = false; oRng.IndentLevel = 0; oRng.ShrinkToFit = false; oRng.MergeCells = false; string BottomRight = "G" + row.ToString(); //<------------------HUSK oRng = oSheetPoster.get_Range("G2", BottomRight); oRng.NumberFormat = "dd-mm-yyyy"; oSheetPoster.ListObjects.AddEx(_Excel.XlListObjectSourceType.xlSrcRange, oSheetPoster.UsedRange, System.Type.Missing, _Excel.XlYesNoGuess.xlYes).Name = "PosterList"; oSheetPoster.Cells.EntireColumn.AutoFit(); oWindow.SplitRow = 1; oWindow.FreezePanes = true; oSheetPoster.get_Range("A1", Missing.Value).Select(); oSheetRegnskab = (_Excel._Worksheet)oWB.Worksheets.Add(oSheetPoster, System.Type.Missing, System.Type.Missing, System.Type.Missing); //oXL.Visible = true; //For debug _Excel.Range x1 = oSheetPoster.Cells[1, 1]; _Excel.Range x2 = oSheetPoster.Cells[row, 13]; //<--------------------HUSK _Excel.Range xx = oSheetPoster.get_Range(x1, x2); _Excel.PivotField _pvtField = null; _Excel.PivotTable _pivot = oSheetPoster.PivotTableWizard( _Excel.XlPivotTableSourceType.xlDatabase, //SourceType xx, //SourceData oSheetRegnskab.get_Range("A3", Missing.Value), //TableDestination "PivotTable1", //TableName System.Type.Missing, //RowGrand System.Type.Missing, //CollumnGrand System.Type.Missing, //SaveData System.Type.Missing, //HasAutoformat System.Type.Missing, //AutoPage System.Type.Missing, //Reserved System.Type.Missing, //BackgroundQuery System.Type.Missing, //OptimizeCache System.Type.Missing, //PageFieldOrder System.Type.Missing, //PageFieldWrapCount System.Type.Missing, //ReadData System.Type.Missing); //Connection _pvtField = (_Excel.PivotField)_pivot.PivotFields("ds"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("k"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Konto"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlRowField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Dato"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlColumnField; _pvtField = (_Excel.PivotField)_pivot.PivotFields("Beløb"); _pvtField.Orientation = _Excel.XlPivotFieldOrientation.xlDataField; _pvtField.Function = _Excel.XlConsolidationFunction.xlSum; _pvtField.NumberFormat = "#,##0"; oSheetRegnskab.Name = "Regnskab"; oSheetRegnskab.Cells[2, 3] = "Regnskab Puls 3060"; oRng = oSheetRegnskab.get_Range("D3", Missing.Value); oRng.Select(); bool[] Periods = { false, false, false, false, true, false, false }; oRng.Group(true, true, Missing.Value, Periods); oRng = oSheetRegnskab.get_Range("D4", "P4"); oRng.HorizontalAlignment = _Excel.XlHAlign.xlHAlignRight; oSheetRegnskab.PageSetup.LeftHeader = "&14Regnskab Puls 3060"; oSheetRegnskab.PageSetup.CenterHeader = ""; oSheetRegnskab.PageSetup.RightHeader = "&P af &N"; oSheetRegnskab.PageSetup.LeftFooter = "&Z&F"; oSheetRegnskab.PageSetup.CenterFooter = ""; oSheetRegnskab.PageSetup.RightFooter = "&D&T"; oSheetRegnskab.PageSetup.LeftMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.RightMargin = oXL.InchesToPoints(0.75); oSheetRegnskab.PageSetup.TopMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.BottomMargin = oXL.InchesToPoints(1); oSheetRegnskab.PageSetup.HeaderMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.FooterMargin = oXL.InchesToPoints(0.5); oSheetRegnskab.PageSetup.PrintHeadings = false; oSheetRegnskab.PageSetup.PrintGridlines = true; oSheetRegnskab.PageSetup.CenterHorizontally = false; oSheetRegnskab.PageSetup.CenterVertically = false; oSheetRegnskab.PageSetup.Orientation = _Excel.XlPageOrientation.xlLandscape; oSheetRegnskab.PageSetup.Draft = false; oSheetRegnskab.PageSetup.PaperSize = _Excel.XlPaperSize.xlPaperA4; oSheetRegnskab.PageSetup.FirstPageNumber = 1; oSheetRegnskab.PageSetup.Order = _Excel.XlOrder.xlDownThenOver; oSheetRegnskab.PageSetup.BlackAndWhite = false; oSheetRegnskab.PageSetup.Zoom = 100; oSheetRegnskab.PageSetup.PrintErrors = _Excel.XlPrintErrors.xlPrintErrorsDisplayed; oWB.ShowPivotTableFieldList = false; /* * for (var i = oWB.Worksheets.Count; i > 0; i--) * { * _Excel._Worksheet oSheetWrk = (_Excel._Worksheet)oWB.Worksheets.get_Item(i); * if ((oSheetWrk.Name != "Regnskab") && (oSheetWrk.Name != "Poster")) * { * oSheetWrk.Delete(); * } * } */ oSheetRegnskab_puls3060 = oSheetRegnskab; oSheetRegnskab.get_Range("A1", Missing.Value).Select(); ////////////////////////////////////////////////////////////////////////////////////////////// oSheetRegnskab_puls3060.Activate(); oSheetRegnskab_puls3060.get_Range("A1", Missing.Value).Select(); oWB.SaveAs(SaveAs, _Excel.XlFileFormat.xlWorkbookDefault, "", "", false, false, _Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); oWB.Saved = true; oXL.Visible = true; this.MainformProgressBar.Visible = false; oXL.Quit(); oXL = null; this.imapSaveExcelFile(SaveAs, "Puls3060 Regnskab"); } catch (Exception theException) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat(errorMessage, theException.Message); errorMessage = String.Concat(errorMessage, " Line: "); errorMessage = String.Concat(errorMessage, theException.Source); MessageBox.Show(errorMessage, "Error"); } } }
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); }
public void generarReporte(DataGridView tabla, string nombreHojaReporte, string tituloReporte, string celdaInicioTitulo, string celdaFinTitulo, int indexInicioTitulo, int indexFinTitulo) { ////Para futura referencia, esta es una forma probable de obtener un rango de celdas basado en indices ////Excel.Range range = hoja.Ranges(hoja.Cells[1, 1], hoja.Cells[1, 2]); string columnaOrdenamiento = "Filtro"; try { Cursor.Current = Cursors.WaitCursor; exportar.Enabled = false; Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook wb = excel.Application.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet hojaDatos = wb.ActiveSheet; int IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) // Columnas { IndiceColumna++; hojaDatos.Cells[1, IndiceColumna] = col.Name; } //agregar campo de ordenamiento hojaDatos.Cells[1, IndiceColumna + 1] = columnaOrdenamiento; int IndiceFila = 0; foreach (DataGridViewRow row in tabla.Rows) // Filas { IndiceFila++; IndiceColumna = 0; foreach (DataGridViewColumn col in tabla.Columns) { IndiceColumna++; hojaDatos.Cells[IndiceFila + 1, IndiceColumna] = "'" + row.Cells[col.Name].Value; } hojaDatos.Cells[IndiceFila + 1, IndiceColumna + 1] = columnaOrdenamiento; } Excel.Worksheet hojaReporte = excel.Sheets.Add(); hojaReporte.Name = nombreHojaReporte; hojaReporte.Activate(); Excel.Range oRange = hojaDatos.UsedRange; Excel.PivotCache oPivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, oRange, Type.Missing); Excel.Range oRange2 = hojaReporte.Cells[5, 2]; Excel.PivotCaches pch = wb.PivotCaches(); pch.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, oRange).CreatePivotTable(oRange2, "reportePersonas", Type.Missing, Type.Missing); Excel.PivotTable pvt = hojaReporte.PivotTables("reportePersonas") as Excel.PivotTable; //configuracion de la tabla dinamica pvt.RowGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.ColumnGrand = false; //Ocultar los totales y subtotales de la tabla dinamica pvt.EnableFieldList = false; //desactivar la opcion para apagar o encender campos en la tabla dinamica pvt.ShowDrillIndicators = false; //quitar los simbolos de + en cada celda pvt.EnableDrilldown = false; //no permitir minimizar las filas pvt.InGridDropZones = false; //no permitir drag&drop de las columnas pvt.ShowTableStyleRowHeaders = false; //no mostrar columna de por medio en negrita/otro color, segun el estilo de tabla pvt.TableStyle2 = "PivotStyleMedium9"; //settear estilo de tabla foreach (DataGridViewColumn col in tabla.Columns) // Columnas { Excel.PivotField field = (Excel.PivotField)pvt.PivotFields(col.Name); field.Orientation = Excel.XlPivotFieldOrientation.xlRowField; field.Subtotals[1] = false; } //agregar el PivotField para el campo de ordenamiento Excel.PivotField f = (Excel.PivotField)pvt.PivotFields(columnaOrdenamiento); f.Orientation = Excel.XlPivotFieldOrientation.xlDataField; f.Name = "No remover, ocultar solamente"; //hacer que las columnas tengan el tamaño adecuado hojaReporte.UsedRange.Columns.AutoFit(); //int startIndex = indexColumnaOrdenamiento.IndexOfAny("0123456789".ToCharArray()); //string indicatedColumnLetter = indexColumnaOrdenamiento.Substring(0, startIndex); string column = obtenerNombreColExcel(tabla.Columns.Count + 2); // se agregan mas dos por la posicion inicial de la tabla y la columna de ordenamiento extra hojaReporte.Range[column + "1"].EntireColumn.Hidden = true; //ocultando la columna de sort //agregar el dato de encabezado hojaReporte.Cells[2, 3] = tituloReporte; Excel.Range titulo = hojaReporte.Range[celdaInicioTitulo, celdaFinTitulo]; titulo.Merge(); titulo.Font.Bold = true; titulo.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; titulo.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = Color.Black; hojaReporte.Cells[3, indexInicioTitulo] = "Fecha:"; hojaReporte.Cells[3, indexInicioTitulo + 1] = DateTime.Today; hojaReporte.Cells[3, indexFinTitulo - 1] = "Hora:"; hojaReporte.Cells[3, indexFinTitulo] = DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString(); //eliminar la hoja de datos excel.DisplayAlerts = false; //bypass del bug que evita que se elimine la hoja hojaDatos.Activate(); hojaDatos.Delete(); hojaReporte.Activate(); excel.DisplayAlerts = true; //retornar la propiedad al valor original MessageBox.Show("Infome generado exitosamente.", "Operación completa", MessageBoxButtons.OK, MessageBoxIcon.Information); exportar.Enabled = true; excel.Visible = true; Cursor.Current = Cursors.Default; } catch (Exception e) { Console.WriteLine(e.StackTrace); Cursor.Current = Cursors.Default; exportar.Enabled = true; MessageBox.Show("Ha ocurrido un error en la creación del documento, póngase en contacto con los desarrolladores del sistema.", "Error - AlbergueHN", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
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; }
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(); }