How to create cell with multiple styles in excel using HSSFSheet Apache POI?

Question

I am creating a script for export document as excel.

How to have cell value like "Name: Mark DOB: 11-11-2014" by merging few cells?

Answer 1

I have created a short complete example for this.

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;


class RichTextTest {

 public static void main(String[] args) {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("Sheet1");

  Row row = sheet.createRow(0);

  Cell cell = row.createCell(0);

  RichTextString richString = new XSSFRichTextString( "Name: Mark DOB: 11-11-2014" );
                                                     //^0  ^4     ^11^14
  Font fontBold = wb.createFont();
  //fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);
  fontBold.setBold(true);

  richString.applyFont( 0, 4, fontBold );
  richString.applyFont( 11, 14, fontBold );
  cell.setCellValue(richString);


  try {
   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);
   fileOut.flush();
   fileOut.close();
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }

 }
}

For further reading see documentation.

How to create workbook, sheet and cells: http://poi.apache.org/spreadsheet/quick-guide.html#CreateCells

How to use Richtext: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRichTextString.html

The Font interface: https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html

Answer 2

Have you tried using JXLS ?

Using xls templates you can read and write data from Excel. Its very simple to use.

Answer 3

What you need to do is create a RichTextString for your cell. That's the way of applying different formatting / styles to different parts of the same cell for display in Excel

You'll want to review the POI "Working With Rich Text" example for more on how to use it, but broadly it'll be something like

    Cell cell = row.createCell(1);
    RichTextString rt = new XSSFRichTextString("The quick brown fox");

    Font font1 = wb.createFont();
    font1.setBoldWeight(Font.BOLDWEIGHT_BOLD);
    rt.applyFont(0, 10, font1);

    Font font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    rt.applyFont(10, 19, font2);

    Font font3 = wb.createFont();
    font3.setBoldWeight(Font.BOLDWEIGHT_NORMAL);
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

That should give you a cell with a mixture of bold, italic+underline and normal