How do I determine the end-of-file in a spreadsheet/excel application
-
Hi, I developed an application that opens an excel doc, and reads/displays its contents to a datagrid. How do I determine its start and end rows? I have tried some code but it does not give me the result I need. e.g. I tried: //get start and end rows Object[] startRow = {worksheet.get_Range("A4", "O4")}; Object[] endRow = {worksheet.Rows.get_End(XlDirection.xlDown)}; which did not work for me. See below: // loop through all rows of the spreadsheet and place each row in the datagrid. for (int i = 4; i <= 50; i++) { Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "Z" + i.ToString()); System.Array myvalues = (System.Array)range.Cells.Value2; string[] strArray = ConvertToStringArray(myvalues); DataGridViewRow row = new DataGridViewRow(); Object[] vals = strArray; row.CreateCells(dgOutput, vals); dgOutput.Rows.Add(row); } This displays the 1st 50 rows. I do not want to display 50 rows, I want to display all the rows in the spreadsheet. I need a variable that will replace the 50 (in the for loop) and represent the end-of-file. Please help. :doh:
Mvelo Walaza Developer Telkom SA
-
Hi, I developed an application that opens an excel doc, and reads/displays its contents to a datagrid. How do I determine its start and end rows? I have tried some code but it does not give me the result I need. e.g. I tried: //get start and end rows Object[] startRow = {worksheet.get_Range("A4", "O4")}; Object[] endRow = {worksheet.Rows.get_End(XlDirection.xlDown)}; which did not work for me. See below: // loop through all rows of the spreadsheet and place each row in the datagrid. for (int i = 4; i <= 50; i++) { Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A" + i.ToString(), "Z" + i.ToString()); System.Array myvalues = (System.Array)range.Cells.Value2; string[] strArray = ConvertToStringArray(myvalues); DataGridViewRow row = new DataGridViewRow(); Object[] vals = strArray; row.CreateCells(dgOutput, vals); dgOutput.Rows.Add(row); } This displays the 1st 50 rows. I do not want to display 50 rows, I want to display all the rows in the spreadsheet. I need a variable that will replace the 50 (in the for loop) and represent the end-of-file. Please help. :doh:
Mvelo Walaza Developer Telkom SA
-
int lastUsedRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row;
Thanks man, it worked!! Can you please recommend any sites I can get more info/code about Excel applications. I still have lots to do in this application like manipulating the data in the cells and inserting the data from the spreadsheet to an Oracle database table. Thanks again!
Mvelo Walaza Developer Telkom SA
-
Thanks man, it worked!! Can you please recommend any sites I can get more info/code about Excel applications. I still have lots to do in this application like manipulating the data in the cells and inserting the data from the spreadsheet to an Oracle database table. Thanks again!
Mvelo Walaza Developer Telkom SA
Glad to help. The best I can do is give you some MSDN links, other than that good documentation isn't easy to find for the Excel stuff. After MSDN and Google I'd recommend using Intellisense to explore the Excel namespace and just try anything that looks useful (and of course posting here)! http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel(VS.80).aspx[^] http://support.microsoft.com/kb/302084[^]