Fit Excel Sheet to One Page using Apache POI in Java

Introduction

We will create an example where we fit excel sheet to one page using Apache POI in Java. Apache POI is a wonderful library for manipulating data into office documents, such as, excel, ppt, word etc. We will fit excel sheet to one page for the existing excel files – an excel file that contains only text as well as an excel file that contains text and images. You may also want to create an excel file and fit or shrink worksheet to one page using Java programming.

Why do we need excel sheet to fit to one page?

When your excel file’s sheet has many columns and you need to print the excel sheet but it overflows one page, then you may want to fit worksheet into one page.

Prerequisites

Eclipse 4.12, Java 12 or 8, Gradle 5.6, Apache POI 4.1.0

Creating Project

Create a gradle based project in Eclipse. The project name is excel-poi-fit-sheet-one-page.

Updating Build Script

We will update the default generated build.gradle script to include the required dependency.

If you are using Java 8 then you need to change the Java version in the below build script.

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.0'
}

Java Class to Fit to One Page

Now we will create a class in Java programming language to fit excel sheet to one page.

You need to change the file path according to your file location.

The sample files can be downloaded from the source code section later at the bottom of this tutorial.

package com.roytuts.excel.poi.fit.sheet.one.page;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelFitToOnePage {

	public static void main(String[] args) throws FileNotFoundException, IOException {
		// InputStream fis = new FileInputStream(new
		// File("C:/jee_workspace/sample.xlsx"));

		InputStream fis = new FileInputStream(new File("C:/jee_workspace/sample-with-image.xlsx"));

		Workbook wb = WorkbookFactory.create(fis);

		Sheet sheet = wb.getSheetAt(0);

		PrintSetup ps = sheet.getPrintSetup();

		sheet.setFitToPage(true);
		sheet.setAutobreaks(true);

		ps.setFitWidth((short) 1);
		ps.setFitHeight((short) 1);

		//OutputStream fileOut = new FileOutputStream("C:/jee_workspace/sample-fit-one-page.xlsx");
		
		OutputStream fileOut = new FileOutputStream("C:/jee_workspace/sample-with-image-fit-one-page.xlsx");
		
		wb.write(fileOut);

		wb.close();

	}

}

First we read the excel file – text or image based.

Then we create Workbook object. Notice we create Workbook object from WorkbookFactory to avoid checking whether the excel file is .xls or .xlsx format. The WorkbookFactory automatically handles it.

Next we configure the PrintSetup for the first sheet. You may also configure for other sheet if you need.

Related Posts:

Setting Print Area for Excel Sheet using Apache POI

We set the sheet to fit to page and auto-break so that sheet will be fit into a page.

In Excel’s Page Setup screen, the method setFitToPage(boolean) controls which radio button is active in the dialog box. The value true sets the radio button for Fit to:, allowing you to control the page(s) wide by page(s) tall boxes. The value false sets the radio button for Adjust to:, % normal size.

We fit sheet to one page width by the line ps.setFitWidth((short) 1);.

We fit sheet to one page height by the line ps.setFitHeight((short) 1);.

Finally we write to the output file.

Testing the Application

When you open the output excel file and try to print then you will see similar output to the below image (excel file that has text and image):

fit excel sheet to one page using apache poi in java

You will also find the similar output for the excel file that has only text data.

Source Code

download source code (sample excel files included)

Thanks for reading.

Roy Tutorials