C#导入导出EXCEL

转自：http://blog.csdn.net/wizardlun/article/details/4823062

參考: http://topic.csdn.net/u/20091113/10/77a993dc-af9a-46a3-ab99-0c5050977d93.html?81131

 

C# code

    
    

     
      
     
     ///
     
      
     
     <summary>
     
     
    
     
     ///
     
      生成EXCEL文件
    
     
     ///
     
      _til是表格的标题，_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
    
     
     ///
     
      
     
     </summary>
     
     

     
         
     
     public
     
      
     
     void
     
      SaveToExcel(
     
     string
     
      _til, 
     
     int
     
      _row, 
     
     int
     
      _col, DataTable _dt, 
     
     string
     
      _filename)
    {
        Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet 
     
     =
     
      
     
     new
     
      Microsoft.Office.Interop.Owc11.SpreadsheetClass();
        
     
     //
     
     合并单元格
     
     

     
             xlsheet.get_Range(xlsheet.Cells[
     
     1
     
     , 
     
     1
     
     ], xlsheet.Cells[
     
     1
     
     , _col]).set_MergeCells(
     
     true
     
     );
        
     
     //
     
      xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
     
     

     
             xlsheet.ActiveSheet.Cells[
     
     1
     
     , 
     
     1
     
     ] 
     
     =
     
      _til;
        
     
     //
     
     字体加粗
     
     

     
             xlsheet.get_Range(xlsheet.Cells[
     
     1
     
     , 
     
     1
     
     ], xlsheet.Cells[
     
     1
     
     , _col]).Font.set_Bold(
     
     true
     
     );
        
     
     //
     
     单元格文本水平居中对齐
     
     

     
             xlsheet.get_Range(xlsheet.Cells[
     
     1
     
     , 
     
     1
     
     ], xlsheet.Cells[_row 
     
     +
     
      
     
     2
     
     , _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
        
     
     //
     
     设置字体大小
     
     

     
             xlsheet.get_Range(xlsheet.Cells[
     
     1
     
     , 
     
     1
     
     ], xlsheet.Cells[
     
     1
     
     , _col]).Font.set_Size(
     
     14
     
     );
        
     
     //
     
     设置列宽
        
     
     //
     
     xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50);

        
     
     //
     
     画边框线
     
     

     
             xlsheet.get_Range(xlsheet.Cells[
     
     1
     
     , 
     
     1
     
     ], xlsheet.Cells[_row 
     
     +
     
      
     
     2
     
     , _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous);

        
     
     //
     
     定义一个2维数组用来存储DATATABLE里的数据
     
     

     
             
     
     object
     
     [,] dataArray 
     
     =
     
      
     
     new
     
      
     
     object
     
     [_row, _col];
        
     
     //
     
     添加列名
     
     

     
             
     
     for
     
      (
     
     int
     
      k 
     
     =
     
      
     
     0
     
     ; k 
     
     <
     
      _dt.Columns.Count; k
     
     ++
     
     )
        {
            xlsheet.ActiveSheet.Cells[
     
     2
     
     , k 
     
     +
     
      
     
     1
     
     ] 
     
     =
     
      _dt.Columns[k].Caption.ToString();
        }
        
     
     //
     
     把DATATABLE里的数据导到2维数组中
     
     

     
             
     
     for
     
      (
     
     int
     
      i 
     
     =
     
      
     
     0
     
     ; i 
     
     <
     
      _row; i
     
     ++
     
     )
        {
            
     
     for
     
      (
     
     int
     
      j 
     
     =
     
      
     
     0
     
     ; j 
     
     <
     
      _col; j
     
     ++
     
     )
            {
                dataArray[i, j] 
     
     =
     
      _dt.Rows[i][j];
            }
        }
        
     
     //
     
     把2维数组中的数据导到EXCEL中
     
     

     
             xlsheet.get_Range(
     
     "
     
     A3
     
     "
     
     , xlsheet.Cells[_row 
     
     +
     
      
     
     2
     
     , _col]).Value2 
     
     =
     
      dataArray;
        xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
    }
    
     
     ///
     
      
     
     <summary>
     
     
    
     
     ///
     
      导入EXCEL表
    
     
     ///
     
      
     
     </summary>
     
     
    
     
     ///
     
      
     
     <param name="strFileName"></param>
     
     
    
     
     ///
     
      
     
     <returns></returns>
     
     

     
         
     
     public
     
      DataSet CreateDataSource(
     
     string
     
      strFileName, 
     
     string
     
      Sqlstr)
    {
        
     
     string
     
      strCon 
     
     =
     
      
     
     "
     
     Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
     
     "
     
      
     
     +
     
      strFileName 
     
     +
     
      
     
     "
     
     ; Extended Properties=Excel 8.0;
     
     "
     
     ;
        OleDbConnection olecon 
     
     =
     
      
     
     new
     
      OleDbConnection(strCon);
        OleDbDataAdapter myda 
     
     =
     
      
     
     new
     
      OleDbDataAdapter(Sqlstr, olecon);
        DataSet myds 
     
     =
     
      
     
     new
     
      DataSet();
        myda.Fill(myds);
        
     
     return
     
      myds;
    }