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,802
    Rep Power
    19

    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.

    ** This space for rent **

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
  •