Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-16
15
Medium Priority
?
153 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:Michael Sterling
  • 8
  • 7
15 Comments
 
LVL 44

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:Michael Sterling
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 44

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:Michael Sterling
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 44

Assisted Solution

by:zephyr_hex (Megan)
zephyr_hex (Megan) earned 2000 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:Michael Sterling
ID: 41758609
Thank you. Lastly: what about concatenating 2 columns?
0
 
LVL 1

Author Comment

by:Michael Sterling
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 44

Accepted Solution

by:
zephyr_hex (Megan) earned 2000 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:Michael Sterling
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 44

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:Michael Sterling
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 44

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:Michael Sterling
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:Michael Sterling
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 44

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

885 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