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:
Full Code:
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Xls;
using System.Data;
using System.IO;
namespace ApplyTableStyles
{
class Program
{
static void Main(string[] args)
{
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);
}
}
}
Imports Spire.Doc
Imports Spire.Doc.Documents
Imports Spire.Doc.Fields
Imports Spire.Xls
Imports System.Data
Imports System.IO
Namespace ApplyTableStyles
Class Program
Private Shared Sub Main(args As String())
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)
End Sub
End Class
End Namespace
