通州北大青鳥學校：C#中Excel數據的導入、導出

通州北大青鳥學校學術部講解：C#中Excel數據的導入、導出

///

 /// 導出到 Excel 文件
 ///

 /// 含完整路徑
 /// 含字段標題名
 public void ExpExcel(string fileName ,DataTable dataTable)
 {
     Excel.ApplicationClass apc =new Excel.ApplicationClass();
 
     apc.Visible = false ;
     Excel.Workbook wkbook = apc.Workbooks.Add( true ) ;
     Excel.Worksheet wksheet = (Excel.Worksheet)wkbook.ActiveSheet;
 
     int rowIndex = 2;
     int colIndex = 1;
 
     wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat = "@";
 
     //取得列標題
     foreach (DataColumn dc in dataTable.Columns)
     {
         colIndex ++;
         wksheet.Cells[1,colIndex] = dc.ColumnName;
     }
 
     //取得表格中數據（通州北大青鳥學校）
     foreach (DataRow dr in dataTable.Rows)
     {
         colIndex = 1;
         foreach (DataColumn dc in dataTable.Columns)
         {
             if(dc.DataType == System.Type.GetType("System.DateTime"))
             {
                 apc.Cells[rowIndex,colIndex] = "'"+(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");
             }
             else
                 if(dc.DataType == System.Type.GetType("System.String"))
             {
                 apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();
             }
             else
             {
                 apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();
             }
 
             wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
 
             colIndex++;
         }
         rowIndex++;
     }
    
     //設置表格樣式（通州北大青鳥學校）

     wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex = 20;
     wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex = 3;
     wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight = Excel.XlBorderWeight.xlThin;
     wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).Columns.AutoFit();
 
     if(File.Exists(fileName))
     {
         File.Delete(fileName);
     }
 
     wkbook.SaveAs( fileName ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
   
     wkbook.Close(Type.Missing,Type.Missing,Type.Missing);
     apc.Quit();
     wkbook = null;
     apc = null;
     GC.Collect();
 }
 ///

 /// 從Excel導入帳戶(逐單元格讀取)
 ///

 /// 完整路徑名（通州北大青鳥學校）

 public IList ImpExcel(string fileName)
 {
     IList alExcel = new ArrayList();
     UserInfo userInfo = new UserInfo();
 
     Excel.Application app;
     Excel.Workbooks wbs;
     Excel.Worksheet ws;
 
     app = new Excel.Application();
     wbs = app.Workbooks;
     wbs.Add(fileName);
     ws= (Excel.Worksheet)app.Worksheets.get_Item(1);
     int a = ws.Rows.Count;
     int b = ws.Columns.Count;
    
     for ( int i = 2; i < 4; i++)
     {
         for ( int j = 1; j < 21; j++)
         {
             Excel.Range range = ws.get_Range(app.Cells[i,j],app.Cells[i,j]);
             range.Select();
             alExcel.Add( app.ActiveCell.Text.ToString() );
         }
     }
 
     return alExcel;
 }
 
 
 ///

 /// 從Excel導入帳戶(新建oleDb連接,Excel整表讀取,適于無合并單元格時)
 ///

 /// 完整路徑名
 ///
 public DataTable ImpExcelDt (string fileName)
 {
     string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0" ;
     OleDbConnection myConn = new OleDbConnection ( strCon ) ;
     string strCom = " SELECT * FROM [Sheet1$] " ;
     myConn.Open ( ) ;
     OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
     DataSet myDataSet = new DataSet ( ) ;
     myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
     myConn.Close ( ) ;
 
     DataTable dtUsers = myDataSet.Tables[0];
 
     return dtUsers;
 }
 （通州北大青鳥學校）

 
 dataGrid中顯示：
 DataGrid1.DataMember= "[Sheet1$]" ;
 DataGrid1.DataSource = myDataSet ;

（通州北大青鳥學校）