Creating xlsx file in java

How to Write Excel Files in Java using Apache POI

In this tutorial, I’d love to share with you guys some examples of writing data to Excel files using the Apache POI library. If today is the first day you get to know Apache POI and you haven’t written any code snippet to read/write Excel files yet, I recommend you to read the sections 1 and 2 in the tutorial How to Read Excel Files in Java using Apache POI to understand the fundamentals of Apache POI.

Or if you are a kind of person who likes getting your hands-dirty first, let jump directly into the following examples.

1. Apache POI API Basics for Writing Excel Files

The fundamental interfaces include Workbook , Sheet , Row and Cell . For basic formatting, use the CellStyle and Font interfaces. Concrete implementing classes include:

• Excel 2003: HSSFWorkbook , HSSFSheet , HSSFRow , HSSFCell , etc.
• Excel 2007: XSSFWorkbook , XSSFSheet , XSSFRow , XSSFCell , etc.

Here are the basic steps for writing an Excel file:

1. Create a Workbook .
2. Create a Sheet .
3. Repeat the following steps until all data is processed:
   1. Create a Row .
   2. Create Cells in a Row . Apply formatting using CellStyle .

   2. A Simple Example to create an Excel file in Java

   The following code snippet is a very simple program that demonstrates writing a list of books to an Excel file in the simplest and dirty form:

   package net.codejava.excel; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * A very simple program that writes some data to an Excel file * using the Apache POI library. * @author www.codejava.net * */ public class SimpleExcelWriterExample < public static void main(String[] args) throws IOException < XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Java Books"); Object[][] bookData = < , , , , >; int rowCount = 0; for (Object[] aBook : bookData) < Row row = sheet.createRow(++rowCount); int columnCount = 0; for (Object field : aBook) < Cell cell = row.createCell(++columnCount); if (field instanceof String) < cell.setCellValue((String) field); >else if (field instanceof Integer) < cell.setCellValue((Integer) field); >> > try (FileOutputStream outputStream = new FileOutputStream("JavaBooks.xlsx")) < workbook.write(outputStream); >> >

   This program creates an Excel 2007 document which looks like the following screenshot (File: JavaBooks.xlsx ):

   

   3. A More Object-Oriented Example to create an Excel file in Java

   The following code snippets demonstrate a nicer program that focuses on OOP approach. That makes the program more flexible and easy to extend or upgrade in the future.

   Create the model class ( Book.java ):

   package net.codejava.excel; public class Book < private String title; private String author; private float price; public Book() < >public Book(String title, String author, double price) < this.title = title; this.author = author; this.price = price; >// getters and setters >

   public void writeExcel(List listBook, String excelFilePath) throws IOException < Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); int rowCount = 0; for (Book aBook : listBook) < Row row = sheet.createRow(++rowCount); writeBook(aBook, row); >try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) < workbook.write(outputStream); >>

   private void writeBook(Book aBook, Row row)

   private List getListBook() < Book book1 = new Book("Head First Java", "Kathy Serria", 79); Book book2 = new Book("Effective Java", "Joshua Bloch", 36); Book book3 = new Book("Clean Code", "Robert Martin", 42); Book book4 = new Book("Thinking in Java", "Bruce Eckel", 35); ListlistBook = Arrays.asList(book1, book2, book3, book4); return listBook; >

   NiceExcelWriterExample excelWriter = new NiceExcelWriterExample(); List listBook = excelWriter.getListBook(); String excelFilePath = "NiceJavaBooks.xls"; excelWriter.writeExcel(listBook, excelFilePath);

   4. Formatting Cells of the Excel file

   Of course you may need to format the Excel file to make it looks nicely and professionally. Formatting is diversity and quite complex so in this introductory tutorial, I just show you how to format the basics like setting font style. Here are the steps:

   Create a CellStyle object what holds formatting information:

   CellStyle cellStyle = sheet.getWorkbook().createCellStyle();

   cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFont(font); cellStyle.setWrapText(true);

   private void createHeaderRow(Sheet sheet)

   You can see the complete program ( FormattedExcelWriterExample.java ) which can be found in the source code attached to this article.

   5. Writing both Excel 2003 and Excel 2007 formats in Java

   For better flexibility (supporting both common Excel formats), I recommend writing a factory method that either returns a HSSFWorkbook or XSSFWorkbook , depending on the extension of the file ( .xls or .xlsx ). Here’s the method:

   private Workbook getWorkbook(String excelFilePath) throws IOException < Workbook workbook = null; if (excelFilePath.endsWith("xlsx")) < workbook = new XSSFWorkbook(); >else if (excelFilePath.endsWith("xls")) < workbook = new HSSFWorkbook(); >else < throw new IllegalArgumentException("The specified file is not Excel file"); >return workbook; >

   You can see the complete program ( FlexibleExcelWriterExample.java ) which can be found in the source code attached to this article.

   That’s how to read Excel files in Java programmatically. To learn more in-depth about Java programming, this Java software developers course would be good choice.

   Related Java Excel Tutorials:

   References

   About the Author:

   

   Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

   Add comment

   Comments

   i have copied the upper first simplest code but its not running its giving me this error «ERROR StatusLogger Log4j2 could not find a logging implementation. Please add log4j-core to the classpath. Using SimpleLogger to log to the console.
   «

   Источник

   Создание Excel файла в Java с помощью Apache POI

   

   В прошлых двух статьях мы познакомились с библиотекой Apache POI, а также разобрались со считыванием данных из Excel документов в форматах .xls и .xlsx . Сегодня мы продолжим изучение возможностей этой библиотеки и попробуем создать новый Excel файл с формулами и стилями. Скачать проект Вы сможете в конце статьи.

   Если Вы еще не знакомы с Apache POI, то рекомендую вкратце ознакомится с ее возможностями и способами ее подключения в проект по этой ссылке.

   Создание нового Excel файла в Java

   Внимание, код не очень красив и оптимизирован. Я хотел просто продемонстрировать возможности этой удобной библиотеки.

   Для начала давайте создадим простой xls файл и запишем в него какие-то данные. А далее будем применять к нему стили и добавлять формулы.

   Для удобной работы с данными нам потребуется дополнительный класс, который будет представлять собой модель данных, которую мы будем записывать в файл:

   Как видим, это простой класс с полями для имени, фамилии, города и зарплаты какого-то человека.

   Ниже представлен листинг класса, в котором создается сам Excel файл:

   try ( FileOutputStream out = new FileOutputStream ( new File ( «F:\\Apache POI Excel File.xls» ) ) )

   Обратите внимание, что мы использовали try with resources — одну из особенностей Java 7. А это значит, что нам не нужно беспокоится о закрытии файла вручную. В Java 7 конструкцию try-catch-finally можно не использовать, так как ей на смену пришла try with resources, которая сама закрывает открытые файлы или потоки без вашего вмешательства.

   После запуска приведенной выше программы, в корне проекта создастся файл с названием Apache POI Excel File.xls. Давайте посмотрим на его содержимое:

   

   Лист Excel файла называется «Просто лист», как мы и называли, а данные расположены правильно.

   Добавление стилей в Excel документ на Java

   Теперь давайте поупражняемся с обновлением файла, а именно добавлением стилей. Например, выделим имя столбцов из первой строки жирным.

   Для этого нам понадобится еще один метод setBoldStyle():

   Источник

   Create Excel XLS Files in Java Without MS Office

   Are you looking for an ultimate solution to create Excel files from within your Java application? If yes, this is the right place for you to learn how to create Excel XLS/XLSX files in Java in a few simple steps.

   

   MS Excel provides an easy way to keep and share a large amount of tabular data. In addition, you can perform various operations such as applying formulas, generating charts and graphs, sorting and filtering data, and so on. So let’s have a look at how to create Excel XLSX or XLS files from scratch in Java. In addition, we will cover how to update an existing Excel file, generate charts, apply formulas, and add pivot tables in Excel worksheets.

   Java Library to Create Excel Files#

   Aspose.Cells for Java is a powerful spreadsheet manipulation library that lets you create or modify Excel files without MS Office. The library supports adding charts, graphs, formulas, and perform other spreadsheet manipulation operations programmatically. You can download the library for free or install it within your Maven-based applications.

    AsposeJavaAPI Aspose Java API https://repository.aspose.com/repo/  

    com.aspose aspose-cells 23.4  

   Create an Excel File in Java#

   The MS Excel files are referred to as workbooks and each workbook is composed of one or more worksheets. The worksheets further contain the rows and columns to keep the data in the form of cells. So let’s start by creating a simple workbook. The following are the steps to create an Excel XLSX file from scratch.

   • Create an instance of Workbook class.
   • Access the desired worksheet using Workbook.getWorksheets.get() method.
   • Put the value in the desired cell in the worksheet using the cell’s identifier, such as A1, B3, etc.
   • Save the workbook as an Excel file using the Workbook.save() method.

   The following code sample shows how to create an Excel XLSX file in Java.

   

   Edit an Excel XLS in Java#

   Lets now have a look at how to modify or insert data into an existing MS Excel file. For this, you can simply load the file, access the desired worksheet and save the updated file. The following are the steps to modify an existing Excel file.

   • Open Excel file using Workbook class.
   • Access the worksheets and cells using the Worksheet and Cell classes respectively.
   • Save the updated workbook as an Excel .xlsx file.

   The following code sample shows how to edit an Excel file in Java.

   Add Charts or Graphs in Excel Files#

   Charts in spreadsheets are used to visually represent the data stored in the worksheets. They make it easier to analyze a large amount of data quite easily. Aspose.Cells for Java provides a wide range of charts that you can create within the Excel files programmatically. The following are the steps to create an Excel file having chart in Java.

   • Create a new Excel file or load an existing one using Workbook class.
   • Add data to the worksheet (optional).
   • Get the chart collection of the worksheet using the Worksheet.getCharts() method.
   • Add a new chart using Worksheet.getCharts().add() method.
   • Get the newly created chart from the collection.
   • Specify the cells’ range to set NSeries for the chart.
   • Save the workbook as an Excel .xlsx file.

   The following code sample shows how to create Excel XLSX with a chart in Java.

   

   Create a Pivot Table in Excel XLSX#

   Pivot tables in Excel worksheets have various purposes such as adding filters to the data, computing totals, summarizing data, and etc. Pivot tables can be created using the range of the cells in the worksheet. The following are the steps to create a pivot table in an Excel XLS file in Java.

   • Create a new Workbook or load an existing file.
   • Insert data into the worksheet (optional).
   • Access the pivot table collection using Worksheet.getPivotTables() method.
   • Add a new pivot table in the worksheet using Worksheet.getPivotTables().add() method.
   • Provide data to the pivot table.
   • Save the workbook.

   The following code sample shows how to create pivot table in Excel XLS in Java.

   

   Create an Excel File having Formulas for Cells#

   Aspose.Cells for Java also allows you to work with formulas in the Excel worksheets. You can apply the built-in as well as add-in functions to the cells.

   Apply Built-in Functions in Excel#

   For using the built-in functions, you can simply access the desired cell in the worksheet and add formula using the Cell.setFormula(String) method.

   The following code sample shows how to set a built-in formula using Java.

   Add Add-in Functions in Excel#

   There might be a case when you have to use a user-defined function. For this, you will have to register the add-in function using a .xlam (Excel macro-enabled add-in) file and then use it for the desired cells. For registering the add-in functions, Aspose.Cells for Java provides registerAddInFunction(int, String) and registerAddInFunction(String, String, boolean) methods.

   The following code sample shows how to register and use an add-in function using Java.

   Create Excel Files Online for Free#

   We also provide an online Excel editor for you to create and edit Excel files. You can use this powerful spreadsheet editor without creating an account.

   Java Excel Library — Get a Free License#

   You can use the library without evaluation limitations by getting a free temporary license.

   Conclusion#

   In this article, you have seen how to create MS Excel files from scratch using Java without MS Office. You have also learned how to update workbooks, create charts, add tables, and apply formulas to cell values within MS Excel worksheets. In addition, we have provided you with a free web-based Excel editor that you can use to create and edit Excel sheets online.

   You can learn more about Aspose’s Java Excel library using documentation.

   See Also#

   Источник