Export Data to Excel in Java

Export Data to Excel in Java

You can easily export data to Microsoft Excel from various available sources such as JSON, and CSV. As a Java developer, you can export data from Arrays, List of objects, JSON, and CSV to Excel documents programmatically. In this article, you will learn how to export data to Excel using Java.

The following topics are discussed/covered in this article:

Java API to Export Data

For exporting data to Excel, I will be using Aspose.Cells for Java API. It is a powerful spreadsheet manipulation API that lets you create, edit, or convert Excel files within Java applications. The API enables you to perform Excel automation features programmatically without needing a Microsoft Excel application.

You can download the JAR of the API or just add the following pom.xml configuration in your Maven-based Java application to try the below-mentioned code examples.

<repository>
    <id>AsposeJavaAPI</id>
    <name>Aspose Java API</name>
    <url>https://repository.aspose.com/repo/</url>
</repository>
<dependency>
    <groupId>com.aspose</groupId>
    <artifactId>aspose-cells</artifactId>
    <version>21.8</version>
</dependency>

Export Array to Excel in Java

You can easily export data from a one-dimensional array or a two-dimensional array to an Excel document. The array can be of a reference type or a value type. You can export data from an array to Excel by following the simple steps mentioned below:

  • Create an instance of the Workbook class
  • Get worksheet in an instance of the Worksheet class
  • Create an array containing string values
  • Call the importArray() method with the array
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to export an array of strings to Excel using Java.

Export Array to Excel in Java
Export Array to Excel in Java

The Workbook class of the API is the main class to create an Excel spreadsheet. It provides functionality to open and save native excel files. The save() method of this class is used to save the output file at the specified file path.

The Worksheet class represents a single worksheet and provides functionality to work with cells and rows.

The importArray() method of the Cells class exports an array of strings to a worksheet. It takes the following input parameters:

  • stringArray: The array of String values
  • firstRow: The row number of the first cell to export to
  • firstColumn: The column number of the first cell to export to
  • isVertical: It specifies whether to export data vertically or horizontally

The API also provides the overloaded versions of the importArray() method to export an array of integer or double to a worksheet.

Export Two Dimensional Array to Excel

Similarly, you can export a two-dimensional array to an Excel file. The following code sample shows how to export a two-dimensional array to an Excel file in Java.

Export Two Dimensional Array to Excel
Export Two Dimensional Array to Excel

The Cells class provides importArray() method to export a two-dimension array of integers to a worksheet. The API also provides the overloaded versions of this method to export a two-dimensional array of strings or double into a worksheet.

Export ArrayList to Excel in Java

You can export data from an ArrayList to Excel by following the steps mentioned below:

  • Create an instance of the Workbook class
  • Get worksheet in an instance of the Worksheet class
  • Create an array list containing string values
  • Call the importArrayList() method with the array list
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to export an ArrayList to Excel in Java.

Export Array List to Excel in Java
Export ArrayList to Excel in Java

The importArrayList() method of the Cells class exports an ArrayList of data to a worksheet. It takes four parameters including the ArrayList of data. The other parameters are the firstRow, firstColumn, and isVertical.

Export Collection of Custom Objects to Excel in Java

You can export data from a collection of custom objects to Excel by following the steps mentioned below:

  • Create an instance of the Workbook class
  • Get worksheet in an instance of the Worksheet class
  • Create an array list of custom objects
  • Call the importCustomObjects() method with the array list
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to export a collection of custom objects to Excel in Java.

Collection of Custom Objects to Excel in Java
Collection of Custom Objects to Excel in Java

The importCustomObjects() method of the Cells class exports a list of custom objects and takes the following parameters. The API also provides the overloaded version of this method that takes fewer parameters.

  • list: The collection of custom objects
  • propertName: Specify name for a specific property to export. If it is null, it will export all properties of the object
  • isPropertyNameShown: Indicates whether the property name will be exported to the first row
  • firstRow: The row number of the first cell to export
  • firstColumn: The column number of the first cell to export
  • rowNumber: Number of rows to be exported
  • insertRows: Indicates whether extra rows are added to fit data
  • dataFormatString: Date format string for cells
  • convertStringToNumber: Indicates if this method will try to convert string to number.

Export Data to Excel with Merged Cells in Java

You can export data from a collection of objects to a worksheet containing merged cells by following the steps mentioned below:

  • Create an instance of the Workbook class with template file path
  • Get worksheet in an instance of the Worksheet class
  • Create an array list of objects
  • Create an instance of the ImportTableOptions class
  • Call the importCustomObjects() method with the array list
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to export a collection of custom objects to an Excel worksheet with merged cells in Java.

Export Data to Excel with Merged Cells in Java
Export Data to Excel with Merged Cells in Java

The ImportTableOptions class provides several options for exporting data into cells. The setCheckMergedCells indicates whether checking merged cells. The setFieldNameShown property indicates whether the field name should be exported or not.

Copy Rows and Columns from one Excel file to Another in Java

You can easily copy rows and columns from one Excel file to another file programmatically by following the steps mentioned below:

  • Create an instance of the Workbook class with source workbook input file
  • Create an instance of the Workbook class for destination workbook
  • Get source and destination worksheets in seperate instances of the Worksheet class
  • Call the copyRows() method of the destination worksheet with the source worksheet cells
  • Save the destination workbook output file by calling the save() method of the Workbook class

The following code sample shows how to copy rows and columns from one Excel file to another using Java.

Copy Rows and Columns from one Excel file to Another in Java
Copy Rows and Columns from one Excel file to Another in Java

You can copy specific rows from one Excel file to another. The following code sample shows how to copy specific rows from one Excel file to another using Java.

Copy Specific Rows and Columns from one Excel file to Another in Java
Copy Specific Rows and Columns from one Excel file to Another in Java

The copyRows() method copies data and formats of whole rows. It takes the source worksheet cells to copy as input parameters along with the source row index, destination row index, and the copied row number. The API also provides the overloaded versions of this method to copy rows with CopyOptions and PasteOptions.

Similarly, You can copy columns data from one Microsoft Excel document to another using the copyColumn() or copyColumns() methods.

Export JSON Data to Excel in Java

You can easily export data from a JSON file to Excel by following the steps mentioned below:

  • Create an instance of the Workbook class
  • Get worksheet in an instance of the Worksheet class
  • Read the JSON file
  • Create an instance of the CellsFactory class
  • Initiate style by calling the createStyle() method
  • Set various style properties such as Horizontal Alignment, Font color, etc.
  • Create an instance of the JsonLayoutOptions class
  • Set title style with the style object
  • Set array as table property to true
  • Call the JsonUtility.importData() method with the JSON input and JsonLayoutOptions
  • Save the output file by calling the save() method of the Workbook class

The following code sample shows how to export data from a JSON file to Excel using Java.

{
  "quiz": {
    "sport": {
      "q1": {
        "question": "Which one is correct team name in NBA?",
        "answer": "Huston Rocket"
      }
    },
    "maths": {
      "q1": {
        "question": "5 + 7 = ?",
        "answer": "12"
      },
      "q2": {
        "question": "12 - 8 = ?",
        "answer": "4"
      }
    }
  }
}
Export JSON Data to Excel in Java
Export JSON Data to Excel in Java

The CellsFactory class instantiates classes of Cells model. The createStyle() method of this class creates a new style object of the Style class. The Style class allows setting display style of Excel document, such as font, color, alignment, border, etc.

The JsonLayoutOptions class provides the options of JSON layout type. The setTitleStyle method of this class is used to set the defined style of the title. The setArrayAsTable method allows processing Array as a table.

The API provides JsonUtility class to process the JSON. The importData() method of this class exports the JSON string and takes the following parameters:

  • json: The JSON string
  • cells: The Cells
  • row: The row index
  • column: The column index
  • option: The options to export JSON string

Get CSV Data in Excel using Java

You can export data from a CSV file to Excel by following the simple steps mentioned below:

  • Create an instance of the LoadOptions class wih LoadFormat
  • Create an instance of the Workbook class with CSV file path and LoadOptions object
  • Call the save() method of the Workbook class and save the output file

The following code sample shows how to export data from a CSV file to Excel using Java.

id,language,edition,author,streetAddress,city,state,postalCode
01,Java,third,Herbert Schildt,126,San Jone,CA,394221
02,C++,second,EAAAA,126,San Jone,CA,394221
03,.Net,second,E.Balagurusamy,126,San Jone,CA,394221
Export CSV Data to Excel in Java
Export CSV Data to Excel in Java

The LoadOptions class of the API provides options for loading the file. The LoadFormat class contains constants representing the load file formats.

Get a Free License

You can try the API without evaluation limitations by requesting a free temporary license.

Conclusion

In this article, you have learned how to export data to Excel in Java. You have also learned how to export data from Arrays, JSON, or CSV files to Excel programmatically. Moreover, you have learned how to copy rows and columns from one Excel file to another file using Java. You can learn more about Aspose.Cells for Java API using the documentation. In case of any ambiguity, please feel free to contact us on the forum.

See Also

Frequently Asked Questions

How to export data to an XLSX file in Java?

You can easily export data from Arrays, Collection of Objects, JSON, and CSV to an XLSX file using easy-to-integrate Aspose.Cells for Java API in your Java application.

How do I export data from JSON to Excel?

Aspose.Cells API provides JsonUtility to export data from a JSON file to Excel in Java. You can find simple steps under the “Export JSON Data to Excel in Java” section.

How do I export data from CSV to Excel in Java?

You can simply load a CSV file and save it as XLSX using Aspose.Cells API. You can find simple steps under the “Get CSV Data in Excel using Java” section.