public static Excel.WorkbookConnection GetWorkbookConnection(Excel.Range rngCell)
{
Excel.PivotTable pt = null;
Excel.PivotCache cache = null;
Excel.WorkbookConnection wbcnn = null;
pt = rngCell.PivotTable;
cache = pt.PivotCache();
wbcnn = cache.WorkbookConnection;
return(wbcnn);
}
public void AddMedianSummary(Excel.Worksheet TableSheet, string TableName, ListingStatus Status)
{
int lastRow = 0;
int firstRow = 0;
int avgRow = 0;
int medianRow = 0;
int lastCol = 0;
double avgRowHeight = 38;
double medianRowHeight = avgRowHeight;
Excel.PivotTable pvt = null;
TableSheet.Select();
pvt = TableSheet.PivotTables(TableName);
firstRow = pvt.TableRange1.Row + 2;
lastRow = pvt.TableRange1.Row + pvt.TableRange1.Rows.Count - 2;
avgRow = lastRow + 1;
medianRow = avgRow + 1;
int rw = 0;
foreach (Excel.PivotField pvf in pvt.RowFields)
{
rw++;
}
Excel.Range Cell = TableSheet.Range["A" + avgRow];
Cell.Value2 = "Average Values";
Cell = TableSheet.Range["A" + medianRow];
Cell.Value2 = "Median Values";
TableSheet.Cells[medianRow, rw + 1].Value = Library.GetCount(ListingSheet, "B", Status, "", "");
TableSheet.Cells[medianRow, rw + 2].Value = Library.GetMedianValue(ListingSheet, "G", Status, "", "");
TableSheet.Cells[medianRow, rw + 3].Value = Library.GetMedianValue(ListingSheet, "I", Status, "", "");
TableSheet.Cells[medianRow, rw + 4].Value = Library.GetMedianValue(ListingSheet, "L", Status, "", "");
TableSheet.Cells[medianRow, rw + 5].Value = Library.GetMedianValue(ListingSheet, "M", Status, "", "");
TableSheet.Cells[medianRow, rw + 6].Value = Library.GetMedianValue(ListingSheet, "O", Status, "", "");
TableSheet.Cells[medianRow, rw + 7].Value = Library.GetMedianValue(ListingSheet, "P", Status, "", "");
TableSheet.Cells[medianRow, rw + 8].Value = Library.GetMedianValue(ListingSheet, "R", Status, "", "");
TableSheet.Cells[medianRow, rw + 9].Value = Library.GetMedianValue(ListingSheet, "S", Status, "", "");
TableSheet.Select();
lastCol = pvt.TableRange1.Columns.Count;
Excel.Range rng = TableSheet.Range[TableSheet.Cells[medianRow, 1], TableSheet.Cells[medianRow, lastCol]];
rng.RowHeight = medianRowHeight;
Excel.Range rngSource = TableSheet.Range[TableSheet.Cells[avgRow, 1], TableSheet.Cells[avgRow, lastCol]];
rngSource.Select();
rngSource.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rngSource.Copy();
rng.Select();
rng.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
rng.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.AliceBlue);
rng.Font.Bold = true;
rngSource.Select();
Globals.ThisAddIn.Application.CutCopyMode = 0;
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"MisTimeSheetPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Полное имя доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Полное имя доктора").Position = 2;
pivotTable.PivotFields("Идентификатор доктора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Идентификатор доктора").Position = 3;
pivotTable.PivotFields("Должность").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность").Position = 4;
pivotTable.PivotFields("Должность (справочник)").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Должность (справочник)").Position = 5;
pivotTable.PivotFields("Дата графика работ").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата графика работ").Position = 1;
pivotTable.AddDataField(pivotTable.PivotFields("Кол-во часов (план)"), "Сумма кол-во часов (план)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма кол-во часов (план)").NumberFormat = "# ##0,00";
pivotTable.PivotFields("Дата графика работ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Должность (справочник)").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Идентификатор доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Полное имя доктора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Дата графика работ").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Должность (справочник)").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Идентификатор доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Полное имя доктора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Группа филиалов").ShowDetail = false;
//pivotTable.PivotFields("Название").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
public static bool ExportToExcel(RevisionData selected,
RevOrderMgr om)
{
List <List <string> > data =
AggregateData(selected, om, REV_SORT_ITEM_DESC);
int row = TITLE_ROW;
string outFile = SetUpOutputFile(Setg.TemplatePathAndFileName,
Setg.ExcelPathAndFileName);
X.Application excel = new X.Application();
if (excel == null)
{
return(false);
}
X.Workbook wb = excel.Workbooks.Open(outFile);
X.Worksheet wsData =
wb.Sheets[Setg.ExcelDataWorksheetName] as X.Worksheet;
if (wsData == null)
{
return(false);
}
excel.Visible = false;
ExportColumnTitles(wsData, row, om);
row++;
// startRow row count
X.Range range = GetRange(wsData, row, data.Count,
// startCol colCount
1, om.ColumnOrder.Count);
FormatDataCells(range);
ExportToExcel(data, row, wsData, om);
AdjustColumnWidthInRange(range.Columns, 1.5);
X.Worksheet wsPivot =
wb.Sheets[Setg.ExcelPivotWorksheetName] as X.Worksheet;
X.PivotTable pivotTable =
(X.PivotTable)wsPivot.PivotTables(Setg.ExcelPivotTableName);
pivotTable.RefreshTable();
excel.Visible = true;
return(true);
}
public static string GetConnectionString(Excel.Range rngCell)
{
Excel.PivotTable pt = null;
Excel.PivotCache cache = null;
string connString;
pt = rngCell.PivotTable;
cache = pt.PivotCache();
connString = cache.Connection;
return(connString);
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"RecordsFromInsuranceCompaniesPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, ws.UsedRange, 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Название страховой").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Название страховой").Position = 1;
pivotTable.PivotFields("Имя оператора").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Имя оператора").Position = 2;
pivotTable.PivotFields("Пациент").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Пациент").Position = 3;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 4;
pivotTable.PivotFields("Доктор").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Доктор").Position = 5;
pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Дата назначения").Position = 1;
pivotTable.PivotFields("Название страховой").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Имя оператора").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Пациент").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Отделение").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Доктор").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Дата назначения").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Название страховой").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Имя оператора").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Пациент").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Отделение").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Доктор").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Дата назначения").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.AddDataField(
pivotTable.PivotFields("SCHEDID"),
"Кол-во",
Excel.XlConsolidationFunction.xlCount);
wb.ShowPivotTableFieldList = false;
wsPivote.Range["A1"].Select();
}
public static void AddKpiToPivotTable(Kpi k, Excel.PivotTable pvt)
{
foreach (string sKpiPart in new string[] { "KPI_VALUE", "KPI_GOAL", "KPI_STATUS", "KPI_TREND" })
{
string sKpiMeasure = Convert.ToString(k.Properties[sKpiPart].Value);
if (string.IsNullOrEmpty(sKpiMeasure))
{
continue;
}
Excel.CubeField field = pvt.CubeFields.get_Item(sKpiMeasure);
if (field.Orientation == Excel.XlPivotFieldOrientation.xlDataField)
{
continue;
}
field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
if (sKpiPart == "KPI_STATUS" || sKpiPart == "KPI_TREND")
{
Excel.PivotItem pi = (Excel.PivotItem)pvt.DataPivotField.PivotItems(sKpiMeasure);
Excel.Range range = pi.DataRange;
Excel.IconSetCondition iconSet = (Excel.IconSetCondition)range.FormatConditions.AddIconSetCondition();
string sStatusGraphic = (sKpiPart == "KPI_STATUS" ? k.StatusGraphic : k.TrendGraphic);
IconSetDefinition def = new IconSetDefinition();
if (_dictIconSetLookup.ContainsKey(sStatusGraphic))
{
def = _dictIconSetLookup[sStatusGraphic];
}
else
{
System.Windows.Forms.MessageBox.Show("Status graphic type " + sStatusGraphic + " not expected. Please contact the authors of OLAP PivotTable Extensions on the About tab.", "OLAP PivotTable Extensions");
}
iconSet.IconSet = pvt.Application.ActiveWorkbook.IconSets[def.IconSet];
try
{
iconSet.ScopeType = Microsoft.Office.Interop.Excel.XlPivotConditionScope.xlDataFieldScope;
}
catch { }
iconSet.ShowIconOnly = true;
iconSet.ReverseOrder = def.Reverse;
int i = 2;
foreach (double d in def.ValueBoundaries)
{
Excel.IconCriterion crit = iconSet.IconCriteria[i++];
crit.Type = Excel.XlConditionValueTypes.xlConditionValueNumber;
crit.Value = d;
crit.Operator = (int)(Excel.XlFormatConditionOperator.xlGreaterEqual);
}
}
}
}
public static Excel.PivotTable CopyPivotTable(Excel.PivotTable pt)
{
Excel.Application excelApp = pt.Application;
var worksheet = (Excel.Worksheet)pt.Parent;
worksheet.Select();
pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true);
Excel.Range rng = (Excel.Range)excelApp.Selection;
rng.Copy();
Excel.Worksheet ws = (Excel.Worksheet)excelApp.Sheets.Add();
ws.Paste();
return(ws.Range["A1"].PivotTable);
}
public static Excel.Range CopyPivotTable(Excel.PivotTable pt)
{
Excel.Application XlApp = pt.Application;
Excel.Worksheet sourceSheet = (Excel.Worksheet)pt.Parent;
sourceSheet.Select();
pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true);
Excel.Range sourceRange = (Excel.Range)XlApp.Selection;
sourceRange.Copy();
Excel.Worksheets sheets = (Excel.Worksheets)XlApp.Sheets;
Excel.Worksheet destSheet = (Excel.Worksheet)sheets.Add();
destSheet.Paste();
return(destSheet.Range["A1"]);
}
private static void AddMultiplePageFieldFilterToDic(Excel.PivotTable pt, PivotCellDictionary pivotCellDic)
{
var mdxString = pt.MDX;
var pivotFields = pt.PivotFields();
var pivotFieldNames = new List <string>();
foreach (Excel.PivotField pf in pivotFields)
{
pivotFieldNames.Add(pf.Name);
}
AddMultiplePageFieldFilterToDic(pivotFieldNames, mdxString, pivotCellDic);
}
/// <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)
{
}
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"PivotTableScheduleRecords";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C16", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("ФИО пользователя").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО пользователя").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 2;
pivotTable.AddDataField(pivotTable.PivotFields("Дата и время записи"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Кол-во записей").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("По направлению?"), "По направлению", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("По направлению").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Прием состоялся?"), "Прием состоялся", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Прием состоялся").NumberFormat = "# ##0";
pivotTable.AddDataField(pivotTable.PivotFields("Сумма, всего"), "Сумма оказанных услуг (руб)", Excel.XlConsolidationFunction.xlSum);
pivotTable.PivotFields("Сумма оказанных услуг (руб)").NumberFormat = "# ##0,00 ?";
//pivotTable.PivotFields("Запись, Должность").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Запись, Должность").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Филиал").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Филиал").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Дата").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
//pivotTable.PivotFields("Дата").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Запись, Должность").ShowDetail = false;
//pivotTable.PivotFields("Филиал").ShowDetail = false;
pivotTable.PivotFields("ФИО пользователя").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"PivotTableEmployeesCovidTreat";
Excel.Worksheet wsPivote = wb.Sheets["Сводная таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C14", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 1;
pivotTable.PivotFields("Диагноз МКБ").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Диагноз МКБ").Position = 2;
pivotTable.PivotFields("Диагноз").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Диагноз").Position = 3;
pivotTable.PivotFields("ФИО пациента").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("ФИО пациента").Position = 4;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 5;
pivotTable.PivotFields("ФИО пациента").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("ФИО пациента").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Диагноз").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Диагноз").LayoutForm = Excel.XlLayoutFormType.xlTabular;
pivotTable.PivotFields("Диагноз МКБ").Subtotals = new bool[] { false, false, false, false, false, false, false, false, false, false, false, false };
pivotTable.PivotFields("Диагноз МКБ").LayoutForm = Excel.XlLayoutFormType.xlTabular;
//pivotTable.PivotFields("Запись, Должность").ShowDetail = false;
//pivotTable.PivotFields("Филиал").ShowDetail = false;
//pivotTable.PivotFields("ФИО пользователя").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
pivotTable.DisplayFieldCaptions = false;
wsPivote.Columns["B:B"].ColumnWidth = 60;
wsPivote.Range["A1"].Select();
}
public static PivotCellDictionary GetPivotCellQuery(Excel.Range rngCell)
{
Excel.PivotTable pt = rngCell.PivotTable;
Excel.PivotCell pc = rngCell.PivotCell; //Field values
Excel.PivotFields pgfs = (Excel.PivotFields)(pt.PageFields);
var pivotCellDic = new PivotCellDictionary();
AddSingleAxisFiltersToDic(pc, pivotCellDic);
AddSinglePageFieldFiltersToDic(pgfs, pivotCellDic);
AddMultiplePageFieldFilterToDic(pt, pivotCellDic);
return(pivotCellDic);
}
private void FormatBottom3(Excel.PivotTable pvt, int FirstRow, int LastRow, int iCol)
{
Excel.Range c1 = PivotSheet.Cells[FirstRow + 1, pvt.ColumnRange.Columns[iCol].Column];
Excel.Range c2 = PivotSheet.Cells[LastRow - 1, pvt.ColumnRange.Columns[iCol].Column];
PivotSheet.Range[c1, c2].Select();
//pvt.PivotSelect("Sales", Excel.XlPTSelectionMode.xlDataOnly, true);
Globals.ThisAddIn.Application.Selection.FormatConditions.AddTop10();
Globals.ThisAddIn.Application.Selection.FormatConditions(Globals.ThisAddIn.Application.Selection.FormatConditions.Count).SetFirstPriority();
Globals.ThisAddIn.Application.Selection.FormatConditions(1).TopBottom = Excel.XlTopBottom.xlTop10Bottom;
Globals.ThisAddIn.Application.Selection.FormatConditions(1).Rank = 3;
Globals.ThisAddIn.Application.Selection.FormatConditions(1).Percent = false;
Globals.ThisAddIn.Application.Selection.FormatConditions(1).Font.Color = System.Drawing.Color.DarkGreen;
Globals.ThisAddIn.Application.Selection.FormatConditions(1).Font.Bold = true;
}
private static List <string> ExtractItemsFromPivotRows()
{
List <string> items = new List <string>();
Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable;
string selectedFields = pt.RowFields.Item(0).Name;
foreach (Excel.PivotItem rowItem in pt.PivotFields(selectedFields).PivotItems)
{
items.Add(rowItem.Name);
}
return(items);
}
public void Format(Excel.Worksheet PivotSheet, string TableName, ListingStatus Status, string City)
{
Excel.PivotTable pvt = PivotSheet.PivotTables(TableName);
int FirstRow = 0;
int LastRow = 0;
int LastCol = 0;
int TitleRow = 0;
FirstRow = pvt.TableRange1.Row + 1;
LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2;
LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column;
TitleRow = pvt.TableRange2.Row - 1;
//Todo: Format Title
//Hide Values Row
Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)];
rng0.EntireRow.Hidden = true;
Excel.Range c1 = PivotSheet.Cells[FirstRow, 1];
Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol];
Excel.Range rng = PivotSheet.Range[c1, c2];
rng.Select();
//rng.Style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//rng.Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//rng.Style.Font.Name = "Roboto";
//rng.Style.Font.Size = 11;
//rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble;
rng.RowHeight = 38;
rng.WrapText = true;
rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
rng = PivotSheet.Range["A" + LastRow];
rng.Select();
rng.RowHeight = 36;
// Create the table style
//ListingBook.TableStyles.Add("Attached Report Style");
Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"];
//ptStyle.ShowAsAvailablePivotTableStyle = true;
// Set Table Style
pvt.TableStyle2 = ptStyle;
FormatColumnWidth();
HideComplexSubTotal(PivotSheet, TableName);
AddSubGroupBottomBorder(TableName);
FormatMaxCells();
FormatMinCells();
AddSectionTitle(PivotSheet, TableName, City + " " + Status + " Records:");
}
public LevelChooserForm(Excel.CubeField cubeField, Excel.PivotTable pt)
{
PivotTable = pt;
InitializeComponent();
this.chkLevels.Items.Clear();
//cubeField.CreatePivotFields(); //shouldn't be necessary since it's already in the PivotTable
foreach (Excel.PivotField pf in cubeField.PivotFields)
{
if (pf.IsMemberProperty)
{
continue;
}
this.chkLevels.Items.Add(new LevelContainer(pf), !pf.Hidden);
}
}
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();
}
public void AddSectionTitle(Excel.Worksheet WS, string PTName, string Title)
{
Excel.PivotTable PT = WS.PivotTables(PTName);
int FirstRow = PT.TableRange2.Row;
//ADD SECTION TITLE
Excel.Range cell = WS.Range["A" + (FirstRow + 1)];
cell.Value = Title;
cell.Font.Size = 24;
cell.Font.Color = System.Drawing.Color.Red.ToArgb();
cell.Font.Bold = true;
cell.Font.Italic = true;
cell.EntireRow.RowHeight = 32;
//HIDE PAGE GROUP FILTER
cell = WS.Range["A" + FirstRow];
cell.EntireRow.Hidden = true;
}
static void Main()
{
string filePath = @"C:\Users\stackoverflow\Desktop\Sample.xlsx";
Excel.Application excel = new Excel.Application();
excel.Visible = true;
excel.EnableAnimations = true;
Excel.Workbook wkb = Open(excel, filePath);
foreach (Excel.Worksheet xlWorksheet in wkb.Worksheets)
{
Excel.PivotTables pivotTablesCollection = xlWorksheet.PivotTables();
if (pivotTablesCollection.Count > 0)
{
for (int i = 1; i <= pivotTablesCollection.Count; i++)
{
Excel.PivotTable currentPivotTable = pivotTablesCollection.Item(i);
Console.WriteLine($"Table is named -> {currentPivotTable.Name}");
foreach (Excel.PivotField pivotField in currentPivotTable.PivotFields())
{
Console.WriteLine($"\nField is named -> {pivotField.Name}");
foreach (Excel.PivotItem visibleItems in pivotField.VisibleItems)
{
Console.WriteLine($"Visible item name -> {visibleItems.Name}");
}
foreach (Excel.PivotItem PivotItem in pivotField.PivotItems())
{
Console.WriteLine($"Item is named -> {PivotItem.Name}");
Console.WriteLine(PivotItem.Visible);
}
}
}
}
}
excel.EnableAnimations = true;
wkb.Close(true);
excel.Quit();
Console.WriteLine("Finished!");
}
public void FormatMinCells()
{
Excel.Range c = null;
Excel.PivotTable PT = this.PivotSheet.PivotTables(this.PivotTableName);
Excel.Worksheet WS = this.PivotSheet;
long i = 0;
long FirstRow = 0;
long LastRow = 0;
long FirstCol = 0;
long LastCol = 0;
string MinCell = "";
double Min = 0;
//FIND THE LAST NON-BLANK CELL IN COLUMNA
FirstRow = PT.TableRange1.Row + 2;
LastRow = FirstRow + PT.TableRange1.Rows.Count - 4;
FirstCol = PT.ColumnRange.Column + 1;
LastCol = PT.ColumnRange.Column + PT.ColumnRange.Columns.Count - 1;
for (long col = FirstCol; col <= LastCol; col++)
{
i = FirstRow;
Min = Library.GetMin(this.PivotSheet, this.PivotTableName, col);
MinCell = "";
for (i = FirstRow; i <= LastRow - 2; i++)
{
c = WS.Cells[i, col];
if (c.Value2 != null && i <= LastRow - 2 && !((bool)c.Rows.Hidden) && (double)c.Value == Min)
{
if (WS.Cells[i, 1].Value == null || WS.Cells[i, 1].Value != "SubTotal")
{
MinCell = WS.Range[c, c].Address;
WS.Range[MinCell].Interior.ColorIndex = 0;
WS.Range[MinCell].Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
WS.Range[MinCell].Font.Bold = true;
}
}
}
}
}
internal static void AddSlicers(
Excel.Workbook eWorkbook,
Excel.PivotTable pivotTable,
Excel.Worksheet pivotWorkSheet,
IEnumerable <string> slicerNames)
{
const int top = 350;
const int width = 200;
const int height = 200;
var left = 440;
foreach (var name in slicerNames)
{
var slicerCurrent = eWorkbook.SlicerCaches.Add(pivotTable, name);
slicerCurrent.Slicers.Add(pivotWorkSheet,
Top: top, Left: left, Width: width, Height: height);
left += width + 10;
}
}
private void CreateOrder(bool isUnscheduled)
{
if (isUnscheduled)
{
this.deliveryDate = ComputeUnscheduledDeliveryDate();
this.nextScheduledDeliveryDate = ComputeWeeklyDeliveryDate();
}
else
{
this.deliveryDate = ComputeWeeklyDeliveryDate();
this.nextScheduledDeliveryDate = this.deliveryDate.AddDays(7);
}
// This creates a PivotTable with information regarding the
// amounts of ice cream sold.
this.PopulateDateInformation(this.orderDate);
Excel.PivotTable pivotTable = this.CreatePivotTable();
this.AddCalculations(pivotTable);
}
private void CreateOrder(bool isUnscheduled)
{
if (isUnscheduled)
{
this.deliveryDate = ComputeUnscheduledDeliveryDate();
this.nextScheduledDeliveryDate = ComputeWeeklyDeliveryDate();
}
else
{
this.deliveryDate = ComputeWeeklyDeliveryDate();
this.nextScheduledDeliveryDate = this.deliveryDate.AddDays(7);
}
// 这会创建一个数据透视表，其中包含
// 与冰淇淋的销售量有关的信息。
this.PopulateDateInformation(this.orderDate);
Excel.PivotTable pivotTable = this.CreatePivotTable();
this.AddCalculations(pivotTable);
}
private void PivotGroupingWindowOkButton_Click(object sender, EventArgs e)
{
if (groups.Count <= 1) // groups are empty
{
return;
}
groups.ForEach(groupItem =>
{
if (groupItem.Item2.Count <= 1)
{
return;
}
Excel.PivotTable pt = Globals.PivotGroupingAddIn.Application.ActiveCell.PivotTable;
Excel.PivotFields pfs = pt.RowFields;
string selectedFields = pfs.Item(0).Name + "[";
groupItem.Item2.ForEach(item => selectedFields += item + ",");
selectedFields = selectedFields.Substring(0, selectedFields.Length - 1);
selectedFields += "]";
pt.PivotSelect(selectedFields, Excel.XlPTSelectionMode.xlLabelOnly);
var selection = Globals.PivotGroupingAddIn.Application.Selection;
selection.Group();
foreach (Excel.PivotField pf in pt.PivotFields())
{
foreach (Excel.PivotItem rowItem in pf.PivotItems())
{
if (rowItem.Value.StartsWith("Group"))
{
rowItem.Value = groupItem.Item1;
}
}
}
});
this.Close();
}
private static void AddPivotTable(Excel.Workbook wb, Excel.Worksheet ws, Excel.Application xlApp)
{
string pivotTableName = @"FrontOfficeClientsPivotTable";
Excel.Worksheet wsPivote = wb.Sheets["Сводная Таблица"];
int rowsUsed = ws.UsedRange.Rows.Count;
wsPivote.Activate();
Excel.PivotCache pivotCache = wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, "Данные!R1C1:R" + rowsUsed + "C12", 6);
Excel.PivotTable pivotTable = pivotCache.CreatePivotTable(wsPivote.Cells[1, 1], pivotTableName, true, 6);
pivotTable = (Excel.PivotTable)wsPivote.PivotTables(pivotTableName);
pivotTable.PivotFields("Дата назначения").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Дата назначения").Position = 1;
pivotTable.PivotFields("Филиал").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Филиал").Position = 2;
pivotTable.PivotFields("Отделение").Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields("Отделение").Position = 3;
pivotTable.PivotFields("Новый пациент?").Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields("Новый пациент?").Position = 1;
wsPivote.Range["B1"].Value2 = "Новый пациент?";
wsPivote.Range["A2"].Value2 = "Филиал";
pivotTable.AddDataField(pivotTable.PivotFields("№ ИБ"), "Кол-во записей", Excel.XlConsolidationFunction.xlCount);
pivotTable.PivotFields("Филиал").ShowDetail = false;
wb.ShowPivotTableFieldList = false;
//pivotTable.DisplayFieldCaptions = false;
wsPivote.Range["A1"].Select();
}
private void Sheet1_Startup(object sender, System.EventArgs e)
{
try
{
this.Sheet1_TitleLabel.Value2 = Properties.Resources.Sheet1Title;
this.Name = Properties.Resources.Sheet1Name;
this.newDateButton.Text = Properties.Resources.AddNewDateButton;
this.saveButton.Text = Properties.Resources.SaveDataButton;
this.dayView = Globals.DataSet.CreateView();
if (Globals.DataSet.Sales.Count != 0)
{
this.DateSelector.MinDate = Globals.DataSet.MinDate;
this.DateSelector.MaxDate = Globals.DataSet.MaxDate;
this.DateSelector.Value = this.DateSelector.MaxDate;
}
using (TextFileGenerator textFile = new TextFileGenerator(Globals.DataSet.Sales))
{
this.pivotTable = CreatePivotTable(textFile.FullPath);
}
Globals.DataSet.Sales.SalesRowChanged += new OperationsBaseData.SalesRowChangeEventHandler(Sales_SalesRowChanged);
UnscheduledOrderControl smartPaneControl = new UnscheduledOrderControl();
smartPaneControl.Dock = DockStyle.Fill;
smartPaneControl.View = this.dayView;
Globals.ThisWorkbook.ActionsPane.Controls.Add(smartPaneControl);
this.Activate();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
MessageBox.Show(ex.Message);
}
}
public string ChangeSourceDataPivotTable(string FilePath, string PivotSheetName, string SourceSheetName, string SourceDataRange, string PivotTableName)
{
string result = "";
try
{
string srcData = SourceSheetName + "!" + Ws.Range[SourceDataRange].Address[XlReferenceStyle.xlR1C1];
result = OpenExcel(FilePath, PivotSheetName);
if (result == "")
{
Excel.PivotTable pivot = Ws.PivotTables(PivotTableName);
pivot.ChangePivotCache(Wb.PivotCaches().Create(XlPivotTableSourceType.xlDatabase, srcData));
result = "";
}
}
catch (Exception e)
{
return("Exception caught - " + e.Message);
}
return(result);
}
// Функция сведения двух таблиц
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);
}
