excelhelp

using System.Data.OleDb;

using System; 
using System.IO; 
using System.Data; 
using System.Text; 
using System.Collections; 

namespace Wxy.Util
{
    
/// <summary>
    
/// ExcelHelper 的摘要说明。
    
/// </summary>

    public class ExcelHelper
    
{
        
public ExcelHelper()
        
{
            
//
            
// TODO: 在此处添加构造函数逻辑
            
//
        }


        
/// <summary>
        
/// 读取Excel文档
        
/// </summary>
        
/// <param name="Path">文件名称</param>
        
/// <returns>返回一个数据集</returns>

        public static DataSet ExcelToDS(string Path)
        
{
            
try
            
{
                
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
                OleDbConnection conn 
= new OleDbConnection(strConn);
                conn.Open();  
                
string strExcel = "";   
                OleDbDataAdapter myCommand 
= null;
                DataSet ds 
= null;
                strExcel
="select * from [Sheet1$]";
                myCommand 
= new OleDbDataAdapter(strExcel, strConn);
                ds 
= new DataSet();
                myCommand.Fill(ds,
"table1");   
                
return ds;
            }

            
catch(System.Data.OleDb.OleDbException ex)
            
{
                System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ex.Message );
                
return null;
            }

        }


        
public static void DataTableToExcel(System.Data.DataView dataview,string Path,Hashtable NameMap)
        
{
            
try
            
{
                
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
                OleDbConnection conn 
= new OleDbConnection(strConn);
                conn.Open();  
                System.Data.OleDb.OleDbCommand cmd
=new OleDbCommand ();
                cmd.Connection 
= conn;

                
string strSql = string.Empty ,strSql1 = string.Empty ;
                
int i , j ;

                
for(  i = 0 ; i < dataview.Count ; i++ )
                
{
                    
                    strSql 
= "INSERT INTO [sheet1$] (";
                    strSql1 
= ") values(";
                    
for(  j = 0 ; j < dataview.Table.Columns.Count ; j++)
                    
{
                        
if( NameMap.ContainsKey( dataview.Table.Columns[j].ColumnName ) )
                        
{
                            strSql 
+= NameMap[dataview.Table.Columns[j].ColumnName] +"," ;  //2414210
                            strSql1 += "'" +dataview[i][j].ToString() + "',";
                        }

                        
                    }

                    
                    
try
                    
{
                        
if( strSql.EndsWith(",") ) 
                            strSql 
= strSql.Substring(0,strSql.Length - 1 ) ;
                        
if( strSql1.EndsWith(",") ) 
                            strSql1 
= strSql1.Substring(0,strSql1.Length - 1 ) ;

                        strSql1 
= strSql1 + ")";
                        strSql 
= strSql + strSql1 ;

                        cmd.CommandText 
= strSql ;
                        cmd.ExecuteNonQuery();
                    }

                    
catch(Exception ex)
                    
{
                        System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ strSql + strSql1 + ex.Message );
                        
throw new Exception(strSql + ex.Message);
                    }

                }

                conn.Close ();
            }

            
catch(System.Data.OleDb.OleDbException ex)
            
{
                System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ex.Message );
            }

        }


        
public static void DataTableToExcel(System.Data.DataView dataview,string Path)
        
{
            
try
            
{
                
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
                OleDbConnection conn 
= new OleDbConnection(strConn);
                conn.Open();  
                System.Data.OleDb.OleDbCommand cmd
=new OleDbCommand ();
                cmd.Connection 
= conn;

                
string strSql = string.Empty ,strSql1 = string.Empty ;
                
int i , j ;

                
for(  i = 0 ; i < dataview.Count ; i++ )
                
{
                    
                    strSql 
= "INSERT INTO [sheet1$] (";
                    strSql1 
= ") values(";
                    
for(  j = 0 ; j < dataview.Table.Columns.Count ; j++)
                    
{
                        strSql 
+= dataview.Table.Columns[j].ColumnName +"," ;  
                        strSql1 
+= "'" +dataview[i][j].ToString() + "',";
                    }

            
//        
                    try
                    
{
                        
if( strSql.EndsWith(",") ) 
                            strSql 
= strSql.Substring(0,strSql.Length - 1 ) ;
                        
if( strSql1.EndsWith(",") ) 
                            strSql1 
= strSql1.Substring(0,strSql1.Length - 1 ) ;
                        strSql1 
= strSql1 + ")";
                        strSql 
= strSql + strSql1 ;
                        cmd.CommandText 
= strSql  ;
                        cmd.ExecuteNonQuery();
                    }

                    
catch(Exception ex)
                    
{
                        System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ strSql + ex.Message );
                        
throw new Exception(strSql + ex.Message);
                    }

                }

                conn.Close ();
            }

            
catch(System.Data.OleDb.OleDbException ex)
            
{
                System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ex.Message );
            }

        }


        
/// <summary>
        
/// 写入Excel文档
        
/// </summary>
        
/// <param name="Path">文件名称</param>

        public bool SaveFP2toExcel(string Path)
        
{
            
try
            
{
                
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
                OleDbConnection conn 
= new OleDbConnection(strConn);
                conn.Open();  
                System.Data.OleDb.OleDbCommand cmd
=new OleDbCommand ();
                cmd.Connection 
=conn;
                cmd.CommandText 
="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
                cmd.ExecuteNonQuery ();
    
/*            for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
                {
                    if(fp2.Sheets [0].Cells[i,0].Text!="")
                    {
                        cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+
                            fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+
                            "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";
                        cmd.ExecuteNonQuery ();
                    }
                }
                
*/

                conn.Close ();
                
return true;
            }

            
catch(System.Data.OleDb.OleDbException ex)
            
{
                System.Diagnostics.Debug.WriteLine (
"写入Excel发生错误:"+ex.Message );
            }

            
return false;
        }


    }

}
posted @ 2009-07-23 21:14  夜色狼  阅读(257)  评论(0编辑  收藏  举报