Question posted in
Some documentation to get you started can be found here.

how to display the excel sheet in a web page using apache POI with same styles and font?

I tried using RichTextString to get individual character’s font, but it works fine only for a cell with at least one different style is applied. In the case of the same style applied throughout the cell:

font1 = workbook.getFontAt(richString.getFontAtIndex(i))

here, the richString.getFontAtIndex(i) returns 0, and thus the entire font object becomes null.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    the following code works fine for the cell with least one different style is applied. In case of the cell with the same style, the richString object return 0 which produces "null" font object

    def fontStyles() {
            String a="",styles="",value="",dummy="",newLine=""
            int flag=0
            Short[] rgb = new Short[3];
            Font font1=null
            HSSFFont font =null 
            InputStream inputStream = new FileInputStream("page.xls")
            Workbook workbook = new HSSFWorkbook(inputStream)
            HSSFSheet sheet = workbook.getSheetAt(0)
            for(Row row:sheet) {
                value=""
                for(Cell cell:row) {
                     flag=0
                    value=""
            RichTextString richString = cell.getRichStringCellValue()
            println "richString ${richString}"
            String string = richString.toString()
            HSSFCellStyle cs = cell.getCellStyle()
            println "richString.length() ${richString.length()}"
            for(int i=0;i<richString.length();i++) {
                newLine=""
                println "${i} ${styles}"
                font = workbook.getFontAt(richString.getFontAtIndex(i))

                if(i<richString.length()-1) {
                    font1 = workbook.getFontAt(richString.getFontAtIndex(i+1))
                } else {
                    font1 = workbook.getFontAt(richString.getFontAtIndex(i))
                }
            if(!(string.charAt(i).toString().equals(" "))){
                HSSFPalette paletteIns = ((HSSFWorkbook)workbook).getCustomPalette()
                HSSFColor color = paletteIns.getColor(font.getColor())
                HSSFColor color1 = paletteIns.getColor(font1.getColor())
                Short[] r = color.getTriplet()
                rgb[0] = r[0]
                rgb[1] = r[1]
                rgb[2] = r[2]

                if( (font.getItalic()) && (font.getUnderline()) ) {
                    dummy="<i><u>"+string.charAt(i).toString()+"</i></u>"
                } else if(font.getItalic()) {
                    dummy="<i>"+string.charAt(i).toString()+"</i>"
                } else if(font.getUnderline()) {
                    dummy="<u>"+string.charAt(i).toString()+"</u>"
                } else {
                    dummy=string.charAt(i).toString()
                } 

                if(string.charAt(i).toString().equals("n")) {
                    flag=1;
                   newLine="<br>"
              }

                if( (font.getFontName().equals(font1.getFontName())) 
                    &&(font.getFontHeightInPoints().equals(font1.getFontHeightInPoints()))
                    &&(Arrays.equals(color.getTriplet(),color1.getTriplet()))
                    && (!(string.charAt(i).toString().equals("n"))) ) {
                    value=value+dummy
                }else {
                    styles="<span style="font-family:"+font.getFontName()+";font-size:"+font.getFontHeightInPoints()+"px;color:rgb("+rgb[0]+","+rgb[1]+","+rgb[2]+");">"+value+""+dummy+""+"</span>"+newLine
                    a=a+styles
                    value=""
                    flag=1 
                }
            }else {
                value=value+" "
            }
        }
        if(flag==0) {
            styles="<span style="font-family:"+font.getFontName()+";font-size:"+font.getFontHeightInPoints()+"px;color:rgb("+rgb[0]+","+rgb[1]+","+rgb[2]+");">"+value+""+"</span>"+newLine
        }
        a=a+styles

            }
        }
        render view:"excelToHTML", model:[str:a]
    }

  2. 0 
      if(richString.getFontAtIndex(i)==0){
            font = workbook.getFontAt(cs.getFontIndex());
            font1 = workbook.getFontAt(cs.getFontIndex());     
   } else {
 font = workbook.getFontAt(richString.getFontAtIndex(i))
  font1 = workbook.getFontAt(richString.getFontAtIndex(i))
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search