Thanks a lot - will try ASAP. /Bengt
2015-10-07 15:38 GMT+02:00 Mark Beardsley <[email protected]>: > 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: [email protected] > For additional commands, e-mail: [email protected] > >
