Write to Excel

Updated: Feb 21, 2022

View on GitHub

This example shows you how to create an MS Excel file in Java using the ExcelDocument and ExcelWriter classes. The demo code uses a CSV file as input, but we can also read from other input formats like XML, JavaBeans, etc

This example can easily be modified to show how to read from an Excel file.

Input CSV file

Account,LastName,FirstName,Balance,CreditLimit,AccountCreated,Rating
101,Reeves,Keanu,9315.45,10000.00,1/17/1998,A
312,Butler,Gerard,90.00,1000.00,8/6/2003,B
868,Hewitt,Jennifer Love,0,17000.00,5/25/1985,B
761,Pinkett-Smith,Jada,49654.87,100000.00,12/5/2006,A
317,Murray,Bill,789.65,5000.00,2/5/2007,C

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import org.apache.log4j.Logger;

import com.northconcepts.datapipeline.core.DataEndpoint;
import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.DataWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelWriter;
import com.northconcepts.datapipeline.job.Job;

public class WriteToANewExcelFile {

    public static final Logger log = DataEndpoint.log;

    public static void main(String[] args) {
        DataReader reader = new CSVReader(new File("example/data/input/credit-balance-01.csv"))
                .setFieldNamesInFirstRow(true);

        ExcelDocument document = new ExcelDocument();
        DataWriter writer = new ExcelWriter(document).setSheetName("balance");

        Job.run(reader, writer);
        
        document.save(new File("example/data/output/credit-balance-05.xls"));
    }

}

Code Walkthrough

1. A CSVReader is created using the file path of the input CSV file credit-balance-01.csv.
2. ExcelDocument and ExcelWriter instances are created.
3. The setSheetName method is invoked to set the Excel sheet name to balance.
4. Data is transferred from the input CSV file to the output Excel file via JobTemplate.DEFAULT.transfer method.
5. Finally, the Excel document is saved via the ExcelDocument.save method using the file path of the output file credit-balance-05.xls.

ExcelWriter and ExcelDocument

ExcelWriter is an output writer which can be used to write to an Excel file. It is a sub-class of AbstractWriter.html and overrides the open and close among other methods. ExcelDocument is a class that encapsulates an Excel document. Normally, when CSV or other types of files are written, the output file is saved via the writer itself. For ex. you can refer to the write to a CSV file example. However in the case of Excel documents, the files need to be opened/saved via ExcelDocument class. ExcelWriter is used purely for writing to the Excel file.

Output Excel File