方法有很多,我只用了最简单的方法
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); }

|