在Excel POI中创建一个小圈子 [英] Create a small circle in excel POI

问题描述

我知道这个问题似乎很相似，但是我有不同的问题.我知道如何创建一个圆或一个形状.这就是我要创建的

I know this question might seem similar but I have different problem. I know how to create a circle or a shape. This is what I want to create

excel单元格中央有一个小圆圈

A little circle in the center of the excel cell

我可以制作的圈子，例如教程等:

The circle that I am able to make, looking at tutorials etc is:

这是我用来创建的代码:

This is the code I am using to create:

      CreationHelper helper = workbook.getCreationHelper();
       Drawing drawing = worksheet.createDrawingPatriarch();

       ClientAnchor anchor = helper.createClientAnchor();

       anchor.setCol1(0);
       anchor.setRow1(0); 
       anchor.setCol2(1);
       anchor.setRow2(1); 
       anchor.setDx1(255);
       anchor.setDx2(255);
       anchor.setDy1(0);
       anchor.setDy2(0);

       XSSFSimpleShape shape = ((XSSFDrawing)drawing).createSimpleShape((XSSFClientAnchor)anchor);
       shape.setShapeType(ShapeTypes.FLOW_CHART_CONNECTOR);
       shape.setFillColor(255, 0, 0);

我认为与dx1，dx2，dy1，dy2有关，但是在其中设置任何值均无效.

I think there is something to do with the dx1,dx2,dy1,dy2 but setting any value in there has no effect.

我需要以某种方式使该形状变小

I need to make that shape smaller somehow

推荐答案

如果形状仅在一个单元格内，则锚点也必须仅是一个单元格.然后用Dx和Dy完成定位.但是测量单位很特殊.它是EMU英制公制单位.

If the shape shall within one cell only then the anchor must also be only one cell. The positioning is done then with Dx and Dy. But the measurement unit is special. It is EMU English Metric Units.

因此，椭圆在单元格A1中的定位将像这样:

So the positioning of an ellipse in cell A1 will be like so:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.util.Units;

import java.io.FileOutputStream;
import java.io.IOException;


class CenterShapeInCell {

 public static void main(String[] args) {
  try {

   Workbook workbook = new XSSFWorkbook();
   Sheet sheet = workbook.createSheet("Sheet1");

   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);
   row.setHeight((short)(20*20));
   sheet.setColumnWidth(0, 20*256);

   CreationHelper helper = workbook.getCreationHelper();
   Drawing drawing = sheet.createDrawingPatriarch();

   ClientAnchor anchor = helper.createClientAnchor();

   //set anchor to A1 only
   anchor.setCol1(0);
   anchor.setRow1(0); 
   anchor.setCol2(0);
   anchor.setRow2(0); 

   //get the cell width of A1
   float cellWidthPx = sheet.getColumnWidthInPixels(0);
System.out.println(cellWidthPx);

   //set wanted shape size
   int shapeWidthPx = 20;
   int shapeHeightPx = 20;

   //calculate the position of left upper edge
   float centerPosPx = cellWidthPx/2f - (float)shapeWidthPx/2f;
System.out.println(centerPosPx);

   //set the position of left edge as Dx1 in unit EMU
   anchor.setDx1(Math.round(centerPosPx * Units.EMU_PER_PIXEL));

   //set the position of right edge as Dx2 in unit EMU
   anchor.setDx2(Math.round((centerPosPx + shapeWidthPx) * Units.EMU_PER_PIXEL));

   //set upper padding
   int upperPaddingPx = 4;

   //set upper padding as Dy1 in unit EMU
   anchor.setDy1(upperPaddingPx * Units.EMU_PER_PIXEL);

   //set upper padding + shape height as Dy2 in unit EMU
   anchor.setDy2((upperPaddingPx + shapeHeightPx) * Units.EMU_PER_PIXEL);

   XSSFSimpleShape shape = ((XSSFDrawing)drawing).createSimpleShape((XSSFClientAnchor)anchor);
   shape.setShapeType(ShapeTypes.ELLIPSE);
   shape.setFillColor(255, 0, 0);


   FileOutputStream fileOut = new FileOutputStream("CenterShapeInCell.xlsx");
   workbook.write(fileOut);
   fileOut.close();

  } catch (IOException ioex) {
  }
 }
}

看起来像:

但是我怀疑您真正想要的是使用交通信号灯符号的条件格式，如下所示:

But I suspect what you really want is a conditional formatting with traffic light symbols like so:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;

class ConditionalFormattingIconSet {

 public static void main(String[] args) {
  try {

   Workbook workbook = new XSSFWorkbook();

   Sheet sheet = workbook.createSheet("Sheet1");

   CellStyle cellStyle = workbook.createCellStyle();
   //cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // old apache poi versions
   cellStyle.setAlignment(HorizontalAlignment.CENTER); // current apache poi version 4.1.0
   //cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // old apache poi versions
   cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // current apache poi version 4.1.0

   Cell cell;

   for (int i = 0; i < 3; i++) {
    cell = sheet.createRow(i).createCell(0);
    cell.setCellValue(1+i);
    cell.setCellStyle(cellStyle);
   }

   SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

   ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(IconMultiStateFormatting.IconSet.GYR_3_TRAFFIC_LIGHTS);

   rule.getMultiStateFormatting().setIconOnly(true);

   ConditionalFormattingRule [] cfRules = {rule};

   CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:A3")};

   sheetCF.addConditionalFormatting(regions, cfRules);

   FileOutputStream fileOut = new FileOutputStream("ConditionalFormattingIconSet.xlsx");
   workbook.write(fileOut);
   fileOut.close();

  } catch (IOException ioex) {
  }
 }
}

外观类似:

这篇关于在Excel POI中创建一个小圈子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！