/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(
new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment =
this.sheet
.createDrawingPatriarch()
.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
// =================================================================================================================================
private int exportFrontEquipListReport_header(
List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
CellStyle style = getHeaderStyle(wb, (short) 11);
CellStyle style2 = getHeaderStyle(wb, (short) 10);
// 第一行列标题
Row row_subtype = sheet.createRow(rownum++);
Cell cell_subtype_0 = row_subtype.createCell(0);
cell_subtype_0.setCellValue("");
cell_subtype_0.setCellStyle(style);
sheet.setColumnWidth(0, 700);
Cell cell_subtype_1 = row_subtype.createCell(1);
cell_subtype_1.setCellValue("");
cell_subtype_1.setCellStyle(style);
sheet.setColumnWidth(1, 1800);
Cell cell_subtype_2 = row_subtype.createCell(2);
cell_subtype_2.setCellValue("");
cell_subtype_2.setCellStyle(style);
sheet.setColumnWidth(2, 4800);
// 第二行列标题
Row row_prod = sheet.createRow(rownum++);
Cell cell_prod_0 = row_prod.createCell(0);
cell_prod_0.setCellValue("序号");
cell_prod_0.setCellStyle(style2);
Cell cell_prod_1 = row_prod.createCell(1);
cell_prod_1.setCellValue("点位编号");
cell_prod_1.setCellStyle(style2);
Cell cell_prod_2 = row_prod.createCell(2);
cell_prod_2.setCellValue("点位名称");
cell_prod_2.setCellStyle(style2);
int cell_num = 3;
for (FrontEquipListReport_subtype subtype : subtypes) {
int start_cell_num = cell_num;
Cell cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellValue(subtype.getSubtype_name());
cell_subtype.setCellStyle(style);
for (FrontEquipListReport_prod prod : subtype.getProds()) {
if (start_cell_num != cell_num) {
cell_subtype = row_subtype.createCell(cell_num);
cell_subtype.setCellStyle(style);
}
Cell cell_prod = row_prod.createCell(cell_num);
cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
cell_prod.setCellStyle(style2);
cell_num++;
}
// 对子类型进行横向的单元格合并
sheet.addMergedRegion(
new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
}
return rownum;
}
@SuppressWarnings("unchecked")
private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) {
Cell currentCell;
// write the section attributes to the row
for (int index = 0; index < bodyRow.length; index++) {
// create the cell
currentCell = currentRow.createCell(index);
// set the format of the cells
currentCell.setCellStyle(bodyStyle);
if (types[index] != Integer.MAX_VALUE) {
currentCell.setCellType(types[index]);
}
// put the value into the cell
switch (types[index]) {
case Cell.CELL_TYPE_NUMERIC:
currentCell.setCellValue((double) bodyRow[index]);
break;
case Cell.CELL_TYPE_FORMULA:
currentCell.setCellFormula(String.valueOf(bodyRow[index]));
break;
case Integer.MAX_VALUE:
HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index];
// sets the hyperlink to be blue and underline
HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook);
Font hyperlinkFont = workbook.createFont();
hyperlinkFont.setUnderline(Font.U_SINGLE);
hyperlinkFont.setColor(HSSFColor.BLUE.index);
hyperlinkStyle.setFont(hyperlinkFont);
currentCell.setCellStyle(hyperlinkStyle);
currentCell.setCellValue(cellInfo.get("Label"));
currentCell.setHyperlink(LinkDataToMap(cellInfo));
break; // MAX_VALUE integer will be used to denote hyperlinks
case Cell.CELL_TYPE_STRING:
currentCell.setCellValue(String.valueOf(bodyRow[index]));
break;
default:
throw new UnsupportedOperationException(
"The type "
+ types[index]
+ " is not supported."
+ " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)"
+ " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell "
+ "for more information on the types of cells.");
}
}
}
private void addSumBottom() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) (ROW_HEIGHT + 100));
for (int j = 0; j < 1000000; j++) {
if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
&& StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
break;
}
Cell cell = row.createCell(j);
cell.setCellStyle(Style.get(book).SUM);
if (j == 0) {
cell.setCellValue("合计");
} else {
cell.setCellValue(0);
}
if (j >= 7) {
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(j + 1),
5,
CellUtils.convertToABC(j + 1),
sheet.getLastRowNum()));
}
}
sheet.addMergedRegion(
new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
}
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
for (int j = 4; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
for (int k = 0; k <= row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
cell.setCellStyle(Style.get(book).SUM);
}
}
}
}
}
protected void createHeaderExtra(Row row, CellStyle style) {
Cell cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue("Actors");
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue("LastWeek");
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue("YesterDay");
cell = row.createCell(5);
cell.setCellStyle(style);
cell.setCellValue("History");
}
private Cell headInfo(CellStyle headerStyle, Row row) {
Cell cell = row.createCell(0);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.serialNumber"));
cell = row.createCell(1);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountNum"));
cell = row.createCell(STR_2);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountName"));
cell = row.createCell(STR_3);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.tranDate"));
cell = row.createCell(STR_4);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.currency"));
cell = row.createCell(STR_5);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.expenditure"));
cell = row.createCell(STR_6);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.income"));
cell = row.createCell(STR_7);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.balance"));
return cell;
}
@SuppressWarnings("rawtypes")
private void generateHeaderRow(
HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) {
CellStyle center = workBook.createCellStyle();
center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int columnType = Cell.CELL_TYPE_STRING;
String col = null, text = null;
short width = 15;
sheet.setDefaultColumnWidth(width);
Row headerRow = createRow(sheet, headerRowNum);
for (short i = 0; i < columnConfigJSONObject.length(); i++) {
JSONObject headerObj = columnConfigJSONObject.getJSONObject(i);
for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) {
String key = (String) iterator.next();
if ("columnType".equals(key)) {
// if(isNumber(headerObj.getString(key))) {
// columnType = Cell.CELL_TYPE_NUMERIC;
// }
} else {
col = key;
text = headerObj.getString(key);
}
}
headerIdArray.add(col);
Cell cell = createCell(headerRow, i, columnType);
cell.setCellValue(new HSSFRichTextString(text));
cell.setCellStyle(center);
}
}
/**
* {@inheritDoc}
*
* @see
* org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#setCellValue(org.apache.poi.ss.usermodel.Cell,
* java.lang.Object, org.eclipse.emf.ecore.EStructuralFeature,
* org.eclipse.emf.ecp.view.spi.context.ViewModelContext)
*/
@Override
public void setCellValue(
Cell cell,
Object fromObject,
EStructuralFeature eStructuralFeature,
ViewModelContext viewModelContext)
throws EMFFormsConverterException {
final EDataType eDataType = EAttribute.class.cast(eStructuralFeature).getEAttributeType();
final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
final StringBuilder result = new StringBuilder();
for (final Object value : (List<?>) fromObject) {
if (result.length() != 0) {
result.append(SEPARATOR);
}
result.append(eFactory.convertToString(eDataType, value));
}
String valueString = result.toString();
if (isDecimalNumber(eDataType.getInstanceClass())) {
valueString =
valueString.replace(
'.',
DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator());
}
cell.setCellValue(valueString);
cell.setCellStyle(
(CellStyle) viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT));
}
@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
private static void createRow(
Sheet sheet,
Map<String, Object> rowData,
List<String> mapping,
List<CellStyle> styles,
int startRowNum) {
Row row = sheet.createRow(startRowNum);
for (int i = 0; i < mapping.size(); i++) {
String name = mapping.get(i);
Object obj = rowData.get(name);
Cell newCell = row.createCell(i);
CellStyle style = styles.get(i);
newCell.setCellStyle(style);
if (obj != null) {
if (obj instanceof Date) {
newCell.setCellValue((Date) obj);
} else if (obj instanceof BigDecimal) {
double dd = ((BigDecimal) obj).doubleValue();
newCell.setCellValue(dd);
} else {
newCell.setCellValue(obj.toString());
}
}
}
}
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.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;
}
}
protected int buildDataMatrix(
Sheet sheet,
CrossTab cs,
int rowOffset,
int columnOffset,
CreationHelper createHelper,
MeasureFormatter measureFormatter)
throws JSONException {
CellStyle cellStyleForNA = buildNACellStyle(sheet);
Map<Integer, CellStyle> decimalFormats = new HashMap<Integer, CellStyle>();
int endRowNum = 0;
for (int i = 0; i < cs.getDataMatrix().length; i++) {
for (int j = 0; j < cs.getDataMatrix()[0].length; j++) {
String text = (String) cs.getDataMatrix()[i][j];
int rowNum = rowOffset + i;
int columnNum = columnOffset + j;
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
endRowNum = rowNum;
Cell cell = row.createCell(columnNum);
try {
double value = Double.parseDouble(text);
int decimals = measureFormatter.getFormatXLS(i, j);
Double valueFormatted = measureFormatter.applyScaleFactor(value, i, j);
cell.setCellValue(valueFormatted);
cell.setCellType(this.getCellTypeNumeric());
cell.setCellStyle(
getNumberFormat(decimals, decimalFormats, sheet, createHelper, cs.getCellType(i, j)));
} catch (NumberFormatException e) {
logger.debug("Text " + text + " is not recognized as a number");
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyleForNA);
}
}
}
return endRowNum;
}
/**
* This method creates the header row in the workbook object
*
* @param currentRow the object representing the header row
* @param theWorkbook the object representing the workbook, which will be modified by this method
*/
private void createHeaderRow(Row currentRow, String[] header) {
Cell currentCell;
// write the section attributes to the row
for (int index = 0; index < header.length; index++) {
currentCell = currentRow.createCell(index);
currentCell.setCellStyle(headerStyle);
currentCell.setCellValue(header[index]);
}
}
private void copyDefaultCellStyle(XSSFDataFormat format, Cell cell, XSSFCellStyle cs, int i) {
cs.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
cs.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cs.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
if (i == 1) cs.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:s"));
if (i == 2) cs.setDataFormat(format.getFormat("0"));
cell.setCellStyle(cs);
}
private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
CreationHelper creationHelper = workbook.getCreationHelper();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
retVal.setCellStyle(cellStyle);
if (null != cellObj) {
Calendar calendar = Calendar.getInstance();
calendar.setTime((Date) cellObj);
retVal.setCellValue(calendar);
}
}
private void setCellValue(
Object objValue, Cell cellCurrent, ExportColumn expCol, HashMap<String, CellStyle> hsStyle) {
if (objValue instanceof Double) {
cellCurrent.setCellValue((Double) objValue);
return;
}
if (objValue instanceof Integer) {
cellCurrent.setCellValue((Integer) objValue);
return;
}
if (objValue instanceof Date) {
cellCurrent.setCellValue((Date) objValue);
switch (expCol.getTimeDateFormat()) {
case ViewColumn.FMT_DATE:
cellCurrent.setCellStyle(hsStyle.get("DATE"));
break;
case ViewColumn.FMT_TIME:
cellCurrent.setCellStyle(hsStyle.get("TIME"));
break;
default:
cellCurrent.setCellStyle(hsStyle.get("DATETIME"));
break;
}
return;
}
if (objValue instanceof DateTime) {
try {
cellCurrent.setCellValue(((DateTime) objValue).toJavaDate());
switch (expCol.getTimeDateFormat()) {
case ViewColumn.FMT_DATE:
cellCurrent.setCellStyle(hsStyle.get("DATE"));
break;
case ViewColumn.FMT_TIME:
cellCurrent.setCellStyle(hsStyle.get("TIME"));
break;
default:
cellCurrent.setCellStyle(hsStyle.get("DATETIME"));
break;
}
} catch (Exception e) {
cellCurrent.setCellValue("" + objValue);
e.printStackTrace();
}
return;
}
if (objValue instanceof Vector<?>) {
Vector<?> vec = (Vector<?>) objValue;
StringBuilder sb = new StringBuilder();
for (Iterator<?> it = vec.iterator(); it.hasNext(); ) {
sb.append("" + it.next());
if (it.hasNext()) {
sb.append(";");
}
}
cellCurrent.setCellValue(sb.toString());
return;
}
cellCurrent.setCellValue("" + objValue);
}
private void fillData(
List<Map<String, Object>> detailList,
Sheet sheet1,
CellStyle firstCellStyle,
CellStyle commonCellStyle,
CellStyle amtCellStyle)
throws IOException {
int i = 1;
for (Map<String, Object> detail : detailList) {
if (i > STR_65535) {
break;
}
Row row = sheet1.createRow(i);
Cell cell = row.createCell(0);
cell.setCellStyle(firstCellStyle);
cell.setCellValue(i);
cell = row.createCell(1);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(detail.get("accountNo").toString());
cell = row.createCell(STR_2);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(new String(cstNameHost.getBytes("ISO8859_1"), "UTF8"));
cell = row.createCell(STR_3);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(formatDate(detail.get("transDate").toString()));
cell = row.createCell(STR_4);
cell.setCellStyle(firstCellStyle);
cell.setCellValue(detail.get("currency").toString());
cell = row.createCell(STR_5);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("debitamt").toString());
cell = row.createCell(STR_6);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("creditamt").toString());
cell = row.createCell(STR_7);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("balance").toString());
i++;
}
}
@Override
public Result upload() {
Result result = new Result();
Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction");
for (Transaction transaction : savingsTransactions) {
try {
Gson gson = new Gson();
String payload = gson.toJson(transaction);
restClient.post(
"recurringdepositaccounts/"
+ transaction.getAccountId()
+ "/transactions?command="
+ transaction.getTransactionType(),
payload);
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue("Imported");
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.LIGHT_GREEN));
} catch (Exception e) {
Cell savingsAccountIdCell =
savingsTransactionSheet
.getRow(transaction.getRowIndex())
.createCell(SAVINGS_ACCOUNT_NO_COL);
savingsAccountIdCell.setCellValue(transaction.getAccountId());
String message = parseStatus(e.getMessage());
Cell statusCell =
savingsTransactionSheet.getRow(transaction.getRowIndex()).createCell(STATUS_COL);
statusCell.setCellValue(message);
statusCell.setCellStyle(getCellStyle(workbook, IndexedColors.RED));
result.addError("Row = " + transaction.getRowIndex() + " ," + message);
}
}
savingsTransactionSheet.setColumnWidth(STATUS_COL, 15000);
writeString(STATUS_COL, savingsTransactionSheet.getRow(0), "Status");
return result;
}
public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
cell.setCellValue(NumberUtils.format(fieldValue, 0));
cell.setCellStyle(cellStyle);
}
/**
* Add the title of the columns in the row headers
*
* @param sheet
* @param titles list of titles
* @param columnHeadersNumber number of column headers
* @param startColumn first column of the crosstab in the xls
* @param startRow first row of the crosstab in the xls
* @param createHelper
* @throws JSONException
*/
protected void buildRowHeaderTitle(
Sheet sheet,
CrossTab cs,
int columnHeadersNumber,
int startColumn,
int startRow,
CreationHelper createHelper,
Locale locale,
CellStyle cellStyle)
throws JSONException {
List<String> titles = cs.getRowHeadersTitles();
if (titles != null) {
Row row = sheet.getRow(startRow + columnHeadersNumber);
for (int i = 0; i < titles.size(); i++) {
Cell cell = row.createCell(startColumn + i);
String text = titles.get(i);
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
}
if (cs.isMeasureOnRow()) {
Cell cell = row.createCell(startColumn + titles.size());
String text = "Measures";
if (locale != null) {
text =
EngineMessageBundle.getMessage("worksheet.export.crosstab.header.measures", locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
}
}
}
/**
* @param wb
* @param reviewerSheetName
*/
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
sheet.setColumnWidth(i, COL_WIDTH * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
}
}
/**
* Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----|
* | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | | 7 | | | 6 |-----| | | | 8 |
* |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|
*
* @param sheet The sheet of the XLS file
* @param siblings The siblings nodes of the headers structure
* @param rowNum The row number where the first sibling must be inserted
* @param columnNum The column number where the siblings must be inserted
* @param createHelper The file creation helper
* @throws JSONException
*/
protected void buildRowsHeaders(
Sheet sheet,
CrossTab cs,
List<Node> siblings,
int rowNum,
int columnNum,
CreationHelper createHelper,
Locale locale,
CellStyle cellStyle)
throws JSONException {
int rowsCounter = rowNum;
for (int i = 0; i < siblings.size(); i++) {
Node aNode = siblings.get(i);
List<Node> childs = aNode.getChilds();
Row row = sheet.getRow(rowsCounter);
Cell cell = row.createCell(columnNum);
String text = (String) aNode.getDescription();
if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
// apply the measure scale factor
text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
int descendants = aNode.getLeafsNumber();
if (descendants > 1) {
sheet.addMergedRegion(
new CellRangeAddress(
rowsCounter, // first row (0-based)
rowsCounter + descendants - 1, // last row (0-based)
columnNum, // first column (0-based)
columnNum // last column (0-based)
));
}
if (childs != null && childs.size() > 0) {
buildRowsHeaders(
sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle);
}
int increment = descendants > 1 ? descendants : 1;
rowsCounter = rowsCounter + increment;
}
}
public static void create(Workbook wb, String sheetName) {
int sheetNum = wb.getSheetIndex(sheetName);
if (sheetNum >= 0) {
wb.removeSheetAt(sheetNum);
}
Sheet sheet = wb.createSheet(sheetName);
CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
Row row = sheet.createRow(0);
for (int i = 0; i < HEADER_TITLES.length; i++) {
sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
sheet.setDefaultColumnStyle(i, defaultStyle);
Cell cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(HEADER_TITLES[i]);
}
}
// <editor-fold defaultstate="collapsed" desc="writeMetaData">
protected void writeMetaData(
Row row, int cellStart, CreationHelper createHelper, CellStyle cellStyle, MetaData meta) {
int cellIdx = cellStart;
for (Column column : meta.getColumns()) {
String colName = column.getName();
if (colName == null) continue;
Cell cell = row.getCell(cellIdx);
if (cell == null) {
cell = row.createCell(cellIdx);
}
cell.setCellValue(createHelper.createRichTextString(colName));
if (cellStyle != null) cell.setCellStyle(cellStyle);
cellIdx++;
}
}
private void updateSelectedCellsFontColor(Color newColor) {
if (spreadsheet != null && newColor != null) {
List<Cell> cellsToRefresh = new ArrayList<Cell>();
for (CellReference cellRef : spreadsheet.getSelectedCellReferences()) {
Cell cell = getOrCreateCell(cellRef);
// Workbook workbook = spreadsheet.getWorkbook();
XSSFCellStyle style = (XSSFCellStyle) cloneStyle(cell);
XSSFColor color = new XSSFColor(java.awt.Color.decode(newColor.getCSS()));
XSSFFont font = (XSSFFont) cloneFont(style);
font.setColor(color);
style.setFont(font);
cell.setCellStyle(style);
cellsToRefresh.add(cell);
}
// Update all edited cells
spreadsheet.refreshCells(cellsToRefresh);
}
}
