Program Guide (123)
Children categories
This article demonstrates how to detect merged cells in an Excel worksheet and unmerge the merged cells using Spire.XLS for Java.
The input Excel file:
import com.spire.xls.CellRange; import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class DetectMergedCells { public static void main(String[] args) throws Exception { //Create a Workbook instance Workbook workbook = new Workbook(); //Load the Excel file workbook.loadFromFile( "Input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Get the merged cell ranges in the first worksheet and put them into a CellRange array CellRange[] range = sheet.getMergedCells(); //Traverse through the array and unmerge the merged cells for(CellRange cell : range){ cell.unMerge(); } //Save the result file workbook.saveToFile("DetectMergedCells.xlsx", ExcelVersion.Version2013); } }
The output Excel file:
Hyperlinks are useful features in Excel documents, providing quick access to other relevant resources such as websites, email addresses, or specific cells within the same workbook. However, sometimes you may want to modify or delete existing hyperlinks for various reasons, such as updating broken links, correcting typos, or removing outdated information. In this article, we will demonstrate how to modify or delete hyperlinks in Excel in Java using Spire.XLS for Java library.
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>14.3.2</version> </dependency> </dependencies>
Modify Hyperlinks in Excel in Java
If there are issues with the functionality of a hyperlink caused by damage or spelling errors, you may need to modify it. The following steps demonstrate how to modify an existing hyperlink in an Excel file:
- Create an instance of Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Get a specific worksheet using the Workbook.getWorksheets().get() method.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
- Change the values of TextToDisplay and Address property using the HyperLinksCollection.get().setTextToDisplay() and HyperLinksCollection.get().setAddress method.
- Save the result file using the Workbook.saveToFile() method.
- Java
import com.spire.xls.*; import com.spire.xls.collections.HyperLinksCollection; public class ModifyHyperlink { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("Sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Get the collection of all hyperlinks in the worksheet HyperLinksCollection links = sheet.getHyperLinks(); //Change the values of TextToDisplay and Address property links.get(0).setTextToDisplay("Republic of Indonesia"); links.get(0).setAddress("https://www.indonesia.travel/gb/en/home"); //Save the document workbook.saveToFile("ModifyHyperlink.xlsx", ExcelVersion.Version2013); workbook.dispose(); } }
Delete Hyperlinks from Excel in Java
Spire.XLS for Java also offers the Worksheet.getHyperLinks().removeAt() method to remove hyperlinks. The following are the steps to delete hyperlink from Excel in Java.
- Create an instance of Workbook class.
- Load an Excel file using the Workbook.loadFromFile() method.
- Get a specific worksheet using the Workbook.getWorksheets().get() method.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.getHyperLinks() method.
- Remove a specific hyperlink and keep link text using the Worksheet.getHyperLinks().removeAt() method.
- Save the result file using the Workbook.saveToFile() method.
- Java
import com.spire.xls.*; import com.spire.xls.collections.HyperLinksCollection; public class RemoveHyperlink { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("Sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Get the collection of all hyperlinks in the worksheet HyperLinksCollection links = sheet.getHyperLinks(); //Remove the first hyperlink and keep link text sheet.getHyperLinks().removeAt(0); //Remove all content from the cell //sheet.getCellRange("A7").clearAll(); //Save the document String output = "RemoveHyperlink.xlsx"; workbook.saveToFile(output, 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 demonstrates how to remove a specific image or all images from an Excel worksheet using Spire.XLS for Java.
Delete specific image
import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class DeleteSpecificImage { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("Input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Delete a specific image by its index sheet.getPictures().get(1).remove(); //Save the document workbook.saveToFile("DeleteSpecificImage.xlsx", ExcelVersion.Version2013); }
Delete all images
import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class DeleteAllImages { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("Input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Loop through the images inside the worksheet for (int i = sheet.getPictures().getCount() - 1; i >= 0; i--) { //Delete an image by its index sheet.getPictures().get(i).remove(); } //Save the document workbook.saveToFile("DeleteAllImages.xlsx", ExcelVersion.Version2013); } }
Java insert text and image watermark to Excel worksheet
2020-08-03 08:55:32 Written by support iceblueThis article will demonstrate how to insert text and image watermark to Excel worksheet in Java applications. The watermark in Excel worksheet could only be viewed under layout view mode.
import com.spire.xls.*; import java.awt.*; import java.awt.image.BufferedImage; import static java.awt.image.BufferedImage.TYPE_INT_ARGB; public class ExcelWatermark { public static void main(String[] args) { //Initialize a new instance of workbook and load the test file Workbook workbook = new Workbook(); workbook.loadFromFile("Sample.xlsx"); //Set the text string and size Font font = new Font("Arial", Font.PLAIN, 40); String watermark = "Draft Version"; for (Worksheet sheet : (Iterable) workbook.getWorksheets()) { //Call DrawText() method to insert the image BufferedImage imgWtrmrk = drawText(watermark, font, Color.pink, Color.white, sheet.getPageSetup().getPageHeight(), sheet.getPageSetup().getPageWidth()); //Set the image as header sheet.getPageSetup().setLeftHeaderImage(imgWtrmrk); sheet.getPageSetup().setLeftHeader("&G"); //Set the viewmode as Layout sheet.setViewMode(ViewMode.Layout); } //Save the document workbook.saveToFile("Watermark.xlsx", ExcelVersion.Version2010); } private static BufferedImage drawText (String text, Font font, Color textColor, Color backColor,double height, double width) { //define the width and height of image BufferedImage img = new BufferedImage((int) width, (int) height, TYPE_INT_ARGB); Graphics2D loGraphic = img.createGraphics(); //set the font size FontMetrics loFontMetrics = loGraphic.getFontMetrics(font); int liStrWidth = loFontMetrics.stringWidth(text); int liStrHeight = loFontMetrics.getHeight(); //set the text format loGraphic.setColor(backColor); loGraphic.fillRect(0, 0, (int) width, (int) height); loGraphic.translate(((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2); loGraphic.rotate(Math.toRadians(-45)); loGraphic.translate(-((int) width - liStrWidth) / 2, -((int) height - liStrHeight) / 2); loGraphic.setFont(font); loGraphic.setColor(textColor); loGraphic.drawString(text, ((int) width - liStrWidth) / 2, ((int) height - liStrHeight) / 2); loGraphic.dispose(); return img; } }
Effective screenshot of Excel text watermark:
TIFF (Tagged Image File Format) is a standard image format created in the mid-1980s for saving high-quality color images on different computer platforms. Since the TIFF format is widely used in faxing, scanning, and other document exchange processing, conversion from Excel to TIFF may be frequently required in your daily work. This article will demonstrate how to programmatically convert Excel to TIFF 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>14.3.2</version> </dependency> </dependencies>
Convert a Whole Excel Worksheet to TIFF
The detailed steps are as follows:
- Create a Workbook instance.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get a specified worksheet using Workbook.getWorksheets().get() method.
- Convert the specified worksheet to TIFF using Worksheet.saveToTiff(java.lang.String fileName) method.
- Java
import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class ConvertExcelToTIFF { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load a sample Excel file workbook.loadFromFile("input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Save the first worksheet to TIFF sheet.saveToTiff("SheetToTiff.tiff"); } }
Convert a Specific Cell Range to TIFF
The detailed steps are as follows:
- Create a Workbook instance.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get a specified worksheet using Workbook.getWorksheets().get() method.
- Convert a specific cell range in the worksheet to TIFF using Worksheet.saveToTiff(java.lang.String fileName, int firstRow, int firstColumn, int lastRow, int lastColumn) method.
- Java
import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class CellRangeToTIFF { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load a sample Excel file workbook.loadFromFile("input.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Save a specific cell range in the first worksheet to TIFF sheet.saveToTiff("CellRangeToTiff.tiff",1,1,2,7); } }
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 breaks in Excel are dividers that separate a large worksheet into individual pages for printing. In this article, you will learn how to add or delete page breaks in Excel in Java using Spire.XLS for Java library.
- Add Page Breaks to Excel in Java
- Delete a Specific Page Break from Excel in Java
- Delete All Page Breaks from Excel in 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>14.3.2</version> </dependency> </dependencies>
Add Page Breaks to Excel in Java
Using Spire.XLS for Java, you can add horizontal and vertical page breaks to an Excel worksheet. Below are the steps to do so:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Specify the cells where you want to add page breaks to using Worksheet.getRange().get() method.
- Add horizontal and vertical page breaks to the cells using Worksheet.getHPageBreaks().add() and Worksheet.getVPageBreaks().add() methods.
- Set the sheet view mode to ViewMode.Preview using Worksheet.setViewMode() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*; public class AddPageBreaks { public static void main(String []args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("Sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Specify the cells where you want to add page breaks to CellRange cell1 = sheet.getRange().get("A10"); CellRange cell2 = sheet.getRange().get("F1"); //Add a horizontal page break sheet.getHPageBreaks().add(cell1); //Add a vertical page break sheet.getVPageBreaks().add(cell2); //Set view mode to Preview in order to view the page breaks sheet.setViewMode(ViewMode.Preview); //Save the result file workbook.saveToFile("AddPageBreaks.xlsx", ExcelVersion.Version2013); } }
Delete a Specific Page Break from Excel in Java
The following are the steps to delete a specific page break from an Excel worksheet:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Delete a specific horizontal or vertical page break from the worksheet by its index using Worksheet.getHPageBreaks().removeAt() or Worksheet.getVPageBreaks().removeAt() method.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class DeleteASpecificPageBreak { public static void main(String []args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("AddPageBreaks.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Delete the first horizontal page break sheet.getHPageBreaks().removeAt(0); //Delete the first vertical page break sheet.getVPageBreaks().removeAt(0); //Save the result file workbook.saveToFile("DeleteASpecificPageBreaks.xlsx", ExcelVersion.Version2013); } }
Delete All Page Breaks from Excel in Java
The following are the steps to delete all the page breaks from an Excel worksheet:
- Create an instance of Workbook class.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index using Workbook.getWorksheets().get() method.
- Delete all the horizontal and vertical page breaks from the worksheet using Worksheet.getHPageBreaks().clear() and Worksheet.getVPageBreaks().clear() methods.
- Save the result file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class DeleteAllPageBreaks { public static void main(String []args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("AddPageBreaks.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Delete all horizontal page breaks sheet.getHPageBreaks().clear(); //Delete all vertical page breaks sheet.getVPageBreaks().clear(); //Save the result file workbook.saveToFile("DeleteAllPageBreaks.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.
This article demonstrates how to apply multiple font styles in a single Excel cell using Spire.XLS for Java.
import com.spire.xls.*; import java.awt.*; public class ApplyMultiFontsInCell { public static void main(String[] args) { //Create a Workbook instance Workbook wb = new Workbook(); //Get the first worksheet Worksheet sheet = wb.getWorksheets().get(0); //Create one Excel font ExcelFont font1 = wb.createFont(); font1.setFontName("Calibri"); font1.setColor(Color.blue); font1.setSize(12f); font1.isBold(true); //Create another Excel font ExcelFont font2 = wb.createFont(); font2.setFontName("Times New Roman"); font2.setColor(Color.red); font2.setSize(14f); font2.isBold(true); font2.isItalic(true); //Insert text to cell B5 RichText richText = sheet.getCellRange("B5").getRichText(); richText.setText("This document was created with Spire.XLS for Java."); //Apply two fonts to the text in the cell B5 richText.setFont(0, 30, font1); richText.setFont(31, 50, font2); //Save the document wb.saveToFile("MultiFonts.xlsx", ExcelVersion.Version2016); } }
This article demonstrates how to get names of worksheets in a workbook using Spire.XLS for Java.
import com.spire.xls.Workbook; import com.spire.xls.Worksheet; public class GetWorksheetName { public static void main(String[] args) { //Create a Workbook object Workbook wb = new Workbook(); //Load a sample Excel file wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx"); //Loop through the worksheets for (Object sheet: wb.getWorksheets() ) { //Get worksheet name String sheetName = ((Worksheet) sheet).getName(); System.out.println(sheetName); } } }
This article demonstrates how to extract OLE objects from an Excel document using Spire.XLS for Java.
import com.spire.xls.*; import com.spire.xls.core.IOleObject; import java.io.*; public class ExtractOLEObjects { public static void main(String[] args){ //Create a Workbook instance Workbook workbook = new Workbook(); //Load the Excel document workbook.loadFromFile("OLEObjectsExample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Extract ole objects if (sheet.hasOleObjects()) { for (int i = 0; i < sheet.getOleObjects().size(); i++) { IOleObject object = sheet.getOleObjects().get(i); OleObjectType type = sheet.getOleObjects().get(i).getObjectType(); switch (type) { //Word document case WordDocument: byteArrayToFile(object.getOleData(), "output/extractOLE.docx"); break; //PowerPoint document case PowerPointSlide: byteArrayToFile(object.getOleData(), "output/extractOLE.pptx"); break; //PDF document case AdobeAcrobatDocument: byteArrayToFile(object.getOleData(), "output/extractOLE.pdf"); break; //Excel document case ExcelWorksheet: byteArrayToFile(object.getOleData(), "output/extractOLE.xlsx"); break; } } } } public static void byteArrayToFile(byte[] datas, String destPath) { File dest = new File(destPath); try (InputStream is = new ByteArrayInputStream(datas); OutputStream os = new BufferedOutputStream(new FileOutputStream(dest, false));) { byte[] flush = new byte[1024]; int len = -1; while ((len = is.read(flush)) != -1) { os.write(flush, 0, len); } os.flush(); } catch (IOException e) { e.printStackTrace(); } } }
The following screenshot shows the extracted OLE documents:
Detect if an Excel Document is Password Protected in Java
2020-06-02 08:23:45 Written by support iceblueThis article demonstrates how to detect whether an Excel document is password protected or not using Spire.XLS for Java.
import com.spire.xls.Workbook; public class DetectProtectedOrNot { public static void main(String[] args) { //Get the file path String filePath= "C:\\Users\\Administrator\\Desktop\\sample.xlsx"; //Detect whether the workbook is password protected or not Boolean isProtected = Workbook.bookIsPasswordProtected(filePath); //Print results if (isProtected) { System.out.print("The document is password protected."); } else { System.out.print("The document is not protected."); } } }