public CellStyle buildDimensionCellStyle(Sheet sheet) {
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
logger.debug("Header background color : " + headerBGColor);
short backgroundColorIndex =
headerBGColor != null
? IndexedColors.valueOf(headerBGColor).getIndex()
: IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
cellStyle.setFillForegroundColor(backgroundColorIndex);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
logger.debug("Header border color : " + bordeBorderColor);
short borderColorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(bordeBorderColor).getIndex()
: IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex();
cellStyle.setLeftBorderColor(borderColorIndex);
cellStyle.setRightBorderColor(borderColorIndex);
cellStyle.setBottomBorderColor(borderColorIndex);
cellStyle.setTopBorderColor(borderColorIndex);
Font font = sheet.getWorkbook().createFont();
Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
logger.debug("Header font size : " + headerFontSize);
short headerFontSizeShort =
headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
font.setFontHeightInPoints(headerFontSizeShort);
String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
logger.debug("Font name : " + fontName);
fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
font.setFontName(fontName);
String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
logger.debug("Dimension color : " + color);
short colorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(color).getIndex()
: IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
font.setColor(colorIndex);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setItalic(true);
cellStyle.setFont(font);
return cellStyle;
}
public CellStyle buildDataCellStyle(Sheet sheet) {
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR);
logger.debug("Cell background color : " + cellBGColor);
short backgroundColorIndex =
cellBGColor != null
? IndexedColors.valueOf(cellBGColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex();
cellStyle.setFillForegroundColor(backgroundColorIndex);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR);
logger.debug("Cell border color : " + bordeBorderColor);
short borderColorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(bordeBorderColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex();
cellStyle.setLeftBorderColor(borderColorIndex);
cellStyle.setRightBorderColor(borderColorIndex);
cellStyle.setBottomBorderColor(borderColorIndex);
cellStyle.setTopBorderColor(borderColorIndex);
Font font = sheet.getWorkbook().createFont();
Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE);
logger.debug("Cell font size : " + cellFontSize);
short cellFontSizeShort =
cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE;
font.setFontHeightInPoints(cellFontSizeShort);
String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
logger.debug("Font name : " + fontName);
fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
font.setFontName(fontName);
String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR);
logger.debug("Cell color : " + cellColor);
short cellColorIndex =
cellColor != null
? IndexedColors.valueOf(cellColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex();
font.setColor(cellColorIndex);
cellStyle.setFont(font);
return cellStyle;
}
private Result populateLoansTable(Sheet savingsTransactionSheet) {
Result result = new Result();
Workbook workbook = savingsTransactionSheet.getWorkbook();
CellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat("dd/mm/yy");
dateCellStyle.setDataFormat(df);
int rowIndex = 1;
Row row;
Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
try {
for (CompactSavingsAccount savingsAccount : savings) {
row = savingsTransactionSheet.createRow(rowIndex++);
writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
writeDate(
LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
row,
savingsAccount.getTimeline().getActivatedOnDate().get(2)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(1)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(0),
dateCellStyle);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
return result;
}
private Color getColor(Font font) {
if (helper instanceof HSSFHtmlHelper) {
return ((HSSFWorkbook) sheet.getWorkbook()).getCustomPalette().getColor(font.getColor());
} else {
return ((XSSFFont) font).getXSSFColor();
}
}
/**
* Compute width of a column based on a subset of the rows and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @param firstRow 0-based index of the first row to consider (inclusive)
* @param lastRow 0-based index of the last row to consider (inclusive)
* @return the width in pixels
*/
public static double getColumnWidth(
Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
AttributedString str;
TextLayout layout;
Workbook wb = sheet.getWorkbook();
DataFormatter formatter = new DataFormatter();
Font defaultFont = wb.getFontAt((short) 0);
str = new AttributedString(String.valueOf(defaultChar));
copyAttributes(defaultFont, str, 0, 1);
layout = new TextLayout(str.getIterator(), fontRenderContext);
int defaultCharWidth = (int) layout.getAdvance();
double width = -1;
for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
Row row = sheet.getRow(rowIdx);
if (row != null) {
Cell cell = row.getCell(column);
if (cell == null) {
continue;
}
double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
width = Math.max(width, cellWidth);
}
}
return width;
}
/**
* Watch list serve for formula changes. Basically all the rows appeared in the formula in the
* current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it
* will be ignored.
*
* @param wbWrapper XSSFEvaluationWorkbook used for formula parse.
* @param sheet current sheet.
* @return List row number for monitoring.
*/
private List<Integer> buildFormWatchList(
final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) {
List<Integer> watchList = new ArrayList<Integer>();
ConfigRange cRange = this.getConfigRange();
List<ConfigCommand> commandList = cRange.getCommandList();
if (commandList.size() <= 0) {
// if no command then no dynamic changes. then no need formula shifts.
return watchList;
}
int lastStaticRow = commandList.get(0).getTopRow() - 1;
if (lastStaticRow < 0) {
lastStaticRow = this.getTopRow();
}
Workbook wb = sheet.getWorkbook();
for (int i = this.getTopRow(); i <= this.getLastRow(); i++) {
Row row = sheet.getRow(i);
for (Cell cell : row) {
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String formula = cell.getCellFormula();
Ptg[] ptgs =
FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet));
for (int k = 0; k < ptgs.length; k++) {
Object ptg = ptgs[k];
// For area formula, only first row is watched.
// Reason is the lastRow must shift same rows with
// firstRow.
// Otherwise it's difficult to calculate.
// In case some situation cannot fit, then should make
// change to the formula.
int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg);
if (areaInt >= 0) {
addToWatchList(sheet, areaInt, lastStaticRow, watchList);
}
}
// when insert row, the formula may changed. so here is the workaround.
// change formula to user formula to preserve the row changes.
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(
ConfigurationHelper.USER_FORMULA_PREFIX
+ formula
+ ConfigurationHelper.USER_FORMULA_SUFFIX);
}
}
}
return watchList;
}
public ExcelAttributeSheetReader(
final Sheet sheet,
final AttributeMappingParameters mapping,
final CyServiceRegistrar serviceRegistrar) {
this.sheet = sheet;
this.mapping = mapping;
this.startLineNumber = mapping.getStartLineNumber();
this.parser = new AttributeLineParser(mapping, serviceRegistrar);
this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
this.formatter = new DataFormatter();
}
@Override
public void renderCell(Element element, int rowIndex, int columnIndex) {
Cell cell = sheet.getRow(rowIndex).createCell(columnIndex);
Double numericValue;
if (isDateCell(element)) {
DateFormat df = new SimpleDateFormat(getDateCellFormat(element));
try {
cell.setCellValue(df.parse(getElementText(element)));
} catch (ParseException pex) {
System.out.println("Invalid Usage");
}
} else if ((numericValue = getNumericValue(element)) != null) {
cell.setCellValue(numericValue);
} else {
cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING);
cell.setCellValue(getElementText(element));
}
Style style = styleMapper.getStyleForElement(element);
cell.setCellStyle(styleGenerator.getStyle(cell, style));
if (isDateCell(element)) {
CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
cell.getCellStyle()
.setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element)));
}
String commentText;
if ((commentText = getCellCommentText(element)) != null) {
ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element));
}
if (definesFreezePane(element)) {
sheet.createFreezePane(columnIndex, rowIndex);
}
}
private void setNames(Sheet worksheet) {
Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
ArrayList<String> officeNames =
new ArrayList<String>(Arrays.asList(officeSheetPopulator.getOfficeNames()));
// Office Names
Name officeGroup = savingsTransactionWorkbook.createName();
officeGroup.setNameName("Office");
officeGroup.setRefersToFormula("Offices!$B$2:$B$" + (officeNames.size() + 1));
// Clients Named after Offices
for (Integer i = 0; i < officeNames.size(); i++) {
Integer[] officeNameToBeginEndIndexesOfClients =
clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
Name name = savingsTransactionWorkbook.createName();
if (officeNameToBeginEndIndexesOfClients != null) {
name.setNameName("Client_" + officeNames.get(i));
name.setRefersToFormula(
"Clients!$B$"
+ officeNameToBeginEndIndexesOfClients[0]
+ ":$B$"
+ officeNameToBeginEndIndexesOfClients[1]);
}
}
// Counting clients with active savings and starting and end addresses of cells for naming
HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<String, Integer[]>();
ArrayList<String> clientsWithActiveSavings = new ArrayList<String>();
int startIndex = 1, endIndex = 1;
String clientName = "";
for (int i = 0; i < savings.size(); i++) {
if (!clientName.equals(savings.get(i).getClientName())) {
endIndex = i + 1;
clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
startIndex = i + 2;
clientName = savings.get(i).getClientName();
clientsWithActiveSavings.add(clientName);
}
if (i == savings.size() - 1) {
endIndex = i + 2;
clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
}
}
// Account Number Named after Clients
for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
Name name = savingsTransactionWorkbook.createName();
name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_"));
name.setRefersToFormula(
"SavingsTransaction!$Q$"
+ clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0]
+ ":$Q$"
+ clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
}
// Payment Type Name
Name paymentTypeGroup = savingsTransactionWorkbook.createName();
paymentTypeGroup.setNameName("PaymentTypes");
paymentTypeGroup.setRefersToFormula(
"Extras!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double getCellWidth(
Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
Sheet sheet = cell.getSheet();
Workbook wb = sheet.getWorkbook();
Row row = cell.getRow();
int column = cell.getColumnIndex();
int colspan = 1;
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress region = sheet.getMergedRegion(i);
if (containsCell(region, row.getRowNum(), column)) {
if (!useMergedCells) {
// If we're not using merged cells, skip this one and move on to the next.
return -1;
}
cell = row.getCell(region.getFirstColumn());
colspan = 1 + region.getLastColumn() - region.getFirstColumn();
}
}
CellStyle style = cell.getCellStyle();
int cellType = cell.getCellType();
// for formula cells we compute the cell width for the cached formula result
if (cellType == Cell.CELL_TYPE_FORMULA) cellType = cell.getCachedFormulaResultType();
Font font = wb.getFontAt(style.getFontIndex());
AttributedString str;
TextLayout layout;
double width = -1;
if (cellType == Cell.CELL_TYPE_STRING) {
RichTextString rt = cell.getRichStringCellValue();
String[] lines = rt.getString().split("\\n");
for (int i = 0; i < lines.length; i++) {
String txt = lines[i] + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
if (rt.numFormattingRuns() > 0) {
// TODO: support rich text fragments
}
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
} else {
String sval = null;
if (cellType == Cell.CELL_TYPE_NUMERIC) {
// Try to get it formatted to look the same as excel
try {
sval = formatter.formatCellValue(cell, dummyEvaluator);
} catch (Exception e) {
sval = String.valueOf(cell.getNumericCellValue());
}
} else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
sval = String.valueOf(cell.getBooleanCellValue()).toUpperCase();
}
if (sval != null) {
String txt = sval + defaultChar;
str = new AttributedString(txt);
copyAttributes(font, str, 0, txt.length());
layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.getRotation() != 0) {
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
AffineTransform trans = new AffineTransform();
trans.concatenate(
AffineTransform.getRotateInstance(style.getRotation() * 2.0 * Math.PI / 360.0));
trans.concatenate(AffineTransform.getScaleInstance(1, fontHeightMultiple));
width =
Math.max(
width,
((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
} else {
width =
Math.max(
width,
((layout.getBounds().getWidth() / colspan) / defaultCharWidth)
+ cell.getCellStyle().getIndention());
}
}
}
return width;
}
