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

Answer 2

What your screenshot shows is a gridline and not a border line. That's a difference in spreadsheets. The gridlines are shown in application window only to see the cells better. They will not be printed.

If you dont want to see the gridlines you either could switching to not showing gridlines for the whole sheet, what i do not recommend, or you could set white border lines which will overpaint some of the gridlines then.

Since you have tagged apache-poi-4 I will show a complete example which uses the advanced methods of CellUtil and PropertyTemplate to produce what you seems to want.

Code:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelLeftRight {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   //create font with bigger size
   Font font = workbook.createFont();
   font.setFontHeightInPoints((short)24);

   Sheet sheet = workbook.createSheet(); 

   //merge A1:E2
   sheet.addMergedRegion(new CellRangeAddress(
    0, //first row (0-based)
    1, //last row  (0-based)
    0, //first column (0-based)
    4  //last column  (0-based)
   ));

   //merge F1:H2
   sheet.addMergedRegion(new CellRangeAddress(
    0, //first row (0-based)
    1, //last row  (0-based)
    5, //first column (0-based)
    7  //last column  (0-based)
   ));

   //create row 1
   Row row = sheet.createRow(0);
   //create cell A1
   Cell cell = row.createCell(0);
   cell.setCellValue("LEFT");
   CellUtil.setFont(cell, font);
   CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
   //create cell F1
   cell = row.createCell(5);
   cell.setCellValue("RIGHT");
   CellUtil.setFont(cell, font);
   CellUtil.setVerticalAlignment(cell, VerticalAlignment.CENTER);
   CellUtil.setAlignment(cell, HorizontalAlignment.RIGHT);
 
   PropertyTemplate propertyTemplate = new PropertyTemplate();
   //paint all inside borders white on A1:H2
   propertyTemplate.drawBorders(new CellRangeAddress(0, 1, 0, 7), 
    BorderStyle.THIN, IndexedColors.WHITE.getIndex(), BorderExtent.INSIDE);
   //paint all bottom borders thick gray on A2:H2
   propertyTemplate.drawBorders(new CellRangeAddress(1, 1, 0, 7), 
    BorderStyle.THICK, IndexedColors.GREY_40_PERCENT.getIndex(), BorderExtent.BOTTOM);
   propertyTemplate.applyBorders(sheet);

   sheet.setActiveCell(new CellAddress(3, 0));

   workbook.write(fileout);
  }

 }
}

Result: