Word and Excel are two completely different file types. Word documents are used to write essays, letters or create reports, while Excel documents are used to save data in tabular form, make charts, or perform mathematical calculations. It is not recommended to convert a complex Word document to an Excel spreadsheet because Excel can hardly render content according to its original layout in Word.
However, if your Word document is primarily composed of tables and you want to analyze the table data in Excel, you can use Spire.Office for .NET to convert Word to Excel while maintaining good readability.
Install Spire.Office for .NET
To begin with, you need to add the DLL files included in the Spire.Office for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.Office
Convert Word to Excel in C# and VB.NET
This scenario actually uses two libraries in the Spire.Office package. They’re Spire.Doc for .NET and Spire.XLS for .NET. The former is used to read and extract content from a Word document, and the latter is used to create an Excel document and write data in the specific cells. To make this code example easy to understand, we created the following three custom methods that preform specific functions.
- ExportTableInExcel() - Export data from a Word table to specified Excel cells.
- CopyContentInTable() - Copy content from a table cell in Word to an Excel cell.
- CopyTextAndStyle() - Copy text with formatting from a Word paragraph to an Excel cell.
The following steps demonstrate how to export data from an entire Word document to a worksheet using Spire.Office for .NET.
- Create a Document object to load a Word file.
- Create a Worbbook object and add a worksheet named "WordToExcel" to it.
- Traverse though all the sections in the Word document, traverse through all the document objects under a certain section, and then determine if a document object is a paragraph or a table.
- If the document object is a paragraph, write the paragraph in a specified cell in Excel using CoypTextAndStyle() method.
- If the document object is a table, export the table data from Word to Excel cells using ExportTableInExcel() method.
- Auto fit the row height and column width in Excel so that the data within a cell will not exceed the bound of the cell.
- Save the workbook to an Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Xls;
using System;
using System.Drawing;
namespace ConvertWordToExcel
{
class Program
{
static void Main(string[] args)
{
//Create a Document object
Document doc = new Document();
//Load a Word file
doc.LoadFromFile(@"C:\Users\Administrator\Desktop\Invoice.docx");
//Create a Workbook object
Workbook wb = new Workbook();
//Remove the default worksheets
wb.Worksheets.Clear();
//Create a worksheet named "WordToExcel"
Worksheet worksheet = wb.CreateEmptySheet("WordToExcel");
int row = 1;
int column = 1;
//Loop through the sections in the Word document
foreach (Section section in doc.Sections)
{
//Loop through the document object under a certain section
foreach (DocumentObject documentObject in section.Body.ChildObjects)
{
//Determine if the object is a paragraph
if (documentObject is Paragraph)
{
CellRange cell = worksheet.Range[row, column];
Paragraph paragraph = documentObject as Paragraph;
//Copy paragraph from Word to a specific cell
CopyTextAndStyle(cell, paragraph);
row++;
}
//Determine if the object is a table
if (documentObject is Table)
{
Table table = documentObject as Table;
//Export table data from Word to Excel
int currentRow = ExportTableInExcel(worksheet, row, table);
row = currentRow;
}
}
}
//Auto fit row height and column width
worksheet.AllocatedRange.AutoFitRows();
worksheet.AllocatedRange.AutoFitColumns();
//Wrap text in cells
worksheet.AllocatedRange.IsWrapText = true;
//Save the workbook to an Excel file
wb.SaveToFile("WordToExcel.xlsx", ExcelVersion.Version2013);
}
//Export data from Word table to Excel cells
private static int ExportTableInExcel(Worksheet worksheet, int row, Table table)
{
CellRange cell;
int column;
foreach (TableRow tbRow in table.Rows)
{
column = 1;
foreach (TableCell tbCell in tbRow.Cells)
{
cell = worksheet.Range[row, column];
cell.BorderAround(LineStyleType.Thin, Color.Black);
CopyContentInTable(tbCell, cell);
column++;
}
row++;
}
return row;
}
//Copy content from a Word table cell to an Excel cell
private static void CopyContentInTable(TableCell tbCell, CellRange cell)
{
Paragraph newPara = new Paragraph(tbCell.Document);
for (int i = 0; i < tbCell.ChildObjects.Count; i++)
{
DocumentObject documentObject = tbCell.ChildObjects[i];
if (documentObject is Paragraph)
{
Paragraph paragraph = documentObject as Paragraph;
foreach (DocumentObject cObj in paragraph.ChildObjects)
{
newPara.ChildObjects.Add(cObj.Clone());
}
if (i < tbCell.ChildObjects.Count - 1)
{
newPara.AppendText("\n");
}
}
}
CopyTextAndStyle(cell, newPara);
}
//Copy text and style of a paragraph to a cell
private static void CopyTextAndStyle(CellRange cell, Paragraph paragraph)
{
RichText richText = cell.RichText;
richText.Text = paragraph.Text;
int startIndex = 0;
foreach (DocumentObject documentObject in paragraph.ChildObjects)
{
if (documentObject is TextRange)
{
TextRange textRange = documentObject as TextRange;
string fontName = textRange.CharacterFormat.FontName;
bool isBold = textRange.CharacterFormat.Bold;
Color textColor = textRange.CharacterFormat.TextColor;
float fontSize = textRange.CharacterFormat.FontSize;
string textRangeText = textRange.Text;
int strLength = textRangeText.Length;
ExcelFont font = cell.Worksheet.Workbook.CreateFont();
font.Color = textColor;
font.IsBold = isBold;
font.Size = fontSize;
font.FontName = fontName;
int endIndex = startIndex + strLength;
richText.SetFont(startIndex, endIndex, font);
startIndex += strLength;
}
if (documentObject is DocPicture)
{
DocPicture picture = documentObject as DocPicture;
cell.Worksheet.Pictures.Add(cell.Row, cell.Column, picture.Image);
cell.Worksheet.SetRowHeightInPixels(cell.Row, 1, picture.Image.Height);
}
}
switch (paragraph.Format.HorizontalAlignment)
{
case HorizontalAlignment.Left:
cell.Style.HorizontalAlignment = HorizontalAlignType.Left;
break;
case HorizontalAlignment.Center:
cell.Style.HorizontalAlignment = HorizontalAlignType.Center;
break;
case HorizontalAlignment.Right:
cell.Style.HorizontalAlignment = HorizontalAlignType.Right;
break;
}
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
