How to Maintain Formatting of Cells when Copying Cells from Excel to Word

Introduction

When we create a report in Word document, we may need to insert Excel worksheet as OLE object in Word or copy only one part of the worksheet to Word, letting readers acquire some useful data without the need to open another file. This article focuses on how to export a range of cells as a Word table while maintaining the cells formatting.

Getting Started

This solution requires .NET Office component, which provides easy APIs to manipulate office files from code, to be added as reference in .NET assemblies. Additionally we should import following namespaces at the beginning of the project.

API reference: http://www.nuget.org/packages/Spire.Office/

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Xls;

Using the code

Step 1: Create an object of Workbook and load an Excel file by path.

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

Step 2: Construct a Word document with template, add a table to it and set the rows/columns number based one the data range of Excel sheet. The sheet.LastRow represents the last row that is filled with data, the sheet.LastColumn represent the last column that contains data. You can change the two arguments according to your specific requirement.

Document doc = new Document(@"..\..\Template.docx");
Table table = doc.Sections[0].AddTable(true);
table.ResetCells(sheet.LastRow, sheet.LastColumn);
table.TableFormat.HorizontalAlignment = RowAlignment.Center;

Step 3: Export data from Excel cell range to Word table, copying the cell formatting using a custom function CopyStyle().

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];
        TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
        CopyStyle(textRange, xCell, wCell); //copy format
    }
}

private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
{
    //copy font style
    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 cell color
    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;
    }
}

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