Results 1 to 3 of 3
  1. #1
    smhasan11 is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default How to keep track whether writing in a worksheet is finished?

    Hi I'm reading a WorkBook using POI and then highlighting certain cells based on my calculation.
    If my workbook has only one sheet then its work fine; but if there are multiple sheets then it gets screwed up. I'm doing something as follows:
    Java Code:
    for( each workBook){
    
      for(each workSheet){
        
         //do some calcualtion on the cells...
        
         highLightCells(workBook, workSheet, cellToHighLight)
      }
    }
    
    
    	private void highLightColumn(ExcelWorkBook workbook, ExcelWorkSheet workSheet, ExcelMaskingInfo maskingInfo){
    
    		try{
    			this.outputStream = new FileOutputStream(workbook.getFileName());
    
    			String startPosition = maskingInfo.getStartPosition();
    			ArrayList<Integer> startP = this.convertPosition(startPosition);
    
    			String endPosition = maskingInfo.getEndPosition();
    			ArrayList<Integer> endP = this.convertPosition(endPosition);
    
    			// index=1 in both Lists(startP, endP) contain numeric digits of position.
    			//eg: position = G23; startP.get(1) = 23
    
    			//iterate through all rows after the columnHeaderRow
    			for(int rowNum = startP.get(1); rowNum <= endP.get(1); rowNum++){			
    
    				HSSFRow row = workSheet.getSheet().getRow(rowNum); // get the current row
    
    				if (row == null) { // if any row is EMPTY, poi treats it as null
    					continue;
    				}
    
    				// get the specific column
    				HSSFCell cell = row.getCell(startP.get(0));
    
    				if (cell == null) { 
    					continue;
    				}
    				else{
    					//System.out.println("cellValue="+cell.getStringCellValue());
    					HSSFCellStyle cellStyle = cell.getCellStyle();
    					
    					//create and set a new font for this cell
    					cellStyle.setFont(this.getCamoFont(workbook.getExcelWorkBook()));
    					
    					cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    					cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    
    				}
    
    			}	
    
    			// write back to the workBook	
    			workSheet.getSheet().getWorkbook().write(this.outputStream);
    			outputStream.flush();
    			outputStream.close();
    		}
    		catch (Exception e) {
    			e.printStackTrace();
    		}
    
    	}
    Now, lets say, I've 2 sheets in a workbook
    in "sheet1"- column-A, column-D and column -F should be highlighted. and in "sheet2" only column -C should be highlighted...
    But my program highlights column -F in sheet2 as well.

    I guess, when POI is writing back to the worksheet, I need to some how keep track whether its finished before it goes to the next sheet.
    Am I correct to say that? Or what else might be the reason?
    Any comments, how can I do that?

  2. #2
    smhasan11 is offline Member
    Join Date
    Dec 2010
    Posts
    4
    Rep Power
    0

    Default Re: How to keep track whether writing in a worksheet is finished?

    * Modifying current cell's CellStyle effects other cell for no good reason...
    * After trying and banging my heads for all day,
    * I found the following comments in <a> Busy Developers' Guide to HSSF and XSSF Features </a>
    *
    * "
    It is important to create a new cell style from the workbook otherwise you can end up
    modifying the built in style and effecting not only this cell but other cells.
    "

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,450
    Rep Power
    18

    Default Re: How to keep track whether writing in a worksheet is finished?

    That's about right.
    The recommendation, which you just quoted, is to create a new style and apply it to the cell.
    It also says to reuse styles...that is, don't create the same style multiple times because Excel won't like that. I think it has a style limit.
    I've fallen foul of that latter one before now.
    Please do not ask for code as refusal often offends.

Similar Threads

  1. Finished the code but don't know how to run this...
    By gomdohri in forum New To Java
    Replies: 5
    Last Post: 09-08-2011, 03:36 PM
  2. help with assignment near finished
    By belfast09 in forum New To Java
    Replies: 11
    Last Post: 06-15-2011, 04:00 AM
  3. Export Excel which has more than 1 worksheet
    By rajesh_grs in forum Advanced Java
    Replies: 4
    Last Post: 03-23-2011, 04:47 PM
  4. Replies: 0
    Last Post: 10-29-2010, 11:25 AM
  5. Reading excel worksheet
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-13-2008, 11:19 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •