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:
- Adjust the table size to fit the available page width.
- Convert all cells that contain numbers and do not contain formulas to merge fields.
- To represent the structure required to merge the document, return a dummy JSON object.
First, we attach a Table ╰ 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 Server ╰ 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. ╰ 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); |
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.
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»" | |
] | |
] |