How to write Excel files in java using Apache POI

In this post, we will see how to write excel in java using Apache POI example.

The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java.

Some basics about Apache POI:

There are two prefixes which you encounter while reading/writing excel in java

HSSF: Used for dealing with files excel 2003 or earlier(.xls). Some of classes with HSSF prefix are HSSFWorkbook , HSSFSheet , HSSFRow and HSSFCell.

XSSF: Used for dealing with files excel 2007 or later(.xlsx). Some of classes with XSSF prefix are XSSFWorkbook , XSSFSheet , XSSFRow and XSSFCell.

Here are few classes which you need to aware of.

  • Workbook : This is high level class for representing excel workbook.
  • Sheet : This is high level class for representing excel sheet.
  • Row : This is high level class for representing excel row. It has methods which are related to row.
  • Cell: This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().

Dependency:

If you are using maven, then you need to add below dependency in pom.xml.
If you are not using maven, then you need to add below jars in classpath.

  • poi-3.13.jar
  • commons-codec-1.9.jar
  • poi-ooxml-3.13.jar
  • poi-ooxml-schemas-3.13.jar
  • xmlbeans-2.6.0.jar
  • stax-api-1.0.1.jar

write excel file using poi:

  1. Create a blank workbook
  2. XSSFWorkbook workbook = new XSSFWorkbook();

  3. Create a sheet and pass name of the sheet
  4. XSSFSheet sheet = workbook.createSheet(“Country”);

  5. Create row
  6. Row row = sheet.createRow(rownum++);

  7. Create cells, set its value and add cell to above row
  8. Cell cell = row.createCell(cellnum++); if(obj instanceof String) cell.setCellValue((String)obj); else if(obj instanceof Double) cell.setCellValue((Double)obj); else if(obj instanceof Integer) cell.setCellValue((Integer)obj);

  9. Repeat 3 and 4 until you have data

Java Program:

We are going to write excel file named  “CountriesDetails.xlsx” Create WriteExcelMain.java as below

When you run above program, you will get following output:

Lets see content of CountriesDetails.xlsx now.

Related Posts

  • 23 May

    Escape Apostrophe in JavaScript

    Table of ContentsWays to escape Apostrophe in JavaScriptUsing escape characterUsing alternate String syntaxUsing template literals In this article, we will see how to escape Apostrophe in JavaScript. Let’s see what happens if we have singe quote in String and we also use single quotes to declare the string in JavaScript. [crayon-628bf88da57b2306647744/] Output: unknown: Unexpected token […]

  • 23 May

    Escape Ampersand in URL in JavaScript

    In this post, we will see how to escape ampersand in JavaScript. To escape Ampersand in JavaScript, use encodeURIComponent() in JavaScript. [crayon-628bf88da5a1d073197406/] Output [crayon-628bf88da5a23806631882/] As you can see that when we called encodeURIComponent, it encoded & to %26. You can use % to escape characters that aren’t allowed in URLs. Here & is 26 in […]

  • 23 May

    TypeError: foreach is not a function in JavaScript

    Table of ContentsUse Object.keys() or Object.values() or Object.entries()Use Arrays.from() TypeError: .foreach is not a function occurs when we call foreach() function on object which is not array, Set or Map. To resolve this issue, you can use Object.keys() rather than directly calling foreach() on object or use Array.from() to convert array like object to array. […]

  • 23 May

    TypeError: toUpperCase is not a function in JavaScript

    TypeError: .toUpperCase is not a function occurs when we call toUpperCase() function on object which is not an string. toUpperCase() function can be only called on string. To resolve this issue, convert value to string using toString() ,method before calling toUpperCase() method. Let’s see with help of simple example [crayon-628bf88da6091997506166/] You will get following error […]

  • 23 May

    TypeError: split is not a function in JavaScript

    TypeError: .split is not a function occurs when we call split() function on object which is not an string. split() function can be only called on string. To resolve this issue, convert value to string using toString() ,method before calling split(). Let’s see with help of simple example. [crayon-628bf88da634d674884003/] Output [crayon-628bf88da6353685487919/] We got this error […]

  • 22 May

    TypeError: replace is not a function in JavaScript

    TypeError: .replace is not a function occurs when we call replace() function on object which is not an string. replace() function can be only called on string. To resolve this issue, convert value to string using toString() ,method before calling replace() method. Let’s see with help of simple example where we want to remove . […]

Comments

Leave a Reply

Your email address will not be published.

Subscribe to our newletter

Get quality tutorials to your inbox. Subscribe now.