C#導入導出數據到Excel的通用類代碼

Excel文件導入導出,需引用Microsoft Excel 11.0 Object Library

///////////////////////////////////////////////////////////////////////////
//Purpose：Excel文件導入導出,需引用Microsoft Excel 11.0 Object Library
//Author: Dangmy
//Date: 2007-03-09
//Version: 1.0
///////////////////////////////////////////////////////////////////////////

public class ExcelIO
{
     private int _ReturnStatus;
     private string _ReturnMessage;

 /// <summary>
 /// 執行返回狀態
 /// </summary>
 public int ReturnStatus
 {
     get{return _ReturnStatus;}
 }

 /// <summary>
 /// 執行返回信息
 /// </summary>
 public string ReturnMessage
 {
     get{return _ReturnMessage;}
 }

 public ExcelIO()
 {
 }

 /// <summary>
 /// 導入EXCEL到DataSet
 /// </summary>
 /// <param name="fileName">Excel全路徑文件名</param>
 /// <returns>導入成功的DataSet</returns>
 public DataSet ImportExcel(string fileName)
 {
     //判斷是否安裝EXCEL
     Excel.Application xlApp=new Excel.ApplicationClass();          
     if(xlApp==null)
     {
         _ReturnStatus = -1;
         _ReturnMessage = "無法創建Excel對象，可能您的計算機未安裝Excel";
         return null;
     }      

     //判斷文件是否被其他進程使用           
     Excel.Workbook workbook;               
     try
     {
         workbook = xlApp.Workbooks.Open(fileName,0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
     }
     catch
     {
         _ReturnStatus = -1;
         _ReturnMessage = "Excel文件處于打開狀態，請保存關閉";
         return null;
     }      

     //獲得所有Sheet名稱
     int n = workbook.Worksheets.Count;
     string[] SheetSet = new string[n];
     System.Collections.ArrayList al = new System.Collections.ArrayList();
     for(int i=1; i<=n; i++)
     {
         SheetSet[i-1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
     }

     //釋放Excel相關對象
     workbook.Close(null,null,null);        
     xlApp.Quit();
     if(workbook != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
         workbook = null;
     }
     if(xlApp != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
         xlApp = null;
     }  
     GC.Collect();

     //把EXCEL導入到DataSet
     DataSet ds = new DataSet();        
     string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+ fileName +";Extended Properties=Excel 8.0" ;
     using(OleDbConnection conn = new OleDbConnection (connStr))
     {
         conn.Open();
         OleDbDataAdapter da;
         for(int i=1; i<=n; i++)
         {
             string sql = "select * from ["+ SheetSet[i-1] +"$] ";
             da = new OleDbDataAdapter(sql,conn);
             da.Fill(ds,SheetSet[i-1]); 
             da.Dispose();
         }              
         conn.Close();
         conn.Dispose();
     }              
     return ds;
 }

 /// <summary>
 /// 把DataTable導出到EXCEL
 /// </summary>
 /// <param name="reportName">報表名稱</param>
 /// <param name="dt">數據源表</param>
 /// <param name="saveFileName">Excel全路徑文件名</param>
 /// <returns>導出是否成功</returns>
 public bool ExportExcel(string reportName,DataTable dt,string saveFileName)
 {
     if(dt==null)
     {
         _ReturnStatus = -1;
         _ReturnMessage = "數據集為空！";
         return false;          
     }

     bool fileSaved=false;
     Excel.Application xlApp=new Excel.ApplicationClass();  
     if(xlApp==null)
     {
         _ReturnStatus = -1;
         _ReturnMessage = "無法創建Excel對象，可能您的計算機未安裝Excel";
         return false;
     }

     Excel.Workbooks workbooks=xlApp.Workbooks;
     Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
     Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
     worksheet.Cells.Font.Size = 10;
     Excel.Range range;

     long totalCount=dt.Rows.Count;
     long rowRead=0;
     float percent=0;

     worksheet.Cells[1,1]=reportName;
     ((Excel.Range)worksheet.Cells[1,1]).Font.Size = 12;
     ((Excel.Range)worksheet.Cells[1,1]).Font.Bold = true;

     //寫入字段
     for(int i=0;i<dt.Columns.Count;i++)
     {
         worksheet.Cells[2,i+1]=dt.Columns[i].ColumnName;
         range=(Excel.Range)worksheet.Cells[2,i+1];
         range.Interior.ColorIndex = 15;
         range.Font.Bold = true;

     }
     //寫入數值
     for(int r=0;r<dt.Rows.Count;r++)
     {
         for(int i=0;i<dt.Columns.Count;i++)
         {
             worksheet.Cells[r+3,i+1]=dt.Rows[r][i].ToString();
         }
         rowRead++;
         percent=((float)(100*rowRead))/totalCount;
     }

     range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
     range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
     if( dt.Rows.Count > 0)
     {
         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(dt.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;
             _ReturnStatus = -1;
             _ReturnMessage = "導出文件時出錯,文件可能正被打開！\n"+ex.Message;
         }
     }
     else
     {
         fileSaved=false;
     }          

     //釋放Excel對應的對象
     if(range != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
         range = null;
     }
     if(worksheet != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
         worksheet = null;
     }
     if(workbook != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
         workbook = null;
     }
     if(workbooks != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
         workbooks = null;
     }              
     xlApp.Application.Workbooks.Close();
     xlApp.Quit();
     if(xlApp != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
         xlApp = null;
     }
     GC.Collect();
     return fileSaved;
 }

}</pre>