Is it possible to ignore hidden columns when I use OLEDB to load the Excel file in C#?

thomaszhwang
thomaszhwang used Ask the Experts™
on
I can load the entire Excel file now, but I don't want to load those hidden columns in Excel.  Is there a way to do that?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
The only way to ignore hidden columns is to copy the worksheet contents to the clipboard and then process the text part of the clipboard.

Or parse the worksheet while ignoring hidden columns.

But you can open an Excel workbook and ask it to leave the hidden columns behind.

Kevin
NorieAnalyst Assistant
Commented:
What do you mean by 'load'?

Are you getting data from Excel or putting it in Excel?

Author

Commented:
By "load", I meant I can use OLEDB to connect to the Excel file and load one of the worksheets into a DataTable component.

I'm getting data from Excel to a DataTable Component.  Thanks.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
Thanks zorvek.  I don't think copy and past the entire worksheet into another worksheet would work for me.

What do you mean by "parse the worksheet while ignoring hidden columns"?
Top Expert 2008
Commented:
Excel only ignores hidden columns and rows when you copy to the clipboard and then to another application, not when copying and pasting within Excel. There is one exception: rows hidden with AutoFilter are not copied within Excel.

So to accomplish what you want you can either parse the cells while ignoring hidden columns, or you can copy the data to the clipboard and then read the text buffer out of the clipboard. But you cannot copy the data from one worksheet to another and expect hidden columns to be left behind.

Kevin
Top Expert 2008
Commented:
When I say parse the worksheet I mean move through the worksheet cell by cell, copying each cell's contents to your destination array.

Kevin

Author

Commented:
Thanks Kevin.  Copying the content simply doesn't work in my situation.
Top Expert 2008
Commented:
Then you will have to parse the data cell by cell. The VBA code would look like this:

    Dim SourceWorkbook As Workbook
    Dim SourceWorksheet As Worksheet
    Dim SourceRange As Range
    Dim Row As Long
    Dim Column As Long
   
    Set SourceWorkbook = ThisWorkbook
    Set SourceWorksheet = SourceWorkbook.Worksheets(1)
    Set SourceRange = SourceWorksheet.UsedRange
    For Row = 1 To SourceRange.Rows.Count
        For Column = 1 To SourceRange.Columns.Count
            If Not SourceRange.Cells(Row, Column).EntireColumn.Hidden Then
                Debug.Print Row & ", " & Column & ": " & SourceRange.Cells(Row, Column).Value
            End If
        Next Column
    Next Row

Kevin
I have successfuly Created a Solution for you using Oledb here it is Use the following method to Get a DataTable object excluding the hidden column :
public DataTable GetExcelTableExcludingHiddenColumns(string hiddenColumnName, string sheetName, string fileName)
        {
            string con = @"Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
            DataTable dt;
            DataTable tempTable = new DataTable();
            using (OleDbConnection cn = new OleDbConnection(con))
            {
                cn.Open();
                using (OleDbCommand cm = new OleDbCommand("Select * From ["+sheetName+"$]", cn))
                {
                    OleDbDataAdapter da = new OleDbDataAdapter(cm);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    

                    foreach (DataColumn cl in dt.Columns)
                    {
                        if (cl.ColumnName != hiddenColumnName)
                        {
                            tempTable.Columns.Add(new DataColumn(cl.ColumnName));
                        }
                    }
                    dataGridView1.DataSource = tempTable;
                    DataRow[] arr = new DataRow[dt.Rows.Count];
                    dt.Rows.CopyTo(arr, 0);

                    int columnCount = tempTable.Columns.Count;

                    foreach (DataRow dr in arr)
                    {
                        DataRow row = tempTable.NewRow();
                        for (int i = 0; i < columnCount; i++)
                        {
                            row[i] = dr[i];
                        }
                        tempTable.Rows.Add(row);
                    }
                }
                return tempTable;
            }

Open in new window


To call the above method use the following code :

 
DataTable dt = GetExcelTableExcludingHiddenColumns("IsAdult", "Sheet1", @"C:\MyExcelFile.xls");

Open in new window


I am Supposing that the hidden column's Name is "IsAdult" name of the excel Sheet is Sheet1
You need to know the name of the hidden column..

Author

Commented:
Thanks both of you.  If we cannot determine if a column is hidden through OLEDB, it would not work for me.

The Excel I'm working with is actually a very complicated Excel application with sheet protection.  I cannot un-hide those hidden columns.  Neither can I see the name of those columns.  What makes it worse, the data in the worksheet is not even strictly column based.

I'm going to close this case.  Thank you guys.  I really appreciate your efforts.
NorieAnalyst Assistant

Commented:
thomas

I was going to post a similar solution to starlite but I wondered if it would work for you because of needing to name the columns.

I didn't get round to try anything which would work without knowing the names, but now that you've mentioned all these other things I don't think that's going to work either.

Can you even use OLEDB with a protected worksheet?

Author

Commented:
Yes, I can use OLEDB against a protected worksheet.  Since the column names are unknown, OLEDB parses them as F1, F2, F3 and etc.  In my case, the thing is the Excel uses Macro, the column with the data I want may not be the same column the next time when data in another worksheet is changed.
NorieAnalyst Assistant

Commented:
Well can't you identify the hidden columns by name, or do you have columns you need called F1, F2 etc.

That's what I was thinking of but assumed you had dismissed the idea, and it might not work anyway.

Or don't you know any of the column names?

By the way, the mention of some of the data not being column based also made me wonder about whether using OLEDB would work.

Author

Commented:
No, there is no way you can get a "fixed" column name.  The content on the worksheet is dependent on the input on another worksheet and the process is automatically controlled by Macro.

What *is* fixed is a pattern of the display of the data.  However in order to make the pattern work, I need to ignore the hidden columns in that worksheet.  Unfortunately, OLEDB cannot do that.

Although the data is not column-based, OLEDB can still take everything out as text.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial