使用 NPOI/DocX 二次封装Office（Word、Excel）帮助类
简介
工作中需要大量生成导出报表或合同证书文件，原理为使用Excel或Word模板，批量替换标签以达到效果。
设计
由于原类库都属于基础方法，二次封装后具有更简易的使用方式，可直接传入生成的数据集或标签替换集合。
引用库介绍
由于微软默认推荐的类库 Microsoft.Office.Interop.Word 与 Microsoft.Office.Interop.Excel 需要电脑安装 Microsoft Office 并引用COM组件才可以使用（已知调用打印机需引用COM组件），所以选用类库可独立于Office组件，在任意一台电脑也可以运行。
NPOI：POI Java项目的.NET版本。可以非常轻松地读/写Office 2003/2007文件。
DocX：DocX是一个.NET库，允许开发人员以简单直观的方式操作Word文件。
Excel文件操作
ExcelHelper提供创建文件（2003/2007）及Sheet分页创建编辑，读取Excel文件至内存DataSet及反向DataSet保存至Excel文件。仅显示最外层引用方法，详细调用请在帮助类种查看！1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34/// <summary>
/// Excel所有分页转换为DataSet
/// </summary>
/// <param name="strDataSourcePath">Excel文件路径</param>
/// <returns>成功返回Excel的DataSet,失败返回NULL</returns>
public static DataSet ExcelConversionDataSet(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
DataSet dsTargetData = new DataSet();
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
foreach (var vAllSheet in dicAllSheet)
{
DataTable dtTargetData = new DataTable();
dtTargetData.TableName = vAllSheet.Value;
dtTargetData = ExcelConversionDataTable(strDataSourcePath, vAllSheet.Value);
if (dtTargetData == null)
{
continue;
}
dsTargetData.Tables.Add(dtTargetData);
}
return dsTargetData;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
1 | /// <summary> |
根据公司项目需要，把多个Excel的Sheet页的内容及样式合并为一个文件，Microsoft.Office.Interop.Excel提供拷贝分页方法，但是需要安装Microsoft Office，所以用NPOI类库实现了一个拷贝方法。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95/// <summary>
/// 拷贝Sheet页到另一个Sheet页
/// </summary>
/// <param name="strSourceExcelPath">源Excel路径</param>
/// <param name="strFromSheetName">源Excel拷贝Sheet</param>
/// <param name="strTargetExcelPath">目标Excel路径</param>
/// <param name="strToSheetName">目标Excel拷贝Sheet</param>
/// <returns>成功返回true,失败返回false</returns>
public static bool CopySheet(string strSourceExcelPath, string strFromSheetName, string strTargetExcelPath, string strToSheetName)
{
try
{
if (string.IsNullOrEmpty(strSourceExcelPath) || string.IsNullOrEmpty(strTargetExcelPath) || !File.Exists(strSourceExcelPath))
{
TXTHelper.Logs(string.Format("源数据和目标数据参数为空或文件不存在!"));
return false;
}
if (string.IsNullOrEmpty(strFromSheetName) || string.IsNullOrEmpty(strToSheetName))
{
TXTHelper.Logs(string.Format("源Sheet页和目标Sheet页参数为空!"));
return false;
}
//获得源数据和目标数据的Sheet页
IWorkbook iSourceWorkbook = null;
ISheet iSourceSheet = GetExcelSheetAt(strSourceExcelPath, strFromSheetName, out iSourceWorkbook);
IWorkbook iTargetWorkbook = null;
ISheet iTargetSheet = null;
if (iSourceSheet == null)
{
TXTHelper.Logs(string.Format("指定源数据Sheet页为空!"));
return false;
}
if (!File.Exists(strTargetExcelPath))
{
//如果文件不存在则创建Excel
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
bool bCreare = CreateExcel_Office2003(strTargetExcelPath, strToSheetName);
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
bool bCreare = CreateExcel_Office2007(strTargetExcelPath, strToSheetName);
}
else
{
TXTHelper.Logs(string.Format("指定目标Excel文件路径格式错误!"));
return false;
}
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
//如果文件存在则判断是否存在执行Sheet
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strTargetExcelPath);
if (dicAllSheet.ContainsValue(strToSheetName))
{
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
iTargetSheet = CreateExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
}
//调用Sheet拷贝Sheet方法
bool bCopySheet = CopySheetAt(iSourceWorkbook, iSourceSheet, iTargetWorkbook, iTargetSheet);
if (bCopySheet)
{
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xls"), FileMode.Create);
iTargetWorkbook.Write(fileStream2003);
fileStream2003.Close();
iTargetWorkbook.Close();
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xlsx"), FileMode.Create);
iTargetWorkbook.Write(fileStream2007);
fileStream2007.Close();
iTargetWorkbook.Close();
}
return true;
}
else
{
TXTHelper.Logs(string.Format("拷贝失败!"));
return false;
}
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
1 | // <summary> |
Word文件操作
WordHelper提供创建文件（2003/2007）及替换段落表格标签（匹配替换’{标签}’,’#标签#’），替换图片功能。仅显示最外层引用方法，详细调用请在帮助类种查看！1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65/// <summary>
/// 替换文本标签
/// </summary>
/// <param name="strDataSourcePath">Word文件路径</param>
/// <param name="strLabelName">标签名称(带标签符号)</param>
/// <param name="strReplaceLabel">替换标签文本</param>
/// <returns>成功返回替换数量,失败返回-1</returns>
public static int ReplaceTextLabel(string strDataSourcePath, string strLabelName, string strReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || string.IsNullOrEmpty(strReplaceLabel))
{
return -1;
}
int iNumber = 0;
FileStream fileStreamOpen = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStreamOpen);
foreach (XWPFParagraph wordParagraph in wordDocument.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
foreach (XWPFTable wordTable in wordDocument.Tables)
{
foreach (XWPFTableRow wordTableRow in wordTable.Rows)
{
foreach (XWPFTableCell wordTableCell in wordTableRow.GetTableCells())
{
foreach (XWPFParagraph wordParagraph in wordTableCell.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
}
}
}
FileStream fileStreamSave = new FileStream(strDataSourcePath, FileMode.Create);
wordDocument.Write(fileStreamSave);
fileStreamSave.Close();
wordDocument.Close();
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
1 | /// <summary> |
1 | /// <summary> |