Export Data to Excel in Java

ExportDataToExcel_Java.md

Learn how to export data from Arrays, JSON, or CSV to Excel programmatically using Java:

You can export data to Excel programmatically as described below:

1. Export Array to Excel in Java
2. Generate Excel File in Java
3. Export ArrayList to Excel in Java
4. Collection of Custom Objects to Excel in Java
5. Export Data with Merged Cells in Java
6. Copy Rows and Columns from one Excel file to Another in Java
7. Export JSON Data to Excel in Java
8. Get CSV Data in Excel using Java

ExportDataToExcel_Java_CopyRowsAndColumns.java

// Open the source excel file.
Workbook srcWorkbook = new Workbook("C:\\Files\\Source_Workbook.xlsx");

// Instantiate the destination excel file.
Workbook destWorkbook = new Workbook();

// Get the first worksheet of the source workbook.
Worksheet srcWorksheet = srcWorkbook.getWorksheets().get(0);

// Get the first worksheet of the destination workbook.
Worksheet desWorksheet = destWorkbook.getWorksheets().get(0);

// Copy all the rows of the first worksheet of source Workbook to
// the first worksheet of destination Workbook.
desWorksheet.getCells().copyRows(srcWorksheet.getCells(), 0, 0, srcWorksheet.getCells().getMaxDisplayRange().getRowCount());

// Save the excel file.
destWorkbook.save("C:\\Files\\Destination_Workbook.xlsx");	

ExportDataToExcel_Java_CopySpecificRows.java

// Open the source excel file.
Workbook srcWorkbook = new Workbook("C:\\Files\\Source_Workbook.xlsx");

// Instantiate the destination excel file.
Workbook destWorkbook = new Workbook();

// Get the first worksheet of the source workbook.
Worksheet srcWorksheet = srcWorkbook.getWorksheets().get(0);

// Get the first worksheet of the destination workbook.
Worksheet desWorksheet = destWorkbook.getWorksheets().get(0);

// Copy the second row of the source Workbook to the first row of destination Workbook.
desWorksheet.getCells().copyRow(srcWorksheet.getCells(), 1, 0);

// Copy the fourth row of the source Workbook to the second row of destination Workbook.
desWorksheet.getCells().copyRow(srcWorksheet.getCells(), 3, 1);

// Save the excel file.
destWorkbook.save("C:\\Files\\Destination_Workbook.xlsx");

ExportDataToExcel_Java_Export2DArrayToExcel.java

// Initialize a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

// Creating a two-dimensional array of integers
int[][] array2D = { 
  { 1, 2 }, 
  { 3, 4 }, 
  { 5, 6 }, 
  { 7, 8 } 
};

// Exporting the array of names to first row and first column vertically
worksheet.getCells().importArray(array2D, 0, 0);

// Saving the Excel file
workbook.save("C:\\Files\\output.xlsx");

ExportDataToExcel_Java_ExportArrayListToExcel.java

// Initialize a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

// Instantiating an ArrayList object
ArrayList<String> list = new ArrayList<String>();

// Add few names to the list as string values
list.add("Laurence Chen");
list.add("Roman Korchagin");
list.add("Kyle Huang");
list.add("Tommy Wang");

// Exporting the contents of ArrayList vertically at the first row and first column of the worksheet. 
worksheet.getCells().importArrayList(list, 0, 0, true);

// Saving the Excel file
workbook.save("C:\\Files\\Output.xlsx");

ExportDataToExcel_Java_ExportArrayToExcel.java

// Initialize a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);

// Creating an array containing names as string values
String[] names = new String[] { "Laurence Chen", "Roman Korchagin", "Kyle Huang" };

// Exporting the array of names to first row and first column vertically
worksheet.getCells().importArray(names, 0, 0, true);

// Saving the Excel file
workbook.save("C:\\Files\\output.xlsx");

ExportDataToExcel_Java_ExportCollectionOfObjectsToExcel.java

// Initialize a new Workbook
Workbook book = new Workbook();

// Obtaining the reference of the worksheet
Worksheet sheet = book.getWorksheets().get(0);

// Define an ArrayList of Persons
List<Person> list = new ArrayList<Person>();

list.add(new Person("Mike", 25, "Software Engineer"));
list.add(new Person("Steve", 30, "Doctor"));
list.add(new Person("Billy", 35, "Teacher"));

// We pick only Name and Age columns, not all, to export to the worksheet         
sheet.getCells().importCustomObjects((Collection)list,
    new String[] { "Name", "Age" }, // propertyNames
    true, // isPropertyNameShown
    0, // firstRow
    0, // firstColumn
    list.size(), // Number of objects to be exported
    true, // insertRows
    null, // dateFormatString
    false); // convertStringToNumber

// Save the Excel file
book.save("C:\\Files\\Output.xlsx");

ExportDataToExcel_Java_ExportCSVDataToExcel.java

// Initialize LoadOptions with CSV LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);

// Open CSV file as a Workbook object
Workbook workbook = new Workbook("C:\\Files\\Sample.csv", loadOptions);

// Save the file as an Excel Documnt
workbook.save("C:\\Files\\Output.xlsx");

ExportDataToExcel_Java_ExportDataWithMergedCells.java

// Opening an existing Workbook.
Workbook workbook = new Workbook("C:\\Files\\SampleMergedTemplate.xlsx");

// Obtaining the reference of the worksheet
Worksheet sheet = workbook.getWorksheets().get(0);

// Instantiating an ArrayList object
List<Product> productList = new ArrayList<Product>();

// Creating a collection of Products
for (int i = 0; i < 3; i++)
{
  Product product = new Product(i, "Product - " + i);
    productList.add(product);
}

// Define Table import options
ImportTableOptions tableOptions = new ImportTableOptions();

// Set CheckMergedCells property to true
tableOptions.setCheckMergedCells(true);
tableOptions.setFieldNameShown(false);

// Export data to excel template (in second row, first column)           
sheet.getCells().importCustomObjects((Collection)productList, 1, 0, tableOptions);

// Save the Excel file
workbook.save("C:\\Files\\Output.xlsx", SaveFormat.XLSX);

ExportDataToExcel_Java_ExportJsonDataToExcel.java

// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

// Read JSON file
File file = new File("C:\\Files\\sample.json");
BufferedReader bufferedReader = new BufferedReader(new FileReader(file));
    String jsonInput = "";
    String tempString;
    while ((tempString = bufferedReader.readLine()) != null) {
      jsonInput = jsonInput + tempString; 
    }
    bufferedReader.close();

// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.createStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.getFont().setColor(Color.getCyan());
style.getFont().setBold(true);

// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.setTitleStyle(style);
options.setArrayAsTable(true);

// Export JSON Data
JsonUtility.importData(jsonInput, worksheet.getCells(), 0, 0, options);

// Save Excel file
workbook.save("C:\\Files\\Output.xlsx");

generate-excel.java

import com.aspose.cells.Cells;
import com.aspose.cells.License;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class GenerateExcelFileInJava {

	public static void main(String[] args) throws Exception {
	// Instantiate Aspose.Cells license to avoid trial version watermark
	License license = new License(); 
	license.setLicense("Aspose.Cells.lic");

        // Instantiate a new Excel workbook instance
        Workbook ExcelWorkbook = new Workbook();

        // Get reference to first worksheet in the workbook
        Worksheet ExcelWorksheet = ExcelWorkbook.getWorksheets().get(0);

        // Get reference to Cells collection in the first worksheet
        Cells WorksheetCells = ExcelWorksheet.getCells();

        // Insert data into the worksheet using the cells collection
        WorksheetCells.get("A1").putValue("Customers Report");
        WorksheetCells.get("A2").putValue("C_ID");
        WorksheetCells.get("B2").putValue("C_Name");
        WorksheetCells.get("A3").putValue("C001");
        WorksheetCells.get("B3").putValue("Customer1");
        WorksheetCells.get("A4").putValue("C002");
        WorksheetCells.get("B4").putValue("Customer2");
        WorksheetCells.get("A5").putValue("C003");
        WorksheetCells.get("B5").putValue("Customer3");
        WorksheetCells.get("A6").putValue("C004");
        WorksheetCells.get("B6").putValue("Customer4");
                    
        // Save the workbook as XLSX
        ExcelWorkbook.save("ExcelFile.xlsx");
	}
}