Java Program to Draw a Shape in Excel Sheet using Apache POI
Apache POI supports customized printing by allowing users to select a range of cells in order to configure the desired print area in a worksheet using Java Program. The top-most shape is the patriarch. This is not visible on the sheet at all. To start drawing you need to call createPatriarch on the HSSFSheet class.
Let’s take an example to Create an oval shape using all the styling to Excel File using Apache POI.
- First, we need to add Maven Dependency to the maven project, we can include the POI dependency using the pom.xml file as shown below:
- Create an instance of the workbook
- Create a spreadsheet in the above workbook.
- Create rows using XSSFRow
- Create a cell using XSSFCell.
- Create a patriarch by using HSSFPatriarch.
- For positioning the shape on the excel sheet, create an anchor by using HSSFClientAnchor.
- Set the shape type (line, oval, rectangle, etc…)
- Set any other style details describing the shape. (eg: line thickness, etc…)
- Writing the content to the workbook by defining the object of type FileOutputStream
- Close the connection of the file.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>
Method 1: HSSFClientAnchor()
- HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
- Creates a new client anchor and sets the top-left and bottom-rightcoordinates of the anchor.
- Note: Microsoft Excel seems to sometimes disallow higher y1 than y2 or higher x1 than x2, you might need to reverse them and draw shapes vertically or horizontally flipped!
- dx1 – the x coordinate within the first cell.
- dy1 – the y coordinate within the first cell.
- dx2 – the x coordinate within the second cell.
- dy2 – the y coordinate within the second cell.
- col1 – the column (0 based) of the first cell.
- row1 – the row (0 based) of the first cell.
- col2 – the column (0 based) of the second cell.
- row2 – the row (0 based) of the second cell.
Method 2: createSimpleShape()
- Creates a simple shape. This includes such shapes as lines, rectangles, and ovals.
- Parameters: anchor the client anchor describes how this group is attached to the sheet.
- Returns: the newly created shape.
Method 3: setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL)
- Parameters: value – shapeType
Method 4: setLineStyleColor(8, 8, 8)
- The color is applied to the lines of this shape.
- Parameters: red green blue
Method 5: setFillColor(100, 10, 150)
- The color is used to fill this shape.
- Parameters: red green blue
Method 6: setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3)
- Sets the width of the line. 12700 = 1 pt.
- Parameters: lineWidth width in EMU’s. 12700EMU’s = 1 pt
Method 7: setLineStyle(HSSFShape.LINESTYLE_DOTSYS)
- Sets the line style.
- Parameters: lineStyle One of the constants in LINESTYLE_
Output: On console window
When the program is successfully executed.
GFG.xlsx is written successfully.
When the program is not successfully executed.
ERROR!! GFG.xlsx (The process cannot access the file because it is being used by another process)
Output: Workbook(excel file)