Solved

How do I select specific cells from my excel spreadsheet for my GridView control.

Posted on 2016-08-16
15
110 Views
Last Modified: 2016-08-17
I'm trying to import an excel spreadsheet into a GridView control, except I'm only needing a few of the columns. How do I select just the columns (cells) from each row that I need for my GridView control? Below is my current code that imports every column.

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)
    {
        string conStr = "";

        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;

            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }

        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();       
        cmdExcel.Connection = connExcel;

        //Get the name of First Sheet
        connExcel.Open();
        DataTable dtExcelSchema;
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        connExcel.Close();

        //Read Data from First Sheet
        connExcel.Open();
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dt);
        connExcel.Close();

        //Bind Data to GridView
        GridView1.Caption = Path.GetFileName(FilePath);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

Open in new window

0
Comment
Question by:mikesExpertExchange
  • 8
  • 7
15 Comments
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 41758445
You could change your query that selects the columns from the spreadsheet so that it only selects the ones you need.

Change this:
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

Open in new window


to something like this:
cmdExcel.CommandText = "SELECT [column1],[column3] From [" + SheetName + "]";

Open in new window


Where [column1], [column3] are the actual column names for the first and third columns.

So, it could look something like :

cmdExcel.CommandText = "SELECT [Name],[City] From [" + SheetName + "]";

Open in new window

0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41758460
@zephyr_hex: I knew this should be easier that I was making it. I am struggling with the syntax on how to get the cells / columns that I want. If you have an example that would be great. I'm still taking stabs at it right now.
0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 41758531
Please note:  A cell is not a column.  A cell is the intersection of a row and a column.  So it's a little confusing when you use the two terms interchangeably.  I *think* you want to only include certain columns from your spreadsheet.

If that's the case, please tell us the column names you want.  It should be as simple as using those column names in your SELECT statement instead of SELECT *

If that's not the case and you truly do want only certain cells, you need to  tell us how you identify which rows to choose, as well as telling us the names of the columns.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41758543
@zephyr_hex: you're right, my mistake. I actually got it to work using the following:

cmdExcel.CommandText = "SELECT [" + SheetName + "].Gender From [" + SheetName + "]";

Open in new window

   
My problem is that some of the names include a ".". How do I account for that?
0
 
LVL 42

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 500 total points
ID: 41758602
Replace the period with a '#'.  It's also a good practice to wrap the column name in square brackets, too.

So, if your column name is Gender.Stuff, you would do:

cmdExcel.CommandText = "SELECT [" + SheetName + "].[Gender#Stuff] From [" + SheetName + "]";

Open in new window

0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41758609
Thank you. Lastly: what about concatenating 2 columns?
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41758612
Also, I thought I was getting an error message telling me that there was an improper use of brackets, when I try to bracket the column name as well. I will double check that.
0
 
LVL 42

Accepted Solution

by:
zephyr_hex (Megan) earned 500 total points
ID: 41758668
I think you may need to tag the $ on to the end of your sheetname.  It shouldn't be necessary to preface the column name with the sheetname.

And, concatenation uses the + operator.

"SELECT [Gender] + [Column2] FROM [" + SheetName + "$]"

Open in new window


if you want a space between them:

"SELECT [Gender] + ' ' + [Column2] FROM [" + SheetName + "$]"

Open in new window

0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41758812
@zephyr_hex: thank you for the concatenation and adding spacing, that worked. For some reason I'm still getting a funky error when the column name has a period (".") in it?

No value given for one or more required parameters.

See below.

Server Error in '/' Application.
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:


Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "].[Org#ID] FROM [" + SheetName + "]";
Line 99:         oda.SelectCommand = cmdExcel;
Line 100:        oda.Fill(dt);
Line 101:        connExcel.Close();
Line 102:


Source File: c:\Dev\QUPowerHours\LoadStudents.aspx.cs    Line: 100 

Open in new window

0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 41759503
Are you sure that's what's causing the error?  To test, did you remove that column from your query statement?  It works when you don't include that column name?
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41759576
@zephyr_hex: Yes, I'm pretty sure this is causing it. If I change the column name so that it doesn't have a period "." in it, it works fine. I only get an error when the column name has a period in it, and I put the "#" character in the column name. I've tried adding the "$" above as well.

Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "$].[Org#Desc] FROM [" + SheetName + "]";

Open in new window


without the "#" I get an: "Invalid bracketing of name..." error.

 Invalid bracketing of name '['August Report#csv$'$].[Org.Desc]'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Invalid bracketing of name '['August Report#csv$'$].[Org.Desc]'.

Source Error:

Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "$].[Org.Desc] FROM [" + SheetName + "]";
Line 99:         oda.SelectCommand = cmdExcel;
Line 100:        oda.Fill(dt);
Line 101:        connExcel.Close();
Line 102:

Open in new window

0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 41759986
One way to debug it is to put a break point on line 100, and change the query back to SELECT *

Inspect the results of oda in debug mode to see how the column name is appearing.  Then use that name in your SELECT query.
0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41760235
@zephyr_hex: haha all it showed was: "Select * FROM ['August Report#csv$']. I laugh because I feel like I tried to trick it and it beat me! ha!

I tried this:

"SELECT [" + SheetName + "].Org#ID FROM [" + SheetName + "]"

Open in new window


When I put the "#" in, but I get this:

I get: "Syntax error in date in query expression '['August Report#csv$'].Org#ID'."

This is what the oda.SelectCommand text looks like:

SELECT ['August Report#csv$'].Org#ID FROM ['August Report#csv$']

Open in new window

0
 
LVL 1

Author Comment

by:mikesExpertExchange
ID: 41760268
@zephyr_hex: I figured it out: I needed to put the column name (with the "#" for the ".") in the qualifier like so:

"SELECT [" + SheetName + ".Org#ID] FROM [" + SheetName + "]";

Open in new window

0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 41760323
yay!  glad you got it working.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question