C# OleDbConnection对特定部分Excel的数据读取

最近在写winform程序，先来一个简单的。

读取特定部分Excel的数据读取，读取Excel第30行开始到H列的数据

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Demo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string path = @"D:\test.xls";

            List<DataTable> list = new List<DataTable>();

            for (int i = 1; i < 6; i++)
            {
                string tableName = GetExcelFirstTableName(path, i) + "A29:H";
                tableName = tableName.Replace("\'", "");

                //设置TSql
                string TSql = "SELECT * FROM [" + tableName + "]";

                //读取数据
                DataTable ds = ExcelToDataSet(path, TSql).Tables[0];

　　　　　　//移除后三行

                // for (int j = 0; j < 3; j++)
                //{
                //　　ds.Rows.RemoveAt(ds.Rows.Count - 1);
                //}              
                //ds.AcceptChanges();
                if (ds.Rows.Count > 0)
                {
                    list.Add(ds);
                }
            }

        }

        public static DataSet ExcelToDataSet(string filename, string TSql)
        {
            DataSet ds;
  
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";

            using (OleDbConnection myConn = new OleDbConnection(strCon))
            {

                string strCom = TSql;

                myConn.Open();

                OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);

                ds = new DataSet();

                myCommand.Fill(ds);

                myConn.Close();

            }
            return ds;

        }

        public static string GetExcelFirstTableName(string fullPath, int i)
        {
            string tableName = null;

            if (File.Exists(fullPath))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +

                "OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fullPath))
                {
                    conn.Open();
                    tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[i][2].ToString().Trim();
                    conn.Close();
                }
            }
            return tableName;
        }
    }
}

转载于:https://www.cnblogs.com/yao3364/p/5821102.html