In Aspose cell 5.0 version column data count for counting nulls getting error in excel export

Aspose.Cells Product Family
1

Hi,

In Export Excel the text Column data count is difference compare when filter with blanks.

for example:

total column1 with data(have no nulls) count:116352

total column2 with data(include nulls) count:114398

ie the null data in column2 is (the diffrence from column1-column2)=1954

but when column2 filter with blank count is :71245

what's the problem?

when the export is done lower version of aspose then it is getting fine,but only nealy 60,000 records are set in one sheet and we have to make loop to create new sheet,so we have used aspose 5.0 version.

Thanks,

Chandini.N

2

Hi,


Since you are using some older version of the product i.e. v5.0 for which we are not sure if this was an issue with it or not, moreover, we cannot fix any issue or include any enhancements in older versions. We can only do enhancements or fix issues based on the latest version of the product.

Please download and try the latest version: Aspose.Cells for .NET v7.2.1.3 and see if it resolves your issue.

If you still find the issue, kindly do create a sample console application, zip it and post it here to show the issue. Also attach the sample Excel files (input + output(if any)). We will check your issue soon.

Thank you.

3

please find the attachewd file .

In that projectName column data count is 115226

another column keypersonName column data count is 115226(this column consists null also,why it is showing same count,when filter with blank it shows the count 71400 nearly)

actually we have to show count as diff of total -nulls.

Thanks

Chandini.N

4

I have implimented Aspose.Cells for .NET v7.2.1.3 version only.

5

Hi,


Thanks for the template file.

Well, if you need to exclude the Null/blanks in a column to get count of data values in the cells, you have to use your own code, it is not very difficult to do it. Please see the following sample code for your reference, I have used an approach to do it, there are some other approaches to do the task as well.

Note: Please see the comments with the lines of code below to get to know about what the properties, e.g Columns.Count, Rows.Count (Properties) and GetLastDataRow() (method) actually do.

Sample code (I used your template file with my sample code):

Workbook wb = new Workbook(“e:\test2\Key+Contacts+Export_25_05_2012.xlsx”);
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.GetLastDataRow(8);
int noofcells = ws.Cells.Count;

MessageBox.Show("Cols count: " + colcount.ToString()); // It will give you column’s count, a column with atleast one non-null value or one initialized cell will be included to the list.
MessageBox.Show("Rows count: " + rowcount.ToString()); // It will give you row’s count, a row has atleast one non-null value / one initialized cell will be included to the list.
MessageBox.Show("Max row index which contains data in the I column: " + maxdatarowincol.ToString()); // It will give you the farthest row index that contains data in the column.
//For excluding null/blank values, you may fill into a datatable and then use Select query to know about the Nulls.
DataTable dt = ws.Cells.ExportDataTable(7, 8, maxdatarowincol - 7, 1);
DataRow[] results = dt.Select(“Column1 IS NULL”);

MessageBox.Show(results.Length.ToString()); //Total nulls in the column

int totalNulls = results.Length;
int dataValues = maxdatarowincol - 7 + totalNulls; //Total number of Data cells/records


Hopefully it will help you to understand it and you may write your own code to do what you want.

Thank you.