Spire.XLS for Java provides you with the ability to shrink text to fit in a cell by using the setShrinkToFit method of the CellStyleObject class. The setShrinkToFit method accepts the following parameter:

boolean: specify whether to shrink text to fit in a cell.

The following example shows how to shrink text to fit in a cell in Excel using Spire.XLS for Java.

import com.spire.xls.*;

public class ShrinkTextToFitInACell {
    public static void main(String []args) throws Exception {
        //Create a workbook instance
        Workbook workbook = new Workbook();

        //Load the Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Get the cell range to shrink text
        CellRange cell = sheet.getRange().get("B2:B3");

        //Enable “shrink to fit”
        cell.getCellStyle().setShrinkToFit(true);

        //Save the file
        workbook.saveToFile("ShrinkTextToFitInACell.xlsx", ExcelVersion.Version2013);
    }
}

The input Excel:

Shrink Text to Fit in a Cell in Excel in Java

The output Excel:

Shrink Text to Fit in a Cell in Excel in Java

Java: Wrap or Unwrap Text in Excel Cells

2022-01-06 06:29:00 Written by Koohji

In the process of manipulating Excel worksheets, sometimes you may encounter the situation where the text in a cell is so long that some of it is hidden. At this time, it’s recommended to wrap the extra-long text into multiple lines so you can see it all. This article will demonstrate how to programmatically wrap or unwrap text in Excel cells using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.6.3</version>
    </dependency>
</dependencies>

Wrap or Unwrap Text in Excel cells

Spire.XLS for Java supports wrapping or unwrapping text in Excel cells using the setWrapText() method provided by the IStyle interface. Below are detailed steps for your reference.

  • Create a Workbook instance.
  • Load a sample Excel document using Workbook.loadFromFile() method.
  • Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
  • Get a specific cell of the worksheet using Worksheet.getRange().get() method.
  • Get the style of the specified cell using XlsRange.getStyle() method and set whether the text is wrapped or not using setWrapText() method provided by IStyle interface.
  • Save the document to another file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class WrapOrUnwrapText {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load a sample Excel document
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Wrap text in the cell "D8"
        sheet.getRange().get("D8").getStyle().setWrapText(true);

        //Unwrap text in the cell "D6"
        sheet.getRange().get("D6").getStyle().setWrapText(false);

        //Save the document to another file
        workbook.saveToFile("output/WrapOrUnwrapText.xlsx", ExcelVersion.Version2013);
    }
}

Java: Wrap or Unwrap Text in Excel Cells

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Java: Copy Worksheets in Excel

2023-07-04 03:23:00 Written by Koohji

Copying worksheet involves duplicating an existing worksheet within the same workbook or across different workbooks. This valuable feature enables developers to create an exact replica of the original worksheet effortlessly, including its structure, formatting, data, formulas, charts, and other objects without any mistake. It proves especially beneficial when dealing with extensive data files, as it significantly reduces time and effort required for backing up files and creating templates. In this article, we will introduce how to copy worksheets in Excel using Spire.XLS for Java. With this method, all the cell formats in the original Excel worksheets will be completely remained.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.6.3</version>
    </dependency>
</dependencies>

Copy Worksheets between Workbooks

Spire.XLS for Java library allows you copy worksheets from one workbook to another file easily by using Worksheet.copyFrom() method. The following are detailed steps.

  • Create a new Workbook object.
  • Load the source Excel file from disk using Workbook.loadFromFile() method.
  • Get the first worksheet of the source file by using Workbook.getWorksheets().get() method.
  • Create an another Workbook object.
  • Load the target file from disk using Workbook.loadFromFile() method.
  • Add a new sheet to the target file using Workbook.getWorksheets().add() method.
  • Copy the first worksheet of the source file to the new added sheet of the target file through Worksheet.copyFrom() method.
  • Finally, specify the output path and save the target file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class copyWorksheet {
    public static void main(String[] args) {

        //Create a Workbook
        Workbook sourceWorkbook = new Workbook();

        //Load the source Excel file from disk
        sourceWorkbook.loadFromFile("sample1.xlsx");

        //Get the first worksheet
        Worksheet srcWorksheet = sourceWorkbook.getWorksheets().get(0);

        //Create a another Workbook
        Workbook targetWorkbook = new Workbook();

        //Load the target Excel file from disk
        targetWorkbook.loadFromFile("sample2.xlsx");

        //Add a new worksheet
        Worksheet targetWorksheet = targetWorkbook.getWorksheets().add("added");

        //Copy the first worksheet of sample1 to the new added sheet of sample2
        targetWorksheet.copyFrom(srcWorksheet);

        //String for output file
        String outputFile = "output/CopyWorksheet.xlsx";

        //Save the result file
        targetWorkbook.saveToFile(outputFile, ExcelVersion.Version2013);
        sourceWorkbook.dispose();
        targetWorkbook.dispose();
    }
}

Java: Copy Worksheets in Excel

Copy Worksheets within Workbooks

You can also copy a worksheet within the same workbook by adding a new worksheet to this workbook and then copying the desired sheet to the new one. The following are the steps to duplicate worksheets within an Excel workbook.

  • Create a new Workbook object.
  • Load the source Excel file from disk using Workbook.loadFromFile() method.
  • Get the first worksheet by using Workbook.getWorksheets().get() method and add a new sheet called "MySheet" using Workbook.getWorksheets().add() method.
  • Copy the first worksheet to the second one through Worksheet.copyFrom() method;
  • Finally, specify the output path and save the result file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;

public class copySheetWithinWorkbook {
    public static void main(String[] args) {
        //Create a Workbook
        Workbook workbook = new Workbook();

        //Load the sample file from disk
        workbook.loadFromFile("sample1.xlsx");

        //Get the first sheet and add a new worksheet to this file
        Worksheet sheet = workbook.getWorksheets().get(0);
        Worksheet sheet1 = workbook.getWorksheets().add("MySheet");

        //Copy the first worksheet to the second one
        sheet1.copyFrom(sheet);

        //String for output file
        String result = "output/CopySheetWithinWorkbook.xlsx";

        //Save to file
        workbook.saveToFile(result, ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Java: Copy Worksheets in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

This article shows you how to duplicate a worksheet inside a workbook using Spire.XLS for Java.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class DuplicateSheetWithinWorkbook {
    public static void main(String[] args) {

        //Create a Workbook object
        Workbook workbook = new Workbook();

        //Load the sample Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");

        //Get the first worksheet
        Worksheet originalSheet = workbook.getWorksheets().get(0);

        //Add a new worksheet
        Worksheet newSheet = workbook.getWorksheets().add(originalSheet.getName()+" - Copy");

        //Copy the worksheet to new sheet
        newSheet.copyFrom(originalSheet);

        //Save to file
        workbook.saveToFile("DuplicateSheet.xlsx");
    }
}

Duplicate Worksheets within a Workbook in Java

This article demonstrates how to create multi-level category chart in Excel using Spire.XLS for Java.

import com.spire.xls.*;
import com.spire.xls.charts.*;

public class CreateMultiLevelChart {
    public static void main(String []args) throws Exception {
        //create a workbook
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        //write data to cells
        sheet.getCellRange("A1").setText( "Main Category");
        sheet.getCellRange("A2").setText("Fruit");
        sheet.getCellRange("A6").setText("Vegies");
        sheet.getCellRange("B1").setText("Sub Category");
        sheet.getCellRange("B2").setText( "Bananas");
        sheet.getCellRange("B3").setText( "Oranges");
        sheet.getCellRange("B4").setText( "Pears");
        sheet.getCellRange("B5").setText("Grapes");
        sheet.getCellRange("B6").setText( "Carrots");
        sheet.getCellRange("B7").setText( "Potatoes");
        sheet.getCellRange("B8").setText( "Celery");
        sheet.getCellRange("B9").setText( "Onions");
        sheet.getCellRange("C1").setText("Value");
        sheet.getCellRange("C2").setValue("52");
        sheet.getCellRange("C3").setValue( "65");
        sheet.getCellRange("C4").setValue( "50");
        sheet.getCellRange("C5").setValue( "45");
        sheet.getCellRange("C6").setValue( "64");
        sheet.getCellRange("C7").setValue( "62");
        sheet.getCellRange("C8").setValue( "89");
        sheet.getCellRange("C9").setValue( "57");

        //vertically merge cells from A2 to A5, A6 to A9
        sheet.getCellRange("A2:A5").merge();
        sheet.getCellRange("A6:A9").merge();
        sheet.autoFitColumn(1);
        sheet.autoFitColumn(2);

        //add a clustered bar chart to worksheet
        Chart chart = sheet.getCharts().add(ExcelChartType.BarClustered);
        chart.setChartTitle( "Value");
        chart.getPlotArea().getFill().setFillType( ShapeFillType.NoFill);
        chart.getLegend().delete();
        chart.setLeftColumn(5);
        chart.setTopRow(1);
        chart.setRightColumn(14);

        //set the data source of series data
        chart.setDataRange(sheet.getCellRange("C2:C9"));
        chart.setSeriesDataFromRange(false);

        //set the data source of category labels
        ChartSerie serie = chart.getSeries().get(0);
        serie.setCategoryLabels( sheet.getCellRange("A2:B9"));

        //show multi-level category labels
        chart.getPrimaryCategoryAxis().setMultiLevelLable( true);

        //save the document
        workbook.saveToFile("output/createMultiLevelChart.xlsx", ExcelVersion.Version2013);
    }
}

Output:

Create Multi-Level Category Chart in Excel in Java

Java: Convert Excel to SVG

2022-07-05 09:19:00 Written by Koohji

SVG is an XML-based scalable vector graphic format and an open standard make up language for describing graphics. SVG is now very common in webpage making because it works well with other web standards, including CSS, DOM, and JavaScript. To add office documents like Excel worksheets on webpages to display them directly is a real challenge, but this can be achieved easily by converting them to SVG images. This article will demonstrate how to convert Excel documents to SVG files with the help of Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.6.3</version>
    </dependency>
</dependencies>

Convert a Specific Sheet of an Excel Document to an SVG File

The steps are as follows:

  • Create an object of Workbook class.
  • Load an Excel document from disk using Workbook.loadFromFile() method.
  • Get the second sheet using Workbook.getWorksheets().get() method.
  • Convert the sheet to an SVG file using Worksheet.toSVGStream() method.
  • Java
import com.spire.xls.*;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelToSVG {
    public static void main(String[] args) throws IOException {

        //Create an object of Workbook class
        Workbook workbook = new Workbook();

        //Load an Excel document from disk
        workbook.loadFromFile("C:/Samples/Sample.xlsx");

        //Get the second sheet
        Worksheet sheet = workbook.getWorksheets().get(1);

        //Convert the worksheet to an SVG file
        FileOutputStream stream = new FileOutputStream("heet.svg");
        sheet.toSVGStream(stream, sheet.getFirstRow(), sheet.getFirstColumn(), sheet.getLastRow(), sheet.getLastColumn());
        stream.flush();
        stream.close();

    }
}

Java: Convert Excel to SVG

Convert Every Sheet of an Excel Document to an SVG File

The steps are as follows:

  • Create an object of Workbook class.
  • Load an Excel document from disk using Workbook.loadFromFile() method.
  • Loop through the document to get its sheets and convert every sheet to an SVG file using Worksheet.toSVGStream() method.
  • Java
import com.spire.xls.*;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelToSVG {
    public static void main(String[] args) throws IOException {

        //Create an object of Workbook class
        Workbook workbook = new Workbook();

        //Load an Excel document from disk
        workbook.loadFromFile("C:/Samples/Sample.xlsx");

        //Loop through the document to get its worksheets
        for (int i = 0; i < workbook.getWorksheets().size(); i++)
        {
            FileOutputStream stream = new FileOutputStream("sheet"+i+".svg");

            //Convert a worksheet to an SVG file
            Worksheet sheet = workbook.getWorksheets().get(i);
            sheet.toSVGStream(stream, sheet.getFirstRow(), sheet.getFirstColumn(), sheet.getLastRow(), sheet.getLastColumn());
            stream.flush();
            stream.close();
        }
    }
}

Java: Convert Excel to SVG

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Add image comment to Excel in Java

2021-05-08 03:26:04 Written by Koohji

We have demonstrated how to add and read text comments in Excel in Java applications. This article will show you how to insert image comment to Excel with Spire.XLS for Java.

import com.spire.xls.*;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;

public class Test {
    public static void main(String[] args)throws IOException {
        //Load the sample Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //set the font
        ExcelFont font = workbook.createFont();
        font.setFontName("Arial");
        font.setSize(11);
        font.setKnownColor(ExcelColors.Orange);

        CellRange range = sheet.getCellRange("D1");
        //Add the commet
        ExcelComment comment = range.addComment();
        //Load the image
        BufferedImage bufferedImage = ImageIO.read(new File("Logo.jpg"));
        //Use the image to fill the comment
        comment.getFill().customPicture(bufferedImage, "Logo.jpg");

        //Set the height and width for the comment
        comment.setHeight(bufferedImage.getHeight());
        comment.setWidth(bufferedImage.getWidth());
        //Show the comment
        comment.setVisible(true);

        //Save the document to file
        workbook.saveToFile("output/setimageComment.xlsx", ExcelVersion.Version2013);

    }
}

Output:

Add image comment to Excel in Java

This article will show you how to replace the searched text with image in Excel worksheet by using Spire.XLS in Java applications.

Sample Excel:

Java replace the text with image in Excel worksheet

import com.spire.xls.*;
import java.io.IOException;

public class replaceTextwithImage {
    public static void main(String[] args) throws IOException {

        //Load the sample Excel document
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Find the text string {{Image}}
        CellRange[] ranges = worksheet.findAllString("{{Image}}", false, false);
        for (CellRange range : ranges) {
            //set the text as null
            range.setText("");

            //get the row and column of the searched range
            int row = range.getRow();
            int column = range.getColumn();
            //Add the image to the searched range
            worksheet.getPictures().add(row, column, "logo.jpg", ImageFormatType.Jpeg);

            //Save the document to file
            workbook.saveToFile("replaceTextwithImage.xlsx", ExcelVersion.Version2013);
            }
        }
    }

Output:

Java replace the text with image in Excel worksheet

Spire.XLS for Java supports to insert Word, Excel, PowerPoint slide and PDF as linked object or embedded object into Excel Worksheet. This article will show you how to insert a Word document as an embedded object into Excel by using Spire.XLS for Java in Java applications.

import com.spire.xls.*;
import com.spire.xls.core.IOleObject;
import com.spire.doc.*;
import com.spire.doc.documents.ImageType;
import java.awt.image.BufferedImage;

public class insertOLEObjects {
    public static void main(String[] args) {
        String docFile = "Sample.docx";
        String outputFile = "output/insertOLEObjects_result.xlsx";

        //Load the Excel document
        Workbook workbook = new Workbook();
        workbook.loadFromFile("Sample.xlsx");
        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Generate image
        BufferedImage image = GenerateImage(docFile);
        //insert OLE object
        IOleObject oleObject = worksheet.getOleObjects().add(docFile, image, OleLinkType.Embed);
        oleObject.setLocation(worksheet.getCellRange("B4"));
        oleObject.setObjectType(OleObjectType.ExcelWorksheet);
        //Save the file
        workbook.saveToFile(outputFile, ExcelVersion.Version2010);
    }

    private static BufferedImage GenerateImage(String fileName) {

        //Load the sample word document
        Document document = new Document();
        document.loadFromFile(fileName);

        //Save the first page of word as an image
        BufferedImage image = document.saveToImages(0, ImageType.Bitmap);
        return image;
    }
}

Output:

Insert OLE Object in Excel in Java applications

Java: Insert or Remove Shapes in Excel

2024-11-07 07:26:00 Written by Koohji

Shapes in Excel are versatile graphical elements that enhance the visual representation of data within your spreadsheets. They include a variety of forms such as rectangles, circles, arrows, lines, and callouts, allowing users to create diagrams, flowcharts, and emphasis on specific data points.

Using shapes can help clarify complex information, guide the reader’s attention, and make presentations more engaging. Shapes can be customized in terms of size, color, and effects, providing flexibility in design.

In this article, you will learn how to insert, format and remove shapes in an Excel worksheet using Spire.XLS for Java.

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.6.3</version>
    </dependency>
</dependencies>

Insert Various Types of Shapes to Excel

To add a shape to a worksheet, use the PrstGeomShapeCollection.addPrstGeomShape(int row, int column, int width, int height, com.spire.xls.PrstGeomShapeType shapeType) method. The first four parameters specify the shape's position and size, while the fifth parameter indicates the type of shape.

The steps to insert a shape of a certain type to a worksheet are as follows:

  • Create a Workbook object.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Add a shape to the worksheet using Worksheet.getPrstGeomShapes().addPrstGeomShape() method, specifying the location, size and type of the shape.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.core.IPrstGeomShape;

import java.io.IOException;

public class InsertShapes {

    public static void main(String[] args) throws IOException {

        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Add a rectangle
        IPrstGeomShape rectangle = sheet.getPrstGeomShapes().addPrstGeomShape(2, 2, 260, 40, PrstGeomShapeType.Rect);
        
        // Set text for the shape
        rectangle.setText("Add various type of shapes to Excel");
        rectangle.setTextVerticalAlignment(ExcelVerticalAlignment.MiddleCentered);

        // Add a triangle, a pie, a curved right arrow, a heart, a smile face, and an octagon to the worksheet
        sheet.getPrstGeomShapes().addPrstGeomShape(7, 2, 100, 100, PrstGeomShapeType.Triangle);
        sheet.getPrstGeomShapes().addPrstGeomShape(7, 6,100,100,PrstGeomShapeType.Pie);
        sheet.getPrstGeomShapes().addPrstGeomShape(7, 10, 100, 100, PrstGeomShapeType.CurvedRightArrow);

        sheet.getPrstGeomShapes().addPrstGeomShape(17, 2, 100, 100, PrstGeomShapeType.Heart);
        sheet.getPrstGeomShapes().addPrstGeomShape(17, 6, 100, 100, PrstGeomShapeType.SmileyFace);
        sheet.getPrstGeomShapes().addPrstGeomShape(17, 10, 100, 100, PrstGeomShapeType.Octagon);

        // Save the workbook to an Excel file
        workbook.saveToFile("output/InsertShapes.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

Java: Insert or Remove Shapes in Excel

Apply Formatting to Shapes in Excel

The example above demonstrates how to add various shapes with default formatting to a worksheet. To customize a shape's appearance, you can utilize the IShapeLineFormat, IShapeFill, and IShadow interfaces provided by Spire.XLS.

The steps to apply formatting to a shape in Excel are as follows:

  • Create a Workbook object.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Add a shape to the worksheet using Worksheet.getPrstGeomShapes().addPrstGeomShape() method, specifying the location, size and type of the shape.
  • Get the IShapeLineFormat object using IShape.getLine() method.
  • Set the line style, color, width and visibility using the methods under the IShapeLineFormat object.
  • Get the IShapeFill object using IShape.getFill() method.
  • Set the fill type, fill color, fill image, or fill pattern using the methods under the IShapeFill object.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.*;
import com.spire.xls.core.IPrstGeomShape;

import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;

public class ApplyFormattingToShapes {

    public static void main(String[] args) throws IOException {

        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Add the first rectangle to the worksheet
        IPrstGeomShape rectangle_one = sheet.getPrstGeomShapes().addPrstGeomShape(4, 2, 220, 120, PrstGeomShapeType.Rect);

        // Set the line style, width, and color
        rectangle_one.getLine().setDashStyle(ShapeDashLineStyleType.Dashed);
        rectangle_one.getLine().setWeight(1.0);
        rectangle_one.getLine().setForeColor(Color.RED);

        // Set the fill type and fore color
        rectangle_one.getFill().setFillType(ShapeFillType.SolidColor);
        rectangle_one.getFill().setForeColor(Color.lightGray);

        // Add the second rectangle and format the shape
        IPrstGeomShape rectangle_two = sheet.getPrstGeomShapes().addPrstGeomShape(4, 6, 220, 120, PrstGeomShapeType.Rect);
        rectangle_two.getLine().setVisible(false);
        rectangle_two.getFill().setFillType(ShapeFillType.Gradient);
        rectangle_two.getFill().setForeColor(Color.lightGray);
        rectangle_two.getFill().setGradientStyle(GradientStyleType.Vertical);

        // Add the third rectangle and format the shape
        IPrstGeomShape rectangle_three = sheet.getPrstGeomShapes().addPrstGeomShape(4, 10, 220, 120, PrstGeomShapeType.Rect);
        rectangle_three.getLine().setWeight(1.0);
        rectangle_three.getFill().setFillType(ShapeFillType.Pattern);
        rectangle_three.getFill().setPattern(GradientPatternType.Pat80Percent);
        rectangle_three.getFill().setForeColor(Color.white);
        rectangle_three.getFill().setBackColor(Color.magenta);

        // Add the fourth rectangle and format the shape
        IPrstGeomShape rectangle_four = sheet.getPrstGeomShapes().addPrstGeomShape(15, 2, 220, 120, PrstGeomShapeType.Rect);
        rectangle_four.getLine().setWeight(1.0);
        BufferedImage image = ImageIO.read(new File("C:\\Users\\Administrator\\Desktop\\cartoon.jpeg"));
        rectangle_four.getFill().customPicture(image,"myPicture");

        // Add the fifth rectangle and format the shape
        IPrstGeomShape rectangle_five = sheet.getPrstGeomShapes().addPrstGeomShape(15, 6, 220, 120, PrstGeomShapeType.Rect);
        rectangle_five.getLine().setWeight(1.0);
        rectangle_five.getFill().setFillType(ShapeFillType.NoFill);

        // Add the sixth rectangle and format the shape
        IPrstGeomShape rectangle_six = sheet.getPrstGeomShapes().addPrstGeomShape(15, 10, 220, 120, PrstGeomShapeType.Rect);
        rectangle_six.getLine().setWeight(1.0);
        rectangle_six.getFill().setFillType(ShapeFillType.Texture);
        rectangle_six.getFill().setTexture(GradientTextureType.Canvas);
        
        // Save the workbook to an Excel file
        workbook.saveToFile("output/FormatShapes.xlsx", ExcelVersion.Version2016);

        // Dispose resources
        workbook.dispose();
    }
}

Java: Insert or Remove Shapes in Excel

Remove Shapes from Excel

The shapes in a worksheet can be retrieved by utilizing the Worksheet.getPrstGeomShapes() method. To remove a specific shape, call the PrstGeomShapeCollection.get(index).remove() method. If you want to remove all shapes, you can use a for loop to iterate through and delete them.

The steps to remove shapes in an Excel worksheet are as follows:

  • Create a Workbook object.
  • Load an Excel file using Workbook.loadFromFile() method.
  • Get a specific worksheet using Workbook.getWorksheets().get() method.
  • Get the shape collection using Worksheet.getPrstGeomShapes() method.
  • Remove a specific shape using PrstGeomShapeCollection.get(index).remove() method.
  • Save the workbook to an Excel file using Workbook.saveToFile() method.
  • Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.spreadsheet.collections.PrstGeomShapeCollection;

public class RemoveShapesFromExcel {

    public static void main(String[] args) {

        // Create a Workbook object
        Workbook workbook = new Workbook();

        // Load an Excel file
        workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\Shapes.xlsx");

        // Get a specific worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Get the shape collection from the worksheet
        PrstGeomShapeCollection shapes = sheet.getPrstGeomShapes();

        // Remove a specific shape
        shapes.get(1).remove();

         /*
         // Remove all shapes
         for (int i = sheet.getPrstGeomShapes().getCount()-1; i >= 0; i--)
         {
             sheet.getPrstGeomShapes().get(i).remove();
         }
         */

        // Save the workbook to an Excel file
        workbook.saveToFile("output/RemoveSpecificShape.xlsx", ExcelVersion.Version2013);

        // Dispose resources
        workbook.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Page 4 of 10
page 4