Worksheet.Cells.Columns.Count - returns 0 when columns are present

Aspose.Cells Product Family
1

Hello,

We have purchased Aspose.Cells site license and using it successfully. Suddenly we found an issue with some excel files where column count is returned as 0 when there are columns.

Code:

string fileName = args[0];

Workbook test = new Workbook();

test.Open(fileName, FileFormatType.Excel2003);

Worksheet worksheet = test.Worksheets[0];

Console.WriteLine("Columns :" + worksheet.Cells.Columns.Count); // Returned as 0 - SHould BE 2

Console.WriteLine("Rows :" + worksheet.Cells.Rows.Count);

Console.WriteLine("Max Columns :" + worksheet.Cells.MaxColumn); // This returns 1

Console.WriteLine("Max Rows :" + worksheet.Cells.MaxRow); // This returns 3

Please help us fix this soon.

Thanks,

Gopal.

2

Hi Gobal,

Well, you slightly misunderstood the Cells.Columns.Count attribute. The property would give you columns count but, the columns with standard (default) settings / formatting are not included to be counted. Since all the columns in the sheet are with defaults formatting, so it will give you 0. For your information, the 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 also those rows / columns other than with default formatting/setting in a worksheet.

Please always use Cells.MaxDataColumn and Cells.MaxDataRow attributes to get the farthest column and row indexes.

For complete reference for using these attributes and others in that series, please check the thread: http://www.aspose.com/community/forums/178764/column-and-row-count-invalid/showthread.aspx#178764

Kindly let us know if you still need some clarifications, we would be happy to help you.


Thank you.

3

Hello,

I tried using MaxDataRow and MaxDataColumn, but does not return me the correct count yet.

string fileName = args[0];

Workbook test = new Workbook();

test.Open(fileName, FileFormatType.Excel2003);

Worksheet worksheet = test.Worksheets[0];

Console.WriteLine("Columns :" + worksheet.Cells.Columns.Count); // This is 0

Console.WriteLine("Rows :" + worksheet.Cells.Rows.Count); // This is 4

Console.WriteLine("Max Data Column :" + worksheet.Cells.MaxDataColumn); // Return 1 - Should be 2

Console.WriteLine("Max Data Row :" + worksheet.Cells.MaxDataRow); // Returns 3 - Should be 4

For the same test file, i should get 2 columns and 4 rows. But i get 1,3.

Please suggest if i am doing something wrong.

Thanks,

Gopal.

4

Hi Gopal,

Well, MaxDataRow and MaxDataColumn work fine and give correct values. It is to be noted here, both attributes return Zero-based indexed values for rows and columns.

So, if the MaxDataColumn returns "1", it means that there are two columns in count in the worksheet and the farthest column would be B (i.e.., 0-A, 1-B)................which is true.

If the MaxDataRow returns "3", it means that there are 4 rows filled with data and the farther row is 4th row.........................which is true again.

Thanks for your understanding.

5

I've been trying to find out how to get the number of cells containing data and for each property there seems to be a catch. For example, in this case, MaxDataRows and MaxDataColumns give the same result if you have a worksheet with one cell containing data, and a worksheet that is totally empty. They both give counts of zero.

Is there a way to distinguish between empty sheets and sheets with only one cell?

Thanks,

Jeff

6

Dear Jeff,

I think, this problem of distinguishing empty sheet or sheet with one cell can be solved, but first I need to test it myself. I will let you know as soon as possible.

7

Dear Jeff,

You can use this code to distinguish empty sheet or sheet with one cell.


if (wb.Worksheets[0].Cells.Count == 0)
{
//this sheet is totally empty
}

if (wb.Worksheets[0].Cells.Count == 1)
{
//this sheet has just one cell
}
8

I have create a bug CELLSNET-23012. Cells.MaxRow and Cells.MaxColumn now should return -1 when worksheet is totally empty.

9

Hi,

We have fixed this issue. Please download the Aspose.Cells for .NET 5.2.2.1

10

The issues you have found earlier (filed as 23012) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
11

Hi, I am trying to get the colum count using the attached file. But i am getting 0 columns. If i open this file and click save as then i am getting the attached popup i.e. this file may contains format which may not compatable with CSV. If i select No option in the popup and save the file again as CSV and try to get the column count with the saved file i am able to get the column count. Can you help me to get the coulumn count with the formatted an unformatted files by using the same property.


Can you please help me ASAP. This is very importent work i need to do.

Thanks in advance
Jithendra
12

Hi,


Thanks for sharing your template file.

Please use Cells.MaxColumn to get the last column’s index which contains data or style.
Please use Cells.MaxDataColumn to get the last column’s index which contains data only.

See the sample code that works fine with your attached file.

Sample code:


TxtLoadOptions txtLoadOptions = new TxtLoadOptions(LoadFormat.CSV);


Workbook workbook = new Workbook(“e:\test2\CSV Sample File.csv”,txtLoadOptions);

Worksheet sheet = workbook.Worksheets[0];


Debug.WriteLine(sheet.Cells.MaxDataColumn); //Get the farthest column index which contains data = 71 - OK (since columns are zero indexed based, so total column = 72)

Debug.WriteLine(sheet.Cells.MaxColumn); //Get the farthest column index which contains data or style/formattings = 71 - OK (since columns are zero indexed based and no style/formatting is applied, so total column = 72)




Thank you.


13

Hi Thank you so much for your quick response. I am also using the Cells.MaxDataColumn. But still i am getting the column count as 0. I am using the below code.

Workbook workbook = new Workbook();

workbook.Open(@"C:\Test\Sample CSV.csv", FileFormatType.CSV);

Worksheet ws = workbook.Worksheets[0];

List<List<string>> cells = new List<List<string>>();

for (int i = 0; i < ws.Cells.MaxDataRow + 1; i++)

{

List<string> row = new List<string>();

for (int j = 0; j < ws.Cells.MaxDataColumn + 1; j++)

{

row.Add(ws.Cells[i, j].StringValue);

}

cells.Add(row);

}

14

Hi, I am using the ‘4.7.1.0’ version dll to run the above posted code.

15

Hi I am attaching the sample file again for your reference.

Thanks

Jithendra

16

Hi,


Thanks for the attaching the newer file here.

Well, I have checked your issue and found your so called CSV file “Sample+CSV” is actually a tab delimited Unicode text file, so it is not a conventional CSV file. For confirmation, you may open the file into notepad or open the file into MS Excel and try to save it as (do not do this though), you will see “Unicode text” as save as type in the Save dialog box in MS Excel. I used the following code which works absolutely fine with our latest version v7.4.2.x(please try it downloading from Aspose site (Downloads module/section)):

Sample code:

TxtLoadOptions txtLoadOptions = new TxtLoadOptions(LoadFormat.TabDelimited);
txtLoadOptions.Encoding = Encoding.Unicode;
Workbook workbook = new Workbook(“e:\test2\Sample+CSV.csv”, txtLoadOptions);
Worksheet sheet = workbook.Worksheets[0];

Debug.WriteLine(sheet.Cells.MaxDataColumn); //Get the farthest column index which contains data = 71 - OK (since columns are zero indexed based, so total column = 72)
Debug.WriteLine(sheet.Cells.MaxColumn); //Get the farthest column index which contains data or style/formattings = 71 - OK (since columns are zero indexed based and no style/formatting is applied, so total column = 72)

As you are using some older version i.e. v4.7.x of the product for which I am not sure if it has these new attributes or options, but you may try to specify Tab Delimited as FileFormatType while loading the file if it works. If it does not work as expected, I am afraid, you have to upgrade to and use the latest versions of the product as there is no other alternative to sort it out.

For your information, over the years we have made tremendous enhancements and included many new features and functionality, the product has become more robust and feature rich component now.

Thanks for your understanding!