DataGrid - 導出Excel文件
2024-07-21 02:23:11
供稿：網友
方法有很多，我只用了最簡單的方法
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);
}
