Row Count returning 0 if I don't call save


#1

Here's my issue,

I create an instance of Excel and write an entry to the default worksheet.

I place a title in the first entry.

If I get a count to write the next entry under the first Rows.Count returns 0.

If I save the workbook the Rows.Count property returns 1.

I'd prefer to finish write everything out Row by Row and Column by Column before having to call save. I don't want to call save after every entry I make.

I'm most likely doing something wrong...

Here's my sample code.

Excel workbook = new Excel();

workbook.Worksheets[0].Name = "Report";

workbook.Worksheets["Report"].Cells[0, 0].PutValue("Some value goes here...");

MessageBox.Show(workbook.Worksheets["Report"].Cells.Rows.Count.ToString());

Thanks in advance.

(FYI, we're also evaluating Aspose.Cells for another project involving importing and exporting DataTables, but I decided to kill 2 birds with one stone and see if I can use Aspose.Cells to replace an in house reporting tool as well. Big Smile [:D])


#2

Rows collection contains style settings for rows, not data in rows.

You can try to use:

MessageBox.Show(workbook.Worksheets["Report"].Cells.MaxRow.ToString());

or

MessageBox.Show(workbook.Worksheets["Report"].Cells.MaxDataRow.ToString());

MaxRow property returns the max row index which contains data or style settings.

MaxDataRow property return the max row index which contains data.

In your case, they will also return 0 but they mean that the max row index which contains data is 0 (zero based index).

They will return 2 if you change your code to:

Excel workbook = new Excel();

workbook.Worksheets[0].Name = "Report";

workbook.Worksheets["Report"].Cells[2, 0].PutValue("Some value goes here...");


#3

I have 2 more issue left to resolve: When I copy a sheet from another instance of Excel into the current and I remove formatting it also removes some of the data (on some workbooks not all). The other issue is: if I rename the default sheet name then call save it saves but the instance of Excel seems to reset - is that by design?

I'll post copies of the workbooks in question as well as sample code (for the 1st issue) when I return to the office.

Thanks again Laurence - I must say, I'm impressed with Aspose's responsiveness to issues posted it's forum. Cheers! Yes [Y] Beer [B]


#4

You are very welcome.

For losing data issue, please give me a sample so I can check if it's a bug more quickly. Thank you.

After calling save method, data in Excel instance is reset. It's by design.


#5

In retrospect, I probably should have started a new thread but since I already commented about the problem in this thread I'll continue on this thread. Here's the sample code and a copy of one of workbooks experiencing the problem.

Excel target = new Excel();

Excel Source = new Excel();

Source.Open(@"C:\\Parent Corporation_2003_Q1_Entity_BalanceSheet-Quarter_Noncurrent Liability_Q1_1.xls");

target.Worksheets[0].Copy(Source.Worksheets[0]);

Cells targetCells = target.Worksheets[0].Cells;

targetCells.ClearFormats(0, 0, targetCells.End.Row, targetCells.End.Column);

target.Save(@"c:\asposeTest.xls");

Thanks.


#6
JeronimoColon wrote:

In retrospect, I probably should have started a new thread but since I already commented about the problem in this thread I'll continue on this thread. Here's the sample code and a copy of one of workbooks experiencing the problem.

Excel target = new Excel();

Excel Source = new Excel();

Source.Open(@"C:\\Parent Corporation_2003_Q1_Entity_BalanceSheet-Quarter_Noncurrent Liability_Q1_1.xls");

target.Worksheets[0].Copy(Source.Worksheets[0]);

Cells targetCells = target.Worksheets[0].Cells;

targetCells.ClearFormats(0, 0, targetCells.End.Row, targetCells.End.Column);

target.Save(@"c:\asposeTest.xls");

Thanks.

Any word on this?

EDIT: I don't expect an instant fix - but it would be nice to know that it's being looked at and possibly a status update...

Thanks.


#7
JeronimoColon wrote:
JeronimoColon wrote:

In retrospect, I probably should have started a new thread but since I already commented about the problem in this thread I'll continue on this thread. Here's the sample code and a copy of one of workbooks experiencing the problem.

Excel target = new Excel();

Excel Source = new Excel();

Source.Open(@"C:\\Parent Corporation_2003_Q1_Entity_BalanceSheet-Quarter_Noncurrent Liability_Q1_1.xls");

target.Worksheets[0].Copy(Source.Worksheets[0]);

Cells targetCells = target.Worksheets[0].Cells;

targetCells.ClearFormats(0, 0, targetCells.End.Row, targetCells.End.Column);

target.Save(@"c:\asposeTest.xls");

Thanks.

Any word on this?

EDIT: I don't expect an instant fix - but it would be nice to know that it's being looked at and possibly a status update...

Thanks.

What's going on? The moment I paid I stopped getting responses?

Good thing there's a money back guarantee... I guess I'll be exercising that right then...


#8

Sorry I missed this post. I will check and fix this issue ASAP.


#9

I checked your file. I found it’s not created by MS Excel or Aspose.Cells. It’s created by other 3rd party product, right? It mixed records with BIFF7 and BIFF8. Though MS Excel can read it, it’s really not a valid MS Excel file. I used MS Excel to open and save this file and all works fine.Please check the attached file.


#10

And please try this latest fix.


#11

Okay. Very interesting. This is actually a client file. We have almost 1200 of them. I will contact the client and see if I can get more information from them about these files.

Thanks.

jc3


#12

Thank you.

Actually I found many BIFF7 records in your Excel file(Excel95 format). It will consume more file size and memory size with BIFF7 record.

Aspose.Cells doesn't support Excel95. It supports Excel file format since Excel97.


#13

Firstly, Laurence, thanks again for you careful attention.

I did test the change you posted earlier and it did not work, but with that said, I wouldn't want Aspose to compromise their product to accommodate an incorrectly created/hybrid Excel file and as per your last post you only support Excel97 or greater. This is understandable.

So, I have spoken with the client and they told us that their files are exported using a component from 3rd party vendor and a tool they wrote in-house to generate the workbooks they provided us. We have asked them to provide us with Excel files formatted in Excel 97 or later. They told us that they need more specific details of the problem so they could get the issue resolved and provide us with more compliant files. We are dealing with almost 1200 files so any help would be greatly appreciated - though I realize that this request is probably above and beyond any reasonable level of support. If you can not comply we can consider the matter closed. Either way we are happy with your product and will probably be upgrading our license to an OEM version.

Thanks you.


#14

I check the 3rd party file and found this problem:

In BIFF8, all strings are saved in a pool and if a cell contains a string, an index to that string are saved in the file. While in BIFF7, if a cell contains a string, this string will be saved with this cell. So multi-copy strings will be saved in the file. So BIFF7 file will be much larger than BIFF8 file.

For example: if Cell A1 and A2 all contains a string "Hello, world", in BIFF7, this string will be saved twice in Cell A1 and A2. While in BIFF8, this string will be saved once in a pool and Cell A1 and A2 will contains an index to this string.

Please check http://sc.openoffice.org/excelfileformat.pdf for more detail of Excel file format.

And some records with same ID in BIFF7 and BIFF8 but may contains different size and contents. So this caused this problem.

Aspose.Cells promises to read Excel files created with MS Excel or Aspose without any problem. However, for 3rd party products we cannot assure this because they are too ad hoc.

A feasible solutions to convert those 3rd party files, you can try to use MS Excel PIA to convert them. Though it's slower, less stable and harder to deploy, it can be an offline pre-process approach.