转自http://martinyang2010.bokee.com/2598999.html
using system;
using system.data;
using system.drawing;
using system.data.sqlclient;
using excel;
using word;
using system.io;
namespace common
{
/// <summary>
/// 把数据导入到.doc、.txt、.xls文件中
/// </summary>
public class export
{
private const string datawordpath = @"c:/folder/doc/datadoc/";
private const string imagewordpath = @"c:/folder/doc/imagedoc/";
private const string imagepath = @"c:/folder/image/";
private const string excelpath = @"c:/folder/excel/";
private const string txtpath = @"c:/folder/txt/";
private const string imagepostfix = ".bmp";
private const string wordpostfix = ".doc";
private const string excelpostfix = ".xls";
private const string txtpostfix = ".txt";
private const int datadistance = 5;
private const int tabdistance = 8;
public export()
{
//
// todo: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 获得数据集dataset--------------------------------用于调试
/// </summary>
/// <returns>dataset</returns>
public dataset getdata()
{
try
{
string sconnectionstring;
sconnectionstring = "workstation id=guofu;packet size=4096;user id=sa;data source=guofu;persist security info=true;initial catalog=yc;password=sc";
sqlconnection objconn = new sqlconnection(sconnectionstring);
objconn.open();
sqldataadapter dapoint = new sqldataadapter("select * from point", objconn);
dataset dsyc = new dataset("yc");
dapoint.fillschema(dsyc,schematype.mapped, "point");
dapoint.fill(dsyc,"point");
dapoint = new sqldataadapter("select * from employee", objconn);
dapoint.fillschema(dsyc,schematype.mapped, "employee");
dapoint.fill(dsyc,"employee");
return dsyc;
}
catch(exception ex)
{
throw new exception(ex.message);
}
}
/// <summary>
/// 把数据文件导入到.xls文件
/// </summary>
/// <param name="ds"></param>
public void exporttoexcel(dataset ds)
{
if(ds.tables.count!=0)
{
//生成.xls文件完整路径名
string tempfilename = gettempfilename();
object filename = excelpath+tempfilename+excelpostfix;
object nothing = system.reflection.missing.value;
//创建excel文件，文件名用系统时间生成精确到毫秒
excel.application myexcel = new excel.applicationclass();
myexcel.application.workbooks.add(nothing);
try
{
//把dataset中的数据插入excel文件中
int totalcount = 0;
for(int k =0;k<ds.tables.count;k++)
{
int row = ds.tables[k].rows.count;
int column = ds.tables[k].columns.count;
for(int i = 0;i<column;i++)
{
myexcel.cells[totalcount+2,1+i] = ds.tables[k].columns[i].columnname;
}
for(int i = 0;i<row;i++)
{
for(int j =0;j<column;j++)
{
myexcel.cells[totalcount+3+i,1+j] = "'" + ds.tables[k].rows[i][j].tostring();
}
}
totalcount = totalcount + row +4;
}
try
{
//保存excel文件到指定的目录下，文件名用系统时间生成精确到毫秒
myexcel.activeworkbook._saveas(filename,nothing,nothing,nothing,nothing,nothing,xlsaveasaccessmode.xlexclusive,nothing,nothing,nothing,nothing);
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+excelpath+tempfilename+excelpostfix);
return;
}
//让生成的excel文件可见
myexcel.visible = true;
}
catch(exception e)
{
system.windows.forms.messagebox.show("向excel文件中写入数据出错： " + e.message);
}
}
else
{
system.windows.forms.messagebox.show("no data");
}
}
/// <summary>
/// 把数据导入到.doc文件
/// </summary>
/// <param name="ds"></param>
public void exporttoword(dataset ds)
{
if(ds.tables.count!=0)
{
string tempfilename = null;
object filename = null;
object tablebehavior = word.wddefaulttablebehavior.wdword9tablebehavior;
object autofitbehavior = word.wdautofitbehavior.wdautofitfixed;
object unit = word.wdunits.wdstory;
object extend = system.reflection.missing.value;
object breaktype = (int)word.wdbreaktype.wdsectionbreaknextpage;
object count = 1;
object character = word.wdunits.wdcharacter;
object nothing = system.reflection.missing.value;
try
{
tempfilename = gettempfilename();
//生成.doc文件完整路径名
filename = datawordpath+tempfilename+wordpostfix;
//创建一个word文件，文件名用系统时间生成精确到毫秒
word.application myword= new word.applicationclass();
word._document mydoc = new word.documentclass();
mydoc = myword.documents.add(ref nothing,ref nothing,ref nothing,ref nothing);
mydoc.activate();
//向把dataset中的表插入到word的文件中
for(int totaltable = 0;totaltable<ds.tables.count;totaltable++)
{
myword.application.selection.typetext(ds.tables[totaltable].tablename+"表的数据如下");
myword.application.selection.typeparagraph();
myword.application.selection.typeparagraph();
word.range para = myword.application.selection.range;
mydoc.tables.add(para,ds.tables[totaltable].rows.count+1,ds.tables[totaltable].columns.count,ref tablebehavior,ref autofitbehavior);
for(int column = 0; column<ds.tables[totaltable].columns.count;column++)
{
mydoc.tables.item(totaltable+1).cell(1,column+1).range.insertbefore(ds.tables[0].columns[column].columnname.trim());
}
for(int row = 0;row<ds.tables[totaltable].rows.count;row++)
{
for(int column = 0;column<ds.tables[totaltable].columns.count;column++)
{
mydoc.tables.item(totaltable+1).cell(row+2,column+1).range.insertbefore(ds.tables[totaltable].rows[row][column].tostring().trim());
}
}
myword.application.selection.endkey(ref unit,ref extend);
myword.application.selection.typeparagraph();
myword.application.selection.typeparagraph();
myword.application.selection.insertbreak(ref breaktype);
}
myword.application.selection.typebackspace();
myword.application.selection.delete(ref character,ref count);
myword.application.selection.homekey(ref unit,ref extend);
//保存word文件到指定的目录下
try
{
mydoc.saveas(ref filename,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing);
myword.visible = true;
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+datawordpath+tempfilename+wordpostfix);
return;
}
//让生成的excel文件可见
myword.visible = true;
}
catch(exception ex)
{
system.windows.forms.messagebox.show("向word文件中写入数据出错： " + ex.message);
}
}
else
{
system.windows.forms.messagebox.show("no data");
}
}
/// <summary>
/// 把图片文件导入到.doc文件
/// </summary>
/// <param name="bp"></param>
public void exporttoword(bitmap bp)
{
string tempfilename = null;
string bmppath = null;
object filename = null;
object nothing = null;
tempfilename = gettempfilename();
//生成.bmp文件完整路径名
bmppath = imagepath+tempfilename+imagepostfix;
//生成.doc文件完整路径名
filename = imagewordpath+tempfilename+wordpostfix;
nothing = system.reflection.missing.value;
//创建一个word文件，文件名用系统时间生成精确到毫秒
word.application myword= new word.applicationclass();
word._document mydoc = new word.documentclass();
mydoc = myword.documents.add(ref nothing,ref nothing,ref nothing,ref nothing);
try
{
//把bitmap对象保存到系统所生成文件完整路径中
bp.save(bmppath);
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+bmppath);
return;
}
try
{
//往word文件中插入图片
mydoc.inlineshapes.addpicture(bmppath,ref nothing,ref nothing,ref nothing);
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+bmppath);
return;
}
try
{
//保存word文件到指定的目录下
mydoc.saveas(ref filename,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing,ref nothing);
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+imagewordpath+tempfilename+wordpostfix);
return;
}
//让生成的word文件可见
myword.visible = true;
}
/// <summary>
/// 把数据文件导入到.txt文件
/// </summary>
/// <param name="ds"></param>
public void exporttotxt(dataset ds)
{
if(ds.tables.count!=0)
{
string tempfilename = null;
tempfilename = gettempfilename();
//创建一个.txt文件，文件名用系统时间生成精确到毫秒
fileinfo file = new fileinfo(txtpath+tempfilename+txtpostfix);
streamwriter textfile = null;
try
{
textfile = file.createtext();
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+txtpath+tempfilename+txtpostfix);
return;
}
//把dataset中的数据写入.txt文件中
for(int totaltable = 0;totaltable<ds.tables.count;totaltable++)
{
//统计dataset中当前表的行数
int row = ds.tables[totaltable].rows.count;
//统计dataset中当前表的列数
int column = ds.tables[totaltable].columns.count;
//用于统计当前表中每列记录中字符数最长的字符串的长度之和
int totallength = 0;
//用于统计标题的长度（dataset中的表名的length+"表的数据如下"的length）
int titlelength = 0;
//统计每列记录中字符数最长的字符串的长度
int[] columnlength = new int[column];
for(int i = 0;i<column;i++)
{
columnlength[i] = ds.tables[totaltable].columns[i].columnname.tostring().length;
}
for(int i = 0;i<row;i++)
{
for(int j = 0;j<column;j++)
{
if(ds.tables[totaltable].rows[i][j].tostring().length>columnlength[j])
{
columnlength[j]=ds.tables[totaltable].rows[i][j].tostring().length;
}
}
}
//统计当前表中每列记录中字符数最长的字符串的长度之和
for(int i = 0;i<column;i++)
{
totallength = totallength+columnlength[i]+datadistance;
}
totallength = totallength+2*tabdistance-datadistance;
//统计标题的长度（dataset中的当前表名的length+"表的数据如下"的length）
titlelength = ds.tables[totaltable].tablename.tostring().length+"表的数据如下".length*2;
//把标题写入.txt文件中
for(int i = 0;i<(int)((totallength-titlelength)/2);i++)
{
textfile.write(' ');
}
textfile.write(ds.tables[totaltable].tablename+"表的数据如下");
textfile.writeline();
for(int i = 0;i<totallength;i++)
{
textfile.write('*');
}
textfile.writeline();
textfile.write("/t");
//把dataset中当前表的字段名写入.txt文件中
for(int i = 0;i<column;i++)
{
textfile.write(ds.tables[totaltable].columns[i].columnname.tostring());
for(int k = 0;k<columnlength[i]-ds.tables[totaltable].columns[i].columnname.tostring().length+datadistance;k++)
{
textfile.write(' ');
}
}
textfile.writeline();
for(int i = 0;i<totallength;i++)
{
textfile.write('-');
}
textfile.writeline();
textfile.write("/t");
//把dataset中当前表的数据写入.txt文件中
for(int i = 0;i<row;i++)
{
for(int j = 0;j<column;j++)
{
textfile.write(ds.tables[totaltable].rows[i][j].tostring());
for(int k = 0;k<columnlength[j]-ds.tables[totaltable].rows[i][j].tostring().length+datadistance;k++)
{
textfile.write(' ');
}
}
textfile.writeline();
textfile.write("/t");
}
textfile.writeline();
for(int i = 0;i<totallength;i++)
{
textfile.write('-');
}
textfile.writeline();
textfile.writeline();
textfile.writeline();
}
//关闭当前的streamwriter流
textfile.close();
system.windows.forms.messagebox.show("数据文件已保存到"+" "+file.fullname);
}
else
{
system.windows.forms.messagebox.show("no data");
}
}
public string gettempfilename()
{
return datetime.now.tostring("yyyymmddhhmmssfff");
}
}
}
补充：使用以上方法必须对dcom进行配置，给用户使用office的权限。
具体配置方法如下:
1:在服务器上安装office的excel软件.
2:在"开始"->"运行"中输入dcomcnfg.exe启动"组件服务"
3:依次双击"组件服务"->"计算机"->"我的电脑"->"dcom配置"
4:在"dcom配置"中找到"microsoft excel 应用程序",在它上面点击右键,然后点击"属性",弹出"microsoft excel 应用程序属性"对话框
5:点击"标识"标签,选择"交互式用户"
6:点击"安全"标签,在"启动和激活权限"上点击"自定义",然后点击对应的"编辑"按钮,在弹出的"安全性"对话框中填加一个"network service"用户(注意要选择本计算机名),并给它赋予"本地启动"和"本地激活"权限.
7:依然是"安全"标签,在"访问权限"上点击"自定义",然后点击"编辑",在弹出的"安全性"对话框中也填加一个"network service"用户,然后赋予"本地访问"权限.
这样,我们便配置好了相应的excel的dcom权限.
注意:我是在win2003上配置的,在2000上,是配置aspnet用户
若不进行配置会出现错误
检索 com 类工厂中 clsid 为 {00024500-0000-0000-c000-000000000046} 的组件时失败，原因是出现以下错误: 80070005。
原因是用户没有使用excel的权限。
导出到word同样要配置使用word的权限。
继续补充： 导出到txt我用了上面的方法有问题，
try
{
textfile = file.createtext();
}
catch
{
system.windows.forms.messagebox.show("系统找不到指定目录下的文件： "+txtpath+tempfilename+txtpostfix);
return;
}
总是在这里跳到catch里面。导出到word,excel都能用，继续研究txt的使用方法。
新闻热点
疑难解答
