public Testing()
{
//DBConnHelper dwHelper = new DBConnHelper(Constants.dwConnString);
//DataSet dwDS = dwHelper.ExecQuery(Constants.stgQuery, 60);
//System.Data.DataTable dwDT = dwDS.Tables[0];
//dwDT.TableName = "FactTable";
//dwDT.ConvertDBNull();
DBConnHelper uatHelper = new DBConnHelper("Constants.uatConnString");
DataSet uatDS = uatHelper.ExecQuery("Constants.uatQuery", 600);
System.Data.DataTable uatDT = uatDS.Tables[0];
uatDT.TableName = "RRRTable";
uatDT.ConvertDBNull();
//System.Data.DataTable unionDT = dwDT.AsEnumerable().Union(uatDT.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();
//unionDT.TableName = "UnionTable";
//System.Data.DataTable expDT = dwDT.AsEnumerable().Except(uatDT.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();
//expDT.TableName = "EXPTable";
ExcelController.PivotTableConfig ptconfi = new ExcelController.PivotTableConfig();
ptconfi.PivotName = "Pivot_Testing";
ptconfi.PivotColumns = new System.Collections.ObjectModel.ObservableCollection<string>();
ptconfi.PivotColumns.Add("Scenario");
ptconfi.PivotRows = new System.Collections.ObjectModel.ObservableCollection<string>();
ptconfi.PivotRows.Add("Desk");
ptconfi.PivotRows.Add("Account");
ptconfi.PivotValues = new System.Collections.ObjectModel.ObservableCollection<string>();
ptconfi.PivotValues.Add("EffectiveDV01");
ptconfi.PivotValues.Add("Convexity");
ptconfi.PivotValues.Add("Duration");
IExcelManager em = new ExcelManager();
em.CreatePivot(uatDT, ptconfi);
}
public void CreatePivot(System.Data.DataTable dataSource, PivotTableConfig config)
{
var sheet = WorkBook.Worksheets.Add(dataSource.TableName);
var source = sheet.Cell(1, 1).InsertTable(dataSource, dataSource.TableName, true);
var range = source.DataRange;
var dataRange = sheet.Range(sheet.Cell(1, 1), range.LastCell());
var ptSheet = WorkBook.Worksheets.Add(PIVOT_PREFIX + dataSource.TableName);
var pt = ptSheet.PivotTables.AddNew(config.PivotName, ptSheet.Cell(1, 1), dataRange);
foreach (var item in config.PivotColumns)
{
pt.ColumnLabels.Add(item);
}
foreach (var item in config.PivotRows)
{
pt.RowLabels.Add(item);
}
foreach (var item in config.PivotValues)
{
pt.Values.Add(item);
}
FilePath = FolderExt.getUniqueXlsxFileName();
WorkBook.SaveAs(FilePath);
openExcel(FilePath, true);
}
public void CreatePivot <T>(IEnumerable <T> source, PivotTableConfig config)
{
throw new NotImplementedException();
}