Apache POI – converting xls to xlsx styling error

I am a junior software engineer who is new to this role. I am not sure if I’m asking a senseless question, so I will apologise here before I start.

I am currently working on a client project and was asked to create an application to upload Excel into the database. After reading up I decided to use Apache POI as I will be doing data mapping to Excel as well. Users are allowed to upload Excel in both xls and xlsx format, then in the springboot, I will convert the uploaded file format into xlsx regardless, so when other users download the Excel file, it will return xlsx format. I managed to do that but when it comes to styling it keeps giving me the styling error. and sometimes, the styling might not be exactly how it looks when I upload.

This is what I’ve tried so far

in my service:

private ByteArrayOutputStream convertWorkbookToXlsx(Workbook workbook) throws IOException {
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try (XSSFWorkbook xssfWorkbook = new XSSFWorkbook()) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet originalSheet = workbook.getSheetAt(i);
                Sheet newSheet = xssfWorkbook.createSheet(originalSheet.getSheetName());

                // for merged cells
                for (int j = 0; j < originalSheet.getNumMergedRegions(); j++) {
                    CellRangeAddress mergedRegion = originalSheet.getMergedRegion(j);
                    if (mergedRegion != null) {
                        newSheet.addMergedRegion(mergedRegion);
                    }
                }

                // copy column widths
                for (int colIndex = 0; colIndex < originalSheet.getRow(0).getLastCellNum(); colIndex++) {
                    newSheet.setColumnWidth(colIndex, originalSheet.getColumnWidth(colIndex));
                }

                // for each row and columns
                for (int j = 0; j <= originalSheet.getLastRowNum(); j++) {
                    Row originalRow = originalSheet.getRow(j);
                    Row newRow = newSheet.createRow(j);
                    if (originalRow != null) {
                        newRow.setHeight(originalRow.getHeight()); // copy row height
                        for (int k = 0; k < originalRow.getLastCellNum(); k++) {
                            Cell originalCell = originalRow.getCell(k);

                            if (originalCell != null) {
                                Cell newCell = newRow.createCell(k);
                                copyCellValue(originalCell, newCell);
                                CellStyleUtil.copyCellStyle(originalCell, newCell, xssfWorkbook);
                            }
                        }
                    }
                }
            }

            xssfWorkbook.write(byteArrayOutputStream);
        }
        return byteArrayOutputStream;
    }

    private void copyCellValue(Cell originalCell, Cell newCell) {
        // Copy cell value based on type
        switch (originalCell.getCellType()) {
            case STRING:
                newCell.setCellValue(originalCell.getStringCellValue());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(originalCell)) {
                    newCell.setCellValue(originalCell.getDateCellValue());
                } else {
                    newCell.setCellValue(originalCell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                newCell.setCellValue(originalCell.getBooleanCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(originalCell.getCellFormula());
                break;
            default:
                newCell.setCellValue(originalCell.toString());
                break;
        }
    }

and in my CellStyleUtil class:

public class CellStyleUtil {

    public static void copyCellStyle(Cell originalCell, Cell newCell, XSSFWorkbook targetWorkbook) {
        Workbook originalWorkbook = originalCell.getSheet().getWorkbook();

        // Create a new cell style in the target XSSFWorkbook
        XSSFCellStyle newCellStyle = targetWorkbook.createCellStyle();

        if (originalWorkbook instanceof XSSFWorkbook) {
            newCellStyle.cloneStyleFrom(originalCell.getCellStyle());
        } else if (originalWorkbook instanceof HSSFWorkbook) {

            // copy styles from HSSF to XSSF
            CellStyle originalCellStyle = originalCell.getCellStyle();
            newCellStyle.setDataFormat(originalCellStyle.getDataFormat());
            if (originalCellStyle != null) {
                newCellStyle.setAlignment(originalCellStyle.getAlignment());
                newCellStyle.setVerticalAlignment(originalCellStyle.getVerticalAlignment());

                newCellStyle.setFillForegroundColor(originalCellStyle.getFillForegroundColor());
                newCellStyle.setFillBackgroundColor(originalCellStyle.getFillBackgroundColor());
                newCellStyle.setFillPattern(originalCellStyle.getFillPattern());
                newCellStyle.setBorderTop(originalCellStyle.getBorderTop());
                newCellStyle.setBorderBottom(originalCellStyle.getBorderBottom());
                newCellStyle.setBorderLeft(originalCellStyle.getBorderLeft());
                newCellStyle.setBorderRight(originalCellStyle.getBorderRight());

                // fonts
                try {
                    Font originalFont = originalWorkbook.getFontAt(originalCellStyle.getFontIndexAsInt());
                    XSSFFont newFont = targetWorkbook.createFont();
                    newFont.setFontName(originalFont.getFontName());
                    newFont.setFontHeightInPoints(originalFont.getFontHeightInPoints());
                    newFont.setFontHeight(originalFont.getFontHeightInPoints());
                    newFont.setBold(originalFont.getBold());
                    newFont.setItalic(originalFont.getItalic());
                    newFont.setColor(originalFont.getColor());
                    newCellStyle.setFont(newFont);
                } catch (Exception e) {
                    System.out.println("Error copying font: " + e.getMessage());
                }
            }
        }
        newCell.setCellStyle(newCellStyle);
    }
}

I saw one post stating that putting these works, but that is not for my case:

newCellStyle.getCoreXf().unsetFontId();
newCellStyle.getCoreXf().unsetFillId();
newCellStyle.getCoreXf().unsetBorderId();

may I ask if anyone knows what could possibly be the issue? Otherwise, could there be another way to convert xls to xlsx retaining the data and styling?

thanks so much!

You need to sign in to view this answers