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.

Approach:

1. 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:
2. Create an instance of the workbook
3. Create a spreadsheet in the above workbook.
4. Create rows using XSSFRow
5. Create a cell using XSSFCell.
6. Create a patriarch by using HSSFPatriarch.
7. For positioning the shape on the excel sheet, create an anchor by using HSSFClientAnchor.
8. Set the shape type (line, oval, rectangle, etc…)
9. Set any other style details describing the shape. (eg: line thickness, etc…)
10. Writing the content to the workbook by defining the object of type FileOutputStream
11. Close the connection of the file.

<dependency>  
      <groupId>org.apache.poi</groupId>  
      <artifactId>poi</artifactId>  
      <version>3.9</version>  
</dependency>

Methods Required

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!
• Parameters:
  • 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_

Implementation:

Java

// Java Program to Creating an Oval Shape Using all the
// styling to Excel File using Apache POI

// Importing required classes
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSimpleShape;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

// Main class
public class GFG {

    // Main driver method
    public static void main(String args[])
        throws IOException
    {
        // Naming a workbook
        String excelfilename = "GFG.xls";

        // Creating a workbook
        Workbook workbook = new HSSFWorkbook();

        // Creating a spreadsheet by creating an object of
        // XSSFSheet and also give name
        Sheet spreadsheet
            = workbook.createSheet("XLDrawingShape");

        // Creating an object patriarch of HSSFPatriarch.
        HSSFPatriarch patriarch
            = (HSSFPatriarch)
                  spreadsheet.createDrawingPatriarch();

        // Creating an object anchor of HSSFClientAnchor
        // and also set a value
        HSSFClientAnchor anchor = new HSSFClientAnchor(
            0, 0, 1023, 255, (short)1, 0, (short)5, 4);

        // Creating an object shape of HSSFSimpleShape
        HSSFSimpleShape shape
            = patriarch.createSimpleShape(anchor);

        // Setting all the attributes of shape
        shape.setShapeType(
            HSSFSimpleShape.OBJECT_TYPE_OVAL);
        shape.setLineStyleColor(8, 8, 8);
        shape.setFillColor(100, 10, 150);
        shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3);
        shape.setLineStyle(HSSFShape.LINESTYLE_DOTSYS);

        // try block to check for exceptions
        try {

            // Placing the output file in default location
            // and also kept in try catch block
            FileOutputStream outputfile
                = new FileOutputStream(excelfilename);

            // Writing to workbook
            workbook.write(outputfile);

            // Closing workbook using close() method
            outputfile.close();

            // Displaying message for console window when
            // program is successfully executed
            System.out.println(excelfilename
                               + " is written successfully");
        }

        // Catch block to handle the exceptions
        catch (FileNotFoundException e) {

            // Displaying error message for console window
            // when program is not successfully executed
            System.out.println("ERROR!! " + e.getMessage());
        }
    }
}

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)