You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by thomas908 <an...@hcl.in> on 2007/08/13 20:11:00 UTC
Set character formatting in Excel using POI
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
italics plain boldItalics allThree<u> plain</u>
Whatever is there inside
.. should be set as italics
.. 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="italics plain boldItalics allThree<u> plain</u>";
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
--
View this message in context: http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262750.html#a12131092
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Set character formatting in Excel using POI
Posted by thomas908 <an...@hcl.in>.
Thanks for replying.
But this'll set the format for the whole cell. In my case format for
different words in the cell may be different.
Lee, Jun S. wrote:
>
> I'm very new to POI but when I was looking through the API,
> I saw you can do:
>
> HSSFCell.getHSSFCellStyle() and HSSFCellStyle class has setFont.
> That looks like changing style for a single cell.
>
> Best,
> Jun
>
>
>
> -----Original Message-----
> From: thomas908 [mailto:anshulkayastha@hcl.in]
> Sent: Monday, August 13, 2007 1:11 PM
> To: user@poi.apache.org
> Subject: Set character formatting in Excel using POI
>
>
> 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
>
> italics plain boldItalics allThree<u> plain</u>
>
>
> Whatever is there inside
> .. should be set as italics
> .. 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="italics plain boldItalics allThree<u> plain</u>";
>
> 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
> --
> View this message in context:
> http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262
> 750.html#a12131092
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> ============================================================
> The information contained in this message may be privileged
> and confidential and protected from disclosure. If the reader
> of this message is not the intended recipient, or an employee
> or agent responsible for delivering this message to the
> intended recipient, you are hereby notified that any reproduction,
> dissemination or distribution of this communication is strictly
> prohibited. If you have received this communication in error,
> please notify us immediately by replying to the message and
> deleting it from your computer. Thank you. Tellabs
> ============================================================
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262750.html#a12131831
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Set character formatting in Excel using POI
Posted by "Lee, Jun S." <Ju...@tellabs.com>.
I'm very new to POI but when I was looking through the API,
I saw you can do:
HSSFCell.getHSSFCellStyle() and HSSFCellStyle class has setFont.
That looks like changing style for a single cell.
Best,
Jun
-----Original Message-----
From: thomas908 [mailto:anshulkayastha@hcl.in]
Sent: Monday, August 13, 2007 1:11 PM
To: user@poi.apache.org
Subject: Set character formatting in Excel using POI
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
italics plain boldItalics allThree<u> plain</u>
Whatever is there inside
.. should be set as italics
.. 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="italics plain boldItalics allThree<u> plain</u>";
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
--
View this message in context:
http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262
750.html#a12131092
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set character formatting in Excel using POI
Posted by thomas908 <an...@hcl.in>.
Now I have code to set the formatting. Can someone please help me develop the
logic to apply it.
The String that needs to be set into excel would be something like
italics plain boldItalics allThree<u> plain</u>
Whatever is there inside
.. should be set as italics
.. 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.
--
View this message in context: http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262750.html#a12133741
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Set character formatting in Excel using POI
Posted by thomas908 <an...@hcl.in>.
This code works fine
File writeXcel = new File("C:/newOUT.xls");
FileOutputStream fileOut = new
FileOutputStream(writeXcel);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet 1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell((short)1);
HSSFRichTextString rts = new HSSFRichTextString("bbb iii
uuu");
HSSFCellStyle style = cell.getCellStyle();
int fontIdx = style.getFontIndex();
HSSFFont font = wb.getFontAt((short)fontIdx);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFFont italicFont = wb.createFont();
italicFont.setItalic(true);
rts.applyFont(4,7,italicFont);
HSSFFont underlinedFont = wb.createFont();
underlinedFont.setUnderline(HSSFFont.U_SINGLE);
rts.applyFont(8,11,underlinedFont);
cell.setCellValue(rts);
wb.write(fileOut);
fileOut.close();
--
View this message in context: http://www.nabble.com/Set-character-formatting-in-Excel-using-POI-tf4262750.html#a12133731
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org