首页 » .NET » C# OleDb读取Excel数据

C# OleDb读取Excel数据

原文 http://blog.csdn.net/luolei3344/article/details/79046058

2018-01-13 02:00:35阅读(544)

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
namespace SYS_TEST.BaseClass
{
    ///OleDB方式
    ///优点:将Excel直接当做数据源处理,通过SQL直接读取内容,读取速度较快。
    ///缺点:读取数据方式不够灵活,无法直接读取某一个单元格,只有将整个Sheet页读取出来后(结果为Datatable)再在Datatable中根据行列数来获取指定的值。
    public class OLEDBClass
    {
        /// <summary>
        /// 获取EXCEL的Sheet表名
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static object[] GetExcelSheetName(string fileName)
        {
            ArrayList sheetNames = new ArrayList();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    conn.Close();
                    for (int i = 0; i != dt.Rows.Count; i++)
                    {
                        sheetNames.Add(dt.Rows[i]["Table_Name"].ToString());
                    }
                }
            }
            catch { }
            return sheetNames.ToArray();
        }
        /// <summary>
        /// Excel转换成DataSet
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileName)
        {
            DataSet ds = new DataSet();
            try
            {
                if (File.Exists(fileName))
                {
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
                    conn.Open();
                    string comm = "select * from [sheet1$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                }
            }
            catch { }
            return ds;
        }
        /// <summary>
        /// DataSet更新到Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="oldDs"></param>
        public static void DataSetToExcel(string fileName, DataSet oldDs)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + fileName);
            conn.Open();
            string comm = "select * from [sheet1$]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter(comm, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
            //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。
            builder.QuotePrefix = "[";//获取insert语句中保留字符(起始位置)
            builder.QuoteSuffix = "]";//获取insert语句中保留字符(结束位置)
            DataSet newDs = new DataSet();
            myCommand.Fill(newDs, "table1");
            for (int i = 0; i < oldDs.Tables[0].Rows.Count; i++)
            {
                //此处不能使用ImportRow方法将一行导入到newDs中,因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。
                //在使用ImportRow后newds内有值但不能更新到Excel中,因为所有导入行的DataRowState!=Added
                DataRow newDr = newDs.Tables["Table1"].NewRow();
                for (int j = 0; j < newDs.Tables[0].Columns.Count; j++)
                {
                    newDr[j] = oldDs.Tables[0].Rows[i][j];
                }
                newDs.Tables["Table1"].Rows.Add(newDr);
            }
            myCommand.Update(newDs, "Table1");
            conn.Close();
        }
        /// <summary>
        /// 将Excel读取到DataSet(多Sheet表)
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDataTable(string fileName)
        {
            //选择源
            string strConn = "";
            strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\"";
            if (Path.GetExtension(fileName).Trim().ToUpper() == ".XLSX")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
            }
            //定义数据集
            DataSet ds = new DataSet();
            //连接数据源
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                string strSheetName = schemaTable.Rows[i][2].ToString().Trim().Trim('\'');
                if (strSheetName.Contains("$") && !strSheetName.EndsWith("$"))
                    continue;
                if (schemaTable.Rows[i][3].ToString().Trim().ToUpper().Equals("TABLE"))
                {
                    //Sql语句
                    string strExcel = string.Format("select * from [{0}]", strSheetName);
                    //适配到数据源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, strSheetName);
                }
            }
            conn.Close();
            //删除空的Excel表单
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DataTable dt = ds.Tables[i];
                if (dt.Columns.Count == 1 && dt.Columns[0].ColumnName.Trim().ToUpper() == "F1")
                {
                    ds.Tables.Remove(dt);
                    i--;
                }
            }
            return ds;
        }
    }
}

最新发布

CentOS专题

关于本站

5ibc.net旗下博客站精品博文小部分原创、大部分从互联网收集整理。尊重作者版权、传播精品博文,让更多编程爱好者知晓!

小提示

按 Ctrl+D 键,
把本文加入收藏夹