Excel OLEDB导入限制 [英] Excel OLEDB Import Restriction

问题描述

有谁知道如何克服ACE/OLEDB 255色谱柱导入OLEDB的限制?

尝试使用命名范围，例如[Sheet1 $ A1:E10]，但它不起作用

Hi,

Does anyone know how to overcome the ACE/OLEDB 255 column import OLEDB restriction?

Tried to use a named range eg [Sheet1$A1:E10] but it did not work

推荐答案

A1:E10]，但它不起作用

A1:E10] but it did not work

到目前为止，我还没有听到任何此类限制，这可能是您的连接字符串问题.

请尝试以下方法:

I havent heard any such restrictions so far, it could be your connection string issue.

Please try the following methods :

public string GetConnectionString(string FileNamePath, bool HasHeader)
        {
            string ConnectionString = "";
            string Extension = Path.GetExtension(FileNamePath).ToLower();

            string BinaryExcelProvider = "Microsoft.Jet.OLEDB.4.0";
            string XmlExcelProvider = "Microsoft.ACE.OLEDB.12.0";
            string BinaryExcelExtProperties = "Excel 8.0";
            string XmlExcelExtProperties = "Excel 12.0";
            string XmlMacroExcelExtProperties = "EXCEL 12.0 Macro";

            string Provider = "";
            string ExtendedProperties = "";

            switch (Extension)
            {
                case ".xls":
                    Provider = BinaryExcelProvider;
                    ExtendedProperties = BinaryExcelExtProperties;
                    break;

                case ".xlsx":
                    Provider = XmlExcelProvider;
                    ExtendedProperties = XmlExcelExtProperties;
                    break;

                case ".xlsm":
                    Provider = XmlExcelProvider;
                    ExtendedProperties = XmlMacroExcelExtProperties;
                    break;
            }

            string Header = ";HDR=NO;IMEX=1";
            if (HasHeader)
                Header = ";HDR=YES;IMEX=1";
            string ConnectionStringFormat = "Provider={0};Data Source={1};Extended Properties=\"{2}{3}\";";

            ConnectionString = string.Format(ConnectionStringFormat, Provider, FileNamePath, ExtendedProperties, Header);
            return ConnectionString;
        }

public DataSet ReadWorkSheet(string excelFile)
        {
            Success = false;
            System.Data.DataSet excelDataSet = new DataSet();
            string connectionString = this.GetConnectionString(excelFile);


            using (OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                try
                {
                    objConn.Open();
                    OleDbDataAdapter cmd = new OleDbDataAdapter("select * from [Sheet1

A1:E10]"，objConn)； cmd.Fill(excelDataSet，" ); cmd.Dispose(); } 捕获 {} 最终 { 如果(objConn！= 空) { objConn.Close(); objConn.Dispose(); } } } 返回 excelDataset }

A1:E10]" , objConn); cmd.Fill(excelDataSet, "MyData"); cmd.Dispose(); } catch { } finally { if (objConn != null) { objConn.Close(); objConn.Dispose(); } } } return excelDataset }

谢谢，

Kuthuparakkal

Thanks,

Kuthuparakkal

这篇关于Excel OLEDB导入限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！