How to get all fonts of text have multiple fonts in the Cell Apache POI

Question

I'm coping cell to another workbook, but it's having multiple Fonts of text in the Cell. It doesn't apply all Fonts in the cell. How to get all Fonts an index of current Cell. I'm using apache POI 3.17 and Excel is .xls file

score 1 · Answer 1 · answered Dec 24 '21 at 07:54

What your picture shows is a cell in column G which has rich text cell content and a cell in column F which has the same text content but not in rich text format.

So your problem is not the cell style but the cell content. Seems you have copied only the text string but not the rich text string. Special font styles of rich text strings are not stored in the cell style but in the rich text itself.

So you need to get the RichTextString from the source cell using Cell.getRichStringCellValue and set this cell value to the target cell using Cell.setCellValue(RichTextString).

The following example shows methods to copy all possible cell contents. Note, the code is for apache poi 3.17, since you mentioned that version. For usage using current apache poi versions one need changing the code a little bit like is commented in the code.

import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
 
public class ReadCopyCellContents {
    
 static Object getCellValue(Cell cell, CellType type) {
  switch (type) {
   case STRING:
    return cell.getRichStringCellValue();
   case NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
     return cell.getDateCellValue();
    } else {
     return cell.getNumericCellValue();
    }
   case BOOLEAN:
    return cell.getBooleanCellValue();
   case FORMULA:
    //return getCellValue(cell, cell.getCachedFormulaResultType()); // apache poi 4 and 5
    return getCellValue(cell, cell.getCachedFormulaResultTypeEnum()); // apache poi 3.17
   case BLANK:
    return null;
   default:
    System.out.println("This should not occur.");
    return null;
  }
 }
    
 static void copyCellContentAndStyle(Cell sourceCell, Cell targetCell) {
  CellStyle cellStyle = sourceCell.getCellStyle();
  targetCell.setCellStyle(cellStyle);
  //Object cellValue = getCellValue(sourceCell, sourceCell.getCellType()); // apache poi 4 and 5
  Object cellValue = getCellValue(sourceCell, sourceCell.getCellTypeEnum()); // apache poi 3.17
  if (cellValue instanceof RichTextString) {
   targetCell.setCellValue((RichTextString)cellValue);
  } else  if (cellValue instanceof java.util.Date) {
   targetCell.setCellValue((java.util.Date)cellValue);  
  } else  if (cellValue instanceof Double) {
   targetCell.setCellValue((Double)cellValue);  
  } else  if (cellValue instanceof Boolean) {
   targetCell.setCellValue((Boolean)cellValue);  
  } else  if (cellValue == null) {
   //targetCell.setBlank(); // apache poi 4 and 5
   String result = null; targetCell.setCellValue(result); // apache poi 3.17
  }
 }
    
 public static void main( String args[] ) throws Exception {
  String inFilePath = "./ExcelExampleIn.xlsx"; String outFilePath = "./ExcelExampleOut.xlsx";
  //String inFilePath = "./ExcelExampleIn.xls"; String outFilePath = "./ExcelExampleOut.xls";

  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inFilePath));
       FileOutputStream out = new FileOutputStream(outFilePath ) ) {

   Sheet sheet = workbook.getSheetAt(0);
   
   for (int r = 0; r < 10; r++) {
       
    Row row = sheet.getRow(r); if (row == null) continue;
    Cell sourceCell = row.getCell(4); if (sourceCell == null) continue;
    Cell targetCell = row.getCell(5); if (targetCell == null) targetCell = row.createCell(5);
   
    copyCellContentAndStyle(sourceCell, targetCell);
    
   }
   
   workbook.write(out);   
  }   
 }
}

I'm trying copy cells style with RichTextString. But it doesn't work if you copy cell having 2 fonts size or underline with normal text to another workbook. I'm coding demo with https://coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel — Huy Nguyen, Dec 24 '21 at 14:34
@Huy Nguyen: My example only works in same workbook. It solves what your question was about. Copying a cell into another cell in same worksheet. Copying between different workbooks is a much more complex task and nearly impossible to solve because of the complex kind how rich text strings, styles and fonts are stored in the workbook. But good luck. — Axel Richter, Dec 24 '21 at 14:41