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.

Spreadsheets in TX Text Control

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.

Spreadsheets in TX Text Control

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.

// load the document part names from the spreadsheet document
var documentPartNames = LoadSettings.GetDocumentPartNames("spreadsheet.xlsx", StreamType.SpreadsheetML);
// load the first sheet
var ls = new LoadSettings() {
DocumentPartName = documentPartNames[0]
};
var dataStructure = "";
// handle the TableCreated event
textControl1.TableCreated += (sender, e) => TextControl1_TableCreated(out dataStructure, sender, e);
// load the document
textControl1.Selection.Load("spreadsheet.xlsx", StreamType.SpreadsheetML, ls);
Debug.WriteLine(dataStructure);
view raw test.cs hosted with ❤ by GitHub

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.

Spreadsheets in TX Text Control

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.

private void TextControl1_TableCreated(out string dataStructure, object sender, TableEventArgs e) {
List<ApplicationField> applicationFields = new List<ApplicationField>();
// adapt the table size to the page width
e.Table.AdaptSize(textControl1);
// loop through all cells
foreach (TableCell cell in e.Table.Cells) {
// if the cell is not a formula, has a length and is not empty
if (cell.Formula == "" &&
cell.Length != -1 &&
cell.Text != "" &&
cell.CellFormat.TextType != TextType.Standard) {
cell.Select();
// create a new merge field
MergeField mf = new MergeField();
mf.Text = "«" + cell.Text + "»"; // set the field text
cell.Text = "";
// get the text of the previous cell in the first column
// and make it unique by adding the column number
var previousCellText = e.Table.Cells.GetItem(cell.Row, 1).Text + "_" + cell.Column;
// remove spaces from previousCellText and convert to title case
mf.Name = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ToTitleCase(previousCellText.ToLower()).Replace(" ", "");
// add the merge field to the document
textControl1.Select(cell.Start - 1, 0);
textControl1.ApplicationFields.Add(mf.ApplicationField);
applicationFields.Add(mf.ApplicationField);
}
}
dataStructure = GetMergeFields(applicationFields);
}
// function to return a json structure from an array of merge fields
private string GetMergeFields(List<ApplicationField> applicationFields) {
var fields = new List<string[]>();
foreach (ApplicationField field in applicationFields) {
fields.Add(new string[] { field.Name, field.Text });
}
return JsonConvert.SerializeObject(fields);
}
view raw test.cs hosted with ❤ by GitHub

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

[
[
"_2",
"«2022»"
],
[
"_3",
"«2021»"
],
[
"Cash_2",
"«$13,700.00»"
],
[
"Cash_3",
"«$143,016.00»"
],
[
"FederalFundsSold_2",
"«$12,683.00»"
],
[
"FederalFundsSold_3",
"«$13,781.00»"
],
[
"Deposits_2",
"«$8,259.00»"
],
[
"Deposits_3",
"«$569,725.00»"
],
[
"Securities_2",
"«$196,787.00»"
],
[
"Securities_3",
"«$279,016.00»"
],
[
"Loans_2",
"«$131,188.00»"
],
[
"Loans_3",
"«$313,238.00»"
],
[
"LoansReceivable_2",
"«$1,510,178.00»"
],
[
"LoansReceivable_3",
"«$1,205,785.00»"
],
[
"Acl_2",
"«$89,757.00»"
],
[
"Acl_3",
"«$95,789.00»"
],
[
"OfficeEquipment_2",
"«$21,105.00»"
],
[
"OfficeEquipment_3",
"«$72,273.00»"
],
[
"Stock_2",
"«$78,357.00»"
],
[
"Stock_3",
"«$22,238.00»"
],
[
"LifeInsurance_2",
"«$86,443.00»"
],
[
"LifeInsurance_3",
"«$64,568.00»"
],
[
"RealEstate_2",
"«$14,511.00»"
],
[
"RealEstate_3",
"«$148,000.00»"
],
[
"PrepaidExpenses_2",
"«$59,783.00»"
],
[
"PrepaidExpenses_3",
"«$45,148.00»"
]
]
view raw test.json hosted with ❤ by GitHub