Is there any way to move horizontal scroll bar the

问题:

I have written a program that reads Excel template sheet. In which the first column was hidden. Now, I have a code that un-hide the excel column programmatically (so Column start from A1).

I'm using Apache POI 3.16 version.

When I open a file, it should show me a column from A1 instead it shows me from B1 column. When I write below code for XLS, It working properly but didn't work for an XLSX format.

sheet.showInPane(0, 0);

I need to manually move the horizontal scroll bar to view my first column. How should I achieve this programmatically to auto-scroll to the first column for XLSX format?

Here is my full code.

public Workbook readWorkBookAndWriteErrors(String bufId,String inputFile, String ext) throws Exception {
        Workbook workBook =null;
        Sheet sheet = null; 
        if(GlobalVariables.EXCEL_FORMAT_XLS.equalsIgnoreCase(ext)){
             // Get the workbook instance for XLS file
            workBook = new HSSFWorkbook(new FileInputStream(inputFile));
        }else{
            // Get the workbook instance for XLSX file
            workBook = new XSSFWorkbook(new FileInputStream(inputFile));
        }
        sheet = workBook.getSheetAt(0);
        Row row = null;
        if(sheet.isColumnHidden(0)){
            sheet.setColumnHidden(0, false);
            sheet.setActiveCell(new CellAddress("A1"));
            sheet.showInPane(0, 0);
            sheet.createFreezePane(0, 1);

             Iterator<Row> rowIterator = sheet.iterator();
                int rowIndex = 1;
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    if(rowIndex == 1){
                        rowIndex++;
                        continue;
                    }
                    Cell cell = row.createCell(0);
                    cell.setCellValue("error message");
                    rowIndex++;
                }
        }
        return workBook;
    }

回答1:

Here is the answer to my question. Please refer this Source

public Workbook readWorkBookAndWriteErrors(String bufId,String inputFile, String ext) throws Exception {
        Workbook workBook =null;
        Sheet sheet = null; 
        if(GlobalVariables.EXCEL_FORMAT_XLS.equalsIgnoreCase(ext)){
             // Get the workbook instance for XLS file
            workBook = new HSSFWorkbook(new FileInputStream(inputFile));
        }else{
            // Get the workbook instance for XLSX file
            workBook = new XSSFWorkbook(new FileInputStream(inputFile));
        }
        sheet = workBook.getSheetAt(0);
        Row row = null;
        if(sheet.isColumnHidden(0)){
            sheet.setColumnHidden(0, false);

            if(sheet instanceof XSSFSheet){
                CTWorksheet ctWorksheet = null; 
                CTSheetViews ctSheetViews = null; 
                CTSheetView ctSheetView = null; 
                XSSFSheet tempSheet = (XSSFSheet) sheet;
                 // First step is to get at the CTWorksheet bean underlying the worksheet. 
                ctWorksheet = tempSheet.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"); 
            }else{
                 sheet.setActiveCell(new CellAddress("A1"));
                 sheet.showInPane(0, 0);
            }


             Iterator<Row> rowIterator = sheet.iterator();
                int rowIndex = 1;
                while (rowIterator.hasNext()) {
                    row = rowIterator.next();
                    if(rowIndex == 1){
                        rowIndex++;
                        continue;
                    }
                    Cell cell = row.createCell(0);
                    cell.setCellValue("error message");
                    rowIndex++;
                }
        }
        return workBook;
    }