Page 1 of 1

green triangle in generated excel

PostPosted: Fri Feb 06, 2015 2:36 pm
by fachhoch
I am filing place holders with values in my excel, I know the row and column which have placeholders which I replace with actual values, the generated excel has green triangle in most of the cells, Please advice what more should I set on the cell so that the the green triangle will not appear.

Re: green triangle in generated excel

PostPosted: Fri Feb 06, 2015 3:39 pm
by jason
Please attach sample xlsx so we can see what those "green triangles" look like

Re: green triangle in generated excel

PostPosted: Sat Feb 07, 2015 1:44 am
by fachhoch
Thank you, attached the file.
art (5).xlsx
excel file with green triangle
(9.13 KiB) Downloaded 436 times

Re: green triangle in generated excel

PostPosted: Sat Feb 07, 2015 12:06 pm
by jason
You have numbers stored as text.

Remove @t="s" from the c elements in your worksheet

Re: green triangle in generated excel

PostPosted: Tue Feb 10, 2015 1:25 am
by fachhoch
Thank you , should i remove it manually ? or I can do it programmatically, below is the code to find the cells and update text.
cellChangeDTOs is a dto containg cellNumber, rowNumber, identifier and value, for each CellChangeDto I find theorg.xlsx4j.sml. Cell object,
call getT() , throuh whihc I find the handler to read or update text. Plesae advice how to remove this @t="s" from code.



Code: Select all
SheetData  sheetData=getSheetData(sc.sheetName);
            sc.cellChangeDTOs.forEach(cc->{
               Row row= sheetData.getRow().stream().filter(r-> r.getR()==cc.rowIndex).findFirst().get();
               Cell cell= row.getC().stream().filter(c-> c.getR().equals(cc.cellIndex)).findFirst().get();
               STCellType stCellType= cell.getT();
               Params  params= new Params();
               params.cell=cell;
               params.sharedStrings=sharedStrings;
               typeHandlersMap.get(stCellType).apply(params).replaceText("{"+cc.identifier+"}", cc.value);




Code: Select all
private static class SCellType extends  CellTypeHandler{
      SharedStrings  sharedStrings;
      @Override
      String getCellText() {
         //CTRst sharedElement=sharedStrings.getJaxbElement().getSi().get(Integer.parseInt(cell.getV()));
         CTRst sharedElement=getShredStringElement();
         CTXstringWhitespace  ctXstringWhitespace=sharedElement.getT();
         String text=null;
         if(ctXstringWhitespace!=null){
            text=ctXstringWhitespace.getValue();
         }else{
            if(!sharedElement.getR().isEmpty()){
               StringBuffer  stringBuffer= new StringBuffer();
               for(CTRElt ctrElt:sharedElement.getR() ){
                  stringBuffer.append(ctrElt.getT().getValue());
               }
               text=stringBuffer.toString();
            }
         }
         return text;
      }
      synchronized  CTRst  getShredStringElement(){
         return sharedStrings.getJaxbElement().getSi().get(Integer.parseInt(cell.getV()));
      }
      @Override
      void replaceText(String identifier, String value) {
         CTRst sharedElement=getShredStringElement();
         CTXstringWhitespace  ctXstringWhitespace=sharedElement.getT();
         String text=ctXstringWhitespace.getValue();
         text=StringUtils.replace(text, identifier, value);
         ctXstringWhitespace.setValue(text);
      }
   }
      private static  class StrTypeHandler extends  CellTypeHandler{
         @Override
         String getCellText() {
            return cell.getV();
         }
         @Override
         void replaceText(String identifier, String value) {
            String text=cell.getV();
            text=StringUtils.replace(text, identifier, value);
            cell.setV(text);
         }
      }


Code: Select all
private static Map<STCellType, Function<Params, CellTypeHandler>> typeHandlersMap = new HashMap<STCellType, Function<Params, CellTypeHandler>>(){{
         put(STCellType.S, t->{
               SCellType sCellType= new SCellType();
               sCellType.cell=t.cell;
               sCellType.sharedStrings=t.sharedStrings;
               return sCellType;
            }
         );
         put(STCellType.STR,  t-> {
               StrTypeHandler  strTypeHandler= new StrTypeHandler();
               strTypeHandler.cell=t.cell;
               return strTypeHandler;
            }
         );
      }};
      


Re: green triangle in generated excel

PostPosted: Wed Feb 11, 2015 10:31 am
by jason
Essentially, for Cell cell, avoid:

Code: Select all
cell.setT(org.xlsx4j.sml.STCellType.S);


Alternatively, fix it with cell.setT(null);