Apache POI, align text to left and other text to right in same row

Question

I'm using Apache POI to create excel export file (.xlsx with XSSF). I'm having one issue with borders between cells.

I need to have few cells merged into one row and in that row I need to have one text aligned to the left, and another text aligned to the right, something like:

but without that border in between.

To get what you see in the image I used two merged areas, in one I aligned text to the left and in the other one I aligned text to the right, I'm not sure if there is a better/more convenient way to do this or not, if you know it please write it in the answer, but for my approach right now the issue is with that border, can I remove it? I tried setting right border for first merged area to NONE and setting left border for the second merged area to NONE as well, but it doesn't work.

How should I handle this?

Answer 1

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class Test {

    public static void main(String[] args) throws Exception {
        try(Workbook wb = new XSSFWorkbook(); OutputStream fos = new FileOutputStream("test.xlsx")){
            Sheet sheet = wb.createSheet();

            Font font = wb.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short)11);

            CellRangeAddress leftCellRangeAddress = new CellRangeAddress(
                0,
                1,
                CellReference.convertColStringToIndex("A"),
                CellReference.convertColStringToIndex("E")
            );
            sheet.addMergedRegion(leftCellRangeAddress);
            CellRangeAddress rightCellRangeAddress = new CellRangeAddress(
                0,
                1,
                CellReference.convertColStringToIndex("F"),
                CellReference.convertColStringToIndex("H")
            );
            sheet.addMergedRegion(rightCellRangeAddress);
            Row row = sheet.createRow(0);

            Cell leftCell = row.createCell(CellReference.convertColStringToIndex("A"));
            leftCell.setCellValue("LEFT");
            leftCell.getCellStyle().setFont(font);
            CellUtil.setVerticalAlignment(leftCell, VerticalAlignment.CENTER);
            CellUtil.setAlignment(leftCell, HorizontalAlignment.LEFT);
            RegionUtil.setBorderRight(BorderStyle.THIN, leftCellRangeAddress, sheet);
            RegionUtil.setRightBorderColor(IndexedColors.WHITE.getIndex(), leftCellRangeAddress, sheet);

            Cell rightCell = row.createCell(CellReference.convertColStringToIndex("F"));
            rightCell.setCellValue("RIGHT");
            rightCell.getCellStyle().setFont(font);
            CellUtil.setVerticalAlignment(rightCell, VerticalAlignment.CENTER);
            CellUtil.setAlignment(rightCell, HorizontalAlignment.RIGHT);
            RegionUtil.setBorderLeft(BorderStyle.THIN, rightCellRangeAddress, sheet);
            RegionUtil.setLeftBorderColor(IndexedColors.WHITE.getIndex(), rightCellRangeAddress, sheet);

            wb.write(fos);
        }
    }
}

If you want the grey border on the bottom you can add

CellRangeAddress firstRowRegion = new CellRangeAddress(
    0,
    1,
    CellReference.convertColStringToIndex("A"),
    CellReference.convertColStringToIndex("H")
);
RegionUtil.setBorderBottom(BorderStyle.THICK, firstRowRegion, sheet);
RegionUtil.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex(), firstRowRegion, sheet);

and you'll get