How to Export Excel Data to Word Table Maintaining Formatting in C#

Sometimes office staff may need to export selected cell range from Excel file to Word document displaying as a Word table, like below. It is easy to complete this task by copy-and-paste. This article focuses on how to programmatically transfer formatted Excel data as Word table using Spire.Office.

This solution requires Spire.XLS and Spire.Doc to be referenced in the same project, please download Spire.Office and add the related DLLs from bin folder to .NET assemblies. Then, let's get started with the code.

How to Export Excel Data to Word Table Maintaining Formatting in C#

Step 1: Initialize an instance of Workbook and load a sample Excel file. Get the first worksheet from workbook.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\DatatableSample.xlsx");
Worksheet sheet = workbook.Worksheets[0];

Step 2: Create a new object of Document class, add a table to it, and set the rows and columns number based on the cells range that contains data.

Document doc = new Document();
Table table = doc.AddSection().AddTable(true);
table.ResetCells(sheet.LastRow, sheet.LastColumn);

Step 3: Fill the table with the data exported from the cells range and call a custom function CopyStype() to copy the font style and cell style from Excel to Word table.

for (int r = 1; r <= sheet.LastRow; r++)
{
    for (int c = 1; c <= sheet.LastColumn; c++)
    {
        CellRange xCell = sheet.Range[r, c];
        TableCell wCell = table.Rows[r - 1].Cells[c - 1];
        //fill data to word table
        TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
        //copy font and cell style from excel to word
        CopyStyle(textRange, xCell, wCell);
    }
}

Step 4: The custom function CopyStyle() is defined as below, which ensures the formatting of the data will be maintained while transferring the data.

private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
{
    //copy font stlye
    wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
    wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
    wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
    wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
    wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
    //copy backcolor
    wCell.CellFormat.BackColor = xCell.Style.Color;
    //copy text alignment
    switch (xCell.HorizontalAlignment)
    {
        case HorizontalAlignType.Left:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
            break;
        case HorizontalAlignType.Center:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
            break;
        case HorizontalAlignType.Right:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
            break;
    }
}

Step 5: Save and launch the Word document.

doc.SaveToFile("result.docx", Spire.Doc.FileFormat.Docx);
System.Diagnostics.Process.Start("result.docx");

Full Code:

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Doc.Formatting;
using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core;

namespace Xls2Doc
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize a workbook and load sample excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\DatatableSample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //initialize a word document
            Document doc = new Document();
            //add a table
            Table table = doc.AddSection().AddTable(true);
            table.ResetCells(sheet.LastRow, sheet.LastColumn);
            for (int r = 1; r <= sheet.LastRow; r++)
            {
                for (int c = 1; c <= sheet.LastColumn; c++)
                {
                    CellRange xCell = sheet.Range[r, c];
                    TableCell wCell = table.Rows[r - 1].Cells[c - 1];
                    //fill data to word table
                    TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
                    //copy font and cell style from excel to word
                    CopyStyle(textRange, xCell, wCell);
                }
            }
            //set column width of word table
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Rows[i].Cells.Count; j++)
                {
                    table.Rows[i].Cells[j].Width = 60f;
                }
            }
            doc.SaveToFile("result.docx", Spire.Doc.FileFormat.Docx);
            System.Diagnostics.Process.Start("result.docx");
        }
        private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
        {
            //copy font stlye
            wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
            wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
            wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
            wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
            wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
            //copy backcolor
            wCell.CellFormat.BackColor = xCell.Style.Color;
            //copy text alignment
            switch (xCell.HorizontalAlignment)
            {
                case HorizontalAlignType.Left:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
                    break;
                case HorizontalAlignType.Center:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
                    break;
                case HorizontalAlignType.Right:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
                    break;
            }
        }
    }
}