Apache POI - Interview Questions


Advertisements

Dear readers, these Apache POI Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of Apache POI. As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer:

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program. It contains classes and methods to decode the user input data or a file into MS Office documents.

Components of Apache POI

Apache POI contains classes and methods to work on all OLE2 Compound documents of MS Office. The list of components of this API is given below.

  • POIFS (Poor Obfuscation Implementation File System) − This component is the basic factor of all other POI elements. It is used to read different files explicitly.

  • HSSF (Horrible Spreadsheet Format) − It is used to read and write xls format of MS-Excel files.

  • XSSF (XML Spreadsheet Format) − It is used for xlsx file format of MS-Excel.

  • HPSF (Horrible Property Set Format) − It is used to extract property sets of the MS-Office files.

  • HWPF (Horrible Word Processor Format) − It is used to read and write doc extension files of MS-Word.

  • XWPF (XML Word Processor Format) − It is used to read and write docx extension files of MS-Word.

  • HSLF (Horrible Slide Layout Format) − It is used for read, create, and edit PowerPoint presentations.

  • HDGF (Horrible DiaGram Format) − It contains classes and methods for MS-Visio binary files.

  • HPBF (Horrible PuBlisher Format) − It is used to read and write MS-Publisher files.

It is a high-level class under the org.apache.poi.hssf.usermodel package. It implements the Workbook interface and is used for Excel files in .xls format.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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");

The following code snippet is used to create a row.

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

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

XSSFCellStyle class is used to style a cell. Following code snippet can be used to set the cell alignment to "Top Left".

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

XSSFFont class is used to add a font to a cell. Following code snippet can be used to set the background color of a cell to "Green".

//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);

XSSFCellStyle class can be used to rotate a cell. Following code snippet can be used to set the cell text alignment to a specified angle.

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

XSSFCell.CELL_TYPE_FORMULA can be used to the cell as formula cell.

// Create SUM formula
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C2:C3)" );
cell = row.createCell(3);
cell.setCellValue("SUM(C2:C3)");

XSSFCell.CELL_TYPE_FORMULA can be used to the cell as formula cell.

// Create SUM formula
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("POWER(C2:C3)" );
cell = row.createCell(3);
cell.setCellValue("POWER(C2:C3)");

XSSFCell.CELL_TYPE_FORMULA can be used to the cell as formula cell.

// Create SUM formula
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("MAX(C2:C3)" );
cell = row.createCell(3);
cell.setCellValue("MAX(C2:C3)");

XSSFCell.CELL_TYPE_FORMULA can be used to the cell as formula cell.

// Create SUM formula
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("FACT(C2)" );
cell = row.createCell(3);
cell.setCellValue("FACT(C2)");

XSSFCell.CELL_TYPE_FORMULA can be used to the cell as formula cell.

// Create SUM formula
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SQRT(C2)" );
cell = row.createCell(3);
cell.setCellValue("SQRT(C2)");

XSSFHyperlink can be used to the add an hyperlink to a cell.

CreationHelper createHelper = workbook.getCreationHelper();
XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://www.tutorialspoint.com/" );
cell.setHyperlink((XSSFHyperlink) link);

Following code snippet demonstrate setting up the printable area of an excel using Apache POI.

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

What is Next?

Further, you can go through your past assignments you have done with the subject and make sure you are able to speak confidently on them. If you are fresher then interviewer does not expect you will answer very complex questions, rather you have to make your basics concepts very strong.

Second it really doesn't matter much if you could not answer few questions but it matters that whatever you answered, you must have answered with confidence. So just feel confident during your interview. We at tutorialspoint wish you best luck to have a good interviewer and all the very best for your future endeavor. Cheers :-)

apache_poi_questions_answers.htm
Advertisements