Bug 43693 - setLandscape(true) has no effect on a loaded sheet.
Summary: setLandscape(true) has no effect on a loaded sheet.
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-FINAL
Hardware: PC Windows 2000
: P2 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-10-24 09:25 UTC by Andreas Kramer
Modified: 2015-05-01 19:55 UTC (History)
1 user (show)



Attachments
the template file (13.50 KB, application/vnd.ms-excel)
2007-10-24 09:26 UTC, Andreas Kramer
Details
The workbook i created with the given reproduce code (12.00 KB, application/vnd.ms-excel)
2007-10-24 09:28 UTC, Andreas Kramer
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andreas Kramer 2007-10-24 09:25:14 UTC
If you create a workbook from an existing xls file, the setLandscape method has
no effect.

If you create a new sheet instead of using the loaded one, the landscape option
works fine. (Commented line)

What i did exactly:

    public static void main(String[] args)
    {        
        try{
            // Read in a template xls document
            InputStream stream = new FileInputStream("c:/temp/template.xls");
           
            HSSFWorkbook wb = new HSSFWorkbook(stream);
            HSSFSheet sheet = wb.getSheetAt(0);
            // HSSFSheet sheet = wb.createSheet("New Sheet");
            
            HSSFPrintSetup setup = sheet.getPrintSetup();
            setup.setLandscape(true);
            
            // Create a row and put some cells in it. Rows are 0 based.
            HSSFRow row = sheet.createRow((short) 1);
            HSSFCell cell = row.createCell((short) 2);
            cell.setCellValue("XXXX");
    
            // Write the output to a file
            FileOutputStream fileOut = new FileOutputStream("c:/temp/workbook.xls");
            wb.write(fileOut);
            fileOut.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

The template.xls is simple a new created and saved xls document. (I would have
attached it, but did not know how.)

Used versions:

POI: poi-3.0.1-FINAL-20070705.jar
Java: jdk150_04
Comment 1 Andreas Kramer 2007-10-24 09:26:57 UTC
Created attachment 21037 [details]
the template file
Comment 2 Andreas Kramer 2007-10-24 09:28:05 UTC
Created attachment 21038 [details]
The workbook i created with the given reproduce code
Comment 3 Nick Burch 2008-01-08 05:30:45 UTC
I've added two new tests to
src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheet.java

One of these does setLandscape on an existing sheet, the other on a newly
created sheet. (Methods are testPrintSetupLandscapeNew() and
testPrintSetupLandscapeExisting())

After doing a save and a re-open, getLandscape is correctly working for both of
them.

Could you please create a failing testcase for your problem, and attach that to
the bug? As it is, I'm unable to replicate your problem.
Comment 4 Vinu Kumar 2009-11-30 23:59:03 UTC
I am able to reproduce this behaviour. The methods work fine in xlsx file while they dont work in xls file. The client in MS Excel 2007. 

            PrintSetup setup = s.getPrintSetup();
            setup.setPaperSize(PrintSetup.LEGAL_PAPERSIZE);
            setup.setLandscape(true);

Print properties for an xlsx file show the paper size as LEGAL and it is also set to landscape mode, whereas in xls file the paper size is A4 (default) and its set to portrait.

Note: The get methods show the correct values
Comment 5 Vinu Kumar 2009-12-01 00:00:38 UTC
I am using POI 3.5 final
Comment 6 Walter Higgins 2009-12-22 07:08:58 UTC
The following code can be used to reproduce this problem.
Create a standard blank workbook and save it as test.xls.
Compile and run the following TestPOI.java source.
Open the out.xls file in excel and choose 'File' -> 'Page Setup' from the application menu. The page orientation has not changed to Landscape.

import java.io.*;

import org.apache.poi.hssf.usermodel.*;
import java.util.*;

public class TestPOI {

    public static void main(String[] args) throws Exception{

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("test.xls"));
        int sheetCount = workbook.getNumberOfSheets();

        for (int i = 0; i < sheetCount; i++){
            System.out.println(i);
            HSSFSheet sheet = workbook.getSheetAt(i);
            HSSFPrintSetup print = sheet.getPrintSetup();
            print.setLandscape(true);
        }

        workbook.write(new FileOutputStream("out.xls"));
    }
}
Comment 7 Walter Higgins 2009-12-22 07:20:09 UTC
(In reply to comment #6)

This problem still occurs in poi-3.6-20091214
Comment 8 Dominik Stadler 2015-05-01 19:55:10 UTC
It seems Excel has some strange checking on the values set in the PrintSetup, so if you do not set all of them to useful values, Excel may ignore the settings and use defaults, e.g. what did work for me was the following:

        setup.setLandscape(true);
        setup.setPaperSize(PrintSetup.A4_PAPERSIZE);
        setup.setScale((short)100);
        setup.setValidSettings(false);

Also the setValidSettings() could be interferring here. Generally it should be "false" to not make Excel use default values!

However I don't think we can re-implement the checking that Excel does internally as it is not part of the Spec and thus a complete mystery to us, so I think this is WONTFIX for us unless someone comes up with a better way to handle the print settings.