Copy and paste with Data Table (restricting new rows and columns)

[GlenL]

Hello, we are using Dash and the Data Table component to build some workflow tools for heavy Excel users. Part of the workflow is copying data out of Excel and into a layout of various Data Tables in a Dash app. Out of the box, if a user copies a blob of cells in Excel and pastes into a Data Table, the Data Table expands to accommodate both rows and columns from the Excel source (assuming the Excel source is larger than the Data Table).

We’d like to keep the columns fixed in the Data Table, and just ignore any “extra columns” from the inbound Excel when pasting (we know the columns at runtime). We would however like to expand the number of rows in the Data Table as that is unknown at run time.

Any advice on controlling this paste-from-Excel behavior?

[AnnMarieW]

I had the same issue. It’s kinda cool that the table will automatically expand to accommodate the cut and paste contents, but when it adds new undefined columns to an existing table, it’s a little odd - especially since by default these columns are not deletable by the user.

One option is to delete the new columns in a callback. Another is to update the columns property in a callback to make the new columns deletable. That way, the user will be able to delete them, plus know that something went wrong with the copy and paste (rather than the data along with the new columns just disappearing)

There are probably other ways, but this worked for me.

[cab07e]

The callback suggestion sounds like a great idea. Any chance you can share an example of your code?

[danpryjma]

You can just return the original columns you used to build the DataTable in the first place.

@app.callback(Output('datatable_id', 'columns'), Input('excel_paste_ or_other_trigger', 'data')
def remove_cols(input):
    return original_col_List

This should do the trick to only display the columns that you had at first. The data will still be there, just invisible.

[AnnMarieW]

@danpryjma has a good solution. Also, if you want to keep the new columns and make them editable, that can be updated in a callback like in this example:

import dash
from dash.dependencies import Input, Output, State
import dash_table
import dash_html_components as html
import pandas as pd
from random import randint, seed


app = dash.Dash(__name__)

df = pd.read_csv("https://raw.githubusercontent.com/plotly/datasets/master/solar.csv")

app.layout = html.Div(
    [
        html.H4("Copy and paste any data into this table"),
        dash_table.DataTable(
            id="copy_paste_table",
            columns=[
                {
                    "name": "Column {}".format(i),
                    "id": "column-{}".format(i),
                    "deletable": True,
                    "renamable": True,
                }
                for i in range(1, 10)
            ],
            data=[
                {"column-{}".format(i): (randint(0, 100)) for i in range(1, 10)}
                for j in range(3)
            ],
            editable=True,
            row_deletable=True,
        ),
        html.H4("Copy and paste updated solar data"),
        html.Button("Edit New Columns", id="edit_col"),
        html.Button("Add Row", id="add_row"),
        dash_table.DataTable(
            id="solar",
            columns=[{"name": i, "id": i} for i in df.columns],
            data=df.to_dict("records"),
            editable=True,
            row_deletable=True,
        ),
    ]
)


@app.callback(
    Output("solar", "data"),
    Input("add_row", "n_clicks"),
    State("solar", "data"),
    State("solar", "columns"),
    prevent_initial_call=True,
)
def add_row(_, rows, columns):
    if rows is None:
        return df.to_dict("records")
    else:
        rows.append({c["id"]: "" for c in columns})
        return rows


@app.callback(
    Output("solar", "columns"),
    Input("edit_col", "n_clicks"),
    State("solar", "columns"),
    prevent_initial_call=True,
)
def edit_col(_, columns):
    if columns is None:
        return dash.no_update
    else:
        for c in columns:
            if c["id"] not in df.columns:
                c["deletable"] = True
                c["renamable"] = True
        return columns


if __name__ == "__main__":
    app.run_server(debug=True)