Updating excel spreadsheet

[nick angel]

Hello to everyone.I have been successful in modifying the data in an spreadsheet. I was able to continue modifying the same spreadsheet whil eit is open. The code (although I am sure not the most efficient) that I have written is the following:

  
 
 public void updateExcelSpreadSheet(HashMap stockMap)  throws java.io.IOException{
        FileInputStream is = new FileInputStream(this.spreadSheetPath);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
 
        for(Iterator iter = stockMap.entrySet().iterator(); iter.hasNext() :wink: {
            Map.Entry entry = (Map.Entry) iter.next();
            String stockName = (String)entry.getKey();
            StockInfo stockInfoObj = (StockInfo)entry.getValue();
            //get equivalent excel stock name
            String excelStockName = (String)stockEquivNames.get(stockName.trim());
            updateExcelRow(excelStockName, stockInfoObj, sheet);
        }
 
        FileOutputStream fileOut = new FileOutputStream( this.spreadSheetPath);
        wb.write(fileOut);
        fileOut.close();
        is.close();
    }
 
    public void updateExcelRow(String excelStockName, StockInfo stockInfoObj, HSSFSheet sheet) throws java.io.IOException{
        int rows = sheet.getLastRowNum();
 
        for (int i = 0; i <= rows; i++) {
            HSSFRow row = sheet.getRow(i);
            row.toString();
            HSSFCell cell = row.getCell((short)0);
            if(cell != null){
                if(cell.getStringCellValue().equals(excelStockName)){
                    HSSFCell lastQuote  = row.getCell((short)1);
                    lastQuote.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    //if using text file as source comment this out
                    lastQuote.setCellValue(Double.parseDouble((stockInfoObj.getlastQuote()).replaceAll(",","")));
                    System.out.println(stockInfoObj.toString());
                    //if using text file as source remove comments
                    //the following line is used temporarily instead of the line above to simulate the changes in the stock values
                    //lastQuote.setCellValue(Double.parseDouble((stockInfoObj.getlastQuote()).replaceAll(",",""))+(++counter*100));
                }
            }
        }
 
    }
 

  

The problem I am having is that while the spreadsheet is open and changes are being made, I cannot see them on the spreadsheet as they occur in "real-time". Is there any way this can be achieved? Any help would be greatly appreciated. Thank you in advance.

[Ulf Dittmer]

My gut feeling is that that's not possible. Excel opens a file, reads it, closes it and displays it. If you were to make a small change in Excel and save it, the changes made by POI would be lost. It's only because Excel is no longer looking at the file that POI is able to change it; otherwise the file would be locked for write operations.