If I create an Excel 2003 or Excel 2007 file and populate the file without resizing any of the columns in Excel and save it, either as a Excel 2003 or Excel 2007 files. Then open the file with Aspose.Cells and get the column count the column count is 0. Which is incorrect the column count should be the number of columns that contain data in them.
If I then open the file in excel and re-size one of the columns, lets say column 5 of 9, then open the file with Aspose.Cells the column count will be 5.
I have attached an Excel 2007 and Excel 2003 file that will give you a Column count of 0.
The same is true for the Row Count.
Hi,
You are slighly doing wrong, you should use Cells.MaxDataColumn to get the farthest column index which contains data and you should use Cells.MaxRowIndex to get the last row index which contains data in it.
Following is the code with some comments, kindly check it out. If any one wants to determine: last row index which contains data / formattings, last column index which contains data / formattings, countings of cols / rows filled with data / styles other than those rows / cols with standard settings, total no. of cells filled with data/ styles in a worksheet. Kindly check the template file attached first and then utilize/run the code for better understanding:
Workbook wb = new Workbook();
wb.Open("d:\\test\\tstbk1.xls");
Worksheet ws = wb.Worksheets[0];
int colcount = ws.Cells.Columns.Count;
int rowcount = ws.Cells.Rows.Count;
int maxcol = ws.Cells.MaxColumn;
int maxdatacol = ws.Cells.MaxDataColumn;
int maxdatarowincol = ws.Cells.MaxDataRowInColumn(3);
int maxrow = ws.Cells.MaxRow;
int maxdatarow = ws.Cells.MaxDataRow;
int noofcells = ws.Cells.Count;
MessageBox.Show("Cols count: " + colcount.ToString()); // Returns 8 ok... since the columns with standard (default) settings / formatting are not included.
MessageBox.Show("Rows count: " + rowcount.ToString()); // Returns 13 ok... since all the rows with default settings / formattings are not included in the collection.
MessageBox.Show("Max Column index which contains data / style attribute: " + maxcol.ToString()); // Returns 10 ok... since maximum data/styled column is K and its index is 10.
MessageBox.Show("Max Column index which contains data only: " + maxdatacol.ToString()); // // Returns 10 ok... since maximum data column is also K and its index is 10.
MessageBox.Show("Max row index which contains data in the fourth (D) column: " + maxdatarowincol.ToString()); // Returns 10 ok...since in the D column the data is filled up to 11th row and row index is 10.
MessageBox.Show("Max Row index which contains data / style attribute:" + maxrow.ToString());// Returns 23 ok... since the data / style is filled up to the 24th rows only so the last row index is 23.
MessageBox.Show("Max Row index which contains data only" + maxdatarow.ToString()); // Returns 23 ok... since the data is filled up to the 24th rows only so the last row index is 23.
MessageBox.Show("Total no. of cells: " + noofcells.ToString()); // Returns 87, since there are total 87 cells filled with data / styles.
Thank you.
That is fine. I will use the Max Data Column and Row properties. But what was the reason for having the Columns.Count and Rows.Count if they do not return the counts of columns and rows unless some formatting has been applied?
Hi,
Well, Rows.Count and Columns.Count are mainly useful if you want to retrieve the number of rows / columns which are initialized / used in a worksheet and other than the rows/columns with default formatting/settting in a worksheet.
Thanks you.
Hi,
I ahve been using Aspose for about 6 months now....
I saw your post i tried it in my code... I have this file with me... I tired all the properties, viz:
Cells.MaxDataColumn,
Cells.Columns.Count,
but it would not get me the last column.... It always misses out the last column...
I am using
dtTemp = ws.Cells.ExportDataTable(0, 0, ws.Cells.Rows.Count, ws.Cells.Columns.Count);
Can you please look in the matter? I have attached the file....1111.xls
~Mayank
Hi,
Thanks for providing us the template file.
Well, I tested your file using the latest version of Aspose.Cells for .NET v4.7.1 and it works fine. The MaxDataColumn and MaxDataRow attributes of Cells class gives me valid values, i.e.., 10 and 164 for your file. For your info, while using the Cells.ExportDataTable(firstrow, firstcol, numrows, numcols) method, you need to provide valid values for 3rd and fourth parameter. The third parameter (numrows) demand the total number of rows to be exported, so you should give 165 = Cells.MaxDataRow +1. Similarly, you need to provide valid value for the fourth parameter(numcols), it demand total number of columns to be exported, i.e.11 = Cells.MaxDataColumn +1.
So, we recommend you should try to change your code accordingly as described and explained above.
e.g
dtTemp = ws.Cells.ExportDataTable(0, 0, ws.Cells.MaxDataRow +1, ws.Cells.MaxDataColumn +1);
Kindly let us know if you need further clarifications, we will be happy to assist you.
Thank you.