Apache POI - Guia rápido

Muitas vezes, um aplicativo de software é necessário para gerar relatórios no formato de arquivo do Microsoft Excel. Às vezes, espera-se até que um aplicativo receba arquivos do Excel como dados de entrada. Por exemplo, um aplicativo desenvolvido para o departamento financeiro de uma empresa terá que gerar todos os seus outputs em Excel.

Qualquer programador Java que deseja produzir arquivos do MS Office como saída deve usar uma API predefinida e somente leitura para fazer isso.

O que é Apache POI?

Apache POI é uma API popular que permite aos programadores criar, modificar e exibir arquivos do MS Office usando programas Java. É uma biblioteca de código aberto desenvolvida e distribuída pela Apache Software Foundation para projetar ou modificar arquivos do Microsoft Office usando o programa Java. Ele contém classes e métodos para decodificar os dados de entrada do usuário ou um arquivo em documentos do MS Office.

Componentes do Apache POI

O Apache POI contém classes e métodos para trabalhar em todos os documentos compostos OLE2 do MS Office. A lista de componentes desta API é fornecida abaixo.

  • POIFS(Sistema de arquivos de implementação de ofuscação pobre) - Este componente é o fator básico de todos os outros elementos de POI. É usado para ler diferentes arquivos explicitamente.

  • HSSF (Formato de planilha horrível) - É usado para ler e escrever xls formato de arquivos MS-Excel.

  • XSSF (Formato de planilha XML) - É usado para xlsx formato de arquivo do MS-Excel.

  • HPSF (Horrible Property Set Format) - É usado para extrair property sets dos arquivos do MS-Office.

  • HWPF (Formato de processador de texto horrível) - É usado para ler e escrever doc arquivos de extensão do MS-Word.

  • XWPF (Formato de Processador de Texto XML) - É usado para ler e escrever docx arquivos de extensão do MS-Word.

  • HSLF (Horrible Slide Layout Format) - É usado para ler, criar e editar apresentações em PowerPoint.

  • HDGF (Horrible DiaGram Format) - Ele contém classes e métodos para MS-Visio arquivos binários.

  • HPBF (Horrible PuBlisher Format) - É usado para ler e escrever MS-Publisher arquivos.

Este tutorial o orienta durante o processo de trabalho em arquivos Excel usando Java. Portanto, a discussão está confinada aos componentes HSSF e XSSF.

Note - Versões mais antigas de POI suportam formatos de arquivo binários como doc, xls, ppt, etc. Da versão 3.5 em diante, POI suporta formatos de arquivo OOXML do MS-Office como docx, xlsx, pptx, etc.

Como o Apache POI, existem outras bibliotecas fornecidas por vários fornecedores para a geração de arquivos do Excel. Esses incluemAspose cells for Java por Aspose, JXL por Bibliotecas Commons, e JExcel por Team Dev.

Este capítulo o conduz por alguns dos sabores da API Java Excel e seus recursos. Existem muitos fornecedores que fornecem APIs relacionadas ao Java Excel; alguns deles são considerados neste capítulo.

Aspose Cells para Java

Aspose Cells for Java é uma API Java Excel puramente licenciada desenvolvida e distribuída pelo fornecedor Aspose. A versão mais recente desta API é 8.1.2, lançada em julho de 2014. É uma API rica e pesada (combinação de classes Java simples e classes AWT) para projetar o componente do Excel que pode ler, escrever e manipular planilhas.

Os usos comuns desta API são os seguintes -

  • Relatórios do Excel, crie relatórios dinâmicos do Excel
  • Renderização e impressão de alta fidelidade do Excel
  • Importar e exportar dados de planilhas do Excel
  • Gerar, editar e converter planilhas

JXL

JXL é uma estrutura de terceiros projetada para Selenium que oferece suporte à automação orientada a dados em navegadores da web (atualização automática de dados em navegadores da web). No entanto, também é usado como uma biblioteca de suporte comum para a API JExcel, pois possui recursos básicos para criar, ler e escrever planilhas.

Os recursos básicos são os seguintes -

  • Gerar arquivos Excel
  • Importar dados de pastas de trabalho e planilhas
  • Obtenha o número total de linhas e colunas

Note - JXL oferece suporte apenas ao formato de arquivo .xls e não pode lidar com grandes volumes de dados.

JExcel

JExcel é uma API puramente licenciada fornecida pelo Team Dev. Usando isso, os programadores podem facilmente ler, escrever, exibir e modificar pastas de trabalho do Excel em ambos.xls e .xlsxformatos. Essa API pode ser facilmente incorporada com Java Swing e AWT. A versão mais recente desta API é Jexcel-2.6.12, lançada em 2009.

As principais características são as seguintes -

  • Automatize o aplicativo Excel, pastas de trabalho, planilhas, etc.
  • Incorpore pastas de trabalho em um aplicativo Java Swing como um componente Swing comum
  • Adicionar ouvintes de eventos a pastas de trabalho e planilhas
  • Adicione manipuladores de eventos para lidar com o comportamento de eventos de planilhas e planilhas
  • Adicione pares nativos para desenvolver uma funcionalidade personalizada

Apache POI

Apache POI é uma biblioteca de código aberto 100% fornecida pela Apache Software Foundation. A maioria dos desenvolvedores de aplicativos de pequena e média escala depende muito do Apache POI (HSSF + XSSF). Ele oferece suporte a todos os recursos básicos das bibliotecas do Excel; no entanto, renderização e extração de texto são seus principais recursos.

Este capítulo o conduz pelo processo de configuração do Apache POI em sistemas baseados em Windows e Linux. O Apache POI pode ser facilmente instalado e integrado ao seu ambiente Java atual seguindo algumas etapas simples sem quaisquer procedimentos de configuração complexos. A administração do usuário é necessária durante a instalação.

Requisitos de sistema

JDK Java SE 2 JDK 1.5 ou superior
Memória 1 GB de RAM (recomendado)
Espaço em disco Sem requisitos mínimos
Versão do sistema operacional Windows XP ou superior, Linux

Vamos agora prosseguir com as etapas para instalar o Apache POI.

Etapa 1: verifique a instalação do Java

Em primeiro lugar, você precisa ter o Java Software Development Kit (SDK) instalado em seu sistema. Para verificar isso, execute qualquer um dos dois comandos, dependendo da plataforma em que está trabalhando.

Se a instalação do Java foi feita corretamente, ele exibirá a versão atual e as especificações de sua instalação do Java. Um exemplo de saída é fornecido na tabela a seguir.

Plataforma Comando Saída de amostra
janelas

Abra o console de comando e digite -

\>java –version

Versão Java "1.7.0_60"

Java (TM) SE Run Time Environment (build 1.7.0_60-b19)

Java Hotspot (TM) Servidor VM de 64 bits (versão 24.60-b09, modo misto)
Linux

Abra o terminal de comando e digite -

$java –version

versão java "1.7.0_25"

Open JDK Runtime Environment (rhel-2.3.10.4.el6_4-x86_64)

Abra o servidor VM JDK de 64 bits (compilação 23.7-b01, modo misto)

  • Presumimos que os leitores deste tutorial tenham o Java SDK versão 1.7.0_60 instalado em seus sistemas.

  • Caso você não tenha Java SDK, baixe sua versão atual em https://www.oracle.com/technetwork/java/javase/downloads/index.html e instalá-lo.

Etapa 2: definir seu ambiente Java

Defina a variável de ambiente JAVA_HOME para apontar para o local do diretório base onde o Java está instalado em sua máquina. Por exemplo,

Sr. Não. Plataforma e descrição
1

Windows

Defina JAVA_HOME como C: \ ProgramFiles \ java \ jdk1.7.0_60
2

Linux

Exportar JAVA_HOME = / usr / local / java-current

Anexe o caminho completo do local do compilador Java ao caminho do sistema.

Sr. Não. Plataforma e descrição
1

Windows

Anexe a string "C: \ Arquivos de programas \ Java \ jdk1.7.0_60 \ bin" ao final da variável de sistema PATH.
2

Linux

Exportar PATH = $ PATH: $ JAVA_HOME / bin /

Execute o comando java -version no prompt de comando, conforme explicado acima.

Etapa 3: Instale a biblioteca Apache POI

Baixe a versão mais recente do Apache POI de https://poi.apache.org/download.htmle descompacte seu conteúdo para uma pasta de onde as bibliotecas necessárias podem ser vinculadas ao seu programa Java. Vamos supor que os arquivos sejam coletados em uma pasta na unidade C.

As imagens a seguir mostram os diretórios e a estrutura de arquivos dentro da pasta baixada.

Adicione o caminho completo dos cinco jars como destacado na imagem acima para CLASSPATH.

Sr. Não. Plataforma e descrição
1

Windows

Anexe as seguintes strings ao final da variável do usuário

CLASSPATH -

“C: \ poi-3.9 \ poi-3.9-20121203.jar;”

“C: \ poi-3.9 \ poi-ooxml-3.9-20121203.jar;”

“C: \ poi-3.9 \ poi-ooxml-schemas-3.9-20121203.jar;”

“C: \ poi-3.9 \ ooxml-lib \ dom4j-1.6.1.jar;”

“C: \ poi-3.9 \ ooxml-lib \ xmlbeans-2.3.0.jar;.;”
2

Linux

Exportar CLASSPATH = $ CLASSPATH:

/usr/share/poi-3.9/poi-3.9-20121203.tar:

/usr/share/poi-3.9/poi-ooxml-schemas-3.9-20121203.tar:

/usr/share/poi-3.9/poi-ooxml-3.9-20121203.tar:

/usr/share/poi-3.9/ooxml-lib/dom4j-1.6.1.tar:

/usr/share/poi-3.9/ooxml-lib/xmlbeans-2.3.0.tar

Este capítulo explica algumas classes e métodos da API Apache POI que são essenciais para trabalhar em arquivos Excel usando programas Java.

Livro de exercícios

Esta é a superinterface de todas as classes que criam ou mantêm pastas de trabalho do Excel. Pertence aoorg.apache.poi.ss.usermodelpacote. As duas classes que implementam esta interface são as seguintes -

  • HSSFWorkbook- Esta classe possui métodos para ler e gravar arquivos do Microsoft Excel no formato .xls. É compatível com as versões 97–2003 do MS-Office.

  • XSSFWorkbook- Esta classe possui métodos para ler e gravar arquivos xml do Microsoft Excel e OpenOffice no formato .xls ou .xlsx. É compatível com as versões do MS-Office 2007 ou posteriores.

HSSFWorkbook

É uma classe de alto nível sob o org.apache.poi.hssf.usermodelpacote. Ele implementa oWorkbookinterface e é usado para arquivos do Excel no formato .xls. Listados abaixo estão alguns dos métodos e construtores dessa classe.

Construtores de classe

Sr. Não. Constructor & Description
1

HSSFWorkbook()

Creates a new HSSFWorkbook object from scratch.
2

HSSFWorkbook(DirectoryNode directory, boolean preserveNodes)

Creates a new HSSFWworkbook objectinside a specific directory.
3

HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes)

Given a POIFSFileSystem object and a specific directory within it, it creates an SSFWorkbook object to read a specified workbook.
4

HSSFWorkbook(java.io.InputStream s)

Creates a new HSSFWorkbook object using an input stream.
5

HSSFWorkbook(java.io.InputStream s, boolean preserveNodes)

Constructs a POI file system around your input stream.
6

HSSFWorkbook(POIFSFileSystem fs)

Constructs a new HSSFWorkbook object using a POIFSFileSystem object.
7

HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)

Given a POIFSFileSystem object, it creates a new HSSFWorkbook object to read a specified workbook.

The frequently used parameters inside these constructors are −

  • directory − It is the POI filesystem directory to process from.

  • fs − It is the POI filesystem that contains the workbook stream.

  • preservenodes − This is an optional parameter that decides whether to preserve other nodes like macros. It consumes a lot of memory as it stores all the POIFileSystem in memory (if set).

Note − The HSSFWorkbook class contains a number of methods; however they are compatible with xls format only. In this tutorial, the focus is on the latest version of Excel file formats. Hence, the class methods of HSSFWorkbook are not listed here. If you require these class methods, then refer POI-HSSFWorkbook class API at https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html.

XSSFWorkbook

It is a class that is used to represent both high and low level Excel file formats. It belongs to the org.apache.xssf.usemodel package and implements the Workbook interface. Listed below are the methods and constructors under this class.

Class Constructors

Sr.No. Constructor & Description
1

XSSFWorkbook()

Creates a new XSSFworkbook object from scratch.
2

XSSFWorkbook(java.io.File file)

Constructs an XSSFWorkbook object from a given file.
3

XSSFWorkbook(java.io.InputStream is)

Constructs an XSSFWorkbook object, by buffering the whole input stream into memory and then opening an OPCPackage object for it.
4

XSSFWorkbook(java.lang.String path)

Constructs an XSSFWorkbook object given the full path of a file.

Class Methods

Sr.No. Method & Description
1

createSheet()

Creates an XSSFSheet for this workbook, adds it to the sheets, and returns the high level representation.
2

createSheet(java.lang.String sheetname)

Creates a new sheet for this Workbook and returns the high level representation.
3

createFont()

Creates a new font and adds it to the workbook's font table.
4

createCellStyle()

Creates a new XSSFCellStyle and adds it to the workbook's style table.
5

createFont()

Creates a new font and adds it to the workbook's font table.
6

setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow,int endRow)

Sets the print area of a given sheet as per the specified parameters.

For the remaining methods of this class, refer the complete API document at: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html. for the complete list of methods.

Sheet

Sheet is an interface under the org.apache.poi.ss.usermodel package and it is a super-interface of all classes that create high or low level spreadsheets with specific names. The most common type of spreadsheet is worksheet, which is represented as a grid of cells.

HSSFSheet

This is a class under the org.apache.poi.hssf.usermodel package. It can create excel spreadsheets and it allows to format the sheet style and sheet data.

Class Constructors

Sr.No. Constructor & Description
1

HSSFSheet(HSSFWorkbook workbook)

Creates new HSSFSheet called by HSSFWorkbook to create a sheet from scratch.
2

HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)

Creates an HSSFSheet representing the given sheet object.

XSSFSheet

This is a class which represents high level representation of excel spreadsheet. It is under org.apache.poi.hssf.usermodel package.

Class Constructors

Sr.No. Constructor & Description
1

XSSFSheet()

Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch.
2

XSSFSheet(PackagePart part, PackageRelationship rel)

Creates an XSSFSheet representing the given package part and relationship.

Class Methods

Sr.No. Method & Description
1

addMergedRegion(CellRangeAddress region)

Adds a merged region of cells (hence those cells form one).
2

autoSizeColumn(int column)

Adjusts the column width to fit the contents.
3

iterator()

This method is an alias for rowIterator() to allow foreach loops
4

addHyperlink(XSSFHyperlink hyperlink)

Registers a hyperlink in the collection of hyperlinks on this sheet

For the remaining methods of this class, refer the complete API at: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html.

Row

This is an interface under the org.apache.poi.ss.usermodel package. It is used for high-level representation of a row of a spreadsheet. It is a super-interface of all classes that represent rows in POI library.

XSSFRow

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Row interface, therefore it can create rows in a spreadsheet. Listed below are the methods and constructors under this class.

Class Methods

Sr.No. Method & Description
1

createCell(int columnIndex)

Creates new cells within the row and returns it.
2

setHeight(short height)

Sets the height in short units.

For the remaining methods of this class, follow the given link https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html

Cell

This is an interface under the org.apache.poi.ss.usermodel package. It is a super-interface of all classes that represent cells in the rows of a spreadsheet.

Cells can take various attributes such as blank, numeric, date, error, etc. Cells should have their own numbers (0 based) before being added to a row.

XSSFCell

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Cell interface. It is a high-level representation of cells in the rows of a spreadsheet.

Field Summary

Listed below are some of the fields of the XSSFCell class along with their description.

Sr.No. Cell Type & Description
1

CELL_TYPE_BLANK

Represents blank cell
2

CELL_TYPE_BOOLEAN

Represents Boolean cell (true or false)
3

CELL_TYPE_ERROR

Represents error value on a cell
4

CELL_TYPE_FORMULA

Represents formula result on a cell
5

CELL_TYPE_NUMERIC

Represents numeric data on a cell
6

CELL_TYPE_STRING

Represents string (text) on a cell

Class Methods

Sr.No. Method & Description
1

setCellStyle(CellStyle style)

Sets the style for the cell.
2

setCellType(int cellType)

Sets the type of cells (numeric, formula, or string).
3

setCellValue(boolean value)

Sets a boolean value for the cell.
4

setCellValue(java.util.Calendar value)

Sets a date value for the cell.
5

setCellValue(double value)

Sets a numeric value for the cell.
6

setCellValue(java.lang.String str)

Sets a string value for the cell.
7

setHyperlink(Hyperlink hyperlink)

Assigns a hyperlink to this cell.

For the remaining methods and fields of this class, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html

XSSFCellStyle

This is a class under the org.apache.poi.xssf.usermodel package. It will provide possible information regarding the format of the content in a cell of a spreadsheet. It also provides options for modifying that format. It implements the CellStyle interface.

Field Summary

The following table lists a few fields that are inherited from the CellStyle interface.

Sr.No. Field & Description
1

ALIGN_CENTER

Center align the cell contents
2

ALIGN_CENTER_SELECTION

Center-selection horizontal alignment
3

ALIGN_FILL

Cell fit to the content size
4

ALIGN_JUSTIFY

Fit cell contents to its width
5

ALIGN_LEFT

Left align the cell contents
6

ALIGN_RIGHT

Right align the cell contents
7

BORDER_DASH_DOT

Cell style with dash and dot
8

BORDER_DOTTED

Cell style with dotted border
9

BORDER_DASHED

Cell style with dashed border
10

BORDER_THICK

Cell style with thick border
11

BORDER_THIN

Cell style with thin border
12

VERTICAL_BOTTOM

Align the cell contents vertical bottom
13

VERTICAL_CENTER

Align the cell contents vertical center
15

VERTICAL_JUSTIFY

Align and justify the cell contents vertically
16

VERTICAL_TOP

Top aligned vertical alignment

Class Constructors

Sr.No. Constructor & Description
1

XSSFCellStyle(int cellXfId, int cellStyleXfId, StylesTable stylesSource, ThemesTable theme)

Creates a cell style from the supplied parts
2

XSSFCellStyle(StylesTable stylesSource)

Creates an empty cell Style

Class Methods

S.No Method & Description
1

setAlignment(short align)

Sets the type of horizontal alignment for the cell
2

setBorderBottom(short border)

Sets the type of border for the bottom border of the cell
3

setBorderColor(XSSFCellBorder.BorderSide side, XSSFColor color)

Sets the color for the selected border
4

setBorderLeft(Short border)

Sets the type of border for the left border of the cell
5

setBorderRight(short border)

Sets the type of border for the right border of the cell
6

setBorderTop(short border)

Sets the type of border for the top border of the cell
7

setFillBackgroundColor(XSSFColor color)

Sets the background fill color represented as an XSSFColor value.
8

setFillForegroundColor(XSSFColor color)

Sets the foreground fill color represented as an XSSFColor value.
9

setFillPattern(short fp)

Specifies the cell fill information for pattern and solid color cell fills.
10

setFont(Font font)

Sets the font for this style.
11

setRotation(short rotation)

Sets the degree of rotation for the text in the cell.
12

setVerticalAlignment(short align)

Sets the type of vertical alignment for the cell.

For the remaining methods and fields in this class, go through the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html

HSSFColor

This is a class under the org.apache.poi.hssf.util package. It provides different colors as nested classes. Usually these nested classes are represented by using their own indexes. It implements the Color interface.

Nested classes

All nested classes of this class are static and each class has its index. These nested color classes are used for cell formatting such as cell content, border, foreground, and background. Listed below are some of the nested classes.

Sr.No. Class names (colors)
1 HSSFColor.AQUA
2 HSSFColor.AUTOMATIC
3 HSSFColor.BLACK
4 HSSFColor.BLUE
5 HSSFColor.BRIGHT_GREEN
6 HSSFColor.BRIGHT_GRAY
7 HSSFColor.CORAL
8 HSSFColor.DARK_BLUE
9 HSSFColor.DARK_GREEN
10 HSSFColor.SKY_BLUE
11 HSSFColor.WHITE
12 HSSFColor.YELLOW

Class Methods

Only one method of this class is important and that is used to get the index value.

Sr.No. Method & Description
1

getIndex()

This method is used to get the index value of a nested class.

For the remaining methods and nested classes, refer the following link: https://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html

XSSFColor

This is a class under the org.apache.poi.xssf.usermodel package. It is used to represent color in a spreadsheet. It implements the Color interface. Listed below are some of its methods and constructors.

Class Constructors

Sr.No. Constructor & Description
1

XSSFColor()

Creates a new instance of XSSFColor.
2

XSSFColor(byte[] rgb)

Creates a new instance of XSSFColor using RGB.
3

XSSFColor(java.awt.Color clr)

Creates a new instance of XSSFColor using the Color class from the awt package.

Class Methods

Sr.No. Method & Description
1

setAuto(boolean auto)

Sets a boolean value to indicate that the ctColor is automatic and the system ctColor is dependent.
2

setIndexed(int indexed)

Sets indexed ctColor value as system ctColor.

For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFColor.html

XSSFFont

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Font interface and therefore it can handle different fonts in a workbook.

Class Constructor

Sr.No. Constructor & Description
1

XSSFFont()

Creates a new XSSFont instance.

Class Methods

Sr.No. Method & Description
1

setBold(boolean bold)

Sets a Boolean value for the 'bold' attribute.
2

setColor(short color)

Sets the indexed color for the font.
3

setColor(XSSFColor color)

Sets the color for the font in Standard Alpha RGB color value.
4

setFontHeight(short height)

Sets the font height in points.
5

setFontName(java.lang.String name)

Sets the name for the font.
6

setItalic(boolean italic)

Sets a Boolean value for the 'italic' property.

For the remaining methods, go through the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html

XSSFHyperlink

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Hyperlink interface. It is used to set a hyperlink to the cell contents of a spreadsheet.

Fields

The fields of this class are as follows. Here, fields mean the types of hyperlinks used.

Sr.No. Field & Description
1

LINK_DOCUMENT

Used to link any other document
2

LINK_EMAIL

Used to link email
3

LINK_FILE

Used to link any other file in any format
4

LINK_URL

Used to link a web URL

Class Methods

Sr.No. Method & Description
1

setAddress(java.lang.String address)

Hyperlink address.

For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFHyperlink.html

XSSFCreationHelper

This is a class under the org.apache.poi.xssf.usermodel package. It implements the CreationHelper interface. It is used as a support class for formula evaluation and setting up hyperlinks.

Class methods

Sr.No. Method & Description
1

createFormulaEvaluator()

Creates an XSSFFormulaEvaluator instance, the object that evaluates formula cells.
2

createHyperlink(int type)

Creates a new XSSFHyperlink.

For the remaining methods, refer the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCreationHelper.html

XSSFPrintSetup

This is a class under the org.apache.poi.xsssf.usermodel package. It implements the PrintSetup interface. It is used to set print page size, area, options, and settings.

Class Methods

Sr.No. Method & Description
1

setLandscape(boolean ls)

Sets a boolean value to allow or block landscape printing.
2

setLeftToRight(boolean ltor)

Sets whether to go left to right or top down in ordering while printing.
3

setPaperSize(short size)

Sets the paper size.

For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFPrintSetup.html

Here the term 'Workbook' means Microsoft Excel file. After completion of this chapter, you will be able to create new Workbooks and open existing Workbooks with your Java program.

Create Blank Workbook

The following simple program is used to create a blank Microsoft Excel Workbook.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;

public class CreateWorkBook {
   public static void main(String[] args)throws Exception {
      //Create Blank workbook
      XSSFWorkbook workbook = new XSSFWorkbook(); 

      //Create file system using specific name
      FileOutputStream out = new FileOutputStream(new File("createworkbook.xlsx"));

      //write operation workbook using file out object 
      workbook.write(out);
      out.close();
      System.out.println("createworkbook.xlsx written successfully");
   }
}

Let us save the above Java code as CreateWorkBook.java, and then compile and execute it from the command prompt as follows −

$javac CreateWorkBook.java $java CreateWorkBook

If your system environment is configured with the POI library, it will compile and execute to generate the blank Excel file named createworkbook.xlsx in your current directory and display the following output in the command prompt.

createworkbook.xlsx written successfully

Open Existing Workbook

Use the following code to open an existing workbook.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;

public class OpenWorkBook {
   public static void main(String args[])throws Exception { 
      File file = new File("openworkbook.xlsx");
      FileInputStream fIP = new FileInputStream(file);
      
      //Get the workbook instance for XLSX file 
      XSSFWorkbook workbook = new XSSFWorkbook(fIP);
      
      if(file.isFile() && file.exists()) {
         System.out.println("openworkbook.xlsx file open successfully.");
      } else {
         System.out.println("Error to open openworkbook.xlsx file.");
      }
   }
}

Save the above Java code as OpenWorkBook.java, and then compile and execute it from the command prompt as follows −

$javac OpenWorkBook.java $java OpenWorkBook

It will compile and execute to generate the following output.

openworkbook.xlsx file open successfully.

After opening a workbook, you can perform read and write operations on it.

This chapter explains how to create a spreadsheet and manipulate it using Java. Spreadsheet is a page in an Excel file; it contains rows and columns with specific names.

After completing this chapter, you will be able to create a spreadsheet and perform read operations on it.

Create a Spreadsheet

First of all, let us create a spreadsheet using the referenced classes discussed in the earlier chapters. By following the previous chapter, create a workbook first and then we can go on and create a sheet.

The following code snippet is used to create a spreadsheet.

//Create Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();

//Create a blank spreadsheet
XSSFSheet spreadsheet = workbook.createSheet("Sheet Name");

Rows on Spreadsheet

Spreadsheets have a grid layout. The rows and columns are identified with specific names. The columns are identified with alphabets and rows with numbers.

The following code snippet is used to create a row.

XSSFRow row = spreadsheet.createRow((short)1);

Write into a Spreadsheet

Let us consider an example of employee data. Here the employee data is given in a tabular form.

Emp Id Emp Name Designation
Tp01 Gopal Technical Manager
TP02 Manisha Proof Reader
Tp03 Masthan Technical Writer
Tp04 Satish Technical Writer
Tp05 Krishna Technical Writer

The following code is used to write the above data into a spreadsheet.

import java.io.File;
import java.io.FileOutputStream;

import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Writesheet {
   public static void main(String[] args) throws Exception {

      //Create blank workbook
      XSSFWorkbook workbook = new XSSFWorkbook(); 

      //Create a blank sheet
      XSSFSheet spreadsheet = workbook.createSheet(" Employee Info ");

      //Create row object
      XSSFRow row;

      //This data needs to be written (Object[])
      Map < String, Object[] > empinfo = 
      new TreeMap < String, Object[] >();
      empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" });
      empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" });
      empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" });
      empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" });
      empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" });
      empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" });
      
      //Iterate over data and write to sheet
      Set < String > keyid = empinfo.keySet();
      int rowid = 0;

      for (String key : keyid) {
         row = spreadsheet.createRow(rowid++);
         Object [] objectArr = empinfo.get(key);
         int cellid = 0;

         for (Object obj : objectArr) {
            Cell cell = row.createCell(cellid++);
            cell.setCellValue((String)obj);
         }
      }

      //Write the workbook in file system
      FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("Writesheet.xlsx written successfully");
   }
}

Save the above Java code as Writesheet.java, and then compile and run it from the command prompt as follows −

$javac Writesheet.java $java Writesheet

It will compile and execute to generate an Excel file named Writesheet.xlsx in your current directory and you will get the following output in the command prompt.

Writesheet.xlsx written successfully

The Writesheet.xlsx file looks as follows.

Read from a Spreadsheet

Let us consider the above excel file named Writesheet.xslx as input. Observe the following code; it is used for reading the data from a spreadsheet.

import java.io.File;
import java.io.FileInputStream;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Readsheet {
   static XSSFRow row;
   public static void main(String[] args) throws Exception {
      FileInputStream fis = new FileInputStream(new File("WriteSheet.xlsx"));
      
      XSSFWorkbook workbook = new XSSFWorkbook(fis);
      XSSFSheet spreadsheet = workbook.getSheetAt(0);
      Iterator < Row >  rowIterator = spreadsheet.iterator();
      
      while (rowIterator.hasNext()) {
         row = (XSSFRow) rowIterator.next();
         Iterator < Cell >  cellIterator = row.cellIterator();
         
         while ( cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            
            switch (cell.getCellType()) {
               case Cell.CELL_TYPE_NUMERIC:
                  System.out.print(cell.getNumericCellValue() + " \t\t ");
                  break;
               
               case Cell.CELL_TYPE_STRING:
                  System.out.print(
                  cell.getStringCellValue() + " \t\t ");
                  break;
            }
         }
         System.out.println();
      }
      fis.close();
   }
}

Let us keep the above code in Readsheet.java file, and then compile and run it from the command prompt as follows −

$javac Readsheet.java $java Readsheet

If your system environment is configured with the POI library, it will compile and execute to generate the following output in the command prompt.

EMP ID   EMP NAME       DESIGNATION 
 tp01     Gopal       Technical Manager 
 tp02     Manisha     Proof Reader 
 tp03     Masthan     Technical Writer 
 tp04     Satish      Technical Writer 
 tp05     Krishna     Technical Writer

Any data that you enter into a spreadsheet is always stored in a cell. We use the labels of rows and columns to identify a cell. This chapter describes how to manipulate data in cells in a spreadsheet using Java programming.

Create a Cell

You need to create a row before creating a cell. A row is nothing but a collection of cells.

The following code snippet is used for creating a cell.

//create new workbook
XSSFWorkbook workbook = new XSSFWorkbook(); 

//create spreadsheet with a name
XSSFSheet spreadsheet = workbook.createSheet("new sheet");

//create first row on a created spreadsheet
XSSFRow row = spreadsheet.createRow(0);

//create first cell on created row
XSSFCell cell = row.createCell(0);

Types of Cells

The cell type specifies whether a cell can contain strings, numeric value, or formulas. A string cell cannot hold numeric values and a numeric cell cannot hold strings. Given below are the types of cells, their values, and type syntax.

Type of cell value Type Syntax
Blank cell value XSSFCell.CELL_TYPE_BLANK
Boolean cell value XSSFCell.CELL.TYPE_BOOLEAN
Error cell value XSSFCell.CELL_TYPE_ERROR
Numeric cell value XSSFCell.CELL_TYPE_NUMERIC
String cell value XSSFCell.CELL_TYPE_STRING

The following code is used to create different types of cells in a spreadsheet.

import java.io.File;
import java.io.FileOutputStream;

import java.util.Date;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TypesofCells {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("cell types");
      
      XSSFRow row = spreadsheet.createRow((short) 2);
      row.createCell(0).setCellValue("Type of Cell");
      row.createCell(1).setCellValue("cell value");
      
      row = spreadsheet.createRow((short) 3);
      row.createCell(0).setCellValue("set cell type BLANK");
      row.createCell(1);
      
      row = spreadsheet.createRow((short) 4);
      row.createCell(0).setCellValue("set cell type BOOLEAN");
      row.createCell(1).setCellValue(true);
      
      row = spreadsheet.createRow((short) 5);
      row.createCell(0).setCellValue("set cell type ERROR");
      row.createCell(1).setCellValue(XSSFCell.CELL_TYPE_ERROR );
      
      row = spreadsheet.createRow((short) 6);
      row.createCell(0).setCellValue("set cell type date");
      row.createCell(1).setCellValue(new Date());
      
      row = spreadsheet.createRow((short) 7);
      row.createCell(0).setCellValue("set cell type numeric");
      row.createCell(1).setCellValue(20 );
      
      row = spreadsheet.createRow((short) 8);
      row.createCell(0).setCellValue("set cell type string");
      row.createCell(1).setCellValue("A String");
      
      FileOutputStream out = new FileOutputStream(new File("typesofcells.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("typesofcells.xlsx written successfully");
   }
}

Save the above code in a file named TypesofCells.java, compile and execute it from the command prompt as follows.

$javac TypesofCells.java $java TypesofCells

If your system is configured with the POI library, then it will compile and execute to generate an Excel file named typesofcells.xlsx in your current directory and display the following output.

typesofcells.xlsx written successfully

The typesofcells.xlsx file looks as follows.

Cell Styles

Here you can learn how to do cell formatting and apply different styles such as merging adjacent cells, adding borders, setting cell alignment and filling with colors.

The following code is used to apply different styles to cells using Java programming.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellStyle {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("cellstyle");
      XSSFRow row = spreadsheet.createRow((short) 1);
      row.setHeight((short) 800);
      XSSFCell cell = (XSSFCell) row.createCell((short) 1);
      cell.setCellValue("test of merging");

      //MEARGING CELLS 
      //this statement for merging cells

      spreadsheet.addMergedRegion(
         new CellRangeAddress(
            1, //first row (0-based)
            1, //last row (0-based)
            1, //first column (0-based)
            4 //last column (0-based)
         )
      );
      
      //CELL Alignment
      row = spreadsheet.createRow(5); 
      cell = (XSSFCell) row.createCell(0);
      row.setHeight((short) 800);
      
      // Top Left alignment 
      XSSFCellStyle style1 = workbook.createCellStyle();
      spreadsheet.setColumnWidth(0, 8000);
      style1.setAlignment(XSSFCellStyle.ALIGN_LEFT);
      style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
      cell.setCellValue("Top Left");
      cell.setCellStyle(style1);
      row = spreadsheet.createRow(6); 
      cell = (XSSFCell) row.createCell(1);
      row.setHeight((short) 800);
      
      // Center Align Cell Contents 
      XSSFCellStyle style2 = workbook.createCellStyle();
      style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
      style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      cell.setCellValue("Center Aligned"); 
      cell.setCellStyle(style2);
      row = spreadsheet.createRow(7); 
      cell = (XSSFCell) row.createCell(2);
      row.setHeight((short) 800);
      
      // Bottom Right alignment 
      XSSFCellStyle style3 = workbook.createCellStyle();
      style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT);
      style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
      cell.setCellValue("Bottom Right");
      cell.setCellStyle(style3);
      row = spreadsheet.createRow(8);
      cell = (XSSFCell) row.createCell(3);
      
      // Justified Alignment 
      XSSFCellStyle style4 = workbook.createCellStyle();
      style4.setAlignment(XSSFCellStyle.ALIGN_JUSTIFY);
      style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_JUSTIFY);
      cell.setCellValue("Contents are Justified in Alignment"); 
      cell.setCellStyle(style4);
      
      //CELL BORDER
      row = spreadsheet.createRow((short) 10);
      row.setHeight((short) 800);
      cell = (XSSFCell) row.createCell((short) 1);
      cell.setCellValue("BORDER");
      
      XSSFCellStyle style5 = workbook.createCellStyle();
      style5.setBorderBottom(XSSFCellStyle.BORDER_THICK);
      style5.setBottomBorderColor(IndexedColors.BLUE.getIndex());
      style5.setBorderLeft(XSSFCellStyle.BORDER_DOUBLE);
      style5.setLeftBorderColor(IndexedColors.GREEN.getIndex());
      style5.setBorderRight(XSSFCellStyle.BORDER_HAIR);
      style5.setRightBorderColor(IndexedColors.RED.getIndex());
      style5.setBorderTop(XSSFCellStyle.BIG_SPOTS);
      style5.setTopBorderColor(IndexedColors.CORAL.getIndex());
      cell.setCellStyle(style5);
      
      //Fill Colors
      //background color
      row = spreadsheet.createRow((short) 10 );
      cell = (XSSFCell) row.createCell((short) 1);
      
      XSSFCellStyle style6 = workbook.createCellStyle();
      style6.setFillBackgroundColor(HSSFColor.LEMON_CHIFFON.index );
      style6.setFillPattern(XSSFCellStyle.LESS_DOTS);
      style6.setAlignment(XSSFCellStyle.ALIGN_FILL);
      spreadsheet.setColumnWidth(1,8000);
      cell.setCellValue("FILL BACKGROUNG/FILL PATTERN");
      cell.setCellStyle(style6);
      
      //Foreground color
      row = spreadsheet.createRow((short) 12);
      cell = (XSSFCell) row.createCell((short) 1);
      
      XSSFCellStyle style7 = workbook.createCellStyle();
      style7.setFillForegroundColor(HSSFColor.BLUE.index);
      style7.setFillPattern( XSSFCellStyle.LESS_DOTS);
      style7.setAlignment(XSSFCellStyle.ALIGN_FILL);
      cell.setCellValue("FILL FOREGROUND/FILL PATTERN");
      cell.setCellStyle(style7);

      FileOutputStream out = new FileOutputStream(new File("cellstyle.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("cellstyle.xlsx written successfully");
   }
}

Salve o código acima em um arquivo chamado CellStyle.java, compile e execute-o a partir do prompt de comando da seguinte maneira.

$javac CellStyle.java $java CellStyle

Ele irá gerar um arquivo Excel chamado cellstyle.xlsx em seu diretório atual e exibir a seguinte saída.

cellstyle.xlsx written successfully

O arquivo cellstyle.xlsx tem a seguinte aparência.

Este capítulo explica como definir diferentes fontes, aplicar estilos e exibir texto em diferentes ângulos de direção em uma planilha do Excel.

Cada sistema vem com uma enorme coleção de fontes, como Arial, Impact, Times New Roman, etc. A coleção também pode ser atualizada com novas fontes, se necessário. Da mesma forma, existem vários estilos em que uma fonte pode ser exibida, por exemplo, negrito, itálico, sublinhado, tachado, etc.

Fontes e estilos de fonte

O código a seguir é usado para aplicar uma fonte e um estilo específicos ao conteúdo de uma célula.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FontStyle {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("Fontstyle");
      XSSFRow row = spreadsheet.createRow(2);

      //Create a new font and alter it.
      XSSFFont font = workbook.createFont();
      font.setFontHeightInPoints((short) 30);
      font.setFontName("IMPACT");
      font.setItalic(true);
      font.setColor(HSSFColor.BRIGHT_GREEN.index);

      //Set font into style
      XSSFCellStyle style = workbook.createCellStyle();
      style.setFont(font);

      // Create a cell with a value and set style to it.
      XSSFCell cell = row.createCell(1);
      cell.setCellValue("Font Style");
      cell.setCellStyle(style);
      
      FileOutputStream out = new FileOutputStream(new File("fontstyle.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("fontstyle.xlsx written successfully");
   }
}

Vamos salvar o código acima em um arquivo chamado FontStyle.java. Compile e execute-o a partir do prompt de comando da seguinte maneira.

$javac FontStyle.java $java FontStyle

Ele gera um arquivo Excel chamado fontstyle.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

fontstyle.xlsx written successfully

o fontstyle.xlsx arquivo tem a seguinte aparência.

Direção do texto

Aqui você pode aprender como definir a direção do texto em diferentes ângulos. Normalmente, o conteúdo da célula é exibido horizontalmente, da esquerda para a direita e no ângulo 00; entretanto, você pode usar o código a seguir para girar a direção do texto, se necessário.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TextDirection {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("Text direction");
      XSSFRow row = spreadsheet.createRow(2);
      XSSFCellStyle myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 0);
      XSSFCell cell = row.createCell(1);
      cell.setCellValue("0D angle");
      cell.setCellStyle(myStyle);

      //30 degrees
      myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 30);
      cell = row.createCell(3);
      cell.setCellValue("30D angle");
      cell.setCellStyle(myStyle);

      //90 degrees
      myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 90);
      cell = row.createCell(5);
      cell.setCellValue("90D angle");
      cell.setCellStyle(myStyle);

      //120 degrees
      myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 120);
      cell = row.createCell(7);
      cell.setCellValue("120D angle");
      cell.setCellStyle(myStyle);

      //270 degrees
      myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 270);
      cell = row.createCell(9);
      cell.setCellValue("270D angle");
      cell.setCellStyle(myStyle);

      //360 degrees
      myStyle = workbook.createCellStyle();
      myStyle.setRotation((short) 360);
      cell = row.createCell(12);
      cell.setCellValue("360D angle");
      cell.setCellStyle(myStyle);
      
      FileOutputStream out = new FileOutputStream(new File("textdirection.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("textdirection.xlsx written successfully");
   }
}

Mantenha o código acima em TextDirectin.java arquivo e, em seguida, compile e execute-o no prompt de comando da seguinte maneira.

$javac TextDirection.java $java TextDirection

Ele irá compilar e executar para gerar um arquivo Excel chamado textdirection.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

textdirection.xlsx written successfully

o textdirection.xlsx arquivo tem a seguinte aparência.

Este capítulo o conduz pelo processo de aplicação de diferentes fórmulas em células usando a programação Java. O objetivo básico do aplicativo Excel é manter os dados numéricos aplicando fórmulas sobre eles.

Em uma fórmula, passamos valores dinâmicos ou localizações dos valores na planilha do Excel. Ao executar esta fórmula, você obtém o resultado desejado. A tabela a seguir lista algumas fórmulas básicas usadas com freqüência no Excel.

Operação Sintaxe
Adicionando vários números = SUM (Loc1: Locn) or = SOMA (n1, n2,)
Contagem = COUNT (Loc1: Locn) or = CONTAR (n1, n2,)
Potência de dois números = POTÊNCIA (Loc1, Loc2) or = POTÊNCIA (número, potência)
Máximo de vários números = MAX (Loc1: Locn) or = MAX (n1, n2,)
produtos = PRODUTO (Loc1: Locn) or = PRODUTO (n1, n2,)
Fatorial = FATO (Locn) or = FATO (número)
Número absoluto = ABS (Locn) or = ABS (número)
Data de hoje = HOJE ()
Converte letras minúsculas = INFERIOR (Locn) or = INFERIOR (texto)
Raiz quadrada = SQRT (locn) or = SQRT (número)

O código a seguir é usado para adicionar fórmulas a uma célula e executá-la.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Formula {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("formula");
      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell = row.createCell(1);
      
      cell.setCellValue("A = ");
      cell = row.createCell(2);
      cell.setCellValue(2);
      row = spreadsheet.createRow(2);
      cell = row.createCell(1);
      cell.setCellValue("B = ");
      cell = row.createCell(2);
      cell.setCellValue(4);
      row = spreadsheet.createRow(3);
      cell = row.createCell(1);
      cell.setCellValue("Total = ");
      cell = row.createCell(2);
      
      // Create SUM formula
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("SUM(C2:C3)");
      cell = row.createCell(3);
      cell.setCellValue("SUM(C2:C3)");
      row = spreadsheet.createRow(4);
      cell = row.createCell(1);
      cell.setCellValue("POWER =");
      cell=row.createCell(2);
      
      // Create POWER formula
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("POWER(C2,C3)");
      cell = row.createCell(3);
      cell.setCellValue("POWER(C2,C3)");
      row = spreadsheet.createRow(5);
      cell = row.createCell(1);
      cell.setCellValue("MAX = ");
      cell = row.createCell(2);
      
      // Create MAX formula
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("MAX(C2,C3)");
      cell = row.createCell(3);
      cell.setCellValue("MAX(C2,C3)");
      row = spreadsheet.createRow(6);
      cell = row.createCell(1);
      cell.setCellValue("FACT = ");
      cell = row.createCell(2);
      
      // Create FACT formula
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("FACT(C3)");
      cell = row.createCell(3);
      cell.setCellValue("FACT(C3)");
      row = spreadsheet.createRow(7);
      cell = row.createCell(1);
      cell.setCellValue("SQRT = ");
      cell = row.createCell(2);
      
      // Create SQRT formula
      cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
      cell.setCellFormula("SQRT(C5)");
      cell = row.createCell(3);
      cell.setCellValue("SQRT(C5)");
      workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
      FileOutputStream out = new FileOutputStream(new File("formula.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("fromula.xlsx written successfully");
   }
}

Salve o código acima como Formula.java e então compilar e executá-lo a partir do prompt de comando da seguinte maneira.

$javac Formula.java $java Formula

Ele irá gerar um arquivo Excel chamado formula.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

fromula.xlsx written successfully

o formula.xlsx arquivo tem a seguinte aparência.

Este capítulo explica como adicionar hiperlinks ao conteúdo de uma célula. Normalmente, os hiperlinks são usados ​​para acessar qualquer URL da web, e-mail ou um arquivo externo.

O código a seguir mostra como criar hiperlinks nas células.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class HyperlinkEX {
   public static void main(String[] args) throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("Hyperlinks");
      XSSFCell cell;
      CreationHelper createHelper = workbook.getCreationHelper();
      XSSFCellStyle hlinkstyle = workbook.createCellStyle();
      XSSFFont hlinkfont = workbook.createFont();
      hlinkfont.setUnderline(XSSFFont.U_SINGLE);
      hlinkfont.setColor(HSSFColor.BLUE.index);
      hlinkstyle.setFont(hlinkfont);

      //URL Link
      cell = spreadsheet.createRow(1).createCell((short) 1);
      cell.setCellValue("URL Link");
      XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(Hyperlink.LINK_URL);
      link.setAddress("http://www.tutorialspoint.com/");
      cell.setHyperlink((XSSFHyperlink) link);
      cell.setCellStyle(hlinkstyle);

      //Hyperlink to a file in the current directory
      cell = spreadsheet.createRow(2).createCell((short) 1);
      cell.setCellValue("File Link");
      link = (XSSFHyperlink)createHelper.createHyperlink(Hyperlink.LINK_FILE);
      link.setAddress("cellstyle.xlsx");
      cell.setHyperlink(link);
      cell.setCellStyle(hlinkstyle);

      //e-mail link
      cell = spreadsheet.createRow(3).createCell((short) 1);
      cell.setCellValue("Email Link");
      link = (XSSFHyperlink)createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
      link.setAddress("mailto:[email protected]?" + "subject = Hyperlink");
      cell.setHyperlink(link);
      cell.setCellStyle(hlinkstyle);
      
      FileOutputStream out = new FileOutputStream(new File("hyperlink.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("hyperlink.xlsx written successfully");
   }
}

Salve o código acima como HyperlinkEX.java. Compile e execute-o a partir do prompt de comando da seguinte maneira.

$javac HyperlinkEX.java $java HyperlinkEX

Ele irá gerar um arquivo Excel chamado hyperlink.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

hyperlink.xlsx written successfully

o hyperlink.xlsx arquivo tem a seguinte aparência.

Este capítulo explica como definir a área de impressão em uma planilha. A área de impressão normal é da parte superior esquerda para a parte inferior direita nas planilhas do Excel. A área de impressão pode ser personalizada de acordo com sua necessidade. Isso significa que você pode imprimir um determinado intervalo de células de toda a planilha, personalizar o tamanho do papel, imprimir o conteúdo com as linhas de grade ativadas, etc.

O código a seguir é usado para configurar a área de impressão em uma planilha.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFPrintSetup;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PrintArea {
   public static void main(String[] args)throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("Print Area");

      //set print area with indexes
      workbook.setPrintArea(
         0, //sheet index
         0, //start column
         5, //end column
         0, //start row
         5 //end row
      );
      
      //set paper size
      spreadsheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
      
      //set display grid lines or not
      spreadsheet.setDisplayGridlines(true);
      
      //set print grid lines or not
      spreadsheet.setPrintGridlines(true);
      
      FileOutputStream out = new FileOutputStream(new File("printarea.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("printarea.xlsx written successfully"); 
   }
}

Vamos salvar o código acima como PrintArea.java. Compile e execute-o a partir do prompt de comando da seguinte maneira.

$javac PrintArea.java $java PrintArea

Irá gerar um arquivo chamado printarea.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

printarea.xlsx written successfully

No código acima, não adicionamos nenhum valor de célula. Conseqüentementeprintarea.xlsxé um arquivo em branco. Mas você pode observar na figura a seguir que a visualização da impressão mostra a área de impressão com linhas de grade.

Este capítulo explica como a biblioteca POI interage com um banco de dados. Com a ajuda do JDBC, você pode recuperar dados de um banco de dados e inseri-los em uma planilha usando a biblioteca POI. Vamos considerar o banco de dados MySQL para operações SQL.

Escreva no Excel a partir do banco de dados

Vamos supor a seguinte tabela de dados de funcionários chamada emp_tbl deve ser recuperado do banco de dados MySQL test.

EMP ID NOME EMP DEG SALÁRIO DEPTO
1201 Gopal Gerente técnico 45000 ISTO
1202 Manisha Leitor de prova 45000 Testando
1203 Masthanvali Escritor técnico 45000 ISTO
1204 Kiran Hr Admin 40.000 RH
1205 Kranthi Op Admin 30000 Admin

Use o código a seguir para recuperar dados de um banco de dados e inserir os mesmos em uma planilha.

import java.io.File;
import java.io.FileOutputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDatabase {
   public static void main(String[] args) throws Exception {
      Class.forName("com.mysql.jdbc.Driver");
      Connection connect = DriverManager.getConnection( 
         "jdbc:mysql://localhost:3306/test" , 
         "root" , 
         "root"
      );
      
      Statement statement = connect.createStatement();
      ResultSet resultSet = statement.executeQuery("select * from emp_tbl");
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("employe db");
      
      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell;
      cell = row.createCell(1);
      cell.setCellValue("EMP ID");
      cell = row.createCell(2);
      cell.setCellValue("EMP NAME");
      cell = row.createCell(3);
      cell.setCellValue("DEG");
      cell = row.createCell(4);
      cell.setCellValue("SALARY");
      cell = row.createCell(5);
      cell.setCellValue("DEPT");
      int i = 2;

      while(resultSet.next()) {
         row = spreadsheet.createRow(i);
         cell = row.createCell(1);
         cell.setCellValue(resultSet.getInt("eid"));
         cell = row.createCell(2);
         cell.setCellValue(resultSet.getString("ename"));
         cell = row.createCell(3);
         cell.setCellValue(resultSet.getString("deg"));
         cell = row.createCell(4);
         cell.setCellValue(resultSet.getString("salary"));
         cell = row.createCell(5);
         cell.setCellValue(resultSet.getString("dept"));
         i++;
      }

      FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("exceldatabase.xlsx written successfully");
   }
}

Vamos salvar o código acima como ExcelDatabase.java. Compile e execute-o a partir do prompt de comando da seguinte maneira.

$javac ExcelDatabase.java $java ExcelDatabase

Ele irá gerar um arquivo Excel chamado exceldatabase.xlsx em seu diretório atual e exiba a seguinte saída no prompt de comando.

exceldatabase.xlsx written successfully

o exceldatabase.xlsx arquivo tem a seguinte aparência.

ja/tutorial
es/tutorial
de/tutorial
fr/tutorial
th/tutorial
pt/tutorial
ru/tutorial
vi/tutorial
it/tutorial
ko/tutorial
tr/tutorial
id/tutorial
pl/tutorial
hi/tutorial
japost
espost
depost
frpost
thpost
ptpost
rupost
vipost
itpost
kopost
trpost
idpost
plpost
hipost

© Copyright 2021 - 2024 | All Rights Reserved