Excel Horizontal Vertical Cell Alignment - Java POI Example Program

Horizontal and Vertical Cell Alignment - Introduction


In this tutorial, we will discuss how to set horizontal and vertical alignment to Excel Cell contents in Java, using Apache POI library. We will explore the various alignment options available through POI, across XLS and XLSX formats. You can test all the example programs by yourself and select alignment options that are required for your development.


setAlignment and setVerticalAlignment


The methods setAlignment is used to define the horizontal alignment for contents inside a cell. On the same lines, the method setVerticalAlignment is used to set the vertical alignment for contents in a Excel Cell. You can use these two methods together, or separately depending on your requirements. When it comes to XLSX formats, these methods can take different inputs. We will discuss them with suitable examples as we move on.

XLS Format - Set Cell Alignment - Java Program Example


The Java program that sets different cell alignment (for handling .XLS format) is shown below:
import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
public class setCellAlignment {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Alignment);
                /* Get access to HSSFCellStyle */
                HSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_2 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_3 = my_workbook.createCellStyle();
                        
                /* Top Left alignment */
                /* Left aligned horizontally */
                my_style_0.setAlignment(HSSFCellStyle.ALIGN_LEFT);
                /* top aligned vertically */
                my_style_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
                
                /* Center Align Cell Contents */
                my_style_1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                my_style_1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                
                /* Bottom Right alignment */
                my_style_2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
                my_style_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
                
                /* Justified Alignment */
                my_style_3.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
                my_style_3.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);
                
                /* We will now attach these alignment options to cells */
                
                /* Attach border colors to a cell */
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue("Top Left");          
                cell.setCellStyle(my_style_0);
                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue("Center Aligned");            
                cell.setCellStyle(my_style_1);
                
                row = my_sheet.createRow(2);            
                cell = row.createCell(2);
                cell.setCellValue("Bottom Right");              
                cell.setCellStyle(my_style_2);
                
                row = my_sheet.createRow(3);            
                cell = row.createCell(3);
                cell.setCellValue("Contents are Justified in Alignment");               
                cell.setCellStyle(my_style_3);
                
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_alignment_example.xls"));
                my_workbook.write(out);
                out.close();
        }
}

The output of this program is shown in the screenshot below. As you can see, the alignment options we have specified are correctly applied.

XLS - Align Cell Contents - Vertical / Horizontal - Java POI Example - Output
XLS - Align Cell Contents - Vertical / Horizontal - Java POI Example - Output

XLSX - Excel Cell Alignment - Java Program Example


You can use the same methods when it comes to XLSX files. However, we would like to set alignments differently for XLSX files. We will use org.apache.poi.ss.usermodel.HorizontalAlignment to define the horizontal alignment options and org.apache.poi.ss.usermodel.VerticalAlignment to set the vertical alignment options. An Example program is provided below:

import java.io.*;
import org.apache.poi.xssf.usermodel.*; 
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
public class setCellAlignmentXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet XLSX Format */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Cell Alignment");
                /* Get access to XSSFCellStyle */
                XSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                XSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                XSSFCellStyle my_style_2 = my_workbook.createCellStyle();
                XSSFCellStyle my_style_3 = my_workbook.createCellStyle();
                
                /* Top Left alignment */
                /* Left aligned horizontally */
                my_style_0.setAlignment(HorizontalAlignment.LEFT);
                /* top aligned vertically */
                my_style_0.setVerticalAlignment(VerticalAlignment.TOP);
                
                /* Center Align Cell Contents */
                my_style_1.setAlignment(HorizontalAlignment.CENTER);
                my_style_1.setVerticalAlignment(VerticalAlignment.CENTER);
                
                /* Bottom Right alignment */
                my_style_2.setAlignment(HorizontalAlignment.RIGHT);
                my_style_2.setVerticalAlignment(VerticalAlignment.BOTTOM);
                
                /* Justified Alignment */
                my_style_3.setAlignment(HorizontalAlignment.JUSTIFY);
                my_style_3.setVerticalAlignment(VerticalAlignment.JUSTIFY);
                
                /* Attach style to XLSX sheet */
                
                Row row = my_sheet.createRow(0);
                Cell cell = row.createCell(0);
                cell.setCellValue("Top Left");
                cell.setCellStyle(my_style_0);
                
                row = my_sheet.createRow(1);
                cell = row.createCell(1);
                cell.setCellValue("Center");
                cell.setCellStyle(my_style_1);
                
                row = my_sheet.createRow(2);
                cell = row.createCell(2);
                cell.setCellValue("Bottom Right");
                cell.setCellStyle(my_style_2);
                
                row = my_sheet.createRow(3);
                cell = row.createCell(3);
                cell.setCellValue("Fully Justified Alignment");
                cell.setCellStyle(my_style_3);
                
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_alignment.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

Example output below. Note that you can play with these options to create any alignment of your choice..

XLSX - Cell Alignment - Vertical / Horizontal - Java POI Example - Output
XLSX - Cell Alignment - Vertical / Horizontal - Java POI Example - Output

See you in a different formatting tutorial next time. If you have any questions meanwhile, you can post it in the comments section.

No comments:

Post a Comment