c#操作Excel

標籤：eric   taf   mic   pat   .exe   null   state   style   varchar   

using System;using System.Collections.Generic;using System.Text;using System.Data.OleDb;using System.Data;namespace BizUpdate.Implement{    public class ExcelOperator    {        /// <summary>        /// 串連Excel        /// </summary>        /// <param name="filePath">資料庫地址</param>        public static OleDbConnection ExcelConnection(string filePath)        {            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";            OleDbConnection odconn = new OleDbConnection(string.Format(connectionString, filePath));            return odconn;        }        /// <summary>        /// 從Excel中讀取資料        /// </summary>        /// <param name="filePath">Excel檔案</param>        /// <param name="selectQuery">SQL</param>        /// <returns></returns>        public static DataTable GetDataFromExcel(string filePath, string selectQuery)        {            OleDbConnection oconn = ExcelConnection(filePath);            try            {                if (oconn.State == ConnectionState.Closed)                { oconn.Open(); }                OleDbCommand odbc = oconn.CreateCommand();                odbc.CommandType = CommandType.Text;                odbc.CommandText = selectQuery;                OleDbDataAdapter da = new OleDbDataAdapter(odbc);                DataSet ds = new DataSet();                da.Fill(ds);                if (oconn.State != ConnectionState.Closed)                {                    oconn.Close();                }                foreach (DataRow dr in ds.Tables[0].Rows)                {                    foreach (DataColumn dc in ds.Tables[0].Columns)                    {                        if (dc.DataType == typeof(string))                        {                            if (dr[dc] == DBNull.Value)                            {                                dr[dc] = "";                            }                        }                    }                }                return ds.Tables[0];            }            catch (Exception ex)            {                throw ex;            }            finally            {                if (oconn.State != ConnectionState.Closed)                {                    oconn.Close();                }            }        }        /// <summary>        /// DataTabl寫入到Excel        /// </summary>        /// <param name="filePath"></param>        /// <param name="selectQuery"></param>        /// <returns></returns>        public static bool WriteDataToExcel(string filePath, DataTable dt, string TableName)        {            OleDbConnection oconn = ExcelConnection(filePath);            try            {                dt.TableName = TableName;                if (oconn.State == ConnectionState.Closed)                { oconn.Open(); }                OleDbCommand odbc = oconn.CreateCommand();                odbc.CommandType = CommandType.Text;                if(dt !=null && dt.Rows.Count > 0)                {                    string creSql = string.Format("CREATE TABLE {0} (",dt.TableName);                    for(int i = 0;i<dt.Columns.Count;i++)                    {                        if(i==dt.Columns.Count -1)                        {                           creSql += dt.Columns[i].ColumnName + " VARCHAR)";                        }                        else                        {                            creSql += dt.Columns[i].ColumnName + " VARCHAR,";                        }                    }                    odbc.CommandText = creSql;                    odbc.ExecuteNonQuery();                    foreach (DataRow row in dt.Rows)                    {                        string inSql = string.Format("insert into {0} values(", dt.TableName);                        for (int i = 0; i < dt.Columns.Count; i++)                        {                            if (i == dt.Columns.Count - 1)                            {                                inSql += "‘" + row[i].ToString() + "‘)";                            }                            else                            {                                inSql += "‘" + row[i].ToString() + "‘,";                            }                        }                        odbc.CommandText = inSql;                        odbc.ExecuteNonQuery();                    }                }                if (oconn.State != ConnectionState.Closed)                {                    oconn.Close();                }                return true;            }            catch (Exception ex)            {                return false;                throw new Exception("寫"+dt.TableName + "失敗"+ex.Message);            }            finally            {                if (oconn.State != ConnectionState.Closed)                {                    oconn.Close();                }            }        }    }}

 

c#操作Excel