Apache POI Insert Line Break In Excel Cell Data

Introduction

Here I am going to show you how to insert line break in excel cell data using Apache POI in Java language. There may be requirements where you need to write long text in a cell or in multiple cells of excel file, but the long text is not fit within the view port or screen of the device; it also looks very ugly and difficult to read the text by scrolling to the right side.

While you are writing in the excel file manually then you can easily insert new line in the column to break the long text into multi-line texts. But in this example I am using Apache POI library to insert new line in the column data to break the line into multiple lines of text.

Prerequisites

Java 1.8+, Apache POI 5.2.2, Maven 3.8.5

Project Setup

I am creating a maven based project with the following pom.xml configurations:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>apache-poi-excel-cells-newlines</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.2.2</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>
		</plugins>
	</build>
</project>

Insert New Line in Cell Data

Now I am going to insert new line in the cell data to fit the text in a column. I will also adjust the height of the row in the excel cell, so that the multi-line data will be visible without changing the height of the row while reading the cell data. I am also going to expand or shrink the cell width to fit the text or data length.

package com.roytuts.apachepoi.excel.cells.newlines;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCellsNewLinesApp {

	public static void main(String[] args) throws FileNotFoundException, IOException {
		Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

		Sheet sheet = wb.createSheet();

		Row row = sheet.createRow(2);

		Cell cell = row.createCell(2);
		cell.setCellValue("This is first line. \n This is second line. \n This is third line.");

		// to enable newlines you need set a cell styles with wrap=true
		CellStyle cs = wb.createCellStyle();
		cs.setWrapText(true);
		cell.setCellStyle(cs);

		// increase row height to accommodate three lines of text
		row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints()));

		// adjust column width to fit the content
		sheet.autoSizeColumn(2);

		try (OutputStream fileOut = new FileOutputStream("excel-newlines.xlsx")) {
			wb.write(fileOut);
		}

		wb.close();
	}

}

In the above Java code, I have created a row instance at 3rd position and cell instance at 3rd position. I am writing text into the cell with new line (\n). So, each text broken by \n will be written on a new line.

To enable new line or insert a line break you need to set setWrapText(true) to CellStyle instance.

To adjust the height of the row you need to set the following to the row instance. I have multiplied by 3 because I have written 3 lines of text in a cell.

setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints()))

You need to set autoSizeColumn(2) to sheet instance for auto sizing the column width.

Finally I have written the text to an excel file.

Testing Line Break in Cell

Once you execute the above program, an excel file will be created under the root folder of the project with the following output:

newline in excel cell data

Source Code

Download

Leave a Reply

Your email address will not be published.