I have attached a very simple excel file. When I read it with WB = Workbook.Load(io) the wrong number of rows are returned. This document contains 32 rows but your component, on the Rows property, returns 28. Now, there are 5 blank rows in the document which is probably the cause for this and you could think that these blank rows have been removed in Rows but there are blank rows in it so there is no way for me to read the last 4 rows.
Hello Henrik,
Thank you for contacting Infragistics. As our documentation states, the rows in the worksheet.Rows collection are lazily created (they are only created and added to the collection when they are accessed). If this collection is enumerated, it only enumerates the rows which were already accessed.
With that said you can still iterate through the number of rows based on the worksheet.Rows.Count and obtain all the cell values.
string _settings = Application.StartupPath + @"\test.xls";Workbook wrkbook = Workbook.Load(_settings);Infragistics.Documents.Excel.Worksheet worksheet = wrkbook.Worksheets["serialsh03_9"]; StringBuilder sb = new StringBuilder();int numRows = worksheet.Rows[((ICollection<WorksheetRow>)worksheet.Rows).Count - 1].Index + 1;MessageBox.Show("Rows: " + numRows.ToString()); for (int i = 0; i < numRows + 1; i++) {sb.AppendLine(String.Format("{0} \t\t {1}",worksheet.Rows[i].Cells[0].GetText(),worksheet.Rows[i].Cells[0].GetText()));MessageBox.Show(sb.ToString()); }
string _settings = Application.StartupPath + @"\test.xls";Workbook wrkbook = Workbook.Load(_settings);Infragistics.Documents.Excel.Worksheet worksheet = wrkbook.Worksheets["serialsh03_9"];
StringBuilder sb = new StringBuilder();int numRows = worksheet.Rows[((ICollection<WorksheetRow>)worksheet.Rows).Count - 1].Index + 1;MessageBox.Show("Rows: " + numRows.ToString());
for (int i = 0; i < numRows + 1; i++)
{sb.AppendLine(String.Format("{0} \t\t {1}",worksheet.Rows[i].Cells[0].GetText(),worksheet.Rows[i].Cells[0].GetText()));MessageBox.Show(sb.ToString());
}
Let me know if you have any questions regarding this matter.
I have 6 rows with records in the spreadsheet, however this syntax :
int numRows = worksheet.Rows[((ICollection<WorksheetRow>)worksheet.Rows).Count - 1].Index + 1;
is returning the record count as 5. What could be the possible reason?
Sorry, but I do not understand. Your code produces the same result: 28 rows. But in fact, there are 32 rows. Only that some rows contains blank spaces.