CNAS/CnasSynchronusDAL/DAL/ExcelDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using CnasSynchronousCommon;

namespace CnasSynchronusDAL
{
    public static class ExcelDAL
    {

        /// <summary>
        /// 读取Excel第一个工作表的内容
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static DataTable ReadExcelFirstSheetToTable(string strPath, string strExcelFileVersion)
        {
            DataTable dt = new DataTable();
            try
            {
                //连接字符串
                //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                string connstring = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = " + strPath + "; Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'; "; //Office 07以下版本 
                if (!string.IsNullOrWhiteSpace(strExcelFileVersion) && strExcelFileVersion != "0")
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字
                    string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
                    string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); //查询字符串
                    //string sql = string.Format("SELECT * FROM [{0}] WHERE [日期] is not null", firstSheetName); //查询字符串

                    OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);
                    return set.Tables[0];
                }
            }
            catch(Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dt;
        }

        internal static DataTable ReadExcelTableStruct(string strPath, string strExcelFileVersion,string strViewName,string strViewSQL, string strTableName)
        {
            DataTable dtNormal = new DataTable();
            try
            {
                //连接字符串
                string connstring = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = " + strPath + "; Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'; "; //Office 07以下版本                 
                if (!string.IsNullOrWhiteSpace(strExcelFileVersion) && strExcelFileVersion != "0")
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;

                    if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                        sql = strViewSQL + " where 1=0";
                    else
                        sql = string.Format("SELECT * FROM [{0}$] where 1=0", strTableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);

                    dtNormal = set.Tables[0];                   
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dtNormal;
        }

        /// <summary>
        /// 通过表名读取Excel中数据
        /// </summary>
        /// <param name="strPath"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static DataTable ReadExcelByNameToTable(string strPath, string strExcelFileVersion, string strViewName,string strViewSQL,string strTableName)
        {
            DataTable dtNormal = new DataTable();
            try
            {
                //连接字符串
                string connstring = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = " + strPath + "; Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'; "; //Office 07以下版本                 
                if (!string.IsNullOrWhiteSpace(strExcelFileVersion) && strExcelFileVersion != "0")
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;

                    if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                        sql = strViewSQL;
                    else
                        sql = string.Format("SELECT * FROM [{0}$]", strTableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);

                    DataTable dtSheetData = set.Tables[0];

                    DataRow drCurrent = dtSheetData.Rows[0];
                    //创建标准表结构
                    foreach (DataColumn dc in dtSheetData.Columns)
                    {
                        DataColumn dcReady = new DataColumn
                        {
                            DataType = dc.DataType,
                            ColumnName = drCurrent[dc.ColumnName].ToString()
                        };
                        dtNormal.Columns.Add(dcReady);
                    }
                    //向列表中添加数据
                    for (int i = 1; i < dtSheetData.Rows.Count; i++)
                    {
                        DataRow drNew = dtNormal.NewRow();
                        for (int j = 0; j < dtSheetData.Columns.Count; j++)
                        {
                            drNew[j] = dtSheetData.Rows[i][j];
                        }
                        dtNormal.Rows.Add(drNew);
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dtNormal;
        }


        /// <summary>
        /// 读取Excel中所有表的表名
        /// </summary>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public static Dictionary<string, DataTable> ReadExcelTableNameToTable(string strPath,string strExcelFileVersion)
        {
            Dictionary<string, DataTable> dictTables = new Dictionary<string, DataTable>();
            try
            {
                //连接字符串
                string connstring = "Provider = Microsoft.JET.OLEDB.4.0; Data Source = " + strPath + "; Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1'; "; //Office 07以下版本 
                if (!string.IsNullOrWhiteSpace(strExcelFileVersion) && strExcelFileVersion != "0")
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;
                    foreach (DataRow dr in sheetsName.Rows)
                    {
                        string strTableName = dr[2].ToString();

                        sql = string.Format("SELECT * FROM [{0}]", strTableName); //查询字符串
                        ada = new OleDbDataAdapter(sql, connstring);
                        DataSet set = new DataSet();
                        ada.Fill(set);

                        //DataTable dtSheetData = set.Tables[0];
                        //DataTable dtNormal = new DataTable();
                        //DataRow drCurrent = dtSheetData.Rows[0];
                        ////创建标准表结构
                        //foreach (DataColumn dc in dtSheetData.Columns)
                        //{
                        //    DataColumn dcReady = new DataColumn
                        //    {
                        //        DataType = dc.DataType,
                        //        ColumnName = drCurrent[dc.ColumnName].ToString()
                        //    };
                        //    dtNormal.Columns.Add(dcReady);
                        //}
                        //dictTables.Add(strTableName.ToUpper(), set.Tables[0]);
                        dictTables.Add(strTableName.ToUpper(), set.Tables[0]);
                    }
                }
            }
            catch(Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dictTables;
        }


        public static  DataTable ReadExcelTableNameToTableByDate(string strPath,string strExcelFileVersion,string strViewName,string strViewSQL,string strTableName,string strDateColumn,string strDate)
        {
            DataTable dtNormal = new DataTable();
            try
            {
                //连接字符串
                //string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                string connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Office 07以下版本 
                if (!string.IsNullOrWhiteSpace(strExcelFileVersion) && strExcelFileVersion != "0")
                    connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
                using (OleDbConnection conn = new OleDbConnection(connstring))
                {
                    conn.Open();
                    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //得到所有sheet的名字

                    string sql = "";
                    OleDbDataAdapter ada = null;

                    if (strViewName == strTableName && !string.IsNullOrWhiteSpace(strViewName))
                        sql = strViewSQL;
                    else
                        sql = string.Format("SELECT * FROM [{0}]", strTableName); //查询字符串
                    ada = new OleDbDataAdapter(sql, connstring);
                    DataSet set = new DataSet();
                    ada.Fill(set);

                    dtNormal = set.Tables[0].Clone();
                    if (set.Tables[0].Rows.Count > 0)
                    {
                        DataRow[] rows = set.Tables[0].Select(string.Format("{0}>'{1}'", strDateColumn, strDate));
                        foreach (DataRow dr in rows)
                        {
                            DataRow drNew = dtNormal.NewRow();
                            drNew.ItemArray = dr.ItemArray;
                            dtNormal.Rows.Add(drNew);                          
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //发生异常，写入日志
                AppLog.Error(ex.Message);
            }
            return dtNormal;
        }
    }
}