I am using AG Grid in my HTML, and I need to achieve a functionality that merges cells with the same values in a column. This should make it visually clear when there are repeated values in consecutive rows. if there are consecutive rows with the same value, they should be merged into a single cell spanning multiple rows, with the value centred vertically.
Here is a picture example of what it looks like without the cell merging
and here is picture of what i am trying to achieve
Additionally, I want the columns to be dynamic based on the user’s selection. If a column is removed, the data and summed values should automatically repivot and provide the new value of the pivot.
Additional Information:
I am using AG Grid, and you can find the documentation here.
https://www.ag-grid.com/javascript-data-grid/grid-interface/
Here is a link to my code for review:
https://codesandbox.io/invite/jn5cyy2572zpm2h7
Below is what my python code which can be found i the link above,
@app.route('/get-data-CF')
def get_data_CF():
try:
correct_order = ['Cash Flow Main 4', 'Cash Flow Main 3',
'Cash Flow Main 2', 'Cash Flow Main 1', 'Cash Flow Category']
include_currency = request.args.get(
'include_currency', 'false').lower() == 'true'
if include_currency:
correct_order.insert(0, 'transaction_currency_code')
start_date = request.args.get('start_date')
end_date = request.args.get('end_date')
cashforce_df = pd.read_csv('static/excel/Modified_Cash_Pool_Data.csv')
bank_balance_df = pd.read_csv(
'static/excel/Modified_Bank_Balances_Data.csv')
cashforce_df['transaction_dt'] = pd.to_datetime(
cashforce_df['transaction_dt'])
if start_date and end_date:
cashforce_df = cashforce_df[(cashforce_df['transaction_dt'] >= start_date) & (
cashforce_df['transaction_dt'] <= end_date)]
else:
current_month_start = datetime.now().replace(day=1)
current_month_end = (
current_month_start + pd.DateOffset(months=1)) - pd.DateOffset(days=1)
cashforce_df = cashforce_df[(cashforce_df['transaction_dt'] >= current_month_start) & (
cashforce_df['transaction_dt'] <= current_month_end)]
grouped_df = cashforce_df.groupby(
correct_order + ['transaction_dt']).agg({'transaction_amount': 'sum'}).reset_index()
pivot_df = grouped_df.pivot_table(
index=correct_order, columns='transaction_dt', values='transaction_amount', aggfunc='sum', fill_value=0)
pivot_df.columns = [col.strftime(
'%Y-%m-%d') if isinstance(col, pd.Timestamp) else col for col in pivot_df.columns]
pivot_df.reset_index(inplace=True)
combined_rows = []
for currency in bank_balance_df['transaction_currency_code'].unique() if include_currency else ['GBP']:
opening_balance_row = {col: '' for col in pivot_df.columns}
closing_balance_row = {col: '' for col in pivot_df.columns}
if include_currency:
opening_balance_row['transaction_currency_code'] = currency
closing_balance_row['transaction_currency_code'] = currency
opening_balance_row['Cash Flow Main 4'] = 'Opening Balance'
closing_balance_row['Cash Flow Main 4'] = 'Closing Balance'
opening_balance = bank_balance_df.loc[bank_balance_df['transaction_currency_code'] == currency, 'balance'].sum(
) if include_currency else 0
closing_balance = opening_balance
for date_col in pivot_df.columns:
if date_col not in correct_order:
opening_balance_row[date_col] = f"{opening_balance:.2f}"
closing_balance_row[date_col] = f"{closing_balance:.2f}"
combined_rows.append(pd.DataFrame([opening_balance_row]))
combined_rows.append(
pivot_df[pivot_df['transaction_currency_code'] == currency] if include_currency else pivot_df)
combined_rows.append(pd.DataFrame([closing_balance_row]))
combined_df = pd.concat(combined_rows, ignore_index=True)
numeric_columns = combined_df.select_dtypes(include=['number']).columns
for col in numeric_columns:
combined_df[col] = combined_df[col].apply(lambda x: f"{x:.2f}")
json_data_CF = combined_df.to_json(orient='records', date_format='iso')
print(json_data_CF)
return jsonify(json_data_CF=json_data_CF)
except Exception as e:
return jsonify(error=str(e)), 500
below is my js code which can also be found in the codesandbox
document.addEventListener("DOMContentLoaded", () => {
const basePinnedColumns = [
"Cash Flow Main 4",
"Cash Flow Main 3",
"Cash Flow Main 2",
"Cash Flow Main 1",
"Cash Flow Category",
];
const allPinnedColumns = ["transaction_currency_code"].concat(
basePinnedColumns,
);
let availablePinnedColumns = [];
function rowSpan(params) {
if (!params.data || !params.column || !params.api) return 1;
const column = params.column.colId;
const rowIndex = params.rowIndex;
const rowData = params.data;
let spanCount = 1;
for (let i = rowIndex + 1; i < params.api.getDisplayedRowCount(); i++) {
const nextData = params.api.getDisplayedRowAtIndex(i).data;
if (nextData && nextData[column] === rowData[column]) {
spanCount++;
} else {
break;
}
}
return spanCount;
}
const gridOptions = {
defaultColDef: {
flex: 1,
minWidth: 100,
resizable: true,
sortable: true,
filter: true,
cellClass: (params) => {
if (
params.data &&
(params.data["Cash Flow Main 4"] === "Opening Balance" ||
params.data["Cash Flow Main 4"] === "Closing Balance")
) {
return params.colDef.field === "Cash Flow Main 4"
? "balance-label-cell"
: "balance-value-cell";
}
return null;
},
cellStyle: (params) => {
if (
params.data &&
(params.data["Cash Flow Main 4"] === "Opening Balance" ||
params.data["Cash Flow Main 4"] === "Closing Balance")
) {
return params.colDef.field === "Cash Flow Main 4"
? { textAlign: "left" }
: { textAlign: "right" };
}
return null;
},
},
autoGroupColumnDef: {
headerName: "Account Currency",
field: "transaction_currency_code",
cellRenderer: "agGroupCellRenderer",
cellRendererParams: {
suppressCount: true,
innerRenderer: (params) => {
if (
params.node.group &&
params.node.field === "transaction_currency_code"
) {
return `<div class="currency-cell">${params.node.key}</div>`;
} else {
return params.value;
}
},
},
},
rowGroupPanelShow: "always",
groupDefaultExpanded: -1,
columnDefs: [],
rowData: [],
suppressRowTransform: true,
};
const gridDiv = document.querySelector("#myGrid");
const gridApi = agGrid.createGrid(gridDiv, gridOptions);
function updateColumns(columnNames, includeCurrency) {
const pinnedColumns = includeCurrency
? allPinnedColumns
: basePinnedColumns;
const orderedColumnNames = pinnedColumns.concat(
columnNames.filter((name) => !pinnedColumns.includes(name)),
);
const columnDefs = orderedColumnNames.map((key) => ({
headerName:
key === "transaction_currency_code" ? "Account Currency" : key,
field: key,
sortable: true,
filter: true,
pinned: pinnedColumns.includes(key) ? "left" : null,
rowSpan: rowSpan,
cellClassRules: {
"cell-span": 'value && value !== ""',
},
}));
gridApi.updateGridOptions({ columnDefs: columnDefs });
updateColumnControlPanel(
columnDefs.filter((col) => basePinnedColumns.includes(col.field)),
);
}
function fetchData(startDate, endDate, includeCurrency = false) {
let url = "/get-data-CF";
if (startDate && endDate) {
url += `?start_date=${startDate}&end_date=${endDate}`;
}
if (includeCurrency) {
url += url.includes("?") ? "&" : "?";
url += "include_currency=true";
}
fetch(url)
.then((response) => {
if (!response.ok) {
throw new Error(
"Network response was not ok: " + response.statusText,
);
}
return response.json();
})
.then((response) => {
const data = JSON.parse(response.json_data_CF);
if (data && data.length > 0) {
const columnNames = Object.keys(data[0]);
updateColumns(columnNames, includeCurrency);
gridApi.updateGridOptions({ rowData: data });
availablePinnedColumns = allPinnedColumns.filter(
(col) => !columnNames.includes(col),
);
updateAvailableColumnsDropdown();
}
})
.catch((error) => {
console.error("Error fetching data:", error);
fetch(url)
.then((response) => response.text())
.then((text) => console.log("Response was:", text));
});
}
const startDatePicker = flatpickr('input[name="start_date"]', {
dateFormat: "Y-m-d",
});
const endDatePicker = flatpickr('input[name="end_date"]', {
dateFormat: "Y-m-d",
});
document.getElementById("dateFilterBtn").addEventListener("click", () => {
const startDate = startDatePicker.input.value;
const endDate = endDatePicker.input.value;
if (startDate && endDate) {
fetchData(
startDate,
endDate,
document.getElementById("currencyToggle").checked,
);
}
});
fetchData();
document.getElementById("currencyToggle").addEventListener("change", () => {
const startDate = startDatePicker.input.value;
const endDate = endDatePicker.input.value;
fetchData(
startDate,
endDate,
document.getElementById("currencyToggle").checked,
);
});
function isColumnOrderValid(currentOrder) {
let indexMap = currentOrder
.map((column) => allPinnedColumns.indexOf(column))
.filter((index) => index !== -1);
return indexMap.every((val, i, arr) => !i || val > arr[i - 1]);
}
function updateColumnControlPanel(columnDefs) {
const panel = document.getElementById("activeColumns");
panel.innerHTML = "";
columnDefs.forEach((col) => {
const colDiv = document.createElement("div");
colDiv.textContent = `${col.headerName} `;
const removeBtn = document.createElement("button");
removeBtn.textContent = "x";
removeBtn.onclick = () => removeColumn(col.field);
colDiv.appendChild(removeBtn);
panel.appendChild(colDiv);
});
updateAvailableColumnsDropdown();
}
function removeColumn(field) {
const allColumns = gridApi.getColumnDefs();
const newColumnDefs = allColumns.filter((col) => col.field !== field);
if (!isColumnOrderValid(newColumnDefs.map((def) => def.field))) {
alert("Removing this column would result in invalid data aggregation.");
return;
}
availablePinnedColumns.push(field);
gridApi.updateGridOptions({ columnDefs: newColumnDefs });
updateColumnControlPanel(
newColumnDefs.filter((col) => basePinnedColumns.includes(col.field)),
);
updateAvailableColumnsDropdown();
}
function updateAvailableColumnsDropdown() {
const dropdown = document.getElementById("availableColumns");
if (!dropdown) {
console.error("Dropdown element not found");
return;
}
dropdown.innerHTML = "";
availablePinnedColumns.forEach((field) => {
const option = new Option(field, field);
dropdown.add(option);
});
}
window.addColumn = function () {
const dropdown = document.getElementById("availableColumns");
const fieldToAdd = dropdown.value;
if (!fieldToAdd) return;
const allColumns = gridApi.getColumnDefs();
const columnToAdd = {
headerName: fieldToAdd,
field: fieldToAdd,
sortable: true,
filter: true,
pinned: "left",
};
const newColumnDefs = [...allColumns, columnToAdd];
if (!isColumnOrderValid(newColumnDefs.map((def) => def.field))) {
alert("Adding this column would result in invalid data aggregation.");
return;
}
gridApi.updateGridOptions({ columnDefs: newColumnDefs });
availablePinnedColumns = availablePinnedColumns.filter(
(col) => col !== fieldToAdd,
);
updateColumnControlPanel(
newColumnDefs.filter((col) => basePinnedColumns.includes(col.field)),
);
updateAvailableColumnsDropdown();
};
});
To display the grid, please select a start date from 1st of April and end date of 30th of April and click the filter button, as those are the only data available, and click the filter button to display the grid.
I have looked into AG Grid’s documentation and tried to implement the row spanning functionality, but I am not getting the desired merging effect for consecutive duplicates. I also tried making the columns dynamic but couldn’t achieve the automatic repivoting and summing of values based on user selection.
