poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Beardsley <markbrd...@tiscali.co.uk>
Subject Re: showInPane does not work
Date Wed, 07 Oct 2015 13:38:28 GMT
Three days of rain and it is too wet to work in the reed beds. So, I have
been able to play and make some real progress. The code below will make sure
that cell A1 is both active and appears at the top leaft hand corner of the
worksheet 'window'.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView;

public class ActiveCellTest {
  
  public static void setCell(String inputFilename, String outputFilename) {
    
    File file = null;
    FileOutputStream fos = null;
    BufferedOutputStream bos = null;
    FileInputStream fis = null;
    BufferedInputStream bis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    CTWorksheet ctWorksheet = null;
    CTSheetViews ctSheetViews = null;
    CTSheetView ctSheetView = null;
    
    try {
      file = new File(inputFilename);
      fis = new FileInputStream(file);
      bis = new BufferedInputStream(fis);
      
      workbook = new XSSFWorkbook(bis);
      
      bis.close();
      
      // Iterate through the sheets and make cell active
      for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
        sheet = workbook.getSheetAt(i);
        
        // First step is to get at the CTWorksheet bean underlying the
worksheet.
        ctWorksheet = sheet.getCTWorksheet();
        // From the CTWorksheet, get at the sheet views.
        ctSheetViews = ctWorksheet.getSheetViews();
        // Grab a single sheet view from that array
        ctSheetView =
ctSheetViews.getSheetViewArray(ctSheetViews.sizeOfSheetViewArray() - 1);
        // Se the address of the top left hand cell.
        ctSheetView.setTopLeftCell("A1");
        
        // Also, make sure that cell A1 is the active cell
        sheet.setActiveCell("A1");
      }
      
      file = new File(outputFilename);
      fos = new FileOutputStream(file);
      bos = new BufferedOutputStream(fos);
      workbook.write(bos);
      
    }
    catch(IOException ioEx) {
      System.out.println("Catch of setCell() and caught an " +
ioEx.getClass().getName());
      System.out.println("Message " + ioEx.getMessage());
      System.out.println("Stacktrace");
      ioEx.printStackTrace(System.out);
    }
    finally {
      try {
        if(bos != null) {
          bos.flush();
          bos.close();
        }
      }
      catch(IOException ioEx) {
        System.out.println("Catch of finally clause in setCell() and caught
an " + ioEx.getClass().getName());
        System.out.println("Message " + ioEx.getMessage());
        System.out.println("Stacktrace");
        ioEx.printStackTrace(System.out);
      }
    }
  }
}

It really goes without saying, but to run the code do something like this;

     
ActiveCellTest.setCell("/home/markb/Public/java/testdocs/ActiveCellTest.xlsx",
                            
"/home/markb/Public/java/testdocs/NewSetCellTest.xlsx");

where the first parameter is the path to and name of the Excel file you wish
to open and the second the path to and name under which it should be saved.

There is one big problem with the code as it stands - what happens if there
is no sheet view in the xml markup? Well, the answer is that we would have
to create a new one and I will look into that. For now, try the code out and
see if it accomplishes what you are after. Use it with care as I would never
make the claim that this is production ready code.




--
View this message in context: http://apache-poi.1045710.n5.nabble.com/showInPane-does-not-work-tp5720321p5720509.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


Mime
View raw message