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