Work with MS Office files
tip
To learn Apache POI API, refer to this guide.
Reading data from Excel
For example, you need to open an Excel sheet and read data from it within your RPA script. For this purpose, use the Apache POI library, which allows you to read, create, and edit Microsoft Office documents using Java.
The classes and methods we are going to use to read data from an Excel sheet are located in the org.apache.poi.ss.usermodel package.
Algorithm description
Let's review simple BeanShell code which creates XLSX file and saves it into the local file system.
Include Apache POI packages into Bot Task. WorkFusion already includes JAR files of POI, you should not care about this. Just use it.
Create an Excel spreadsheet in memory and fill it with some generated data.
Save a generated spreadsheet into file.
<config> <script> <![CDATA[ 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.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFWorkbook; SXSSFWorkbook wb = new SXSSFWorkbook(100); Sheet sh = wb.createSheet(); for(int rownum = 0; rownum < 1000; rownum++){ Row row = sh.createRow(rownum); for(int cellnum = 0; cellnum < 10; cellnum++){ Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); } } for(int rownum = 0; rownum < 900; rownum++){ System.out.println(sh.getRow(rownum)); } for(int rownum = 900; rownum < 1000; rownum++){ System.out.println(sh.getRow(rownum)); } FileOutputStream out = new FileOutputStream("/data/sxssf.xlsx"); wb.write(out); out.close(); wb.dispose(); ]]> </script> </config>
Advanced sample of work with Excel
BP consists of three Bot Tasks
Input Data: Apache POI - Working with MS Office Files.
Read files from s3 task
This code is merely a sample. In order for the code sample to work, replace the s3_bucket_name with your bucket name.
Read from S3
<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
<required name="s3_bucket_name"/>
<s3 bucket="${s3_bucket_name}">
<var-def name="dataFile_contentString">
<s3-get name="Udeshika/${data_file}"></s3-get>
</var-def>
</s3>
<s3 bucket="${s3_bucket_name}">
<var-def name="configFile_contentString">
<s3-get name="Udeshika/${configuration_file}"></s3-get>
</var-def>
</s3>
<s3 bucket="${s3_bucket_name}">
<var-def name="templateFile_contentString">
<s3-get name="Udeshika/${country}.xlsx"></s3-get>
</var-def>
</s3>
<var-def name="datafile_url">
<script language="groovy" return="tmpFile_data"></script>
</var-def>
<var-def name="xmlfile_url">
<script language="groovy" return="tmpFile_xml"></script>
</var-def>
<var-def name="templatefile_url">
<script language="groovy" return="tmpFile_excel"></script>
</var-def>
<export include-original-data="true">
<single-column name="datafile_url">
<template>${datafile_url}</template>
</single-column>
<single-column name="xmlfile_url">
<template>${xmlfile_url}</template>
</single-column>
<single-column name="templatefile_url">
<template>${templatefile_url}</template>
</single-column>
</export>
</config>
Format base files task (java-poi) task
Format base files
<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
<var-def name="json_list_of_maps">
<script language="groovy" return="jsonString"><![CDATA[
import com.google.common.io.Files;
import java.nio.charset.StandardCharsets;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
//attributes
Map<String, Integer> columnsDetails;
String blockStartingChar;
int startingDistance;
String blockEndingChar;
Map<Integer, Integer> rowLengths;
parseXml();
List<String> data_list = getAllRowsList();
List<String> data_list1=filterRows(data_list)
List<Map<String, Object>> list3=getColumnsList(data_list1)
//convert list of maps to json string
jsonString=com.freedomoss.workfusion.utils.gson.GsonUtils.GSON.toJson(list3);
//methods
void parseXml() throws ParserConfigurationException, SAXException, IOException {
this.columnsDetails = new LinkedHashMap<String, Integer>();
Map<Integer, Integer> rowLengthsMap = new LinkedHashMap<Integer, Integer>();
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document document = builder.parse(new File(xmlfile_url.toString()));
// get the startingchar from xml
this.blockStartingChar = document.getElementsByTagName("StartingChar").item(0).getTextContent();
// get endingchar from xml
this.blockEndingChar = document.getElementsByTagName("EndingChar").item(0).getTextContent();
// get starting distance from xml
this.startingDistance = Integer.parseInt(document.getElementsByTagName("StartingDistance").item(0).getTextContent());
// get the row lengths and add them to a map
NodeList rowLenghts = document.getElementsByTagName("RowLengths").item(0).getChildNodes();
for (int i = 1; i < rowLenghts.getLength(); i += 2) {
Node list = rowLenghts.item(i);
int key = Integer.parseInt(list.getAttributes().getNamedItem("name").getTextContent());
int value = Integer.parseInt(list.getTextContent());
rowLengthsMap.put(key, value);
}
this.rowLengths = rowLengthsMap;
// get the column lengths
NodeList list = document.getElementsByTagName("Column");
for (int i = 0; i < list.getLength(); i++) {
Node currentNode = list.item(i);
if (currentNode.getNodeType() == Node.ELEMENT_NODE) {
NodeList colData = currentNode.getChildNodes();
String columnName = colData.item(1).getTextContent();
String columnSize = colData.item(3).getTextContent();
columnsDetails.put(columnName, Integer.parseInt(columnSize));
}
}
}
List<String> getAllRowsList() throws IOException {
// Text file row list
List<String> rowsList = new ArrayList<String>();
BufferedReader br = new BufferedReader(new FileReader(datafile_url.toString()));
String currentLine;
while ((currentLine = br.readLine()) != null) {
// add Text file rows to a list
if (!currentLine.trim().equals("")) {
rowsList.add(currentLine);
log.error("************************** " + currentLine);
}
}
br.close();
return rowsList;
}
List<String> filterRows(List<String> rows) {
List<String> returningList = new ArrayList<String>();
boolean flag = false;
int count = 0;
StringBuffer joinedLine = new StringBuffer();
for (int i = 0; i < rows.size(); i++) {
StringBuffer row = new StringBuffer(rows.get(i));
if (row.toString().contains(blockStartingChar)) {
flag = true;
i = i + startingDistance;
continue;
} else if (row.toString().contains(blockEndingChar)) {
flag = false;
}
// merging rows
if (flag) {
count++;
int size = rowLengths.get(count);
// fixing the row size error
if (row.length() != size) {
row.setLength(size);
}
joinedLine.append(row);
}
if (count == rowLengths.size()) {
returningList.add(joinedLine.toString());
// clean the stringbuffer
joinedLine.setLength(0);
count = 0;
}
}
return returningList;
}
List<Map<String, Object>> getColumnsList(List<String> rowsList) {
// return data separated list
List<Map<String, Object>> separatedColumnsList = new ArrayList<Map<String, Object>>();
for (int i = 0; i < rowsList.size(); i++) {
// get the row
String row = rowsList.get(i);
Map<String, Object> columnsMap = new HashMap<String, Object>();
// create iterator
Iterator<Entry<String, Integer>> it = columnsDetails.entrySet().iterator();
// get the column lenths of the each column
while (it.hasNext()) {
Map.Entry<String, Integer> pair = (Map.Entry<String, Integer>) it.next();
// get the column
Object column = row.substring(0, pair.getValue()).trim();
// get the other column row after substring
row = row.substring(pair.getValue());
// add to columns to list
columnsMap.put(pair.getKey(), column);
}
separatedColumnsList.add(columnsMap);
}
return separatedColumnsList;
}
]]></script>
</var-def>
<export include-original-data="true">
<single-column name="json_list_of_maps">
<template>${json_list_of_maps}</template>
</single-column>
</export>
</config>
Write to excel task
This code is merely a sample. In order for the code sample to work, replace the s3_bucket_name with your bucket name.
Write to Excel
<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
<required name="s3_bucket_name"/>
<var-def name="excelfile">
<script><![CDATA[
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
File createWorkbook() {
XSSFWorkbook workbook_temp;
File file_template = new File(templatefile_url.toString());
String file_name = "CN-CFCCA000";
if (file_template.exists() == false) {
System.out.println("Template file is not available");
log.error("******************** Template file is not available");
}
else {
System.out.println("Appending to existing workbook template '" + file_template + "'");
log.error("Appending to existing workbook template '" + file_template + "'");
try {
// Get the workbook instance from a template
FileInputStream fin = new FileInputStream(file_template);
workbook_temp = new XSSFWorkbook(fin);
// get the specific spreadsheet
XSSFSheet spreadsheet = workbook_temp.getSheet(file_name);
List separatedColumnsList = new ArrayList();
Map rowValues2 = new HashMap();
rowValues2.put("A", "2500.00");
rowValues2.put("B", "text1");
rowValues2.put("C", "text2");
separatedColumnsList.add(rowValues2);
// Create row object
XSSFRow row;
int rowid = 0;
//for each map,create a row
for (Map map : separatedColumnsList) {
Map rowValues = map;
row = spreadsheet.createRow(rowid++);
// get data to populate a row
Collection objectArr = rowValues.values();
int cellid = 0;
for (Object obj : objectArr) {
XSSFSheet template_sheet = workbook_temp.getSheet(file_name);
CellStyle origStyle = template_sheet.getColumnStyle(cellid);
System.out.println(origStyle.getDataFormatString());
Cell cell = row.createCell(cellid++);
cell.setCellStyle(origStyle);
cell.setCellValue((String) obj);
}
}
FileOutputStream
fop = new FileOutputStream(file_template);
workbook_temp.write(fop);
} catch (IOException e)
{
e.printStackTrace();
System.out.println("IOException");
log.error("IOException");
}
catch (EncryptedDocumentException e1)
{
e1.printStackTrace();
System.out.println("EncryptedDocumentException");
log.error("EncryptedDocumentException");
}
}
return file_template;
}
f1 = createWorkbook();
]]></script>
</var-def>
<var-def name="fileLink">
<s3 bucket="${s3_bucket_name}">
<s3-put path="Udeshika/data_list1.xlsx" content-type="application/xlsx" content-disposition="inline" acl="PublicRead">
${excelfile}
</s3-put>
</s3>
</var-def>
<export include-original-data="true">
<single-column name="excel_file_link">
<template>${fileLink.toString()}</template>
</single-column>
</export>
</config>
Save XLS file to Data Store
This Bot config opens XLS file, reads the first tab, and creates a json data to insert into Data Store.
Saving XLS file to Data Store
<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
<script><![CDATA[
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.google.gson.Gson;
public List getJsonListFromXLS(String fileValue) {
List list = new ArrayList();
List headerList = new ArrayList();
List rowList = new ArrayList();
FileInputStream file;
try {
file = new FileInputStream(new File(fileValue));
XSSFWorkbook baseBook = new XSSFWorkbook(file);
String sheetName = baseBook.getSheetAt(0).getSheetName();
System.out.println("================== SHEET NAME " + sheetName + " =========================");
Iterator sheetIterator = baseBook.iterator();
while (sheetIterator.hasNext()) {
// Get sheet from the workbook
XSSFSheet sheet = (XSSFSheet) sheetIterator.next();
// Iterate through each rows from first sheet
Iterator rowIterator = sheet.iterator();
int rowIndex = 0;
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
if (rowIndex == 0) {
Iterator cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
headerList.add(((Cell) cellIterator.next()).getStringCellValue());
}
} else {
Map jSonToRec = new LinkedHashMap();
// For each row, iterate through each columns
Iterator cellIterator = row.cellIterator();
Iterator headerIterator = headerList.iterator();
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
String cellValue = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
cellValue = Double.toString(cell.getNumericCellValue());
// System.out.print(cell.getNumericCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
// System.out.print(cell.getStringCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_BLANK:
// System.out.print("\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = Boolean.toString(cell.getBooleanCellValue());
// System.out.print(cell.getBooleanCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_ERROR:
// System.out.print(cell.getErrorCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
// System.out.print(cell.getCellFormula() + "\t");
break;
default:
break;
}
jSonToRec.put(headerIterator.next(), cellValue);
}
String jsonString = new Gson().toJson(jSonToRec);
jsonString = jsonString.replace("[", "").replace("]", "");
// list.add(org.apache.commons.lang.StringEscapeUtils.escapeJavaScript(jsonString));
list.add(jSonToRec);
System.out.println("================== JSON VALUE " + jsonString + " =========================");
}
rowIndex++;
}
break; // remove to get all others workbooks
}
baseBook.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
listOfMap = getJsonListFromXLS("C:\\TMP\\ExcelToProcess.xlsx");
]]></script>
<loop item="newStatusSPData" maxloops="${listOfMap.size()}" index="index">
<list>
<script return="listOfMap" /></script>
<insert-datastore datastore-name="xls_file_return" create="true" json-value-map="${jsonMap}" />
</body>
</loop>
<export include-original-data="true">
</export>
</config>
Apache POI integration
POI usage
<?xml version="1.0" encoding="UTF-8"?>
<config charset="UTF-8">
<script><![CDATA[
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigInteger;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.docx4j.Docx4J;
import org.docx4j.convert.out.FOSettings;
import org.docx4j.fonts.IdentityPlusMapper;
import org.docx4j.fonts.Mapper;
import org.docx4j.fonts.PhysicalFont;
import org.docx4j.fonts.PhysicalFonts;
import org.docx4j.model.fields.FieldUpdater;
import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
public Workbook mergeExcelFiles(Workbook book, Workbook baseBook) throws IOException {
for (int i = 0; i < baseBook.getNumberOfSheets(); i++) {
System.out.println(baseBook.getSheetAt(i).getSheetName());
// not entering sheet name, because of duplicated names
copySheets(book.createSheet(baseBook.getSheetAt(i).getSheetName()), baseBook.getSheetAt(i));
}
System.out.println("================== FILTERING FINISHED SUCCESSFULLY =========================");
return book;
}
public void copySheets(Sheet newSheet, Sheet sheet) {
copySheets_x(newSheet, sheet, false);
}
public void copySheets_x(Sheet newSheet, Sheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map styleMap = (copyStyle) ? new HashMap() : null;
int headerRowIndex = 0;
int newRowIndex = 0;
String sourceSheetName = sheet.getSheetName();
int idColumnIndex = getBaseNumberColumn(sourceSheetName);
String filterBaseNumber = "123456";
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
if (i == sheet.getFirstRowNum()) {
newRowIndex = i;
}
if (i == headerRowIndex || matchRow(sourceSheetName, sheet.getRow(i), filterBaseNumber, idColumnIndex)) {
Row srcRow = sheet.getRow(i);
Row destRow = newSheet.createRow(newRowIndex++);
if (srcRow != null) {
copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow, Map styleMap) {
// manage a list of merged zone in order to not insert two times a
// merged zone
Set mergedRegions = new TreeSet();
destRow.setHeight(srcRow.getHeight());
// reckoning delta rows
int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
// pour chaque row
for (int j = srcRow.getFirstCellNum(); srcRow.getLastCellNum() > j; j++) {
Cell oldCell = srcRow.getCell(j); // ancienne cell
// System.out.println(oldCell);
Cell newCell = destRow.getCell(j); // new cell
// Enter the correct column selection condition for the if clause.
if (oldCell.getColumnIndex() < 5) {
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
// copy chaque cell
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
(short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn());
if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
mergedRegions.add(newMergedRegion);
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
}
public void copyCell(Cell oldCell, Cell newCell, Map styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
CellStyle newCellStyle = (CellStyle) styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
public boolean matchRow(String sheetName, Row row, String filterId, int idColumnIndex) {
Boolean isValidRow = false;
Cell idCell = row.getCell(idColumnIndex);
String value = null;
switch (sheetName) {
case "C4CH1000 - LC":
value = getBaseNumber(idCell, "TAKE_LAST_6_DIGITS");
break;
case "C1101000 - Outstanding":
value = getBaseNumber(idCell, "TAKE_LAST_6_DIGITS");
break;
case "CFCBA032-LND OUSTANDING":
value = getBaseNumber(idCell, "TAKE_LAST_6_DIGITS");
break;
case "CN-CFCC000-CUSTOMER ACCOUNTS LI":
value = getBaseNumber(idCell, "IGNORE_LAST_3_TAKE_6_PRECEDING_DIGITS");
break;
case "C4CH1000 - LCCFCFX123-C-CB7185 - FX OUTSTAND":
value = getBaseNumber(idCell, "IGNORE_LAST_3_TAKE_6_PRECEDING_DIGITS");
break;
default:
break;
}
if (value != null && !value.equals("")) {
if (value.equals(filterId)) {
isValidRow = true;
} else {
isValidRow = false;
}
}
return isValidRow;
}
public String getBaseNumber(Cell idCell, String criteria) {
String cellValue = null;
String baseNumber = null;
if (idCell != null) {
switch (idCell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
cellValue = Long.toString((long) idCell.getNumericCellValue()).trim();
break;
case Cell.CELL_TYPE_STRING:
cellValue = idCell.getStringCellValue().trim();
break;
default:
System.out.println(idCell.getCellType());
break;
}
if (cellValue != null && !cellValue.equals("") && cellValue.length() >= 6) {
switch (criteria) {
case "IGNORE_LAST_3_DIGITS":
baseNumber = cellValue.substring(0, cellValue.length() - 3);
break;
case "TAKE_LAST_6_DIGITS":
baseNumber = cellValue.substring(cellValue.length() - 6);
break;
case "IGNORE_LAST_3_TAKE_6_PRECEDING_DIGITS":
baseNumber = cellValue.substring(cellValue.length() - 9, cellValue.length() - 3);
break;
default:
break;
}
}
}
//System.out.println("::::: " + baseNumber);
return baseNumber;
}
public int getBaseNumberColumn(String sheetName) {
int filterColumnIndex = 0;
switch (sheetName) {
case "C4CH1000 - LC":
filterColumnIndex = 3;
break;
case "C1101000 - Outstanding":
filterColumnIndex = 5;
break;
case "CFCBA032-LND OUSTANDING":
filterColumnIndex = 4;
break;
case "CN-CFCC000-CUSTOMER ACCOUNTS LI":
filterColumnIndex = 2;
break;
case "CFCFX123-C-CB7185 - FX OUTSTAND":
filterColumnIndex = 2;
break;
default:
filterColumnIndex = -1;
break;
}
return filterColumnIndex;
}
public CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, short cellNum) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress merged = sheet.getMergedRegion(i);
if (merged.isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
private boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Collection mergedRegions) {
return !mergedRegions.contains(newMergedRegion);
}
public List readExcel(byte[] bFile) throws InvalidFormatException {
List returnList = new ArrayList();
FileInputStream fileInputStream = null;
try {
// byte[] bFile =
// excelFile.getWrappedObject().get(0).getWrappedObject();
File tempFile = File.createTempFile("test_excel_des", ".xlsx", null);
FileOutputStream fileOuputStream = new FileOutputStream(tempFile);
fileOuputStream.write(bFile);
fileOuputStream.close();
FileInputStream file = new FileInputStream(tempFile);
// Get the workbook instance for XLS file
XSSFWorkbook workbook = new XSSFWorkbook(file);
Iterator sheetIterator = workbook.iterator();
while (sheetIterator.hasNext()) {
Map sheetMap = new HashMap(2);
List headerList = new ArrayList();
List rowList = new ArrayList();
// Get sheet from the workbook
XSSFSheet sheet = (XSSFSheet) sheetIterator.next();
// Iterate through each rows from first sheet
Iterator rowIterator = sheet.iterator();
int rowIndex = 0;
while (rowIterator.hasNext()) {
Row row = (Row) rowIterator.next();
if (rowIndex == 0) {
Iterator cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
headerList.add(((Cell) cellIterator.next()).getStringCellValue());
}
} else {
Map rowMap = new LinkedHashMap();
// For each row, iterate through each columns
Iterator cellIterator = row.cellIterator();
Iterator headerIterator = headerList.iterator();
while (cellIterator.hasNext()) {
rowMap.put(headerIterator.next(), cellIterator.next());
}
rowList.add(rowMap);
}
rowIndex++;
}
sheetMap.put("headerList", headerList);
sheetMap.put("rowList", rowList);
returnList.add(sheetMap);
}
workbook.close();
tempFile.delete();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return returnList;
}
public Path writeToWord(List sheetList) {
File tempFile = null;
XWPFDocument document = null;
FileOutputStream out = null;
try {
tempFile = File.createTempFile("create_table", ".docx", null);
// Write the Document in file system
out = new FileOutputStream(tempFile);
// Blank Document
document = new XWPFDocument();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
for (Map map : sheetList) {
List headerList = new ArrayList((List) map.get("headerList"));
List rowList = new ArrayList((List) map.get("rowList"));
// create table
XWPFTable table = document.createTable(rowList.size() + 1, headerList.size());
CTTbl table2 = table.getCTTbl();
CTTblPr pr = table2.getTblPr();
CTTblWidth tblW = pr.getTblW();
tblW.setW(BigInteger.valueOf(5000));
tblW.setType(STTblWidth.PCT);
pr.setTblW(tblW);
table2.setTblPr(pr);
if (!rowList.isEmpty()) {
int rowIndex = 0;
for (Map row : rowList) {
if (rowIndex == 0) {
XWPFTableRow headerRow = table.getRow(0);
for (int i = 0; i < headerList.size(); i++) {
headerRow.getCell(i).setText((String) headerList.get(i));
System.out.println("-----------------------" + (String) headerList.get(i));
}
rowIndex++;
}
System.out.println("=======" + rowIndex);
XWPFTableRow tableRow = table.getRow(rowIndex);
for (int i = 0; i < row.size(); i++) {
System.out.println("row: " + row + " , cell: " + i);
Cell cell = (Cell) row.get(headerList.get(i));
XWPFTableCell tableCell = tableRow.getCell(i);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
tableCell.setText(Double.toString(cell.getNumericCellValue()));
// System.out.print(cell.getNumericCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_STRING:
tableCell.setText(cell.getStringCellValue());
// System.out.print(cell.getStringCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_BLANK:
tableCell.setText("");
// System.out.print("\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
tableCell.setText(Boolean.toString(cell.getBooleanCellValue()));
// System.out.print(cell.getBooleanCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_ERROR:
tableCell.setText("");
// System.out.print(cell.getErrorCellValue() +
// "\t");
break;
case Cell.CELL_TYPE_FORMULA:
tableCell.setText(cell.getCellFormula());
// System.out.print(cell.getCellFormula() + "\t");
break;
default:
tableCell.setText("");
// System.out.print("\t");
break;
}
}
rowIndex++;
}
} else {
XWPFTableRow headerRow = table.getRow(0);
for (int i = 0; i < headerList.size(); i++) {
headerRow.getCell(i).setText((String) headerList.get(i));
System.out.println("-----------------------" + (String) headerList.get(i));
}
}
document.createParagraph().createRun().addBreak();
}
try {
document.write(out);
out.close();
document.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// log.info("create_table.docx written successully");
return tempFile.toPath();
}
public Path docxToPdf(Path path) {
// path of the temporary docx file
String inputFilePath = path.toString();
// whether the intermediary FO and Docx files should be saved. (for
// dubugging purposes)
boolean saveFO = false;
boolean saveDocx = true;
// Font regex (optional). Set regex if you want to restrict to some
// defined subset of fonts
// Windows:
// String
// regex=".*(calibri|camb|cour|arial|symb|times|Times|zapf).*";
// regex=".*(calibri|camb|cour|arial|times|comic|georgia|impact|LSANS|pala|tahoma|trebuc|verdana|symbol|webdings|wingding).*";
String regex = null;
PhysicalFonts.setRegex(regex);
// Document loading (required)
WordprocessingMLPackage wordMLPackage = null;
if (inputFilePath == null) {
// thorow exception
throw new NullPointerException("File path is null");
} else {
// Load .docx file
System.out.println("Input file at: " + inputFilePath);
try {
wordMLPackage = WordprocessingMLPackage.load(new java.io.File(inputFilePath));
} catch (Docx4JException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// Refresh the values of DOCPROPERTY fields
FieldUpdater updater = new FieldUpdater(wordMLPackage);
String outputfilepath = null;
try {
updater.update(true);
outputfilepath = inputFilePath.replaceAll(".docx", "") + ".pdf";
// All methods write to an output stream
OutputStream os = new java.io.FileOutputStream(outputfilepath);
System.out.println("Attempting to use XSL FO");
// Set up font mapper (optional)
Mapper fontMapper = new IdentityPlusMapper();
wordMLPackage.setFontMapper(fontMapper);
PhysicalFont font = PhysicalFonts.get("Arial Unicode MS");
// FO exporter setup (required)
// .. the FOSettings object
FOSettings foSettings = Docx4J.createFOSettings();
if (saveFO) {
foSettings.setFoDumpFile(new java.io.File(inputFilePath.replaceAll(".docx", "") + ".fo"));
}
foSettings.setWmlPackage(wordMLPackage);
// Specify whether PDF export uses XSLT or not to create the FO
// (XSLT takes longer, but is more complete).
Docx4J.toFO(foSettings, os, Docx4J.FLAG_EXPORT_PREFER_XSL);
System.out.println("Saved: " + outputfilepath);
if (!saveDocx) {
Files.delete(path);
}
// Clean up, so any ObfuscatedFontPart temp files can be deleted
if (wordMLPackage.getMainDocumentPart().getFontTablePart() != null) {
wordMLPackage.getMainDocumentPart().getFontTablePart().deleteEmbeddedFontTempFiles();
}
} catch (Docx4JException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return new File(outputfilepath).toPath();
}
FileInputStream file;
try {
file = new FileInputStream(new File("C:\\Country-China-Datatable.xlsx"));
// file = new FileInputStream(new
// File("src/main/resources/China/China_with data.xlsx"));
XSSFWorkbook baseBook = new XSSFWorkbook(file);// WorkbookFactory.create(file);
XSSFWorkbook workbook = new XSSFWorkbook();
mergeExcelFiles(workbook, baseBook);
File tempExcelFile = File.createTempFile("filtered_excel", ".xlsx", null);
FileOutputStream outputStream = new FileOutputStream(tempExcelFile);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
FileInputStream fileInputStream = null;
byte[] bFile = new byte[(int) tempExcelFile.length()];
fileInputStream = new FileInputStream(tempExcelFile);
fileInputStream.read(bFile);
fileInputStream.close();
Path path = null;
path = docxToPdf(writeToWord(readExcel(bFile)));
//array to hold bytestream to be sent to S3 in WF
byte[] data = null;
data = Files.readAllBytes(path);
//Whether to delete the temporary pdf File.
// Files.delete(path);
// System.out.println(Arrays.toString(data));
System.out.println("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! FINISH !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
]]></script>
<export include-original-data="true">
</export>
</config>
Save Read text from MSWORD DOC and DOCX files
This Bot config opens a DOCX or DOC file (all formats are supported using Apache POI library), reads text from it, and saves a TXT version to S3. The input columns are:
bp_run_id- any UUID to keep files on S3original_document_url- a link to a DOCX or DOC file
Output is the txt_document_link column with the TXT file link.
Save Read text from DOC and DOCX
<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config">
<script></script>
<var-def name="bp_run_id">
<template>${bp_run_id}</template>
</var-def>
<var-def name="inputFileNameValue" />
<var-def name="convertedTxt">
<script return="convert(original_document_url.toString())"><![CDATA[
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URL;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import javax.net.ssl.HttpsURLConnection;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xwpf.extractor.XWPFWordExtractor;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import com.google.common.io.Files;
import com.lowagie.text.Document;
private static javax.net.ssl.TrustManager[] get_trust_mgr() {
javax.net.ssl.TrustManager[] certs = new javax.net.ssl.TrustManager[] { new javax.net.ssl.X509TrustManager() {
public java.security.cert.X509Certificate[] getAcceptedIssuers() {
return null;
}
public void checkClientTrusted(java.security.cert.X509Certificate[] certs, String t) {
}
public void checkServerTrusted(java.security.cert.X509Certificate[] certs, String t) {
}
} };
return certs;
}
public String convert(String documentLink)
throws NoSuchAlgorithmException, KeyManagementException, IOException, OpenXML4JException {
documentLink = documentLink.replace("doc-upload.app.workfusion.com/","app.workfusion.com/doc-upload/");
javax.net.ssl.SSLContext ssl_ctx = javax.net.ssl.SSLContext.getInstance("TLS");
javax.net.ssl.TrustManager[] trust_mgr = get_trust_mgr();
ssl_ctx.init(null, trust_mgr, new java.security.SecureRandom());
javax.net.ssl.HttpsURLConnection.setDefaultSSLSocketFactory(ssl_ctx.getSocketFactory());
String converted = "";
if (!documentLink.contains("https:")) {
documentLink = documentLink.replace("http:", "https:");
}
File baseDir = Files.createTempDir();
File inputFolder = new File(baseDir, "input");
URL documentUrl = new URL(documentLink);
HttpsURLConnection connection = (HttpsURLConnection) documentUrl.openConnection();
connection.setHostnameVerifier(new javax.net.ssl.HostnameVerifier() {
public boolean verify(String host, javax.net.ssl.SSLSession sess) {
return true;
}
});
String inputFileName = FilenameUtils.getName(documentUrl.getPath());
if (inputFileName == null || inputFileName.isEmpty()) {
inputFileName = "input.docx";
}
File inputFile = new File(inputFolder, inputFileName);
FileUtils.copyURLToFile(documentUrl, inputFile);
Document document = new Document();
if (documentLink.contains(".docx")) {
FileInputStream streamFs = new FileInputStream(inputFile);
XWPFDocument doc = new XWPFDocument(OPCPackage.open(streamFs));
XWPFWordExtractor we = new XWPFWordExtractor(doc);
document.open();
converted = we.getText();
} else {
try {
FileInputStream streamFs = new FileInputStream(inputFile);
POIFSFileSystem fs = new POIFSFileSystem(streamFs);
HWPFDocument doc = new HWPFDocument(fs);
WordExtractor we = new WordExtractor(doc);
document.open();
converted = we.getText();
}
catch (org.apache.poi.poifs.filesystem.OfficeXmlFileException ex) {
FileInputStream streamFs = new FileInputStream(inputFile);
XWPFDocument doc = new XWPFDocument(OPCPackage.open(streamFs));
XWPFWordExtractor we = new XWPFWordExtractor(doc);
document.open();
converted = we.getText();
}
}
document.close();
FileUtils.forceDelete(inputFolder);
sys.defineVariable("inputFileNameValue", inputFileName, true);
return converted;
}
]]></script>
</var-def>
<script></script>
<var-def name="txt_document_link">
<s3 bucket="${s3_bucket_name}">
<s3-put-public path="${path}" content="${convertedTxt}" content-type="text/html" content-disposition="inline"/>
</s3>
</var-def>
<script></script>
<export include-original-data="true">
<single-column name="txt_document_link" value="${docUrl}" />
</export>
</config>