# AI.QUERY ## Content Users can pass specified parameters to the AI.QUERY function to obtain query results from the AI model. ## Syntax `AI.QUERY(prompt1, [data1], [prompt2], [data2] ...)` ## Arguments The AI.QUERY function can accept any number of prompts and data, which will be organized into a single text block and sent to the AI model for processing. This function has these arguments: | **Argument** | **Description** | | -------- | ----------- | | *prompt1* | [Required] Text describing the task or asking a question to the AI model. Each *prompt* parameter is merged with the context in order to form a complete prompt message. | | *data1* | [Optional] Table reference providing context or data for the AI model; can be a single cell or a range. | | *prompt2, data2, …* | [Optional] Additional text and table references for further context to the AI model. Unlimited in number; they can be used in pairs or just specify *prompt*. | ## Remarks The prompt sent to the AI model is generated by concatenating all *prompt* and *data* parameters. For example, when using the formula `=COPILOT("Classify", B1:B10, "into one of the following categories ", A1:A4)`, the final content sent to the AI model is: "Classify [*the values from B1:B10*] into the following categories [*the values from A1:A4*]". ## Examples **Example 1** The example below demonstrates how to pass multiple *prompt* parameters to the AI.QUERY function so that the AI can intelligently analyze the given text based on the prompts. ```java // To use this example, add the following dependency to your project: com.openai:openai-java:4.6.1 // Configure the model request handler and choose different large model providers as needed. Here the example uses OpenAI GPT-4.1; replace with your API key when using. Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.openai.com/v1", "sk-xxxx", "gpt-4.1")); // DeepSeek model. // Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.deepseek.com/v1", "sk-xxxx", "deepseek-chat")); // Qwen model. // Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://dashscope.aliyuncs.com/compatible-mode/v1", "sk-xxxx", "qwen-plus")); // Initialize the workbook and set data.Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getWorksheets().get(0); sheet.getColumns().get(0).setColumnWidth(57); sheet.getColumns().get(1).setColumnWidth(55); sheet.getColumns().get(2).setColumnWidth(42); sheet.getRange("A1:C1").merge(); sheet.getRange("A1").setValue("Example1: Customer Product Reviews and Classification"); sheet.getRange("A1").getFont().setBold(true); sheet.getRange("A1").getFont().setSize(16); sheet.getRange("A1").getFont().setColor(Color.GetWhite()); sheet.getRange("A1").getInterior().setColor(Color.FromArgb(90, 126, 158)); sheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Center); sheet.getRange("A1").setVerticalAlignment(VerticalAlignment.Center); sheet.getRange("A1").setRowHeight(35); sheet.getRange("A3").setValue("Formula:"); sheet.getRange("A3").getFont().setBold(true); sheet.getRange("A3").getFont().setSize(11); sheet.getRange("A3").getInterior().setColor(Color.FromArgb(217, 225, 242)); sheet.getRange("B3").setValue("=AI.QUERY(\"evaluate these reviews\", A6:A13, \"based on these categories\",B5:C5)"); sheet.getRange("B3").getFont().setItalic(true); sheet.getRange("B3").getFont().setColor(Color.FromArgb(68, 114, 196)); sheet.getRange("A5:C5").setValue(new Object[][]{ {"Taco Truck Reviews", "Positive or negative", "Topics"} }); sheet.getRange("A5:C5").getFont().setBold(true); sheet.getRange("A5:C5").getInterior().setColor(Color.FromArgb(155, 194, 230)); sheet.getRange("A5:C5").setHorizontalAlignment(HorizontalAlignment.Center); sheet.getRange("A6:A13").setValue(new Object[][]{ {"Great tacos with fresh ingredients! Definitely coming back for more."}, {"The service was slow, but the food was worth the wait."}, {"Not impressed. The tacos were bland and lacked flavor."}, {"Amazing variety of salsas and toppings. Loved it!"}, {"The truck was clean and the staff was friendly."}, {"Overpriced for the portion size. Won't be returning."}, {"The tortillas were soggy and the meat was dry."}, {"Best taco truck in town! Highly recommend the carne asada."} }); // Define AI formula; the concatenated prompt is: "Evaluate these reviews [values in A6:A13], based on these categories [values in B5:C5]" sheet.getRange("B6").setFormula2("=AI.QUERY(\"evaluate these reviews\", A6:A13, \"based on these categories\",B5:C5)"); sheet.getRange("A6:C13").getFont().setSize(11); sheet.getRange("A6:C13").setHorizontalAlignment(HorizontalAlignment.Center); sheet.getRange("A6:C13").getBorders().setLineStyle(BorderLineStyle.Medium); sheet.getRange("A6:C13").getBorders().setColor(Color.FromArgb(200, 200, 200)); // The AI function works as an asynchronous calculation function; you need to wait for its calculation to complete. workbook.calculate(); workbook.waitForCalculationToFinish(); // Set the page to print as a single page. sheet.getPageSetup().setFitToPagesTall(1); sheet.getPageSetup().setFitToPagesWide(1); sheet.getPageSetup().setIsPercentScale(false); // Save as PDF file.workbook.save("AIQueryDataAnalysis.pdf"); ``` ```java /** * Implementation of IAIModelRequestHandler for OpenAI API. */ public class OpenAIModelRequestHandler implements IAIModelRequestHandler { private String _apiEndpoint; private String _apiKey; private String _model; public OpenAIModelRequestHandler(String apiEndpoint, String apiKey, String model) { if (apiEndpoint == null || apiEndpoint.trim().isEmpty()) throw new IllegalArgumentException("API endpoint cannot be null or empty."); if (apiKey == null || apiKey.trim().isEmpty()) throw new IllegalArgumentException("API key cannot be null or empty."); _apiEndpoint = apiEndpoint.replaceAll("/$", ""); _apiKey = apiKey; _model = model; } @Override public CompletableFuture sendRequestAsync(AIModelRequest request) { CompletableFuture result = new CompletableFuture<>(); if (request == null) { AIModelResponse modelResponse = new AIModelResponse(); modelResponse.setSuccess(false); System.err.println("Request cannot be null."); result.complete(modelResponse); return result; } OpenAIClientAsync openAIClient = OpenAIOkHttpClientAsync.builder() .apiKey(_apiKey) .baseUrl(_apiEndpoint) .build(); ChatCompletionCreateParams.Builder builder = ChatCompletionCreateParams.builder(); for (AIMessage item : request.getMessages()) { switch (item.getRole().toLowerCase()) { case "system": builder.addSystemMessage(item.getContent()); break; case "user": builder.addUserMessage(item.getContent()); break; default: throw new RuntimeException("Unknown message role:" + item.getRole()); } } builder.model(_model); ChatCompletionCreateParams params = builder.build(); CompletableFuture chatCompletion = openAIClient.chat().completions().create(params); chatCompletion.whenComplete((response, exception) -> { try { if (exception != null) { AIModelResponse errorResponse = new AIModelResponse(); errorResponse.setSuccess(false); System.err.println("An error occurred: " + exception.getMessage()); result.complete(errorResponse); } else { if (response != null && !response.choices().isEmpty()) { StringBuilder contentBuilder = new StringBuilder(); for (ChatCompletion.Choice choice : response.choices()) { choice.message(); if (choice.message().content().isPresent()) { contentBuilder.append(choice.message().content().get()); } } AIModelResponse successResponse = new AIModelResponse(); successResponse.setSuccess(true); successResponse.setContent(contentBuilder.toString()); result.complete(successResponse); } else { AIModelResponse noContentResponse = new AIModelResponse(); noContentResponse.setSuccess(false); System.err.println("No content received from the model."); result.complete(noContentResponse); } } } finally { try { openAIClient.close(); } catch (Exception e) { System.err.println("Error closing OpenAI client: " + e.getMessage()); } } }); return result; } } ``` The output is shown below: ![image](https://cdn.mescius.io/document-site-files/images/7466bdc8-02f8-494c-b60a-49ddbe7c10d4/image.6dd837.png?width=950) **Example 2** The following example shows how to automatically generate text content in the spreadsheet using the AI.QUERY function. ```java // To use this example, add the following dependency to your project: com.openai:openai-java:4.6.1 // Configure the model request handler and choose different large model providers as needed. Here the example uses OpenAI GPT-4.1; replace with your API key when using. Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.openai.com/v1", "sk-xxxx", "gpt-4.1")); // DeepSeek model. // Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://api.deepseek.com/v1", "sk-xxxx", "deepseek-chat")); // Qwen model. // Workbook.setAIModelRequestHandler(new OpenAIModelRequestHandler("https://dashscope.aliyuncs.com/compatible-mode/v1", "sk-xxxx", "qwen-plus")); // Initialize the workbook and set data.Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getWorksheets().get(0); sheet.getColumns().get(0).setColumnWidth(28); sheet.getColumns().get(1).setColumnWidth(35); sheet.getColumns().get(2).setColumnWidth(18); sheet.getColumns().get(3).setColumnWidth(18); sheet.getColumns().get(4).setColumnWidth(25); sheet.getRange("A1:D1").merge(); sheet.getRange("A1").setValue("Example 2: Content Generation - Ice Cream Flavors"); sheet.getRange("A1").getFont().setBold(true); sheet.getRange("A1").getFont().setSize(14); sheet.getRange("A1").getFont().setColor(Color.GetWhite()); sheet.getRange("A1").getInterior().setColor(Color.FromArgb(90, 126, 158)); sheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Center); sheet.getRange("A1").setVerticalAlignment(VerticalAlignment.Center); sheet.getRange("A1").setRowHeight(30); // Define AI formula to generate 5 unique ice cream flavors. String QueryFormula3 = "=AI.QUERY(\"Generate 5 unique ice cream flavors, arranged vertically\")"; sheet.getRange("A3").setValue("Query:"); sheet.getRange("A3").getFont().setBold(true); sheet.getRange("A3").getFont().setSize(11); sheet.getRange("A3").getInterior().setColor(Color.FromArgb(217, 227, 242)); sheet.getRange("B3:D3").merge(); sheet.getRange("B3").setValue(QueryFormula3); sheet.getRange("B3").getFont().setItalic(true); sheet.getRange("B3").getFont().setColor(Color.FromArgb(68, 114, 196)); sheet.getRange("A5").setValue("Generated Flavors:"); sheet.getRange("A5").getFont().setBold(true); sheet.getRange("A5").getFont().setSize(11); sheet.getRange("A5").getInterior().setColor(Color.FromArgb(155, 194, 230)); sheet.getRange("B5").setFormula2(QueryFormula3); sheet.getRange("B5:B5").getFont().setItalic(true); // The AI function works as an asynchronous calculation function; you need to wait for its calculation to complete. workbook.calculate(); workbook.waitForCalculationToFinish(); // Set the page to print as a single page. sheet.getPageSetup().setFitToPagesTall(1); sheet.getPageSetup().setFitToPagesWide(1); sheet.getPageSetup().setIsPercentScale(false); // Save as PDF file. workbook.save("AIQueryTextGeneration.pdf"); ``` The output is shown below: ![image](https://cdn.mescius.io/document-site-files/images/7466bdc8-02f8-494c-b60a-49ddbe7c10d4/image.bbe9cb.png)