use of org.apache.poi.ss.usermodel.Row in project head by mifos.
the class XlsLoansAccountImporter method parse.
/**
* Parse input stream.
* @param is input stream containing loan accounts' data
* @return object containing successfully parsed rows and rows with errors
*/
public ParsedLoansDto parse(InputStream is) {
//prepare objects: result, lists for rows
ParsedLoansDto result = null;
List<String> errors = new ArrayList<String>();
//temporary list for new accounts numbers, currently not used
List<String> newAccountsNumbers = new ArrayList<String>();
List<ImportedLoanDetail> parsedLoanDetails = new ArrayList<ImportedLoanDetail>();
// open spreadsheet
try {
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFSheet sheet = workbook.getSheetAt(0);
// check first row of data
HSSFRow row = sheet.getRow(XlsLoansImportTemplateConstants.FIRST_ROW_WITH_DATA.getValue());
if (row == null) {
throw new XlsParsingException(getMessage(XlsMessageConstants.NOT_ENOUGH_INPUT_ROW, null));
}
Iterator<Row> iterator = sheet.rowIterator();
// skip to rows with data
while (iterator.hasNext() && (iterator.next().getRowNum() < XlsLoansImportTemplateConstants.FIRST_ROW_WITH_DATA.getValue() - 1)) ;
// parse loan account's data
while (iterator.hasNext()) {
row = (HSSFRow) iterator.next();
List<Object> params = new ArrayList<Object>();
// setup the first cell
XlsLoansImportTemplateConstants currentCell = XlsLoansImportTemplateConstants.ACCOUNT_NUMBER;
try {
// account number
String accountNumber = getCellStringValue(row, currentCell);
// TODO: rewrite this account number validation to more universal and extract method
if (StringUtils.isBlank(accountNumber) && isEdit) {
//editing and account number is missing
throw new XlsParsingException(getCellError(XlsMessageConstants.MISSING_ACCOUNT_NUMBER, row, currentCell.getValue(), null));
} else //TODO: validation if account for edition exists
if (StringUtils.isBlank(accountNumber) && !isEdit) {
//not editing, adding with predefined account number and account number is not good
accountNumber = null;
} else //account number is good for creating new account with predefined account number...
if (!StringUtils.isBlank(accountNumber) && !isEdit) {
//...but it's duplicate
if (!validateAccountNumber(accountNumber, newAccountsNumbers)) {
params.clear();
params.add(accountNumber);
throw new XlsParsingException(getCellError(XlsMessageConstants.DUPLICATE_GLOBAL_NUM_ERROR, row, currentCell.getValue(), params));
}
}
//all good, account is either predefined from xls document or null and will be generated
//TODO: extract methods that can be shared between loans and savings
// customer global id
currentCell = XlsLoansImportTemplateConstants.CUSTOMER_GLOBAL_ID;
String customerGlobalId = getCellStringValue(row, currentCell);
if (customerGlobalId.isEmpty()) {
throw new XlsParsingException(getCellError(XlsMessageConstants.CUSTOMER_NOT_BLANK, row, currentCell.getValue(), params));
}
CustomerBO customerBO = null;
customerBO = validateCustomerGlobalId(customerGlobalId);
if (customerBO == null) {
params.clear();
params.add(customerGlobalId);
throw new XlsParsingException(getCellError(XlsMessageConstants.CUSTOMER_NOT_FOUND, row, currentCell.getValue(), params));
}
// product name
currentCell = XlsLoansImportTemplateConstants.PRODUCT_NAME;
String productName = getCellStringValue(row, currentCell);
LoanOfferingBO loanOfferingBO = null;
loanOfferingBO = validateProductName(productName, customerBO, row, currentCell.getValue());
//TODO: add support for backdated payments
LoanCreationLoanDetailsDto lcldd = loanAccountServiceFacade.retrieveLoanDetailsForLoanAccountCreation(customerBO.getCustomerId(), loanOfferingBO.getPrdOfferingId(), false);
// status name
currentCell = XlsLoansImportTemplateConstants.STATUS_NAME;
String statusName = getCellStringValue(row, currentCell);
XlsLoanSavingsAccountStatesConstants statusConstant = null;
statusConstant = validateStatusName(statusName, customerBO, this.isEdit, row, currentCell.getValue());
// status reason flag
currentCell = XlsLoansImportTemplateConstants.CANCEL_FlAG_REASON;
String cancelReason = getCellStringValue(row, currentCell);
XlsLoanSavingsFlagsConstants flagConstant = null;
flagConstant = validateStatusFlagReason(cancelReason, statusName, AccountTypes.LOAN_ACCOUNT, row, currentCell.getValue());
// loan amount
currentCell = XlsLoansImportTemplateConstants.LOAN_AMOUNT;
BigDecimal loanAmount = getCellDecimalValue(row, currentCell);
validateAmount(loanAmount, loanOfferingBO, customerBO, lcldd, row, currentCell.getValue());
// Interest rate
currentCell = XlsLoansImportTemplateConstants.INTEREST_RATE;
BigDecimal interestRate = getCellDecimalValue(row, currentCell);
validateInterestRate(interestRate, loanOfferingBO, customerBO, lcldd, row, currentCell.getValue());
// number of installments
currentCell = XlsLoansImportTemplateConstants.NO_OF_INSTALLMENTS;
Integer numberOfInstallments = getCellIntegerValue(row, currentCell);
validateNumberOfInstallments(numberOfInstallments, customerBO, loanOfferingBO, lcldd, row, currentCell.getValue());
// disbursal date
currentCell = XlsLoansImportTemplateConstants.DISBURLSAL_DATE;
Date disbursalDate = getCellDateValue(row, currentCell);
validateDisbursalDate(disbursalDate, customerBO, loanOfferingBO, currentCell.getValue(), row, statusName);
// grace period
currentCell = XlsLoansImportTemplateConstants.GRACE_PERIOD;
Integer gracePeriod = getCellIntegerValue(row, currentCell);
validateGracePeriod(gracePeriod, loanOfferingBO, customerBO, numberOfInstallments, row, currentCell.getValue());
// source of founds
currentCell = XlsLoansImportTemplateConstants.SOURCE_OF_FOUNDS;
List<FundDto> funds = lcldd.getFundDtos();
String sourceOfFund = getCellStringValue(row, currentCell);
Integer sourceOfFundId = null;
sourceOfFundId = validateSourceOfFund(sourceOfFund, funds, row, currentCell.getValue());
// purpose
List<ValueListElement> purposes = lcldd.getLoanPurposes();
currentCell = XlsLoansImportTemplateConstants.PURPOSE;
String loanPurpose = getCellStringValue(row, currentCell);
Integer loanPurposeId = null;
loanPurposeId = validateLoanPurposeId(loanPurpose, purposes, row, currentCell.getValue());
// collateral type
currentCell = XlsLoansImportTemplateConstants.COLLATERAL_TYPE;
Integer collateralTypeId = null;
String collateralType = getCellStringValue(row, currentCell);
Map<String, String> collaterals = lcldd.getCollateralOptions();
collateralTypeId = validateCollateralType(collateralType, collaterals, row, currentCell.getValue());
// collateral notes
currentCell = XlsLoansImportTemplateConstants.COLLATERAL_NOTES;
String collateralNotes = getCellStringValue(row, currentCell);
collateralNotes = StringUtils.isBlank(collateralNotes) ? null : collateralNotes;
// external id
currentCell = XlsLoansImportTemplateConstants.EXTERNAL_ID;
String externalId = getCellStringValue(row, currentCell);
externalId = StringUtils.isBlank(externalId) ? null : externalId;
//...will be used for editing/adding loans with predefined account numbers
if (accountNumber != null) {
newAccountsNumbers.add(accountNumber);
}
//create final objects
//TODO handle backdated payments
Short flagValue = flagConstant == null ? null : flagConstant.getFlag().getValue();
ImportedLoanDetail detail = new ImportedLoanDetail(accountNumber, customerBO.getCustomerId(), loanOfferingBO.getPrdOfferingId(), statusConstant.getState().getValue(), flagValue, loanAmount, interestRate, numberOfInstallments, disbursalDate, gracePeriod, sourceOfFundId, loanPurposeId, collateralTypeId, collateralNotes, externalId);
parsedLoanDetails.add(detail);
} catch (XlsParsingException xex) {
if (xex.isMultiple()) {
for (String msg : xex.getMessages()) {
errors.add(msg);
}
} else {
errors.add(xex.getMessage());
}
} catch (Exception cex) {
errors.add(cex.getMessage());
}
}
} catch (Exception ex) {
errors.add(ex.getMessage());
}
result = new ParsedLoansDto(errors, parsedLoanDetails);
return result;
}
use of org.apache.poi.ss.usermodel.Row in project spring-framework by spring-projects.
the class XlsViewTests method testXlsxView.
@Test
public void testXlsxView() throws Exception {
View excelView = new AbstractXlsxView() {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
Sheet sheet = workbook.createSheet("Test Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Test Value");
}
};
excelView.render(new HashMap<>(), request, response);
Workbook wb = new XSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
assertEquals("Test Sheet", wb.getSheetName(0));
Sheet sheet = wb.getSheet("Test Sheet");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals("Test Value", cell.getStringCellValue());
}
use of org.apache.poi.ss.usermodel.Row in project spring-framework by spring-projects.
the class XlsViewTests method testXls.
@Test
public void testXls() throws Exception {
View excelView = new AbstractXlsView() {
@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
Sheet sheet = workbook.createSheet("Test Sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Test Value");
}
};
excelView.render(new HashMap<>(), request, response);
Workbook wb = new HSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray()));
assertEquals("Test Sheet", wb.getSheetName(0));
Sheet sheet = wb.getSheet("Test Sheet");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
assertEquals("Test Value", cell.getStringCellValue());
}
use of org.apache.poi.ss.usermodel.Row in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetExporter method buildAverageColumn.
@AddTrace(name = "buildAverageColumn")
private void buildAverageColumn(Sheet sheet, TeamHelper teamHelper) {
int farthestColumn = 0;
for (Row row : sheet) {
int last = row.getLastCellNum();
if (last > farthestColumn)
farthestColumn = last;
}
Map<Chart, Pair<LineChartData, List<ChartAxis>>> chartData = new HashMap<>();
Map<Metric<Void>, Chart> chartPool = new HashMap<>();
Iterator<Row> rowIterator = sheet.rowIterator();
for (int i = 0; rowIterator.hasNext(); i++) {
Row row = rowIterator.next();
Cell cell = row.createCell(farthestColumn);
if (i == 0) {
cell.setCellValue(getString(R.string.average));
cell.setCellStyle(mCache.getColumnHeaderStyle());
continue;
}
Cell first = row.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellStyle(first.getCellStyle());
int type = getMetricForScouts(mScouts.get(teamHelper), mCache.getMetricKey(row)).getType();
String rangeAddress = getCellRangeAddress(first, row.getCell(cell.getColumnIndex() - 1, MissingCellPolicy.CREATE_NULL_AS_BLANK));
switch(type) {
case BOOLEAN:
cell.setCellFormula("COUNTIF(" + rangeAddress + ", TRUE) / COUNTA(" + rangeAddress + ")");
mCache.setCellFormat(cell, "0.00%");
break;
case NUMBER:
cell.setCellFormula("SUM(" + rangeAddress + ")" + " / " + "COUNT(" + rangeAddress + ")");
buildTeamChart(row, teamHelper, chartData, chartPool);
break;
case STOPWATCH:
String excludeZeros = "\"<>0\"";
cell.setCellFormula("IF(COUNTIF(" + rangeAddress + ", " + excludeZeros + ") = 0, 0, AVERAGEIF(" + rangeAddress + ", " + excludeZeros + "))");
buildTeamChart(row, teamHelper, chartData, chartPool);
break;
case LIST:
sheet.setArrayFormula("INDEX(" + rangeAddress + ", " + "MATCH(" + "MAX(" + "COUNTIF(" + rangeAddress + ", " + rangeAddress + ")" + "), " + "COUNTIF(" + rangeAddress + ", " + rangeAddress + ")" + ", 0))", new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex()));
break;
case HEADER:
case TEXT:
// Nothing to average
break;
default:
throw new IllegalStateException();
}
}
for (Chart chart : chartData.keySet()) {
Pair<LineChartData, List<ChartAxis>> data = chartData.get(chart);
chart.plot(data.first, data.second.toArray(new ChartAxis[data.second.size()]));
if (chart instanceof XSSFChart) {
XSSFChart xChart = (XSSFChart) chart;
CTChart ctChart = xChart.getCTChart();
CTPlotArea plotArea = ctChart.getPlotArea();
setChartAxisTitle(plotArea.getValAxArray(0).addNewTitle(), "Values");
setChartAxisTitle(plotArea.getCatAxArray(0).addNewTitle(), "Scouts");
String name = getMetricForChart(xChart, chartPool).getName();
if (!TextUtils.isEmpty(name))
xChart.setTitle(name);
}
}
}
use of org.apache.poi.ss.usermodel.Row in project Robot-Scouter by SUPERCILEX.
the class SpreadsheetExporter method buildTeamAveragesSheet.
@AddTrace(name = "buildTeamAveragesSheet")
private void buildTeamAveragesSheet(Sheet averageSheet) {
Workbook workbook = averageSheet.getWorkbook();
Row headerRow = averageSheet.createRow(0);
headerRow.createCell(0);
List<Sheet> scoutSheets = getAdjustedList(workbook);
for (int i = 0; i < scoutSheets.size(); i++) {
Sheet scoutSheet = scoutSheets.get(i);
Row row = averageSheet.createRow(i + 1);
Cell rowHeaderCell = row.createCell(0);
rowHeaderCell.setCellValue(scoutSheet.getSheetName());
rowHeaderCell.setCellStyle(mCache.getRowHeaderStyle());
List<Row> metricsRows = getAdjustedList(scoutSheet);
rowIterator: for (Row averageRow : metricsRows) {
Cell averageCell = averageRow.getCell(averageRow.getLastCellNum() - 1);
Metric<?> keyMetric = mCache.getKeyMetric(averageRow.getCell(0));
if (TextUtils.isEmpty(getStringForCell(averageCell)) || keyMetric.getType() == TEXT) {
continue;
}
for (Cell keyCell : getAdjustedList(headerRow)) {
if (TextUtils.equals(keyMetric.getRef().getKey(), mCache.getMetricKey(keyCell))) {
setAverageFormula(scoutSheet, row.createCell(keyCell.getColumnIndex()), averageCell);
continue rowIterator;
}
}
Cell keyCell = headerRow.createCell(headerRow.getLastCellNum());
keyCell.setCellValue(averageRow.getCell(0).getStringCellValue());
keyCell.setCellStyle(mCache.getColumnHeaderStyle());
mCache.putKeyMetric(keyCell, keyMetric);
setAverageFormula(scoutSheet, row.createCell(keyCell.getColumnIndex()), averageCell);
}
}
buildAverageCharts(averageSheet);
}
Aggregations