You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by falsedmitri <jd...@gmail.com> on 2011/11/06 00:49:51 UTC

Where is POI formula read for HSSF?

I need to find the point in the POI code where a formula is read in from an
excel cell. Can someone point me to this?

I'm having a problem with POI with the a formula value not being calculated
(always getting 0 in FormulaRecord.getValue()). So presumably it first has
to read the formula before it can calculate it, but it is difficult to find
this without knowing the internal structure of excel files.

The class extends HSSFListener and so has:
      public void processRecord(Record record) ...

and the record at that point has 0 when it should have a calculated number.

Looking in RecordFactoryInputStream.nextRecord() and adding a debug
statement, it appears that the number is already calculated:

*record = readNextRecord();
if (record instanceof FormulaRecord) {
	System.out.println ("RecordFactoryInputStream.nextRecord(); " +
record.toString());
}*

Toggle HighlightingOpen in New WindowSelect All

RecordFactoryInputStream.nextRecord(); [FORMULA]
    .row    = 0x0003
    .col    = 0x0002
    .xfindex= 0x0040
  .value       = -0.1609487366724528
  .options   = 0x0008
    .alwaysCalc= false
    .calcOnLoad= false
    .shared    = true
  .zero      = 0xFE090003
    Ptg[0]=[Array Formula or Shared Formula]
row = 3
col = 2
.
[/FORMULA]

The output data above is a case where the formula is calculated correctly.

Using 3.8 beta3. 

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4967675.html
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: Where is POI formula read for HSSF?

Posted by falsedmitri <jd...@gmail.com>.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * TestEventBasedExcelExtractor
 */
public final class TestEventBasedExcelExtractor {

	/**
	 * constructor
	 */
	public TestEventBasedExcelExtractor(String filename) {

		File file = new File(filename);
		
		FileInputStream fileInputStream = null;
		POIFSFileSystem poiFSFileSystem = null;

		try {
			fileInputStream = new FileInputStream(file);
			poiFSFileSystem = new POIFSFileSystem(fileInputStream);
		} 
		catch (FileNotFoundException e) {
			System.out.println ("TestEventBasedExcelExtractor: file: " + 
file.getAbsoluteFile());
			e.printStackTrace();
		} 
		catch (IOException e) {
			e.printStackTrace();
		}

		EventBasedExcelExtractor eventBasedExcelExtractor = new
EventBasedExcelExtractor(poiFSFileSystem);
		eventBasedExcelExtractor.setFormulasNotResults(true); // set to true to
see formula in cell
		String string = eventBasedExcelExtractor.getText();

		System.out.println ("TestEventBasedExcelExtractor: string:\n" +  string);

        // Output string to file
		// Use Apache Commons IO 
//		String filenameNoExtension =  FilenameUtils.getFullPath(filename) +
FilenameUtils.getBaseName(filename);
//
//		BufferedWriter bufferedWriter = null;
//		try {
//			bufferedWriter = new BufferedWriter(new FileWriter(filenameNoExtension
+ ".txt"));
//			bufferedWriter.write(string);
//			bufferedWriter.close();		
//		} 
//		catch (IOException e) {
//			e.printStackTrace();
//		}

	}

	/**
	 *
	 * @param args Expect one argument that is the file to read.
	 */
	public static void main(String[] args) throws IOException {

		String filename = null;

		if (args.length == 0) {
			System.out.println ("Please supply filename on command line");
		}
		else {
			filename = args[0];
		}

		TestEventBasedExcelExtractor testEventBasedExcelExtractor = new
TestEventBasedExcelExtractor(filename);

	}
}


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4974849.html
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: Where is POI formula read for HSSF?

Posted by falsedmitri <jd...@gmail.com>.
Thanks, I will look at this.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4972508.html
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: Where is POI formula read for HSSF?

Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 7 Nov 2011, falsedmitri wrote:
> How do I get a String representing the formula in a cell using 
> HSSFListener?

We have one excellent example of this sort of thing, in the form of 
EventBasedExcelExtractor, I'd suggest you take a look at that:
http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java

Nick

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


Re: Where is POI formula read for HSSF?

Posted by falsedmitri <jd...@gmail.com>.
So all this boils down to this:

How do I get a String representing the formula in a cell using HSSFListener?


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4972423.html
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: Where is POI formula read for HSSF?

Posted by falsedmitri <jd...@gmail.com>.
Thanks for your response.

This is a difficult problem to reproduce. The excel file is originated
elsewhere in our application by writing out using POI. When it is read in
this state, the error occurs. If you open the file with Excel, then save it,
then access it in the application, the error does not occur. As such, I
cannot provide an example excel file because by downloading it and saving
it, the error is removed. Something in the save in Excel is cleaning up the
problem.

The formula string is something like: =f4+g4+h4+i4, where the f,g,h, and i
stay the same, but the 4 changes with each row.

The code that writes the formula cell looks like this:

	final StringBuffer excelFormula = new StringBuffer(<some string>);
	excelSheet.setUserValue(dataRow, currentColumn, excelFormula.toString());
			
The cell type is not set. How would I set the cell type to
HSSFCell.CELL_TYPE_FORMULA here?


--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4971408.html
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: Where is POI formula read for HSSF?

Posted by Yegor Kozlov <ye...@dinom.ru>.
A zero returned by FormulaRecord#getValue() means that the formula
wasn't calculated before. Did you try to evaluate the cell and ask the
value after that?
Some help on formula evaluation can be found here:
http://poi.apache.org/spreadsheet/eval.html

The text dump shows and the formula is either shared or array:

>    Ptg[0]=[Array Formula or Shared Formula]

It might be that POI doen't properly handle these type, but I can't
tell for sure without a sample file.

What is the formula string you see in Excel?

If you think it is a bug, then please create a new issue and Bugzilla
and upload the problematic file and a Java code (ideally a failing
junit test case) that demonstrates the problem.

Yegor
On Sun, Nov 6, 2011 at 2:49 AM, falsedmitri <jd...@gmail.com> wrote:
> I need to find the point in the POI code where a formula is read in from an
> excel cell. Can someone point me to this?
>
> I'm having a problem with POI with the a formula value not being calculated
> (always getting 0 in FormulaRecord.getValue()). So presumably it first has
> to read the formula before it can calculate it, but it is difficult to find
> this without knowing the internal structure of excel files.
>
> The class extends HSSFListener and so has:
>      public void processRecord(Record record) ...
>
> and the record at that point has 0 when it should have a calculated number.
>
> Looking in RecordFactoryInputStream.nextRecord() and adding a debug
> statement, it appears that the number is already calculated:
>
> *record = readNextRecord();
> if (record instanceof FormulaRecord) {
>        System.out.println ("RecordFactoryInputStream.nextRecord(); " +
> record.toString());
> }*
>
> Toggle HighlightingOpen in New WindowSelect All
>
> RecordFactoryInputStream.nextRecord(); [FORMULA]
>    .row    = 0x0003
>    .col    = 0x0002
>    .xfindex= 0x0040
>  .value       = -0.1609487366724528
>  .options   = 0x0008
>    .alwaysCalc= false
>    .calcOnLoad= false
>    .shared    = true
>  .zero      = 0xFE090003
>    Ptg[0]=[Array Formula or Shared Formula]
> row = 3
> col = 2
> .
> [/FORMULA]
>
> The output data above is a case where the formula is calculated correctly.
>
> Using 3.8 beta3.
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Where-is-POI-formula-read-for-HSSF-tp4967675p4967675.html
> 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
>
>

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