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

object to create a new Excel file. It also shows how to merge cells, specify column properties, and set ExcelCell.setValue and CellStyle.

Excel file created with GemBox.Spreadsheet for Java
Screenshot of Excel file created with GemBox.Spreadsheet for Java
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.

See also


Next steps

GemBox.Spreadsheet for Java is a component that enables you to read, write, edit and convert spreadsheet files from your applications using one simple API.

Download Buy