Create and write to Excel file in Java
GemBox.Spreadsheet for Java can create an Excel file in many formats (including XLSX, XLS, ODS, CSV and HTML) in the same manner. Specifically, it uses a ExcelFile.Save method to write to an Excel file or stream in Java.
The following example shows how you can write typical table data to an
GemBox.Spreadsheet for Java has been retired
object to create a new Excel file. It also shows how to merge cells, specify column properties, and set ExcelCell.setValue and CellStyle.
import com.gembox.spreadsheet.*;
import java.awt.*;
import java.util.EnumSet;
class Program {
public static void main(String[] args) throws java.io.IOException {
// If using the Professional version, put your serial key below.
SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.addWorksheet("Writing");
// Tabular sample data for writing into an Excel file.
Object[][] skyscrapers = {
{ "Rank", "Building", "City", "Country", "Metric", "Imperial", "Floors", "Built (Year)" },
{ 1, "Burj Khalifa", "Dubai", "United Arab Emirates", 828, 2717, 163, 2010 },
{ 2, "Shanghai Tower", "Shanghai", "China", 632, 2073, 128, 2015 },
{ 3, "Abraj Al-Bait Clock Tower", "Mecca", "Saudi Arabia", 601, 1971, 120, 2012 },
{ 4, "Ping An Finance Centre", "Shenzhen", "China", 599, 1965, 115, 2017 },
{ 5, "Lotte World Tower", "Seoul", "South Korea", 554.5, 1819, 123, 2016 },
{ 6, "One World Trade Center", "New York City", "United States", 541.3, 1776, 104, 2014 },
{ 7, "Guangzhou CTF Finance Centre", "Guangzhou", "China", 530, 1739, 111, 2016 },
{ 7, "Tianjin CTF Finance Centre", "Tianjin", "China", 530, 1739, 98, 2018 },
{ 9, "China Zun", "Beijing", "China", 528, 1732, 108, 2018 },
{ 10, "Taipei 101", "Taipei", "Taiwan", 508, 1667, 101, 2004 },
{ 11, "Shanghai World Financial Center", "Shanghai", "China", 492, 1614, 101, 2008 },
{ 12, "International Commerce Centre", "Hong Kong", "China", 484, 1588, 118, 2010 },
{ 13, "Lakhta Center", "St. Petersburg", "Russia", 462, 1516, 86, 2018 },
{ 14, "Landmark 81", "Ho Chi Minh City", "Vietnam", 461.2, 1513, 81, 2018 },
{ 15, "Changsha IFS Tower T1", "Changsha", "China", 452.1, 1483, 88, 2017 },
{ 16, "Petronas Tower 1", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
{ 16, "Petronas Tower 2", "Kuala Lumpur", "Malaysia", 451.9, 1483, 88, 1998 },
{ 16, "The Exchange 106", "Kuala Lumpur", "Malaysia", 451.9, 1483, 97, 2018 },
{ 19, "Zifeng Tower", "Nanjing", "China", 450, 1476, 89, 2010 },
{ 19, "Suzhou IFS", "Suzhou", "China", 450, 1476, 92, 2017 }
};
worksheet.getCell(0, 0).setValue("Example of writing typical table - tallest buildings in the world (2004):");
// Column width of 8, 30, 16, 20, 9, 11, 9, 9, 4 and 5 characters.
worksheet.getColumn("A").setWidth(8, LengthUnit.ZERO_CHARACTER_WIDTH); // Rank
worksheet.getColumn("B").setWidth(30, LengthUnit.ZERO_CHARACTER_WIDTH); // Building
worksheet.getColumn("C").setWidth(16, LengthUnit.ZERO_CHARACTER_WIDTH); // City
worksheet.getColumn("D").setWidth(20, LengthUnit.ZERO_CHARACTER_WIDTH); // Country
worksheet.getColumn("E").setWidth(9, LengthUnit.ZERO_CHARACTER_WIDTH); // Metric
worksheet.getColumn("F").setWidth(11, LengthUnit.ZERO_CHARACTER_WIDTH); // Imperial
worksheet.getColumn("G").setWidth(9, LengthUnit.ZERO_CHARACTER_WIDTH); // Floors
worksheet.getColumn("H").setWidth(9, LengthUnit.ZERO_CHARACTER_WIDTH); // Built (Year)
worksheet.getColumn("I").setWidth(4, LengthUnit.ZERO_CHARACTER_WIDTH);
worksheet.getColumn("J").setWidth(5, LengthUnit.ZERO_CHARACTER_WIDTH);
int i, j;
// Write header data to Excel cells.
for (j = 0; j < 7; j++)
worksheet.getCell(3, j).setValue(skyscrapers[0][j]);
worksheet.getCells().getSubrange("A3:A4").setMerged(true); // Rank
worksheet.getCells().getSubrange("B3:B4").setMerged(true); // Building
worksheet.getCells().getSubrange("C3:C4").setMerged(true); // City
worksheet.getCells().getSubrange("D3:D4").setMerged(true); // Country
worksheet.getCells().getSubrange("E3:F3").setMerged(true); // Height
worksheet.getCell("E3").setValue("Height");
worksheet.getCells().getSubrange("G3:G4").setMerged(true); // Floors
worksheet.getCells().getSubrange("H3:H4").setMerged(true); // Built (Year)
CellStyle style = new CellStyle();
style.setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);
style.setVerticalAlignment(VerticalAlignmentStyle.CENTER);
style.getFillPattern().setSolid(SpreadsheetColor.fromColor(Color.ORANGE));
style.getFont().setWeight(ExcelFont.BOLD_WEIGHT);
style.getFont().setColor(SpreadsheetColor.fromColor(Color.WHITE));
style.setWrapText(true);
style.getBorders().setBorders(EnumSet.of(MultipleBorders.RIGHT, MultipleBorders.TOP), SpreadsheetColor.fromColor(Color.BLACK), LineStyle.THIN);
worksheet.getCells().getSubrange("A3:H4").setStyle(style);
style = new CellStyle();
style.setHorizontalAlignment(HorizontalAlignmentStyle.CENTER);
style.setVerticalAlignment(VerticalAlignmentStyle.CENTER);
style.getFont().setWeight(ExcelFont.BOLD_WEIGHT);
CellRange mergedRange = worksheet.getCells().getSubrange("I5:I14");
mergedRange.setMerged(true);
mergedRange.setValue("T o p 1 0");
style.setRotation(-90);
style.getFillPattern().setSolid(SpreadsheetColor.fromName(ColorName.LIGHT_GREEN));
mergedRange.setStyle(style);
mergedRange = worksheet.getCells().getSubrange("J5:J24");
mergedRange.setMerged(true);
mergedRange.setValue("T o p 2 0");
style.setTextVertical(true);
style.getFillPattern().setSolid(SpreadsheetColor.fromName(ColorName.YELLOW));
mergedRange.setStyle(style);
mergedRange = worksheet.getCells().getSubrange("I15:I24");
mergedRange.setMerged(true);
mergedRange.setStyle(style);
// Write and format sample data to Excel cells.
for (i = 0; i < 20; i++) {
for (j = 0; j < 8; j++) {
ExcelCell cell = worksheet.getCell(i + 4, j);
cell.setValue(skyscrapers[i + 1][j]);
if (i % 2 == 0)
cell.getStyle().getFillPattern().setSolid(SpreadsheetColor.fromName(ColorName.LIGHT_BLUE));
else
cell.getStyle().getFillPattern().setSolid(SpreadsheetColor.fromArgb(210, 210, 230));
if (j == 4)
cell.getStyle().setNumberFormat("#\" m\"");
if (j == 5)
cell.getStyle().setNumberFormat("#\" ft\"");
if (j > 3)
cell.getStyle().getFont().setName("Courier New");
cell.getStyle().getBorders().get(IndividualBorder.RIGHT).setLineStyle(LineStyle.THIN);
}
}
worksheet.getCells().getSubrange("A5", "J24").getStyle().getBorders().setBorders(MultipleBorders.outside(), SpreadsheetColor.fromColor(Color.BLACK), LineStyle.DOUBLE);
worksheet.getCells().getSubrange("A3", "H4").getStyle().getBorders().setBorders(EnumSet.of(MultipleBorders.LEFT, MultipleBorders.RIGHT, MultipleBorders.TOP), SpreadsheetColor.fromColor(Color.BLACK), LineStyle.DOUBLE);
worksheet.getCells().getSubrange("A5", "I14").getStyle().getBorders().setBorders(EnumSet.of(MultipleBorders.BOTTOM, MultipleBorders.RIGHT), SpreadsheetColor.fromColor(Color.BLACK), LineStyle.DOUBLE);
worksheet.getCell("A27").setValue("Notes:");
worksheet.getCell("A28").setValue("a) \"Metric\" and \"Imperial\" columns use custom number formatting.");
worksheet.getCell("A29").setValue("b) All number columns use \"Courier New\" font for improved number readability.");
worksheet.getCell("A30").setValue("c) Multiple merged ranges were used for table header and categories header.");
worksheet.getPrintOptions().setFitWorksheetWidthToPages(1);
workbook.save("Writing.%OutputFileType%");
}
}
Note that this example creates an entire Excel file from scratch. In most cases you would use a template Excel file with a predefined table to accomplish the same task by writing into that template.
