You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@poi.apache.org by Paolo Sacconier <ax...@gmail.com> on 2007/09/12 10:28:21 UTC

Re: Excel 2003 does not update formula

hi joern,
I've got the same issue since I switched from poi 2.5.1 to 3.0.1, did
you find any solution?

Is there a bug open for this? I couldn't find anyone.

thanks
--
Paolo

On 2/7/07, joern@muehlencord.de <jo...@muehlencord.de> wrote:
>
> Hello,
>
> I am using pov 3.0-dev to create an excel file from a template. The existing
> excel file does contain some formulas. I am writing the raw data, which the
> existing formulas evaluate.
>
> Example:
>  - the formula in B1 is "=A1*2"
>  - in the template A1 = 50 is stored (-> B1 = 100)
>  - I update cell A1 to 100 (-> B1 = 200)
>
> When I open the file in excel, B1 shows 100 until I press F2 and then Enter
> (edit) on B2 -> the the formula is updated.
> If I open the file in OpenOffice v2.1 the formula is recalculated.
>
> I have read, that only Excel 97 is supposed to "forget" the recalculation
> and that a possible solution is the SaveRecalcRecord class but I do not know
> how to use it
>
> Has any body the same problem with Excel 2003 (v 11.8117.8170, SP2) and / or
> who can tell me, how to use the SaveRecalcRecord class.
>
> Thanks in adance!
>
> PS: Sorry for the mail to the dev list....
>
> Kind regards
> Joern
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Excel 2003 does not update formula

Posted by Norbert Berens <BS...@procom.de>.


Norbert Berens wrote:
> 
> Paolo wrote:
> 
>>Thanks Antony, following your suggestion I've added this macro to my
workbook
>>
>>Private Sub Workbook_Open()
>>    For Each ws In Worksheets>
>>        ws.UsedRange.Calculate
>>        ws.Calculate
>>    Next ws
>>    Application.Calculation = xlCalculationAutomatic
>>    Application.Calculate
>>    Application.CalculateFull
>>    Application.CalculateFullRebuild
>>End Sub
>>
>>This way when the file is opened after being modified with poi it
>>tries to recalculate every formulas.
> 
> This solution worked fine but when you open the excel file you are asked
> to save the changes, that are actually made by the recalculation.
> We solved this by introducing the following macro:
> 
> Sub workbook_open()
>     If Tabelle3.Cells(1, 1).Value = 1 Then
>         ' Application.CalculateFullRebuild is definitely enough
>         Application.CalculateFullRebuild 
>         Tabelle3.Cells(1, 1).Value = 0
>         Application.DisplayAlerts = False
>         ActiveWorkbook.Save
>         Application.DisplayAlerts = True
>     End If
> End Sub
> 
> From the POI side, we write a value of 1 into a well defined cell in a
> well defined sheet (here Tabelle3(1,1)), so recalculation is only
> performed the first time you open the workbook and is automatically saved
> without user interaction.
> 

By accident I found a solution without an excel macro for the update
problem. I do not quite understand why it works but it works for me: You
only have to read the formula off the cell and write it back again. You do
not even have to evaluate the formula. If you then open Excel, the formulas
are calculated. I did it with the following methods:

        public void updateFormulas(HSSFWorkbook wb) { 
		int numSheets = wb.getNumberOfSheets();
		for( int i = 0; i < numSheets; i++) {
			HSSFSheet sheet = wb.getSheetAt(i);
			updateFormulas(sheet, wb);
		}
	}
	
	public void updateFormulas(HSSFSheet sheet, HSSFWorkbook wb) {

		HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);

		int firstrow = sheet.getFirstRowNum();

		int lastrow = sheet.getLastRowNum();

		for (int i = firstrow; i <= lastrow; i++) {

			HSSFRow row = sheet.getRow(i);
			if( row == null ) {
				continue;
			}
			
			short firstcell = row.getFirstCellNum();
			short lastcell = row.getLastCellNum();

			for(int j = firstcell; j <= lastcell; j++) {
				HSSFCell cell = row.getCell((short) j);
	
				if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
	
					String formel = cell.getCellFormula();
	
					
					/*
                                        evaluator.setCurrentRow(row);
					
					try {
						evaluator.evaluateInCell(cell);
					} catch (Exception ex) {
                                                // This always throwed a
ClassCastException !!
						
					}*/

					cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
	
					cell.setCellFormula(formel);
	
				}
			}

		}

	}



This seems to be a work around for the problem within poi-3.0.1. Any
explanations are welcome.

Regards, Norbert
-- 
View this message in context: http://www.nabble.com/Excel-2003-does-not-update-formula-tf3188604.html#a13098001
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: Trouble writing a large file

Posted by Lance Woodson <La...@FranklinCovey.com>.

Thanks for the advice.

-Lance

-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Friday, September 14, 2007 9:47 AM
To: POI Users List
Subject: Re: Trouble writing a large file

On Fri, 14 Sep 2007, Lance Woodson wrote:
> Does anyone have any suggestions as to how I might go about debugging
> this issue?  POI doesn't give any errors, and there is nothing in excel
> that I have found that is more helpful than the message above.

Your best bet is to get two files, one that works, and one that's only had
a single row/cell change and doesn't

Then, use the tools in org.apache.poi.hssf.dev
<http://poi.apache.org/apidocs/org/apache/poi/hssf/dev/package-summary.html>
especially BiffViewer to spot what has changed between the two

You might find the poifs.dev tools useful too, which are a lower level,
but might be easier to spot the bytes that've changed
<http://poi.apache.org/apidocs/org/apache/poi/poifs/dev/package-summary.html>

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Trouble writing a large file

Posted by Nick Burch <ni...@torchbox.com>.

On Fri, 14 Sep 2007, Lance Woodson wrote:
> Does anyone have any suggestions as to how I might go about debugging 
> this issue?  POI doesn't give any errors, and there is nothing in excel 
> that I have found that is more helpful than the message above.

Your best bet is to get two files, one that works, and one that's only had 
a single row/cell change and doesn't

Then, use the tools in org.apache.poi.hssf.dev
<http://poi.apache.org/apidocs/org/apache/poi/hssf/dev/package-summary.html>
especially BiffViewer to spot what has changed between the two

You might find the poifs.dev tools useful too, which are a lower level, 
but might be easier to spot the bytes that've changed
<http://poi.apache.org/apidocs/org/apache/poi/poifs/dev/package-summary.html>

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Trouble writing a large file

Posted by Lance Woodson <La...@FranklinCovey.com>.

Hello, all. I'm using POI to develop a service for our ecommerce website that writes lists of persistent objects to excel spreadsheets and redigests the spreadsheet, updating the persistent objects with any changes made in the spreadsheet.

One issue that has come up is that when writing a large list of a particular kind of persistent object to a file, I get errors in the created xls file that result in the following message being displayed when opening in excel: "Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted."

The data corruption consistently occurs at 793 records/rows always for this particular type of persistent object, and never for other types of persistent objects with many more rows. Data in the 792nd row seems no different than the data in previous rows, so my most likely guess is that the data within this particular object not being kosher with some sort of cell constraint. Also, I can download this file, remove the garbled rows, add rows for new items from 793 down, and the file is digested fine when uploading it. So everything seems to be constrained to the write operation.

Does anyone have any suggestions as to how I might go about debugging this issue? POI doesn't give any errors, and there is nothing in excel that I have found that is more helpful than the message above.

Thanks for any help anyone can provide.

-Lance Woodson

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org