private void AddConditionalFormats()
{
NewRuleConditionalFormat newRule1 = new NewRuleConditionalFormat();
newRule1.RuleType = RuleType.FormatAllCellsBasedOnTheirValues;
newRule1.FormatStyle = FormatStyle.IconSets;
newRule1.SummaryElement = "Quantity";
newRule1.Image = BitMapImageName.ThreeTriangle;
newRule1.ValueType1 = ValueType.Number;
newRule1.ValueType2 = ValueType.Number;
newRule1.ValueType3 = ValueType.Number;
newRule1.ValueType4 = ValueType.Number;
PivotGridNewRuleConditionalFormat newRuleFormat1 = new PivotGridNewRuleConditionalFormat();
newRuleFormat1.NewRuleCollections.Add(newRule1);
this.pivotGridControl1.TableControl.NewRuleConditionalFormat.Add(newRuleFormat1);
this.pivotGridControl1.TableControl.Refresh(true);
}
private void genarateData()
{
if (cmbJenisPel.Text.Equals("Rawat Jalan"))
{
this.pivotGridControl1.ItemSource = getDataRawatJalan();
}
else
{
this.pivotGridControl1.ItemSource = getDataRawatInap();
}
if (chkBoxSort.Checked)
{
this.pivotGridControl1.RowPivotsOnly = true;
}
else
{
this.pivotGridControl1.RowPivotsOnly = false;
this.pivotGridControl1.PivotRows.Add(new PivotItem {
FieldMappingName = "diagnosa_grouper", FieldHeader = "Diagnosa", AllowSort = true
});
}
this.pivotGridControl1.GridVisualStyles = GridVisualStyles.Metro;
this.pivotGridControl1.PivotRows.Add(new PivotItem {
FieldMappingName = "deskripsi", FieldHeader = "Deskripsi", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "no_sep", FieldHeader = "Total Kasus(SEP)", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "biaya_rs", FieldHeader = "Biaya RS", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "iurPasien", FieldHeader = "Iuran Pasien", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "potongan", FieldHeader = "Potongan", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "COB", FieldHeader = "COB", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "piutangRS", FieldHeader = "Piutang RS", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true, FieldCaption = "Piutang RS"
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "umbal", FieldHeader = "Umbal BPJS", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true
});
this.pivotGridControl1.PivotCalculations.Add(new PivotComputationInfo {
FieldName = "selisihUmbal", FieldHeader = "Selisih Dgn Umbal", SummaryType = SummaryType.Sum, Format = "#,##0", AllowSort = true, FieldCaption = "Selisih Umbal"
});
this.pivotGridControl1.ShowSubTotals = false;
this.pivotGridControl1.ShowPivotValueChooser = true;
this.pivotGridControl1.TableControl.AllowRowPivotFiltering = true;
this.pivotGridControl1.TableControl.AllowRowResizeUsingCellBoundaries = true;
this.pivotGridControl1.TableControl.AllowColumnResizeUsingCellBoundaries = true;
// this.pivotGridControl1.RowPivotsOnly = true;
this.pivotGridControl1.AllowSorting = true;
pivotGridControl1.TableControl.FreezeHeaders = true;
NewRuleConditionalFormat newRule1 = new NewRuleConditionalFormat();
newRule1.RuleType = RuleType.FormatOnlyCellsThatContain;
newRule1.SummaryElement = "Selisih";
ConditionalFormat condition1 = new ConditionalFormat();
condition1.ConditionType = PivotGridDataConditionType.LessThan;
condition1.StartValue = 0;
newRule1.Conditions.Add(condition1);
PivotGridNewRuleConditionalFormat newRuleFormat1 = new PivotGridNewRuleConditionalFormat();
newRuleFormat1.NewRuleCollections.Add(newRule1);
newRuleFormat1.PivotCellStyle.BackColor = Color.Pink;
newRuleFormat1.PivotCellStyle.TextColor = Color.White;
this.pivotGridControl1.TableControl.NewRuleConditionalFormat.Add(newRuleFormat1);
this.pivotGridControl1.Refresh();
this.pivotGridControl1.ShowPivotTableFieldList = true;
this.pivotGridControl1.ShowGroupBar = true;
this.pivotGridControl1.PivotSchemaDesigner.RefreshGridSchemaLayout();
pivotGridControl1.TableModel.Model.ColWidths[1] = 100;
pivotGridControl1.TableModel.Model.ColWidths[2] = 400;
}
/// <summary>
/// Set the values for all the cell
/// </summary>
private void button1_Click(object sender, EventArgs e)
{
bool isNeedColor = false;
NewRuleConditionalFormat newRule1 = new NewRuleConditionalFormat();
string RuleType = this.grdRuleType.Text;
newRule1.RuleType = SetRuleType(RuleType);
double number = 0;
switch (RuleType)
{
case "Format Only Cells That Contain":
ConditionalFormat form = new ConditionalFormat();
form.ConditionType = SetConditionalType(cboRule2ConditionType.SelectedItem.ToString());
form.PredicateType = SetPredicateType(cboRule2ConditionType.SelectedItem.ToString());
if (!string.IsNullOrEmpty(txtRule2StartValue.Text) &&
double.TryParse(txtRule2StartValue.Text, out number))
{
form.StartValue = Convert.ToDouble(txtRule2StartValue.Text);
}
if (!string.IsNullOrEmpty(txtRule2EndValue.Text) &&
double.TryParse(txtRule2EndValue.Text, out number))
{
form.EndValue = Convert.ToDouble(txtRule2EndValue.Text);
}
newRule1.Conditions.Add(form);
newRule1.SummaryElement = cboRule2SummaryElement.SelectedItem.ToString();
isNeedColor = true;
break;
case "Format Top Or Bottom Ranked Values":
newRule1.FormateValuesRankType = SetFormateValuesRankType(cboRule3FormatRankType.SelectedItem.ToString());
if (!string.IsNullOrEmpty(txtRule3RankValue.Text) &&
double.TryParse(txtRule3RankValue.Text, out number))
{
newRule1.RankValue = Convert.ToInt32(Convert.ToDouble(txtRule3RankValue.Text.ToString()));
}
if (cboRule3SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule3SummaryElement.SelectedItem.ToString();
}
isNeedColor = true;
break;
case "Format Only Values That Are Above Or Below Average":
newRule1.FormateSelectedAverageValueType = SetFormateSelectedAverageValueType(cboRule4FormatValueType.SelectedItem.ToString());
if (cboRule4SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule4SummaryElement.SelectedItem.ToString();
}
isNeedColor = true;
break;
case "Format Only Unique Or Duplicate Values":
newRule1.FormatAllType = SetFormatAllType(cboRule5FormatAllType.SelectedItem.ToString());
if (cboRule5SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule5SummaryElement.SelectedItem.ToString().Trim();
}
isNeedColor = true;
break;
default:
if (cboRule1FormatStyle.SelectedIndex == 0)
{
newRule1.FormatStyle = FormatStyle.IconSets;
}
if (cboRule1SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule1SummaryElement.SelectedItem.ToString();
}
newRule1.Image = SetImageName(cboRule1ImageName.Text);
isNeedColor = false;
if (cboValueType1.SelectedIndex == 0)
{
newRule1.ValueType1 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Percent;
}
else
{
newRule1.ValueType1 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Number;
}
if (cboValueType2.SelectedIndex == 0)
{
newRule1.ValueType2 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Percent;
}
else
{
newRule1.ValueType2 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Number;
}
if (cboValueType3.SelectedIndex == 0)
{
newRule1.ValueType3 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Percent;
}
else
{
newRule1.ValueType3 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Number;
}
if (cboValueType4.SelectedIndex == 0)
{
newRule1.ValueType4 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Percent;
}
else
{
newRule1.ValueType4 = Syncfusion.Windows.Forms.PivotAnalysis.ValueType.Number;
}
break;
}
PivotGridNewRuleConditionalFormat newRuleFormat1 = new PivotGridNewRuleConditionalFormat();
newRuleFormat1.NewRuleCollections.Add(newRule1);
if (isNeedColor)
{
newRuleFormat1.PivotCellStyle.BackColor = Color.Red;
newRuleFormat1.PivotCellStyle.TextColor = Color.White;
}
this.pivotGridControl1.TableControl.NewRuleConditionalFormat.Add(newRuleFormat1);
this.pivotGridControl1.TableControl.Refresh(true);
}
/// <summary>
/// Load the form for Setting the Grid
/// </summary>
void Form1_Load(object sender, EventArgs e)
{
this.grdDropDown.BeginUpdate();
this.grdDropDown.DataSource = CreateTable();
this.grdDropDown.DisplayMember = "Name";
this.grdDropDown.ValueMember = "Id";
this.grdDropDown.SelectionMode = SelectionMode.One;
this.grdDropDown.ShowColumnHeader = false;
this.grdDropDown.ThemesEnabled = true;
this.grdDropDown.Grid.VerticalThumbTrack = true;
this.grdDropDown.Grid.DrawCellDisplayText += new Syncfusion.Windows.Forms.Grid.GridDrawCellDisplayTextEventHandler(Grid_DrawCellDisplayText);
this.grdDropDown.FillLastColumn = true;
this.grdDropDown.EndUpdate();
this.grdDropDown.Grid.QueryCellInfo += new Syncfusion.Windows.Forms.Grid.GridQueryCellInfoEventHandler(Grid_QueryCellInfo);
DataTable dtRuleType = new DataTable();
dtRuleType.Columns.Add("RuleType");
dtRuleType.Rows.Add("Format All Cells Based On Their Values");
dtRuleType.Rows.Add("Format Only Cells That Contain");
dtRuleType.Rows.Add("Format Top Or Bottom Ranked Values");
dtRuleType.Rows.Add("Format Only Values That Are Above Or Below Average");
dtRuleType.Rows.Add("Format Only Unique Or Duplicate Values");
this.grdRuleType.DisplayMember = "RuleType";
this.grdRuleType.SelectionMode = SelectionMode.One;
this.grdRuleType.ShowColumnHeader = false;
this.grdRuleType.ThemesEnabled = true;
this.grdRuleType.Grid.VerticalThumbTrack = true;
this.grdRuleType.DataSource = dtRuleType;
this.grdRuleType.FillLastColumn = true;
this.grdRuleType.BorderStyle = BorderStyle.FixedSingle;
this.grdDropDown.BorderStyle = BorderStyle.FixedSingle;
this.grdDropDown.ThemesEnabled = false;
this.grdRuleType.ThemesEnabled = false;
// default selection
this.grdRuleType.Grid.Model.Selections.Add(GridRangeInfo.Cell(1, 1));
loadRule1Combobox();
imageSelection(5);
bool isNeedColor = false;
NewRuleConditionalFormat newRule1 = new NewRuleConditionalFormat();
string RuleType = this.grdRuleType.Text;
newRule1.RuleType = SetRuleType(RuleType);
ConditionalFormat form = new ConditionalFormat();
cboRule2ConditionType.SelectedIndex = 0;
form.ConditionType = SetConditionalType(cboRule2ConditionType.SelectedItem.ToString());
form.PredicateType = SetPredicateType(cboRule2ConditionType.SelectedItem.ToString());
if (!string.IsNullOrEmpty(txtRule2StartValue.Text))
{
form.StartValue = Convert.ToDouble(txtRule2StartValue.Text);
}
if (!string.IsNullOrEmpty(txtRule2EndValue.Text))
{
form.EndValue = Convert.ToDouble(txtRule2EndValue.Text);
}
newRule1.Conditions.Add(form);
cboRule2SummaryElement.SelectedIndex = 0;
this.cboRule1ImageName.TextBox.Text = "3-Flag";
newRule1.Image = SetImageName(this.cboRule1ImageName.TextBox.Text);
newRule1.SummaryElement = cboRule2SummaryElement.SelectedItem.ToString();
isNeedColor = true;
PivotGridNewRuleConditionalFormat newRuleFormat1 = new PivotGridNewRuleConditionalFormat();
newRuleFormat1.NewRuleCollections.Add(newRule1);
if (isNeedColor)
{
newRuleFormat1.PivotCellStyle.BackColor = Color.Red;
newRuleFormat1.PivotCellStyle.TextColor = Color.White;
}
this.pivotGridControl1.TableControl.NewRuleConditionalFormat.Add(newRuleFormat1);
this.pivotGridControl1.TableControl.Refresh(true);
}
private void ApplyConditionalFormat()
{
bool isNeedColor = false;
NewRuleConditionalFormat newRule1 = new NewRuleConditionalFormat();
string RuleType = grdRuleType.Text;
newRule1.RuleType = SetRuleType(RuleType);
double number;
switch (RuleType)
{
case "Format all value cells based on certain criteria":
ConditionalFormat form = new ConditionalFormat();
form.ConditionType = SetConditionalType(cboRule2ConditionType.SelectedItem.ToString());
form.PredicateType = SetPredicateType(cboRule2ConditionType.SelectedItem.ToString());
if (!string.IsNullOrEmpty(txtRule2StartValue.Text) &&
double.TryParse(txtRule2StartValue.Text, out number))
{
form.StartValue = Convert.ToDouble(txtRule2StartValue.Text);
}
if (!string.IsNullOrEmpty(txtRule2EndValue.Text) &&
double.TryParse(txtRule2EndValue.Text, out number))
{
form.EndValue = Convert.ToDouble(txtRule2EndValue.Text);
}
newRule1.Conditions.Add(form);
newRule1.SummaryElement = cboRule2SummaryElement.SelectedItem.ToString();
isNeedColor = true;
break;
case "Format top or bottom ranked value cells":
newRule1.FormateValuesRankType = SetFormateValuesRankType(cboRule3FormatRankType.SelectedItem.ToString());
if (!string.IsNullOrEmpty(txtRule3RankValue.Text) &&
double.TryParse(txtRule3RankValue.Text, out number))
{
newRule1.RankValue = Convert.ToInt32(Convert.ToDouble(txtRule3RankValue.Text));
}
if (cboRule3SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule3SummaryElement.SelectedItem.ToString();
}
isNeedColor = true;
break;
case "Format only value cells containing above or below average":
newRule1.FormateSelectedAverageValueType = SetFormateSelectedAverageValueType(cboRule4FormatValueType.SelectedItem.ToString());
if (cboRule4SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule4SummaryElement.SelectedItem.ToString();
}
isNeedColor = true;
break;
case "Format only unique or duplicate value cells":
newRule1.FormatAllType = SetFormatAllType(cboRule5FormatAllType.SelectedItem.ToString());
if (cboRule5SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule5SummaryElement.SelectedItem.ToString().Trim();
}
isNeedColor = true;
break;
default:
if (cboRule1FormatStyle.SelectedIndex == 0)
{
newRule1.FormatStyle = FormatStyle.IconSets;
}
if (cboRule1SummaryElement.SelectedItem != null)
{
newRule1.SummaryElement = cboRule1SummaryElement.SelectedItem.ToString();
}
newRule1.Image = SetImageName(cboRule1ImageName.Text);
if (cboValueType1.Enabled)
{
newRule1.ValueType1 = cboValueType1.SelectedIndex == 0 ? ValueType.Percent : ValueType.Number;
newRule1.Value1 = double.Parse(txtValue1.Text);
}
if (cboValueType2.Enabled)
{
newRule1.ValueType2 = cboValueType2.SelectedIndex == 0 ? ValueType.Percent : ValueType.Number;
newRule1.Value2 = double.Parse(txtValue2.Text);
}
if (cboValueType3.Enabled)
{
newRule1.ValueType3 = cboValueType3.SelectedIndex == 0 ? ValueType.Percent : ValueType.Number;
newRule1.Value3 = double.Parse(txtValue3.Text);
}
if (cboValueType4.Enabled)
{
newRule1.ValueType4 = cboValueType4.SelectedIndex == 0 ? ValueType.Percent : ValueType.Number;
newRule1.Value4 = double.Parse(txtValue4.Text);
}
break;
}
PivotGridNewRuleConditionalFormat newRuleFormat1 = new PivotGridNewRuleConditionalFormat();
newRuleFormat1.NewRuleCollections.Add(newRule1);
if (isNeedColor)
{
newRuleFormat1.PivotCellStyle.BackColor = Color.Red;
newRuleFormat1.PivotCellStyle.TextColor = Color.White;
}
pivotGridControl1.TableControl.NewRuleConditionalFormat.Add(newRuleFormat1);
pivotGridControl1.TableControl.Refresh(true);
}
