Skip to content

Instantly share code, notes, and snippets.

@conholdate-gists
Last active February 19, 2024 09:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save conholdate-gists/335b0ec29e791a6df12fbe54ce4c93ff to your computer and use it in GitHub Desktop.
Save conholdate-gists/335b0ec29e791a6df12fbe54ce4c93ff to your computer and use it in GitHub Desktop.
Export Data to Excel in Java

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. Export ArrayList to Excel in Java
  3. Collection of Custom Objects to Excel in Java
  4. Export Data with Merged Cells in Java
  5. Copy Rows and Columns from one Excel file to Another in Java
  6. Export JSON Data to Excel in Java
  7. Get CSV Data in Excel using 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");
// 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");
// 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");
// 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");
// 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");
// 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");
// 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");
// 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);
// 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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment