Developing and implementing Excel sheet scripting in Boomi

How to Get the Excel records in the Boomi. 
Introduction: 
 Data format conversions, such as converting Excel files to flat files and vice versa, are essential for ensuring compatibility across systems in data integration tasks. In Boomi, these conversions help manage data integration between applications with differing format requirements. This article provides a comprehensive guide on how to perform these conversions using Boomi’s tools and connectors. 

Common Challenges and Troubleshooting: 

Inconsistent Data Formats: Ensure that both the source and target profiles match in terms of field names and data types. 

File Size Constraints: For large files, consider using Boomi’s chunking options to process the data in parts. 

Boomi Process for convert Excel to Flatfile: 

Prerequisites: 

1. Download the Jar files from the below links as in the given image 

• JAR FILES LINKS: https://archive.apache.org/dist/poi/release/bin/ 

• NOTE:  For Windows use .zip files, For LINUX use .tar 

2. Upload the jar files in the account libraries  Go  to the Setup > Account Libraries: 

On the Build tab, create a new Custom Library component, configured as Type=”Scripting”, and include the jar files added to the Account Libraries above: 

Deployed the library and Restart the Atom, 

Create a process, Set the Dynamic document as SHEET as property and Value as Your sheet name 

Use the below script to Convert the excel to flatfile 

In data process -> custom scripting -> Groovy2.4, 

import java.util.Properties; 
import java.io.InputStream; 
import java.io.File; 
import java.io.FileNotFoundException; 
import java.io.IOException; 
import java.util.Locale; 
import org.apache.poi.openxml4j.exceptions.InvalidFormatException 
import org.apache.poi.ss.usermodel.Cell 
import org.apache.poi.ss.usermodel.DataFormatter 
import org.apache.poi.ss.usermodel.DateUtil 
import org.apache.poi.ss.usermodel.Row 
import org.apache.poi.ss.usermodel.Sheet 
import org.apache.poi.ss.usermodel.Workbook 
import org.apache.poi.ss.usermodel.WorkbookFactory; 
for( int i = 0; i < dataContext.getDataCount(); i++ ) { 
InputStream is = dataContext.getStream(i); 
Properties props = dataContext.getProperties(i); 
DataFormatter formatter = new DataFormatter( Locale.default ); 
Workbook wb = WorkbookFactory.create( is ); 
List sheetList = wb.sheets; 
StringBuffer sb = new StringBuffer(); 
for ( int j = 0; j < sheetList.size(); j++) { 
Sheet sheet = wb.getSheetAt( j ); 
for ( Row row : sheet ) { 
for ( Cell cell : row ) { 
switch( cell.getCellType() ) { 
case Cell.CELL_TYPE_NUMERIC : if (DateUtil.isCellDateFormatted(cell)) { 
sb.append(“\”” + formatter.formatCellValue( cell ) +”\”” ); 
} else { 
cell.setCellType(Cell.CELL_TYPE_STRING); 
sb.append( “\”” + cell.getStringCellValue() +”\”” ); 
} 
break; 
case Cell.CELL_TYPE_STRING : sb.append( “\”” + cell.getStringCellValue() +”\”” ); 
break; 
case Cell.CELL_TYPE_FORMULA : sb.append( “\”” + cell.getCellFormula() +”\”” ); 
break; 
case Cell.CELL_TYPE_BOOLEAN : sb.append( “\”” + cell.getBooleanCellValue() +”\”” ); 
break; 
case Cell.CELL_TYPE_BLANK : sb.append(“”); 
break; 
default : sb.append(“”); 
break; 
} 
sb.append(“,”); 
} 
if ( sb.length() > 0 ) { 
sb.setLength(sb.length() – 1); 
} 
sb.append(“\r\n”); 
} 
String output = sb.toString(); 
sb.setLength(0); 
InputStream is2= new ByteArrayInputStream(output.getBytes()); 
 props.setProperty(“document.dynamic.userdefined.SHEET“, sheet.getSheetName()); 
dataContext.storeStream(is2, props); 
} 
} 

How to create the multiple excel sheet in Boomi. 

Create a process: 

In the Data process, Combine the records and use the below script 

Based on your criteria (rownum >= 3) modify the row Num, but max limit in a sheet is 1Million per sheet 

Groovy 2.4 Script 

import java.io.BufferedReader; 
import java.io.ByteArrayInputStream; 
import java.io.ByteArrayOutputStream; 
import java.io.InputStream; 
import java.io.InputStreamReader; 
import java.util.Properties; 
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.ss.usermodel.Row; 
import org.apache.poi.ss.usermodel.Sheet; 
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
for (int i = 0; i < dataContext.getDataCount(); i++) { 
    InputStream is = dataContext.getStream(i); 
    Properties props = dataContext.getProperties(i); 
    Workbook wb = new XSSFWorkbook(); 
    BufferedReader reader = new BufferedReader(new InputStreamReader(is)); 
    int rownum = 0; 
    int sheetIndex = 1; 
    Sheet sheet = wb.createSheet(“Sheet” + sheetIndex); 
    String line; 
    while ((line = reader.readLine()) != null) { 
        // Create a new sheet if row limit is reached 
        if (rownum >= 3) { 
            sheetIndex++; 
            sheet = wb.createSheet(“Sheet” + sheetIndex); 
            rownum = 0; 
        } 
        Row row = sheet.createRow(rownum++); 
        int cellnum = 0; 
        String[] fields = line.split(“,”); 
        for (String field : fields) { 
            Cell cell = row.createCell(cellnum++); 
            cell.setCellValue(field); 
        } 
    } 
    // Write workbook to a byte array output stream 
    ByteArrayOutputStream baos = new ByteArrayOutputStream(); 
    wb.write(baos); 

    // Convert output stream to input stream for storing in data context 
     is = new ByteArrayInputStream(baos.toByteArray()); 
     dataContext.storeStream(is, props); 
}