Display excel in listview on button click

Question

0.00/5 (No votes)

See more:

Im having the excel file which i read by initialising oledb. I just want to display the read excel file in list view.
Thanks in advance for helpers!!

What I have tried:

C#

void InitializeOledbConnection(string filename, string extrn)
        {
            connString = "";

            if (extrn == ".xlsx")

                connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + glb + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; //for above excel 2007
            else

                connString = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + glb + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; //for below excel 2007

            Oledb = new OleDbConnection(connString);

        }


private DataTable ReadFile()

        {
            schemaTable = new DataTable();
            OleDbCommand Oledbcmd = new OleDbCommand();
            Oledbcmd.Connection = Oledb;
            Oledb.Open();
            Oledbcmd.CommandText = "Select SIGNAL,ROUTE_BUTTON from [CONTROL_TABLE$]";
            OleDbDataAdapter da = new OleDbDataAdapter(Oledbcmd);
            ContentTable = new DataTable();
            da.Fill(ContentTable);
            OleDbDataReader dr = Oledbcmd.ExecuteReader();
            ContentTable = null;
            
            dr.Close();
            Oledb.Close();
            Oledb.Dispose();
            return ContentTable;

        }

 private void t1NextTabRedirectButton_Click(object sender, EventArgs e)
        
            {
                string filePath = string.Empty;
                string fileExt = string.Empty;

                InitializeOledbConnection("C:\\gui_Files\\ICT_LAYOUT_AJJ.xlsx", ".xlsx");
                DataTable schemaTable = ReadFile();

               
                    routeNamesListView.Columns.Add("SIGNAL");
                    routeNamesListView.Columns.Add("ROUTE_BUTTON");
                    routeNamesListView.View = View.List;
                    routeNamesListView.CheckBoxes = true;
            }

Posted 1-Jan-18 20:30pm

Arvi.S

Updated 1-Jan-18 22:55pm

Add a Solution

Comments

Karthik_Mahalingam 2-Jan-18 2:49am

are you getting any error?

Arvi.S 2-Jan-18 3:32am

I dont get any errors.When i kept break point and debug, in the button event "DataTable schemaTable" has null values.

Maciej Los 2-Jan-18 3:10am

What's wrong with your code? Improve your question and provide more details...

Arvi.S 2-Jan-18 3:37am

Established a connection string in oledb to use excel.
In the next function, im reading particular columns of a sheet from the excel file.
What i want is:
when the button is clicked, the file has to be read and should be displayed in listview.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Answer 1 · 2018-01-01T22:17:00

Solution 1

remove this line

ContentTable = null;

Posted 1-Jan-18 22:17pm

Karthik_Mahalingam

Comments

Arvi.S 2-Jan-18 4:29am

Tried removing it.
Also i removed "schemaTable = new DataTable" from read file function as it is never used.
But there is no effect!

Karthik_Mahalingam 2-Jan-18 4:32am

still getting null or empty table ?

Arvi.S 2-Jan-18 4:38am

could gain the column, row count of content table in the DataTable ReadFile function. But the same values of content table could not be seen in the button click event.

Note:
In the button event, i changed DataTable schemaTable = ReadFile(); to DataTable ContentTable = ReadFile();

Karthik_Mahalingam 2-Jan-18 4:47am

try

   string GetConnString(string path)
        {
            string connString = "";
            string extrn = Path.GetExtension(path);
            if (extrn == ".xlsx")
                connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; //for above excel 2007
            else
                connString = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; //for below excel 2007
            return (connString);
        }

        private DataTable ReadFile(string path)
        {
            DataTable dt = new DataTable();
            string conString = GetConnString(path);
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                string query = "Select SIGNAL,ROUTE_BUTTON from [CONTROL_TABLE$]";
                OleDbCommand Oledbcmd = new OleDbCommand(query, con);
                OleDbDataAdapter da = new OleDbDataAdapter(Oledbcmd);
                da.Fill(dt);
            }
            return dt;

        }

        private void t1NextTabRedirectButton_Click(object sender, EventArgs e)
        {
            DataTable schemaTable = ReadFile("C:\\gui_Files\\ICT_LAYOUT_AJJ.xlsx"); 
        }

Arvi.S 2-Jan-18 5:30am

Tried the above code Mr.Karthick, but the same problem im facing.
There is no errors but i didnt get output.
One thing i observed when debugging your code.
Column and row count in da.Fill(dt) is null.

Pradeep Arthanari · Answer 2 · 2018-01-01T22:55:00

Solution 2

You have to add type of your command as text type(below line) in your ReadFile() method:

Oledbcmd.CommandText = "Select SIGNAL,ROUTE_BUTTON from [CONTROL_TABLE$]";
Oledbcmd.CommandType = CommandType.Text;

Posted 1-Jan-18 22:55pm

Pradeep Arthanari

Comments

Arvi.S 2-Jan-18 5:06am

Added, But there is no effect!

Pradeep Arthanari 2-Jan-18 5:41am

You may try this:


        public string connString;

        DataTable schemaTable;

        DataTable ContentTable;

        private void button1_Click(object sender, EventArgs e)
        {
            string filePath = string.Empty;
            string fileExt = string.Empty;

            string fileName = "D:\\00Latitude\\00InfyTFS\\Standard Interface\\ExcelRepository\\GPB 14-4-17.xls";

            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            DataTable schemaTable = ReadFile();
            

            routeNamesListView.Columns.Add("IPCNODEID");
            routeNamesListView.Columns.Add("IPC");
            for (int i = 0; i < schemaTable.Rows.Count; i++)
            {
                DataRow drow = schemaTable.Rows[i];

                // Only row that have not been deleted
                if (drow.RowState != DataRowState.Deleted)
                {
                    // Define the list items
                    ListViewItem lvi = new ListViewItem(drow[0].ToString());
                    for (int j = 1; j < schemaTable.Columns.Count; j++)
                    {
                        lvi.SubItems.Add(drow[j].ToString());
                    }
                    routeNamesListView.Items.Add(lvi);
                }
            }
            routeNamesListView.View = View.Details;
            routeNamesListView.CheckBoxes = true;

        }


        private DataTable ReadFile()
        {
            OleDbConnection conn = new OleDbConnection(connString);

            schemaTable = new DataTable();
            OleDbCommand Oledbcmd = new OleDbCommand();
            Oledbcmd.Connection = conn;
            conn.Open();
            Oledbcmd.CommandText = "Select IPCNODEID,IPC from [daily data$]";

            Oledbcmd.CommandType = CommandType.Text; //--------------

            OleDbDataAdapter da = new OleDbDataAdapter(Oledbcmd);
            ContentTable = new DataTable();
            da.Fill(ContentTable);
            OleDbDataReader dr = Oledbcmd.ExecuteReader();
            dr.Close();
            conn.Close();
            conn.Dispose();

            return ContentTable;

        }

Arvi.S 2-Jan-18 6:15am

Thanks pradeep!! The above code is running well. But out of two columns, only value of first column(SIGNAL) is displayed in listview and the value of next column(ROUTE_BUTTON) is not displayed.

Pradeep Arthanari 2-Jan-18 7:56am

Check if the values of ROUTE_BUTTON is properly populated in schemaTable. If its there in schemaTable it should be displayed in list and also make sure you have the routeNamesListView.View is set to View.Details.

routeNamesListView.View = View.Details;