Created attachment 23229 [details]
example input

Created attachment 23230 [details]
example output

I've tried poi-3.2-final, poi-3.5-final, and poi-3.6

This problem is not happened in poi-3.2
But this problem is happened in poi-3.5-final and poi-3.6

Any chance you could create 4 files:
* a simple document, but with enough data to make the print area make sense
* that document, as saved by excel having set the print area
* that document, as saved by POI 3.2 having set the print area
* that document, as saved by a recent POI svn checkout, having set the print area

If you're also able to use org.apache.poi.hssf.dev.BiffViewer to spot the differences between the 4, that'd be handy, as that'll help us narrow in on which bits have gone wrong

Created attachment 25545 [details]
TestPoiPrintArea.java

Created attachment 25546 [details]
Generated Excel File when TestPoiPrintArea.java is run with poi-3.6.jar

Created attachment 25547 [details]
Generated Excel File when TestPoiPrintArea.java is run with poi-3.2-FINAL.jar

Sorry,

I'm not able to use BiffViewer.java.
I also do not have good enough internet connection to checkout from poi svn.

I've attached 3 files:
1. TestPoiPrintArea.java
2. test_poi_36.xls: Generated Excel file when TestPoiPrintArea.java is being run with poi-3.6.jar (set print area failed)
3. test_poi_32.xls: Generated Excel file when TestPoiPrintArea.java is being run with poi-3.2-FINAL.jar (set print area success)

Do you need another document ?

Set print area is not working at all since poi-3.5-FINAL.jar, this is not intermittent bug or in specific case.

Thank you very much..

Could you please generate a file with the same data and print area in excel? That way we can also check that what we do matches what excel does once fixed, and will also be a useful one for a read unit test

Created attachment 25548 [details]
same document + set print area written manually in Excel (not using POI)

Created attachment 25552 [details]
Generated Excel File when TestPoiPrintArea.java is run with poi-3.7-SNAPSHOT-20100608.jar

The set print area still not working on poi-3.7-snapshot

Fixed in r953180. It seems that some versions of excel are pickier than others about the TabIdRecord when processing the print rules, and we'd stopped updating it for new sheets....

(In reply to comment #12)
> Fixed in r953180. It seems that some versions of excel are pickier than others
> about the TabIdRecord when processing the print rules, and we'd stopped
> updating it for new sheets....

Thanks a lot.. 
I will try when poi-3.7-SNAPSHOT-20100610.jar is available on http://encore.torchbox.com/poi-cvs-build/

I've running the TestPoiPrintArea.java using poi-3.7-SNAPSHOT-20100610.jar and the print area still not set. (bug still not solved)

Does this snapshot contain fixed version (r953180) ?

Created attachment 25577 [details]
Generated Excel File when TestPoiPrintArea.java is run with poi-3.7-SNAPSHOT-20100610.jar

Hmm, maybe it wasn't just the TabID problem

The only other difference I can spot is in the print area name record:

     .NameIsMultibyte        = false
     .Name (Unicode text)    = Print_Area
     .Formula (nTokens=1):
-       org.apache.poi.hssf.record.formula.Area3DPtg [sheetIx=0 ! $A$1:$C$1]R
+       org.apache.poi.hssf.record.formula.Area3DPtg [sheetIx=0 ! $A$1:$C$1]V

(- is for the 3.2 version, + for the 3.7 one)

It seems to have been switched from a by-reference to by-value definition

I've no idea why, or what this'll do to excel (well, other than confuse yours but not mine). I guess one for Josh (our resident formula guru)

Is this topic dead? I'm noticing the same bug in the POI 3.7 release. If there is any information I can provide tell me and I'll do what I can.

Best Regards

(In reply to comment #17)
> Is this topic dead? I'm noticing the same bug in the POI 3.7 release. If there
> is any information I can provide tell me and I'll do what I can.

I'd suggest you try hacking the Ptg on the definition to be Reference not Value, and see if that fixes it. (It'll probably mean some low level fiddling about in the record layer)

If it does, we can make the change in POI. If not, more work will be needed to identify the problem.

I found a difference HSSFWorkbook and XSSFWorkbook.

In XSSFWorkbook#setPrintArea(), FormulaParser#parse() is called as follows.

FormulaParser.parse (formulaText, fpb, FormulaType.NAMEDRANGE, getSheetIndex ());

The third argument is FormulaType.NAMEDRANGE.

However, in HSSFWorkbook#setPrintArea(), third argument is FormulaType.CELL as follows.

HSSFFormulaParser.parse (sb.toString (), this, FormulaType.CELL, sheetIndex)

In fact, if I create a following class, setPrintArea works fine.

--------------------------------------------------------------------------------

import java.lang.reflect.Field;
import java.util.regex.Pattern;

import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.NameRecord;
import org.apache.poi.hssf.record.formula.SheetNameFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaType;

public class MyHSSFWorkbook extends HSSFWorkbook {

    private static final Pattern COMMA_PATTERN = Pattern.compile(",");
    private Workbook workbook;

    public MyHSSFWorkbook() throws NoSuchFieldException, IllegalAccessException {
        this.workbook = getLowLevelWorkbook();
    }

    private Workbook getLowLevelWorkbook() throws NoSuchFieldException, IllegalAccessException {
        Field field = HSSFWorkbook.class.getDeclaredField("workbook");
        field.setAccessible(true);
        return (Workbook) field.get(this);
    }

    @Override
    public void setPrintArea(int sheetIndex, String reference) {
        NameRecord name =
                workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex + 1);

        if (name == null) {
            name = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex + 1);
            // adding one here because 0 indicates a global named region; doesn't make sense for print areas
        }
        String[] parts = COMMA_PATTERN.split(reference);
        StringBuffer sb = new StringBuffer(32);
        for (int i = 0; i < parts.length; i++) {
            if (i > 0) {
                sb.append(",");
            }
            SheetNameFormatter.appendFormat(sb, getSheetName(sheetIndex));
            sb.append("!");
            sb.append(parts[i]);
        }
        // FormulaType.CELL -> FormulaType.NAMEDRANGE
        name.setNameDefinition(HSSFFormulaParser.parse(sb.toString(), this, FormulaType.NAMEDRANGE,
            sheetIndex));
    }
}

--------------------------------------------------------------------------------

Is this a bug?

Thanks for tracking that down. I've changed HSSFWorkbook to set the correct type, and added a unit test for it. Committed in r1069780.