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 event to the TextControl or ServerTextControl class and load the spreadsheet document using the Selection.Load 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);

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.

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);
}

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»"
   ]
]