using System;
using System.Collections.Generic;
using SE.Core.Automation.Interfaces.Common;
using SE.Core.Automation.Interfaces;
using Microsoft.Office.Interop.Excel;
namespace CodeEditorNameSpace
{
public class CodePluginClass : ICodeEditor
{
public event EventHandler ExecutionCompleted;
public void PerformAction(IApplicationAutomation plugin, Dictionary<string, object> extractedFields)
{
//Add your code here
try
{
string path = @"C:\Users\UX155512\Documents\Book1fd.xlsx";
var excelFile = new Application();
Workbook workBook = excelFile.Workbooks.Open(path);
Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
Worksheet pivotSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.Add(
System.Reflection.Missing.Value,
(Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[workBook.Worksheets.Count],
1,
System.Reflection.Missing.Value);
Range last = workSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
int lastRow = last.Row;
int lastCol = last.Column;
string lastColVal = Column(lastCol);
string lastFilledCol = lastColVal + lastRow.ToString();
string Column(int column)
{
column--;
if (column >= 0 && column < 26)
return ((char)('A' + column)).ToString();
else if (column > 25)
return Column(column / 26) + Column(column % 26 + 1);
else
throw new Exception("Invalid Column #" + (column + 1).ToString());
}
Range lastRange = workSheet.Range["A1", lastFilledCol];
pivotSheet.Name = "Pivot Table";
Range range = (Range)pivotSheet.Cells[1, 1];
PivotCache pivotCache = (PivotCache)workBook.PivotCaches().Add(XlPivotTableSourceType.xlDatabase, lastRange);
//the add portion here is giving an error in the code editor please help with the same
PivotTable pivotTable = (PivotTable)pivotSheet.PivotTables().Add(PivotCache: pivotCache, TableDestination: range);
PivotField pivotField = (PivotField)pivotTable.PivotFields(“Plan Number”);
pivotField.Orientation = XlPivotFieldOrientation.xlRowField;
PivotField pivotField2 = (PivotField)pivotTable.PivotFields("Source");
pivotField2.Orientation = XlPivotFieldOrientation.xlColumnField;
PivotField pivotField3 = (PivotField)pivotTable.PivotFields("Total");
pivotField3.Orientation = XlPivotFieldOrientation.xlDataField;
pivotField3.Function = XlConsolidationFunction.xlSum;
workBook.SaveAs(@"C:\Users\UX155512\Documents\Excel Dump\Trial9.xlsx");
workBook.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
ExecutionCompleted.Invoke(this, null);
}
}
}
