With the advancement of AI technology, we are entering a new era where the capabilities of Excel can be revolutionized and expanded through artificial intelligence. Leveraging AI technology, we can reconstruct Excel's routine functions, such as merging data from multiple ranges, intelligently populating data, and even exploring intelligent generation strategies based on existing content, further enabling the capability to directly transform textual descriptions into visual representations. This can significantly enhance efficiency and reduce the burden of manual operations for users. This article will explore how to rewrite Excel content using Spire.XLS AI.

• Merge Data from Multiple Ranges
• Fill Excel with Data Extracted from Specified Range
• Generate Data based on Existing Content
• Generate Images based on Text Descriptions

Install Spire.XLS for .NET

The Excel AI integrated into Spire.XLS for .NET package, hence to begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Request a License Key of AI Product

A license key is required to run Spire.XLS AI, please contact our sales department (sales@e-iceblue.com) to request one.

Use AI to Rewrite Excel

Spire.XLS AI provides the ExcelAI class, which empowers users with the ability to intelligently rewrite Excel worksheets. Below is an overview of the key methods involved in implementing this functionality:

• ExcelMerge(CellRange descRange, List<CellRange> srcRanges, CellRange titleRange) :This method is used to merges the data from multiple ranges into a destination range with the given title range.
  • Please note that, to ensure the AI can correctly identify and match data across different source cell ranges, title within each range intended for merging should remain consistent or have clear correspondences. If titles within the source data ranges are inconsistent or missing, it could lead to the AI being unable to accurately map the data, thus impacting the accuracy of the merging outcome.
• ExcelFill(CellRange dataRange, CellRange descRange, bool containTitle, List<int> fillColumnIndexs, List<string> keys = null): This method is used to fill the Excel worksheet with data extracted from the specified data range.
  • The "descRange" parameter represents the description range, which has a description corresponding to the source data to ensure that AI can intelligently match and fill in.
  • The "containTitle" boolean indicates whether the data range contains a title row.
  • The "fillColumnIndices" parameter represents the index of the data source column to be filled.
  • The "keys" parameter is an optional parameter used to associate and match the data during the filling process, when provided, the ExcelFill method utilizes these key values for data correspondence, if null, the method will automatically identify and associate data based on the description information within the descRange.
• ExcelGenerate(List<CellRange> srcRanges): This method is used to analyze the content and context within cell ranges and leverage artificial intelligence technology to generate corresponding calculations, aggregations, or other operations based on natural language instructions. ExcelAI class will recognize and parse the natural language instructions within the markers enclosed in <ai>...</ai>.
• ImageGenerate(string description): This method is designed to intelligently create images that align with natural language text descriptions provided as input. It further extends its functionality through an enhanced overloaded interface, namely ImageGenerate(String description, ImageGenerationModel model, ImageGenerationSize size), which allows users to specify the image model and size.

Merge Data from Multiple Ranges in C#

The following steps introduce how to merge data from multiple ranges in worksheet:

• Create a Workbook class instance.
• Load an Excel document using Workbook.LoadFromFile() method.
• Create a new Workbook class instance and fill data in worksheet.
• Create an ExcelAI class instance.
• Reset the user's all chat history by ExcelAI.ResetUserHistory() method.
• Create a list and store multiple ranges that need to be merged.
• Specify the destination range and title range.
• Merge the data from the ranges stored in list using ExcelAI.ExcelMerge() method.
• Save the workbook using Workbook.SaveToFile () method.

using Spire.Xls;
using Spire.Xls.AI;
using System.Collections.Generic;

// Create a new Workbook instance and load source Excel file
Workbook wb1 = new Workbook();
wb1.LoadFromFile("MergeSource.xlsx");

// Create another new instance of Workbook
Workbook wb2 = new Workbook();

// Access the first Worksheet in wb2
Worksheet sheet = wb2.Worksheets[0];

// Fill data into cells
sheet.Range["A1"].Text = "Name"; 
sheet.Range["B1"].Text = "Capital";
sheet.Range["C1"].Text = "Continent"; 
sheet.Range["D1"].Text = "Area";
sheet.Range["E1"].Text = "Population";
sheet.Range["A2"].Text = "Argentina";
sheet.Range["B2"].Text = "Kingston"; 
sheet.Range["C2"].Text = "North America"; 
sheet.Range["D2"].NumberValue = 11424; 
sheet.Range["E2"].NumberValue = 2500000;

// Create a new ExcelAI instance
ExcelAI excelAI = new ExcelAI();

// Reset the user's all chat history
excelAI.ResetUserHistory();

// Create a list of CellRange objects to specify the source data ranges to merge
var srcRanges = new List();
srcRanges.Add(sheet["A1:E2"]); 
srcRanges.Add(wb1.Worksheets[0].Range["A1:E7"]);
srcRanges.Add(wb1.Worksheets[1].Range["A1:E2"]);

// Specify the destination range in wb1 where merged data will be placed
var descRange = wb1.Worksheets[0].Range["A10"];

// Specify the title range for merging purposes
var titleRange = wb1.Worksheets[0].Range["A1:E1"];

// Merge the data from source ranges into the destination range with the given title range
string jsonStr = excelAI.ExcelMerge(descRange, srcRanges, titleRange);

// Save the Excel file
wb1.SaveToFile("Merge_out.xlsx", ExcelVersion.Version2016);

Fill Excel with Data Extracted from Specified Range in C#

The following steps introduce how to intelligently fill excel with data extracted from specified range:

• Create a Workbook class instance.
• Load an Excel document using Workbook.LoadFromFile() method.
• Get the first worksheet.
• Specify the data range and description range within the worksheet.
• Create an ExcelAI class instance.
• Reset the user's all chat history by ExcelAI.ResetUserHistory() method.
• Fill the worksheet based on given data and descriptions using ExcelAI.ExcelFill() method.
• Save the workbook using Workbook.SaveToFile () method.

using Spire.Xls;
using Spire.Xls.AI;
using System.Collections.Generic;

// Create a new Workbook instance 
Workbook wb = new Workbook();

// Load an Excel file from disk
wb.LoadFromFile("FillSource.xlsx");

// Access the first worksheet
var worksheet = wb.Worksheets[0];

// Define the data range
var dataRange = worksheet.Range["A1:E11"];

// Specify the description range 
var descRange = worksheet.Range["B14"];

// Create a new ExcelAI instance 
ExcelAI excelAI = new ExcelAI();

// Reset the user's all chat history
excelAI.ResetUserHistory();

// Intelligently fill the worksheet based on given data and descriptions
string jsonStr = excelAI.ExcelFill(dataRange, descRange, true, new List() { 0, 1,2 }, new List() { "Name", "Capital", "Continent" });

// Save the Excel file
wb.SaveToFile("Fill_out.xlsx", ExcelVersion.Version2016);

Generate Data based on Existing Content in C#

The following steps introduce how to generate data based on existing content in worksheet:

• Create a Workbook class instance.
• Load an Excel document using Workbook.LoadFromFile() method.
• Get the first worksheet.
• Get the index of last data row and last data column.
• Fill text and insert AI instructions in different cells to return the total number of students, the sum formula and the ranking information.
• Create a list and store allocated ranges from worksheet.
• Create an ExcelAI class instance.
• Generate intelligent processing information using ExcelAI.ExcelGenerate() method.
• Save the workbook using Workbook.SaveToFile () method.

using Spire.Xls;
using Spire.Xls.AI;
using System.Collections.Generic;

// Create a new Workbook instance 
Workbook wb = new Workbook();

// Load an Excel file from disk
wb.LoadFromFile("StudentScoreCard.xlsx");

// Get the first worksheet
Worksheet worksheet = wb.Worksheets[0];

// Calculate the index of the last data row
int lastrow = worksheet.LastDataRow;

// Calculate the index of the last data column 
int lastcol = worksheet.LastDataColumn;

// Fill text in specified cell
worksheet.Range[lastrow + 1, 1].Text = "Total:";

// Insert an AI instruction in cell to return the total number of students
worksheet.Range[lastrow + 1, 2].Text = "Return total number of student";

// Fill text in specified cell
worksheet.Range[2, lastcol + 1].Text = "Sum:";
worksheet.Range[2, lastcol + 2].Text = "Rank:";

for (int i = 3; i <= lastrow; i++)
{
    // Insert AI instructions in different cells to return the sum formula for this row's score and the ranking information for the student
    worksheet.Range[i, lastcol + 1].Text = "Return the summation formula";
    worksheet.Range[i, lastcol + 2].Text = "Return the ranking of the student";
}

// Create a list to store ranges
List ranges = new List();

// Add entire allocated range in list
ranges.Add(worksheet.AllocatedRange);

// Create a new ExcelAI instance 
ExcelAI excelAI = new ExcelAI();

// Generate intelligent processing information based on the provided cell ranges
string jsonStr = excelAI.ExcelGenerate(ranges);

// Save the Excel file
wb.SaveToFile("Generate_out.xlsx", ExcelVersion.Version2016);

Generate Images based on Text Description in C#

The following steps introduce how to generate images based on text description:

• Create an ExcelAI class instance.
• Generate image streams based on given descriptions using ExcelAI.ImageGenerate() method.
• Create a list to store the image streams.
• Create a Workbook class instance.
• Iterate over image stream from list and write them to PNG.
• Create empty worksheets and add the image streams in specified cells.
• Save the workbook using Workbook.SaveToFile () method.

using Spire.AI.Api;
using Spire.Xls;
using Spire.Xls.AI;
using System;
using System.Collections.Generic;
using System.IO;

// Define three text descriptions for generating images
string description1 = "A little dog running in the countryside";
string description2 = "A road full of beautiful flowers";
string description3 = "Students learning in the classroom";

// Create a new ExcelAI instance
ExcelAI excelAI = new ExcelAI();

// Generate image streams based on the descriptions
var imageStream1 = excelAI.ImageGenerate(description1, ImageGenarationModel.STABLE_DIFFUSION_XL_1024_V1_0, ImageGenarationSize.I1024x1024);
var imageStream2 = excelAI.ImageGenerate(description2, ImageGenarationModel.STABLE_DIFFUSION_XL_1024_V1_0, ImageGenarationSize.I1024x1024);
var imageStream3 = excelAI.ImageGenerate(description3);

// Create a list to store the generated image streams
var imageStreams = new List();

// Add the generated image streams to the list
imageStreams.Add(imageStream1);
imageStreams.Add(imageStream2);
imageStreams.Add(imageStream3);

// Create a new Workbook instance and clear any existing worksheets
Workbook wb = new Workbook();
wb.Worksheets.Clear();

// Iterate over each image stream in the list
for (int i = 0; i < imageStreams.Count; i++)
{
    // Generate a unique filename for the image
    string randomFileName = Guid.NewGuid().ToString() + ".png";

    // Create a new file with the generated filename and write the image stream to it
    using (Stream outputStream = File.Create(randomFileName))
    {
        byte[] buffer = new byte[4096];
        int bytesRead;
        while ((bytesRead = imageStreams[i].Read(buffer, 0, buffer.Length)) > 0)
        {
            outputStream.Write(buffer, 0, bytesRead);
        }
    }

    // Create a new empty worksheet in the workbook
    Worksheet worksheet = wb.CreateEmptySheet(String.Format("ImageSheet{0}",i+1));

    // Add the image stream to the worksheet at cell (1, 1)
    worksheet.Pictures.Add(1, 1, imageStreams[i]);
}

// Save the Excel file
wb.SaveToFile("GenerateImage_out.xlsx", ExcelVersion.Version2016);

Excel documents, being a widely-used electronic spreadsheet across the globe, often involve multi-language collaboration in various scenarios. Against this backdrop, we introduce AI-powered translation technology, which not only accurately identifies and translates text content but also preserves the original data formats and structures, significantly enhancing work efficiency and accuracy while reducing communication costs incurred by language differences. In this article, we will introduce how to translate Excel using Spire.XLS AI.

• Translate Contents from Worksheets
• Translate Contents from Ranges

Install Spire.XLS for .NET

The Excel AI integrated into Spire.XLS for .NET package, hence to begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Request a License Key of AI Product

A license key is required to run Spire.XLS AI, please contact our sales department (sales@e-iceblue.com) to request one.

Use AI to Translate Excel

Spire.XLS AI provides the ExcelAI class, which supports intelligent translation of either all content within a worksheet or specific ranges within specific worksheets. Below is an overview of the key methods involved in implementing this functionality:

• UploadWorkbook(Workbook wb): This method is used to upload a Workbook object processed by Spire.XLS to the AI server, facilitating the integration of Excel content with the AI system's data.
• ExcelTranslate(List<CellRange> srcRanges, string language): This method is designed to translate the content within specified cell ranges from worksheets into the specified target language.
• ExcelTranslate(List<Worksheet> srcWorksheets, string language, bool translateSheetName = true): This method is responsible for translating all content across a list of source worksheets, optionally including worksheet names, into the specified target language.

Translate Contents from Worksheets in C#

The following steps introduce how to translate the content within worksheets:

• Create a Workbook class instance.
• Load an Excel document using Workbook.LoadFromFile() method.
• Create an ExcelAI class instance.
• Upload the workbook to AI system using ExcelAI.UploadWorkbook() method.
• Create a list and store all worksheets from workbook.
• Translate content within worksheets into specified language using ExcelTranslate(List<Worksheet> srcWorksheets, string language, bool translateSheetName = true) method.
• Save the translated workbook using Workbook.SaveToFile () method.

using Spire.Xls;
using Spire.Xls.AI;
using System.Collections.Generic;

// Define the file path of the Excel document 
string inputfile = "Input.xlsx";

// Create a new instance of the Workbook
Workbook wb = new Workbook();

// Load the Excel file
wb.LoadFromFile(inputfile);

// Create a new instance of the ExcelAI
ExcelAI excelAI = new ExcelAI();

// Upload the workbook to AI system
excelAI.UploadWorkbook(wb);

// Create a list to store worksheets
List worksheets = new List();

// Iterate through each worksheet
foreach (Worksheet worksheet in wb.Worksheets)
{
    // Add the worksheet to the list
    worksheets.Add(worksheet);
}

// Translate the content of worksheets into Spanish
excelAI.ExcelTranslate(worksheets, "spanish");

// Save the translated workbook to a new Excel file
wb.SaveToFile("output.xlsx", ExcelVersion.Version2016);

Translate Contents from Ranges in C#

The following steps introduce how to translate the content within specified ranges:

• Create a Workbook class instance.
• Load an Excel document using Workbook.LoadFromFile() method.
• Create an ExcelAI class instance.
• Upload the workbook to AI system using ExcelAI.UploadWorkbook() method.
• Create a list to store cell ranges.
• Get the specified worksheet and add some cell ranges into the list.
• Translate content within the ranges into specified language using ExcelTranslate(List<CellRange> srcRanges, string language) method.
• Save the translated workbook using Workbook.SaveToFile () method.

using Spire.Xls;
using Spire.Xls.AI;
using System.Collections.Generic;

// Define the file path of the Excel document 
string inputfile = "Input.xlsx";

// Create a new instance of the Workbook
Workbook wb = new Workbook();

// Load the Excel file
wb.LoadFromFile(inputfile);

// Create a new instance of the ExcelAI
ExcelAI excelAI = new ExcelAI();

// Upload the workbook to AI system
excelAI.UploadWorkbook(wb);

// Get the first worksheet
Worksheet worksheet = wb.Worksheets[0];

// Create a list to store cell ranges
List ranges = new List();

// Add two ranges to the list
ranges.Add(worksheet.Range["B1"]);
ranges.Add(worksheet.Range["H2"]);

// Translate the content of ranges into Chinese
excelAI.ExcelTranslate(ranges, "chinese");

// Save the translated workbook to a new Excel file
wb.SaveToFile("output.xlsx", ExcelVersion.Version2016);