5/27/2024 Admin

Using OpenAI to Update A Blazor Excel Worksheet

Watch the YouTube vide at this link: https://www.youtube.com/watch?v=sKNHlM6GdCc

What happens when you combine Microsoft Blazor with a new cutting-edge open source data editor and the latest Open AI 4o model?

In this blog post, we’ll explore how the OpenAI LLM model can be used to update an Excel-like data editor control within a Blazor application. We can use AI to update data based on rules it infers from data edits you make.

The Application

In this first example we edit the phone number field to add the area code.

When then click the Submit button.

The OpenAI LLM will analyze the data changed and deduce that the remaining phone numbers are missing their area code.

We ask the AI to send us the data grid cells that it determines need to be changed and we programmatically highlight them.

We can click the Log button to see the logic the AI used.

When can then click the Apply Changes button to instruct the AI to indicate what the cell values should be based on the rules it inferred.

Notice in this case the AI was able to determine what the correct area code is based on the value in the City column.

In the next example we demonstrate that you can make changes to multiple columns.

Creating The Code

When we look at the Visual Studio project we see that only a few files were added or updated:

Home – Contains the user interface for the sample
OpenAIServiceOptions – Simple class to hold the OpenAI key
OpenAIService – Handles calls to OpenAI
Appsettings – Configuration for the OpenAI key
Program – Registration of the BlazorDataSheet and OpenAI-DotNet packages

The first step is to install the required Nuget packages.

Install the following packages:

Open the Program.cs file and add the following code:

   builder.Services.AddBlazorDatasheet();
   builder.Services.AddScoped<OpenAIService>();

The BlazorDatasheet control also requires that you add the following to the App.razor page:

<link href="_content/BlazorDatasheet/sheet-styles.css" rel="stylesheet" />
<script src="_content/BlazorDatasheet/blazor-datasheet.js" type="text/javascript"></script>

You will also need to obtain an OpenAI key (See: https://platform.openai.com/account/api-keys) and put that key in the appsettings.json file.

The Excel-Like Data Editor

The sample uses the open-source BlazorDatasheet control that provides a Microsoft Excel-like editing experience in a Microsoft Blazor application.

You can see a demo of that control here: https://anmcgrath.github.io/BlazorDatasheet/

The next step is to open the Home.razor control and add the control using the following code:

<Datasheet Sheet="@sheet" />

By itself this wont display anything so we will add a dropdown using this code:

<select @onchange="@(async (args) => await LoadTableData(args.Value?.ToString()))">
    <option value="TableOne">Table One</option>
    <option value="TableTwo">Table Two</option>
    </select>

This will allow us to select a set of sample data that will populate a DataTable. We will then use that DataTable to populate the BlazorDatasheet control using this code:

   private async Task LoadTableData(string paramTableName)
   {
       IsLoadingSheet = true;
       strAIResponse = "";
       Message = "";
       InitializeDataTable(paramTableName);
       // Get a list of the columns from the DataTable
       TableColumns = OrginalDataTable.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
       // Create sheet with the number of columns
       sheet = new Sheet(OrginalDataTable.Rows.Count, TableColumns.Count);
       // Wait 1 second before loading the table data
       // to join the UI thread
       await Task.Delay(1000);
       // Turn off history
       sheet.BatchUpdates();
       sheet.Commands.PauseHistory();
       int i = 0;
       foreach (string objDatabaseColumn in TableColumns)
       {
           sheet.Columns.SetHeadings(i, i, objDatabaseColumn);  
           i++;
       }
       int ii = 0;
       foreach (DataRow dataRow in OrginalDataTable.Rows)
       {
           i = 0;
           foreach (string objDatabaseColumn in TableColumns)
           {
               // Set the value of the cell
               sheet.Cells[ii, i].Value = dataRow[i].ToString();
               // Make the first column read only and hidden and an integer
               if (i==0)
               {
                   sheet.Cells[ii, i].Format = new CellFormat() { IsReadOnly = true };
                   sheet.Cells[ii, i].Type = "int";
               }
               else
               {
                   sheet.Cells[ii, i].Format = new CellFormat() { IsReadOnly = false };
                   sheet.Cells[ii, i].Type = "string";
               }
               i++;
           }
           ii++;
       }
       // Hide the Id column
       sheet.Columns.SetWidth(0, 0);
       // Turn on history
       sheet.EndBatchUpdates();
       sheet.Commands.ResumeHistory();
       IsLoadingSheet = false;
   }

The data will display in the data editor. The data can now be updated.

We can then hit the Submit button to send the previous and updated data to the OpenAI 4o model.

The first step is to convert the data in the BlazorDatasheet back to a DataTable using the following code:

private async Task SubmitData()
{
    // Initialize a new DataTable
    CurrentDataTable = new DataTable();
    // Define columns based on TableColumns
    foreach (var column in TableColumns)
    {
        CurrentDataTable.Columns.Add(new DataColumn(column));
    }
    // Populate the DataTable with rows from the sheet
    for (int i = 0; i < sheet.NumRows; i++)
    {
        DataRow row = CurrentDataTable.NewRow();
        for (int j = 0; j < TableColumns.Count; j++)
        {
            try
            {
                string currentValue = sheet.Cells[i, j].Value?.ToString() ?? "";
                currentValue = currentValue.Replace("\r\n", " ")
                                           .Replace("\t", " ")
                                           .Replace("\r", " ")
                                           .Replace("\n", " ")
                                           .Trim();
                row[j] = currentValue;
            }
            catch
            {
                row[j] = "";
            }
        }
        CurrentDataTable.Rows.Add(row);
    }
    await GetChanges();
}

This calls the GetChanges method that passes the initial and updated DataTables to the AI with instructions to determine what has changed and to return JSON that indicates what data cells have changed:

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Please examine the following data to determine how the data has changed.");
            sb.AppendLine("");
            sb.AppendLine("For all data here are the column headers.");
            sb.AppendLine("");
            sb.AppendLine(string.Join(",", TableColumns));
            sb.AppendLine("");
            sb.AppendLine("The following show the #Original and #Updated Data:");
            sb.AppendLine("");
            var drOrginal = OrginalDataTable.Rows;
            var drCurrent = CurrentDataTable.Rows;
            // Convert to Arrays
            var arrOrginal = drOrginal.Cast<DataRow>().Select(x => x.ItemArray).ToArray();
            var arrCurrent = drCurrent.Cast<DataRow>().Select(x => x.ItemArray).ToArray();
            // Convert each item array to a string for proper joining
            var originalStrings = arrOrginal.Select(array => string.Join(",", array.Select(item => item.ToString())));
            var currentStrings = arrCurrent.Select(array => string.Join(",", array.Select(item => item.ToString())));
            sb.AppendLine("#Original: " + string.Join("; ", originalStrings));
            sb.AppendLine("#Updated: " + string.Join("; ", currentStrings));  
            sb.AppendLine("");
            sb.AppendLine("Infer any validation rules such as spelling or formatting or other logical rules. ");
            sb.AppendLine("Infer that any changed data is the correct data and the previous data is incorrect. ");
            sb.AppendLine("");
            sb.AppendLine("");
            sb.AppendLine("Please indicate what cells in #Updated, that have not been updated, ");
            sb.AppendLine("that should be highlighted as being affected by the validation rules. ");
            sb.AppendLine("As the final response please return a .json result surounded by ###JSON-START and JSON-END###. ");
            sb.AppendLine("Use this format: [{ 'row number': 'value', 'column number': 'value', 'validation Reason': 'reason1'}]");
            sb.AppendLine("For example: ");
            sb.AppendLine("{'row number': '1', 'column number': '2', 'validation Reason': 'Data missing'},");
            sb.AppendLine("{'row number': '2', 'column number': '4', 'validation Reason': 'Invalid format'}");
            sb.AppendLine("");
            // *** Call AI
            // Create a list of messages
            var messages = new List<OpenAI.Chat.Message>
            {
                new OpenAI.Chat.Message(Role.System, "You are a helpful assistant."),
                new OpenAI.Chat.Message(Role.User, sb.ToString()),
            };
            // Call the OpenAI service
            ChatMessages = await OpenAIService.CallOpenAIService(messages);
            // Get the last response
            strAIResponse = ChatMessages.LastOrDefault();
            // Get the JSON response from the content between ###JSON-START and JSON-END###
            int intStart = strAIResponse.IndexOf("###JSON-START###");
            int intEnd = strAIResponse.IndexOf("###JSON-END###");
            // Get the JSON from the response
            string strJSON = "";
            if (intStart > 0 && intEnd > 0)
            {
                strJSON = strAIResponse.Substring(intStart + 16, intEnd - intStart - 16);
            }

That JSON is passed though the AI again to ensure that it is valid:

 public async Task<string> CallOpenAIServiceToGetJSON(string? jsonText)
 {
     // Get the API key from the appsettings.json file
     var ApiKey = _openAIServiceOptions.ApiKey;
     // Create a new instance of OpenAIClient using
     // the ApiKey and Organization
     var api =
     new OpenAIClient(new OpenAIAuthentication(ApiKey));
     var messages = new List<OpenAI.Chat.Message>
     {
         new OpenAI.Chat.Message(
             Role.System, 
             $"Please correct this json to make it valid. Return only the valid json in a collection called data: {jsonText}"),
     };
     var chatRequest = new OpenAI.Chat.ChatRequest(
         messages, temperature: 0.1, responseFormat: ChatResponseFormat.Json, model: "gpt-4o");
     var ChatResponse =
     await api.ChatEndpoint.GetCompletionAsync(chatRequest);
     return ChatResponse.FirstChoice.Message;
 }

The JSON is then used to highlight the fields using the following code:

                // Parse the JSON
                ValidationDatas json = JsonConvert.DeserializeObject<ValidationDatas>(strJSON);
                // If there is data, then highlight the cells
                if (json.Data.Count > 0)
                {
                    sheet.BatchUpdates();
                    // Loop through the JSON and highlight the cells
                    foreach (var item in json.Data)
                    {
                        int row = Convert.ToInt32(item.RowNumber);
                        int column = Convert.ToInt32(item.ColumnNumber);
                        sheet.Cells[(row - 1), (column - 1)].Format = new CellFormat() 
                        { BackgroundColor = "yellow", ForegroundColor = "black", FontWeight = "bold" };
                    }
                    sheet.EndBatchUpdates();
                    CanApplyChanges = true;
                }
                else
                {
                    Message = "No JSON data was returned.";
                    CanApplyChanges = false;
                }

Once the cells are highlighted, we can click the Apply Changes to request that the AI determine what the values should be changed to based on the rules it has now inferred.

The following code is used:

                StringBuilder sb = new StringBuilder();
                sb.AppendLine("");
                sb.AppendLine("Infer any validation rules such as spelling or formatting or other logical rules. ");
                sb.AppendLine("Infer that any changed data is the correct data and the previous data is incorrect. ");
                sb.AppendLine("");
                sb.AppendLine("");
                sb.AppendLine("Please indicate what cells in #Updated, that have not been updated, ");
                sb.AppendLine("should have their values updated based on the validation rules. ");
                sb.AppendLine("As the final response please return a .json result surounded by ###JSON-START and JSON-END###. ");
                sb.AppendLine("Use this format: [{ 'row number': 'value', 'column number': 'value', 'updated value': 'value'}]");
                sb.AppendLine("For example: ");
                sb.AppendLine("{'row number': '1', 'column number': '2', 'updated value': '5'},");
                sb.AppendLine("{'row number': '2', 'column number': '4', 'updated value': '(213) 555-1212'}");
                sb.AppendLine("");
                // *** Call AI
                ChatMessages.Add(new OpenAI.Chat.Message(Role.User, sb.ToString()));
                // Call the OpenAI service
                ChatMessages = await OpenAIService.CallOpenAIService(ChatMessages);
                // Get the last response
                strAIResponse = ChatMessages.LastOrDefault();
                // Get the JSON response from the content between ###JSON-START and JSON-END###
                int intStart = strAIResponse.IndexOf("###JSON-START###");
                int intEnd = strAIResponse.IndexOf("###JSON-END###");
                // Get the JSON from the response
                string strJSON = "";
                if (intStart > 0 && intEnd > 0)
                {
                    strJSON = strAIResponse.Substring(intStart + 16, intEnd - intStart - 16);
                }

The resulting JSON is used to update the BlazorDatasheet using the following code:

                    // If there is data...
                    if (json.Data.Count > 0)
                    {
                        sheet.BatchUpdates();
                        // Loop through the JSON and update the cells
                        foreach (var item in json.Data)
                        {
                            try
                            {       
                                int row = Convert.ToInt32(item.RowNumber);
                                int column = Convert.ToInt32(item.ColumnNumber);
                                string value = item.UpdatedValue;
                                sheet.Cells[(row - 1), (column - 1)].Clear();
                                sheet.Cells[(row - 1), (column - 1)].Value = value;
                                sheet.Cells[(row - 1), (column - 1)].Format = new CellFormat() 
                                { BackgroundColor = "lightgreen", ForegroundColor = "black", FontWeight = "bold" };
                            }
                            catch { }
                        }
                        sheet.EndBatchUpdates();
                        CanApplyChanges = true;
                    }
                    else
                    {
                        Message = "No JSON data was returned.";
                        CanApplyChanges = false;
                    }

Download

The project is available on the Downloads page on this site.

You must have Visual Studio 2022 (or higher) installed to run the code.

Links

https://github.com/anmcgrath/BlazorDatasheet

https://github.com/RageAgainstThePixel/OpenAI-DotNet