@Override public void close() { try { int nbS = wb.getNumberOfSheets(); for (int i = 0; i < nbS; i++) { Sheet s = wb.getSheetAt(i); if (s.getRow(0) != null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce // cas pas de possibilité de traiter la mise en page de gros fichiers for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) { Cell c = s.getRow(0).getCell(j); c.getSheet().autoSizeColumn(c.getColumnIndex()); } Cell firstCell = s.getRow(0).getCell(0); Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1); s.setAutoFilter( new CellRangeAddress( firstCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getRowIndex(), lastCell.getColumnIndex())); } } wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
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); }
/** * * Gets the formula content of a given cell from the POI-model as a stack of ptgs.The Ptgs are * in reverse polish notation. * * @param poiCell The given cell from the POI-model. * @return The contents of the given cell as a stack of ptgs. */ private Stack<Ptg> getPtgStackFor(org.apache.poi.ss.usermodel.Cell poiCell) { // Parse POI-Formula to ptgs. Ptg[] ptgArray = FormulaParser.parse( poiCell.getCellFormula(), poiIO.formulaParsingWorkbook, poiCell.getCellType(), poiIO.poiWorkbook.getSheetIndex(poiCell.getSheet())); // Create stack to keep the reverse polish notation and to ease the // transformation. Stack<Ptg> ptgs = new Stack<Ptg>(); for (Ptg ptg : ptgArray) { ptgs.push(ptg); } return ptgs; }
public static Object resolveCellValue( Cell cell, String emptyMarker, String nullMarker, Converter<String, ?> stringPreprocessor) { if (cell == null) return null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return convertString(cell, emptyMarker, nullMarker, stringPreprocessor); case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { double numericCellValue = cell.getNumericCellValue(); if (MathUtil.isIntegralValue(numericCellValue)) return ((Double) numericCellValue).longValue(); return numericCellValue; } case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_FORMULA: FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case HSSFCell.CELL_TYPE_STRING: return convertString(cellValue, emptyMarker, stringPreprocessor); case HSSFCell.CELL_TYPE_NUMERIC: return cellValue.getNumberValue(); case Cell.CELL_TYPE_BOOLEAN: return cellValue.getBooleanValue(); case HSSFCell.CELL_TYPE_BLANK: case HSSFCell.CELL_TYPE_ERROR: return null; default: throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType()); // CELL_TYPE_FORMULA is not supposed to be encountered here } default: throw new ConfigurationError("Not a supported cell type: " + cell.getCellType()); } }
public void outputNumericAdapter( DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); if (ObjectHelper.isNullOrEmptyString(fieldValue)) return; OutputNumericConfig config = dataBean.getOutputConfig(fieldName); Workbook workbook = cell.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); StringBuilder format = new StringBuilder("0"); for (int i = 0; i < config.floatCount(); i++) { if (i == 0) format.append("."); format.append("0"); } cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString())); cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount())); cell.setCellStyle(cellStyle); }
/** * {@inheritDoc} * * @see * org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell, * org.eclipse.emf.ecore.EStructuralFeature) */ @Override public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature) throws EMFFormsConverterException { String string; try { string = cell.getStringCellValue(); } catch (final IllegalStateException e) { throw new EMFFormsConverterException( String.format( "Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$ cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()), e); } if (string == null || string.length() == 0) { return Collections.emptyList(); } final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature); final EDataType eDataType = eAttribute.getEAttributeType(); if (isDecimalNumber(eDataType.getInstanceClass())) { string = string.replace( DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(), '.'); } final List<Object> result = new ArrayList<Object>(); final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance(); for (final String element : string.split(SEPARATOR)) { try { result.add(eFactory.createFromString(eDataType, element)); } // BEGIN SUPRESS CATCH EXCEPTION catch (final RuntimeException ex) { // END SUPRESS CATCH EXCEPTION throw new EMFFormsConverterException( MessageFormat.format( "The cell value {0} could not converted to a model value.", string)); // $NON-NLS-1$ } } return result; }
/** * 导出时间适配器 * * @param fieldValue * @param fieldName * @return * @throws AdapterException */ public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException { log.debug( "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}", fieldName, fieldValue); Date date = null; if (fieldValue == null) { log.debug("fieldValue is null return"); cell.setCellValue(""); return; } else if (fieldValue instanceof Date) { log.debug("fieldValue instanceof Date "); date = (Date) fieldValue; } else if (fieldValue instanceof String) { log.debug("fieldValue instanceof String "); InputDateConfig config = dataBean.getInputConfig(fieldName); try { date = DateUtil.formatToDate((String) fieldValue, config.format()); } catch (ParseException e) { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } } else if (fieldValue instanceof Long) { log.debug("fieldValue instanceof Long "); date = new Date((Long) fieldValue); } else { throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell); } Workbook workbook = cell.getSheet().getWorkbook(); OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle == null) cellStyle = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format())); cell.setCellStyle(cellStyle); cell.setCellValue(date); }
/** * 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; }
/** * Discription:[写一个单元格] * * @param cell 单元格 * @param value 写入的值 * @param valueType 写入的值的类型 * @param dateFormat 日期格式,默认yyyy-MM-dd * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public void writeCell(Cell cell, Object value, String valueType, String dateFormat) { if (cell == null || value == null) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } String cellValue = ""; if ("String".equals(valueType)) { cellValue = value.toString(); } else if ("int".equals(valueType)) { cellValue = String.valueOf(value); } else if ("float".equals(valueType)) { cellValue = String.valueOf(value); } else if ("double".equals(valueType)) { cellValue = String.valueOf(value); } else if ("Number".equals(valueType)) { cellValue = String.valueOf(value); } else if ("BigDecimal".equals(valueType)) { cellValue = String.valueOf(value); } else if ("byte[]".equals(valueType)) { // 有图片时,设置行高为60px; cell.getRow().setHeightInPoints(60); // 设置图片所在列宽度为80px,注意这里单位的一个换算 cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex()); anchor.setAnchorType(2); // 声明一个画图的顶级管理器 cell.getSheet() .createDrawingPatriarch() .createPicture( anchor, cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); return; } else if ("Date".equals(valueType)) { SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); cellValue = sdf.format(value); } else if ("boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else if ("Boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else { cellValue = String.valueOf(value); } cell.setCellValue(cellValue); return; }