Get Excel data range using oledb in c#

Question

I want to get excel sheet used data range by using oledb. Code is below,

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();
        }
    }

my sheet range is not always A1:D500, it may vary frequently. So i need to get the sheet range dynamically. I know this can be achieved by interop, but i need to do it in oledb. Any suggestion?

Answer 1

create a named range: https://msdn.microsoft.com/EN-US/library/office/ff196817.aspx and replace selectString to

"SELECT * FROM [CA$MyNamedRange]"

Answer 2

Hi I'm also working on same kind of problem in OLEDB C# excel, I found below solution. It works for me. But I'm new to C#, I'm not sure how efficient it is. But it is satisfying my requirements so far. This may be helpful for others.

I was able to get dynamic range in an excel sheet from a browsed input excel file (make sure excel file doesn't contain hidden sheets). This works perfectly for excel workbook containing single sheet. I haven't tested with multiple sheets.

Range: A [stat value]: Column Name[0] // Returns all rows from start value till the column name. Example: A1:M0 // It will return all rows from A1 till column M. So here no need to worry how many rows you have in your excel. Just by giving Column Name[0] takes all rows from the starting till the column M. So '0' will be our outer range.

//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 class file has below methods:

//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