通州北大青鸟学校：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 ;

（通州北大青鸟学校）