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:
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):

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.