Set character formatting in Excel using POI

[Thomas Greene]

I want to set some values in the excel sheet using POI. These values must have the required character formatting like bold, italics and underline.

Say, the value that I need to set in some cell in the excel could be something like

<i>italics </i>plain<b><i> boldItalics</i></b><b> allThree</b><b><i><u> plain</u></i></b>


Whatever is there inside
<i>..</i> should be set as italics
<b>..</b> should be set as bold
<u>..</u> should be set as underline

I was trying to set this formatting by harcoding the indexes (for the time being) using the following code

 String str="<i>italics </i>plain<b><i> boldItalics</i></b><b> allThree</b><b><i><u> plain</u></i></b>";

HSSFWorkbook wb;

void func()
{
 try{
    File writeXcel=new File("C:/newOUT.xls");
FileOutputStream fileOut = new FileOutputStream(writeXcel);
wb=new HSSFWorkbook();
HSSFSheet sheet=wb.createSheet("Sheet 1");
HSSFRow row=sheet.createRow(0);
HSSFCell cell=row.createCell((short)0);

HSSFRichTextString rts=new HSSFRichTextString(str);
HSSFFont font=wb.createFont();

font.setItalic(true);
font.setUnderline((byte)1);
font.setBoldweight((short)700);
rts.applyFont(0,5,font);

font.setItalic(false);
font.setUnderline((byte)0);
font.setBoldweight((short)400);
rts.applyFont(6,11,font);

font.setItalic(false);
font.setUnderline((byte)0);
font.setBoldweight((short)700);
rts.applyFont(12,16,font);

cell.setCellValue(rts);
wb.write(fileOut);
fileOut.close();
 }catch(Exception e) {
e.printStackTrace(); 
 }

}

   

But it seems it cannot be done, once the font is set it is set for everything.
Can someone please tell me how to resolve this.
Thank You

[Paul Clapham]

I would suggest creating one HSSFFont object for each cell you want to apply a font to.

[Ulf Dittmer]

The following code shows how it is done. As in reading the styles, the first run of text is the cell style, and the ones after that are set especially by rts.applyFont.

It's important to create new fonts, though, and not re-use the same one for all runs. As you've discovered, not the font attributes are stored, but the font object itself. So if you use a single font object and change it later, then all styles get changed.

File writeXcel = new File("newOUT.xls");
FileOutputStream fileOut = new FileOutputStream(writeXcel);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet 1");
sheet.setZoom(5,4);   // 125 percent magnification
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)0);
 
HSSFRichTextString rts = new HSSFRichTextString("bbb iii uuu sss");
 
// get font of cell, which is used for the first run of styled text
HSSFCellStyle style = cell.getCellStyle();
int fontIdx = style.getFontIndex();
HSSFFont font = wb.getFontAt((short)fontIdx);
// bold
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 
// italic
HSSFFont italicFont = wb.createFont();
italicFont.setItalic(true);
rts.applyFont(4,7,italicFont);
 
// underline
HSSFFont underlinedFont = wb.createFont();
underlinedFont.setUnderline(HSSFFont.U_SINGLE);
rts.applyFont(8,11,underlinedFont);
 
// strikethrough
HSSFFont strikethroughFont = wb.createFont();
strikethroughFont.setStrikeout(true);
rts.applyFont(12,15,strikethroughFont);
 
cell.setCellValue(rts);
wb.write(fileOut);
fileOut.close();

[Thomas Greene]

Thanks a lot. It works great

[Thomas Greene]

Can you please help me develop the logic to apply it.

The String that needs to be set into excel would be something like

<i>italics </i>plain<b><i> boldItalics</i></b><b> allThree</b><b><i><u> plain</u></i></b>


Whatever is there inside
<i>..</i> should be set as italics
<b>..</b> should be set as bold
<u>..</u> should be set as underline

How can I traverse through this and set the appropriate style and also remove these HTML tags from the final output.

[Ulf Dittmer]

The logic seems straightforward. You'd go through the string left to right, one character at a time. You'd need to keep track of which styles are currently active (e.g. in 3 booleans), and where the current run of text starts. Whenever you encounter a closing tag, you'd append the current run of text to the rich text string, and apply a font based on the booleans to it.

A minor complication would be that you also need to keep track of whether the current run is the first run, in which case you'd change the cell style font instead of applying a font to the rich text string.

[Ulf Dittmer]

Little-known fun fact: the numbers of 400 for non-bold text and 700 for bold text are the same ones used in CSS style sheets. Go figure.

[Thomas Greene]

This is what I have done but it is pretty crude. Is there way to improve it

 public class Test1 
{
 
String str="three <i>italics </i><b><i> boldItalics</i></b><b><u> boldUnderline</u></b> plain";
 
HSSFWorkbook wb;
Map frmt=new TreeMap();
Map trym=new TreeMap();

void func()
{
String newStr=str;
newStr=newStr.replaceAll("<b>","");
newStr=newStr.replaceAll("</b>","");
newStr=newStr.replaceAll("<u>","");
newStr=newStr.replaceAll("</u>","");
newStr=newStr.replaceAll("<i>","");
newStr=newStr.replaceAll("</i>","");

 
System.out.println(str);
 try{
    File writeXcel=new File("C:/newOUT.xls");
FileOutputStream fileOut = new FileOutputStream(writeXcel);
wb=new HSSFWorkbook();
HSSFSheet sheet=wb.createSheet("Sheet 1");
HSSFRow row=sheet.createRow(0);
HSSFCell cell=row.createCell((short)0);
HSSFRichTextString rts=new HSSFRichTextString(newStr);

while(str.indexOf("<b><i><u>")!=-1)
{
double weight=0.7;
int start=str.indexOf("<b><i><u>");
int end=str.indexOf("</u></i></b>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+9,end);
String part3=str.substring(end+12);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<b><i>")!=-1)
{
double weight=0.6;

int start=str.indexOf("<b><i>");
int end=str.indexOf("</i></b>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+6,end);
String part3=str.substring(end+8);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<b><u>")!=-1)
{
double weight=0.5;

int start=str.indexOf("<b><u>");
int end=str.indexOf("</u></b>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+6,end);
String part3=str.substring(end+8);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<i><u>")!=-1)
{
double weight=0.3;

int start=str.indexOf("<i><u>");
int end=str.indexOf("</u></i>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+6,end);
String part3=str.substring(end+8);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<b>")!=-1)
{
double weight=0.4;

int start=str.indexOf("<b>");
int end=str.indexOf("</b>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+3,end);
String part3=str.substring(end+4);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);
 
Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<u>")!=-1)
{
double weight=0.1;

int start=str.indexOf("<u>");
int end=str.indexOf("</u>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+3,end);
String part3=str.substring(end+4);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}

while(str.indexOf("<i>")!=-1)
{
double weight=0.2;

int start=str.indexOf("<i>");
int end=str.indexOf("</i>",start);

String part1=str.substring(0,start);
String part2=str.substring(start+3,end);
String part3=str.substring(end+4);
str=part1+part2+part3;
int first=getIndex(start);
int last=first+part2.length();
System.out.println("From "+first+ "to "+last);
System.out.println(str);

Object key=new Integer(first);
Object value=new Double(last+weight);
frmt.put(key,value);
}
System.out.println(frmt);
Set s=frmt.entrySet();
Iterator it=s.iterator();
while(it.hasNext())
{
Map.Entry me=(Map.Entry) it.next();
int first=Integer.parseInt(me.getKey().toString());
double value=Double.parseDouble(me.getValue().toString());
int last=(int) value;
double fr=(value*10)%10;
int format=(int) fr;

System.out.println(first+" "+last+" "+format);
HSSFFont font;
switch(format)
{
case 7: font=wb.createFont();
font.setUnderline(HSSFFont.U_SINGLE);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setItalic(true);
rts.applyFont(first,last,font);
break;
case 6: font=wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setItalic(true);
rts.applyFont(first,last,font);
break;
case 5: font=wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setUnderline(HSSFFont.U_SINGLE);
rts.applyFont(first,last,font);
break;
case 4: font=wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
rts.applyFont(first,last,font);
break;
case 3: font=wb.createFont();
font.setItalic(true);
font.setUnderline(HSSFFont.U_SINGLE);
rts.applyFont(first,last,font);
break;
case 2: font=wb.createFont();
font.setItalic(true);
rts.applyFont(first,last,font);
break;
case 1: font=wb.createFont();
font.setUnderline(HSSFFont.U_SINGLE);
rts.applyFont(first,last,font);
break;
}
}
cell.setCellValue(rts);


wb.write(fileOut);
fileOut.close();
 }catch(Exception e)
 {
 e.printStackTrace();
 }
 
}

int getIndex(int i)
{
int finali=0,ttags=0;
String st=str.substring(0,i);

while(st.indexOf("<u>")!=-1 )
{

ttags++;
int start=st.indexOf("<u>");
int end=st.indexOf("</u>",start);

String part1=st.substring(0,start);
String part2=st.substring(start+3,end);
String part3=st.substring(end+4);
st=part1+part2+part3;
}
while(st.indexOf("<i>")!=-1 )
{

ttags++;
int start=st.indexOf("<i>");
int end=st.indexOf("</i>",start);

String part1=st.substring(0,start);
String part2=st.substring(start+3,end);
String part3=st.substring(end+4);
st=part1+part2+part3;

}
while(st.indexOf("<b>")!=-1 )
{

ttags++;
int start=st.indexOf("<b>");
int end=st.indexOf("</b>",start);

String part1=st.substring(0,start);
String part2=st.substring(start+3,end);
String part3=st.substring(end+4);
st=part1+part2+part3;
}
System.out.println("i="+i);
System.out.println("tags:"+ttags);
finali=i-ttags*7;
System.out.println("final pos:"+finali);
return finali;
}



public static void main(String[] args) 
{
Test1 hc=new Test1();
hc.func();
 
}
 
}
   

[Ulf Dittmer]

Didn't you like the approach I outlined before? It seems that it would need a lot fewer lines of code, and also be more stable (e.g. if there was whitespace between the HTML tags).

[Thomas Greene]

Originally posted by Ulf Dittmer:
Didn't you like the approach I outlined before? It seems that it would need a lot fewer lines of code, and also be more stable (e.g. if there was whitespace between the HTML tags).

I liked it but couldn't implement it.

[Thomas Greene]

Originally posted by Thomas Greene:


I liked it but couldn't implement it.

My code will not work for something like

  <b>one <i>two</i> three</b>  

So its actually useless.

[Ulf Dittmer]

My approach would be usable in that case too, and even in situations of a malformed tag structure like:

<b>one <i>two</b> three</i>

What specific difficulty did you encounter implementing it?