使用C#中的OLEDB获取Excel数据范围

Question

我希望通过使用oledb获取Excel表格中使用的数据范围。代码如下：

String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
                                    + "Data Source=E:\\DOTNET\\CrsMicro\\CA.xls;"
                                    + "Extended Properties='Excel 8.0;HDR=Yes'";


using (OleDbConnection connExcel = new OleDbConnection(strExcelConn))
        {
            string selectString = "SELECT * FROM [CA$A1:D500]";
            using (OleDbCommand cmdExcel = new OleDbCommand(selectString,connExcel))
            {
                cmdExcel.Connection = connExcel;
                connExcel.Open();
                DataTable dt=new DataTable();                    
                OleDbDataAdapter adp = new OleDbDataAdapter();
                adp.SelectCommand = cmdExcel;
                adp.FillSchema(dt, SchemaType.Source);
                adp.Fill(dt);
                int range=dt.Columns.Count;
                int row = dt.Rows.Count;

            //var result = cmdExcel.ExecuteReader();

            //DataTable dtExcelSchema;
            //dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
           // string excelsheetname = dtExcelSchema.Rows[0].ItemArray[2].ToString();
            connExcel.Close();
            //string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        }
    }

我的表格范围并不总是A1:D500，它可能经常变化。因此我需要动态获取表格范围。我知道这可以通过Interop实现，但我需要在OLEDB中完成。有什么建议吗？

- Govind

---

您可以使用 VSTO（Visual Studio Tools for Office）查询 Excel 中的所选范围，或者弹出一个输入框要求用户输入某个范围。VSTO 与 Interop 兼容。 - Mr Balanikas

我可以看到不使用Interop的原因，但是在使用OLEDB驱动程序时存在许多陷阱（文件类型、仅适用于统一表格、奇怪的行类型猜测）。我建议看看托管的替代方案，如OpenXML SDK（用于xlsx）或类似https://github.com/ExcelDataReader/ExcelDataReader的东西。 - Charles Mager

如果您只使用工作表名称，后跟"$"，那么它将返回使用的范围，例如：SELECT * FROM [CA$]。 - Doug Glancy

那听起来像是一个有问题的设计。祝你好运！ - Doug Glancy

为了让您清楚，我想在SSIS中使用SQL命令获取Excel数据，并将其加载到单个OLEDB目标中。如果Excel中有很多列，我将使用union all将它们合并成单个列。这就是为什么我想知道范围的原因。 - Govind

Answer 1

你好，我也在使用OLEDB C# Excel解决同样的问题，我找到了以下解决方案。它对我有效，但我是C#的新手，不确定它的效率如何。但目前它满足了我的要求。这可能对其他人有所帮助。

我能够从浏览的输入Excel文件中获取动态范围（确保Excel文件不包含隐藏工作表）。这对于包含单个工作表的Excel工作簿完美地运行。我还没有测试过多个工作表。

范围：A [stat value]：Column Name [0] // 返回从起始值到列名的所有行。 例如：A1：M0 // 它将返回从A1到列M的所有行。因此，在Excel中无需担心有多少行。只需通过给定的列名称[0]从起点开始获取所有行，所以'0'将是我们的外部范围。

//Code under actual c# clas file where we are uploading excel.
Excel_Common excelComm = new Excel_Common(); // object to Excel_Common class file

string rangeStringwithSHeet =excelComm.GetSheetName(filepath).ToString().Trim('\'') + GetRange(excelComm.GetSheetName(filepath), excelComm.ExcelConn(filepath));

queryForExcelInput = string.Format("SELECT * FROM [{0}]", rangeStringwithSHeet);

Econ1 = new OleDbConnection(excelComm.ExcelConn(filepath));
Econ1.Open();
dataExcelInputTable = new DataTable();
OleDbCommand oleDbCommand1 = new OleDbCommand(queryForExcelInput, Econ1);
OleDbDataAdapter oleDbDaAdapter1 = new OleDbDataAdapter(oleDbCommand1);
oleDbDaAdapter1.Fill(dataExcelInputTable);

Excel_Common类文件包含以下方法：

//Get Range like A4:M30

    public string GetRange(string SheetName, string excelConnectionString)
   {
        string rangeInput = "",rangeColName="";
        int columnsCount = 0;
        int rowStartRange = 0;

        columnsCount = GetNumberOfColumnsInSheet(SheetName, excelConnectionString);
        rowStartRange = GetStartRowRange(SheetName, excelConnectionString); // This is optional if you want always A1. just assign 1 here 
        while (columnsCount > 0)
        {
            columnsCount--;
            rangeColName = (char)('A' + columnsCount % 26) + rangeColName;
            columnsCount /= 26;
        }

        rangeInput = "A" + rowStartRange + ":" + rangeColName + "0";



        return rangeInput;
    }



// Get Sheet Name assuming only one sheet for workbook and no hidden sheets
 public string GetSheetName(string filepath)
 {
 string sheetname = "";
 String connect = ExcelConn(filepath);
 OleDbConnection con = new OleDbConnection(connect);
 con.Open();

 DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        foreach (DataRow row in tables.Rows)
        {
            sheetname = row[2].ToString();
            if (!sheetname.EndsWith("$"))
                continue;

        }

  con.Close();
  return sheetname;
 }


// Get number of columns in a given sheet
    public int GetNumberOfColumnsInSheet(string SheetName, string excelConnectionString)
    {
        int columnsCount = 0;

        //If a valid excel file
        if (!string.IsNullOrEmpty(excelConnectionString))
        {
            using (OleDbConnection conn = new OleDbConnection(excelConnectionString))
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
                if (dt.Rows.Count > 0)
                columnsCount = dt.AsEnumerable().Where(a => a["TABLE_NAME"].ToString() == SheetName).Count();
                conn.Close();
            }
        }
        return columnsCount;
    }


// Get the first row count in sheet contains some keyword . This method call is optional if you always want A1. Here I need to check some keyword exist and from there only I have to start something like A4


public int GetStartRowRange(string SheetName, string excelConnectionString)
 {
     int rowStartRange = 1;

     //If a valid excel file
     if (!string.IsNullOrEmpty(excelConnectionString))
     {
         using (OleDbConnection conn = new OleDbConnection(excelConnectionString))
         {
             string colValue;
             conn.Open();
             string cmdstr = "select * from [" + SheetName + "]";

             OleDbCommand com = new OleDbCommand(cmdstr, conn);
             DataTable dt = new DataTable();
             OleDbDataAdapter da = new OleDbDataAdapter(com);
             da.Fill(dt);



             // get first row data where it started

             foreach (DataRow dataRow in dt.Rows)
             {

                 colValue = dataRow[0].ToString();


                 if ((colValue.Contains("Value1") || colValue.Contains("Value2") || colValue.Contains("Value3")) && (string.IsNullOrEmpty(dataRow[1].ToString()) == false))
                 {
                     rowStartRange = rowStartRange + 1;
                     break;
                 }
                 else
                 {
                     rowStartRange = rowStartRange + 1;
                 }

             }

             conn.Close();


         }

     }
     return rowStartRange;
 }


// Connection to excel document
public string ExcelConn(string FilePath)
{
    string constr = "";
    string extension = Path.GetExtension(FilePath);

    //Checking for the extentions, if XLS connect using Jet OleDB
    if (extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
    {
        constr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\"", FilePath);
    }
    //Use ACE OleDb if xlsx extention
    else if (extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
    {
        constr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\"", FilePath);
    }


    return constr;

} // end of ExcelConn method

- himak

Answer 2

创建一个命名区域： https://msdn.microsoft.com/zh-cn/library/office/ff196817.aspx 并将selectString替换为

"SELECT * FROM [CA$MyNamedRange]"

- Thomas Ertl