How to Convert Embedded Excel Sheet to Word Table in C#, VB.NET

In our daily work, we may receive Word documents that will sometimes contain embedded Excel object (sheet) and we need to convert embedded Excel sheet to Word table so that we can easily change the date or format the table with style. In this article, you will learn how to convert embedded Excel sheet to Word table using Spire.Doc and Spire.XLS in C#, VB.NET.

Firstly, you need to download Spire.Office because Spire.Doc and Spire.XLS will be used in the same program. Add Spire.Doc.dll and Spire.XLS.dll as references in your VS project. Then follow the program guidance below to finish this work.

Step 1: Create a new Word document, load the sample file. Get the paragraph that contains the Excel object from the section. Initialize a new datatable.

Document doc = new Document("Sample.docx", Spire.Doc.FileFormat.Docx2010);
Section section = doc.Sections[0];
Paragraph para = section.Paragraphs[2];
DataTable dt = new DataTable();

Step 2: Traverse every DocumentObject in the paragraph, use IF statement to test if DocumentObject is OLE object, use another IF statement to test if OLE object type is Excel.Sheet.12. If yes, save the data of OLE object to a workbook through LoadFromStrem(). Then export data from worksheet to datatable.

foreach (DocumentObject obj in para.ChildObjects)
{
    if (DocumentObjectType.OleObject == obj.DocumentObjectType)
    {
        DocOleObject dObj = obj as DocOleObject;
        if (dObj.ObjectType == "Excel.Sheet.12")
        {
            Workbook wb = new Workbook();
            wb.LoadFromStream(new MemoryStream(dObj.NativeData));
            Worksheet ws = wb.Worksheets[0];
            dt = ws.ExportDataTable(ws.AllocatedRange, false);
        }
    }
}

Step 3: Create a new Word table and set row number and column number according to rows and columns of datatable. Export data from datatable to Word table.

Table table = section.AddTable(true);
 table.ResetCells(dt.Rows.Count, dt.Columns.Count);

 for (int i = 0; i < dt.Rows.Count; i++)
 {
     for (int j = 0; j < dt.Columns.Count; j++)
     {
         string text = dt.Rows[i][j] as string;
         table.Rows[i].Cells[j].AddParagraph().AppendText(text);
     }
 }

Step 4: Save the file.

doc.SaveToFile("Result.docx", Spire.Doc.FileFormat.Docx2010);

Result:

How to Convert Embedded Excel Sheet to Word Table in C#, VB.NET

Full Code:

[C#]
Document doc = new Document("Sample.docx", Spire.Doc.FileFormat.Docx2010);
Section section = doc.Sections[0];
Paragraph para = section.Paragraphs[2];
DataTable dt = new DataTable();

foreach (DocumentObject obj in para.ChildObjects)
{
    if (DocumentObjectType.OleObject == obj.DocumentObjectType)
    {
        DocOleObject dObj = obj as DocOleObject;
        if (dObj.ObjectType == "Excel.Sheet.12")
        {
            Workbook wb = new Workbook();
            wb.LoadFromStream(new MemoryStream(dObj.NativeData));
            Worksheet ws = wb.Worksheets[0];
            dt = ws.ExportDataTable(ws.AllocatedRange, false);
        }
    }
}

Table table = section.AddTable(true);
table.ResetCells(dt.Rows.Count, dt.Columns.Count);

for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        string text = dt.Rows[i][j] as string;
        table.Rows[i].Cells[j].AddParagraph().AppendText(text);
    }
}

doc.SaveToFile("Result.docx", Spire.Doc.FileFormat.Docx2010);
[VB.NET]
Dim doc As New Document("Sample.docx", Spire.Doc.FileFormat.Docx2010)
Dim section As Section = doc.Sections(0)
Dim para As Paragraph = section.Paragraphs(2)
Dim dt As New DataTable()

For Each obj As DocumentObject In para.ChildObjects
	If DocumentObjectType.OleObject = obj.DocumentObjectType Then
		Dim dObj As DocOleObject = TryCast(obj, DocOleObject)
		If dObj.ObjectType = "Excel.Sheet.12" Then
			Dim wb As New Workbook()
			wb.LoadFromStream(New MemoryStream(dObj.NativeData))
			Dim ws As Worksheet = wb.Worksheets(0)
			dt = ws.ExportDataTable(ws.AllocatedRange, False)
		End If
	End If
Next

Dim table As Table = section.AddTable(True)
table.ResetCells(dt.Rows.Count, dt.Columns.Count)

For i As Integer = 0 To dt.Rows.Count - 1
	For j As Integer = 0 To dt.Columns.Count - 1
		Dim text As String = TryCast(dt.Rows(i)(j), String)
		table.Rows(i).Cells(j).AddParagraph().AppendText(text)
	Next
Next

doc.SaveToFile("Result.docx", Spire.Doc.FileFormat.Docx2010)