C#的Office操作(Word&Excel)

2024-07-21 02:26:12

字體：大中小

供稿：網(wǎng)友

手頭上的一個(gè)項(xiàng)目報(bào)表相對(duì)比較簡(jiǎn)單，所以報(bào)表打印采用vba引擎，通過(guò)定制word模版，然后根據(jù)模版需要填充數(shù)據(jù)，然后ok,打印即可。

　　實(shí)現(xiàn)方法：首先需要引用vba組建,我用的是office2003 professional，dll版本號(hào)為microsoft word11.0

另外當(dāng)然還需要引用interop.word.dll.

代碼如下：

/**////#region 打開(kāi)word文檔,并且返回對(duì)象wdoc,wdoc
///
/// 打開(kāi)word文檔,并且返回對(duì)象wdoc,wdoc
///
/// 完整word文件路徑+名稱
/// 返回的word.document wdoc對(duì)象
/// 返回的word.application對(duì)象
public static void createworddocument(string filename,ref word.document wdoc,ref word.application wapp)
{
if(filename == "") return;
word.document thisdocument = null;
word.formfields formfields = null;
word.application thisapplication = new word.applicationclass();
thisapplication.visible = true;
thisapplication.caption = "";
thisapplication.options.checkspellingasyoutype = false;
thisapplication.options.checkgrammarasyoutype = false;

object filename = filename;
object confirmconversions = false;
object readonly = true;
object addtorecentfiles = false;

object passworddocument = system.type.missing;
object passwordtemplate = system.type.missing;
object revert = system.type.missing;
object writepassworddocument = system.type.missing;
object writepasswordtemplate = system.type.missing;
object format = system.type.missing;
object encoding = system.type.missing;
object visible = system.type.missing;
object openandrepair = system.type.missing;
object documentdirection = system.type.missing;
object noencodingdialog = system.type.missing;
object xmltransform = system.type.missing;

try
{
word.document worddoc =
thisapplication.documents.open(ref filename, ref confirmconversions,
ref readonly, ref addtorecentfiles, ref passworddocument, ref passwordtemplate,
ref revert,ref writepassworddocument, ref writepasswordtemplate, ref format,
ref encoding, ref visible, ref openandrepair, ref documentdirection,
ref noencodingdialog, ref xmltransform );

thisdocument = worddoc;
wdoc = worddoc;
wapp = thisapplication;
formfields = worddoc.formfields;
}
catch(exception ex)
{
messagebox.show(ex.message);
}

}
#endregion
調(diào)用上面靜態(tài)方法，打開(kāi)目標(biāo)文件并且把datagrid中數(shù)據(jù)填充到對(duì)應(yīng)word標(biāo)簽中去

/**////#region word填充數(shù)據(jù)(for example)
///
/// word填充數(shù)據(jù)
///
private void wordloaddata()
{
word.document wdoc=null;
word.application wapp=null;
sysfun.createworddocument("e://監(jiān)測(cè)報(bào)告(new).dot",ref wdoc,ref wapp);

//對(duì)標(biāo)簽"c"進(jìn)行填充
object bkmc="c";
if(wapp.activedocument.bookmarks.exists("c") == true)
{
wapp.activedocument.bookmarks.get_item
(ref bkmc).select();
}
wapp.selection.typetext(this.txt1.text);
object bkmg = "twatertable3";
object unit;
object count; //移動(dòng)數(shù)
object extend;

extend = word.wdmovementtype.wdextend;
unit = word.wdunits.wdcell;
//把datagrid中數(shù)據(jù)填充到標(biāo)簽twatertable3上
if(wapp.activedocument.bookmarks.exists("twatertable3") == true)
{
wapp.activedocument.bookmarks.get_item
(ref bkmg).select();

for(int i=0;i {
if(i==0)
{
count=1;
}
else
{
count=0;
}
//需填充5列數(shù)據(jù)
wapp.selection.move(ref unit,ref count);
wapp.selection.typetext(gridex1.getrow(i).cells[0].text);
count=1;

wapp.selection.move(ref unit,ref count);
wapp.selection.typetext(gridex1.getrow(i).cells[1].text);

wapp.selection.move(ref unit,ref count);
wapp.selection.typetext(gridex1.getrow(i).cells[2].text);

wapp.selection.move(ref unit,ref count);
wapp.selection.typetext(gridex1.getrow(i).cells[3].text);

wapp.selection.move(ref unit,ref count);
wapp.selection.typetext(gridex1.getrow(i).cells[4].text);
//換行
wapp.selection.moveright(ref unit,ref count,ref extend);
}
}
}
#endregion
然后就ok了，在對(duì)標(biāo)簽表控制要注意列循環(huán)和換行.

c#操作excel（導(dǎo)入導(dǎo)出）

有很多朋友說(shuō)需要c#導(dǎo)出到excel的代碼，現(xiàn)共享給大家

/**////
/// 讀取excel文檔
///
/// 文件名稱
/// 返回一個(gè)數(shù)據(jù)集
public dataset exceltods(string path)
{
string strconn = "provider=microsoft.jet.oledb.4.0;" +"data source="+ path +";"+"extended properties=excel 8.0;";
oledbconnection conn = new oledbconnection(strconn);
conn.open();
string strexcel = "";
oledbdataadapter mycommand = null;
dataset ds = null;
strexcel="select * from [sheet1$]";
mycommand = new oledbdataadapter(strexcel, strconn);
ds = new dataset();
mycommand.fill(ds,"table1");
return ds;
}

/**////
/// 寫入excel文檔
///
/// 文件名稱
public bool savefp2toexcel(string path)
{
try
{
string strconn = "provider=microsoft.jet.oledb.4.0;" +"data source="+ path +";"+"extended properties=excel 8.0;";
oledbconnection conn = new oledbconnection(strconn);
conn.open();
system.data.oledb.oledbcommand cmd=new oledbcommand ();
cmd.connection =conn;
//cmd.commandtext ="update [sheet1$] set 姓名='2005-01-01' where 工號(hào)='日期'";
//cmd.executenonquery ();
for(int i=0;i {
if(fp2.sheets [0].cells[i,0].text!="")
{
cmd.commandtext ="insert into [sheet1$] (工號(hào),姓名,部門,職務(wù),日期,時(shí)間) values('"+fp2.sheets [0].cells[i,0].text+ "','"+
fp2.sheets [0].cells[i,1].text+"','"+fp2.sheets [0].cells[i,2].text+"','"+fp2.sheets [0].cells[i,3].text+
"','"+fp2.sheets [0].cells[i,4].text+"','"+fp2.sheets [0].cells[i,5].text+"')";
cmd.executenonquery ();
}
}
conn.close ();
return true;
}
catch(system.data.oledb.oledbexception ex)
{
system.diagnostics.debug.writeline ("寫入excel發(fā)生錯(cuò)誤："+ex.message );
}
return false;
}
這種方法是相當(dāng)有效的。