private void formatCellDate(Sheet sheet, Cell cell, String format) { CellStyle style = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); style.setDataFormat(createHelper.createDataFormat().getFormat(format)); cell.setCellStyle(style); }
/** * @작성자 : KYJ * @작성일 : 2016. 9. 9. * @param sheet * @throws Exception */ final static void createDefaultLogo(Sheet sheet) throws Exception { Workbook workbook = sheet.getWorkbook(); byte[] defaultLogoImage = getDefaultLogoImage(); if(defaultLogoImage == null) return; int pictureIdx = workbook.addPicture(defaultLogoImage, Workbook.PICTURE_TYPE_PNG); CreationHelper creationHelper = workbook.getCreationHelper(); ClientAnchor anchor = creationHelper.createClientAnchor(); //new XSSFClientAnchor(); // anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE); Drawing createDrawingPatriarch = sheet.createDrawingPatriarch(); anchor.setDx1(0); anchor.setCol1(0); anchor.setRow1(0); //#1 테이블 셀의 너비에 의존적이지않게 사이즈조절. anchor.setAnchorType(AnchorType.MOVE_DONT_RESIZE); Picture createPicture = createDrawingPatriarch.createPicture(anchor, pictureIdx); //#2 테이블 셀의 너비에 의존적이지않게 사이즈조절. createPicture.resize(); }
/** * See the comment for the given cell * * @param cell * the cell * @param message * the comment message */ public static void setComment(HSSFCell cell, String message) { Drawing drawing = cell.getSheet().createDrawingPatriarch(); CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper(); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(cell.getRowIndex()); anchor.setRow2(cell.getRowIndex() + 1); anchor.setDx1(100); anchor.setDx2(1000); anchor.setDy1(100); anchor.setDy2(1000); // Create the comment and set the text+author Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(message); comment.setString(str); comment.setAuthor("TURNUS"); // Assign the comment to the cell cell.setCellComment(comment); }
/** * could be interesting for spring batch * * @param inputResource source of the data read by lines * @param outputResource generated .xlsx resource */ public void process(Resource inputResource, Resource outputResource) { SXSSFWorkbook wb = new SXSSFWorkbook(flushSize); try { wb.setCompressTempFiles(true); // temp files will be gzipped final Sheet sh = wb.createSheet(); InputStreamReader is = new InputStreamReader(inputResource.getInputStream()); BufferedReader br = new BufferedReader(is); final CreationHelper createHelper = sh.getWorkbook().getCreationHelper(); br.lines().forEachOrdered(string -> createRow(string, sh, createHelper)); FileOutputStream out = new FileOutputStream(outputResource.getFile()); wb.write(out); IOUtils.closeQuietly(out); } catch (IOException e) { logger.error("IOE", e); } finally { if (wb != null) { // dispose of temporary files backing this workbook on disk wb.dispose(); } } }
private static void fillFirstRow(Sheet currSheet, Map<String, Object> attributes, CreationHelper crHelper) { Row firstRow = currSheet.createRow(0); int idx = 0; firstRow.createCell(idx++).setCellValue("Value"); firstRow.createCell(idx++).setCellValue("Context"); firstRow.createCell(idx++).setCellValue("Doc"); firstRow.createCell(idx++).setCellValue("Page"); firstRow.createCell(idx++).setCellValue("Region"); firstRow.createCell(idx++).setCellValue("Line"); firstRow.createCell(idx++).setCellValue("Word"); Iterator<String> attributeIterator = attributes.keySet().iterator(); for (int i = 0; i < attributes.size(); i++){ String attributeName = attributeIterator.next(); //logger.debug("attributeName " + attributeName); firstRow.createCell(i+idx).setCellValue(crHelper.createRichTextString(attributeName)); } }
/** * Set a link to a cell. The link type should one of {@link Hyperlink} * * @param wb * the workbook which contains the cell * @param cell * the cell where the link is stored * @param address * the cell destination address * @param linkType * the type selected among {@link Hyperlink} */ public static void setLink(Workbook wb, HSSFCell cell, String address, int linkType) { CreationHelper helper = wb.getCreationHelper(); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setUnderline(Font.U_SINGLE); font.setColor(IndexedColors.BLUE.getIndex()); style.setFont(font); Hyperlink link = helper.createHyperlink(linkType); link.setAddress(address); cell.setHyperlink(link); cell.setCellStyle(style); }
protected Cell getCell(Column column) { Cell cell = CellUtil.getCell(getRow(), column.getIndex()); ColumnOption option = getColumnOption(column); if (option != null) { Optional<String> formatOption = option.getDataFormat(); if (formatOption.isPresent()) { String formatString = formatOption.get(); CellStyle style = styleMap.get(formatString); if (style == null) { Workbook book = sheet.getWorkbook(); style = book.createCellStyle(); CreationHelper helper = book.getCreationHelper(); short fmt = helper.createDataFormat().getFormat(formatString); style.setDataFormat(fmt); styleMap.put(formatString, style); } cell.setCellStyle(style); } } return cell; }
/** * セルにハイパーリンクを設定する。 * * @param cell セル * @param type リンクタイプ * @param address ハイパーリンクアドレス * @see org.apache.poi.common.usermodel.Hyperlink */ public static void setHyperlink( Cell cell, HyperlinkType hyperlinkType, String address) { Workbook wb = cell.getRow().getSheet().getWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Hyperlink link = createHelper.createHyperlink( hyperlinkType); if ( link instanceof HSSFHyperlink) { (( HSSFHyperlink) link).setTextMark( address); } else if ( link instanceof XSSFHyperlink) { (( XSSFHyperlink) link).setAddress( address); } cell.setHyperlink( link); }
public String getFormula2(Point point, Cell cell) { if(Utils.equals(comment, "空文字")) { return null; } // ダミーでリンクも設定する final CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper(); final Hyperlink link = helper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(comment); cell.setHyperlink(link); final int rowNumber = point.y + 1; return String.format("HYPERLINK(D%s,\"リンク\"&A%s)", rowNumber, rowNumber); }
private Comment createCellComment(String author, String comment) { // comments only supported for XLSX if (data.sheet instanceof XSSFSheet) { CreationHelper factory = data.wb.getCreationHelper(); Drawing drawing = data.sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment cmt = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(comment); cmt.setString(str); cmt.setAuthor(author); return cmt; } return null; }
/** * <p>Place the Hyperlink in the Cell, which replaces any other value left * behind in the Cell.</p> * @return Whether the first <code>Cell</code> in the <code>Block</code> * associated with this <code>Tag</code> was processed. */ public boolean process() { TagContext context = getContext(); Sheet sheet = context.getSheet(); Block block = context.getBlock(); int left = block.getLeftColNum(); int top = block.getTopRowNum(); // It should exist in this Cell; this Tag was found in it. Row row = sheet.getRow(top); Cell cell = row.getCell(left); SheetUtil.setCellValue(cell, myValue); CreationHelper helper = sheet.getWorkbook().getCreationHelper(); Hyperlink hyperlink = helper.createHyperlink(myLinkType); hyperlink.setAddress(myAddress); cell.setHyperlink(hyperlink); BlockTransformer transformer = new BlockTransformer(); transformer.transform(context, getWorkbookContext()); return true; }
/** * Find any <code>Expressions</code> embedded in the given string, evaluate * them, and replace the expressions with the resulting values. If the * entire string consists of one <code>Expression</code>, then the returned * value may be any <code>Object</code>. * * @param richTextString The rich text string, with possibly embedded * expressions. * @param helper A <code>CreationHelper</code> that can create the proper * <code>RichTextString</code>. * @param beans A <code>Map</code> mapping strings to objects. * @return A new string, with any embedded expressions replaced with the * expression string values. */ public static Object evaluateString(RichTextString richTextString, CreationHelper helper, Map<String, Object> beans) { String value = richTextString.getString(); List<Expression> expressions = getExpressions(value); if (value.startsWith(Expression.BEGIN_EXPR) && value.endsWith(Expression.END_EXPR) && expressions.size() == 1) { Expression expression = new Expression(value.substring(2, value.length() - 1)); Object result = expression.evaluate(beans); if (result instanceof String) { return RichTextStringUtil.replaceAll(richTextString, helper, value, (String) result, true); } else { return result; } } else { return replaceExpressions(richTextString, helper, expressions, beans); } }
/** * Replace all expressions with their evaluated results. This attempts to * preserve any formatting within the <code>RichTextString</code>. * @param richTextString The entire string, with possibly many expressions * and possibly embedded formatting. * @param helper A <code>CreationHelper</code> that can create the proper * <code>RichTextString</code>. * @param expressions A <code>List</code> of <code>Expressions</code>. * @param beans A <code>Map</code> of beans to provide context for the * <code>Expressions</code>. * @return A <code>RichTextString</code> with all expressions replaced with * their evaluated results, and formatted preserved as best as possible. */ private static RichTextString replaceExpressions(RichTextString richTextString, CreationHelper helper, List<Expression> expressions, Map<String, Object> beans) { ArrayList<String> exprStrings = new ArrayList<String>(expressions.size()); ArrayList<String> exprValues = new ArrayList<String>(expressions.size()); for (Expression expr : expressions) { exprStrings.add(BEGIN_EXPR + expr.myExpression + END_EXPR); Object result = expr.evaluate(beans); if (result != null) exprValues.add(result.toString()); else exprValues.add(""); } return RichTextStringUtil.replaceValues(richTextString, helper, exprStrings, exprValues); }
public synchronized Workbook getWorkbook() { if (this.workbook == null) { this.workbook = new SXSSFWorkbook(10); this.style = this.workbook.createCellStyle(); CreationHelper helper = this.workbook.getCreationHelper(); this.style.setDataFormat(helper.createDataFormat().getFormat("MM/DD/YY")); XSSFWorkbook wb = this.workbook.getXSSFWorkbook(); POIXMLProperties props = wb.getProperties(); POIXMLProperties.CustomProperties custProp = props.getCustomProperties(); custProp.addProperty("dataset", this.context.getId(this.sheetName)); } return this.workbook; }
private Comment createCellComment( String author, String comment ) { // comments only supported for XLSX if ( data.sheet instanceof XSSFSheet ) { CreationHelper factory = data.wb.getCreationHelper(); Drawing drawing = data.sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment cmt = drawing.createCellComment( anchor ); RichTextString str = factory.createRichTextString( comment ); cmt.setString( str ); cmt.setAuthor( author ); return cmt; } return null; }
/** * 抽象出图片生成业务代码 * * @throws IOException */ private void extractPicturePortion(String svgString, XSSFWorkbook wb, XSSFSheet sheet, int startCol, int endCol, int startRow, int endRow) throws IOException { // 图片 if (org.apache.commons.lang3.StringUtils.isNotBlank(svgString)) { byte[] safeDataBytes = new BASE64Decoder().decodeBuffer(svgString); int pictureIdx = wb.addPicture(safeDataBytes, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = wb.getCreationHelper(); // Create the drawing patriarch. This is the top level container for // all shapes. Drawing drawing = sheet.createDrawingPatriarch(); // add a picture shape ClientAnchor anchor = helper.createClientAnchor(); // set top-left corner of the picture, // subsequent call of Picture#resize() will operate relative to it anchor.setCol1(startCol); anchor.setCol2(endCol); anchor.setRow1(startRow); anchor.setRow2(endRow); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(0); anchor.setDy2(0); anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(1); } }
/** 设置超链接属性 * @param cell * @param processor * @param returnVal * @param current */ private static void setHyperlink(Cell cell, LinkProcessor processor, Object returnVal, Object current) { int linkType = 0 ; String prefix = ""; switch (processor.getLinkType()) { case Url: linkType = Hyperlink.LINK_URL; prefix = "http"; break; case Document: linkType = Hyperlink.LINK_DOCUMENT; break; case Email: linkType = Hyperlink.LINK_EMAIL; prefix = "mailto:"; break; case File: linkType = Hyperlink.LINK_FILE; break; } CreationHelper creationHelper = cell.getSheet().getWorkbook().getCreationHelper(); org.apache.poi.ss.usermodel.Hyperlink hyperlink = creationHelper.createHyperlink(linkType); String address = processor.getLinkAddress(returnVal, current); if(!address.startsWith(prefix)){ if(linkType == Hyperlink.LINK_EMAIL){ address = prefix + address; }else{ address = "http://" + address; } } hyperlink.setAddress(address); cell.setHyperlink(hyperlink); }
public static void evaluateFormulas(String fileName) throws IOException { xLogger.fine("Entering evaluateFormulas. fileName: {0}", fileName); // Create a InoutStream from the bytes in the cloud storage. // Create a template workbook // Evaluate the formulas // Save the workbook. if (fileName == null || fileName.isEmpty()) { xLogger.severe("Cannot evaluate formulas in a null or empty file"); return; } InputStream is = null; OutputStream os = null; // Create a workbook from the bytes try { // Get the template bytes from GCS ( Note: By now the data has been added to the appropriate sheet/s) is = _storageUtil.getInputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName); if (is == null) { xLogger.severe("Failed to create Input stream for {0}", fileName); return; } Workbook templateWb = WorkbookFactory.create( is); // From the bytes downloaded from the google cloud storage, form the Workbook if (templateWb != null) { CreationHelper createHelper = templateWb.getCreationHelper(); xLogger.fine("Created createHelper. {0}", createHelper); if (createHelper != null) { FormulaEvaluator evaluator = createHelper.createFormulaEvaluator(); xLogger.fine("Created evaluator. {0}", evaluator); if (evaluator != null) { evaluator.evaluateAll(); xLogger.fine("After evaluator.evaluateAll"); templateWb.setForceFormulaRecalculation( true); // Added this line because some formula cells were not getting updated even after calling evaluateAll xLogger.fine("After templateWb.setForceFormulaRecalculation"); // Write to file xLogger.fine("Now creating baos"); os = _storageUtil .getOutputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName, false); xLogger.fine("os: {0}", os); templateWb.write(os); // Write the workbook to OutputStream xLogger.fine("Wrote templateWb to baos"); } // end if evaluator != null } // end if createHelper != null } // end if templateWb != null } catch (Exception e) { xLogger.severe("{0} while evaluating formulas in the file {1}. Message: {2}", e.getClass().getName(), fileName, e.getMessage(), e); } finally { if (is != null) { is.close(); } if (os != null) { os.close(); } } xLogger.fine("Exiting evaluateFormulas"); }
ExcelTestHelper(final String parent, boolean generateXls) throws Exception { this.xls = generateXls; // Create a test Excel sheet with all types of supported data Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); DataFormat dataFormat = creationHelper.createDataFormat(); short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss"); CellStyle style = wb.createCellStyle(); style.setDataFormat(fmt); Sheet sheetWithHeader = wb.createSheet("Sheet 1"); // Create header row Row headerRow = sheetWithHeader.createRow((short) 0); headerRow.createCell(0).setCellValue("Number"); headerRow.createCell(1).setCellValue("String1"); headerRow.createCell(2).setCellValue("String2"); headerRow.createCell(3).setCellValue("MyTime"); headerRow.createCell(4).setCellValue("Formula"); headerRow.createCell(5).setCellValue("Boolean"); headerRow.createCell(6).setCellValue("Error"); generateSheetData(sheetWithHeader, style, (short)1); Sheet sheetWithoutHeader = wb.createSheet("Sheet 2"); generateSheetData(sheetWithoutHeader, style, (short)0); testFilePath = new File(parent, "excelTestFile").getPath(); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(testFilePath); wb.write(fileOut); fileOut.close(); }
private static void ExcelTest(String Path) { Workbook wb = new XSSFWorkbook(); Sheet employees = wb.createSheet(WorkbookUtil.createSafeSheetName("Mitarbeiter")); wb.getNumberOfSheets(); wb.getSheet("test").getLastRowNum(); Sheet s2 = wb.createSheet(WorkbookUtil.createSafeSheetName("Schorsch")); CreationHelper crHelper = wb.getCreationHelper(); Row firstRow = employees.createRow(0); firstRow.createCell(0).setCellValue(crHelper.createRichTextString("Vorname")); firstRow.createCell(1).setCellValue(crHelper.createRichTextString("Nachname")); firstRow.createCell(2).setCellValue(crHelper.createRichTextString("Geburtsdatum")); Row secondRow = employees.createRow(1); secondRow.createCell(0).setCellValue(crHelper.createRichTextString("Santa")); secondRow.createCell(1).setCellValue(crHelper.createRichTextString("Claus")); secondRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1823-12-23")); Row thirdRow = employees.createRow(2); thirdRow.createCell(0).setCellValue(crHelper.createRichTextString("Oster")); thirdRow.createCell(1).setCellValue(crHelper.createRichTextString("Hase")); thirdRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1682-01-01")); CellStyle formatTableHead = wb.createCellStyle(); formatTableHead.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); formatTableHead.setFillPattern(CellStyle.SOLID_FOREGROUND); Font fontTableHead = wb.createFont(); fontTableHead.setColor(IndexedColors.WHITE.getIndex()); formatTableHead.setFont(fontTableHead); firstRow.getCell(0).setCellStyle(formatTableHead); firstRow.getCell(1).setCellStyle(formatTableHead); firstRow.getCell(2).setCellStyle(formatTableHead); CellStyle formatGebDate = wb.createCellStyle(); formatGebDate.setDataFormat(crHelper.createDataFormat().getFormat("dd.mm.yy")); secondRow.getCell(2).setCellStyle(formatGebDate); thirdRow.getCell(2).setCellStyle(formatGebDate); employees.autoSizeColumn(0); employees.autoSizeColumn(1); employees.autoSizeColumn(2); FileOutputStream fOut; try { fOut = new FileOutputStream(Path); wb.write(fOut); fOut.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
private CellStyle getDateCellStyle(Sheet sheet) { if (dateCellStyle == null) { dateCellStyle = sheet.getWorkbook().createCellStyle(); CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yyyy hh:mm:ss")); // TODO flexible } return dateCellStyle; }
public static HSSFCellStyle createCelStyleForTime(HSSFWorkbook workBook) { HSSFCellStyle cellStyle = workBook.createCellStyle(); CreationHelper createHelper = workBook.getCreationHelper(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss")); return cellStyle; }
public void setValue(int rowNumber, int columnNumber, String value) throws CasterException { if(value==null) value=""; Sheet sheet = workbook.getSheet(getExcelSheetName()); // get Row Row row = sheet.getRow(rowNumber); if(row==null) row = sheet.createRow(rowNumber); // get Cell Cell cell = row.getCell(columnNumber); CellStyle style = null; if(cell != null) { style = cell.getCellStyle(); row.removeCell(cell); } cell = row.createCell(columnNumber); if(style != null) cell.setCellStyle(style); CreationHelper createHelper = workbook.getCreationHelper(); boolean isFormula=style != null && style.getDataFormatString().equals("@"); if(!isFormula && Decision.isNumeric(value)) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); double dbl = Caster.toDoubleValue(value); cell.setCellValue(dbl); _expandColumnWidth(sheet,Caster.toString(dbl),columnNumber); } else if(StringUtil.isEmpty("")) { cell.setCellType(Cell.CELL_TYPE_BLANK); cell.setCellValue(createHelper.createRichTextString("")); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(createHelper.createRichTextString(value)); _expandColumnWidth(sheet,value,columnNumber); } }
private static void createSheet(XSSFWorkbook wb, Report report) { String sheetName = Long.toString(report.getAppId());//name of sheet XSSFSheet sheet = wb.createSheet(sheetName); CellStyle cellStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat(DATE_FORMAT)); createFirstRow(sheet); int r = 1; //iterating r number of rows for (Review review : report.getReviews()) { XSSFRow row = sheet.createRow(r); row.createCell(0).setCellValue(review.getVersion()); row.createCell(1).setCellValue(review.getRate()); row.createCell(2).setCellValue(review.getTitle()); row.createCell(3).setCellValue(review.getBody()); XSSFCell dateCell = row.createCell(4); dateCell.setCellValue(review.getDate()); dateCell.setCellStyle(cellStyle); row.createCell(5).setCellValue(review.getCountry().getName()); r++; } }
protected void addChart(HSSFWorkbook workbook, byte[] chart, String name) { int pictureIndex = workbook.addPicture(chart, HSSFWorkbook.PICTURE_TYPE_PNG); HSSFSheet sheet = workbook.createSheet(name); addTitle(sheet, name, 0); Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setRow1(1); anchor.setCol1(0); Picture picture = drawing.createPicture(anchor, pictureIndex); picture.resize(); }
public static void main(String[] args) throws Exception { String dataPath = "src/featurescomparison/workingwithworkbook/adddataincells/data/"; Workbook wb = new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(dataPath + "DataInCells_Apache_Out.xls"); wb.write(fileOut); fileOut.close(); // Print message System.out.println("Data Added Successfully"); }
public static void main(String[] args) throws Exception { String dataPath = "src/featurescomparison/workingwithworkbook/addimages/data/"; //create a new workbook Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); //add picture data to this workbook. InputStream is = new FileInputStream(dataPath + "aspose.jpg"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); is.close(); CreationHelper helper = wb.getCreationHelper(); //create sheet Sheet sheet = wb.createSheet(); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(3); anchor.setRow1(2); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(); //save workbook String file = dataPath + "ApacheImage.xls"; if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); System.out.println("Done..."); }
private CellStyle createDateStyle(Workbook wb) { CreationHelper createHelper = wb.getCreationHelper(); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat( "yyyy-MM-dd HH:mm:ss")); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); return cellStyle; }
@Override public void render(OutputStream outputStream, Object model, String mimeType, Map<String, Object> parameters) { Validate.isTrue(StringUtils.equals(mimeType, "application/xls")); try { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); CreationHelper createHelper = wb.getCreationHelper(); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); // Or do it on one line. CustomerBean bean = (CustomerBean) model; row.createCell(1).setCellValue(bean.getCustomerNo()); row.createCell(2).setCellValue(createHelper.createRichTextString(bean.getFirstName())); row.createCell(3).setCellValue(createHelper.createRichTextString(bean.getLastName())); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); Cell cell = row.createCell(4); cell.setCellValue(bean.getBirthDate()); cell.setCellStyle(cellStyle); row.createCell(5).setCellValue(bean.getMailingAddress()); row.createCell(6).setCellValue(bean.getMarried()); row.createCell(7).setCellValue(bean.getNumberOfKids()); row.createCell(8).setCellValue(bean.getFavouriteQuote()); row.createCell(9).setCellValue(bean.getEmail()); row.createCell(10).setCellValue(bean.getLoyaltyPoints()); wb.write(outputStream); outputStream.close(); } catch (Exception e) { throw new RuntimeException(e); } }
private void initColumnCellStyles() { columnCellStyles = new CellStyle[tableRDesc.getRootNode().getChildren().size()]; for (int col = 0; col < tableRDesc.getRootNode().getChildren().size(); col++) { Node<TableRDescBase> columnNode = tableRDesc.getRootNode().getChildren().get(col); ColRDesc colRenderDesc = (ColRDesc) columnNode.getNodeElement(); FDesc fdesc = getFieldDescForColumn(columnNode); CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); if (fdesc instanceof LongFDesc) { cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat( IFConsts.NumberFormat.defaultExcelWholeNumberFormat)); } else if (fdesc instanceof DoubleFDesc) { cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat( IFConsts.NumberFormat.defaultExcelNumberFormat)); } else { cellStyle.setAlignment(CellStyle.ALIGN_CENTER); } String dataFormatOverride = null; if (colRenderDesc.getPropValue(ColRDesc.EXCEL_NUMBERFORMAT) != null) { dataFormatOverride = colRenderDesc.getPropValue(ColRDesc.EXCEL_NUMBERFORMAT); } if (dataFormatOverride != null) { cellStyle .setDataFormat(createHelper.createDataFormat().getFormat(dataFormatOverride)); } columnCellStyles[col] = cellStyle; } }
public CellStyle getOrCreateCellStyle(String format) { if (definedStyles == null) { definedStyles = new HashMap<String, CellStyle>(); } if (!definedStyles.containsKey(format)) { CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format)); definedStyles.put(format, cellStyle); } return definedStyles.get(format); }
public void fillCellStyle (@Nonnull final Workbook aWB, @Nonnull final CellStyle aCS, @Nonnull final CreationHelper aCreationHelper) { if (m_eAlign != null) aCS.setAlignment (m_eAlign); if (m_eVAlign != null) aCS.setVerticalAlignment (m_eVAlign); aCS.setWrapText (m_bWrapText); if (m_sDataFormat != null) aCS.setDataFormat (aCreationHelper.createDataFormat ().getFormat (m_sDataFormat)); if (m_eFillBackgroundColor != null) aCS.setFillBackgroundColor (m_eFillBackgroundColor.getIndex ()); if (m_eFillForegroundColor != null) aCS.setFillForegroundColor (m_eFillForegroundColor.getIndex ()); if (m_eFillPattern != null) aCS.setFillPattern (m_eFillPattern); if (m_eBorderTop != null) aCS.setBorderTop (m_eBorderTop); if (m_eBorderRight != null) aCS.setBorderRight (m_eBorderRight); if (m_eBorderBottom != null) aCS.setBorderBottom (m_eBorderBottom); if (m_eBorderLeft != null) aCS.setBorderLeft (m_eBorderLeft); if (m_nFontIndex >= 0) aCS.setFont (aWB.getFontAt (m_nFontIndex)); }
protected CellStyle createDateFormatCellStyle(final Workbook wb) { final CreationHelper createHelper = wb.getCreationHelper(); final short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd"); final CellStyle dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(dateFormat); return dateCellStyle; }
/** * Replace all occurrences of the given collection expression name with the * given item name, in preparation for implicit collections processing * loops. * @param sheet The <code>Sheet</code> on which the <code>Block</code> lies. * @param block The <code>Block</code> in which to perform the replacement. * @param collExpr The collection expression string to replace. * @param itemName The item name that replaces the collection expression. */ public static void setUpBlockForImplicitCollectionAccess(Sheet sheet, Block block, String collExpr, String itemName) { int left = block.getLeftColNum(); int right = block.getRightColNum(); int top = block.getTopRowNum(); int bottom = block.getBottomRowNum(); CreationHelper helper = sheet.getWorkbook().getCreationHelper(); // Look at the given range of Cells in the given range of rows. for (int rowNum = top; rowNum <= bottom; rowNum++) { Row row = sheet.getRow(rowNum); if (row != null) { for (int cellNum = left; cellNum <= right; cellNum++) { Cell cell = row.getCell(cellNum); if (cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING) { RichTextString value = cell.getRichStringCellValue(); cell.setCellValue(RichTextStringUtil.replaceAll( value, helper, collExpr, itemName, false, 0, true)); } } } } }
@Override public void inlineToXls(HSSFPatriarch patriarch, HSSFCell resultCell, Object paramValue, Matcher paramsMatcher) { try { Image image = new Image(paramValue, paramsMatcher); if (image.isValid()) { HSSFSheet sheet = resultCell.getSheet(); HSSFWorkbook workbook = sheet.getWorkbook(); int pictureIdx = workbook.addPicture(image.imageContent, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(resultCell.getColumnIndex()); anchor.setRow1(resultCell.getRowIndex()); anchor.setCol2(resultCell.getColumnIndex()); anchor.setRow2(resultCell.getRowIndex()); if (patriarch == null) { throw new IllegalArgumentException(String.format("No HSSFPatriarch object provided. Charts on this sheet could cause this effect. Please check sheet %s", resultCell.getSheet().getSheetName())); } HSSFPicture picture = patriarch.createPicture(anchor, pictureIdx); Dimension size = ImageUtils.getDimensionFromAnchor(picture); double actualHeight = size.getHeight() / EMU_PER_PIXEL; double actualWidth = size.getWidth() / EMU_PER_PIXEL; picture.resize((double) image.width / actualWidth, (double) image.height / actualHeight); } } catch (IllegalArgumentException e) { throw new ReportFormattingException("An error occurred while inserting bitmap to xls file", e); } }
@Override public void setCellValue(Cell cell, String value) { CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper(); cell.setCellValue(helper.createRichTextString(value)); }
