当你在制作公司月度支出的 Word 报表时，你可能需要从 Excel 表格中复制财务数据到该报表，这样可以让别人直接在该报表里查看相关数据而无需打开另一个 Excel 文档。本文演示了如何使用 Spire.Office for .NET 在 C#/VB.NET 中将 Excel 数据转换为带格式的 Word 表格。
安装 Spire. Office for .NET
首先，您需要添加 Spire.Office for .NET 包中包含的 DLL 文件作为 .NET 项目中的引用。DLL 文件可以从此链接下载或通过 NuGet 安装。
PM> Install-Package Spire.Office将 Excel 数据转换为带格式的 Word 表格
以下是使用 Spire.Office for .NET 将 Excel 数据转换为 Word 表格并保留其格式的步骤。
- 创建一个 Workbook 对象并使用 Workbook.LoadFromFile() 方法加载一个示例 Excel 文件。
- 通过 Workbook.Worksheets[index] 属性获取特定的工作表。
- 创建一个 Document 对象，并向其添加一个节。
- 使用 Section.AddTable() 方法添加一个表。
- 检测工作表中合并的单元格，并使用自定义方法 MergeCells() 合并 Word 表格相应的单元格。
- 通过 CellRange.Value 属性获取特定 Excel 单元格的值，并使用 TableCell.AddParagraph().AppendText() 方法将其添加到 Word 表格的单元格中。
- 使用自定义方法 CopyStyle() 将字体样式和单元格样式从 Excel 复制到 Word 表格中。
- 使用 Document.SaveToFile() 方法将文档保存到 Word 文件。
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Xls;
namespace ConvertExcelToWord
{
internal class Program
{
static void Main(string[] args)
{
//加载一个示例 Excel 文件
Workbook workbook = new Workbook();
workbook.LoadFromFile("企业经营数据表.xlsx");
//获取特定的工作表
Worksheet sheet = workbook.Worksheets[0];
//创建一个 Document 对象
Document doc = new Document();
Section section = doc.AddSection();
section.PageSetup.Orientation = PageOrientation.Landscape;
//添加一个表格
Table table = section.AddTable(true);
table.ResetCells(sheet.LastRow, sheet.LastColumn);
//合并单元格
MergeCells(sheet, table);
for (int r = 1; r <= sheet.LastRow; r++)
{
//设置行高
table.Rows[r - 1].Height = (float)sheet.Rows[r - 1].RowHeight;
for (int c = 1; c <= sheet.LastColumn; c++)
{
CellRange xCell = sheet.Range[r, c];
TableCell wCell = table.Rows[r - 1].Cells[c - 1];
//将数据从 Excel 导出到 Word 表格
TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
//将字体和单元格样式从 Excel 复制到 Word
CopyStyle(textRange, xCell, wCell);
}
}
//将文档保存到 Word 文件
doc.SaveToFile("导出到Word.docx", Spire.Doc.FileFormat.Docx);
}
//合并单元格（如果有）
private static void MergeCells(Worksheet sheet, Table table)
{
if (sheet.HasMergedCells)
{
//从 Excel 获取合并的单元格范围
CellRange[] ranges = sheet.MergedCells;
//合并Word表格中对应的单元格
for (int i = 0; i < ranges.Length; i++)
{
int startRow = ranges[i].Row;
int startColumn = ranges[i].Column;
int rowCount = ranges[i].RowCount;
int columnCount = ranges[i].ColumnCount;
if (rowCount > 1 && columnCount > 1)
{
for (int j = startRow; j <= startRow + rowCount; j++)
{
table.ApplyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
}
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1);
}
if (rowCount > 1 && columnCount == 1)
{
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1);
}
if (columnCount > 1 && rowCount == 1)
{
table.ApplyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount - 1);
}
}
}
}
//将Excel的单元格样式复制到Word表格
private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
{
//复制字体样式
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;
//复制背景色
wCell.CellFormat.BackColor = xCell.Style.Color;
//复制水平对齐
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;
}
//复制垂直对齐
switch (xCell.VerticalAlignment)
{
case VerticalAlignType.Bottom:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Bottom;
break;
case VerticalAlignType.Center:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Middle;
break;
case VerticalAlignType.Top:
wCell.CellFormat.VerticalAlignment = VerticalAlignment.Top;
break;
}
}
}
}Imports Spire.Doc
Imports Spire.Doc.Documents
Imports Spire.Doc.Fields
Imports Spire.Xls
Namespace ConvertExcelToWord
Friend Class Program
Private Shared Sub Main(ByVal args As String())
'加载一个示例 Excel 文件 Dim workbook As Workbook = New Workbook() workbook.LoadFromFile("企业经营数据表.xlsx") '获取特定的工作表
Dim sheet As Worksheet = workbook.Worksheets(0)
'创建一个 Document 对象 Dim doc As Document = New Document() Dim section As Section = doc.AddSection() section.PageSetup.Orientation = PageOrientation.Landscape '添加一个表格
Dim table As Table = section.AddTable(True)
table.ResetCells(sheet.LastRow, sheet.LastColumn)
'合并单元格 Program.MergeCells(sheet,table) For r As Integer = 1 To sheet.LastRow '设置行高
table.Rows(r - 1).Height = CSng(sheet.Rows(r - 1).RowHeight)
For c As Integer = 1 To sheet.LastColumn
Dim xCell As CellRange = sheet.Range(r, c)
Dim wCell As TableCell = table.Rows(r - 1).Cells(c - 1)
'将数据从 Excel 导出到 Word 表格 Dim textRange As TextRange = wCell.AddParagraph().AppendText(xCell.NumberText) '将字体和单元格样式从 Excel 复制到 Word
Program.CopyStyle(textRange, xCell, wCell)
Next
Next
'将文档保存到 Word 文件 doc.SaveToFile("导出到Word.docx",Spire.Doc.FileFormat.Docx) End Sub '合并单元格（如果有）
Private Shared Sub MergeCells(ByVal sheet As Worksheet, ByVal table As Table)
If sheet.HasMergedCells Then
'从 Excel 获取合并的单元格范围 Dim ranges As CellRange() = sheet.MergedCells '合并Word表格中对应的单元格
For i = 0 To ranges.Length - 1
Dim startRow As Integer = ranges(i).Row
Dim startColumn As Integer = ranges(i).Column
Dim rowCount As Integer = ranges(i).RowCount
Dim columnCount As Integer = ranges(i).ColumnCount
If rowCount > 1 AndAlso columnCount > 1 Then
For j = startRow To startRow + rowCount
table.ApplyHorizontalMerge(j - 1, startColumn - 1, startColumn - 1 + columnCount - 1)
Next
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)
End If
If rowCount > 1 AndAlso columnCount = 1 Then
table.ApplyVerticalMerge(startColumn - 1, startRow - 1, startRow - 1 + rowCount - 1)
End If
If columnCount > 1 AndAlso rowCount = 1 Then
table.ApplyHorizontalMerge(startRow - 1, startColumn - 1, startColumn - 1 + columnCount - 1)
End If
Next
End If
End Sub
'将Excel的单元格样式复制到Word表格 Private Shared Sub CopyStyle(ByVal wTextRange As TextRange,ByVal xCell As CellRange,ByVal wCell As TableCell) '复制字体样式
wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color
wTextRange.CharacterFormat.FontSize = CSng(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
'复制背景色 wCell.CellFormat.BackColor = xCell.Style.Color '复制水平对齐
Select Case xCell.HorizontalAlignment
Case HorizontalAlignType.Left
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left
Case HorizontalAlignType.Center
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center
Case HorizontalAlignType.Right
wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right
End Select
'复制垂直对齐 Select Case xCell.VerticalAlignment Case VerticalAlignType.Bottom wCell.CellFormat.VerticalAlignment = VerticalAlignment.Bottom Case VerticalAlignType.Center wCell.CellFormat.VerticalAlignment = VerticalAlignment.Middle Case VerticalAlignType.Top wCell.CellFormat.VerticalAlignment = VerticalAlignment.Top End Select End Sub End Class End Namespace
申请临时 License
如果您希望删除结果文档中的评估消息，或者摆脱功能限制，请该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。获取有效期 30 天的临时许可证。
