ExcelHelper

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;
        }

    }
}