Java CreationHelper類代碼示例

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * @작성자 : 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();
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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);
	 
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * 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);
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * 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();
        }
    }
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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)); 	
	}
	
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * 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);
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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;
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
public static void main(String[] args)throws Exception
{
	String dataPath = "src/featurescomparison/workingwithdata/hyperlink/data/";
	
	Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();

    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);

    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell((short)0);
    cell.setCellValue("URL Link");

    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a file in the current directory
    cell = sheet.createRow(1).createCell((short)0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //e-mail link
    cell = sheet.createRow(2).createCell((short)0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:[email protected]?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);

    //link to a place in this workbook

    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");

    cell = sheet.createRow(3).createCell((short)0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);

    FileOutputStream out = new FileOutputStream(dataPath + "ApacheHyperlinks.xlsx");
    wb.write(out);
    out.close();
    
    System.out.println("Done..");
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * セルにハイパーリンクを設定する。
 * 
 * @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);
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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);
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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;

	}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * <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;
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * 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);
   }
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
/**
 * 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);
}
 

import org.apache.poi.ss.usermodel.CreationHelper; //導入依賴的package包/類
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;
}