DataGrid – 导出Excel文件-.NET教程,数据库应用

方法有很多，我只用了最简单的方法

1.引用 com组件 excel.dll

2.实现如下

public void exportexcel(){

dataset ds=this.dataset;//取得datagrid绑定的dataset

if(ds==null) return;

string savefilename="";

bool filesaved=false;

savefiledialog savedialog=new savefiledialog();

savedialog.defaultext ="xls";

savedialog.filter="excel文件|*.xls";

savedialog.filename ="sheet1";

savedialog.showdialog();

savefilename=savedialog.filename;

if(savefilename.indexof(":")<0) return; //被点了取消

excel.application xlapp=new excel.application();

if(xlapp==null){

messagebox.show("无法创建excel对象，可能您的机子未安装excel");

return;

}

excel.workbooks workbooks=xlapp.workbooks;

excel.workbook workbook=workbooks.add(excel.xlwbatemplate.xlwbatworksheet);

excel.worksheet worksheet=(excel.worksheet)workbook.worksheets[1];//取得sheet1

excel.range range;

string oldcaption=this.captiontext;

long totalcount=ds.tables[0].rows.count;

long rowread=0;

float percent=0;

worksheet.cells[1,1]=this.captiontext;

//写入字段

for(int i=0;i<ds.tables[0].columns.count;i++){

worksheet.cells[2,i+1]=ds.tables[0].columns[i].columnname;

range=(excel.range)worksheet.cells[2,i+1];

range.interior.colorindex = 15;

range.font.bold = true;

}

//写入数值

this.captionvisible = true;

for(int r=0;r<ds.tables[0].rows.count;r++){

for(int i=0;i<ds.tables[0].columns.count;i++){

worksheet.cells[r+3,i+1]=ds.tables[0].rows[r][i];

}

rowread++;

percent=((float)(100*rowread))/totalcount;

this.captiontext = "正在导出数据["+ percent.tostring("0.00") +"%]…";

application.doevents();

}

this.captionvisible = false;

this.captiontext = oldcaption;

range=worksheet.get_range(worksheet.cells[2,1],worksheet.cells[ds.tables[0].rows.count+2,ds.tables[0].columns.count]);

range.borderaround(excel.xllinestyle.xlcontinuous,excel.xlborderweight.xlthin,excel.xlcolorindex.xlcolorindexautomatic,null);

range.borders[excel.xlbordersindex.xlinsidehorizontal].colorindex = excel.xlcolorindex.xlcolorindexautomatic;

range.borders[excel.xlbordersindex.xlinsidehorizontal].linestyle =excel.xllinestyle.xlcontinuous;

range.borders[excel.xlbordersindex.xlinsidehorizontal].weight =excel.xlborderweight.xlthin;

if(ds.tables[0].columns.count>1){

range.borders[excel.xlbordersindex.xlinsidevertical].colorindex =excel.xlcolorindex.xlcolorindexautomatic;

range.borders[excel.xlbordersindex.xlinsidevertical].linestyle = excel.xllinestyle.xlcontinuous;

range.borders[excel.xlbordersindex.xlinsidevertical].weight = excel.xlborderweight.xlthin;

}

if(savefilename!=""){

try{

workbook.saved =true;

workbook.savecopyas(savefilename);

filesaved=true;

}catch(exception ex){

filesaved=false;

messagebox.show("导出文件时出错,文件可能正被打开！\n"+ex.message);

}

}else{

filesaved=false;

}

xlapp.quit();

gc.collect();//强行销毁

if(filesaved && file.exists(savefilename)) system.diagnostics.process.start(savefilename);

}