Read BigDecimal and BigInteger from an Excel file

In this example you are going to see how you can use DataPipline to read BigDecimal and BigInteger from an Excel file.

You will use ExcelReader to read the excel document and TransformingReader to do the convertions e.g. from string to BigDecimal.

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.excel.ExcelDocument;
import com.northconcepts.datapipeline.excel.ExcelReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.BasicFieldTransformer;
import com.northconcepts.datapipeline.transform.TransformingReader;

public class ReadBigDecimalAndBigIntegerFromAnExcelFile {

    public static void main(String[] args) throws Throwable {
        ExcelDocument document = new ExcelDocument()
        		.open(new File("example/data/input/ReadBigDecimalAndBigIntegerToExcel-credit-balance.xls"));
        DataReader reader = new ExcelReader(document)
            .setSheetName("balance")
            .setFieldNamesInFirstRow(true);

        reader = new TransformingReader(reader)
                .add(new BasicFieldTransformer("balance").stringToBigDecimal()) // convert balance to BigDecimal
                .add(new BasicFieldTransformer("creditLimit").stringToBigInteger()); //convert creditLimit to BigInteger
        
        Job.run(reader, new StreamWriter(System.out));
        
    }
}

1. First you create ExcelDocument coresponding to the input file ReadBigDecimalAndBigIntegerToExcel-credit-balance.xls.
2. ExcelReader - Obtains records from a Microsoft Excel document i.e. document.
3. .setSheetName("balance") sets the name of the excel sheet to balance and .setFieldNamesInFirstRow(true) is set to specify that the field names should be placed in the first row.
4. TransformingReader is used to apply tranformations to the record passing through. In our case it is used to convert balance to BigDecimal and creditLimit to BigInteger i.e. stringToBigInteger().
5. Data is transferred from the reader to the StreamWriter via Job.run() method.
6. StreamWriter will be used to print the results to the console.

ExcelDocument

The in-memory abstraction for an Excel workbook. This object is not thread-safe and will throw an exception if used in multiple ExcelReaders and/or ExcelWriter concurrently. Since its data is stored in memory, reading and re-reading from it multiple times is very cheap (think millions of reads per second).

Console Output

-----------------------------------------------
0 - Record (MODIFIED) {
    0:[name]:STRING=[John Wayne]:String
    1:[balance]:BIG_DECIMAL=[1234567890.0987654321]:BigDecimal
    2:[creditLimit]:BIG_INTEGER=[98765432109876543210]:BigInteger
}

-----------------------------------------------
1 - Record (MODIFIED) {
    0:[name]:STRING=[Peter Parker]:String
    1:[balance]:BIG_DECIMAL=[987654321.123456789]:BigDecimal
    2:[creditLimit]:BIG_INTEGER=[12345678901234567890]:BigInteger
}

-----------------------------------------------
2 records
14:40:01,144 DEBUG [main] datapipeline:661 - job::Success