Import and Manipulate Excel Spreadsheets by Converting Cells into Merge Fields in C#

When you import spreadsheets into TX Text Control, the resulting tables retain not only their content, but also their formatting and formulas. Consider the following spreadsheet, which is intended to be added to a document as a table.

The table appears as shown in the screenshot below when this spreadsheet is loaded into the TX Text Control. You can see that the formulas as well as the cell type and cell format have been maintained.

In the following code, we want to process the table in order to realize these three objectives:

1. Adjust the table size to fit the available page width.
2. Convert all cells that contain numbers and do not contain formulas to merge fields.
3. To represent the structure required to merge the document, return a dummy JSON object.

First, we attach a TableCreated ╰ TX Text Control .NET Server for ASP.NET
╰ TXTextControl Namespace
╰ ServerTextControl Class
╰ TableCreated Event
Occurs after a new table has been created when loading a document which contains a table without an identifier. event to the TextControl or ServerTextControl ╰ TX Text Control .NET Server for ASP.NET
╰ TXTextControl Namespace
╰ ServerTextControl Class
The ServerTextControl class implements a component that provide high-level text processing features for server-based applications. class and load the spreadsheet document using the Selection.Load ╰ TX Text Control .NET Server for ASP.NET
╰ TXTextControl Namespace
╰ Selection Class
╰ Load Method
Exchanges the currently selected text with text in a certain format. method.

When the spreadsheet is loaded, a table is created and the TableCreated event is fired. To adjust the width of the table, we use the table extension method AdaptSize, which is discussed in another article.

Learn More

This article shows how to resize a table to fit the entire page by adjusting each column proportionally.

Auto-Sizing Tables Proportionally

In the screenshot below, you can see the imported table and the converted cells, which have been converted into merge fields.

After the table has been adjusted, we will loop through all the cells that are not empty, do not contain a formula, and are of the type number. The contents of these cells are then used as the text for a new merge field. The merge field is inserted into the cell to replace the cell contents. The name is taken from the first column. This is very specific to this example, but it gives an idea of what can be done to automate the recognition.

Finally, all added fields are returned as a JSON string. This provides a data dump file that contains the structure for a merge process.