Solved

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

Posted on 2016-08-16
15
92 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
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
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
 
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
zephyr_hex 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 42

Accepted Solution

by:
zephyr_hex 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
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
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
ID: 41760323
yay!  glad you got it working.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to count occurrences of each item in an array.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now