System.Data.OleDb & Microsoft Excel [modified]
-
Hello Code Project Team. Someone know how get the row index from excel spreadsheet? Example: [Profile.xls - spreadsheet1$]
| FirstName | LastName | Age | ---|-----------|-------------|-----| 1 | Anthony | Acuña | 22 | 2 | Michael | Danagan | 24 | 3 | Benjamin | Beat | 24 | 4 | Monica | Danagan | 23 |C# CodeOleDbConnection conn = new OleDbConnection(); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Profile.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MaxScanRows=0;\""; conn.Open(); DataTable dt = new DataTable(); string SQL = "SELECT * FROM [spreadsheet1$] WHERE FirstName LIKE 'M%'"; OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn); da.Fill(dt); this.dataGridView1.SuspendLayout(); this.dataGridView1.DataSource = dt; this.dataGridView1.ResumeLayout();This work correctly and only show:| FirstName | LastName | Age | |-----------|-------------|-----| | Michael | Danagan | 24 | | Monica | Danagan | 23 |But I need retrieve what is the row index on excel file for every row. I need show the info in DataGridView in the follow format:| ExcelIndex | FirstName | LastName | Age | |------------|-----------|-------------|-----| | 2 | Michael | Danagan | 24 | | 4 | Monica | Danagan | 23 |I hope that you can help me. Thanks in advanced. -- modified at 22:55 Sunday 12th November, 2006SINCERELY. ANTHONY ACUÑA PREFERED PHRASE: SOMEBODY TELL ME WHY IS MORE REAL WHEN I DREAM THAT I AM WAKE?
-
Hello Code Project Team. Someone know how get the row index from excel spreadsheet? Example: [Profile.xls - spreadsheet1$]
| FirstName | LastName | Age | ---|-----------|-------------|-----| 1 | Anthony | Acuña | 22 | 2 | Michael | Danagan | 24 | 3 | Benjamin | Beat | 24 | 4 | Monica | Danagan | 23 |C# CodeOleDbConnection conn = new OleDbConnection(); conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Profile.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;MaxScanRows=0;\""; conn.Open(); DataTable dt = new DataTable(); string SQL = "SELECT * FROM [spreadsheet1$] WHERE FirstName LIKE 'M%'"; OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn); da.Fill(dt); this.dataGridView1.SuspendLayout(); this.dataGridView1.DataSource = dt; this.dataGridView1.ResumeLayout();This work correctly and only show:| FirstName | LastName | Age | |-----------|-------------|-----| | Michael | Danagan | 24 | | Monica | Danagan | 23 |But I need retrieve what is the row index on excel file for every row. I need show the info in DataGridView in the follow format:| ExcelIndex | FirstName | LastName | Age | |------------|-----------|-------------|-----| | 2 | Michael | Danagan | 24 | | 4 | Monica | Danagan | 23 |I hope that you can help me. Thanks in advanced. -- modified at 22:55 Sunday 12th November, 2006SINCERELY. ANTHONY ACUÑA PREFERED PHRASE: SOMEBODY TELL ME WHY IS MORE REAL WHEN I DREAM THAT I AM WAKE?
Im gonna guess that you cant just add a row to the excel sheet, and write the numbers in yourself? Hmm, perhaps, creating a temporary array to store all the data from the excel sheet, then you can use the index from that, but of course, if its a big document it would consume alot of memory.