DataGrid-Export Excel files
Last Update:2017-02-28
Source: Internet
Author: User
Datagrid|excel| Export Excel Methods There are many, I only use the simplest way
1. Referencing COM components Excel.dll
2. To achieve the following
public void Exportexcel () {
DataSet Ds=this. dataset;//Gets the dataset bound by the DataGrid
if (ds==null) return;
String Savefilename= "";
BOOL Filesaved=false;
SaveFileDialog savedialog=new SaveFileDialog ();
Savedialog.defaultext = "xls";
savedialog.filter= "Excel file |*.xls";
Savedialog.filename = "Sheet1";
Savedialog.showdialog ();
Savefilename=savedialog.filename;
if (Savefilename.indexof (":") <0) return; It was ordered to cancel.
Excel.Application xlapp=new Excel.Application ();
if (xlapp==null) {
MessageBox.Show ("Cannot create Excel object, you may not have Excel installed on your machine");
Return
}
Excel.Workbooks Workbooks=xlapp.workbooks;
Excel.Workbook Workbook=workbooks. ADD (Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet= (excel.worksheet) workbook. worksheets[1];//get 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;
Write fields
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;
}
Write value
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 = "Data is being exported [" + 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 ("Error exporting file, file may be open!") \ n "+ex. message);
}
}else{
Filesaved=false;
}
xlApp.Quit ();
Gc. Collect ()//forcibly destroyed
if (filesaved && file.exists (savefilename)) System.Diagnostics.Process.Start (savefilename);
}