数据导出：从DataSet导出到Excel中

  1         /// <summary>
  2         /// 从DataSet导出到Excel中
  3         /// </summary>
  4         /// <param name="ds">要导出的DataSet</param>
  5         /// <param name="strExcelFileName">要导出的文件名</param>
  6         public static void OutputToExcel(DataSet ds,string strFileName)
  7         {    
  8             int iIndex = 0;
  9             try
 10             {
 11                 if (System.IO.File.Exists(strFileName))
 12                 {
 13                     System.IO.File.Delete(strFileName);
 14                 }
 15                 if (strFileName.Length == 0)
 16                 {
 17                     throw(new Exception("路径为空！"));
 18                 }            
 19                 //****************************************************************************
 20                 //保存以前打开的Excel进程的ID号
 21                 //****************************************************************************
 22                 int[] processid = new int[System.Diagnostics.Process.GetProcesses().Length];
 23                 foreach(Process process in System.Diagnostics.Process.GetProcesses())
 24                 {
 25                     if (process.ProcessName.ToUpper().Equals("EXCEL"))
 26                     {                
 27                         processid[iIndex] = process.Id;
 28                         iIndex++;                                            
 29                     }
 30                 }
 31                 //*****************************************************************************
 32                 //新建一个Excel进程
 33                 //*****************************************************************************
 34                 Excel.Application myExcel;
 35                 try
 36                 {
 37                     //                    myExcel = new Excel.Application();
 38                     myExcel = new Excel.ApplicationClass();
 39                 }
 40                 catch(System.Exception)
 41                 {    
 42                     throw(new Exception("请检查本机器是否安装 Excel！"));
 43                 }        
 44                 //*****************************************************************************
 45                 //找出程序新建的Excel进程的ID号
 46                 //*****************************************************************************
 47                 bool bHaveWord = false;
 48                 foreach(Process process in System.Diagnostics.Process.GetProcesses())
 49                 {
 50                     if (process.ProcessName.ToUpper().Equals("EXCEL"))
 51                     {
 52                         int Count = 0;            
 53                         for(int i = 0;i< processid.Length;i++)
 54                         {
 55                             if(process.Id != processid[i])
 56                             {  
 57                                 Count++;                                    
 58                             }
 59                             if(Count == processid.Length)
 60                             {
 61                                 bHaveWord = true;
 62                                 iIndex = process.Id;
 63                             }
 64                         }                                            
 65                     }
 66                 }    
 67                 //*****************************************************************************
 68                 //操作程序新建的Excel进程(向Excel中导入数据)
 69                 //*****************************************************************************
 70                 myExcel.Visible = false;
 71                 myExcel.DisplayAlerts = false;        
 72                 System.Data.DataTable table = ds.Tables[0];
 73                 Excel.Workbooks workbooks = myExcel.Workbooks;
 74                 Excel._Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);                
 75                 Excel.Sheets sheets = workbook.Worksheets;         
 76                 sheets.Add(System.Reflection.Missing.Value,Missing.Value,Missing.Value,Missing.Value);//添加一个Sheet
 77                 Excel._Worksheet worksheet = (Excel._Worksheet)sheets.get_Item(1);   //取得第一个sheet   [get_Item(2)取得第二个sheet]
 78                 worksheet.Name = table.TableName;
 79                 Excel._Worksheet worksheet2 = (Excel._Worksheet)sheets.get_Item(2);
 80                 if (worksheet2 != null)
 81                 {
 82                     worksheet2.Delete();
 83                 }
 84 
 85                 int rowIndex = 1;
 86                 int colIndex = 0;
 87                 foreach(DataColumn col in table.Columns)
 88                 {
 89                     colIndex++;    
 90                     worksheet.Cells[1,colIndex] = col.ColumnName;                
 91                 }
 92                 foreach( DataRow row in table.Rows)
 93                 {
 94                     rowIndex++;
 95                     colIndex = 0;
 96                     foreach( DataColumn col in table.Columns)
 97                     {
 98                         colIndex++;
 99                         //                        excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();                        
100                         if (col.DataType == Type.GetType("System.DateTime"))
101                         {                            
102                             worksheet.Cells[rowIndex,colIndex] = row[col.ColumnName];
103                         }
104                         else
105                         {
106                             worksheet.Cells[rowIndex,colIndex] = "'" + row[col.ColumnName];
107                         }
108                     }
109                 }              
110                 try
111                 {                    
112                     workbook.SaveCopyAs(strFileName);                    
113                 }
114                 catch(Exception)
115                 {                    
116                     try
117                     {    
118                         //Excel2000版本
119                         workbook.SaveAs(strFileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
120                         //Excel2002版本
121                         //                        workbook.SaveAs(strFileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
122                         //                            Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
123                     }
124                     catch(Exception err)
125                     {
126                         Console.WriteLine(err.Message + System.Environment.NewLine + err.StackTrace);
127                         //throw(new Exception("Excel版本不符合本功能操作！"));
128                     }
129                 }
130                 //*****************************************************************************
131                 //删除程序新建的Excel进程
132                 //*****************************************************************************
133                 if(bHaveWord && iIndex != 0)
134                 {
135                     foreach(Process process in System.Diagnostics.Process.GetProcesses())
136                     {
137                         if (process.Id == iIndex)
138                         {    
139                             iIndex = 1;
140                             process.Kill();                                
141                             bHaveWord = false;
142                             iIndex = 0;
143                             break;
144                         }
145                     }
146                 }
147                 //*****************************************************************************                
148             }
149             catch(System.Exception err)
150             {
151                 MessageBox.Show(err.Message,"提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
152             }
153         }

该例子是采用组件Excel进行数据导出，所以需要依赖office，关联环境较强，所以有一定的缺点；
大家有更好的方法拿出来大家一起学习！

作者：冯珺
日期：2006-03-07