DataGrid - 匯出Excel檔案

來源:互聯網
上載者:User
datagrid|excel|匯出excel 方法有很多,我只用了最簡單的方法

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);

}





相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。