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.
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.
Although there are 32 rows, our Excel engine only creates 28 for the ones that are not empty. This is expected. My code simply demonstrates how to obtain the values in all 32 rows. Let me know if you have any questions regarding this matter.
This is not the case. 28 rows are found but some are empty. Please test with the file supplied. I am using 14.1.
The blank cells in your Worksheet have formatting applied, affecting the overall row count, which makes sense because essentially were "accessed" as our docs mention. I performed a "Clear All" (Home > Editing > Clear) each cell and now you can get an exact row count and use a foreach loop to iterate through your worksheet rows to return all of them if needed. I attached my sample that I've been working on with your included excel file that demonstrates this. The sample also includes code that skips over the empty rows and only returns the values that are found.
Please let me know if you have any questions regarding this matter.
I am glad to hear this. Thank you.
Thank you - it works.