Program Guide (129)
Children categories
An Excel document with Track Changes turned on will let you know what changes have been made to the document since the author has saved it. If you have the full authority over the document, you can accept or reject each revision. This article covers how to accept or reject all tracked changes at once 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.12.15</version>
</dependency>
</dependencies>
Accept Tracked Changes in a Workbook
To determine whether a workbook has tracked changes, use Workbook.hasTrackedChanegs() method. If yes, you can accept all changes at once using Workbook.acceptAllTrackedchanges() method. The following are the steps to accept tracked changes in an Excel workbook.
- Create a Workbook object.
- Load the sample Excel document using Workbook.loadFromFile() method.
- Determine if the workbook has tracked changes by Workbook.hasTrackedChanegs() method.
- Accept tracked changes using Workbook.acceptAllTrackedChanges() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
public class AcceptTrackedChanges {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load the sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");
//Determine if the workbook has tracked changes
if (wb.hasTrackedChanges())
{
//Accept tracked changes in the workbook
wb.acceptAllTrackedChanges();
}
//Save to file
wb.saveToFile("output/AcceptChanges.xlsx", FileFormat.Version2013);
}
}
Reject Tracked Changes in a Workbook
If the tracked changes have been proven to exist in a workbook, you can reject them using Workbook.rejectAllTrackedChanges() method. The following are the steps to achieve this.
- Create a Workbook object.
- Load the sample Excel document using Workbook.loadFromFile() method.
- Determine if the workbook has tracked changes by Workbook.hasTrackedChanegs() method.
- Reject all tracked changes using Workbook.rejectAllTrackedChanges() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
public class RejectTrackedChanges {
public static void main(String[] args) {
//Create a Workbook object
Workbook wb = new Workbook();
//Load the sample Excel file
wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\Employees.xlsx");
//Determine if the workbook has tracked changes
if (wb.hasTrackedChanges())
{
//Reject tracked changes in the workbook
wb.rejectAllTrackedChanges();
}
//Save to file
wb.saveToFile("output/RejectChanges.xlsx", FileFormat.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.
Spire.XLS for Java provides the getStyle() method and setStyle() method under the IXLSRange interface to get or set the style of a specific cell range. To copy formatting from one cell to another, get the style first and then apply it to another cell.
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class CopyCellFormatting {
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 sheet = workbook.getWorksheets().get(0);
//Get the number of rows used
int rowCount = sheet.getRows().length;
//Loop through the rows
for (int i = 1; i < rowCount + 1; i++)
{
//Copy the formatting from a certain cell to another
sheet.getRange().get(String.format("C%d",i)).setStyle(sheet.getRange().get(String.format("A%d",i)).getStyle());
}
//Save the result to file
workbook.saveToFile("output/CopyFormatting.xlsx", ExcelVersion.Version2016);
}
}
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.12.15</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.12.15</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.12.15</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：
