C#读取Excel文件，并写入word模板文档

C#读取Excel⽂件，并写⼊word模板⽂档
1.该程序是⼀个win32控制台程序
2.开发⼯具是VS2010，office是2013版的
3.需要添加以下引⽤
4 在C盘保存.dot模板，样式如下
6.excel中的数据格式，最好都设置为常规。

具体的代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Drawing;
using Microsoft.Office.Interop.Word;
using Microsoft.Office.Interop.Excel;
namespace ReadANDWrite
{
class Program
{
//读取EXCEL数据
public System.Data.DataTable LoadDataFromExcel(string Path)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Path + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
System.Data.DataTable dt = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new System.Data.DataTable();
myCommand.Fill(dt);
return dt;
}
// 写⼊word 模板
public void WriteDataTOword(System.Data.DataTable dt, System.Data.DataTable dt1, System.Data.DataTable dt2, System.Data.DataTable dt3,string [] sstr) {
object oMissing = System.Reflection.Missing.Value;
//创建⼀个Word应⽤程序实例
Microsoft.Office.Interop.Word._Application oWord = new Microsoft.Office.Interop.Word.Application();
//设置为不可见
oWord.Visible = false;
//模板⽂件地址，这⾥假设在X盘根⽬录
object oTemplate = "C://template11.dot";
//以模板为基础⽣成⽂档
Microsoft.Office.Interop.Word._Document oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing);
//声明书签数组
object[] oBookMark = new object[20];
//赋值书签名
oBookMark[0] = "name";
oBookMark[1] = "sex";
oBookMark[2] = "people";
oBookMark[3] = "jiguan";
oBookMark[4] = "birth";
oBookMark[5] = "zzmm";
oBookMark[6] = "cjgzsj";
oBookMark[7] = "whcd";
oBookMark[8] = "byyx";
oBookMark[9] = "major";
//
oBookMark[10] = "graduate";
oBookMark[11] = "scgwpyqzw";
oBookMark[12] = "prsj";
oBookMark[13] = "kh1";
oBookMark[14] = "kh2";
oBookMark[15] = "kh3";
oBookMark[16] = "xpgw";
oBookMark[17] = "xpgwdj";
oBookMark[18] = "npgw";
oBookMark[19] = "npgwdj";
//
//赋值任意数据到书签的位置
//for (int i = 0; i < 20;++i )
//{
// oDoc.Bookmarks.get_Item(ref oBookMark[0]).Range.Text = dt.Rows[i][0].ToString(); //}
oDoc.Bookmarks.get_Item(ref oBookMark[0]).Range.Text = sstr[1];
oDoc.Bookmarks.get_Item(ref oBookMark[1]).Range.Text = sstr[2];
oDoc.Bookmarks.get_Item(ref oBookMark[2]).Range.Text = sstr[3];
oDoc.Bookmarks.get_Item(ref oBookMark[3]).Range.Text = sstr[4];
oDoc.Bookmarks.get_Item(ref oBookMark[4]).Range.Text = sstr[5];
oDoc.Bookmarks.get_Item(ref oBookMark[5]).Range.Text = sstr[6];
oDoc.Bookmarks.get_Item(ref oBookMark[6]).Range.Text = sstr[7];
oDoc.Bookmarks.get_Item(ref oBookMark[7]).Range.Text = sstr[8];
oDoc.Bookmarks.get_Item(ref oBookMark[8]).Range.Text = sstr[9];
oDoc.Bookmarks.get_Item(ref oBookMark[9]).Range.Text = sstr[10];
oDoc.Bookmarks.get_Item(ref oBookMark[10]).Range.Text = sstr[11];
oDoc.Bookmarks.get_Item(ref oBookMark[11]).Range.Text = sstr[12];
oDoc.Bookmarks.get_Item(ref oBookMark[12]).Range.Text = sstr[13];
string[] str1 = new string[3];
string ss1,ss2,ss3;
////2014年
for (int i = 0; i < 193; i++)
{
ss1 = dt1.Rows[i][1].ToString();
if (sstr[1] == dt1.Rows[i][1].ToString()) //若名字相同
{
str1[0] = dt1.Rows[i][6].ToString();
break;
}
}
//2015年
for (int i = 0; i < 182; i++)
{
ss2 = dt2.Rows[i][1].ToString();
if (sstr[1] == dt2.Rows[i][1].ToString()) //若名字相同
{
str1[1] = dt2.Rows[i][6].ToString();
break;
}
}
//2016年
for (int i = 0; i < 189; i++)
{
ss3 = dt3.Rows[i][1].ToString();
if (sstr[1] == dt3.Rows[i][1].ToString()) //若名字相同
{
str1[2] = dt3.Rows[i][6].ToString();
break;
}
}
oDoc.Bookmarks.get_Item(ref oBookMark[13]).Range.Text = str1[0];
oDoc.Bookmarks.get_Item(ref oBookMark[14]).Range.Text = str1[1];
oDoc.Bookmarks.get_Item(ref oBookMark[15]).Range.Text = str1[2];
for (int i = 0; i < 3;i++ ) //清空考核
{
str1[i] = "";
}
//
//oDoc.Bookmarks.get_Item(ref oBookMark[16]).Range.Text = dt.Rows[0][0].ToString(); //oDoc.Bookmarks.get_Item(ref oBookMark[17]).Range.Text = dt.Rows[0][0].ToString(); //oDoc.Bookmarks.get_Item(ref oBookMark[18]).Range.Text = dt.Rows[0][0].ToString(); //oDoc.Bookmarks.get_Item(ref oBookMark[19]).Range.Text = dt.Rows[0][0].ToString(); //保存⽣成的Word
object filename ="C:\\Users\\LV\\Desktop\\temp\\"+ sstr[1]+"-xx单位";
oDoc.SaveAs(ref filename, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
ref oMissing, ref oMissing);
oDoc.Close(ref oMissing, ref oMissing, ref oMissing);
//关闭word
oWord.Quit(ref oMissing, ref oMissing, ref oMissing);
}
static void Main(string[] args)
{
Program p = new Program();
System.Data.DataTable dt = p.LoadDataFromExcel("C:\\Users\\administrator\\Desktop\\花名册111.xls");
System.Data.DataTable dt1 = p.LoadDataFromExcel("C:\\Users\\administrator\\Desktop\\14考核表.xls");
System.Data.DataTable dt2 = p.LoadDataFromExcel("C:\\Users\\administrator\\Desktop\\15考核表.xls");
System.Data.DataTable dt3 = p.LoadDataFromExcel("C:\\Users\\administrator\\Desktop\\16考核表.xls");
string[] sstr = new string[21]; //定义⼀个存储输出数据的数组
string[] substr=new string[3];
System.DateTime currenttime1 = new System.DateTime();
currenttime1 = System.DateTime.Now;
for (int i = 0; i < 190; i++)
{
sstr[1] = dt.Rows[i][1].ToString();//姓名
sstr[2] = dt.Rows[i][2].ToString();//性别
sstr[3] = dt.Rows[i][3].ToString();//民族
sstr[4] = dt.Rows[i][5].ToString();//籍贯
sstr[5] = dt.Rows[i][25].ToString();//出⽣年⽉
sstr[6] = dt.Rows[i][6].ToString();//政治⾯貌
sstr[7] = dt.Rows[i][21].ToString();//参加⼯作时间
sstr[8] = dt.Rows[i][8].ToString();// ⽂化程度
//
sstr[0]=dt.Rows[i][23].ToString();// 毕业院校
substr = sstr[0].Split('，');
sstr[9] = substr[0];// 毕业院校
sstr[10] = substr[1];// 专业
sstr[11] = dt.Rows[i][24].ToString();//毕业时间
//
sstr[12] = dt.Rows[i][13].ToString();// ⾸次聘任前职务
sstr[13] = dt.Rows[i][14].ToString();// 聘任时间
//Console.WriteLine(dt1.Rows[i][0].ToString() + dt1.Rows[i][1].ToString() + dt1.Rows[i][2].ToString() + dt1.Rows[i][3].ToString() + dt1.Rows[i][4].ToString() + dt1.Rows[i][5].ToString()); //Console.WriteLine("\n");
p.WriteDataTOword(dt,dt1,dt2,dt3,sstr);
Console.WriteLine("{0}{1} 输出完毕", dt.Rows[i][0].ToString(),dt.Rows[i][1].ToString());
Console.WriteLine("\n");
}
System.DateTime currenttime2 = new System.DateTime();
currenttime2 = System.DateTime.Now;
Console.WriteLine("\n");
Console.WriteLine("全部输出完毕");
Console.WriteLine("开始时间{0}---结束时间{1}", currenttime1,currenttime2);
Console.ReadLine();
}
}
}
7运⾏结果。