当你在制作公司月度支出的 word 报表时，你可能需要从 excel 表格中复制财务数据到该报表，这样可以让别人直接在该报表里查看相关数据而无需打开另一个 excel 文档。本文演示了如何使用 spire.office for .net 在 c#/vb.net 中将 excel 数据转换为带格式的 word 表格。
安装 spire. office for .net
首先，您需要添加 spire.office for .net 包中包含的 dll 文件作为 .net 项目中的引用。dll 文件可以从此链接下载或通过 安装。
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 天的临时许可证。
