Ag-Grid component with input support

i know how define a conditional on pandas, but not on ag grid

i am trying to add image field into my Ag-Grid object by following this example but with success.
i am retrieving data from SQL server and convert it to dataframe than try to add the image but is not working.
i mage exist on my drive
c:\Users\test\desktop\pictures\pic1.png

where is my error ?

code:


show_image=JsCode("""function(params){
           var element = document.createElement("span");
            var imageElement = document.createElement("img");
        
            if (params.data.image_path) {
                imageElement.src = params.data.image_path;
                imageElement.width="20";
            } else {
                imageElement.src = "";
            }
            element.appendChild(imageElement);
            element.appendChild(document.createTextNode(params.value));
            return element;
            }""")
    gb.configure_column('pic', cellRenderer=show_image)

it depends on the field name or column that you want to make a condition for it let say based on your post you want to change the color or the record based on the field name ESTOQUE

inside the Ag-Grid object you need to follow these steps.

let say you have a datafarme df

code:


from st_aggrid import GridOptionsBuilder,JsCode
dg = GridOptionsBuilder.from_dataframe(df)
change_color = JsCode("""
          function(params){
            if(params.data.ESTOQUE ====4){
               
                   return {
                        'background':'#f17b7b'
                   }
           }           
           if(params.data.ESTOQUE ====2){
               
                 return {
                       'background':'#ffffff'
                  }
            
           }
""")

gridoptions = gd.build()
gridoptions['getRowStyle']=change_color

grid_table = AgGrid(df,gridoptions, allow_unsafe_jscode = True)

Hi @Max_Witteman , I’m interested to know how you implemented default values for added rows and also the pop confirmation for delete button. Thank you so much!

Hi, I am trying to use a list inside JScode to check my if condition. But whenever I pass the the list, all the values of the data frame disappear. How can I use a list to check if my params.value equals to one of the value in the list, rather an a single value.

```
value = [1,2]
cellstyle_jscode = JsCode(f"""
const error = {value}   
    function (params) {{  
        const hasValue = error.includes(params.value)          
            if (hasValue === true){{
                return {{
                    'backgroundColor': 'red',
                }}
            }}
    }}

""")
gb = GridOptionsBuilder.from_dataframe(failed_indices_df)
gb.configure_columns(failed_indices_df, cellStyle=cellstyle_jscode,)
gridoptions = gb.build()
response = st.write(
    grid_return=AgGrid(
        failed_indices_df,
        gridOptions=gridoptions,
        height=600,
        width=600,
        reload_data=False,
        theme="balham",
        fit_columns_on_grid_load =True,
        allow_unsafe_jscode=True,
        enable_enterprise_modules=True,
       #update_mode=GridUpdateMode.MANUAL,
    )
)

Local images need to be base64. Here’s an example you can test out (Display images in AgGrid table - #8 by Shawn_Pereira).

Cheers

I tried your answer with the connection with SQL server DB the picture doesn’t show in the AgGrid table.
But if i tried to read the image based on the giving path it works and the ReadPictureFile() return the correct file.

Note: if i try your code it works perfectly with the giving dataframe but once i tried to use it inside my SQL table it crash and doesn’t display the picture.

what could be the problem and how to display image inside the AgGrid table i needed in order to accomplish my project.

File path: C:/Users/admin/Desktop/Python/imgs/david-wong.png

code:

import pyodbc
import pandas as pd
import numpy as np

import streamlit as st

#  aggrid pckgs
from st_aggrid import AgGrid,GridOptionsBuilder,JsCode
from st_aggrid import GridUpdateMode,DataReturnMode

from io import BytesIO

import base64


#  connect to DB
@st.experimental_singleton
def connect_db():
    try:
        con = pyodbc.connect(
            driver = "ODBC Driver 17 for SQL Server",
            Server="127.0.01",

            DATABASE="FTF",
            UID="XXXX",
            PWD="XXXXXXX",
        )
        cursor = con.cursor()
    except Exception as e:
        st.write("error is: {}".format(e))
    return con

con = connect_db()

def ReadPictureFile(wch_fl):
    try:
        return base64.b64encode(open(wch_fl, 'rb').read()).decode()

    except:
        return ""

ShowImage = JsCode("""function (params) {
            var element = document.createElement("span");
            var imageElement = document.createElement("img");
        
            if (params.data.ImgPath != '') {
                imageElement.src = params.data.ImgPath;
                imageElement.width="100";
            } else { imageElement.src = ""; }
            element.appendChild(imageElement);
            element.appendChild(document.createTextNode(params.value));
            return element;
            }""")

df = pd.read_sql_query('''select ID,
name,
ImgPath
from [FTF].[dbo].[profile_info] 
''',con)

df['ImgPath'] = df['ImgPath'].replace(np.nan,'NULL')
if df.shape[0] > 0:
    for i, row in df.iterrows():
        # if row.ImgLocation == 'Local':
        imgExtn = row.ImgPath[-4:]

    
        if imgExtn == '.png':
            st.write(imgExtn)
            row.ImgPath = f'data:image/{imgExtn};base64,' + ReadPictureFile(row.ImgPath)
            st.write(row.ImgPath)



with con.cursor()as cur:
    gb = GridOptionsBuilder.from_dataframe(df)
    gb.configure_default_column(editable=True,min_column_width=2)
    gb.configure_column('Face', cellRenderer=ShowImage)
    
    gb.configure_grid_options(rowHeight = 100)
    vgo = gb.build()
    AgGrid(df, gridOptions=vgo, height=1000, allow_unsafe_jscode=True )

Well, I don’t have SQL server DB nor have access to your data.

Just before your “if df.shape[0]…” statement, insert a st.dataframe(df) to check what comes back from the database into your ImgPath column of your data frame.

If valid image paths come back, do they actually point to the exact image location? You will need to test this.

Also, does your ImgLocation column correctly classify the image as ‘Local’ for the image that resides on your disk?

Cheers

Hello,
Is there currently any support for server side row models? I was hoping to use this for something large enough that the data table might get too large for the 200 MB max that streamlit will take in a single table. Luckily, aggrid has a tool for this sort of situation, but I’m struggling to figure out if/how I can accomplish this with this package.

Does anyone have any ideas?

Thanks!

looking at one of the examples it appears the grid needs to be set to rowModelType: ‘serverSide’

Srry though I haven’t attempted an example I’m still trying to get my aggrid to work with immutable data…
JavaScript Data Grid: Updating Row Data …once I get that done I’ll attempt larger data sets and get back…but for extremely large data sets I would think for interim to filter out data before sending to grid with options to then select which buckets of data to view (user selects which bucket when data has to be sliced up)

Thanks for the response.
Yes, I agree that the grid option “rowModelType” needs to be set to “serverSide”, but that is not the only piece. In particular, in looking into it more, it seems we need to be able to set the ServerSideDatasource to be able to retrieve new rows.
(For example: if you were to create the aggrid in JS, I think you would need a line like
function ServerSideDatasource() {}
ServerSideDatasource.prototype.getRows = function (params) {
<…>
}
gridOptions.api.setServerSideDatasource(new ServerSideDatasource());
)
As far as I can tell, there isn’t anything currently built into this module to allow that. Moreover, when I look at the st_aggrid source code, it appears it has been manually set to only take client side row models, but I would be happy to be wrong!

Hi, Is there anyway to display the full contents of a cell in Ag-Grid when we click on it. My cell contents are too big. Resizing the columns of the table will make it less convenient for the user.

I’m looking for something like the second picture(normal st.dataframe). Displaying full cell contents when clicking on it

Ag-Grid table

Thanks! :slight_smile:

Hey Kiran, I have a couple ideas for hacks for this.

The closest thing to what you are looking for is probably the onCellClicked grid option. This option can take some JsCode that will be executed whenever a cell is clicked. I don’t know javascript well enough to tell you what that code should be, though. Using the GridOptionBuilder, this would look something like:
gb.configure_grid_options( onCellClicked = JsCode(“”“function(event) { <your code> }”“”) )

The second idea would be to mess with the master-detail stuff. The aggrid documentation for this is here. To use this for st_aggrid, you can use the gridOptionBuilder to set the option masterDetail to True, and set detailCellRendererParams to a dictionary which at least includes “getDetailRowData” : <Some JSCode to access the text>.

This may look something like:
gb.configure_grid_options(
masterDetail=True,
detailCellRendererParams={
“detailGridOptions”: {
“columnDefs”: <your code>,
},
“getDetailRowData”: JsCode(“”“function(params){
params.successCallback(params.data.VulnerabilityImpact);
}”“”),
},
)

Not sure if that is exactly right because VulnerabilityImpact column has just strings instead of its JSON objects, but you can probably figure it out from there.

Finally, I think there might be grid options that works like onCellClicked but for double click and right click, but I don’t remember their names or how they work.

(Look at this Angular Data Grid: Grid Events (ag-grid.com))

Hello,

Thank you all for this! Just wondering, is grouping and hovering enabled from this package?

Angular Data Grid: Column Groups (ag-grid.com)

ag-Grid Styling & Appearance: Row Styles

Hi @crsca, you can try out my sample code for grouping as below, and modify it for your use thereafter:

import pandas as pd
import streamlit as st
from st_aggrid import JsCode, AgGrid, GridOptionsBuilder
from st_aggrid.shared import GridUpdateMode


df=pd.DataFrame({ "Name": ['Paul', 'Gina'], "Age": [43, 35], "Inducted": [True, False], 
                  "Firm": ['Google', 'Microsoft'], "JDesc": ['Analyst', 'Programmer']})
gridOptions = GridOptionsBuilder.from_dataframe(df)
gb = gridOptions.build()

gb['columnDefs'] = [ { 'headerName': 'Personal Dtls', 'children': [ { 'field': 'Name' }, { 'field': 'Age' } ] }, 
                     { 'headerName': 'Partially', 'children': [{'field': 'Inducted'}] }, 
                     { 'headerName': 'Profession Dtls', 'children': [ { 'field': 'Firm' }, { 'field': 'JDesc' } ] }, 
                     { 'children': [{'field': 'Processed'}] } ]
                    
dta = AgGrid(df, gridOptions=gb, height=350, theme="blue", update_mode=GridUpdateMode.SELECTION_CHANGED)

Cheers

1 Like

@Shawn_Pereira this worked perfectly! Thank you!

Just a quick follow up, how would you center the header names and is it possible to customize their background color?

I tried adding headerClass and cellClass, and still no luck.

gb[“columnDefs”] = [
{
“headerName”: “Personal Dtls”,
“headerClass”: “ag-center-header”,
“cellClass”: “ag-center-cell”,
“children”: [{“field”: “Name”}, {“field”: “Age”}],
},
{“headerName”: “Partially”, “children”: [{“field”: “Inducted”}]},
{
“headerName”: “Profession Dtls”,
“children”: [{“field”: “Firm”}, {“field”: “JDesc”}],
},
{“children”: [{“field”: “Processed”}]},
]

Hi @crsca, colours seem possible, but I didn’t get the time to try it out. Will let you know if that happens.

Cheers

Hi @Shawn_Pereira,

Following this interesting post, do you know if it’s possible to add a onCellClicked grid event that enlarge the image in a popup window ? The goal would be to click on a cell and expand the image like this.

Thank you in advance for your response,
Best regards,

Louis

Hi @Louis_Monier, pls refer the code below for an implementation of onCellClicked. You could probably write the image into the new window (myWin) and expand it with JS. I didn’t get a chance to try it as yet. Do give it a shot at your end on the basis of the following code.

Hi @Kiran_K, below is an implementation of a browser window (instead of a popup). You can easily modify the JS to further suit your purpose.

Clicking on the Name field gives a standard JS popup; clicking on the Topics field gives a browser-based popup. I have inserted paragraph tags in the 3rd topic to illustrate line breaks. You can change the text attributes in the secondary browser window as required.

Copy & paste the code to test it out, and then repurpose it for your needs.

import pandas as pd
import streamlit as st
from st_aggrid import JsCode, AgGrid, GridOptionsBuilder
from st_aggrid.shared import GridUpdateMode

st.set_page_config(layout = "wide", initial_sidebar_state = "expanded")
t1 ="Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum."
t2 = "Congue quisque egestas diam in arcu. Libero nunc consequat interdum varius sit amet mattis vulputate. Vitae congue mauris rhoncus aenean vel elit scelerisque mauris. Nunc sed velit dignissim sodales ut eu sem integer vitae. Ut pharetra sit amet aliquam id diam. Tristique risus nec feugiat in. Non quam lacus suspendisse faucibus interdum. Faucibus ornare suspendisse sed nisi lacus sed viverra. Aliquet lectus proin nibh nisl"
t3 = "Urna cursus eget nunc scelerisque viverra mauris in aliquam. <p>Dictumst vestibulum rhoncus est pellentesque elit ullamcorper dignissim cras tincidunt. </p> Vestibulum sed arcu non odio euismod lacinia at. Massa id neque aliquam vestibulum morbi blandit cursus risus. Tincidunt tortor aliquam nulla facilisi cras. Nisi est sit amet facilisis magna. Id velit ut tortor pretium viverra suspendisse potenti. Blandit libero volutpat sed cras ornare arcu dui vivamus. Sit amet purus gravida quis blandit turpis cursus."

df=pd.DataFrame({ "Name": ['Erica', 'Rogers', 'Malcolm'], 'Topics': [t1, t2, t3], "Age": [43, 35, 57]})

clicked_name_cell_func = "function(params) { alert(params.node.data.Name); };"
clicked_topic_cell_func = """function (params) { 
    let myWin = window.open("Topic", "wid", "toolbar=no,menubar=no,location=no,status=no,height=285,width=500, left=450,top=175");
    myWin.document.write("<span style='font-size: 24px; color:red;'>" + params.data.Topics + "</span>");
    myWin.focus();
                                                }"""

gridOptions = GridOptionsBuilder.from_dataframe(df)
gridOptions.configure_column("Name", headerTooltip='Click to see cell data', onCellClicked=JsCode(clicked_name_cell_func))
gridOptions.configure_column("Topics", headerTooltip='Click to see cell data', onCellClicked=JsCode(clicked_topic_cell_func), maxWidth=600)

gridOptions.configure_default_column(editable=True)
gb = gridOptions.build()

dta = AgGrid(df, gridOptions=gb, height=200, allow_unsafe_jscode=True, theme="blue",
             update_mode=GridUpdateMode.SELECTION_CHANGED & GridUpdateMode.MODEL_CHANGED)

Cheers

1 Like

@Shawn_Pereira

Awesome, it worked! I have applied this to the entire table using the below code.

def clicked_cell_func(col_name):
    return f"function(params){{ alert(params.node.data.{col_name}); }};"


for col_index, col_name in enumerate(df.columns):
    gd.configure_column(f"{col_name}", headerTooltip="Click to sort data",
                        onCellDoubleClicked=JsCode(clicked_cell_func(col_name)), maxWidth=800)
1 Like