c#操作Excel-白红宇

c#操作Excel

阅读量：5262 次

发布时间：2019-06-14

本文共 2903 字，大约阅读时间需要 9 分钟。

using System;using System.Collections.Generic;using System.Text;using System.Data.OleDb;using System.Data;namespace BizUpdate.Implement{    public class ExcelOperator    {        ///         /// 连接Excel        /// 
        /// 数据库地址        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;        }        ///         /// 从Excel中读取数据        /// 
        /// Excel文件        /// SQL        /// 
             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();                }            }        }        ///         /// DataTabl写入到Excel        /// 
        ///         ///         /// 
             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