TX Text Control can load spreadsheets from Microsoft Excel XLSX files and import them as fully formatted tables with compatible self-calculating formulas. The following screenshot shows a typical profit and loss statement with formatted table cells and a lot of logic in the form of formulas to calculate various costs and revenues.

Benefits and Use Cases

Excel spreadsheets are not page-oriented, as you can see if you have ever tried to print Excel documents. The tables span multiple pages, including horizontally, and it is difficult to get Excel spreadsheets into a formal document format such as a report. As the name "spreadsheet" implies, it comes from the way data is arranged in a format that resembles a "spread" of information across rows and columns on a sheet of paper.

In the past, accountants would use large physical sheets of paper to manually record and calculate numbers, spreading the data across rows and columns. This concept is not very useful when inserting these tables into a pixel-perfect report in letter or other standard format.

TX Text Control can load these spreadsheets and convert them into fully formatted tables that can be inserted into the text flow of a document.

Loading the Spreadsheet

When loading the document into TX Text Control using the Load method, the LoadSettings must be used to define the sheet of the document to be imported. The DocumentPartName property specifies the name of the part of the document to load. The available document parts or sheets can be obtained using the GetDocumentPartNames static method.

var availableSheets = LoadSettings.GetDocumentPartNames("pl.xlsx", StreamType.SpreadsheetML);

 LoadSettings loadSettings = new LoadSettings()
 {
     DocumentPartName = availableSheets[0]
 };

 textControl1.Load("pl.xlsx", TXTextControl.StreamType.SpreadsheetML, loadSettings);

The red circled sheet Sheet1 is loaded in the code below. When the spreadsheet is loaded into the TX Text Control, it is converted into a table that contains all of the cell formatting as well as the formulas for each of the cells. In the animated screenshot below, you can see that a cell value is manually changed to $2000 and the Total Sales cell is automatically updated.

TX Text Control also provides a full-featured ribbon tab that allows you to view and manipulate the imported formulas of all table cells. In the next screenshot, you can see the selected table cell B24 with the gross profit calculation formula and the cell number format set to a USD currency format.

Adjust Table Width

As you can see in the screenshot, the table is too large to fit on the page and overlaps the right margin. In the sample application, if you check the Adjust Table Size option, the imported table will be adjusted to fit the page. The table is resized to fit the page width and the cell widths are adjusted proportionally. In addition, if the table size is larger than the current page width, the page orientation of the current section is automatically changed to landscape.

This is done by calling the AdaptSize extension method on the created table.

var availableSheets = LoadSettings.GetDocumentPartNames("pl.xlsx", StreamType.SpreadsheetML);

 LoadSettings loadSettings = new LoadSettings()
 {
     DocumentPartName = availableSheets[0]
 };

 textControl1.Load("pl.xlsx", TXTextControl.StreamType.SpreadsheetML, loadSettings);

 if (cbAdaptSize.Checked)
 {
     TXTextControl.Table table = textControl1.Tables.GetItem();
     table.AdaptSize(textControl1, true);
 }

The AdaptSize method checks the available page width and calculates the percentage ratio that is used to scale all table cells accordingly. To calculate the required space (negative or positive), you need the total available width (maxWidth). This is the page size minus the left and right margins of the current section. currentWidth is calculated by summing all column widths in each row. The missing space is then the maximum width minus the current width of the table row (destinationWidth).

The following code is the complete implementation of the AdaptSize method.

public static void AdaptSize(this Table table, TextControl textControl, bool landscapeIfNecessary = true)
{
    if (table == null)
        return;

    textControl.PageUnit = MeasuringUnit.Twips;
    Section section = textControl.Sections.GetItem();

    // Calculate margins and available page width
    int CalculateAvailablePageWidth(Section sec)
    {
        var margins = sec.Format.PageMargins.Left + sec.Format.PageMargins.Right;
        return (int)(sec.Format.PageSize.Width - margins);
    }

    var availablePageWidth = CalculateAvailablePageWidth(section);

    // Calculate maximum table width based on the last column
    var lastColumn = table.Columns[table.Columns.Count];
    var maxTableWidth = lastColumn.Position + lastColumn.Width;

    // Switch to landscape if necessary
    if (landscapeIfNecessary && maxTableWidth > availablePageWidth)
    {
        section.Format.Landscape = true;
        section = textControl.Sections.GetItem();
        availablePageWidth = CalculateAvailablePageWidth(section);
        maxTableWidth = lastColumn.Position + lastColumn.Width;
    }

    int rowCount = table.Rows.Count;
    int colCount = table.Columns.Count;

    // Adjust the width of each cell
    for (int row = 1; row <= rowCount; row++)
    {
        int totalRowWidth = 0;

        // Calculate the total width of the row
        for (int col = 1; col <= colCount; col++)
        {
            totalRowWidth += table.Cells[row, col].Width;
        }

        // Calculate the difference between row width and available page width
        int widthDifference = totalRowWidth - availablePageWidth;

        // Adjust cell widths proportionally to fit within the available page width
        for (int col = 1; col <= colCount; col++)
        {
            var cell = table.Cells[row, col];
            float ratio = (float)cell.Width / totalRowWidth;
            cell.Width -= (int)(widthDifference * ratio);
        }
    }
}

Other Typical Use Cases

Another typical use case for importing Excel spreadsheets is to merge cell values with dynamic data. Therefore, the cell contents must be converted to merge fields that can be merged using the MailMerge class.

The following code converts all cells in rows 6 through 10 to merge fields and uses row 5 as the vertical reference row and column 1 as the horizontal reference column to assemble dynamic field names. These reference columns are removed from the cell conversion process.

table.ConvertCellsToMergeFields(textControl1, 1, 5, 6, 10);

The following code is the complete implementation of the ConvertCellsToMergeFields method.

public static void ConvertCellsToMergeFields(this Table table, TextControl textControl, int referenceColumn, int referenceRow, int fromRow, int toRow)
{
    static string CleanString(string input)
    {
        // Directly remove unwanted characters and replace spaces
        return Regex.Replace(input.Replace(" ", "_"), "[^a-zA-Z0-9_]", "");
    }

    // Validate the input table and bounds
    if (table == null || fromRow > toRow || fromRow < 1 || toRow > table.Rows.Count)
        return;

    int colCount = table.Columns.Count;

    for (int row = fromRow; row <= toRow; row++)
    {
        for (int col = 1; col <= colCount; col++)
        {
            // Skip reference row/column
            if (col == referenceColumn || row == referenceRow)
                continue;

            var cell = table.Cells[row, col];
            var referenceCellVertical = table.Cells[row, referenceColumn];
            var referenceCellHorizontal = table.Cells[referenceRow, col];

            // Combine and clean reference text
            string referenceCellText = CleanString(referenceCellVertical.Text + "_" + referenceCellHorizontal.Text);

            // Create and configure the merge field
            var mergeField = new TXTextControl.DocumentServer.Fields.MergeField
            {
                Name = referenceCellText,
                Text = cell.Text,
            };

            mergeField.ApplicationField.HighlightColor = System.Drawing.Color.FromArgb(0, 255, 255, 0);
            mergeField.ApplicationField.HighlightMode = HighlightMode.Always;

            // Clear cell text and insert the merge field
            cell.Text = string.Empty;
            textControl.InputPosition = new InputPosition(cell.Start - 1);
            textControl.ApplicationFields.Add(mergeField.ApplicationField);
        }
    }
}

Conclusion

TX Text Control can load spreadsheets from Microsoft Excel XLSX files and import them as fully formatted tables with compatible self-calculating formulas. The table can be resized to fit the page width and cell contents can be converted to merge fields for further processing.

Download the sample from GitHub and test it on your own.