News Category

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:

Detect Merged Cells in an Excel Worksheet in Java

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:

Detect Merged Cells in an Excel Worksheet in Java

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();
    }
}

Java: Modify or Delete Hyperlinks in Excel

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();
    }
}

Java: Modify or Delete Hyperlinks 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.

Delete Images in Excel in Java

2020-08-21 03:18:13 Written by support iceblue

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);
    }
}

This 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:

Java insert text and image watermark to Excel worksheet

Java: Convert Excel to TIFF

2022-09-19 07:16:00 Written by support iceblue

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");

    }
}

Java: Convert Excel to 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);
    }
}

Java: Convert Excel to TIFF

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.

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);
    }
}

Java: Add or Delete Page Breaks in Excel

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);
    }
}

Appy Multiple Fonts in One Cell in Java

Get Worksheet Names in Java

2020-06-12 08:44:27 Written by support iceblue

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);
        }
    }
}

Get Worksheet Names in Java

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:

Extract OLE Objects from an Excel Document in Java

This 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.");
        }
    }
}

Detect if an Excel Document is Password Protected in Java

Page 5 of 9