How to Create Different Types of Cells in a Spreadsheet using Java?
Apache POI is an API which was provided by Apache Foundation and is used to set the various type of values to the cell. The overloading function setCellValue() of Apache POI is used to set the value type of the cell. Maven is a powerful project management tool that is based on POM (project object model). It is used for project build, dependency, and documentation. It simplifies the build process like ANT. But it is too much advanced than ANT.
Algorithm: Steps To Create Different Types of Cells In A Spreadsheet
- Step 1: Create an instance of the workbook
- Step 2: Create a spreadsheet in the above workbook.
- Step 3: Create rows using XSSFRow
- Step 4: Writing the content to the workbook by defining the object of type FileOutputStream
- Step 5: Close the file
Procedure:
Step 1: Create a Maven project in eclipse and add Apache POI(used for setting value type of cells) dependencies to it or a Java project with Apache POI library installed.
Step 2: As in creating a Maven project, prerequisites are required to add the following Maven dependencies to pom.xml file, in order to use the Apache POI library. For these libraries search online for Maven Central Library and then search for Apache POI and get the dependencies or can also copy the following dependencies as shown below to the main java program.
XML
< dependencies > <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> < dependency > < groupId >org.apache.poi</ groupId > < artifactId >poi</ artifactId > < version >3.12</ version > </ dependency > <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> < dependency > < groupId >org.apache.poi</ groupId > < artifactId >poi-ooxml</ artifactId > < version >3.9</ version > </ dependency > </ dependencies > |
Step 3: After adding dependencies create a new class. For creating a new class go to src/main/java/package right-click and create a new class and name it, here for illustration purpose file is names as GFG. . In this class we will first create an instance of the workbook and with that instance, we will create a spreadsheet and in that spreadsheet, we will define the value type of cells. Below is the code snippet is given for the GFG class(taken here) as discussed above.
Implementation: Code snippet
Example: To create different types of cells in a spreadsheet
Java
// Java Program to create // different types of cells in a spreadsheet // Importing Maven dependencies // Importing Apache POI dependency files // Importing java input/output file and // Date class from java.util package import java.io.File; import java.io.FileOutputStream; import java.util.Date; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; 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; // Class having different types of cells public class GFG { // Main driver method public static void main(String[] args) throws Exception { // Step 1: Create an instance of the workbook XSSFWorkbook workbook = new XSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); // Step 2: Create a spreadsheet in the above // workbook // and name it as CellTypesSheet XSSFSheet spreadsheet = workbook.createSheet( "CellTypesSheet" ); // Step 3: Create rows using XSSFRow // We need to create row before creating cells, // row is a collection of cells XSSFRow noOfRows = spreadsheet.createRow(( short ) 2 ); // Creating cells // Custom inputs in cells // Cell 1 noOfRows.createCell( 0 ).setCellValue( "Cell Types" ); // Cell 2 noOfRows.createCell( 1 ).setCellValue( "Cell Value" ); // Above two cells(Cell Types and Cell Values) are // created at row 3 in excel and similarly for rest // of the cells noOfRows = spreadsheet.createRow(( short ) 3 ); // Cell 3 // Setting a Blank type cell noOfRows.createCell( 0 ).setCellValue( "Cell Type-BLANK" ); noOfRows.createCell( 1 ); noOfRows = spreadsheet.createRow(( short ) 4 ); // Cell 4 // Setting a Boolean type cell noOfRows.createCell( 0 ).setCellValue( "Cell Type-BOOLEAN" ); noOfRows.createCell( 1 ).setCellValue( true ); noOfRows = spreadsheet.createRow(( short ) 5 ); // Cell 5 // Setting a Error type cell noOfRows.createCell( 0 ).setCellValue( "Cell Type-ERROR" ); noOfRows.createCell( 1 ).setCellValue( XSSFCell.CELL_TYPE_ERROR); noOfRows = spreadsheet.createRow(( short ) 6 ); // Cell 6 // Setting a Date and Time type cell CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat( creationHelper.createDataFormat().getFormat( "d/m/y h:mm" )); noOfRows.createCell( 0 ).setCellValue( "Cell Type-DATE-TIME" ); noOfRows.createCell( 1 ).setCellValue( new Date()); noOfRows.getCell( 1 ).setCellStyle(cellStyle); noOfRows = spreadsheet.createRow(( short ) 7 ); // Cell 7 // Setting a Numeric type cell noOfRows.createCell( 0 ).setCellValue( "Cell Type-Numeric" ); noOfRows.createCell( 1 ).setCellValue( 35 ); noOfRows = spreadsheet.createRow(( short ) 8 ); // Cell 8 // Setting a String type cell noOfRows.createCell( 0 ).setCellValue( "Cell Type-String" ); noOfRows.createCell( 1 ).setCellValue( "w3wiki" ); // Step 4: Writing the content to the workbook // by defining object of type FileOutputStream FileOutputStream out = new FileOutputStream( new File( "GfgTypesOfCells.xlsx" )); workbook.write(out); // Step 5: Close the file out.close(); // Display message to be printed for successful // execution of program System.out.println( "GFG.xlsx Created Successfully" ); } } |
Output: A message will be printed in console as specified at last in the program that “GFG.xlsx Created Successfully” as the successful execution of the program responsible for changes in the spreadsheet below:
Contact Us