Results 1 to 10 of 10
  1. #1
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default conditional formatting does not work on copied cells

    Hi everyone!

    I am copying cells (value and cellstyle) from one workbook to another,
    using the code below.
    Now i have two xlsx files, where src.xlsx is the source - where the values are copied from - and the
    second one is dst.xlsx.

    The dst.xlsx has conditional formatting, e.g. the cells of the first column are "green" if the value is "1" (numeric).

    The problem is, the copied cells are not formatted automatically anymore.
    That means, the copied cell in column one has the value "1" but is not green (at least not in excel, in libreoffice it is).
    It's easy to fix it by hand: Simply select the cell, right click, select filling options and click on the already selected "automatic" or "none".
    Afterwards, the cell is correctly displayed in green.

    The reason for this behaviour seems to be the following:
    POI creates a new FillStyle:

    Java Code:
    <patternFill>
    <fgColor indexed="64"/>
    <bgColor indexed="64"/>
    </patternFill>
    even if a cell is set to NONE by:

    Java Code:
    CellUtil.setCellStyleProperty(newCell, CellUtil.FILL_PATTERN, FillPatternType.NO_FILL);
    The correct (or at least "working") solution would be to set the cell to the predefined (fillId=0):
    Java Code:
    <fill>
    <patternFill patternType="none"/>
    </fill>
    Excel seems to apply conditional formatting only to two sorts of cells:
    1) Those who have the "default" fillstyle - that means fillId=0
    2) Those, who have set the fillstyle to: patternType="solid">

    Looks like a bug in POI, or at least in Excel... right?
    Any suggestions for a workaround? What I need is a way to set the FillStyle (only) to the default one (fillId=0)

    Greetings, Flo


    Java Code:
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellUtil;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    
    public class copyHelper{
    
        private final XSSFSheet srcSheet;
        private final XSSFSheet dstSheet;
    
       public copyHelper(XSSFSheet srcWorksheet, XSSFSheet dstWorkheet){
    
            this.srcSheet = srcWorksheet;
            this.dstSheet = dstWorkheet;
        }
        
        public void copyMergedRegions(){
    
            List<CellRangeAddress> rgLst = srcSheet.getMergedRegions();
            for(CellRangeAddress rg : rgLst)
                dstSheet.addMergedRegion(rg);
        }
                
    
        public void copyRow(int srcRowNum, int dstRowNum) {
            
            // Get the srcSheet row at srcRowNum
            // and the dstSheet row at dstRowNum
            XSSFRow srcRow = srcSheet.getRow(srcRowNum);
            
            // If the row doesn't exist in the destination worksheet, create
            if(dstSheet.getRow(dstRowNum) == null)
                dstSheet.createRow(dstRowNum);
            
            XSSFRow dstRow = dstSheet.getRow(dstRowNum);
    
            // Loop through srcSheet columns to add to new row
            for (int i = 0; i < srcRow.getLastCellNum(); i++) {
                
                // Grab a referencing copy of the old/new cell
                XSSFCell oldCell = srcRow.getCell(i);
                XSSFCell newCell = dstRow.createCell(i);
    
                // If the old cell is null jump to next cell
                if (oldCell == null) {
                    newCell = null;
                    continue;
                }
    
                // Copy style from old cell and apply to new cell
                // TODO: Do not copy each and every CellStyle since that leads to
                //       some sort of CellStyle explosion
                //            XSSFCellStyle newStyle = dstSheet.getWorkbook().createCellStyle();
                //            newStyle.cloneStyleFrom(oldCell.getCellStyle());      
                //            newCell.setCellStyle(newStyle);
                
                Map<String, Object> prop = new HashMap<>();
                prop.put(CellUtil.ROTATION, oldCell.getCellStyle().getRotation());
                CellUtil.setCellStyleProperties(newCell, prop);
                
      
                // If there is a cell comment, copy
                if (oldCell.getCellComment() != null)
                    newCell.setCellComment(oldCell.getCellComment());
    
                // If there is a cell hyperlink, copy
                if (oldCell.getHyperlink() != null)
                    newCell.setHyperlink(oldCell.getHyperlink());
    
                // Set the cell data type
                newCell.setCellType(oldCell.getCellType());
    
                // Set the cell data value
                switch (oldCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        newCell.setCellValue(oldCell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        newCell.setCellValue(oldCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        newCell.setCellErrorValue(oldCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        newCell.setCellFormula(oldCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        newCell.setCellValue(oldCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        newCell.setCellValue(oldCell.getRichStringCellValue());
                        break;
                }
            }
        }
    }
    Last edited by flodo; 03-12-2017 at 10:15 PM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: conditional formatting does not work on copied cells

    Can you figure out what is creating the FillStyle?

    Does it exist simply from the createCell call, or is it one of the other, later, calls that set some values (eg the setStyleProperties maybe)?

    One thing (not related):
    Java Code:
                XSSFCell oldCell = srcRow.getCell(i);
                XSSFCell newCell = dstRow.createCell(i);
     
                // If the old cell is null jump to next cell
                if (oldCell == null) {
                    newCell = null;
                    continue;
                }
    if oldCell is null I would argue you shouldn't be creating a new cell at all.
    At the moment you are (potentially) creating cells for no reason, possibly making the sheet larger than it should be.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default Re: conditional formatting does not work on copied cells

    Thanks for the hint, i will fix that.

    The new fill styles are created by
    Java Code:
    prop.put(CellUtil.FILL_PATTERN, FillPatternType.NO_FILL);
    CellUtil.setCellStyleProperties(newCell, prop);
    The behaviour should be:
    set the FillId to 0 (the default patternStyle="NONE"), but POI creates a new style (with indexed values 64) instead:

    Java Code:
    <patternFill>
    <fgColor indexed="64"/>
    <bgColor indexed="64"/>
    </patternFill>
    even the following doesn't work:

    Java Code:
    <patternFill patternType="none">
    <fgColor indexed="64"/>
    <bgColor indexed="64"/>
    </patternFill>
    neither does

    Java Code:
    <patternFill patternType="none">
    <fgColor indexed="64"/>
    <bgColor indexed="64"/>
    </patternFill>
    since the new patternFill style is NOT fillId=0.

    Therefore i would need a way to set the fillId "by hand" to 0 (the default "none")

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: conditional formatting does not work on copied cells

    I've never used the CellUtil helpers for CellStyles.
    What happens if you create a CellStyle that represents what you want, as a constant for the copyHelper (should be CopyHelper, by the way) class?
    Then apply that to each cell.
    That would ensure that the Util isn't doing something strange.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default Re: conditional formatting does not work on copied cells

    I made a "Standalone" Test Example and added the excel files, maybe someone can confirm the behaviour this way:

    Java Code:
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.util.CellUtil;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    
    public class PoiTest {
        
        private final XSSFWorkbook wbOut;
        private final XSSFWorkbook wbIn;
    
        private final XSSFSheet srcSheet;
        private final XSSFSheet dstSheet;
        
        private XSSFRow srcRow = null;
        private XSSFRow dstRow = null;
        
        public PoiTest() throws FileNotFoundException, IOException{
            
            // Files
            // TODO: Try...
            FileInputStream in = new FileInputStream("t1.xlsx");
            FileInputStream out_template = new FileInputStream("t2.xlsx");
            FileOutputStream out = new FileOutputStream("t2_noLonger.xlsx");
    
            // Grab the Worksheets
            wbOut = new XSSFWorkbook(out_template);
            dstSheet = wbOut.getSheetAt(0);
            
            wbIn = new XSSFWorkbook(in);
            srcSheet = wbIn.getSheetAt(0);
            
            
            int rowsToCopy = 2;
            
            for(int j = 0; j < rowsToCopy; j ++){
                
                // ... and finally the rows
                srcRow = srcSheet.getRow(j);
    
                // If the row doesn't exist in the destination worksheet, create
                if(dstSheet.getRow(j) == null)
                    dstSheet.createRow(j);
    
                dstRow = dstSheet.getRow(j);
    
    
                // Loop through srcSheet columns to add to new row
                for (int i = 0; i < srcRow.getLastCellNum(); i++) {
    
                    // Grab a referencing copy of the old cell
                    XSSFCell oldCell = srcRow.getCell(i);
    
                    // If the old cell is null jump to next cell
                    if (oldCell == null)
                        continue;
    
                    // Grab a referencing copy of the new cell
                    XSSFCell newCell = dstRow.createCell(i);
    
    
                    // THIS IS WHERE THE MISBEHAVIOUR IS:
                    // The Rotation Property shouldn't influence the FillPattern!?
                    // But in fact POI somehow does...
                    CellUtil.setCellStyleProperty(newCell, CellUtil.ROTATION, oldCell.getCellStyle().getRotation());
    
    
                    // If there is a cell comment, copy
                    if (oldCell.getCellComment() != null)
                        newCell.setCellComment(oldCell.getCellComment());
    
                    // If there is a cell hyperlink, copy
                    if (oldCell.getHyperlink() != null)
                        newCell.setHyperlink(oldCell.getHyperlink());
    
                    // Set the cell data type
                    newCell.setCellType(oldCell.getCellType());
    
                    // Set the cell data value
                    switch (oldCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            newCell.setCellValue(oldCell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            newCell.setCellValue(oldCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            newCell.setCellErrorValue(oldCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            newCell.setCellFormula(oldCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            newCell.setCellValue(oldCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            newCell.setCellValue(oldCell.getRichStringCellValue());
                            break;
                    }
                }
            }
    
            wbOut.write(out);
            out.close();
            in.close();
        }
    }
    The "funny" part in fact is, when you remove line 71
    Java Code:
    CellUtil.setCellStyleProperty(newCell, CellUtil.ROTATION, oldCell.getCellStyle().getRotation());
    then everything is fine (except that the styles are not copied ^^ for sure - but at least the conditional formatting works).
    But if you do not, the resulting styles.xml is totally fucked up:

    styles.xml of the resulting output (t2_noLonger.xlsx) without the line in question:
    https://gist.github.com/anonymous/d8...86edc61b8f45dc

    styles.xml with:
    https://gist.github.com/anonymous/f2...40746ccd362274


    @tolls:
    The problem is, that I don't know in beforehand which styles are used, i just want to copy the "original" ones :D
    edit: wait, you mean like a "template" which is then modified? nice idea, i will give it a try
    Attached Files Attached Files
    Last edited by flodo; 03-14-2017 at 11:13 AM.

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: conditional formatting does not work on copied cells

    Well, the only styles you have are the Fill thing, and whatever ROTATION values there are (which I guess aren't many?).
    So just create them beforehand.

    Java Code:
    private final Map<Short, CellType> cellTypes;
    ...
    short rotation = oldCell.getCellStyle().getRotation();
    CellStyle style = getStyle(rotation);
    XSSFCell newCell = dstRow.createCell(i, cellStyle);
    then the getStyle method will look in the Map for a style with that rotation. If it doesn't exist it will create one and add it to the Map.

    That's what I'm thinking of.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default Re: conditional formatting does not work on copied cells

    The Rotation thing is just a "placeholder" to simplify things a bit :)
    I want to copy everything (background color, font size, font color, ...).

    I'll try to sum up what i found until now (to order my thoughts):

    Java Code:
        public static void setCellStyleProperties(Cell cell, Map<String, Object> properties) {
            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle originalStyle = cell.getCellStyle();
            CellStyle newStyle = null;
            Map<String, Object> values = getFormatProperties(originalStyle);
            putAll(properties, values);
    
            // index seems like what index the cellstyle is in the list of styles for a workbook.
            // not good to compare on!
            int numberCellStyles = workbook.getNumCellStyles();
    
            for (int i = 0; i < numberCellStyles; i++) {
                CellStyle wbStyle = workbook.getCellStyleAt(i);
                Map<String, Object> wbStyleMap = getFormatProperties(wbStyle);
    
                // the desired style already exists in the workbook. Use the existing style.
                if (wbStyleMap.equals(values)) {
                    newStyle = wbStyle;
                    break;
                }
            }
    
            // the desired style does not exist in the workbook. Create a new style with desired properties.
            if (newStyle == null) {
                newStyle = workbook.createCellStyle();
                setFormatProperties(newStyle, workbook, values);
            }
    
            cell.setCellStyle(newStyle);
        }

    The equals-call compares the the new style to all the existing ones.
    Stringified output of the wbStyleMap (e.g. for Cell A1):

    in src:
    [fillPattern=NO_FILL, indention=0, fillBackgroundColor=64, hidden=false, topBorderColor=8, dataFormat=0, rotation=180, bottomBorderColor=8, borderTop=NONE, borderLeft=NONE, rightBorderColor=8, leftBorderColor=8, fillForegroundColor=64, borderRight=NONE, alignment=GENERAL, locked=true, borderBottom=NONE, wrapText=false, verticalAlignment=BOTTOM, font=2]

    in dst:
    [fillPattern=NO_FILL, indention=0, fillBackgroundColor=64, hidden=false, topBorderColor=8, dataFormat=0, rotation=0, bottomBorderColor=8, borderTop=NONE, borderLeft=NONE, rightBorderColor=8, leftBorderColor=8, fillForegroundColor=64, borderRight=NONE, alignment=GENERAL, locked=true, borderBottom=NONE, wrapText=false, verticalAlignment=BOTTOM, font=0]

    in dst (after rotation):
    [fillPattern=NO_FILL, indention=0, fillBackgroundColor=64, hidden=false, topBorderColor=8, dataFormat=0, rotation=180, bottomBorderColor=8, borderTop=NONE, borderLeft=NONE, rightBorderColor=8, leftBorderColor=8, fillForegroundColor=64, borderRight=NONE, alignment=GENERAL, locked=true, borderBottom=NONE, wrapText=false, verticalAlignment=BOTTOM, font=0]


    The Function recognizes that the the new style does not exist, and creates a new one. That's not the problem,... the problem is somewhere deeper - since the creation of the new style somehow seems to "overlook" the already existing fillType or somehing like that, i am still searching in the sourcecode... brb
    Last edited by flodo; 03-14-2017 at 12:48 PM.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: conditional formatting does not work on copied cells

    That's what I mean, though.

    I would create my own styles (as above) and see if that avoids the issue with the rotations change as it stands.
    If it does then yes, it's likely an issue with how the CellUtil code retrieves the CellStyles...or how the Workbook stores them.
    Any number of things, frankly.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default Re: conditional formatting does not work on copied cells

    Thanks for all your replies!
    I finally found out where the problem is:

    1. Excel shows conditional formatting if, and only if, the cell has fillId=0 (the default NONE one) OR if the fillStylePattern != NONE.
      Thats a bug in Excel, not in POI. The problem is, that Excel is de facto "Standard".
    2. POI calls like this to set the new Style: setCellStyleProperties >> setFormatProperties >> setFillPattern
      Here is what setFillPattern looks like:

      Java Code:
      public void setFillPattern(FillPatternType pattern) {
      		CTFill ct = getCTFill();
      		CTPatternFill ctptrn = ct.isSetPatternFill() ? ct.getPatternFill() : ct.addNewPatternFill();
      
      		if (pattern == FillPatternType.NO_FILL && ctptrn.isSetPatternType()) {
      				ctptrn.unsetPatternType();
      		} else {
      				ctptrn.setPatternType(STPatternType.Enum.forInt(pattern.getCode() + 1));
      		}
      
      		addFill(ct);
      }


    It (POI) does simply not check if the FillStyle does already exist, it always adds a new one. And in general, this is then not fullfilling condtions in 1)

  10. #10
    flodo is offline Member
    Join Date
    Mar 2017
    Posts
    6
    Rep Power
    0

    Default Re: conditional formatting does not work on copied cells

    Modification in order to make it working (in progress):

    #1: XSSFCellStyle.java

    Java Code:
        public void setFillPattern(FillPatternType pattern) {
        	
        	//////////////////////////////////////////////////////////
        	// START: MODIFICATION
    
        	// Check if the new FillPattern (pattern) exists in the current Fills
            // Problem: The current Style doesn't know anything about the workbook
            //   Is it okay to do it like that (with _styleSource)?
        
            List<XSSFCellFill> cellFills = this._stylesSource.getFills();
            
            XSSFCellFill cellFill = null;
            for(int fId = 0; fId < cellFills.size(); fId++){
            	cellFill = cellFills.get(fId);
    	        
            	if(FillPatternType.forInt(cellFill.getPatternType().intValue() -1) == pattern){
            		this.getStyleXf().setFillId(fId);
            		return;
            	}	
            }
            
            // DO WE NEED TO set applyFill too? Since the code below does!
            // addFill:
            // ---
            // int idx = _stylesSource.putFill(new XSSFCellFill(ct));
            // _cellXf.setFillId(idx);
            // _cellXf.setApplyFill(true);
            
            // END: MODIFICATION
        	//////////////////////////////////////////////////////////
    
            CTFill ct = getCTFill();
    
            CTPatternFill ctptrn = ct.isSetPatternFill() ? ct.getPatternFill() : ct.addNewPatternFill();
            if (pattern == FillPatternType.NO_FILL && ctptrn.isSetPatternType()) {
                ctptrn.unsetPatternType();
            } else {
                ctptrn.setPatternType(STPatternType.Enum.forInt(pattern.getCode() + 1));
            }
    
            addFill(ct);
        }

Similar Threads

  1. Conditional Formatting
    By mimidep in forum Apache POI
    Replies: 0
    Last Post: 06-11-2014, 11:27 PM
  2. Replies: 2
    Last Post: 02-03-2013, 12:09 AM
  3. Copied code from this tutorial- BufferStrategy
    By JavaWizKid in forum New To Java
    Replies: 19
    Last Post: 11-14-2011, 07:06 PM
  4. Replies: 0
    Last Post: 10-21-2011, 12:06 PM
  5. how can objects themselves be copied???
    By ishakteyran in forum New To Java
    Replies: 1
    Last Post: 12-29-2007, 10:04 AM

Posting Permissions

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