# Import and Manipulate Excel Spreadsheets by Converting Cells into Merge Fields in C# > You can import Microsoft Excel spreadsheets and convert them into smart tables, complete with formulas and formatting. This article shows how to import a spreadsheet and programmatically convert value cells into merge fields. - **Author:** Bjoern Meyer - **Published:** 2023-08-08 - **Modified:** 2025-11-16 - **Description:** You can import Microsoft Excel spreadsheets and convert them into smart tables, complete with formulas and formatting. This article shows how to import a spreadsheet and programmatically convert value cells into merge fields. - **4 min read** (695 words) - **Tags:** - ASP.NET - Spreadsheet - Excel - **Web URL:** https://www.textcontrol.com/blog/2023/08/08/import-and-manipulate-excel-spreadsheets-by-converting-cells-into-merge-fields/ - **LLMs URL:** https://www.textcontrol.com/blog/2023/08/08/import-and-manipulate-excel-spreadsheets-by-converting-cells-into-merge-fields/llms.txt - **LLMs-Full URL:** https://www.textcontrol.com/blog/2023/08/08/import-and-manipulate-excel-spreadsheets-by-converting-cells-into-merge-fields/llms-full.txt --- 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](https://s1-www.textcontrol.com/assets/dist/blog/2023/08/08/a/assets/spread1.webp "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](https://s1-www.textcontrol.com/assets/dist/blog/2023/08/08/a/assets/spread2.webp "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 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 ](https://www.textcontrol.com/blog/2021/08/05/auto-sizing-tables-proportionally/llms-full.txt) 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](https://s1-www.textcontrol.com/assets/dist/blog/2023/08/08/a/assets/spread3.webp "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 applicationFields = new List(); // 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 applicationFields) { var fields = new List(); 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»" ] ] ``` --- ## About Bjoern Meyer As CEO, Bjoern is the visionary behind our strategic direction and business development, bridging the gap between our customers and engineering teams. His deep passion for coding and web technologies drives the creation of innovative products. If you're at a tech conference, be sure to stop by our booth - you'll most likely meet Bjoern in person. With an advanced graduate degree (Dipl. Inf.) in Computer Science, specializing in AI, from the University of Bremen, Bjoern brings significant expertise to his role. In his spare time, Bjoern enjoys running, paragliding, mountain biking, and playing the piano. - [LinkedIn](https://www.linkedin.com/in/bjoernmeyer/) - [X](https://x.com/txbjoern) - [GitHub](https://github.com/bjoerntx) --- ## Related Posts - [Importing Excel XLSX Spreadsheets into TX Text Control in ASP.NET Core and Windows Forms in C#](https://www.textcontrol.com/blog/2023/02/07/importing-excel-xlsx-spreadsheets-into-tx-text-control/llms.txt) - [Loading and Processing Excel XLSX Spreadsheet Tables into TX Text Control using .NET C#](https://www.textcontrol.com/blog/2024/10/16/loading-and-processing-excel-spreadsheet-tables-into-tx-text-control-using-net-csharp/llms.txt)