public static void CreatePivot(string tableSource, int[] pageFields, int[] rowFields, int[] dataFields, string pivotTableName = "Pivot Table", string[] slicerColumns = null)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
worksheet = workbook.Worksheets.Add(After: workbook.ActiveSheet);
worksheet.Name = "Pivot";
ListObject table = GetTable(tableSource);
Range rng = table.Range;
worksheet.PivotTableWizard(
XlPivotTableSourceType.xlDatabase,
rng,
worksheet.Range["A1"],
pivotTableName
);
PivotTable pivot = (PivotTable)worksheet.PivotTables(pivotTableName);
//pivot.HasAutoFormat = true;
pivot.ColumnGrand = true;
pivot.RowGrand = true;
for (int i = 0; i < pageFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(pageFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlPageField;
field1.Position = i + 1;
field1.CurrentPage = "(All)";
}
for (int i = 0; i < rowFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(rowFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlRowField;
field1.Position = i + 1;
}
PivotField columnField = pivot.PivotFields();
columnField.Orientation = XlPivotFieldOrientation.xlColumnField;
columnField.Position = 1;
for (int i = 0; i < dataFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(dataFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlDataField;
field1.Position = 1 + i;
field1.Function = XlConsolidationFunction.xlSum;
}
//Add Slicers
SlicerCaches caches = workbook.SlicerCaches;
int counter = 1;
if (slicerColumns != null)
{
foreach (string s in slicerColumns)
{
SlicerCache cache = caches.Add(pivot, s, s);
Slicers slicers = cache.Slicers;
Slicer slicer = slicers.Add(worksheet, Type.Missing, s, s, 160 * counter, 10, 144, 200);
counter++;
}
}
}
private void CreateMainPivotTable(Worksheet inSheet, Worksheet outSheet)
{
Workbook workbook = ThisAddIn.app.ActiveWorkbook;
ColumnInfo durationColumn = this.GetDurationColumnInfo();
ColumnInfo eventTypeColumn = this.GetEventTypeColumnInfo();
string A = durationColumn.columnId;
inSheet.Range[A + ":" + A].NumberFormat = "mm:ss";
// Create Pivot Cache from Source Data
PivotCache pvtCache = workbook.PivotCaches().Create(SourceType: XlPivotTableSourceType.xlDatabase,
//SourceData: srcData);
SourceData: inSheet.UsedRange);
// Create Pivot table from Pivot Cache
//PivotTable pvt = pvtCache.CreatePivotTable(TableDestination: startPvt, TableName: "PivotTable2");
PivotTable pvt = pvtCache.CreatePivotTable(TableDestination: outSheet.Range["A3"], TableName: "PivotTable2");
string durationField = durationColumn.name;
string eventTypeField = eventTypeColumn.name;
pvt.AddDataField(pvt.PivotFields(eventTypeField), "Count of " + eventTypeField, XlConsolidationFunction.xlCount);
pvt.AddDataField(pvt.PivotFields(eventTypeField), "Percent of " + eventTypeField, XlConsolidationFunction.xlCount);
pvt.PivotFields("Percent of " + eventTypeField).Calculation = XlPivotFieldCalculation.xlPercentOfColumn;
pvt.AddDataField(pvt.PivotFields(durationField), "Average Duration of " + eventTypeField, XlConsolidationFunction.xlAverage);
pvt.AddDataField(pvt.PivotFields(durationField), "Max Duration of " + eventTypeField, XlConsolidationFunction.xlMax);
pvt.AddDataField(pvt.PivotFields(durationField), "Standard Deviation of " + eventTypeField, XlConsolidationFunction.xlStDev);
pvt.PivotFields("Average Duration of " + eventTypeField).NumberFormat = "mm:ss";
pvt.PivotFields("Max Duration of " + eventTypeField).NumberFormat = "mm:ss";
pvt.PivotFields("Standard Deviation of " + eventTypeField).NumberFormat = "mm:ss";
pvt.PivotFields(eventTypeField).Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField;
}
/// <summary>
/// Crear una tabla pivot.
/// <param name="hoja_tabla">Número de la hoja a la que se agregará la tabla pivot</param>
/// <param name="nombre_campo_fila">Nombre del campo de fila</param>
/// <param name="nombre_campo_datos">Nombre del campo de datos</param>
/// </summary>
public override void crearTablaPivot(string hoja_tabla, string nombre_campo_fila, string nombre_campo_datos)
{
XlPivotTableSourceType fuente = XlPivotTableSourceType.xlDatabase;
XlPivotFieldOrientation orientacion_fila = XlPivotFieldOrientation.xlRowField;
XlPivotFieldOrientation orientacion_datos = XlPivotFieldOrientation.xlDataField;
// Crear la tabla pivot
_hoja = (Worksheet)_libro.Worksheets[hoja_tabla];
_hoja.PivotTableWizard(fuente, _seleccionado, _missing, "PivTab1");
PivotTable tabla = (PivotTable)_hoja.PivotTables("PivTab1");
PivotField campo_fila = ((PivotField)tabla.PivotFields(nombre_campo_fila));
PivotField campo_datos = ((PivotField)tabla.PivotFields(nombre_campo_datos));
campo_fila.Orientation = orientacion_fila;
campo_datos.Orientation = orientacion_datos;
}
public void InsertPivotTable(PivotTableData data)
{
Range dataRange = GetRange(data.Range);
PivotCache pivotCache = _workbook.WrappedWorkbook.PivotCaches().Add(
XlPivotTableSourceType.xlDatabase, dataRange);
Range insertRange = GetCell(data.InsertCell.Start.Row, data.InsertCell.Start.Column);
PivotTables sheetPivotTables = _sheet.PivotTables(Type.Missing);
PivotTable pivotTable = sheetPivotTables.Add(
pivotCache, insertRange, data.Name,
Type.Missing, Type.Missing);
pivotTable.TableStyle = data.StyleName;
foreach (string column in data.Columns)
{
PivotField pageField = (PivotField)pivotTable.PivotFields(column);
pageField.Orientation = XlPivotFieldOrientation.xlColumnField;
}
foreach (string row in data.Rows)
{
PivotField rowField = (PivotField)pivotTable.PivotFields(row);
rowField.Orientation = XlPivotFieldOrientation.xlRowField;
}
foreach (string value in data.Values)
{
PivotField valueField = (PivotField)pivotTable.PivotFields(value);
valueField.Orientation = XlPivotFieldOrientation.xlDataField;
}
pivotTable.GrandTotalName = "Suma";
pivotTable.SmallGrid = true;
}
public override void RunCommand(object sender)
{
var engine = (IAutomationEngineInstance)sender;
string vSheetExcelTable = v_SheetNameExcelTable.ConvertUserVariableToString(engine);
string vSheetPivotTable = v_SheetNamePivotTable.ConvertUserVariableToString(engine);
var vTableName = v_TableName.ConvertUserVariableToString(engine);
var vCellLocation = v_CellLocation.ConvertUserVariableToString(engine);
var vPivotTable = v_PivotTable.ConvertUserVariableToString(engine);
var excelObject = v_InstanceName.GetAppInstance(engine);
var excelInstance = (Application)excelObject;
var workBook = excelInstance.ActiveWorkbook;
var workSheetExcelTable = excelInstance.Sheets[vSheetExcelTable] as Worksheet;
var workSheetPivotTable = excelInstance.Sheets[vSheetPivotTable] as Worksheet;
var excelTable = workSheetExcelTable.ListObjects[vTableName];
object useDefault = Type.Missing;
Range pivotDestination = workSheetPivotTable.Range[vCellLocation, useDefault];
var pivotCache = workBook.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, vTableName, Type.Missing);
PivotTable pivotTable = pivotCache.CreatePivotTable(pivotDestination, vPivotTable, Type.Missing, Type.Missing);
PivotField pivotField;
for (int i = 1; i <= excelTable.ListColumns.Count; i++)
{
pivotField = pivotTable.PivotFields(i);
if (XlPivotFieldDataType.xlText != pivotField.DataType)
{
pivotTable.AddDataField(pivotTable.PivotFields(i), Type.Missing, Type.Missing);
}
else
{
pivotTable.PivotFields(i).Orientation = XlPivotFieldOrientation.xlRowField;
}
}
pivotTable.RefreshTable();
}
public void PivotTable_CollapseSections(string pivotTableName, string fieldName)
{
PivotTable pT = default(PivotTable);
PivotField pF = default(PivotField);
//Dim pI As PivotItem
pT = Globals.ThisAddIn.Application.ActiveSheet.PivotTables(pivotTableName);
// Not sure what the difference is between these two. Both seem to work.
pF = pT.PivotFields(fieldName);
// Set pF = pT.RowFields(fieldName)
// Collapse each of the sections
// TODO: Figure out why this throws exception?? Works in VBA??
//For Each pI In pF.PivotItems
// pI.ShowDetail = False
//Next
}
void AddPivotSummary(WorkItemActionRequest request)
{
long startTicks = Log.EVENT_HANDLER("Enter", Common.PROJECT_NAME);
Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet;
Microsoft.Office.Interop.Excel.Range activeCell = Globals.ThisAddIn.Application.ActiveCell;
Options_AZDO_TFS options = new Options_AZDO_TFS();
int workItemID = int.Parse(request.WorkItemID);
XlHlp.XlLocation insertAt = CreateNewWorksheet(string.Format("P_WI_{0}", workItemID), GetOptions());
// TODO(crhodes)
// Figure out how to get the table name from the active cell.
var tableName = activeCell.ListObject.Name;
PivotCache pc = wb.PivotCaches().Create(
SourceType: XlPivotTableSourceType.xlDatabase,
SourceData: tableName,
Version: 6);
string insertRange = $"{insertAt.workSheet.Name}!R{options.StartingRow}C{options.StartingColumn}";
PivotTable pt = pc.CreatePivotTable(TableDestination: insertRange);
// this is from the macro recording. Not all may be needed or desired.
pt.ColumnGrand = true;
pt.HasAutoFormat = true;
pt.DisplayErrorString = false;
pt.DisplayNullString = true;
pt.EnableDrilldown = true;
pt.ErrorString = "";
pt.MergeLabels = false;
pt.NullString = "";
pt.PageFieldOrder = 2;
pt.PageFieldWrapCount = 0;
pt.PreserveFormatting = true;
pt.RowGrand = true;
pt.SaveData = true;
pt.PrintTitles = false;
pt.RepeatItemsOnEachPrintedPage = true;
pt.TotalsAnnotation = false;
pt.CompactRowIndent = 1;
pt.InGridDropZones = false;
pt.DisplayFieldCaptions = true;
pt.DisplayMemberPropertyTooltips = false;
pt.DisplayContextTooltips = true;
pt.ShowDrillIndicators = true;
pt.PrintDrillIndicators = false;
pt.AllowMultipleFilters = false;
pt.SortUsingCustomLists = true;
pt.FieldListSortAscending = false;
pt.ShowValuesRow = false;
pt.CalculatedMembersInFilters = false;
pt.RowAxisLayout(XlLayoutRowType.xlCompactRow);
pt.PivotCache().RefreshOnFileOpen = false;
pt.PivotCache().MissingItemsLimit = XlPivotTableMissingItems.xlMissingItemsDefault;
PivotField pf1 = pt.PivotFields("Source.Type");
PivotField pf2 = pt.PivotFields("Target.Type");
pf1.Orientation = XlPivotFieldOrientation.xlRowField;
pf1.Position = 1;
pf2.Orientation = XlPivotFieldOrientation.xlRowField;
pf2.Position = 2;
//pt.AddDataField(pf1, "Count", XlConsolidationFunction.xlCount);
//pf2.Orientation = XlPivotFieldOrientation.xlRowField;
//pf2.Position = 2;
//With ActiveSheet.PivotTables("PivotTable1").PivotFields("Target.Type")
// .Orientation = xlRowField
// .Position = 1
//End With
//ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_
// "PivotTable1").PivotFields("Target.Type"), "Count of Target.Type", xlCount
//With ActiveSheet.PivotTables("PivotTable1").PivotFields("SourceId")
// .Orientation = xlRowField
// .Position = 2
//End With
insertAt.workSheet.Select();
Log.EVENT_HANDLER("Exit", Common.PROJECT_NAME, startTicks);
}
private 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") + ".xls";
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.Bruttobeløb,
};
var erMedlem = from h in Program.dbData3060.tblMedlems
where h.Status == 1
select h;
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);
}
int row = 1;
this.MainformProgressBar.Value = 0;
this.MainformProgressBar.Minimum = 0;
this.MainformProgressBar.Maximum = (from h in Program.karPosteringer select h).Count();
this.MainformProgressBar.Step = 1;
this.MainformProgressBar.Visible = true;
foreach (clsJournalposter m in JournalPoster)
{
this.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() == "nsPuls3060.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);
oRng = oSheetRegnskab.get_Range("C2", Missing.Value);
oRng.Formula = "Antal medlemmer: " + erMedlem.Count().ToString();
//oXL.Visible = true; //For debug
PivotField _pvtField = null;
PivotTable _pivot = oSheetPoster.PivotTableWizard(
XlPivotTableSourceType.xlDatabase, //SourceType
oSheetPoster.get_Range(oSheetPoster.Cells[1, 1], oSheetPoster.Cells[row, 9]), //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 = (PivotField)_pivot.PivotFields("ds");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("k");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Konto");
_pvtField.Orientation = XlPivotFieldOrientation.xlRowField;
_pvtField = (PivotField)_pivot.PivotFields("Dato");
_pvtField.Orientation = XlPivotFieldOrientation.xlColumnField;
_pvtField = (PivotField)_pivot.PivotFields("Beløb");
_pvtField.Orientation = XlPivotFieldOrientation.xlDataField;
_pvtField.Function = 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, 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 = XlPageOrientation.xlLandscape;
oSheetRegnskab.PageSetup.Draft = false;
oSheetRegnskab.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
oSheetRegnskab.PageSetup.FirstPageNumber = 1;
oSheetRegnskab.PageSetup.Order = XlOrder.xlDownThenOver;
oSheetRegnskab.PageSetup.BlackAndWhite = false;
oSheetRegnskab.PageSetup.Zoom = 100;
oSheetRegnskab.PageSetup.PrintErrors = 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.xlWorkbookNormal, "", "", 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;
this.emailExcelFile(SaveAs, "Puls3060 Regnskab");
//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");
}
}
}
public override string Export(string json)
{
string fileName = "";
try
{
if (string.IsNullOrEmpty(json))
{
throw new KMJXCException("没有销售数据，不能导出");
}
JArray jObject = JArray.Parse(json);
if (jObject == null || jObject.Count() == 0)
{
throw new KMJXCException("没有销售数据，不能导出");
}
Worksheet sheet = (Worksheet)this.WorkBook.ActiveSheet;
sheet.Name = "销售报表";
int startRow = 2;
object[,] os = new object[jObject.Count, 6];
for (int i = 0; i < jObject.Count(); i++)
{
JObject obj = (JObject)jObject[i];
string productName = obj["ProductName"].ToString();
string propName = obj["PropName"].ToString();
string shopName = obj["ShopName"].ToString();
string month = obj["Month"].ToString();
string quantity = obj["Quantity"].ToString();
string amount = obj["Amount"].ToString();
os[i, 0] = productName;
os[i, 1] = propName;
os[i, 2] = shopName;
os[i, 3] = month;
os[i, 4] = quantity;
os[i, 5] = amount;
}
Range range1 = sheet.Cells[startRow, 1];
Range range2 = sheet.Cells[startRow + jObject.Count - 1, 6];
Range range = sheet.get_Range(range1, range2);
range.Value2 = os;
Worksheet pivotTableSheet = (Worksheet)this.WorkBook.Worksheets[2];
pivotTableSheet.Name = "销售透视表";
PivotCaches pch = WorkBook.PivotCaches();
sheet.Activate();
pch.Add(XlPivotTableSourceType.xlDatabase, "'" + sheet.Name + "'!A1:'" + sheet.Name + "'!F" + (jObject.Count() + 1)).CreatePivotTable(pivotTableSheet.Cells[4, 1], "PivTbl_1", Type.Missing, Type.Missing);
PivotTable pvt = pivotTableSheet.PivotTables("PivTbl_1") as PivotTable;
pvt.Format(XlPivotFormatType.xlTable1);
pvt.TableStyle2 = "PivotStyleLight16";
pvt.InGridDropZones = true;
foreach (PivotField pf in pvt.PivotFields() as PivotFields)
{
pf.ShowDetail = false;
}
PivotField productField = (PivotField)pvt.PivotFields("产品");
productField.Orientation = XlPivotFieldOrientation.xlRowField;
productField.set_Subtotals(1, false);
PivotField propField = (PivotField)pvt.PivotFields("属性");
propField.Orientation = XlPivotFieldOrientation.xlRowField;
propField.set_Subtotals(1, false);
PivotField shopField = (PivotField)pvt.PivotFields("店铺");
shopField.Orientation = XlPivotFieldOrientation.xlRowField;
shopField.set_Subtotals(1, false);
PivotField monthField = (PivotField)pvt.PivotFields("年月");
monthField.Orientation = XlPivotFieldOrientation.xlRowField;
monthField.set_Subtotals(1, false);
pvt.AddDataField(pvt.PivotFields(5), "销量", XlConsolidationFunction.xlSum);
pvt.AddDataField(pvt.PivotFields(6), "销售额", XlConsolidationFunction.xlSum);
((PivotField)pvt.DataFields["销量"]).NumberFormat = "#,##0";
((PivotField)pvt.DataFields["销售额"]).NumberFormat = "#,##0";
pivotTableSheet.Activate();
this.WorkBook.Saved = true;
this.WorkBook.SaveCopyAs(this.ReportFilePath);
this.WorkBook.Close();
}
catch (Exception ex)
{
throw ex;
}
return(fileName);
}
private static void AddPivotRemotely(
_Workbook eWorkBook,
Worksheet pivotWorkSheet,
PivotTable pivotTable,
IEnumerable <string> fieldRowNames,
IEnumerable <string> fieldColumnNames,
IEnumerable <string> fieldValueNames,
IEnumerable <string> fieldReportNames,
IEnumerable <string> slicerfieldNames,
string slicerStyle)
{
PivotField rowField;
PivotField dataField;
PivotField colField;
PivotField repField;
pivotTable.InGridDropZones = false;
pivotTable.GrandTotalName = "Total";
pivotTable.DisplayNullString = false;
pivotTable.ShowTableStyleRowHeaders = true;
pivotTable.ShowTableStyleColumnHeaders = true;
string masterFieldName = string.Empty;
string masterColumnName = string.Empty;
int rowCounter = 0;
if (fieldRowNames != null)
{
foreach (var name in fieldRowNames)
{
rowCounter += 1;
rowField = (PivotField)pivotTable.PivotFields(name);
if (masterFieldName.Equals(string.Empty))
{
var pf = (PivotField)pivotTable.PivotFields(name);
masterFieldName = pf.Name;
}
rowField.Orientation = XlPivotFieldOrientation.xlRowField;
}
}
if (fieldColumnNames != null)
{
foreach (var name in fieldColumnNames)
{
colField = (PivotField)pivotTable.PivotFields(name);
colField.Orientation = XlPivotFieldOrientation.xlColumnField;
if (masterColumnName.Equals(string.Empty))
{
var pf = (PivotField)pivotTable.PivotFields(name);
masterColumnName = pf.Name;
}
}
}
var nameOfSum = string.Empty;
if (fieldValueNames != null)
{
foreach (var name in fieldValueNames)
{
dataField = (PivotField)pivotTable.PivotFields(name);
dataField.Orientation = XlPivotFieldOrientation.xlDataField;
dataField.Function = XlConsolidationFunction.xlSum;
dataField.NumberFormat = "#,##0.00";
dataField.Name = $"Total {name}";
if (nameOfSum.Equals(string.Empty))
{
nameOfSum = dataField.Name;
}
}
}
if (!nameOfSum.Equals(string.Empty) && !masterFieldName.Equals(string.Empty))
{
var pf = (PivotField)pivotTable.PivotFields(masterFieldName);
pf.AutoSort((int)XlSortOrder.xlDescending, nameOfSum);
if (rowCounter > 1)
{
var pf1 = (PivotField)pivotTable.PivotFields(masterFieldName);
pf1.ShowDetail = false;
}
pivotTable.CompactLayoutRowHeader = masterFieldName;
}
if (fieldReportNames != null)
{
foreach (var name in fieldReportNames)
{
repField = (PivotField)pivotTable.PivotFields(name);
repField.Orientation = XlPivotFieldOrientation.xlPageField;
}
}
if (slicerfieldNames != null)
{
const int cTop = 300;
const int cWidth = 100;
const int cHeight = 100;
var dLeft = 0;
foreach (var name in slicerfieldNames)
{
var slicerCurrent = eWorkBook.SlicerCaches.Add(pivotTable, name);
var slicer = slicerCurrent.Slicers.Add(
pivotWorkSheet,
Top: cTop,
Left: dLeft,
Width: cWidth * slicerCurrent.SlicerItems.Count,
Height: cHeight);
slicer.NumberOfColumns = slicerCurrent.SlicerItems.Count / 2;
slicer.Style = slicerStyle;
dLeft += cWidth + 20;
}
}
}
static void Main(string[] args)
{
// Declare variables that hold references to excel objects
Application eApplication = null;
Workbook eWorkbook = null;
Worksheet sheet = null;
PivotTable pivotTable = null;
Range pivotData = null;
Range pivotDestination = null;
PivotField salesRegion = null;
PivotField salesAmount = null;
ChartObjects chartObjects = null;
ChartObject pivotChart = null;
SlicerCache salesTypeSlicer = null;
SlicerCache salesRegionSlicer = null;
SlicerCache salesPersonSlicer = null;
// Declare helper variables
string workBookName = @"C:\temp\Excel2010Slicers.xlsx";
string pivotTableName = @"Sales By Type";
string workSheetName = @"Quarterly Sales";
try
{
// Create an instance of Excel
eApplication = new Application();
//Create a workbook and add a worksheet.
eWorkbook = eApplication.Workbooks.Add(
XlWBATemplate.xlWBATWorksheet);
sheet = (Worksheet)(eWorkbook.Worksheets[1]);
sheet.Name = workSheetName;
//Add Data to the worksheet.
//The custom SetRow helper function located at the end of this
// source file is used to add a row of values to the worksheet.
SetRow(sheet, 1,
"Sales Region", "Sales Person", "Sales Type", "Sales Amount");
SetRow(sheet, 2, "West", "Joe", "Wholesale", "123");
SetRow(sheet, 3, "West", "Joe", "Retail", "432");
SetRow(sheet, 4, "West", "Joe", "Government", "111");
SetRow(sheet, 5, "East", "Robert", "Wholesale", "564");
SetRow(sheet, 6, "East", "Robert", "Retail", "234");
SetRow(sheet, 7, "East", "Robert", "Government", "321");
SetRow(sheet, 8, "East", "Michelle", "Wholesale", "940");
SetRow(sheet, 9, "East", "Michelle", "Retail", "892");
SetRow(sheet, 10, "East", "Michelle", "Government", "10");
SetRow(sheet, 11, "West", "Erich", "Wholesale", "120");
SetRow(sheet, 12, "West", "Erich", "Retail", "45");
SetRow(sheet, 13, "West", "Erich", "Government", "410");
SetRow(sheet, 14, "West", "Dafna", "Wholesale", "800");
SetRow(sheet, 15, "West", "Dafna", "Retail", "3409");
SetRow(sheet, 16, "West", "Dafna", "Government", "123");
SetRow(sheet, 17, "East", "Rob", "Wholesale", "777");
SetRow(sheet, 18, "East", "Rob", "Retail", "450");
SetRow(sheet, 19, "East", "Rob", "Government", "900");
sheet.Columns.AutoFit();
// Select a range of data for the Pivot Table.
pivotData = sheet.get_Range("A1", "D19");
// Select location of the Pivot Table.
pivotDestination = sheet.get_Range("F2");
// Add a pivot table to the worksheet.
sheet.PivotTableWizard(
XlPivotTableSourceType.xlDatabase,
pivotData,
pivotDestination,
pivotTableName
);
// Set variables used to manipulate the Pivot Table.
pivotTable =
(PivotTable)sheet.PivotTables(pivotTableName);
salesRegion = ((PivotField)pivotTable.PivotFields(3));
salesAmount = ((PivotField)pivotTable.PivotFields(4));
// Format the Pivot Table.
pivotTable.TableStyle2 = "PivotStyleLight16";
pivotTable.InGridDropZones = false;
// Set Sales Region as a Row Field.
salesRegion.Orientation =
XlPivotFieldOrientation.xlRowField;
// Set Sum of Sales Amount as a Value Field.
salesAmount.Orientation =
XlPivotFieldOrientation.xlDataField;
salesAmount.Function = XlConsolidationFunction.xlSum;
//Add a pivot chart to the work sheet.
chartObjects = (ChartObjects)sheet.ChartObjects();
pivotChart = chartObjects.Add(310, 100, 225, 175);
//Format the pivot chart.
pivotChart.Chart.ChartWizard(pivotData,
XlChartType.xlColumnClustered,
Title: "Sales",
HasLegend: false,
CategoryLabels: 3,
SeriesLabels: 0);
//Add slicers to the pivot table.
salesTypeSlicer =
eWorkbook.SlicerCaches.Add(pivotTable, "Sales Type");
salesTypeSlicer.Slicers.Add(sheet,
Top: 10, Left: 540, Width: 100, Height: 100);
salesRegionSlicer =
eWorkbook.SlicerCaches.Add(pivotTable, "Sales Region");
salesRegionSlicer.Slicers.Add(sheet,
Top: 120, Left: 540, Width: 100, Height: 100);
salesPersonSlicer =
eWorkbook.SlicerCaches.Add(pivotTable, "Sales Person");
salesPersonSlicer.Slicers.Add(sheet,
Top: 10, Left: 645, Width: 100, Height: 200);
// Save the workbook.
sheet.get_Range("A1").Activate();
eWorkbook.SaveAs(workBookName,
AccessMode: XlSaveAsAccessMode.xlNoChange);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
//Release the references to the Excel objects.
salesAmount = null;
salesRegion = null;
pivotDestination = null;
pivotData = null;
pivotChart = null;
chartObjects = null;
pivotTable = null;
salesTypeSlicer = null;
salesRegionSlicer = null;
salesPersonSlicer = null;
sheet = null;
//Release the Workbook object.
if (eWorkbook != null)
{
eWorkbook = null;
}
//Release the ApplicationClass object.
if (eApplication != null)
{
eApplication.Quit();
eApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public static void WriteData()
{
Application xlApp = (Application)ExcelDnaUtil.Application;
Workbook wb = xlApp.ActiveWorkbook;
if (wb == null)
{
return;
}
/* Worksheet ws = wb.Worksheets.Item["Sheet1"];
* ws.Activate();
* ws.Range["A1"].Value = "Date";
* ws.Range["B1"].Value = "Value";
*
* Range headerRow = ws.Range["A1", "B1"];
* headerRow.Font.Size = 12;
* headerRow.Font.Bold = true;
*
* // Generally it's faster to write an array to a range
* var values = new object[100, 2];
* var startDate = new DateTime(2007, 1, 1);
* var rand = new Random();
* for (int i = 0; i < 100; i++)
* {
* values[i, 0] = startDate.AddDays(i);
* values[i, 1] = rand.NextDouble();
* }
*
* ws.Range["A2"].Resize[100, 2].Value = values;
* ws.Columns["A:A"].EntireColumn.AutoFit();
*
* // Add a chart
* Range dataRange= ws.Range["A1:B101"];
* dataRange.Select();
* ws.Shapes.AddChart(XlChartType.xlColumnClustered).Select();
* xlApp.ActiveChart.SetSourceData(Source: dataRange);
*/
string filename = "D:\\OneDrive\\Alan\\Code\\Visual Studio\\Projects\\Ribbon\\bin\\Debug\\ID1541v1-FUZZY-results-extract.csv";
System.Windows.Forms.OpenFileDialog fd = new System.Windows.Forms.OpenFileDialog();
fd.Filter = "CSV Files|*.csv";
fd.InitialDirectory = wb.Path;
if (fd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filename = fd.FileName;
}
xlApp.ScreenUpdating = false;
Worksheet ws = wb.Worksheets.Add(Type: XlSheetType.xlWorksheet);
ws.Name = "data";
var columns = File.ReadLines("D:\\OneDrive\\Alan\\Code\\Visual Studio\\Projects\\Ribbon\\bin\\Debug\\test.txt").First().Split('\t').ToArray();
int cols = columns.Length;
var contents = File.ReadAllLines(filename);
int rows = contents.Length;
var headings = contents.First().Split('\t').ToArray();
var vals = new object[rows, cols + 2];
var colInd = new int[cols];
for (int i = 0; i < cols; i++)
{
colInd[i] = Array.IndexOf(headings, columns[i]);
}
int r = 1;
int depthCol = Array.IndexOf(headings, "Depth");
int nameCol = Array.IndexOf(headings, "Test Name");
for (int j = 0; j < columns.Length; j++)
{
vals[0, j] = headings[colInd[j]];
}
vals[0, cols] = "Len";
vals[0, cols + 1] = "Words";
for (int i = 1; i < contents.Length; i++)
{
var thisLine = contents[i].Split('\t').ToArray();
if (Int32.Parse(thisLine[depthCol]) == 1)
{
for (int j = 0; j < Math.Min(thisLine.Length, cols); j++)
{
vals[r, j] = thisLine[colInd[j]];
}
vals[r, cols] = thisLine[nameCol].Length;
vals[r, cols + 1] = NumOccurances(thisLine[nameCol], " ");
r++;
}
}
Range data = ws.Range["A1"].Resize[r, cols + 2];
data.Value = vals;
Worksheet ws2 = wb.Worksheets.Add(Type: XlSheetType.xlWorksheet);
Range pivotDestination = ws2.Range["A1"];
string pivotTableName = @"mypivot";
wb.PivotTableWizard(XlPivotTableSourceType.xlDatabase, data, pivotDestination, pivotTableName, true, true, true, true,
Type.Missing, Type.Missing, false, false, XlOrder.xlDownThenOver, 0, Type.Missing, Type.Missing);
PivotTable pivotTable = (PivotTable)ws2.PivotTables(pivotTableName);
PivotField shortnamePivotField = (PivotField)pivotTable.PivotFields(2);
//itemcodePivotField = (PivotField)pivotTable.PivotFields(3);
//descriptionPivotField = (PivotField)pivotTable.PivotFields(4);
//pricePivotField = (PivotField)pivotTable.PivotFields(7);
// Format the Pivot Table.
//pivotTable.Format(XlPivotFormatType.xlReport2);
//pivotTable.InGridDropZones = false;
//pivotTable.SmallGrid = false;
//pivotTable.ShowTableStyleRowStripes = true;
//pivotTable.TableStyle2 = "PivotStyleLight1";
// Page Field
shortnamePivotField.Orientation = XlPivotFieldOrientation.xlPageField;
/*shortnamePivotField.Position = 1;
* shortnamePivotField.CurrentPage = "(All)";
*
* // Row Fields
* descriptionPivotField.Orientation = XlPivotFieldOrientation.xlRowField;
* descriptionPivotField.Position = 1;
* itemcodePivotField.Orientation = XlPivotFieldOrientation.xlRowField;
* itemcodePivotField.Position = 2;
*
* // Data Field
* pricePivotField.Orientation = XlPivotFieldOrientation.xlDataField;
* pricePivotField.Function = XlConsolidationFunction.xlSum;
*/
xlApp.ScreenUpdating = true;
}
static void makeGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.Worksheet chartSheet = null;
Excel.Range range = null;
Excel.Range dataR = null;
int rowC = 0;
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "table";
chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
chartSheet.Name = "graph";
Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
dataR = dataSheet.UsedRange;
rowC = dataR.Rows.Count;
range = newSheet.get_Range("A1");
PivotCaches pCs = workbook.PivotCaches();
PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
PivotField fA = pT.PivotFields("Time");
PivotField fB = pT.PivotFields("Command");
fA.Orientation = XlPivotFieldOrientation.xlRowField;
fA.Position = 1;
fB.Orientation = XlPivotFieldOrientation.xlColumnField;
fB.Position = 1;
pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);
ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
Chart chartP = pChart.Chart;
chartP.SetSourceData(pT.TableRange1, Type.Missing);
chartP.ChartType = XlChartType.xlLine;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
using (StreamWriter outfile = File.AppendText("output.txt")) {
outfile.WriteLine("Did have issues interacting with Excel on " + file);
}
} finally {
/*Excel.Application excelApp = null;
* Excel.Workbook workbook = null;
* Excel.Sheets sheets = null;
* Excel.Worksheet dataSheet = null;
* Excel.Worksheet newSheet = null;
* Excel.Worksheet chartSheet = null;
* Excel.Range range = null;
* Excel.Range dataR = null;*/
releaseObject(dataR);
releaseObject(range);
releaseObject(chartSheet);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
}
/// <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();
}
}
