WPF和EXCEL的交互

WPF和EXCEL的交互

      日常工作中我们经常需要和office打交道，我们的程序也可能需要将数据导出为excel进行再次处理。在WPF中我觉得比较方便的和excel进行交互的2个方式如下：

 

　　写入excel：

 

代码

   
   

    
    System.Windows.Forms.SaveFileDialog sd
    
    =
    
    new
    
     System.Windows.Forms.SaveFileDialog();
sd.Filter
    
    =
    
    "
    
    Excel 2003 文档|*.xls
    
    "
    
    ;

    
    if
    
    (sd.ShowDialog()
    
    ==
    
    System.Windows.Forms.DialogResult.OK)
{
    FileStream fs
    
    =
    
    new
    
     FileStream(sd.FileName,FileMode.Create,FileAccess.Write);
    StreamWriter sw
    
    =
    
    new
    
     StreamWriter(fs,Encoding.Default);
    
    
    string
    
     line
    
    =
    
    ""
    
    ;
    
    
    //
    
    写入列标题
    
    

    
        
    
    for
    
     (
    
    int
    
     i 
    
    =
    
     
    
    0
    
    ; i 
    
    <
    
     datamain.Columns.Count; i
    
    ++
    
    )
    {
        line 
    
    +=
    
     datamain.Columns[i].Header.ToString() 
    
    +
    
     
    
    "
    
    /t
    
    "
    
    ;
    }
    sw.WriteLine(line);
    
    
    for
    
     (
    
    int
    
     j 
    
    =
    
     
    
    0
    
    ; j 
    
    <
    
     datamain.Items.Count; j
    
    ++
    
    )
    {

        Statresult dt 
    
    =
    
     datamain.Items[j] 
    
    as
    
     Statresult;
        line 
    
    =
    
     dt.Client 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Total 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Receive 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Rates 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Inspection 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Bugnum 
    
    +
    
     
    
    "
    
    /t
    
    "
    
     
    
    +
    
     dt.Qualified 
    
    +
    
     
    
    "
    
    /t
    
    "
    
    ;
        sw.WriteLine(line);

    }
    
    
    //
    
    关闭流
    
    

    
        sw.Close();
    fs.Close();
    MessageBox.Show(
    
    "
    
    数据已经成功导出!
    
    "
    
    ,
    
    "
    
    注意
    
    "
    
    ,MessageBoxButton.OK,MessageBoxImage.Information);
}
   
   

 

　　读取excel:

代码

   
   

    
    //
    
    以oledb方式连接excel文件并将混合型作为文本型读取
    
    

    
    strConn 
    
    =
    
     
    
    "
    
    Provider=Microsoft.Jet.  OLEDB.4.0;Data Source=C://Erp1912.xls;Extended   Properties='Excel8.0;HDR=Yes;IMEX=1'
    
    "
    
    ;  
OleDbConnection conn 
    
    =
    
     
    
    new
    
     OleDbConnection(strCon);

    
    //
    
    将工作表作为数据表
    
    

    
    string
    
     sql 
    
    =
    
     
    
    "
    
    SELECT * FROM [Sheet1$]
    
    "
    
    ;
OleDbDataAdapter adp 
    
    =
    
     
    
    new
    
     OleDbDataAdapter(sql,conn);
DataSet myDataSet 
    
    =
    
     
    
    new
    
     DataSet();
adp.Fill(myDataSet,
    
    "
    
    [Sheet1$]
    
    "
    
    );