private void PivotData() { index++; Excel.Worksheet osheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; var pch = Globals.ThisAddIn.Application.ActiveWorkbook.PivotCaches(); Excel.Range pivotData = (Excel.Range)osheet.UsedRange; Excel.PivotCache pc = pch.Create(Excel.XlPivotTableSourceType.xlDatabase, pivotData); Excel.PivotTable pvt = pc.CreatePivotTable(osheet.Range["J1"], "MyPivotTable" + index); Excel.PivotField pageField = (Excel.PivotField)pvt.PivotFields("WORKFLOW"); pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; Excel.PivotField rowField = (Excel.PivotField)pvt.PivotFields("Status"); rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField valueField = (Excel.PivotField)pvt.PivotFields("Status"); valueField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; Excel.SlicerCaches slicerCaches = Globals.ThisAddIn.Application.ActiveWorkbook.SlicerCaches; //Month Slicer string nameDateTime = "Slicer_DATETIME" + index.ToString(); string nameMonth = "Slicer_Month" + index.ToString(); Excel.SlicerCache monthSlicerCache = slicerCaches.Add2(pvt, "DATETIME", nameDateTime, XlSlicerCacheType.xlTimeline); Excel.Slicers monthSlicers = monthSlicerCache.Slicers; Excel.Slicer monthSlicer = monthSlicers.Add(osheet, Type.Missing, nameMonth, "Date Range", 160, 10, 250, 150); }
// Parametre olarak aktif çalışma sayfası, aktif uygulama, aktf çalışma kitabını alan ve sonucunda bir pivot tablo çizen metod. public Worksheet createPivotTable(Worksheet currentSheet, Application currentApp, Workbook currentBook, int rowCount) { // Pivot tablo yeni bir excel çalışma sayfası açılarak yazdırılıyor. Excel.Range oRange = currentSheet.Range["C1:D" + rowCount]; if (currentApp.Application.Sheets.Count < 3) { currentSheet = (Excel.Worksheet)currentBook.Worksheets.Add(); } else { currentSheet = currentApp.Worksheets[3]; } currentSheet.Name = "Apriori Pivot Table"; Excel.Range oRange2 = currentSheet.Cells[1, 1]; Excel.PivotCache oPivotCache = (Excel.PivotCache)currentBook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); Excel.PivotTable oPivotTable = (Excel.PivotTable)currentSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: oRange2, TableName: "Apriori Pivot Table"); // Çizilecek pivot tablo referans olarak işlem numarası ve ürünleri alıyor. // işlem numarasına göre hangi ürünler o işlem içerisinde varsa tabloda işaretleme yapıyor. Excel.PivotField oPivotField1 = (Excel.PivotField)oPivotTable.PivotFields("Transaction"); oPivotField1.Orientation = Excel.XlPivotFieldOrientation.xlRowField; Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField2.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields("Item"); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlCount; return(currentSheet); }
static void Main(string[] args) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\stackoverflow.xlsx"); Excel.Worksheet xlWs = (Excel.Worksheet)xlWb.Sheets[1]; // Sheet1 Excel.Worksheet xlWsNew = (Excel.Worksheet)xlWb.Sheets.Add(); // find the data range Excel.Range dataRange = getDataRange(ref xlWs); // start by creating the PivotCache - this tells Excel that there is a data connection // to data inside the workbook (could be used to get external data, too) Excel.PivotCache pc = xlWb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase , dataRange , Excel.XlPivotTableVersionList.xlPivotTableVersion14); // create the pivot table and set the destination to the new sheet at A1 Excel.PivotTable pt = pc.CreatePivotTable(xlWsNew.Range["A1"]); // get the PivotField "Same" for easy referencing Excel.PivotField pf = (Excel.PivotField)pt.PivotFields("Same"); // first add the count pt.AddDataField(pf, "Count of Same", Excel.XlConsolidationFunction.xlCount); // now add the row with the same field pf.Orientation = Excel.XlPivotFieldOrientation.xlRowField; pf.Position = 1; // behold!!! xlWsNew.Select(); xlApp.Visible = true; }
public static void SetPivotFieldPage(Excel.PivotField pf, string currentPageName) { pf.ClearAllFilters(); if (currentPageName != "All") { var pageName = DaxDrillParser.CreatePivotFieldPageName(pf.Name, currentPageName); pf.CurrentPageName = pageName; } }
public static bool IsPivotDataCell(Excel.Range rngCell) { try { Excel.PivotCell pc = rngCell.PivotCell; Excel.PivotField pf = pc.PivotField; return(pf.Orientation == Excel.XlPivotFieldOrientation.xlDataField); } catch { return(false); } }
public static bool IsMultiplePageItemsEnabled(Excel.PivotField pf) { // Excel throws error if you try to access CurrentPageName on a page field that has multiple selections // This function handles the error try { return(pf.CurrentPageName == null); } catch { return(pf.Orientation == Excel.XlPivotFieldOrientation.xlPageField); } }
public void SetPivotFieldPage() { try { var xlApp = (Excel.Application)ExcelDnaUtil.Application; Excel.PivotField pf = xlApp.ActiveCell.PivotField; ExcelHelper.SetPivotFieldPage(pf, pivotFieldPageEditForm.PageItemValue); } catch (Exception ex) { MsgForm.ShowMessage(ex); } }
/// <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) { } }
//Creates pivot table private void CreatePivotSheet(Excel._Workbook workbook, Excel._Worksheet dataSheet, Excel._Worksheet pivotSheet, string tableName, bool unified) { //If consolidated sheet, range selects up until column C, if normal pivot, selects up until K string col; if (unified == true) { col = "C"; } else { col = "K"; } //Get last used row var lastUsedRow = getLastUsedRow(dataSheet); //Select all data from starting cell to last column + row var dataRange = dataSheet.get_Range("A1", col + lastUsedRow); var pivotRange = pivotSheet.Cells[1, 1]; //Select target location var oPivotCache = (Excel.PivotCache)workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, dataRange); //Create cache specifying data is coming from a table var oPivotTable = (Excel.PivotTable)pivotSheet.PivotTables().Add(PivotCache: oPivotCache, TableDestination: pivotRange, TableName: tableName); //Create table if (unified == true) //If consolidated sheet { //Set Row field to 'APP' var RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP"); RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; //Set Values field to 'Total' var SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("Total"); SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; SumPivotField.Function = Excel.XlConsolidationFunction.xlSum; SumPivotField.Name = "CPU Time"; //Set Column field to 'LPAR' var ColPivotField = (Excel.PivotField)oPivotTable.PivotFields("LPAR"); ColPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; } else //If normal sheet { //Set Row field to 'APP' Excel.PivotField RowPivotField = (Excel.PivotField)oPivotTable.PivotFields("APP"); RowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; //Set Values field to 'CPUTIME' Excel.PivotField SumPivotField = (Excel.PivotField)oPivotTable.PivotFields("CPUTIME"); SumPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; SumPivotField.Function = Excel.XlConsolidationFunction.xlSum; SumPivotField.Name = "CPU Time"; } }
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 AddSingleAxisFiltersToDic(Excel.PivotCell pc, PivotCellDictionary pivotCellDic) { Dictionary <string, string> singDic = pivotCellDic.SingleSelectDictionary; //Filter by Row and ColumnFields - note, we don't need a loop here but will use one just in case foreach (Excel.PivotItem pi in pc.RowItems) { Excel.PivotField pf = (Excel.PivotField)pi.Parent; singDic.Add(pf.Name, pi.SourceName.ToString()); } foreach (Excel.PivotItem pi in pc.ColumnItems) { Excel.PivotField pf = (Excel.PivotField)pi.Parent; singDic.Add(pf.Name, pi.SourceName.ToString()); } }
Excel.PivotTable CreatePivotTable() { TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { Excel.Range destination = this.worksheet.get_Range(pivotTableAddress, defaultParameter); Excel.PivotTable pivotTable; pivotTable = Globals.ThisWorkbook.CreateSalesPivotTable(destination, generator.FullPath); // 调整新的数据透视表的属性 pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; // 在数据透视表中,添加所需的 // 行和列。 pivotTable.AddFields("Flavor", defaultParameter, defaultParameter, defaultParameter); Excel.PivotField soldField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.AverageOfUnitsSold, Excel.XlConsolidationFunction.xlAverage); // 在数据透视表中设置所需数据的视图。 // 格式“0.0”- 一个小数位。 soldField.NumberFormat = "0.0"; Excel.PivotField profitField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.StdDevOfUnitsSold, Excel.XlConsolidationFunction.xlStDev); // 在数据透视表中设置所需数据的视图。 // 格式“0.00”- 两个小数位。 profitField.NumberFormat = "0.00"; // 隐藏创建数据透视表时添加的两个浮动栏。 Globals.ThisWorkbook.ShowPivotTableFieldList = false; Globals.ThisWorkbook.Application.CommandBars["PivotTable"].Visible = false; return(pivotTable); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; } finally { generator.Dispose(); } }
Excel.PivotTable CreatePivotTable() { TextFileGenerator generator = new TextFileGenerator(Globals.DataSet.Sales); try { Excel.Range destination = this.worksheet.get_Range(pivotTableAddress, defaultParameter); Excel.PivotTable pivotTable; pivotTable = Globals.ThisWorkbook.CreateSalesPivotTable(destination, generator.FullPath); // Adjusts the properties of the new PivotTable pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; // Adds the desired rows and columns within // the PivotTable. pivotTable.AddFields("Flavor", defaultParameter, defaultParameter, defaultParameter); Excel.PivotField soldField = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.AverageOfUnitsSold, 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 = pivotTable.AddDataField(pivotTable.PivotFields("Sold"), Properties.Resources.StdDevOfUnitsSold, Excel.XlConsolidationFunction.xlStDev); // Sets the view of data desired within the PivotTable. // Format "0.00" - 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(pivotTable); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); throw; } finally { generator.Dispose(); } }
// Функция сведения двух таблиц 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 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"); } } }
/// <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(); } }
//성공 1 실패 0 만든 기존파일에 내용추가 피벗 경간 static int makePivotPosition(Excel.Workbook wb, Dictionary <string, List <readVO> > lists, String Start, String End) { try { Excel.Worksheet copyws = null; Excel.Worksheet pivotws = null; foreach (KeyValuePair <string, List <readVO> > data in lists) { string sheet_name = null; copyws = wb.Worksheets[data.Key] as Excel.Worksheet; sheet_name = copyws.Name; pivotws = wb.Sheets.Add(After: wb.Sheets[wb.Sheets.Count]); pivotws.Name = sheet_name + "_피벗테이블(경간)"; Excel.Range oRange = copyws.get_Range(Start, End); // specify first cell for pivot table Excel.Range oRange2 = pivotws.get_Range("B2", "B2"); // create Pivot Cache and Pivot Table Excel.PivotCache oPivotCache = (Excel.PivotCache)wb.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, oRange); // I have error on this line Excel.PivotTable oPivotTable = (Excel.PivotTable)pivotws.PivotTables().Add(oPivotCache, oRange2, "Summary"); // create Pivot Field, note that name will be the same as column name on sheet one Excel.PivotField oPivotField = (Excel.PivotField)oPivotTable.PivotFields(1); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; int count = oPivotTable.PivotFields(1).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[1] = true; oPivotField.Subtotals[1] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(3); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(3).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[3] = true; oPivotField.Subtotals[3] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(2); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(2).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[2] = true; oPivotField.Subtotals[2] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(5); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(5).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField.Subtotals[5] = true; oPivotField.Subtotals[5] = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(11); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; count = oPivotTable.PivotFields(11).PivotItems.Count; oPivotField.PivotItems(count).visible = false; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(9); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlSum; oPivotField.Value = "합계:개소"; oPivotField = (Excel.PivotField)oPivotTable.PivotFields(10); oPivotField.Orientation = Excel.XlPivotFieldOrientation.xlDataField; oPivotField.Function = Excel.XlConsolidationFunction.xlSum; oPivotField.Value = "합계:물량"; Excel.PivotField dataField = oPivotTable.DataPivotField; dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; Excel.Range rs = pivotws.UsedRange; rs.Columns.AutoFit(); oPivotTable.SubtotalHiddenPageItems = true; } return(1); } catch (Exception ex) { Console.WriteLine(ex); ReleaseExcelProcess(excelApp); MessageBox.Show(ex.ToString(), "오류발생", MessageBoxButtons.OK, MessageBoxIcon.Error); return(0); } }
private void GenerateReport() { #region Initialize 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 monthPivotField = null; Excel.PivotField statusPivotField = null; Excel.PivotField resolvedPivotField = null; Excel.PivotField threatIdPivotField = null; Excel.PivotField threatIdCountPivotField = null; Excel.SlicerCaches slicerCaches = null; Excel.SlicerCache monthSlicerCache = null; Excel.Slicers monthSlicers = null; Excel.Slicer monthSlicer = null; Excel.SlicerCache statusSlicerCache = null; Excel.Slicers statusSlicers = null; Excel.Slicer statusSlicer = null; Excel.SlicerCache resolvedSlicerCache = null; Excel.Slicers resolvedSlicers = null; Excel.Slicer resolvedSlicer = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; try { pivotWorkSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Worksheets.get_Item("Threat Reports"); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet.Application.DisplayAlerts = false; pivotWorkSheet.Delete(); pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet; pivotWorkSheet.Name = "Agent Reports"; pivotWorkSheet.Activate(); } catch { pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook.Worksheets.Add() as Excel.Worksheet; pivotWorkSheet.Name = "Agent Reports"; pivotWorkSheet.Activate(); } (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWindow.DisplayGridlines = false; #region Create Headings // Create headings // ================================================================================================================= Excel.Range title = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "A1"); title.ClearFormats(); title.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); title.Font.Color = System.Drawing.Color.White; title.InsertIndent(1); title.Font.Size = 18; title.VerticalAlignment = -4108; // xlCenter Excel.Range titleRow = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "CA1"); titleRow.Select(); titleRow.RowHeight = 33; titleRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[1, 1] = "Threat Reports"; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[2, 1] = "Generated by: " + userName; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 1] = DateTime.Now.ToString("f"); Excel.Range rowSeparator = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A3", "CA3"); rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(r_color, g_color, b_color)); // rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = 1; // xlContinuous rowSeparator.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = 4; // xlThick #endregion // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; // pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, "Threats!$A$4:$" + ExcelColumnLetter(colCount) + "$" + rowCount); // string rangeName = "Threats!$A$4:$T$100"; string rangeName = "'Agent Data'!$A$4:$" + eHelper.ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); // pivotTable = pivotCache.CreatePivotTable("Reports!R3C1"); pivotTable = pivotCache.CreatePivotTable("'Agent Reports'!R7C1"); pivotTable.NullString = "0"; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Month Pivot Field monthPivotField = (Excel.PivotField)pivotFields.Item("Created Date"); monthPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; monthPivotField.Position = 1; monthPivotField.DataRange.Cells[1].Group(true, true, Type.Missing, new bool[] { false, false, false, false, true, true, true }); // Mitigation Status Pivot Field statusPivotField = (Excel.PivotField)pivotFields.Item("Mitigation Status"); statusPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Resolved Pivot Field resolvedPivotField = (Excel.PivotField)pivotFields.Item("Resolved"); resolvedPivotField.Orientation = Excel.XlPivotFieldOrientation.xlPageField; // Threat ID Pivot Field threatIdPivotField = (Excel.PivotField)pivotFields.Item("ID"); // Count of Threat ID Field threatIdCountPivotField = pivotTable.AddDataField(threatIdPivotField, "# of Threats", Excel.XlConsolidationFunction.xlCount); slicerCaches = activeWorkBook.SlicerCaches; // Month Slicer monthSlicerCache = slicerCaches.Add(pivotTable, "Created Date", "CreatedDate"); monthSlicers = monthSlicerCache.Slicers; monthSlicer = monthSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Created Date", "Created Date", 80, 480, 144, 100); // Mitigation Status Slicer statusSlicerCache = slicerCaches.Add(pivotTable, "Mitigation Status", "MitigationStatus"); statusSlicers = statusSlicerCache.Slicers; statusSlicer = statusSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Mitigation Status", "Mitigation Status", 80, 634, 144, 100); // Resolved Slicer resolvedSlicerCache = slicerCaches.Add(pivotTable, "Resolved", "Resolved"); resolvedSlicers = resolvedSlicerCache.Slicers; resolvedSlicer = resolvedSlicers.Add((ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet, Type.Missing, "Resolved", "Resolved", 80, 788, 144, 100); // Slicer original sizes top 15, width 144, height 200 } catch (Exception ex) { MessageBox.Show(ex.Message, "Error generating report", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { #region Finally if (resolvedSlicer != null) { Marshal.ReleaseComObject(resolvedSlicer); } if (resolvedSlicers != null) { Marshal.ReleaseComObject(resolvedSlicers); } if (resolvedSlicerCache != null) { Marshal.ReleaseComObject(resolvedSlicerCache); } if (statusSlicer != null) { Marshal.ReleaseComObject(statusSlicer); } if (statusSlicers != null) { Marshal.ReleaseComObject(statusSlicers); } if (statusSlicerCache != null) { Marshal.ReleaseComObject(statusSlicerCache); } if (monthSlicer != null) { Marshal.ReleaseComObject(monthSlicer); } if (monthSlicers != null) { Marshal.ReleaseComObject(monthSlicers); } if (monthSlicerCache != null) { Marshal.ReleaseComObject(monthSlicerCache); } if (slicerCaches != null) { Marshal.ReleaseComObject(slicerCaches); } if (threatIdCountPivotField != null) { Marshal.ReleaseComObject(threatIdCountPivotField); } if (threatIdPivotField != null) { Marshal.ReleaseComObject(threatIdPivotField); } if (resolvedPivotField != null) { Marshal.ReleaseComObject(resolvedPivotField); } if (statusPivotField != null) { Marshal.ReleaseComObject(statusPivotField); } if (monthPivotField != null) { Marshal.ReleaseComObject(monthPivotField); } 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 (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } if (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } #endregion } }
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; }
private string _Caption; //cache this public LevelContainer(Excel.PivotField pf) { PivotField = pf; _Caption = pf.Caption; }
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(); } } }
private void Exportar(String Empresa, Int32 Anio, String Titulo, String SubTitulo, String Nombre, System.Data.DataTable Reporte, System.Data.DataTable Comparacion1, System.Data.DataTable Comparacion2, Boolean MostrarRebate = false) { XLExcel.Application xlApplication; XLExcel.Workbook xlWorkbook; XLExcel.Worksheet xlWorksheetData; XLExcel.Worksheet xlWorksheetPivot; XLExcel.Worksheet xlWorksheetComparacion; XLExcel.Worksheet xlWorksheetPivotRebate = null; XLExcel.Worksheet xlWorksheetPivotFFVV = null; XLExcel.Worksheet xlWorksheetPivotFFVVServicios = null; XLExcel.Range xlRange; XLExcel.Range xlRangeComparacion; object misValue = System.Reflection.Missing.Value; xlApplication = new XLExcel.Application(); Int32 _WorksheetsIndex = 1; xlWorkbook = xlApplication.Workbooks.Add(misValue); xlWorksheetData = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetData.Name = "BD"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetData, misValue, misValue); } xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : ""); if (MostrarRebate) { _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE"; } _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotFFVV.Name = "FUERZA VENTAS"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetPivotFFVVServicios.Name = "FFVV POR SERVICIOS"; _WorksheetsIndex += 1; if (xlWorkbook.Worksheets.Count < _WorksheetsIndex) { xlWorkbook.Worksheets.Add(misValue, xlWorksheetPivot, misValue, misValue); } xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[_WorksheetsIndex]; xlWorksheetComparacion.Name = "COMPARATIVO"; try { String fuente = "Calibri"; int fontsize = 11; int _sheetIndex = 1; Int32 rowIndex = 0; Int32 colIndex = 0; #region [ Statment ] xlWorksheetData.Activate(); //HEADER var _columns = new Object[1, Reporte.Columns.Count]; foreach (System.Data.DataColumn _column in Reporte.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[1, Reporte.Columns.Count]]; xlRange.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS var _data = new Object[Reporte.Rows.Count, Reporte.Columns.Count]; foreach (System.Data.DataRow _row in Reporte.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Reporte.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[2, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlRange.Value2 = _data; #endregion #region [ Comparacion ] xlWorksheetComparacion = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 6 : 5)]; xlWorksheetComparacion.Name = "COMPARATIVO"; xlWorksheetComparacion.Activate(); rowIndex = 0; colIndex = 0; //HEADER _columns = new Object[1, Comparacion1.Columns.Count]; foreach (System.Data.DataColumn _column in Comparacion1.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, Comparacion1.Columns.Count]]; xlRangeComparacion.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS _data = new Object[Comparacion1.Rows.Count, Comparacion1.Columns.Count]; foreach (System.Data.DataRow _row in Comparacion1.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Comparacion1.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[8, 1], xlWorksheetComparacion.Cells[Comparacion1.Rows.Count + 7, Comparacion1.Columns.Count]]; xlRangeComparacion.Value2 = _data; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 1]]; xlRangeComparacion.Value = (Anio - 1).ToString(); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 14]]; setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[7, 14]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[7, 1], xlWorksheetComparacion.Cells[10, 1]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; rowIndex = 0; colIndex = 0; //HEADER _columns = new Object[1, Comparacion2.Columns.Count]; foreach (System.Data.DataColumn _column in Comparacion2.Columns) { _columns[rowIndex, colIndex] = _column.ColumnName; colIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, Comparacion2.Columns.Count]]; xlRangeComparacion.Value2 = _columns; rowIndex = 0; colIndex = 0; //CELLS _data = new Object[Comparacion2.Rows.Count, Comparacion2.Columns.Count]; foreach (System.Data.DataRow _row in Comparacion2.Rows) { colIndex = 0; foreach (System.Data.DataColumn _column in Comparacion2.Columns) { _data[rowIndex, colIndex] = _row[_column.ColumnName]; colIndex += 1; } rowIndex += 1; } xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[13, 1], xlWorksheetComparacion.Cells[Comparacion2.Rows.Count + 12, Comparacion2.Columns.Count]]; xlRangeComparacion.Value2 = _data; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 1]]; xlRangeComparacion.Value = Anio.ToString(); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 14]]; setBorderCelda(xlRangeComparacion, XLExcel.XlBorderWeight.xlThin, XLExcel.XlLineStyle.xlContinuous, true, true, true, true, true, true, true, false, false); xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[12, 14]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[12, 1], xlWorksheetComparacion.Cells[15, 1]]; xlRangeComparacion.Interior.Pattern = XLExcel.XlPattern.xlPatternSolid; xlRangeComparacion.Interior.PatternColorIndex = XLExcel.XlPattern.xlPatternAutomatic; xlRangeComparacion.Interior.ThemeColor = XLExcel.XlThemeColor.xlThemeColorAccent2; xlRangeComparacion.Interior.TintAndShade = 0; xlRangeComparacion.Interior.PatternTintAndShade = 0; xlRangeComparacion.Font.ThemeColor = XLExcel.XlThemeColor.xlThemeColorDark1; xlRangeComparacion.Font.TintAndShade = 0; XLExcel.Chart XLChart1 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 250, 500, 300).Chart; XLExcel.SeriesCollection XLSeriesCollection1 = XLChart1.SeriesCollection(); XLExcel.Series XLSerie1 = XLSeriesCollection1.NewSeries(); XLSerie1.Name = "=COMPARATIVO!$A$7"; XLSerie1.Values = "=COMPARATIVO!$B$8:$M$8"; XLSerie1.XValues = "=COMPARATIVO!$B$7:$M$7"; XLExcel.Series XLSerie2 = XLSeriesCollection1.NewSeries(); XLSerie2.Name = "=COMPARATIVO!$A$12"; XLSerie2.Values = "=COMPARATIVO!$B$13:$M$13"; XLSerie2.XValues = "=COMPARATIVO!$B$7:$M$7"; XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart); XLChart1.ChartTitle.Text = String.Format("COMPARATIVO {0} - TEUS {1} VS. {2}", Titulo, (Anio - 1), Anio); XLChart1.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); XLExcel.Axis XlAxis1 = XLChart1.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis; XlAxis1.HasTitle = true; XlAxis1.AxisTitle.Caption = "TEUS"; //XLChart1.Activate(); XLChart1.Refresh(); XLExcel.Chart XLChart2 = (XLExcel.Chart)xlWorksheetComparacion.Shapes.AddChart(XLExcel.XlChartType.xl3DColumnClustered, 100, 600, 500, 300).Chart; XLExcel.SeriesCollection XLSeriesCollection2 = XLChart2.SeriesCollection(); XLExcel.Series XLSerie3 = XLSeriesCollection2.NewSeries(); XLSerie3.Name = "=COMPARATIVO!$A$7"; XLSerie3.Values = "=COMPARATIVO!$B$9:$M$9"; XLSerie3.XValues = "=COMPARATIVO!$B$7:$M$7"; XLExcel.Series XLSerie4 = XLSeriesCollection2.NewSeries(); XLSerie4.Name = "=COMPARATIVO!$A$12"; XLSerie4.Values = "=COMPARATIVO!$B$14:$M$14"; XLSerie4.XValues = "=COMPARATIVO!$B$7:$M$7"; XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart); XLChart2.ChartTitle.Text = String.Format("COMPARATIVO {0} - PROFIT {1} VS. {2}", Titulo, (Anio - 1), Anio); XLChart2.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleAdjacentToAxis); XLExcel.Axis XlAxis2 = XLChart2.Axes(XLExcel.XlAxisType.xlValue, XLExcel.XlAxisGroup.xlPrimary) as XLExcel.Axis; XlAxis2.HasTitle = true; XlAxis2.AxisTitle.Caption = "PROFIT"; //XLChart2.Activate(); XLChart2.Refresh(); //TITLE xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[2, 1], xlWorksheetComparacion.Cells[2, 14]]; xlRangeComparacion.MergeCells = true; xlRangeComparacion.Value = Titulo + " - COMPARATIVO TEUS Y PROFIT"; xlRangeComparacion.Font.Bold = true; xlRangeComparacion.Font.Size = 16; xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangeComparacion = xlWorksheetComparacion.Range[xlWorksheetComparacion.Cells[3, 1], xlWorksheetComparacion.Cells[3, 14]]; xlRangeComparacion.MergeCells = true; xlRangeComparacion.Value = SubTitulo + " - " + (Anio - 1).ToString() + " VS. " + Anio.ToString(); xlRangeComparacion.Font.Bold = true; xlRangeComparacion.Font.Size = 12; xlRangeComparacion.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangeComparacion.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; #endregion #region [ TOTAL SIN REBATE ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivot = (XLExcel.Worksheet)xlWorkbook.Worksheets[2]; xlWorksheetPivot.Name = "TOTAL " + Nombre + (MostrarRebate ? " SIN REBATE" : ""); xlWorksheetPivot.Activate(); XLExcel.Range xlRangePivot; //TITLE xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[2, 1], xlWorksheetPivot.Cells[2, 14]]; xlRangePivot.MergeCells = true; xlRangePivot.Value = Empresa + "-" + Titulo; xlRangePivot.Font.Bold = true; xlRangePivot.Font.Size = 16; xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[3, 1], xlWorksheetPivot.Cells[3, 14]]; xlRangePivot.MergeCells = true; xlRangePivot.Value = SubTitulo + " " + Anio.ToString(); xlRangePivot.Font.Bold = true; xlRangePivot.Font.Size = 12; xlRangePivot.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivot.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivot = xlWorksheetPivot.Range[xlWorksheetPivot.Cells[6, 1], xlWorksheetPivot.Cells[6, 1]];; // create Pivot Cache and Pivot Table XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTable = (XLExcel.PivotTable)xlWorksheetPivot.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivot, TableName: "SIN REBATE"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMES = (XLExcel.PivotField)XLPivotTable.PivotFields("MES"); XLPivotFieldMES.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMES.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDOR = (XLExcel.PivotField)XLPivotTable.PivotFields("VENDEDOR"); XLPivotFieldVENDEDOR.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDOR.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUS = (XLExcel.PivotField)XLPivotTable.PivotFields("TEUS"); XLPivotFieldTEUS.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUS.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUS.Name = "Sum TEUS"; XLExcel.PivotField XLPivotFieldRENT_TOTAL = (XLExcel.PivotField)XLPivotTable.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTAL.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTAL.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTAL.Name = "Sum RENT_TOTAL"; XLPivotTable.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTable.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTable.DataPivotField.Position = 2; XLPivotTable.TableStyle2 = "PivotStyleLight18"; XLPivotTable.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion if (MostrarRebate) { #region [ TOTAL CON REBATE ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotRebate = (XLExcel.Worksheet)xlWorkbook.Worksheets[3]; xlWorksheetPivotRebate.Name = "TOTAL " + Nombre + " CON REBATE"; xlWorksheetPivotRebate.Activate(); XLExcel.Range xlRangePivotRebate; //TITLE xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[2, 1], xlWorksheetPivotRebate.Cells[2, 14]]; xlRangePivotRebate.MergeCells = true; xlRangePivotRebate.Value = Empresa + "-" + Titulo; xlRangePivotRebate.Font.Bold = true; xlRangePivotRebate.Font.Size = 16; xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[3, 1], xlWorksheetPivotRebate.Cells[3, 14]]; xlRangePivotRebate.MergeCells = true; xlRangePivotRebate.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotRebate.Font.Bold = true; xlRangePivotRebate.Font.Size = 12; xlRangePivotRebate.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotRebate.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotRebate = xlWorksheetPivotRebate.Range[xlWorksheetPivotRebate.Cells[6, 1], xlWorksheetPivotRebate.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCacheRebate = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableRebate = (XLExcel.PivotTable)xlWorksheetPivotRebate.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotRebate, TableName: "CON REBATE"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("MES"); XLPivotFieldMESRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESRebate.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("VENDEDOR"); XLPivotFieldVENDEDORRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORRebate.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("TEUS"); XLPivotFieldTEUSRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSRebate.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSRebate.Name = "Sum TEUS"; XLExcel.PivotField XLPivotFieldRENT_TOTALRebate = (XLExcel.PivotField)XLPivotTableRebate.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALRebate.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALRebate.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALRebate.Name = "Sum RENTABILIDAD_CON_REBATE"; XLPivotTableRebate.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableRebate.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableRebate.DataPivotField.Position = 2; XLPivotTableRebate.TableStyle2 = "PivotStyleLight18"; XLPivotTableRebate.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion } #region [ FUERZA DE VENTAS ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotFFVV = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 4 : 3)]; xlWorksheetPivotFFVV.Name = "FUERZA VENTAS"; xlWorksheetPivotFFVV.Activate(); XLExcel.Range xlRangePivotFFVV; //TITLE xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[2, 1], xlWorksheetPivotFFVV.Cells[2, 14]]; xlRangePivotFFVV.MergeCells = true; xlRangePivotFFVV.Value = Empresa + "-" + Titulo; xlRangePivotFFVV.Font.Bold = true; xlRangePivotFFVV.Font.Size = 16; xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[3, 1], xlWorksheetPivotFFVV.Cells[3, 14]]; xlRangePivotFFVV.MergeCells = true; xlRangePivotFFVV.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotFFVV.Font.Bold = true; xlRangePivotFFVV.Font.Size = 12; xlRangePivotFFVV.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVV.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVV = xlWorksheetPivotFFVV.Range[xlWorksheetPivotFFVV.Cells[6, 1], xlWorksheetPivotFFVV.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableFFVV = (XLExcel.PivotTable)xlWorksheetPivotFFVV.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVV, TableName: "FFVV"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("MES"); XLPivotFieldMESFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESFFVV.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("VENDEDOR"); XLPivotFieldVENDEDORFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORFFVV.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("TEUS"); XLPivotFieldTEUSFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSFFVV.Name = "Sum TEUS"; if (MostrarRebate) { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENTABILIDAD_CON_REBATE"; } else { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVV = (XLExcel.PivotField)XLPivotTableFFVV.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTALFFVV.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVV.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVV.Name = "Sum RENT_TOTAL"; } XLPivotTableFFVV.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableFFVV.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableFFVV.DataPivotField.Position = 2; XLPivotTableFFVV.TableStyle2 = "PivotStyleLight18"; XLPivotTableFFVV.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion #region [ FUERZA DE VENTAS TRAFICO ] xlWorksheetData.Activate(); xlRange = xlWorksheetData.Range[xlWorksheetData.Cells[1, 1], xlWorksheetData.Cells[Reporte.Rows.Count + 1, Reporte.Columns.Count]]; xlWorksheetPivotFFVVServicios = (XLExcel.Worksheet)xlWorkbook.Worksheets[(MostrarRebate ? 5 : 4)]; xlWorksheetPivotFFVVServicios.Name = "FF VV Servicios"; xlWorksheetPivotFFVVServicios.Activate(); XLExcel.Range xlRangePivotFFVVServicios; //TITLE xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[2, 1], xlWorksheetPivotFFVVServicios.Cells[2, 14]]; xlRangePivotFFVVServicios.MergeCells = true; xlRangePivotFFVVServicios.Value = Empresa + "-" + Titulo; xlRangePivotFFVVServicios.Font.Bold = true; xlRangePivotFFVVServicios.Font.Size = 16; xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[3, 1], xlWorksheetPivotFFVVServicios.Cells[3, 14]]; xlRangePivotFFVVServicios.MergeCells = true; xlRangePivotFFVVServicios.Value = SubTitulo + " " + Anio.ToString(); xlRangePivotFFVVServicios.Font.Bold = true; xlRangePivotFFVVServicios.Font.Size = 12; xlRangePivotFFVVServicios.VerticalAlignment = XLExcel.XlVAlign.xlVAlignCenter; xlRangePivotFFVVServicios.HorizontalAlignment = XLExcel.XlHAlign.xlHAlignCenter; xlRangePivotFFVVServicios = xlWorksheetPivotFFVVServicios.Range[xlWorksheetPivotFFVVServicios.Cells[6, 1], xlWorksheetPivotFFVVServicios.Cells[6, 1]];; // create Pivot Cache and Pivot Table //XLExcel.PivotCache XLPivotCache = (XLExcel.PivotCache)xlWorkbook.PivotCaches().Add(XLExcel.XlPivotTableSourceType.xlDatabase, xlRange); XLExcel.PivotTable XLPivotTableFFVVServicios = (XLExcel.PivotTable)xlWorksheetPivotFFVVServicios.PivotTables().Add(PivotCache: XLPivotCache, TableDestination: xlRangePivotFFVVServicios, TableName: "FFVV Servicios"); // create Pivot Field, note that name will be the same as column name on sheet one XLExcel.PivotField XLPivotFieldMESFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("MES"); XLPivotFieldMESFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlColumnField; XLPivotFieldMESFFVVServicios.Name = "MES"; XLExcel.PivotField XLPivotFieldVENDEDORFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TRAFICO"); XLPivotFieldVENDEDORFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldVENDEDORFFVVServicios.Name = "TRAFICO"; XLExcel.PivotField XLPivotFieldSERVICIOFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("VENDEDOR"); XLPivotFieldSERVICIOFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotFieldSERVICIOFFVVServicios.Name = "VENDEDOR"; XLExcel.PivotField XLPivotFieldTEUSFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("TEUS"); XLPivotFieldTEUSFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldTEUSFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldTEUSFFVVServicios.Name = "Sum TEUS"; if (MostrarRebate) { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENTABILIDAD_CON_REBATE"); XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENTABILIDAD_CON_REBATE"; } else { XLExcel.PivotField XLPivotFieldRENT_TOTALFFVVServicios = (XLExcel.PivotField)XLPivotTableFFVVServicios.PivotFields("RENT_TOTAL"); XLPivotFieldRENT_TOTALFFVVServicios.Orientation = XLExcel.XlPivotFieldOrientation.xlDataField; XLPivotFieldRENT_TOTALFFVVServicios.Function = XLExcel.XlConsolidationFunction.xlSum; XLPivotFieldRENT_TOTALFFVVServicios.Name = "Sum RENT_TOTAL"; } XLPivotTableFFVVServicios.SubtotalLocation(XLExcel.XlSubtototalLocationType.xlAtBottom); XLPivotTableFFVVServicios.DataPivotField.Orientation = XLExcel.XlPivotFieldOrientation.xlRowField; XLPivotTableFFVVServicios.DataPivotField.Position = 2; XLPivotTableFFVVServicios.TableStyle2 = "PivotStyleLight18"; XLPivotTableFFVVServicios.RowAxisLayout(XLExcel.XlLayoutRowType.xlCompactRow); #endregion xlApplication.Visible = true; releaseObject(xlWorksheetData); releaseObject(xlWorksheetPivot); if (MostrarRebate) { releaseObject(xlWorksheetPivotRebate); } releaseObject(xlWorksheetComparacion); releaseObject(xlWorkbook); releaseObject(xlApplication); } catch (Exception ex) { releaseObject(xlWorksheetData); releaseObject(xlWorksheetPivot); if (MostrarRebate) { releaseObject(xlWorksheetPivotRebate); } releaseObject(xlWorksheetComparacion); releaseObject(xlWorkbook); releaseObject(xlApplication); throw ex; } }
/// <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(); } }
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"); } } }
//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); } }
/// <summary> /// Creates all the pivot tables, slicers, and row fields, filters and Description, based on values from /// their hashTables. /// </summary> /// <param name="inputCSVFile"></param> /// <param name="PivotOutputReportFullPath"></param> /// <param name="newSheetName"></param> /// <param name="inputDataSheetName"></param> /// <param name="PivottableName"></param> /// <param name="htRowPivotFields"></param> /// <param name="htPgaefilterFields"></param> /// <param name="htSlicers"></param> /// <param name="pivotCountField"></param> /// <param name="component"></param> /// <param name="numberOfFilesCount"></param> /// <param name="otherNodes"></param> public static void GeneratePivotAndSlicersView(string inputCSVFile, string PivotOutputReportFullPath, ref string newSheetName, string inputDataSheetName, string PivottableName, Hashtable htRowPivotFields, Hashtable htPgaefilterFields, Hashtable htSlicers, string pivotCountField, XmlNode component, int numberOfFilesCount, List <XmlNode> otherNodes) { Excel.Application oApp; Excel.Worksheet oSheet; Excel.Workbook oBook = null; Excel.Worksheet oSheet1 = null; Excel.Worksheet oSheet2 = null; oApp = new Excel.Application(); var workbooks = oApp.Workbooks; string sheetName = newSheetName; XmlNode style = otherNodes.Find(item => item.Name == "Style"); Excel.Range rangeToChange = null; string exceptionComment = "Processing for CSV :" + inputCSVFile; Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Started for (" + inputCSVFile + ")"), false); Excel.Workbook oDiscoveryViewook = null; try { oBook = oApp.Workbooks.Open(inputCSVFile); //Excel.Workbook oDiscoveryViewook = null; if (System.IO.File.Exists(PivotOutputReportFullPath)) { oDiscoveryViewook = workbooks.Open(PivotOutputReportFullPath); // create multiple sheets if (oApp.Application.Application.Sheets.Count >= 1) { oSheet2 = oDiscoveryViewook.Worksheets.OfType <Excel.Worksheet>().FirstOrDefault(ws => ws.Name == sheetName); oSheet1 = (Excel.Worksheet)oDiscoveryViewook.Worksheets.Add(After: oDiscoveryViewook.Sheets[oDiscoveryViewook.Sheets.Count]); if (oSheet2 == null) { oSheet1.Name = newSheetName; } else { if (newSheetName.Length >= Constants.SheetNameMaxLength) { newSheetName = newSheetName.Remove(newSheetName.Length - 3); } newSheetName = newSheetName + "_" + numberOfFilesCount; oSheet1.Name = newSheetName; } } else { oSheet1 = oApp.Worksheets[2]; } } if (inputDataSheetName.Length >= Constants.SheetNameMaxLength) { oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(1); } else { oSheet = (Excel.Worksheet)oBook.Sheets.get_Item(inputDataSheetName); } // now capture range of the first sheet Excel.Range oRange = oSheet.UsedRange; // specify first cell for pivot table Excel.Range oRange2 = (Excel.Range)oSheet1.Cells[3, 1]; //Create Pivot Cache if (oRange.Rows.Count > 1) { PivotCache oPivotCache = oDiscoveryViewook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, oRange, XlPivotTableVersionList.xlPivotTableVersion14); PivotTable oPivotTable = oPivotCache.CreatePivotTable(TableDestination: oRange2, TableName: PivottableName); //Creating row pivot fields foreach (DictionaryEntry rowField in htRowPivotFields) { string rowFieldContent = rowField.Value.ToString(); string rowFieldValue = rowFieldContent.Substring(0, rowFieldContent.IndexOf("~")); string rowFieldLabel = rowFieldContent.Substring(rowFieldContent.IndexOf("~") + 1); if ((Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue)) != null) { Excel.PivotField oPivotFieldPivotFieldName = (Excel.PivotField)oPivotTable.PivotFields(Convert.ToString(rowFieldValue)); oPivotFieldPivotFieldName.Orientation = Excel.XlPivotFieldOrientation.xlRowField; oPivotTable.CompactLayoutRowHeader = rowFieldLabel; } else { Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Error: No data available"), true); } } //page filters foreach (DictionaryEntry rowPgaeField in htPgaefilterFields) { Excel.PivotField scPageFilterFiled = oPivotTable.PivotFields(Convert.ToString(rowPgaeField.Value)); scPageFilterFiled.Orientation = Excel.XlPivotFieldOrientation.xlPageField; } //Count Field Excel.PivotField oPivotField2 = (Excel.PivotField)oPivotTable.PivotFields(pivotCountField); oPivotTable.AddDataField(oPivotField2, "Count of " + pivotCountField + "", Excel.XlConsolidationFunction.xlCount); rangeToChange = oPivotTable.TableRange2; oSheet1.Activate(); rangeToChange.Select(); //excelApp.Selection.Font; oSheet1.Application.Selection.Font.Name = CommonHelper.CheckAttributes("FontFamily", null, style); oSheet1.Application.Selection.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", null, style)); //Create Slicer Cache Object int Slicerpos = 0, slicersCount = 0; foreach (DictionaryEntry rowSlicer in htSlicers) { slicersCount++; string rowSlicerValue = rowSlicer.Value.ToString(); if (rowSlicerValue.Contains("~")) { string sliceName = rowSlicerValue.Substring(0, rowSlicerValue.IndexOf("~")); string sliceStyle = rowSlicerValue.Substring(rowSlicerValue.IndexOf("~") + 1); Excel.SlicerCache oSlicerCache = (Excel.SlicerCache)oDiscoveryViewook.SlicerCaches.Add2(oPivotTable, sliceName); Excel.Slicer oSlicer = (Excel.Slicer)oSlicerCache.Slicers.Add(oSheet1, Type.Missing, sliceName + "_" + newSheetName, sliceName, Top: 30, Left: 400 + Slicerpos, Width: 144, Height: 200); oSlicer.Style = sliceStyle; //To Move Left Position of next slicers(2,3...) Slicerpos += 190; } } } Range Line = (Range)oSheet1.Rows[1]; Line.Insert(); XmlNode descriptionTitle = component.SelectSingleNode("DescriptionTitle"); XmlNode description = component.SelectSingleNode("Description"); //Get the range of sheet to fill count XmlNode styleOfDescription = style.SelectSingleNode("ComponentStyle").SelectSingleNode("Description"); Excel.Range descriptionRange = oSheet1.get_Range("B1", "O1"); descriptionRange.Merge(); string descText = description.InnerText.Trim(); descriptionRange.Value = descText.Replace("\r", "").Replace("\n", ""); descriptionRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; descriptionRange.Columns.AutoFit(); descriptionRange.RowHeight = Convert.ToDouble(CommonHelper.CheckAttributes("RowHeight", styleOfDescription, style)); descriptionRange.WrapText = true; descriptionRange.Interior.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescription, style)); descriptionRange.Font.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescription, style)); descriptionRange.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescription, style)); descriptionRange.Font.Name = CommonHelper.CheckAttributes("FontFamily", styleOfDescription, style); descriptionRange.Borders.Color = XlRgbColor.rgbSlateGray; //Get the range of sheet to fill count XmlNode styleOfDescTitle = style.SelectSingleNode("ComponentStyle").SelectSingleNode("DescriptionTitle"); Excel.Range descriptionTitleRange = oSheet1.get_Range("A1", "A1"); descriptionTitleRange.Value = descriptionTitle.InnerText.Trim(); //(styleOfDescription.Attributes["FontFamily"] == null || (styleOfDescription.Attributes["FontFamily"].InnerText == null) ? fontFamily : styleOfDescription.Attributes["FontFamily"].InnerText; descriptionTitleRange.ColumnWidth = Convert.ToDouble(CommonHelper.CheckAttributes("ColumnWidth", styleOfDescTitle, style)); descriptionTitleRange.Interior.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("BgColor", styleOfDescTitle, style)); descriptionTitleRange.Font.Color = CommonHelper.GetColor(CommonHelper.CheckAttributes("FontColor", styleOfDescTitle, style)); descriptionTitleRange.Borders.Color = XlRgbColor.rgbSlateGray; descriptionTitleRange.Font.Size = Convert.ToDouble(CommonHelper.CheckAttributes("FontSize", styleOfDescTitle, style)); descriptionTitleRange.Font.Name = CommonHelper.CheckAttributes("FontFamily", styleOfDescTitle, style); descriptionTitleRange.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop; descriptionTitleRange.Font.Bold = true; //descriptionTitleRange.Columns.AutoFit(); oDiscoveryViewook.Save(); oDiscoveryViewook.Close(); object misValue = System.Reflection.Missing.Value; oBook.Close(false, misValue, misValue); oApp.Quit(); oApp.Application.Quit(); Marshal.ReleaseComObject(oBook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(oDiscoveryViewook); Logger.LogInfoMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView] Processing Completed for (" + inputCSVFile + ") and sheet " + newSheetName + " is created in Pivot Output file: " + PivotOutputReportFullPath), true); } catch (Exception ex) { if (oDiscoveryViewook != null) { oDiscoveryViewook.Save(); oDiscoveryViewook.Close(); } object misValue = System.Reflection.Missing.Value; if (oBook != null) { oBook.Close(false, misValue, misValue); } if (oApp != null) { oApp.Quit(); oApp.Application.Quit(); } Marshal.ReleaseComObject(oBook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(oDiscoveryViewook); Logger.LogErrorMessage(string.Format("[GeneratePivotReports][GeneratePivotAndSlicersView][Exception]: " + ex.Message + ", " + exceptionComment), true); ExceptionCsv.WriteException(Constants.NotApplicable, Constants.NotApplicable, Constants.NotApplicable, "Pivot", ex.Message, ex.ToString(), "[GeneratePivotReports]: GeneratePivotAndSlicersView", ex.GetType().ToString(), exceptionComment); } finally { Marshal.ReleaseComObject(oApp); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
public void CreatePivot(string sheetName, int colCount, int rowCount, string pivotTableLocation, string pivotTableName, string rowField, string rowHeader, string columnField, string columnHeader) { #region Initialization Excel.Workbook activeWorkBook = null; Excel.Worksheet pivotWorkSheet = null; Excel.PivotCaches pivotCaches = null; Excel.PivotCache pivotCache = null; Excel.PivotTable pivotTable = null; Excel.PivotFields pivotFields = null; Excel.PivotField rowPivotField = null; Excel.PivotField columnPivotField = null; Excel.PivotField countPivotField = null; Excel.PivotItems pivotItems = null; Excel.Range pivotRange = null; Excel.PivotField hiddenPivotField = null; #endregion try { activeWorkBook = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveWorkbook; pivotWorkSheet = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; // Create the Pivot Table pivotCaches = activeWorkBook.PivotCaches(); activeWorkBook.ShowPivotTableFieldList = false; string rangeName = "'" + sheetName + "'!$A$4:$" + ExcelColumnLetter(colCount - 1) + "$" + (rowCount + 4).ToString(); pivotCache = pivotCaches.Create(Excel.XlPivotTableSourceType.xlDatabase, rangeName); pivotTable = pivotCache.CreatePivotTable(pivotTableLocation, pivotTableName); pivotTable.NullString = "0"; pivotTable.HasAutoFormat = false; // Set the Pivot Fields pivotFields = (Excel.PivotFields)pivotTable.PivotFields(); // Row Pivot Field rowPivotField = (Excel.PivotField)pivotFields.Item(rowField); rowPivotField.Orientation = Excel.XlPivotFieldOrientation.xlRowField; rowPivotField.Position = 1; pivotTable.CompactLayoutRowHeader = rowHeader; // Column Pivot Field columnPivotField = (Excel.PivotField)pivotFields.Item(columnField); // Causes error and not needed // columnPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField; // Count Field countPivotField = pivotTable.AddDataField(columnPivotField, columnHeader, Excel.XlConsolidationFunction.xlCount); pivotTable.PivotFields(rowField).AutoSort(Excel.XlSortOrder.xlDescending, columnHeader); // Show only Top 10 results for the pivot table int top10 = 10; // pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, // DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add2(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } else { pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotFilters.Add(Type: Excel.XlPivotFilterType.xlTopCount, DataField: pivotWorkSheet.PivotTables(pivotTableName).PivotFields(columnHeader), Value1: top10); } string cellValue = ""; pivotRange = pivotTable.RowRange; int itemCount = pivotRange.Count - 2; // Minus 2 because of title and total rows if (itemCount > top10) { pivotItems = pivotWorkSheet.PivotTables(pivotTableName).PivotFields(rowField).PivotItems; for (int i = itemCount - 1; i >= top10; i--) { cellValue = (string)(pivotWorkSheet.Cells[pivotRange.Row + i + 1, pivotRange.Column] as Excel.Range).Value.ToString(); pivotItems.Item(cellValue).Visible = false; } } if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUser = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroup = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoint = (string)(pivotWorkSheet.Cells[pivotRange.Row + 1, pivotRange.Column] as Excel.Range).Value.ToString(); } string reportTable = ""; string reportLabel = "\""; string reportValue = ""; reportTable = "<table id=\"newspaper-a\" class=\"sortable\">"; string head1 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column] as Excel.Range).Value.ToString(); string head2 = (string)(pivotWorkSheet.Cells[pivotRange.Row, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<thead><tr><th scope=\"col\">" + head1 + "</th><th style=\"text-align:right;\" scope=\"col\" nowrap>" + head2 + "</th></tr></thead><tbody>"; string col1 = ""; string col2 = ""; string labelShorted = ""; int tableItems = itemCount > 10 ? 10 : itemCount; for (int i = 1; i <= tableItems; i++) { col1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column] as Excel.Range).Value.ToString(); col2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + i, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "<tr><td>" + col1 + "</td><td style=\"text-align:right;\">" + col2 + "</td></tr>"; if (tableItems > 5 && col1.Length > 10) { labelShorted = col1.Substring(0, 10) + ".."; } else if (col1.Length > 20) { labelShorted = col1.Substring(0, 20) + ".."; } else { labelShorted = col1; } reportLabel = reportLabel + labelShorted + "\",\""; reportValue = reportValue + col2 + ","; } string foot1 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column] as Excel.Range).Value.ToString(); string foot2 = (string)(pivotWorkSheet.Cells[pivotRange.Row + tableItems + 1, pivotRange.Column + 1] as Excel.Range).Value.ToString(); reportTable = reportTable + "</tbody><tfoot><tr><td>" + foot1 + "</td><td style=\"text-align:right;\">" + foot2 + "</td></tr></tfoot></table>"; reportValue = reportValue.TrimEnd(','); reportLabel = reportLabel.TrimEnd('\"'); reportLabel = reportLabel.TrimEnd(','); if (pivotTableName == "PivotTableClassifier") { Globals.DetectionEngine = reportTable; Globals.DetectionEnginesLabel = reportLabel; Globals.DetectionEnginesValue = reportValue; } else if (pivotTableName == "PivotTableFileDisplayName") { Globals.InfectedFiles = reportTable; Globals.InfectedFilesLabel = reportLabel; Globals.InfectedFilesValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskGroups") { Globals.MostAtRiskGroups = reportTable; Globals.MostAtRiskGroupsLabel = reportLabel; Globals.MostAtRiskGroupsValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskUsers") { Globals.MostAtRiskUsers = reportTable; Globals.MostAtRiskUsersLabel = reportLabel; Globals.MostAtRiskUsersValue = reportValue; } else if (pivotTableName == "PivotTableAtRiskEndpoints") { Globals.MostAtRiskEndpoints = reportTable; Globals.MostAtRiskEndpointsLabel = reportLabel; Globals.MostAtRiskEndpointsValue = reportValue; } else if (pivotTableName == "PivotTableIsActive") { Globals.NetworkStatus = reportTable; Globals.NetworkStatusLabel = reportLabel; Globals.NetworkStatusValue = reportValue; } else if (pivotTableName == "PivotTableOs") { Globals.EndpointOS = reportTable; Globals.EndpointOSLabel = reportLabel; Globals.EndpointOSValue = reportValue; } else if (pivotTableName == "PivotTableAgent") { Globals.EndpointVersion = reportTable; Globals.EndpointVersionLabel = reportLabel; Globals.EndpointVersionValue = reportValue; } else if (pivotTableName == "PivotTableApplicationName") { Globals.TopApplications = reportTable; Globals.TopApplicationsLabel = reportLabel; Globals.TopApplicationsValue = reportValue; } Excel.Range colToFormat = pivotWorkSheet.get_Range("K:Q", System.Type.Missing); colToFormat.EntireColumn.AutoFit(); Excel.Range colToEdit = pivotWorkSheet.get_Range("K:K", System.Type.Missing); // Was 28, changed 11/9/2017 if (pivotTableName == "PivotTableApplicationName") { colToEdit.EntireColumn.ColumnWidth = 70; } else if (colToEdit.EntireColumn.ColumnWidth > 35) { colToEdit.EntireColumn.ColumnWidth = 35; } // Customizing the pivot table style pivotWorkSheet.PivotTables(pivotTableName).TableStyle2 = "PivotStyleMedium9"; // Remembers the bottom of the pivot table so that the next one will not overlap Globals.PivotBottom = pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Row + pivotTable.TableRange2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count; Excel.Range rng = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A" + Globals.PivotBottom.ToString(), "A" + Globals.PivotBottom.ToString()); // Globals.ChartBottom = (int)rng.Top + (int)rng.Height; } catch (Exception ex) { (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Cells[3, 3] = ex.Message; /* * Excel.PivotTable pt = pivotWorkSheet.PivotTables(pivotTableName); * Excel.Range pr = pt.TableRange2; * pr.Clear(); */ } finally { #region Finally if (countPivotField != null) { Marshal.ReleaseComObject(countPivotField); } if (columnPivotField != null) { Marshal.ReleaseComObject(columnPivotField); } if (rowPivotField != null) { Marshal.ReleaseComObject(rowPivotField); } if (pivotFields != null) { Marshal.ReleaseComObject(pivotFields); } if (pivotTable != null) { Marshal.ReleaseComObject(pivotTable); } if (pivotCache != null) { Marshal.ReleaseComObject(pivotCache); } if (pivotCaches != null) { Marshal.ReleaseComObject(pivotCaches); } if (activeWorkBook != null) { Marshal.ReleaseComObject(activeWorkBook); } if (pivotWorkSheet != null) { Marshal.ReleaseComObject(pivotWorkSheet); } #endregion } }
public 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); }