DataSet 导出 excel

第一步，引用 com组件 excel.dll 
如何生成excel.dll

1.要保证机器本身要安装OFFICE.

2.把[C:\Program Files\Microsoft Office\Office:默认安装路径]下的EXCEL9.OLB文件拷贝到[C:\Visual Studio.Net\SDK\v1.1\Bin:VS.Net安装路径]路径下。

3.打开Visual Studio .Net2003命令提示，运行TlbImp Excel9.olb Excel.dll ，就会在[C:\Visual Studio.Net\SDK\v1.1\Bin]下生成Excel.dll组件。

4.在项目中添加Excel.dll引用就OK了。

Excel2003生成Excel.dll的方法

为了在VS2005中，对Excel文件进行操作，需要用到Excel.dll，方法是将Office目录下的Excel.exe文件拷出来，把Excel.exe文件考到C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin里
然后运行VS2005命令提示输入TlbImp EXCEL.EXE Excel.dll
然后会在C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0下找到Excel.dll Office.dll 和VBIDE.dll

第二步，调用此方法即可将dataSet的数据导出到本地
public static void ExporToExcel(DataSet ds)
  {
   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.ApplicationClass();

   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") +"%]";
    System.Windows.Forms.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;
     MessageBox.Show("文件已经成功导出...");
    }
    catch(Exception ex)
    {
     filesaved=false;
     MessageBox.Show("导出文件时出错,文件可能正被打开！\n"+ex.Message);
    }
   }
   else
   {
    filesaved=false;
   }
   xlapp.Quit();
   GC.Collect();//强行销毁

  }