Apache POI cell with multiple lines with different styles

Question

I am trying to achieve something like this:

As you can see, there are cells with just 1 line such as name or 112233:

Also, there are cells with multiple lines which every line could have different style (hyper link, background color, plain text) is this possible with apache poi library for java? couldn't find anything relevant.

I'm using java 8 with apache poi 4.1.2. Currently managed to create excel with each row to have 1 cell in every column with specific type.

try (Workbook workbook = new XSSFWorkbook()) {

        Sheet sheet = workbook.createSheet("sheet");
        Row header = sheet.createRow(0);

        CellStyle hlinkStyle = workbook.createCellStyle();
        CellStyle cellStyle = workbook.createCellStyle();
        CellStyle dateStyle = workbook.createCellStyle();
        CellStyle numberStyle = workbook.createCellStyle();
        CellStyle rowStyle = workbook.createCellStyle();
        rowStyle.setWrapText(true);
        setHeaderAndStyles(workbook, hlinkStyle, dateStyle, numberStyle, cellStyle);
        createHeaders(workbook, sheet, header, null);

        int[] rowNum = {1};
        data.forEach(ele -> {

            Row row = sheet.createRow(rowNum[0]);
            row.setRowStyle(rowStyle);
            int cellIdx = 0;

            // name
            cell = row.createCell(cellIdx++);
            cell.setCellValue(ele.getName());
            cell.setCellStyle(cellStyle);

            // ID
            cell = row.createCell(cellIdx++);
            cell.setCellValue(ele.getId());
            cell.setCellStyle(cellStyle);

            // hyper link
            cell = row.createCell(cellIdx++);
            Hyperlink link = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
            link.setAddress(CONST_URL);
            cell.setCellValue(ele.getHyperLinkText());
            cell.setHyperlink(link);
            cell.setCellStyle(hlinkStyle);
            // need to create more cells here with background color

            // desc
            cell = row.createCell(cellIdx);
            cell.setCellValue(ele.getDesc());
            cell.setCellStyle(cellStyle);
            row.getCell(cellIdx).setCellStyle(rowStyle); // for cells with multiple lines.
            // need to create more cells here with plain text

            rowNum[0]++;
        });
}

Thanks

Answer 1

(1) The code you're asking for

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
    
// Creating all needed cellStyles
    
XSSFCellStyle noBottomGridLine = workbook.createCellStyle();
noBottomGridLine.setBorderBottom(BorderStyle.THIN);
noBottomGridLine.setBottomBorderColor(IndexedColors.WHITE.index);
XSSFCellStyle noTopGridLine = workbook.createCellStyle();
noTopGridLine.setBorderTop(BorderStyle.THIN);
noTopGridLine.setTopBorderColor(IndexedColors.WHITE.index);
XSSFCellStyle noVerticalGridLine = workbook.createCellStyle();
noVerticalGridLine.setBorderTop(BorderStyle.THIN);
noVerticalGridLine.setTopBorderColor(IndexedColors.WHITE.index);
noVerticalGridLine.setBorderBottom(BorderStyle.THIN);
noVerticalGridLine.setBottomBorderColor(IndexedColors.WHITE.index);
XSSFFont hlinkFont = workbook.createFont();
hlinkFont.setUnderline(Font.U_SINGLE);
hlinkFont.setColor(IndexedColors.BLUE.getIndex());
XSSFCellStyle hlinkStyle = workbook.createCellStyle();
hlinkStyle.setFont(hlinkFont);
XSSFCellStyle coloredStyle = workbook.createCellStyle();
coloredStyle.setFillForegroundColor(IndexedColors.RED.index);
coloredStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFCellStyle alignedText = workbook.createCellStyle();
alignedText.setVerticalAlignment(VerticalAlignment.TOP);

// Creating merged regions for columns one and two

sheet.addMergedRegion(new CellRangeAddress(0, 5, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(0, 5, 1, 1));

// Removing gridlines from columns three four and five

for (int i = 2 ; i < 5 ; ++i) {
    for (int j = 0 ; j < 6 ; ++j) {
        XSSFRow r = sheet.getRow(j);
        if (r == null) r = sheet.createRow(j);
        if (j == 0) {
            r.createCell(i, CellType.STRING).setCellStyle(noBottomGridLine);
        } else if (j == 5){
            r.createCell(i, CellType.STRING).setCellStyle(noTopGridLine);
        } else {
            r.createCell(i, CellType.STRING).setCellStyle(noVerticalGridLine);
        }
    }
}
    
// Resizing columns
for (int j = 0 ; j < 6 ; ++j) sheet.setColumnWidth(j, 125*50);
    
// First column
    
sheet.getRow(0).createCell(0, CellType.STRING).setCellValue("Name");
sheet.getRow(0).getCell(0).setCellStyle(alignedText);
    
// Second column
sheet.getRow(0).createCell(1, CellType.NUMERIC).setCellValue("112233");
sheet.getRow(0).getCell(1).setCellStyle(alignedText);
    
// Third column
XSSFCell hyperlinkCell = sheet.getRow(0).getCell(2);
hyperlinkCell.setCellValue("hyperlink text");
XSSFHyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://www.youtube.com/watch?v=mI_y8h22c_o");
hyperlinkCell.setHyperlink(hyperlink);
hyperlinkCell.setCellStyle(hlinkStyle);
XSSFCell backGroundColoredCell = sheet.getRow(2).getCell(2);
backGroundColoredCell.setCellValue("Colored Cell");
backGroundColoredCell.setCellStyle(coloredStyle);
    
// Fourth column
sheet.getRow(0).getCell(3).setCellValue("Team");
sheet.getRow(1).getCell(3).setCellValue("Title");
sheet.getRow(2).getCell(3).setCellValue("New Team");
sheet.getRow(3).getCell(3).setCellValue("New Team");
    
// Fifth column
sheet.getRow(0).getCell(4).setCellValue("Full Team description");
sheet.getRow(1).getCell(4).setCellValue("Full Title Description");
sheet.getRow(2).getCell(4).setCellValue("Origin");
sheet.getRow(3).getCell(4).setCellValue("Code");
            
// Saving Workbook
    
FileOutputStream outputStream = new FileOutputStream("D:\\Desktop\\test-excel.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();

Output:

(2) A short guide about XSSFCells customization

1. Formatted text inside cells
2. Colored text inside cells
3. Colored cells' background
4. Hyperlinks
5. Merging a group of cells into a single one
6. Removing cells' gridlines (a little trick)

When using .xlsx excel file (which means using XSSF Apache Poi Objects, have a look at this post for differences between XSSF and HSSF Apache Poi Objects) you can use XSSFRichTextString in order to fill an XSSFCell with some formatted text. With XSSFRichTextString you can both append new text which is associated with a custom XSSFFont and use \n character to start a new line (if you've called CellStyle#setWrapTest(true) on the XSSFCellStyle of your XSSFCell as is discussed here).

Here it is an example:

XSSFRichTextString richValue = new XSSFRichTextString();
            
XSSFFont italicBoldFont = workbook.createFont();
italicBoldFont.setBold(true);
italicBoldFont.setItalic(true);
XSSFFont onlyBoldFont = workbook.createFont();
onlyBoldFont.setBold(true);
            
richValue.append("First text", italicBoldFont);
richValue.append("\nSecond text", onlyBoldFont);
            
XSSFCell cell = workbook.getSheetAt(0).createRow(0).createCell(0, CellType.STRING);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
cell.setCellValue(richValue);

---

Using XSSFColor in order to change the text color may be a little more tricky (some useful hints here and here), here it is an example:

XSSFRichTextString richValue = new XSSFRichTextString();

XSSFFont coloredFont = workbook.createFont();
coloredFont.setColor(IndexedColors.BLUE1.index);
            
richValue.append("Colored text", coloredFont);
            
workbook.getSheetAt(0).createRow(0).createCell(0, CellType.STRING).setCellValue(richValue);

---

If you want to change the color background, you can only change the color background of an entire XSSFCell. In order to do that use a custom XSSFCellStyle as it's shown here. Here it is an example:

XSSFCell cell = workbook.getSheetAt(0).createRow(0).createCell(0, CellType.STRING);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.DARK_RED.index);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle);
cell.setCellValue("Some text");

---

In order to add an hyperlink, the only way is to create a XSSFCell which contains only that hyperlink. You have to use the XSSFHyperlink Java Object and the XSSFCell#setHyperlink(Hyperlink hyperlink) method. Here it is an example:

XSSFHyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://www.youtube.com/watch?v=mI_y8h22c_o");
            
XSSFCell cell = workbook.getSheetAt(0).createRow(0).createCell(0, CellType.STRING);
cell.setHyperlink(hyperlink);
cell.setCellValue("Click here to open cute video");

If you want an highlighted hyperlink:

XSSFHyperlink hyperlink = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://www.youtube.com/watch?v=mI_y8h22c_o");
            
XSSFCellStyle hlinkStyle = workbook.createCellStyle();
XSSFFont hlinkFont = workbook.createFont();
hlinkFont.setUnderline(Font.U_SINGLE);
hlinkFont.setColor(IndexedColors.BLUE.getIndex());
hlinkStyle.setFont(hlinkFont);
            
XSSFCell cell = workbook.getSheetAt(0).createRow(0).createCell(0, CellType.STRING);
cell.setHyperlink(hyperlink);
cell.setCellValue("Click here to open cute video");
cell.setCellStyle(hlinkStyle);

---

If you want to merge some XSSFCell toghether into a single one, use the method XSSFSheet#addMergedRegion(CellRangeAddress region) as follows

sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));

where CellRangeAddress constructor requires int firstRow, int lastRow, int firstCol, int lastCol.

---

If you want to remove all gridlines from your XSSFSheet, use XSSFSheet#setDisplayGridlines(boolean show). If you want to remove gridlines from a specific XSSFCell, you have to use a trick like that (take advantage of XSSFCellStyle once again)(this is the only possible way using Apache Poi):

XSSFCellStyle noUpGridlines = workbook.createCellStyle();
noUpGridlines.setBorderTop(BorderStyle.THIN);
noUpGridlines.setTopBorderColor(IndexedColors.WHITE.index);
            
XSSFCell cell = workbook.getSheetAt(0).createRow(60).createCell(1, CellType.STRING);
cell.setCellStyle(noUpGridlines);
cell.setCellValue("Oh no, where is my top gridline?");