Program Guide (127)
Children categories
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:
The output Excel:
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);
}
}
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.
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();
}
}
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();
}
}
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");
}
}
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:
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.
- Convert a Specific Sheet of an Excel Document to an SVG File
- Convert Every Sheet of an Excel Document to an SVG File
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();
}
}
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();
}
}
}
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.
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：
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:
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：
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:
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.
- Insert Various Types of Shapes to Excel
- Apply Formatting to Shapes in Excel
- Remove Shapes from Excel
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();
}
}
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();
}
}
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.
