CODEWITHSUNDEEP
c#asp.netexceldatasetimport-from-excel
user1799214
user1799214

Reputation: 513

How to access hidden column in excel using c#

I'm using Excel.Interop

This is the code for traversing through the excel cells :

for(i=1 ; i< 10; i++) 
{ 
for (int j = 1; j < 10; j++) // 10 is no of columns(static)
                        {   
    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 1, j+1]; // getting value here(its "")
    string strValue = oRng.Text.ToString(); 
     dr[j - 1] = strValue; 

                }}

I have a column hidden in the excel sheet, n when i iterate over that cell, i'm getting "". I dont know as to what should be done in order to get that cell's value.

thanks

PS: the dr used here is a DataRow which i'm further storing it in DataSet

Upvotes: 3

Views: 4826

Answers (3)

Sandeep
Sandeep

Reputation: 288

Even i tried doing this in my solution, but, I think the hidden files cant be read when hidden. but one work around( not recommended) is u could just make the hidden cells/columns hidden to false right before u are reading values from the excel sheet. Like Worksheetobject.Cells[row,colum].Hidden = false. This is not recommended, but works if u want to make it hidden and access.

Upvotes: 0

imsurya
imsurya

Reputation: 164

try this

Public string gethiddenexcellcolumns()
{     

    Excel.Application excel = New Excel.Application();
    excel.Visible = True;
    excel.Workbooks.Add();
    excel.Columns("C:C").Select();
    excel.Selection.EntireColumn.Hidden = True;
    var columns = excel.Columns;
    bool hasHiddenColumns = false;
    foreach(column in columns)
    {
        If(column.Hidden==true)
        {
            hasHiddenColumns = true
        }            
    }
    return "excel.Columns.Hidden = " + hasHiddenColumns.ToString();
}

Upvotes: 2

GeorgesD
GeorgesD

Reputation: 1082

Here. Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

I recommend you to look for a free library like Open Office XML or a not free library like Aspose.

Upvotes: 2

Related Questions