HTML Formatted Cell value to Excel using Apache POI 1

Question

I want to display the Html Formatted value in to Excel cell. the html tag contains unordered, ordered and bullets, italic and underline and paragraph.

I want to pass the html markup tag to something which shows the formatted value in apache poi excel's cell value.

XSSFWorkbook workBook = null; 
String value = "<html>My String</html>"; 
workBook = new XSSFWorkbook (); 
XSSFCell cell = row.createCell(columnIndex); 
XSSFRichTextString textString = new XSSFRichTextString (value); 
cell.setCellValue(value);

Input data:

   <ul>
    <li> Iam <strong>Bold </strong> and Iam <u>Underlined </u> </li>
    <li> Just a Normal Text </li>
    <li> Iam <b> Bold </b> <em>and italic </em> <u> and underlined </u> </li>
  </ul>

Expected output:

. Iam Bold and Iam Italic
. Iam Bold and Iam Italic and Underlined
. Just a normal text

Any api provides that, Aspose or Itext or Some other api.

possible duplicate of [Apache POI Excel text formatting through XSSFRichTexString and Jsoup](http://stackoverflow.com/questions/24613916/apache-poi-excel-text-formatting-through-xssfrichtexstring-and-jsoup) — Mirco, Jul 09 '14 at 09:00

score 0 · Answer 1 · answered Jul 09 '14 at 10:06

I am working as Social Media Developer at Aspose and your desired result can be achieved with ease using Aspose.Cells for Java. Check out the following code.

//Instantiate the workbook object                                                                       
Workbook workbook = new Workbook();                                                                     

//Get first worksheet of workbook                                                                       
Worksheet worksheet1 = workbook.getWorksheets().get(0);                                                 

//Get Cells in a Worksheet                                                                              
Cells cells = worksheet1.getCells();                                                                    

//Get the individual cell                                                                               
Cell cell = cells.get("A1");                                                                            

String htmlstring = "<ul><li> I am <strong>Bold </strong> and Iam <u>Underlined </u> " +                
        "</li><li> Just a Normal Text </li>" +                                                          
"<li> Iam <b> Bold </b> <i>and italic </i> <u> and underlined </u></li></ul>";                          

//Setting Cell Style                                                                                    
Style style = new Style();                                                                              
style.setHorizontalAlignment(TextAlignmentType.LEFT);                                                   
style.setTextWrapped(true);                                                                             
cell.setStyle(style);                                                                                   

//Set HTML string as Cell Text                                                                          
cell.setHtmlString(htmlstring);                                                                         

worksheet1.autoFitColumns();                                                                            
worksheet1.autoFitRows();                                                                               

//Save the workbook.                                                                                    
workbook.save("c:\\HtmlStringtest.xlsx");

Okie.. How can i get the Aspose.cells for java. Is that a licensed or opensource. If it's licensed before getting it i wanna test whether the above code will work for my requirement or not. So for testing purpose how can i get aspose.cells. can i get the aspose.cells jar for testing purpsose — LazyGuy, Jul 09 '14 at 10:24
Aspose.Cells for Java is licensed API. You can download a free trail version and test your desired features using the link http://goo.gl/Hhrwkd — Nausherwan Aslam, Jul 09 '14 at 10:46

score -2 · Answer 2 · answered Jul 09 '14 at 08:18

-2

You can format a String in POI by creating and setting an XSSFFont. https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html There you can use setBold(true) and so on.

You just have to apply the font to your XSSFRichTextString. You can also apply the font just to parts of the string.

XSSFWorkbook wb=new XSSFWorkbook();
XSSFFont font = wb.createFont();
font.setBold( true );
font.setItalic( true );
new XSSFRichTextString().applyFont( font );

Using HTML in the string seems to be impossible

answered Jul 09 '14 at 08:18

Mirco

2,940
5
34
57

No verbose.. in a total text, a subtext can contain bold, other part can contain italic, ..underline etc... the html tag will look something like this., plz see the updated question – LazyGuy Jul 09 '14 at 08:27
Well whats the difference? This will make your whole text in the String bold and italic. List etc. from html seem not to be possible with POI – Mirco Jul 09 '14 at 08:31
I am not looking for whole text to be bold and italic.. parts of the string and that too varies and inside li item . How can u pass it to XSSFRichTextString's applyFont(startIndex, endIndex, font) method without knowing the exact index at where the text is bold and italic or underlined or both or multiple. the tag can vary.. so that was the problem. if it is for complete text , we can do it. – LazyGuy Jul 09 '14 at 08:36
You could search for all the etc in the text, remember their positions and then set it – Mirco Jul 09 '14 at 08:37
that was the question,... How can i do that? remembering their positiins varies,right. can u provide me a solution for the html input data mentioned in the question. so that i can start from there for other tags – LazyGuy Jul 09 '14 at 08:41
Take a look her http://stackoverflow.com/questions/3052052/how-to-find-if-string-contains-html-data – Mirco Jul 09 '14 at 08:45
@Verbose... finding the string is not a problem at all..u can get the text from html by using any jsoup or other parser..later how the different parts of text are to be formed and get the specific positon as bold, italic and underline – LazyGuy Jul 09 '14 at 08:49
the solution was provide by syam but without bullet points. I want to check without explicitly written by us..is there any api which takes html tag and gives a formatted output so that to send it to excel http://stackoverflow.com/questions/24613916/apache-poi-excel-text-formatting-through-xssfrichtexstring-and-jsoup/24639004#24639004 – LazyGuy Jul 09 '14 at 08:52
Why are you posting this twice? – Mirco Jul 09 '14 at 09:00
I Just want to check other ways than implementing it myself by writing the code provided by syam..! So that i can reduce the code lines or code complexity – LazyGuy Jul 09 '14 at 09:04

HTML Formatted Cell value to Excel using Apache POI 1

2 Answers2