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