Create an excel file with poi

A memorandum on how to create an Excel file using a template file in poi.

  1. Load the template file

Example


File file = new File("C:\\tmp\\templates\\templates.xml");
Workbook workbook = null;

try (InputStream is = new ByteArrayInputStream(
		Files.readAllBytes(file.toPath()));) {
	workbook = WorkbookFactory.create(is);
} catch (IOException e) {
	e.printStackTrace();
}
  1. Output the file

Example


FileOutputStream fout = null;
try {
	fout = new FileOutputStream("C:\\tmp\\");
	workbook.write(fout);
} catch (FileNotFoundException e) {
	e.printStackTrace();
} catch (IOException e) {
	e.printStackTrace();
} finally {
	if (fout != null) {
		try {
			fout.close();
		} catch (IOException e) {
        }
	}
}
  1. Set the value in the cell Note that the index to specify the cell starts at 0. Also, if row or cell is NULL, NullPointerException will occur at the time of setting, so be sure to perform createRow / createCell.

Example


Sheet sheet = workbook.getSheet("Sheet1");
getCell(sheet, 0, 0).setCellValue("test");

private Cell getCell(Sheet sheet, int rowIndex, int colIndex) {

    Row row = sheet.getRow(rowIndex);

    if (row == null) {
    	row = sheet.createRow(rowIndex);
    }

    Cell cell = row.getCell(colIndex);

    if (cell == null) {
    	cell = row.createCell(colIndex);
    }

    return cell;

}

  1. Delete the sheet The index also starts at 0. Set 0 if you want to delete Sheet1 and 1 if you want to delete Sheet2.

Example


workbook.removeSheetAt(0);
  1. Specify the print range The index is omitted below. First argument: Index of the sheet that sets the print range Second argument: Index of the column at the start position of the print range Third argument: Index of the column at the end of the print range Fourth argument: Index of the line at the start position of the print range Fifth argument: Index of the line at the end of the print range In this example, only cell A1 of Sheet1 is specified as the print range.

Example


workbook.setPrintArea(0, 0, 1, 0, 1);

Recommended Posts

Create an excel file with poi
[Java] Creating an Excel file using Apache POI
Manipulate Excel with Apache POI
Create an HTTPS file server for development with ring-jetty-adapter
Test the contents of an Excel file with JUnit
Replace text in Excel file autoshapes with Apache POI
How to create an Excel form using a template file with Spring MVC
Create an immutable class with JAVA
Create an app with Spring Boot 2
Create an app with Spring Boot
EXCEL file update sample with JAVA
[Java] Create an executable module with Gradle
Create a jar file with the command
[Java] Handle Excel files with Apache POI
Create an or search function with Ransack.
Sample to create PDF from Excel with Ruby
Create an RSA encryption-enabled JSON API with wicket
Create an EC site with Rails5 ⑤ ~ Customer model ~
Create an Annotator that uses kuromoji with NLP4J [007]
[Swift] Create an image selection UI with PhotoKit
Create an EC site with Rails 5 ⑩ ~ Create an order function ~
Try create with Trailblazer
Create a Jar file with two lines of command
Create an EC site with Rails5 ⑦ ~ Address, Genre model ~
Create an EC site with Rails 5 ⑨ ~ Create a cart function ~
Create an EC site with Rails5 ④ ~ Header and footer ~
Create an E2E test environment with Docker x Cypress
Create a user with an empty password on CentOS7
Create an EC site with Rails5 ⑥ ~ seed data input ~
Create a Service with an empty model Liferay 7.0 / DXP
[Rails] Create an application
Maybe it works! Create an image with Docker and share it!
Create an AWS IoT EduKit development environment with Ubuntu 20.04 + VirtualBox 6.1
[Rails] Create an email sending function with ActionMailer (complete version)
csv file output with opencsv
[Apache POI] Corresponding Excel version
Create XML-RPC API with Wicket
Excel operation using Apache POI
Let's operate Excel with Java! !!
iOS: File upload with SFTP
[Java] Create a temporary file
File upload with Spring Boot
Create a playground with Xcode 12
Java creates an Excel document
Post an image with POSTMAN
Create microservices with Spring Boot
Apache POI Excel in Kotlin
Output Excel with formulas with XlsMapper
Excel output using Apache POI!
Kinx Tips-Create an executable file
Create an EC site with Rails5 ② ~ Bootstrap4 settings, Controller / action definition ~
Create an app catalog site using CLI for Microsoft 365 with Docker