/** * 初始化函数 * * @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); } }