DEV Community: Leon Davis

Java - Extract Images from PDF and Compress PDF Image Size

Leon Davis — Thu, 18 Jun 2026 12:22:31 +0000

In some document processing scenarios, PDF files often contain many images, such as scanned documents, product manuals, report attachments, contract images, and so on. Sometimes we need to extract images from a PDF for archiving, recognition, or further editing. In other cases, a PDF file may become too large because it contains high-resolution images, and image compression becomes necessary.

This article uses demonstrate two common operations:

Extract images from a PDF document in Java
Compress high-quality images in a PDF document in Java

1. Preparation

The sample code uses a Maven project. First, add the Spire.PDF for Java dependency to your 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.pdf</artifactId>
        <version>12.6.1</version>
    </dependency>
</dependencies>

If you are using a regular Java project, you can also manually add the corresponding Jar file to your project dependencies. For demonstration purposes, the following code uses local file paths. You need to modify them according to your actual directory structure.

2. Extract Images from a PDF in Java

1. Implementation Logic

The core process of extracting images is straightforward:

Load the PDF file;
Traverse each page of the PDF;
Get the image information on the current page;
Save the image object as a local image file.

In Spire.PDF, you can use PdfImageHelper to get image information from a PDF page, then use PdfImageInfo.getImage() to obtain a BufferedImage object, and finally use ImageIO.write() to write it to a local file.

2. Sample Code

import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.utilities.PdfImageHelper;
import com.spire.pdf.utilities.PdfImageInfo;

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

public class ExtractPdfImages {

    public static void main(String[] args) throws IOException {
        String inputPdf = "C:/pdf/input.pdf";
        String outputDir = "C:/pdf/images/";

        PdfDocument document = new PdfDocument();

        try {
            // Load the PDF file
            document.loadFromFile(inputPdf);

            // Create the output directory
            File dir = new File(outputDir);
            if (!dir.exists()) {
                dir.mkdirs();
            }

            PdfImageHelper imageHelper = new PdfImageHelper();
            int imageIndex = 1;

            // Traverse each page of the PDF
            for (int pageIndex = 0; pageIndex < document.getPages().getCount(); pageIndex++) {
                PdfPageBase page = document.getPages().get(pageIndex);

                // Get all images from the current page
                PdfImageInfo[] imageInfos = imageHelper.getImagesInfo(page);

                // Save images
                for (PdfImageInfo imageInfo : imageInfos) {
                    BufferedImage image = imageInfo.getImage();

                    String fileName = String.format(
                            "page-%d-image-%d.png",
                            pageIndex + 1,
                            imageIndex++
                    );

                    File outputFile = new File(outputDir + fileName);
                    ImageIO.write(image, "PNG", outputFile);
                }
            }

            System.out.println("Image extraction completed. Total images exported: " + (imageIndex - 1));

        } finally {
            document.dispose();
        }
    }
}

3. Code Explanation

Instead of using a simple name like image-1.png, the code includes the page number in the file name, for example:

page-1-image-1.png
page-2-image-3.png

This makes it easier to locate the source page of each extracted image later. Especially when a PDF has many pages, using only sequential numbers makes it harder to identify where each image originally came from.

In this example, the extracted images are saved in PNG format. If the images in the original PDF are already large, saving them as PNG may not reduce the file size. If the images are only used for preview or web display, you can save them as JPG instead.

For example:

ImageIO.write(image, "JPG", outputFile);

However, JPG is a lossy format and is more suitable for photos. It is not ideal for text screenshots, table screenshots, or images with transparent backgrounds.

3. Compress Images in a PDF in Java

1. Applicable Scenarios

When a PDF file is too large, images are often one of the main reasons. For example:

The resolution of scanned documents is too high;
A report contains many high-definition images;
Images are inserted directly into the PDF without compression;
The PDF has only a few pages, but the file size reaches dozens of MB.

In such cases, you can try compressing the images in the PDF to reduce the overall file size.

However, image compression may reduce image quality to some extent. If the PDF is used for printing, archiving, or formal delivery, it is recommended to back up the original file first and compress a copy instead.

2. Sample Code

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.utilities.PdfImageHelper;
import com.spire.pdf.utilities.PdfImageInfo;

import java.io.File;

public class CompressPdfImages {

    public static void main(String[] args) {
        String inputPdf = "C:/pdf/input.pdf";
        String outputPdf = "C:/pdf/output-compressed.pdf";

        PdfDocument document = new PdfDocument();

        try {
            // Load the PDF file
            document.loadFromFile(inputPdf);

            // Disable incremental update to avoid abnormal file size increase after saving
            document.getFileInfo().setIncrementalUpdate(false);

            PdfImageHelper imageHelper = new PdfImageHelper();

            // Traverse each page and compress images on the page
            for (int pageIndex = 0; pageIndex < document.getPages().getCount(); pageIndex++) {
                PdfPageBase page = document.getPages().get(pageIndex);

                PdfImageInfo[] imageInfos = imageHelper.getImagesInfo(page);

                for (PdfImageInfo imageInfo : imageInfos) {
                    imageInfo.tryCompressImage();
                }
            }

            // Save the compressed PDF
            document.saveToFile(outputPdf, FileFormat.PDF);

            printFileSize(inputPdf, outputPdf);

        } finally {
            document.dispose();
        }
    }

    private static void printFileSize(String beforePath, String afterPath) {
        File before = new File(beforePath);
        File after = new File(afterPath);

        double beforeMb = before.length() / 1024.0 / 1024.0;
        double afterMb = after.length() / 1024.0 / 1024.0;

        System.out.printf("Before compression: %.2f MB%n", beforeMb);
        System.out.printf("After compression: %.2f MB%n", afterMb);
    }
}

4. Explanation of `setIncrementalUpdate(false)`

When compressing a PDF, there is one line in the code:

document.getFileInfo().setIncrementalUpdate(false);

It is recommended to keep this step.

When a PDF is saved, it may use incremental update mode. This means that the modified content is appended to the end of the original file instead of rewriting the entire file. This approach is suitable for some editing scenarios, but when compressing images, it may cause old data to remain in the file. As a result, you may find that the images have been compressed, but the PDF file size has not decreased significantly.

Therefore, when compressing images in a PDF, disabling incremental update can make the saved result better match the expected compressed output.

5. Frequently Asked Questions

1. Why can’t some images be extracted from a PDF?

Not everything that looks like an image is necessarily an image object in a PDF.

For example:

The page content may be vector graphics;
Text and graphics may be generated by drawing instructions;
A scanned document may use one large image for the entire page;
Images in some PDFs may be specially encoded or packaged.

Therefore, in real projects, you should test with PDFs from different sources instead of judging compatibility based on only one sample file.

2. What if the PDF file size does not change much after compression?

There may be several reasons:

The main file size does not come from images, but from fonts, attachments, or other resources;
The images have already been compressed;
There are not many images in the PDF;
The original PDF has a special saving structure;
The compression ratio is limited, so the size change is not obvious.

It is recommended to compare the file size before and after compression and test with real business documents as much as possible.

3. Will compression affect image clarity?

It may.

The goal of image compression is to reduce file size, which usually affects image quality to some degree. If the PDF needs to be printed, stamped, or archived, it is better not to overwrite the original file. Instead, output a new compressed version.

6. Conclusion

This article demonstrates two common PDF image processing requirements in Java:

Use PdfImageHelper.getImagesInfo() to get image information from PDF pages;
Use PdfImageInfo.getImage() to extract images;
Use PdfImageInfo.tryCompressImage() to compress images in a PDF;
Disable incremental update when saving the PDF to avoid ineffective compression.

In real business scenarios, it is recommended to encapsulate “image extraction” and “PDF compression” as separate utility methods, and add file size checks, exception handling, directory validation, and other logic. This makes the code easier to reuse for document archiving, attachment processing, or batch PDF compression.

Print Word Document in Java

Leon Davis — Thu, 18 Jun 2026 11:49:54 +0000

In enterprise applications, Word document printing is usually not a core feature at first glance, but it often becomes essential once workflows move into production.

Common scenarios include:

Automated generation and printing of reports
Batch printing of contracts, invoices, or statements
Backend services generating documents without user interaction
Integration with enterprise systems such as ERP or OA platforms

Unlike plain text printing, Word document printing involves rendering layout, maintaining formatting consistency, and interacting with system-level printers. These details are where most implementation challenges appear in real projects.

This guide walks through practical ways to print Word documents in Java, from basic usage to more controlled scenarios like page setup, duplex printing, and batch processing.

Environment setup

For handling Word documents, Spire.Doc is used here as the document processing layer. It takes care of loading and rendering Word files before they are sent to the printer.

Maven dependency:

<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.doc</artifactId>
        <version>14.6.0</version>
    </dependency>
</dependencies>

1. Simple Way to Print Word Document in Java

The most direct approach is to load the document and send it to the printer.

Document document = new Document();
document.loadFromFile("data/report.docx");

document.getPrintDocument().print();

document.dispose();

This approach relies entirely on default system behavior.

In practice, it:

Uses the default system printer
Keeps original document formatting
Requires no layout or printer configuration

It works well when printing requirements are minimal and consistency across printers is not a concern.

2. Java Word Printing with PrinterJob for Layout Control

Once printing requirements become more strict, default behavior is usually not enough.

Typical needs include controlling margins, adjusting layout behavior, or setting multiple copies.

Java’s PrinterJob API provides that level of control.

Document document = new Document();
document.loadFromFile("data/report.docx");

PrinterJob printerJob = PrinterJob.getPrinterJob();

PageFormat pageFormat = printerJob.defaultPage();
Paper paper = pageFormat.getPaper();

// Override default margins to use full page
paper.setImageableArea(0, 0, pageFormat.getWidth(), pageFormat.getHeight());

// Print multiple copies
printerJob.setCopies(2);

pageFormat.setPaper(paper);

printerJob.setPrintable(document, pageFormat);
printerJob.print();

document.dispose();

This approach is typically used when:

Print layout must be consistent across environments
Margins and printable area need explicit control
Multiple copies are required in a single job

3. Custom Paper Size Configuration for Word Printing

In real business systems, documents are not always standard A4 pages. Labels, receipts, and custom reports often require different page sizes.

Document doc = new Document();
doc.loadFromFile("data/report.docx");

PrintDocument printDoc = doc.getPrintDocument();

PaperSize paperSize = new PaperSize();
paperSize.setWidth(900);
paperSize.setHeight(800);

printDoc.getDefaultPageSettings().setPaperSize(paperSize);

printDoc.print();

doc.dispose();

Paper sizes in Java printing are defined in points:

Paper Type	Size (points)
A4	595 × 842
Letter	612 × 792
Legal	612 × 1008
A3	842 × 1191

1 inch equals 72 points

Incorrect paper configuration is one of the most common reasons for layout issues when printing Word documents programmatically.

4. Page Margins and Duplex Printing in Java

In production document systems, margin control and duplex printing are often required for consistency and cost efficiency.

Document doc = new Document();
doc.loadFromFile("data/report.docx");

PrintDocument printDoc = doc.getPrintDocument();

printDoc.setOriginAtMargins(true);

// Set page margins (left, top, right, bottom)
printDoc.getDefaultPageSettings()
        .setMargins(new Margins(0, 0, 0, 0));

// Enable duplex printing (long-edge binding)
printDoc.getPrinterSettings().setDuplex(Duplex.Vertical);

printDoc.print();

doc.dispose();

Duplex printing modes

Mode	Description	Typical use case
Duplex.Vertical	Long-edge binding	Reports, books
Duplex.Horizontal	Short-edge binding	Calendars
Simplex	Single-sided printing	Default output

In practice, duplex behavior may also depend on printer drivers and hardware support, not just application-level configuration.

5. Batch Printing Multiple Word Documents in Java

Batch printing is common in automated document systems, where files are processed from a directory rather than individually triggered.

File folder = new File("C:\\Documents\\ToPrint");

File[] files = folder.listFiles((dir, name) ->
        name.toLowerCase().endsWith(".docx") ||
        name.toLowerCase().endsWith(".doc")
);

for (File file : files) {

    Document document = null;

    try {
        document = new Document();
        document.loadFromFile(file.getAbsolutePath());

        PrintDocument printDoc = document.getPrintDocument();
        printDoc.print();

        System.out.println("Printed: " + file.getName());

    } catch (Exception e) {
        System.err.println("Failed: " + file.getName());

    } finally {
        if (document != null) {
            document.dispose();
        }
    }
}

In batch scenarios, stability matters more than performance optimization:

Each document must be isolated
Failures should not interrupt the entire batch
Memory should be released consistently after each print job

6. Common Issues When Printing Word Documents in Java

Print dialog appears during execution

This usually happens when the printing process is not fully configured for silent execution or the system defaults override programmatic settings.

Document layout is cut off or misaligned

This is typically caused by:

Incorrect paper size configuration
Improper margin setup
Printer scaling or driver behavior

Duplex printing does not work as expected

In most cases, this is not a code issue but a limitation of the printer or its driver settings.

If duplex is not supported, the system will fall back to single-sided printing.

printDoc.getPrinterSettings().setDuplex(Duplex.Simplex);

7. Practical Considerations for Production Printing Systems

Always release document resources

try {
    document.loadFromFile(filePath);
} finally {
    document.dispose();
}

Resource cleanup is critical in batch processing environments.

Validate environment before printing

Before sending a print job, it is usually necessary to ensure:

The file exists and is accessible
A valid printer is available
Paper size and layout are correctly defined

Conclusion

Printing Word documents in Java is straightforward at the API level, but real-world implementation depends heavily on printer environments, driver behavior, and document formatting consistency.

Most production issues are not caused by code itself, but by differences between printers and system configurations.

How to Add Hyperlinks to Word Documents in Java

Leon Davis — Thu, 11 Jun 2026 10:41:14 +0000

Hyperlinks in Word documents are not limited to opening web pages. They can also link to email addresses, local files, bookmarks within the same document, and even images. In technical documents, project reports, user manuals, and multi-section documents, hyperlinks help readers navigate information more efficiently.

For a small document, adding hyperlinks manually in Microsoft Word may be acceptable. However, when you need to add links to multiple keywords, generate documents in batches, or process many files automatically, using Java is more efficient and helps keep hyperlink formatting consistent.

This article shows how to work with Word hyperlinks in Java, including how to add web links, email links, file links, bookmark links, image hyperlinks, and hyperlinks to existing text. It also includes a complete example for batch-adding reference links to keywords in a technical document.

Environment Setup

Before writing the code, you need a Java development environment and a library for working with Word documents. The examples in this article use Spire.Doc for Java, which can create, read, edit, and save Word documents without requiring Microsoft Word to be installed.

1. Add the Maven Dependency

If your project uses Maven, add the following configuration to your 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.doc</artifactId>
        <version>14.6.0</version>
    </dependency>
</dependencies>

If you use Gradle, add the following configuration:

repositories {
    maven {
        url 'https://repo.e-iceblue.com/nexus/content/groups/public/'
    }
}

dependencies {
    implementation 'e-iceblue:spire.doc:14.6.0'
}

2. Prepare Test Files

Some examples in this article create a new Word document directly. Other examples load an existing Word document by using the loadFromFile() method:

document.loadFromFile("sample.docx");

This means the program reads sample.docx from the current project directory. If the file is stored elsewhere, use a full file path instead:

document.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.docx");

Similarly, if the code uses external images or files, make sure those files exist and the paths are correct. Otherwise, the generated hyperlink may not open the target file, or the image may fail to be inserted into the document.

Add Web and Email Links to a Word Document

Web links and email links are the most common hyperlink types in Word documents. They are often used for official websites, online references, documentation pages, support pages, or contact information.

In Spire.Doc for Java, you can use the paragraph.appendHyperlink() method to add a hyperlink to a paragraph. You need to specify the target address, the display text, and the hyperlink type.

The following example adds a web link and an email link to a Word document:

import com.spire.doc.*;
import com.spire.doc.documents.*;

public class WebAndEmailLinks {
    public static void main(String[] args) {
        // Create a Word document
        Document doc = new Document();
        Section section = doc.addSection();
        Paragraph paragraph = section.addParagraph();

        // Add a web link
        paragraph.appendHyperlink(
            "https://www.google.com/",
            "Visit Google",
            HyperlinkType.Web_Link
        );

        // Add line breaks
        paragraph.appendBreak(BreakType.Line_Break);
        paragraph.appendBreak(BreakType.Line_Break);

        // Add an email link
        paragraph.appendHyperlink(
            "mailto:12345@mail.com",
            "Contact Us",
            HyperlinkType.E_Mail_Link
        );

        // Save the document
        doc.saveToFile("output/web_email_links.docx", FileFormat.Docx_2013);
        doc.dispose();
    }
}

Note that whether an email link opens correctly depends on whether the user has a default email client configured on their machine.

Link to an External File

In project documentation, financial reports, or resource lists, you may need to link from a Word document to an external file, such as an Excel workbook, a PDF document, an image, or another supporting file. This allows users to access related materials from a single Word index document.

For this type of hyperlink, use HyperlinkType.File_Link. The target path can be either a relative path or an absolute path. In real projects, relative paths are often preferred because they are less likely to break when the whole folder is moved together.

The following example creates a hyperlink to an external Excel file:

import com.spire.doc.*;
import com.spire.doc.documents.*;

public class FileLink {
    public static void main(String[] args) {
        Document doc = new Document();
        Section section = doc.addSection();
        Paragraph paragraph = section.addParagraph();

        // Create a link to an external file
        String filePath = "C:\\Users\\Admin\\Desktop\\report.xlsx";
        paragraph.appendHyperlink(
            filePath,
            "View Report",
            HyperlinkType.File_Link
        );

        doc.saveToFile("output/file_link.docx", FileFormat.Docx_2013);
        doc.dispose();
    }
}

When sharing the Word document with others, make sure the linked files are also available to the recipient. Otherwise, the file links may not work on another computer.

Link to a Bookmark Inside the Word Document

Internal links are useful in long or multi-section Word documents. They allow readers to jump from a table of contents page to a specific chapter, from an introduction to an appendix, or from a summary section to detailed data.

To create an internal hyperlink in Spire.Doc for Java, you need to create a bookmark first, and then add a hyperlink that points to that bookmark. The hyperlink type should be set to HyperlinkType.Bookmark.

The following example creates a bookmark and adds a link to it:

import com.spire.doc.*;
import com.spire.doc.documents.*;

public class BookmarkLink {
    public static void main(String[] args) {
        Document doc = new Document();

        // First section: add a hyperlink to the bookmark
        Section section1 = doc.addSection();
        Paragraph linkPara = section1.addParagraph();
        linkPara.appendHyperlink(
            "myBookmark",
            "Go to Chapter 2",
            HyperlinkType.Bookmark
        );

        // Second section: create the bookmark target
        Section section2 = doc.addSection();
        Paragraph bookmarkPara = section2.addParagraph();

        // Add bookmark start, text, and bookmark end
        bookmarkPara.appendBookmarkStart("myBookmark");
        bookmarkPara.appendText("This is the content of Chapter 2.");
        bookmarkPara.appendBookmarkEnd("myBookmark");

        doc.saveToFile("output/bookmark_link.docx", FileFormat.Docx_2013);
        doc.dispose();
    }
}

Common use cases include:

Linking a contents page to chapters
Linking an introduction to detailed explanations
Linking a summary to raw data sections
Linking glossary terms to definitions
Creating quick navigation in long documents

Bookmark names should be short and clear. Avoid special characters where possible. For example:

"chapter1"      // Chapter 1
"appendix_a"    // Appendix A
"conclusion"    // Conclusion

Keeping bookmark names simple helps reduce path or name resolution issues.

Add a Hyperlink to an Image in Word

Word hyperlinks can also be applied to images. This is useful when creating clickable logos, icon buttons, help entries, or navigation images.

In Spire.Doc for Java, you can insert an image by using paragraph.appendPicture(), and then call paragraph.appendHyperlink() to make the image clickable.

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.*;

public class ImageHyperlink {
    public static void main(String[] args) {
        Document doc = new Document();
        Section section = doc.addSection();
        Paragraph paragraph = section.addParagraph();

        // Insert an image
        String imagePath = "C:\\Users\\Admin\\Desktop\\logo.png";
        DocPicture picture = paragraph.appendPicture(imagePath);

        // Add a hyperlink to the image
        paragraph.appendHyperlink(
            "https://www.e-iceblue.com/",
            picture,
            HyperlinkType.Web_Link
        );

        doc.saveToFile("output/image_link.docx", FileFormat.Docx_2013);
        doc.dispose();
    }
}

In this example:

paragraph.appendPicture(imagePath) inserts the image into the paragraph.
The second parameter of appendHyperlink() is the image object, which means the hyperlink is applied to the image.
The third parameter specifies that the hyperlink is a web link.

Make sure the image path is correct before running the code. If the image file cannot be found, it will not be inserted into the document.

Add a Hyperlink to Existing Text

In many real projects, the Word document already exists, and you need to add hyperlinks to specific text, such as keywords, product names, class names, or reference terms. In this case, you cannot simply use appendHyperlink() because that method is mainly used to append new hyperlink content.

To convert existing text into a hyperlink, you need to build a Word hyperlink field structure.

A Word hyperlink field usually contains the following parts:

Field - the field start object
Field_Separator - the separator between the field code and display text
TextRange - the visible hyperlink text
Field_End - the field end marker

The following example adds a hyperlink to the first occurrence of the word Java in an existing Word document:

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.*;
import com.spire.doc.interfaces.*;
import java.awt.Color;

public class AddLinkToExistingText {
    public static void main(String[] args) {
        // Load an existing Word document
        Document document = new Document();
        document.loadFromFile("input/sample.docx");

        // Find all occurrences of "Java"
        TextSelection[] selections = document.findAllString("Java", true, true);

        if (selections.length > 0) {
            // Get the first matched text range
            TextRange range = selections[0].getAsOneRange();

            // Get the owner paragraph
            Paragraph paragraph = range.getOwnerParagraph();

            // Get the position of the text range in the paragraph
            int index = paragraph.getItems().indexOf(range);

            // Remove the original text range
            paragraph.getItems().remove(range);

            // 1. Create a hyperlink field
            Field field = new Field(document);
            field.setType(FieldType.Field_Hyperlink);

            Hyperlink hyperlink = new Hyperlink(field);
            hyperlink.setType(HyperlinkType.Web_Link);
            hyperlink.setUri("https://en.wikipedia.org/wiki/Java_(programming_language)");

            paragraph.getItems().insert(index, field);

            // 2. Insert the field separator
            IParagraphBase separator = document.createParagraphItem(ParagraphItemType.Field_Mark);
            FieldMark start = (FieldMark) separator;
            start.setType(FieldMarkType.Field_Separator);
            paragraph.getItems().insert(index + 1, start);

            // 3. Insert the display text and preserve part of the original formatting
            ITextRange textRange = new TextRange(document);
            textRange.setText("Java");

            textRange.getCharacterFormat().setFontName(
                range.getCharacterFormat().getFontName()
            );
            textRange.getCharacterFormat().setFontSize(
                range.getCharacterFormat().getFontSize()
            );

            // Apply a common hyperlink style: blue text with underline
            textRange.getCharacterFormat().setTextColor(Color.blue);
            textRange.getCharacterFormat().setUnderlineStyle(UnderlineStyle.Single);

            paragraph.getItems().insert(index + 2, textRange);

            // 4. Insert the field end marker
            IParagraphBase endItem = document.createParagraphItem(ParagraphItemType.Field_Mark);
            FieldMark end = (FieldMark) endItem;
            end.setType(FieldMarkType.Field_End);
            paragraph.getItems().insert(index + 3, end);
        }

        // Save the document
        document.saveToFile("output/java_with_link.docx", FileFormat.Docx_2013);
        document.dispose();
    }
}

The core logic is:

Find the target text.
Remove the original text.
Insert a hyperlink field structure at the same position.
Add display text and apply hyperlink formatting.

Key techniques used in this example:

Preserve original formatting: copy properties such as font name and font size from the original text.
Apply hyperlink style: use blue text and underline, which matches common user expectations.
Insert at the correct position: use indexOf() to locate the original text position before replacing it.

Complete Example: Batch Add Hyperlinks to Keywords

The previous sections covered web links, email links, file links, bookmark links, image links, and hyperlinks for existing text. In this section, we combine these ideas into a batch-processing example.

This example scans a technical document and adds official reference links to programming language or technology keywords. After processing, readers can click the keywords directly to open related official resources.

import com.spire.doc.*;
import com.spire.doc.documents.*;
import com.spire.doc.fields.*;
import com.spire.doc.interfaces.*;
import java.awt.Color;
import java.util.LinkedHashMap;
import java.util.Map;

public class BatchAddHyperlinks {

    /**
     * Add hyperlinks to all matched keyword occurrences in a document.
     */
    public static void addLinksToKeyword(Document document, String keyword, String url) {
        // Find all matched text ranges
        TextSelection[] selections = document.findAllString(keyword, false, true);

        System.out.println("Found " + selections.length + " occurrence(s) of \"" + keyword + "\"");

        // Process from the end to the beginning to avoid index changes
        for (int i = selections.length - 1; i >= 0; i--) {
            try {
                TextRange range = selections[i].getAsOneRange();
                Paragraph paragraph = range.getOwnerParagraph();
                int index = paragraph.getItems().indexOf(range);

                // Skip text that is already inside a hyperlink field
                if (isAlreadyHyperlinked(paragraph, index)) {
                    continue;
                }

                // Use the actual matched text as the display text
                String displayText = range.getText();

                // Remove the original text range
                paragraph.getItems().remove(range);

                // Create a hyperlink at the original position
                createHyperlinkInParagraph(document, paragraph, index, displayText, url, range);

            } catch (Exception e) {
                System.err.println("Error while processing occurrence " + (i + 1) + ": " + e.getMessage());
            }
        }
    }

    /**
     * Check whether the current text range is already inside a hyperlink field.
     */
    private static boolean isAlreadyHyperlinked(Paragraph paragraph, int itemIndex) {
        boolean insideHyperlink = false;

        for (int i = 0; i <= itemIndex; i++) {
            Object item = paragraph.getItems().get(i);

            if (item instanceof Field) {
                Field field = (Field) item;
                if (field.getType() == FieldType.Field_Hyperlink) {
                    insideHyperlink = true;
                }
            } else if (item instanceof FieldMark) {
                FieldMark mark = (FieldMark) item;
                if (mark.getType() == FieldMarkType.Field_End) {
                    insideHyperlink = false;
                }
            }
        }

        return insideHyperlink;
    }

    /**
     * Create a hyperlink field in a paragraph.
     */
    private static void createHyperlinkInParagraph(
        Document document,
        Paragraph paragraph,
        int index,
        String text,
        String url,
        TextRange originalRange
    ) {
        // 1. Create a hyperlink field
        Field field = new Field(document);
        field.setType(FieldType.Field_Hyperlink);

        Hyperlink hyperlink = new Hyperlink(field);
        hyperlink.setType(HyperlinkType.Web_Link);
        hyperlink.setUri(url);

        paragraph.getItems().insert(index, field);

        // 2. Insert the field separator
        IParagraphBase separator = document.createParagraphItem(ParagraphItemType.Field_Mark);
        FieldMark start = (FieldMark) separator;
        start.setType(FieldMarkType.Field_Separator);
        paragraph.getItems().insert(index + 1, start);

        // 3. Insert the display text and preserve part of the original formatting
        ITextRange textRange = new TextRange(document);
        textRange.setText(text);

        if (originalRange != null) {
            textRange.getCharacterFormat().setFontName(
                originalRange.getCharacterFormat().getFontName()
            );
            textRange.getCharacterFormat().setFontSize(
                originalRange.getCharacterFormat().getFontSize()
            );
            textRange.getCharacterFormat().setBold(
                originalRange.getCharacterFormat().isBold()
            );
            textRange.getCharacterFormat().setItalic(
                originalRange.getCharacterFormat().isItalic()
            );
        }

        // Apply a common hyperlink style
        textRange.getCharacterFormat().setTextColor(Color.blue);
        textRange.getCharacterFormat().setUnderlineStyle(UnderlineStyle.Single);

        paragraph.getItems().insert(index + 2, textRange);

        // 4. Insert the field end marker
        IParagraphBase endItem = document.createParagraphItem(ParagraphItemType.Field_Mark);
        FieldMark end = (FieldMark) endItem;
        end.setType(FieldMarkType.Field_End);
        paragraph.getItems().insert(index + 3, end);
    }

    public static void main(String[] args) {
        // Load the Word document
        Document document = new Document();
        document.loadFromFile("input/technical_doc.docx");

        // Define keywords and their corresponding links
        Map<String, String> keywordLinks = new LinkedHashMap<>();
        keywordLinks.put("Java", "https://www.oracle.com/java/");
        keywordLinks.put("Python", "https://www.python.org/");
        keywordLinks.put("Spring Boot", "https://spring.io/projects/spring-boot");
        keywordLinks.put("MySQL", "https://www.mysql.com/");

        // Add hyperlinks in batches
        for (Map.Entry<String, String> entry : keywordLinks.entrySet()) {
            System.out.println("\nProcessing keyword: " + entry.getKey());
            addLinksToKeyword(document, entry.getKey(), entry.getValue());
        }

        // Save the result document
        document.saveToFile("output/doc_with_links.docx", FileFormat.Docx_2013);
        document.dispose();

        System.out.println("\nBatch hyperlink processing completed.");
    }
}

This example does the following:

Finds all keyword occurrences in the document
Adds the corresponding reference link to each keyword
Preserves part of the original text formatting, such as font, size, bold, and italic
Applies a common hyperlink style, using blue text and underline
Processes matches from the end to the beginning to avoid index shifting
Skips text that is already inside a hyperlink field

This structure is suitable for:

Automatically generated technical documents
API documentation that links class names or method names
Help documents that link terms to explanations
Report systems that link product names to official pages

Summary

Word hyperlinks can be used for web pages, email addresses, external files, internal bookmarks, and clickable images. By using Java to automate hyperlink creation, you can process long documents, technical manuals, and batch-generated reports more efficiently.

How to AutoFit Excel Row Height and Column Width in C#

Leon Davis — Thu, 11 Jun 2026 09:39:40 +0000

When generating or processing Excel files programmatically, it is common to encounter layout issues. For example, long text may be cut off because the column is too narrow, wrapped text may not be fully visible, or exported data may look uneven after being written in bulk.

For a single Excel file, you can manually adjust the layout in Microsoft Excel by double-clicking the row or column border. However, in scenarios such as report generation, data export, or batch file processing, it is more practical to adjust row heights and column widths automatically through code.

This article shows how to auto fit row height and column width in Excel using C#. The examples cover the following scenarios:

Auto fit all used rows and columns in a worksheet
Auto fit a specific column
Auto fit a specific row
Auto fit a specific cell range

Preparation

This article uses Free Spire.XLS for .NET to work with Excel files in C#. You can install it through NuGet:

Install-Package FreeSpire.XLS

Then include the following namespace in your code:

using Spire.Xls;

The examples use the following Excel file path:

E:\Files\Test.xlsx

You can replace it with your own file path as needed.

Auto Fit All Rows and Columns in an Excel Worksheet

If you want the data in a worksheet to be displayed as completely as possible, you can get the used range of the worksheet and call the AutoFitColumns() and AutoFitRows() methods.

using Spire.Xls;

namespace AutoFitWorksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Load an Excel file
            workbook.LoadFromFile(@"E:\Files\Test.xlsx");

            // Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Auto fit columns in the used range
            worksheet.AllocatedRange.AutoFitColumns();

            // Auto fit rows in the used range
            worksheet.AllocatedRange.AutoFitRows();

            // Save the result file
            workbook.SaveToFile(@"E:\Files\AutoFit.xlsx", FileFormat.Version2010);

            workbook.Dispose();
        }
    }
}

Here, AllocatedRange refers to the actual used range in the worksheet. Compared with processing the entire worksheet, this approach is more suitable for common data tables and avoids unnecessary operations on blank areas.

Auto Fit a Specific Column in Excel

In some worksheets, you may not need to adjust every column. Instead, you may only need to adjust columns that contain longer text, such as product names, customer names, addresses, or remarks.

The following example shows how to auto fit the width of the first column:

using Spire.Xls;

namespace AutoFitSpecificColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Files\Test.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            // Auto fit the first column
            // Note: The column index starts from 1
            worksheet.AutoFitColumn(1);

            workbook.SaveToFile(@"E:\Files\AutoFitColumn.xlsx", FileFormat.Version2010);

            workbook.Dispose();
        }
    }
}

If you need to auto fit multiple columns, you can use a loop:

// Auto fit columns from 1 to 3
for (int i = 1; i <= 3; i++)
{
    worksheet.AutoFitColumn(i);
}

This approach is useful when a worksheet has many columns, but only some fields require automatic width adjustment.

Auto Fit a Specific Row in Excel

When cells contain wrapped text, large font sizes, or imported content of different lengths, the row height may not be enough to display all content. In this case, you can auto fit only the row that needs adjustment.

The following example shows how to auto fit the height of the first row:

using Spire.Xls;

namespace AutoFitSpecificRow
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Files\Test.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            // Auto fit the first row
            // Note: The row index starts from 1
            worksheet.AutoFitRow(1);

            workbook.SaveToFile(@"E:\Files\AutoFitRow.xlsx", FileFormat.Version2010);

            workbook.Dispose();
        }
    }
}

If you need to auto fit multiple rows, you can also use a loop:

// Auto fit rows from 1 to 10
for (int i = 1; i <= 10; i++)
{
    worksheet.AutoFitRow(i);
}

This method is suitable for title rows, description rows, or data rows with variable content length.

Auto Fit a Specific Cell Range in Excel

In real-world Excel files, you may not want to adjust the entire worksheet. For example, a worksheet may contain a report title, notes, a data table, and a signature area. In this case, you can auto fit only a specific range.

The following example auto fits the row height and column width in the range A1:D10:

using Spire.Xls;

namespace AutoFitRange
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Files\Test.xlsx");

            Worksheet worksheet = workbook.Worksheets[0];

            // Get a specific cell range
            CellRange range = worksheet.Range["A1:D10"];

            // Auto fit columns in the range
            range.AutoFitColumns();

            // Auto fit rows in the range
            range.AutoFitRows();

            workbook.SaveToFile(@"E:\Files\AutoFitRange.xlsx", FileFormat.Version2010);

            workbook.Dispose();
        }
    }
}

This approach is especially useful for report templates. For example, you can adjust only the data table area without affecting the title, header notes, or footer remarks.

Notes and Best Practices

Auto fitting row height and column width is mainly calculated based on the content inside cells. In practice, the following points are worth noting.

1. Enable Text Wrapping When Needed

If cells contain long text or multiple lines of text, you can enable text wrapping before calling AutoFitRows().

// Enable text wrapping for the used range
worksheet.AllocatedRange.IsWrapText = true;

// Auto fit row height based on the wrapped content
worksheet.AllocatedRange.AutoFitRows();

However, IsWrapText = true changes how text is displayed in cells. If you want to preserve the original layout of a template, avoid applying it to the entire worksheet. Instead, apply it only to the required range:

CellRange range = worksheet.Range["A1:D10"];
range.IsWrapText = true;
range.AutoFitRows();

2. Be Careful with Merged Cells

If a worksheet contains merged cells, the auto fit result may be different from that of normal cells, especially when text spans multiple columns. For such areas, you may need to check the output file and manually set row height or column width if necessary.

3. Avoid Processing Blank Areas

Auto fitting usually has little effect on empty cells. For better efficiency, it is recommended to apply auto fitting to the used range or a specific data range instead of the entire worksheet.

4. Consider Performance for Large Workbooks

For large workbooks, auto fitting a wide range of cells may add extra processing time. If only certain rows, columns, or ranges need adjustment, apply auto fitting only to those areas.

Conclusion

Auto fitting row height and column width is a common requirement when creating or processing Excel files in C#. For regular data tables, you can call AutoFitColumns() and AutoFitRows() on the worksheet’s used range. For more complex worksheets, you can apply auto fitting only to specific rows, columns, or cell ranges.

By using these methods properly, you can make generated Excel files easier to read and reduce the need for manual layout adjustments after export.

Convert PDF to PDF/A or PDF/A to PDF in Java: Complete Guide

Leon Davis — Fri, 05 Jun 2026 12:18:58 +0000

When working with enterprise documents, government archives, or files that need long-term preservation, you've probably heard of "PDF/A." Many people know about PDF, but aren't familiar with PDF/A—what exactly is it? Why convert to it? And how do you implement it in Java?

Today, let's explore this topic and share some practical development experiences with code examples.

Understanding PDF/A

PDF/A (Portable Document Format/Archive) is an archival version of PDF specifically designed for long-term preservation of electronic documents. Compared to regular PDF, it has these characteristics:

PDF/A Restrictions:

❌ No external dependencies except embedded fonts
❌ No JavaScript, audio, video, or other dynamic content
❌ No encryption (partially allowed in some levels)
❌ All colors must be explicitly defined (no device-dependent colors)

PDF/A Advantages:

✅ Ensures documents display correctly even decades later
✅ Self-contained, no external resource dependencies
✅ Complies with ISO standards (ISO 19005)
✅ Widely adopted by governments, courts, and archives

Common PDF/A Standards:

PDF/A-1 (2005): The earliest standard, based on PDF 1.4
PDF/A-2 (2011): Supports transparency effects and JPEG 2000 compression
PDF/A-3 (2012): Allows embedding arbitrary file formats (XML, CSV, etc.)

Each standard has two conformance levels:

Level A (Accessible): Preserves structural information for accessibility
Level B (Basic): Guarantees consistent visual rendering only

Environment Setup

Maven Dependency

<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.pdf</artifactId>
        <version>12.6.1</version>
    </dependency>
</dependencies>

Gradle Configuration

repositories {
    maven {
        url 'https://repo.e-iceblue.cn/repository/maven-public/'
    }
}

dependencies {
    implementation 'e-iceblue:spire.pdf:12.6.1'
}

1. Basic Conversion: PDF to Various PDF/A Formats

The most straightforward approach—using the PdfStandardsConverter class:

import com.spire.pdf.conversion.PdfStandardsConverter;

public class BasicPdfToPdfA {
    public static void main(String[] args) {
        // Create converter instance
        PdfStandardsConverter converter = new PdfStandardsConverter("sample.pdf");

        // Convert to different PDF/A levels
        converter.toPdfA1A("output/PdfA1A.pdf");   // PDF/A-1A
        converter.toPdfA1B("output/PdfA1B.pdf");   // PDF/A-1B
        converter.toPdfA2A("output/PdfA2A.pdf");   // PDF/A-2A
        converter.toPdfA2B("output/PdfA2B.pdf");   // PDF/A-2B
        converter.toPdfA3A("output/PdfA3A.pdf");   // PDF/A-3A
        converter.toPdfA3B("output/PdfA3B.pdf");   // PDF/A-3B

        System.out.println("Conversion complete!");
    }
}

That's it! One line of code completes each format conversion.

How to Choose PDF/A Level?

Format	Use Case	Characteristics
PDF/A-1B	General archiving	Best compatibility, most conservative
PDF/A-2B	Modern documents	Supports transparency and layers
PDF/A-3B	Data embedding	Can embed XML, Excel, and other attachments
Level A	Accessibility needs	Preserves tag structure for disabled users
Level B	General purpose	Only guarantees visual consistency

Practical Recommendations:

Government/legal documents → PDF/A-1B (strictest)
Enterprise internal archiving → PDF/A-2B (balance of compatibility and features)
Need to embed data → PDF/A-3B (highest flexibility)

2. Handling Encrypted PDFs

If the source PDF is password-protected, decrypt it first before conversion:

import com.spire.pdf.conversion.PdfStandardsConverter;

public class EncryptedPdfToPdfA {
    public static void main(String[] args) {
        String inputFile = "data/encrypted.pdf";
        String password = "your_password";

        // Pass password when creating converter
        PdfStandardsConverter converter = new PdfStandardsConverter(inputFile, password);

        // Convert to PDF/A-2A
        converter.toPdfA2A("output/decrypted_pdfa.pdf");

        System.out.println("Encrypted PDF conversion complete!");
    }
}

Important Notes:

The converted PDF/A file is no longer encrypted (PDF/A standard restriction)
If you need to re-encrypt after archiving, handle it separately

3. Preserving Metadata

By default, conversion might lose some metadata. To preserve it, configure as follows:

import com.spire.pdf.conversion.PdfStandardsConverter;

public class PdfToPdfAWithMetadata {
    public static void main(String[] args) {
        String input = "data/document_with_metadata.pdf";
        String output = "output/pdfa_with_metadata.pdf";

        PdfStandardsConverter converter = new PdfStandardsConverter(input);

        // Key setting: preserve allowed metadata
        converter.getOptions().setPreserveAllowedMetadata(true);

        // Execute conversion
        converter.toPdfA1A(output);

        System.out.println("Conversion complete, metadata preserved!");
    }
}

Which Metadata Is Preserved?

✅ Title, author, subject, keywords
✅ Creation date, modification date
✅ PDF/A compliance information
❌ Some custom properties (if they don't comply with PDF/A standards)

4. Converting PDF/A Back to Regular PDF

Sometimes you need the reverse operation—converting PDF/A back to regular PDF (e.g., to add interactive features):

import com.spire.pdf.*;
import com.spire.pdf.graphics.PdfMargins;
import java.awt.geom.Dimension2D;

public class PdfAToPdf {
    public static void main(String[] args) {
        String input = "data/sample_pdfa.pdf";
        String output = "output/regular_pdf.pdf";

        // Load PDF/A file
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile(input);

        // Create new document (non-PDF/A)
        PdfNewDocument newDoc = new PdfNewDocument();
        newDoc.setCompressionLevel(PdfCompressionLevel.None);

        // Copy content page by page
        for (PdfPageBase page : (Iterable<PdfPageBase>) doc.getPages()) {
            Dimension2D size = page.getSize();
            PdfPageBase p = newDoc.getPages().add(size, new PdfMargins(0));

            // Draw page content using template
            page.createTemplate().draw(p, 0, 0);
        }

        // Save as regular PDF
        newDoc.save(output);

        // Release resources
        newDoc.close();
        newDoc.dispose();
        doc.close();
        doc.dispose();

        System.out.println("PDF/A to PDF conversion complete!");
    }
}

Core Approach:

Load the PDF/A document
Create a new regular PDF document
Copy content page by page (via templates)
Save as new file

Use Cases:

Need to add JavaScript interactivity
Want to embed multimedia content
Remove PDF/A restrictions for editing

5. Creating PDF/A with Attachments

PDF/A-3 allows embedding arbitrary files as attachments, which is very useful in archiving scenarios:

import com.spire.pdf.*;
import com.spire.pdf.attachments.PdfAttachment;
import com.spire.pdf.graphics.PdfMargins;
import java.awt.geom.Dimension2D;
import java.io.*;

public class PdfAWithAttachments {
    public static void main(String[] args) throws IOException {
        String input = "data/report.pdf";
        String output = "output/report_with_attachments.pdfa";

        // Load source PDF
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile(input);

        // Create PDF/A-3B document
        PdfNewDocument newDoc = new PdfNewDocument();
        newDoc.setConformance(PdfConformanceLevel.Pdf_A_3_B);

        // Copy page content
        for (PdfPageBase page : (Iterable<PdfPageBase>) doc.getPages()) {
            Dimension2D size = page.getSize();
            PdfPageBase p = newDoc.getPages().add(size, new PdfMargins(0));
            page.createTemplate().draw(p, 0, 0);
        }

        // Read attachment data
        byte[] excelData = readBytesFromFile("data/raw_data.xlsx");
        byte[] xmlData = readBytesFromFile("data/metadata.xml");

        // Create attachment objects
        PdfAttachment attach1 = new PdfAttachment("raw_data.xlsx", excelData);
        PdfAttachment attach2 = new PdfAttachment("metadata.xml", xmlData);

        // Add attachments
        newDoc.getAttachments().add(attach1);
        newDoc.getAttachments().add(attach2);

        // Save
        newDoc.save(output, FileFormat.PDF);

        // Release resources
        doc.close();
        doc.dispose();
        newDoc.close();
        newDoc.dispose();

        System.out.println("PDF/A-3B created with 2 attachments!");
    }

    private static byte[] readBytesFromFile(String filePath) throws IOException {
        FileInputStream input = new FileInputStream(filePath);
        byte[] data = new byte[input.available()];
        input.read(data);
        input.close();
        return data;
    }
}

Typical Use Cases:

Financial reports + raw Excel data
Academic papers + research datasets
Contract documents + signing record XML
Technical documentation + source code packages

6. Practical Example: Batch Conversion Tool

In real projects, you often need to process files in batch. Here's a complete utility class:

import com.spire.pdf.conversion.PdfStandardsConverter;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class BatchPdfToPdfAConverter {

    /**
     * Batch convert all PDFs in a folder to PDF/A
     * 
     * @param inputDir Input folder path
     * @param outputDir Output folder path
     * @param pdfALevel PDF/A level (e.g., "1B", "2B", "3B")
     */
    public static void batchConvert(String inputDir, String outputDir, String pdfALevel) {
        File dir = new File(inputDir);

        if (!dir.exists() || !dir.isDirectory()) {
            System.err.println("Error: Input directory does not exist - " + inputDir);
            return;
        }

        // Create output directory
        new File(outputDir).mkdirs();

        // Get all PDF files
        File[] pdfFiles = dir.listFiles((d, name) -> 
            name.toLowerCase().endsWith(".pdf") && !name.toLowerCase().contains("pdfa")
        );

        if (pdfFiles == null || pdfFiles.length == 0) {
            System.out.println("No PDF files found");
            return;
        }

        int successCount = 0;
        int failCount = 0;
        List<String> errors = new ArrayList<>();

        System.out.println("Starting batch conversion, total " + pdfFiles.length + " files...\n");

        for (File pdfFile : pdfFiles) {
            try {
                String outputFileName = pdfFile.getName().replace(".pdf", "_PDFA-" + pdfALevel + ".pdf");
                String outputPath = outputDir + File.separator + outputFileName;

                PdfStandardsConverter converter = new PdfStandardsConverter(pdfFile.getAbsolutePath());

                // Convert according to specified level
                switch (pdfALevel.toUpperCase()) {
                    case "1A":
                        converter.toPdfA1A(outputPath);
                        break;
                    case "1B":
                        converter.toPdfA1B(outputPath);
                        break;
                    case "2A":
                        converter.toPdfA2A(outputPath);
                        break;
                    case "2B":
                        converter.toPdfA2B(outputPath);
                        break;
                    case "3A":
                        converter.toPdfA3A(outputPath);
                        break;
                    case "3B":
                        converter.toPdfA3B(outputPath);
                        break;
                    default:
                        throw new IllegalArgumentException("Unsupported PDF/A level: " + pdfALevel);
                }

                successCount++;
                System.out.println("✓ " + pdfFile.getName() + " -> " + outputFileName);

            } catch (Exception e) {
                failCount++;
                String errorMsg = pdfFile.getName() + ": " + e.getMessage();
                errors.add(errorMsg);
                System.err.println("✗ " + errorMsg);
            }
        }

        // Output statistics
        System.out.println("\n========== Conversion Complete ==========");
        System.out.println("Successful: " + successCount);
        System.out.println("Failed: " + failCount);

        if (!errors.isEmpty()) {
            System.out.println("\nError Details:");
            for (String error : errors) {
                System.out.println("  - " + error);
            }
        }
    }

    public static void main(String[] args) {
        // Batch convert to PDF/A-2B
        batchConvert("input/pdfs", "output/pdfa", "2B");
    }
}

Features:

✅ Automatically scans all PDFs in folder
✅ Supports all PDF/A levels
✅ Detailed progress feedback and error reporting
✅ Skips already converted files (filename doesn't contain "pdfa")
✅ Automatically creates output directory

7. Common Issues and Solutions

Issue 1: Conversion Fails Due to Font Problems

Cause: PDF uses fonts that are not embedded.

Solution:

// Spire.PDF automatically handles font embedding
// If still failing, check if source PDF is corrupted
PdfStandardsConverter converter = new PdfStandardsConverter(inputFile);
converter.getOptions().setDisableFontSubstitution(false); // Allow font substitution
converter.toPdfA1B(outputFile);

Issue 2: File Size Increases Dramatically After Conversion

Cause: PDF/A requires embedding all fonts and resources.

Optimization Suggestions:

// 1. Compress source PDF before conversion
// 2. Use more efficient compression algorithms
// 3. Remove unnecessary metadata

// For large files, consider batch processing
Runtime runtime = Runtime.getRuntime();
long freeMemory = runtime.freeMemory();
if (freeMemory < 100 * 1024 * 1024) { // Less than 100MB
    System.gc(); // Trigger garbage collection
}

Issue 3: How to Verify Generated PDF/A Compliance?

Method 1: Use Online Validation Tools

veraPDF - Open-source PDF/A validator
Adobe Acrobat Pro - Built-in validation feature

Method 2: Programmatic Validation (Requires Additional Library)

// Can use Apache PDFBox preflight module
// Or call third-party API for validation

Issue 4: Slow Conversion Speed

Optimization Strategies:

// 1. Process multiple files in parallel
ExecutorService executor = Executors.newFixedThreadPool(4);
for (File file : files) {
    executor.submit(() -> convertSingleFile(file));
}
executor.shutdown();

// 2. Use SSD storage to improve I/O speed
// 3. Increase JVM heap memory: -Xmx4g

8. Best Practices Summary

1. Choose the Right PDF/A Level

Legal/Government Documents → PDF/A-1B (strictest, best compatibility)
Enterprise Internal Archiving → PDF/A-2B (balance of features and compatibility)
Research Data Archiving → PDF/A-3B (can embed datasets)
Accessibility Requirements → Level A series (preserves structural information)

2. Resource Management

// Always release resources in finally block
PdfStandardsConverter converter = null;
try {
    converter = new PdfStandardsConverter(inputFile);
    converter.toPdfA1B(outputFile);
} finally {
    if (converter != null) {
        converter.dispose();
    }
}

3. Error Handling

try {
    converter.toPdfA1B(outputFile);
} catch (Exception e) {
    // Log detailed error information
    logger.error("PDF conversion failed: " + inputFile, e);

    // Provide user-friendly messages
    if (e.getMessage().contains("font")) {
        System.err.println("Font issue, please check if source PDF uses special fonts");
    } else if (e.getMessage().contains("corrupt")) {
        System.err.println("File corrupted, please regenerate source PDF");
    }
}

4. Performance Monitoring

long startTime = System.currentTimeMillis();

// Execute conversion
converter.toPdfA1B(outputFile);

long endTime = System.currentTimeMillis();
System.out.println("Conversion time: " + (endTime - startTime) + " ms");

// Monitor memory usage
Runtime runtime = Runtime.getRuntime();
long usedMemory = (runtime.totalMemory() - runtime.freeMemory()) / (1024 * 1024);
System.out.println("Memory usage: " + usedMemory + " MB");

9. Comparison with Alternative Solutions

Spire.PDF vs Apache PDFBox

Feature	Spire.PDF	Apache PDFBox
API Simplicity	✅ One-line conversion	⚠️ Requires multi-step operations
PDF/A Support	✅ Full support for all levels	⚠️ Partial support
Learning Curve	✅ Low	⚠️ Moderate
License	Commercial (free tier available)	Apache 2.0 (free)
Chinese Language Support	✅ Excellent	⚠️ Requires extra configuration
Technical Support	✅ Official support	Community support

Selection Advice:

Sufficient budget, need rapid development → Spire.PDF
Open-source project, limited budget → Apache PDFBox
Need enterprise-level support → Spire.PDF

Conclusion

Converting PDF to PDF/A is common in real-world projects, especially in scenarios requiring long-term archiving. With Spire.PDF for Java, the entire process becomes quite simple:

Key Takeaways:

✅ Use PdfStandardsConverter for conversion
✅ Choose appropriate PDF/A level based on requirements
✅ Pay attention to resource cleanup (call dispose())
✅ Handle encrypted files and metadata preservation
✅ Implement proper error handling and logging for batch processing

Practical Application Recommendations:

Test with small samples first to confirm conversion quality
Establish automated processes for regular document archiving
Keep both original PDF and converted PDF/A
Periodically verify PDF/A file compliance

Hope this article helps you better understand and implement PDF to PDF/A conversion. If you have specific questions, feel free to discuss in the comments!

Happy coding! 🚀

How to Convert Between OFD and PDF in Java

Leon Davis — Fri, 05 Jun 2026 12:00:17 +0000

OFD, short for Open Fixed-layout Document, is a fixed-layout document format commonly used in China for electronic documents, invoices, and official files. PDF is another fixed-layout format widely used for document sharing, printing, and archiving.

In Java document processing projects, you may need to convert OFD files to PDF for easier viewing or distribution. You may also need to convert PDF files to OFD to meet specific document exchange or archiving requirements.

This article shows how to convert OFD to PDF and PDF to OFD in Java using Spire.PDF for Java. The examples are kept simple and focus on common conversion tasks.

Install Spire.PDF for Java

If you use Maven, add the following repository and dependency to your 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.pdf</artifactId>
        <version>12.6.1</version>
    </dependency>
</dependencies>

For Gradle projects, use:

repositories {
    maven {
        url 'https://repo.e-iceblue.cn/repository/maven-public/'
    }
}

dependencies {
    implementation 'e-iceblue:spire.pdf:12.6.1'
}

You can replace the version number with the version used in your project.

Convert OFD to PDF in Java

To convert an OFD file to PDF, use the OfdConverter class. You only need to specify the input OFD file, call the toPdf() method, and then release the converter.

import com.spire.pdf.conversion.OfdConverter;

public class ConvertOfdToPdf {
    public static void main(String[] args) {
        // Specify the input OFD file path
        String inputFile = "data/sample.ofd";

        // Specify the output PDF file path
        String outputFile = "output/sample.pdf";

        // Create an OfdConverter instance
        OfdConverter converter = new OfdConverter(inputFile);

        // Convert OFD to PDF
        converter.toPdf(outputFile);

        // Release resources
        converter.dispose();
    }
}

The OfdConverter class is used for OFD-related conversion. In this example, the source OFD file is loaded from the data folder and saved as a PDF file in the output folder.

Convert PDF to OFD in Java

To convert a PDF document to OFD, load the PDF with PdfDocument, then save it using FileFormat.OFD.

import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;

public class ConvertPdfToOfd {
    public static void main(String[] args) {
        // Create a PdfDocument object
        PdfDocument document = new PdfDocument();

        // Load the PDF file
        document.loadFromFile("data/sample.pdf");

        // Save the PDF file as OFD
        document.saveToFile("output/sample.ofd", FileFormat.OFD);

        // Release resources
        document.close();
        document.dispose();
    }
}

The important part is the saveToFile() method. By passing FileFormat.OFD, the loaded PDF document is saved as an OFD file.

Batch Convert OFD Files to PDF in Java

If you need to convert multiple OFD files, you can scan a folder and process all .ofd files one by one.

import com.spire.pdf.conversion.OfdConverter;

import java.io.File;

public class BatchConvertOfdToPdf {
    public static void main(String[] args) {
        String inputDirectory = "input/ofd";
        String outputDirectory = "output/pdf";

        File inputDir = new File(inputDirectory);
        File outputDir = new File(outputDirectory);

        if (!outputDir.exists()) {
            outputDir.mkdirs();
        }

        File[] ofdFiles = inputDir.listFiles((dir, name) -> name.toLowerCase().endsWith(".ofd"));

        if (ofdFiles == null || ofdFiles.length == 0) {
            System.out.println("No OFD files found.");
            return;
        }

        for (File ofdFile : ofdFiles) {
            String fileName = ofdFile.getName();
            String outputFileName = fileName.substring(0, fileName.lastIndexOf(".")) + ".pdf";
            String outputPath = new File(outputDir, outputFileName).getAbsolutePath();

            OfdConverter converter = new OfdConverter(ofdFile.getAbsolutePath());
            converter.toPdf(outputPath);
            converter.dispose();

            System.out.println("Converted: " + fileName);
        }
    }
}

This example is useful for simple batch conversion tasks, such as converting a folder of OFD invoices, reports, or archived files to PDF.

Practical Notes

Before using the conversion code in a project, there are a few details worth checking:

Make sure the input file path and output folder path are correct.
Create the output directory before saving converted files.
Call dispose() after using OfdConverter.
Call both close() and dispose() after using PdfDocument.
If the document contains Chinese characters or special fonts, test the output on the target server environment.
For important documents, compare the converted file with the source file before using it in production.

FAQs

Can Java convert OFD to PDF?

Yes. You can use OfdConverter in Spire.PDF for Java to load an OFD file and convert it to PDF with the toPdf() method.

Can Java convert PDF to OFD?

Yes. You can load a PDF file with PdfDocument and save it as an OFD file by using saveToFile() with FileFormat.OFD.

Does the conversion require Microsoft Office or Adobe Acrobat?

No. The conversion is handled directly in Java and does not require Microsoft Office or Adobe Acrobat to be installed.

Will the converted file keep the same layout?

OFD and PDF are both fixed-layout document formats, so they are suitable for preserving page layout during conversion. However, the final result may still depend on the source document, fonts, images, and runtime environment. For business or official documents, it is recommended to review the converted output.

Conclusion

Converting between OFD and PDF in Java can be done with a small amount of code. For OFD to PDF conversion, use the OfdConverter class and call toPdf(). For PDF to OFD conversion, load the PDF with PdfDocument and save it with FileFormat.OFD.

For single-file conversion, the basic examples are usually enough. For repeated tasks, you can use a simple batch conversion loop to process multiple OFD files in a folder.

How to Add, Modify, and Manage Excel Hyperlinks in Python

Leon Davis — Fri, 29 May 2026 11:52:51 +0000

Excel hyperlinks are not limited to opening web pages. They can also link to email addresses, external files, other worksheets, and even specific cells. In report directories, project document indexes, multi-sheet navigation, and interactive dashboards, hyperlinks help users locate information more efficiently.

When you only need to add a few links, doing it manually in Excel may be acceptable. However, when you need to add links to many cells, multiple worksheets, or automatically generated reports, using Python is more efficient and helps keep link paths and formats consistent.

This tutorial explains how to work with Excel hyperlinks in Python, including how to add web links, email links, external file links, internal workbook links, and image hyperlinks. It also covers how to modify and delete existing hyperlinks, and ends with a complete example that automatically creates a navigable Excel report index.

Environment Setup

Before writing code, prepare your Python environment and install a library for processing Excel files. This tutorial uses Spire.XLS for Python, which can create, read, edit, and save Excel files without requiring Microsoft Excel. It is suitable for report generation, document indexing, batch file processing, and office automation workflows.

1. Install the Python Library

Install Spire.XLS for Python with pip:

pip install spire.xls

After installation, import the commonly used classes in your Python script:

from spire.xls import Workbook, ExcelVersion, HyperLinkType

Where:

Workbook: Creates or loads an Excel workbook.
ExcelVersion: Specifies the Excel file format when saving.
HyperLinkType: Specifies the hyperlink type, such as URL, file, or workbook link.

If an example involves image hyperlinks, you will also use the worksheet Pictures collection. If you need to format cells, you can use the style-related APIs provided by Spire.XLS.

2. Prepare Test Files

Some examples in this tutorial create a new Excel file directly, so you can run the code and generate the result file. Other examples load an existing workbook using LoadFromFile(), for example:

workbook.LoadFromFile("template.xlsx")

This means the program reads template.xlsx from the current directory of the Python script. If the file is located elsewhere, use a full path:

workbook.LoadFromFile(r"C:\Users\Administrator\Desktop\template.xlsx")

Similarly, if the code uses external files or images, make sure these files exist and that the paths are correct. Otherwise, the generated hyperlink may not open the target file correctly, or the image may fail to insert into the worksheet.

3. Output File Format

Most examples in this tutorial save Excel files as follows:

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010)

ExcelVersion.Version2010 saves the file in .xlsx format, which is suitable for Excel 2010 and later. You can choose another Excel version parameter depending on your requirements.

After saving the workbook, it is recommended to call:

workbook.Dispose()

This releases the resources used by the workbook object, which is especially useful when processing multiple Excel files in batches.

1. Add Web and Email Hyperlinks

Web and email hyperlinks are the most basic and common types of Excel hyperlinks. They are often used to add official websites, reference pages, online documentation, or clickable email addresses to reports and contact lists.

In Spire.XLS, you can create a hyperlink for a specified cell by calling sheet.HyperLinks.Add(). After the hyperlink is created, set its display text, hyperlink type, and target address.

The following example adds a website link and an email link to two cells:

from spire.xls import Workbook, ExcelVersion, HyperLinkType

workbook = Workbook()
workbook.LoadFromFile("template.xlsx")
sheet = workbook.Worksheets[0]

# Add a web hyperlink
url_link = sheet.HyperLinks.Add(sheet.Range["D10"])
url_link.TextToDisplay = "View Wikipedia"
url_link.Type = HyperLinkType.Url
url_link.Address = "http://en.wikipedia.org/wiki/Chicago"

# Add an email hyperlink
mail_link = sheet.HyperLinks.Add(sheet.Range["E10"])
mail_link.TextToDisplay = "Contact Us"
mail_link.Type = HyperLinkType.Url
mail_link.Address = "mailto:support@example.com"

workbook.SaveToFile("hyperlinks.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Key points:

TextToDisplay: The text displayed in the cell.
Type: The hyperlink type. In this case, HyperLinkType.Url is used.
Address: The actual target address.
Email links should use the mailto: prefix, such as mailto:support@example.com.

Web links are suitable for online pages, while email links are useful in customer contact sheets, project contact lists, or internal support documents. Whether an email link opens correctly depends on whether the user has a default email client configured locally.

2. Link to External Files

In project documents, financial reports, or resource lists, you may need to link from an Excel file to external files such as PDF reports, Word documents, images, ZIP archives, or local instruction files. These links help users access related materials from a central Excel index.

For this type of hyperlink, set the type to HyperLinkType.File. The target address can be a relative path or an absolute path. In real projects, relative paths are usually preferred because they are less likely to break when the whole folder is moved.

from spire.xls import Workbook, ExcelVersion, HyperLinkType

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Create a hyperlink to an external file
file_link = sheet.HyperLinks.Add(sheet.Range["A1"])
file_link.Type = HyperLinkType.File
file_link.TextToDisplay = "View Detailed Report"
file_link.Address = "./reports/annual_report.pdf"

workbook.SaveToFile("file_link.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Use cases:

Project document indexes
Financial report attachments
Product manual directories
Review material lists
Local file archive navigation

Note:

The path can be relative or absolute. For example:

file_link.Address = r"C:\Reports\annual_report.pdf"

However, when files need to be packaged, shared, or moved, using relative paths is recommended. Keep the directory structure between the Excel file and the target files unchanged.

3. Link to Other Worksheets or Cells

Internal workbook hyperlinks are very useful when building multi-sheet reports. They allow users to jump from a summary sheet to a detail sheet, from an index sheet to a specific section, or from a dashboard to the source data area.

In Spire.XLS, internal links should use HyperLinkType.Workbook. The link address usually follows this format:

WorksheetName!CellAddress

For example:

Sheet2!C5

The example below shows how to link from the current worksheet to cell C5 in Sheet2:

from spire.xls import Workbook, ExcelVersion, HyperLinkType

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Link to cell C5 in Sheet2
internal_link = sheet.HyperLinks.Add(sheet.Range["A1"])
internal_link.Type = HyperLinkType.Workbook
internal_link.TextToDisplay = "Go to Detailed Data"
internal_link.Address = "Sheet2!C5"

workbook.SaveToFile("internal_link.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Practical applications:

Summary sheet to detail sheet navigation
Index sheet to section navigation
Dashboard to source data navigation
Review sheet to abnormal data location
Report homepage to different business modules

Format notes:

Internal link addresses are usually written as:

"WorksheetName!CellAddress"

For example:

"Sheet2!C5"
"Q4 Data!A1"
"Sales Details!D20"

If a worksheet name contains spaces or special characters, pay attention to Excel’s worksheet reference rules. To reduce naming and parsing issues, keep worksheet names simple and clear whenever possible.

4. Add Hyperlinks to Images

In addition to cells, images in Excel can also have hyperlinks. This is useful for creating clickable logos, icon buttons, help entries, or navigation images.

In Spire.XLS, you can first insert an image with sheet.Pictures.Add(), and then call SetHyperLink() on the picture object.

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert an image
picture = sheet.Pictures.Add(2, 1, "./logo.png")

# Add a hyperlink to the image
picture.SetHyperLink("https://www.example.com", True)

workbook.SaveToFile("image_link.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Use cases:

Company logo linking to the official website
Help icon linking to online documentation
QR code image linking to a mobile page
Icon button linking to a business system
Branding or explanation entry on a report cover sheet

In this example:

sheet.Pictures.Add(2, 1, "./logo.png") inserts the image near row 2, column 1.
The first parameter of SetHyperLink() is the hyperlink address.
The second parameter specifies whether it is an external link.

If the image file path is incorrect, the image cannot be inserted. Before running the code, make sure logo.png exists in the specified path.

5. Modify and Delete Hyperlinks

In real projects, hyperlinks are not always created once and left unchanged. For example, you may need to update website URLs, change file directories, or adjust report structures. Similarly, before publishing a final workbook, you may need to remove invalid or temporary links.

Spire.XLS stores worksheet hyperlinks in the sheet.HyperLinks collection. You can access existing hyperlinks by index and modify their display text or target address.

Modify Existing Hyperlinks

The following example loads an existing Excel file, gets all hyperlinks in the current worksheet, and modifies the display text and target address of the first hyperlink:

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("existing.xlsx")
sheet = workbook.Worksheets[0]

# Get all hyperlinks
links = sheet.HyperLinks

# Modify the first hyperlink
if links.Count > 0:
    links[0].TextToDisplay = "New Display Text"
    links[0].Address = "https://new-url.com"

workbook.SaveToFile("modified.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

This approach is suitable for:

Updating old links in batches
Replacing test environment URLs with production URLs
Fixing broken web links
Changing display text in reports
Standardizing file paths

Delete Hyperlinks

If some hyperlinks are no longer needed, you can remove a hyperlink at a specified index with RemoveAt(), or remove all hyperlinks in the current worksheet with Clear().

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("existing.xlsx")
sheet = workbook.Worksheets[0]

# Remove a specific hyperlink
sheet.HyperLinks.RemoveAt(0)

# Or remove all hyperlinks
# sheet.HyperLinks.Clear()

workbook.SaveToFile("cleaned.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Note:

Before deleting a hyperlink by index, make sure the index exists. For example, before using RemoveAt(0), it is better to check whether sheet.HyperLinks.Count is greater than 0. This helps avoid errors when the worksheet contains no hyperlinks.

6. Complete Example: Automatically Create a Report Index

The previous sections covered web links, email links, external file links, internal workbook links, image hyperlinks, and how to modify and delete hyperlinks. Now let’s apply these ideas to a multi-sheet report.

This example automatically creates an Excel report with an index sheet. Each item on the index sheet links to a corresponding worksheet, and each worksheet includes a “Back to Index” link. This makes it easier for users to navigate between sheets in a multi-sheet report.

from spire.xls import Workbook, ExcelVersion, HyperLinkType

def create_indexed_report():
    workbook = Workbook()

    # Create worksheets
    sheets = ["Index", "Sales Data", "Financial Summary", "Market Analysis"]
    for name in sheets:
        workbook.Worksheets.Add(name)

    # Create hyperlinks on the index sheet
    index_sheet = workbook.Worksheets[0]
    index_sheet.Range["A1"].Text = "Report Index"
    index_sheet.Range["A1"].Style.Font.IsBold = True
    index_sheet.Range["A1"].Style.Font.Size = 14

    row = 3
    for i, sheet_name in enumerate(sheets[1:], start=1):
        # Add hyperlink
        link = index_sheet.HyperLinks.Add(index_sheet.Range[row, 1])
        link.Type = HyperLinkType.Workbook
        link.TextToDisplay = f"{i}. {sheet_name}"
        link.Address = f"{sheet_name}!A1"

        # Add description text
        index_sheet.Range[row, 2].Text = "Click to view details"
        row += 1

    # Add back links on each worksheet
    for i in range(1, len(sheets)):
        sheet = workbook.Worksheets[i]
        back_link = sheet.HyperLinks.Add(sheet.Range["A1"])
        back_link.Type = HyperLinkType.Workbook
        back_link.TextToDisplay = "← Back to Index"
        back_link.Address = "Index!A1"

    workbook.SaveToFile("indexed_report.xlsx", ExcelVersion.Version2010)
    workbook.Dispose()
    print("Indexed report generated!")

create_indexed_report()

This example achieves the following results:

Generates multiple hyperlinks on the index sheet
Links each item to the corresponding worksheet
Adds a “Back to Index” link on each worksheet
Creates a complete navigation loop for the report

This structure is useful for multi-sheet files such as:

Monthly business reports
Financial analysis workbooks
Project progress summaries
Regional sales details
Data review workbooks
Multi-section Excel documents

If your report contains many worksheets, you can also generate the index based on the worksheet collection instead of maintaining the index manually.

Summary

Excel hyperlinks can be used for web access, email sending, external file references, internal workbook navigation, and clickable images. Automating these links with Python can improve the efficiency of multi-sheet reports, document indexes, and batch file processing.

Common hyperlink types include:

Type	Use Case	Key Property
`Url`	Web page or email	`Address = "http://..."` or `"mailto:..."`
`File`	External file	`Address = "./path/to/file.pdf"`
`Workbook`	Internal workbook navigation	`Address = "SheetName!Cell"`
Image hyperlink	Clickable icon or image	`picture.SetHyperLink(url, isExternal)`

Good use cases for hyperlinks:

Multi-sheet report navigation
Document or report indexes
Interactive dashboards
Attachment lists
Contact information and online resource entries

Cases where hyperlinks may not be the best choice:

If you only need to add explanatory notes, comments may be more suitable.
If you need complex interaction logic, VBA or a more complete automation script may be required.
If link targets change frequently, design a consistent path management strategy to reduce maintenance work.

Overall, hyperlinks are best used to create navigation relationships, not to store complex information. By choosing the right link type, keeping paths clear, and checking link validity regularly, you can make Excel files easier to navigate, share, and maintain.

How to Add, Edit & Delete Excel Comments in Python

Leon Davis — Fri, 29 May 2026 11:10:09 +0000

If you frequently work with Excel data, you have probably noticed the small red triangle in the top-right corner of a cell. That is a comment. Comments are especially useful in scenarios such as team collaboration, data review, and report annotations. However, manually adding comments is inefficient, particularly when batch processing is required.

In this guide, we will demonstrate how to work with Excel comments in Python, covering everything from simple text comments to advanced features like rich text formatting, background images, precise positioning, and automated review workflows.

Use Cases for Excel Comments

In real-world workflows, Excel comments are mainly used for the following purposes:

Data review: Mark abnormal data and explain calculation logic
Team collaboration: Leave suggestions, review notes, or discussion records
Template documentation: Explain field meanings and input rules
Audit trail: Record data sources and processing steps
Visual enhancement: Use image comments for branding or visual indicators

Understanding these use cases helps you decide when to use comments and when to use other approaches, such as a separate explanation sheet.

Environment Setup

Before writing code, prepare your Python environment and install a library for manipulating Excel files. This guide uses Spire.XLS for Python, which allows you to create, read, edit, and save Excel files without requiring Microsoft Excel. It is suitable for server-side reporting, batch data review, and office automation tasks.

1. Install the Python Library

Install Spire.XLS for Python via pip:

pip install spire.xls

Once installed, import the necessary classes in your Python script:

from spire.xls import Workbook, ExcelVersion

Where:

Workbook is used to create or load an Excel workbook.
ExcelVersion specifies the Excel file format when saving, such as .xlsx.
For advanced settings such as colors, text rotation, and alignment, you may also need to import classes such as Color, ExcelColors, and TextRotationType.

2. Prepare Test Files

If an example creates a new workbook, you can run the code directly to generate an Excel file. If the code uses LoadFromFile(), prepare an existing Excel file first, for example:

workbook.LoadFromFile("data.xlsx")

This means the program will read data.xlsx from the same directory as the Python script. If the file is located elsewhere, use a full file path:

workbook.LoadFromFile(r"C:\Users\Administrator\Desktop\data.xlsx")

3. Output File Format

Most examples in this guide save the result like this:

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013)

Here, ExcelVersion.Version2013 saves the file in the modern .xlsx format, which is suitable for Microsoft Excel 2013 and later. If you need compatibility with older Excel versions, choose another Excel version parameter based on your requirements.

1. Add Comments to Excel in Python

Adding comments is the basic operation for working with Excel comments. The usual workflow is to create or load a workbook, access the target worksheet and cell, and then use the AddComment() method to add a comment object to that cell.

After creating a comment, you can set its text, visibility, size, and rich text formatting.

1.1 Add a Simple Text Comment

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Add a comment to cell C1
cell = sheet.Range["C1"]
comment = cell.AddComment()
comment.Text = "This is a simple comment"

# By default, comments are hidden and appear on hover
# To keep the comment visible:
comment.Visible = True

workbook.SaveToFile("simple_comment.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

This is the simplest way to add a comment. In real projects, however, you usually need more control over the content and appearance.

1.2 Add a Comment with Author Information

In team collaboration or data review scenarios, a simple note may not be clear enough. To make comments easier to track, you can include the author, review time, or processing status in the comment text.

Spire.XLS does not require comments to contain a separate author field, so you can organize the content manually, such as using the format Author + newline + comment content.

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("data.xlsx")
sheet = workbook.Worksheets[0]

# Get the target cell
cell = sheet.Range["C1"]

# Set author and content
author = "John Doe"
text = "This data requires further verification"

comment = cell.AddComment()
comment.Width = 200
comment.Visible = True

# Format: author + newline + content
comment.Text = f"{author}:\n{text}"

# Make the author name bold
font = workbook.CreateFont()
font.FontName = "Tahoma"
font.IsBold = True
comment.RichText.SetFont(0, len(author), font)

workbook.SaveToFile("comment_with_author.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Why include the author? In a multi-person workflow, it makes it easy to identify who left each note.

2. Format Excel Comments with Rich Text in Python

Plain comments apply the same style to all text. But in some cases, you may need to emphasize key information or distinguish different parts of a comment. This is where rich text comments are useful.

When a comment is long, or when you need to highlight words such as “Warning”, “Reviewed”, or “Pending”, plain text may not be readable enough. You can use rich text formatting to apply different fonts, colors, font sizes, or bold styles to specific text ranges.

The core idea is to set the complete comment text first, and then use RichText.SetFont() to apply a font style to a specified character range.

2.1 Apply Multiple Text Colors in a Comment

The following example shows how to use multiple colors in one comment. In real development, this method is commonly used to distinguish note types, such as using a prominent color for “Attention” or “Warning” and a softer color for the detailed explanation.

from spire.xls import Workbook, ExcelVersion, ExcelColors

workbook = Workbook()
workbook.LoadFromFile("template.xlsx")
sheet = workbook.Worksheets[0]

# Create fonts with different colors
font_orange = workbook.CreateFont()
font_orange.FontName = "Arial"
font_orange.Size = 11
font_orange.KnownColor = ExcelColors.Orange

font_blue = workbook.CreateFont()
font_blue.KnownColor = ExcelColors.LightBlue

font_green = workbook.CreateFont()
font_green.KnownColor = ExcelColors.LightGreen

# Add a comment and set rich text
cell = sheet.Range["B12"]
comment = cell.Comment

comment.RichText.Text = "Attention: This data has been reviewed"

# Apply green to the first part, "Attention:"
comment.RichText.SetFont(0, 10, font_green)

# Apply blue to the remaining text
comment.RichText.SetFont(11, len(comment.RichText.Text) - 11, font_blue)

workbook.SaveToFile("rich_text_comment.xlsx", ExcelVersion.Version2007)
workbook.Dispose()

Expected effect:

Attention: appears in green as a warning or marker.
This data has been reviewed appears in blue as informational text.

2.2 Create a Comment with Complex Formatting

from spire.xls import Workbook, ExcelVersion, Color

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Create multiple font styles
font_title = workbook.CreateFont()
font_title.FontName = "Calibri"
font_title.Color = Color.get_Firebrick()  # Dark red
font_title.IsBold = True
font_title.Size = 12

font_body = workbook.CreateFont()
font_body.FontName = "Calibri"
font_body.Color = Color.get_Blue()
font_body.Size = 12

# Add a detailed comment
cell = sheet.Range["G5"]
comment = cell.Comment
comment.IsVisible = True
comment.Height = 150
comment.Width = 300

comment.RichText.Text = "Product Description:\nSpire.XLS is an independent Excel component that supports conversion, data processing, charts, and more."

# Apply bold red formatting to the title
comment.RichText.SetFont(0, 20, font_title)

# Apply blue formatting to the body
comment.RichText.SetFont(21, len(comment.RichText.Text) - 21, font_body)

workbook.SaveToFile("formatted_comment.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Applicable scenarios: Technical documentation, product descriptions, and comments that need a clear information hierarchy.

3. Read and Edit Existing Excel Comments in Python

Besides creating comments, you may also need to read and update existing comments. For example, after a review process, you may need to extract all comment content and generate a review report. Or you may need to update comments in bulk according to new business rules.

To read a comment, access the Comment property of a cell. To edit a comment, modify its Text or RichText content directly.

3.1 Read Comment Content

from spire.xls import Workbook

workbook = Workbook()
workbook.LoadFromFile("with_comments.xlsx")
sheet = workbook.Worksheets[0]

# Read the comment from cell A1
comment_text = sheet.Range["A1"].Comment.Text
print(f"Comment content: {comment_text}")

# If the comment contains rich text, get the RTF content
rich_text = sheet.Range["A2"].Comment.RichText.RtfText
print(f"Rich text format: {rich_text}")

workbook.Dispose()

Use cases:

Extract all comments to generate a review report
Analyze keywords in comments
Export comments to a database

3.2 Read All Comments in Bulk

from spire.xls import Workbook

workbook = Workbook()
workbook.LoadFromFile("reviewed_data.xlsx")
sheet = workbook.Worksheets[0]

# Iterate through all cells that may contain comments
comments_info = []

for row in range(1, sheet.AllocatedRange.RowCount + 1):
    for col in range(1, sheet.AllocatedRange.ColumnCount + 1):
        cell = sheet.Range[row, col]

        if cell.Comment is not None:
            info = {
                'cell': f"{chr(64+col)}{row}",  # For example, "A1"
                'author': cell.Comment.Text.split(':')[0] if ':' in cell.Comment.Text else '',
                'content': cell.Comment.Text,
                'visible': cell.Comment.Visible
            }
            comments_info.append(info)

# Output all comment information
for info in comments_info:
    print(f"Cell {info['cell']}: {info['content']}")

workbook.Dispose()

3.3 Edit Existing Comments

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("existing_comments.xlsx")
sheet = workbook.Worksheets[0]

# Method 1: Directly modify the first comment
comment = sheet.Comments[0]
comment.Text = "Comment content updated - 2024 review"

# Method 2: Modify the comment in a specific cell
sheet.Range["B5"].Comment.Text = "Data has been corrected"

workbook.SaveToFile("updated_comments.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

4. Delete Excel Comments in Python

Comments are often used for review, explanation, or temporary communication. When a file is ready for final release, archiving, or external sharing, you may need to delete some or all comments to avoid distracting readers or exposing internal review information.

You can delete comments through the sheet.Comments collection, or locate a specific cell first and then remove its comment.

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("with_comments.xlsx")
sheet = workbook.Worksheets[0]

# Method 1: Delete a specific comment
comments = sheet.Comments
if comments.Count > 0:
    comments[1].Remove()  # Delete the second comment

# Method 2: Delete all comments
while sheet.Comments.Count > 0:
    sheet.Comments[0].Remove()

workbook.SaveToFile("no_comments.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Practical scenarios:

Remove review comments before publishing the final version
Delete temporary notes before archiving
Delete comments based on conditions, such as keeping only comments from the last month

5. Customize Excel Comments in Python

Basic comments solve the problem of adding explanations. Advanced customization focuses on whether comments are readable, layout-friendly, and suitable for report presentation. In formal reports, template files, or automatically generated review documents, you may need to control comment size, position, visibility, background images, and text direction.

These settings allow comments to become part of the reporting and review workflow rather than simple notes.

5.1 Control Comment Position and Size

The default comment position may not always be ideal. You can control it precisely:

from spire.xls import Workbook, ExcelVersion, CommentVAlignType, CommentHAlignType

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Add a comment
cell = sheet.Range["G5"]
cell.Text = "View comment"
comment = cell.Comment

comment.IsVisible = True
comment.Height = 150  # Height
comment.Width = 300   # Width

# Set absolute position relative to the top-left corner of the worksheet
comment.Top = 20   # 20 pixels from the top
comment.Left = 40  # 40 pixels from the left

# Set text alignment
comment.VAlignment = CommentVAlignType.Center
comment.HAlignment = CommentHAlignType.Justified

comment.RichText.Text = "This is a precisely positioned comment.\nYou can control its size, position, and alignment."

workbook.SaveToFile("positioned_comment.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Why control the position?

Avoid covering important data
Arrange multiple comments more cleanly
Make comment placement more predictable when printing

5.2 Set a Background Image for a Comment

In addition to text formatting, you can further customize the appearance of comments. For example, in corporate templates or branded reports, you can set an image as the comment background to display a logo, icon, or other visual element.

To do this, load the image as a file stream and apply it to the comment using comment.Fill.CustomPicture().

from spire.xls import Workbook, ExcelVersion
from System.IO import FileStream, FileMode

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Add text to the cell
cell = sheet.Range["C6"]
cell.Text = "Company Logo"

# Add a comment
comment = cell.AddComment()

# Load an image as the background
image_stream = FileStream("logo.png", FileMode.Open)
comment.Fill.CustomPicture(image_stream, "logo.png")

comment.Visible = True

workbook.SaveToFile("comment_with_image.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Use cases:

Add a company logo to enterprise templates
Use icons to distinguish different comment types
Build branded reporting systems

5.3 Rotate Comment Text

from spire.xls import Workbook, ExcelVersion, TextRotationType

workbook = Workbook()
sheet = workbook.Worksheets[0]

cell = sheet.Range["D10"]
comment = cell.AddComment()

comment.Text = "This text can be rotated"
comment.TextRotation = TextRotationType.LeftToRight

# Other options:
# TextRotationType.RightToLeft
# TextRotationType.TopToBottom

comment.Visible = True

workbook.SaveToFile("rotated_comment.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Applicable scenarios: Special layout requirements, multilingual documents, and vertical text display.

5.4 Show or Hide Comments

from spire.xls import Workbook, ExcelVersion

workbook = Workbook()
workbook.LoadFromFile("with_comments.xlsx")
sheet = workbook.Worksheets[0]

# Show all comments
for comment in sheet.Comments:
    comment.Visible = True

# Or show/hide specific comments
sheet.Range["A1"].Comment.Visible = True
sheet.Range["B2"].Comment.Visible = False  # Hide this comment

workbook.SaveToFile("visibility_control.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Practical applications:

Review mode: Show all comments
Final version: Hide comments without deleting them, preserving history
Before printing: Hide comments to avoid visual clutter

6. Practical Example: Build an Automated Excel Review System

The previous examples covered adding, reading, editing, deleting, and styling comments. Now let us combine these operations in a complete example that simulates a simple data review workflow.

The program reads sales data from a CSV file, writes it to Excel, automatically detects abnormal values based on business rules, and adds comments and background colors to the corresponding cells.

This example is closer to a real-world project and is suitable for report review, data quality checks, and abnormal data marking.

from spire.xls import Workbook, ExcelVersion, Color
import csv

def create_review_report(csv_file: str, output_file: str):
    """
    Create an Excel report from a CSV file and automatically add review comments.
    """

    workbook = Workbook()
    sheet = workbook.Worksheets[0]
    sheet.Name = "Sales Data Review"

    # Create font styles
    font_warning = workbook.CreateFont()
    font_warning.Color = Color.get_Red()
    font_warning.IsBold = True

    font_info = workbook.CreateFont()
    font_info.Color = Color.get_Blue()

    # Read CSV data
    with open(csv_file, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)
        headers = next(reader)

        # Write headers
        for col, header in enumerate(headers, 1):
            sheet.Range[1, col].Value = header

        # Write data and check for anomalies
        row_num = 2
        for row in reader:
            for col, value in enumerate(row, 1):
                sheet.Range[row_num, col].Value = value

            # Assume the 3rd column is sales amount and check abnormal values
            try:
                sales = float(row[2])

                # If sales amount exceeds 10000, add a warning comment
                if sales > 10000:
                    cell = sheet.Range[row_num, 3]
                    comment = cell.AddComment()

                    comment.Text = f"Warning: Sales amount {sales:.2f} exceeds the normal range\nPlease verify data accuracy"
                    comment.Width = 250
                    comment.Visible = True

                    # Apply warning style
                    comment.RichText.SetFont(0, 7, font_warning)
                    comment.RichText.SetFont(8, len(comment.Text), font_info)

                    # Highlight the cell
                    cell.Style.Color = Color.get_LightYellow()

            except (ValueError, IndexError):
                pass

            row_num += 1

    # Auto-fit columns
    sheet.AllocatedRange.AutoFitColumns()

    # Add an instruction comment
    title_cell = sheet.Range["A1"]
    title_comment = title_cell.AddComment()
    title_comment.Text = "Review Notes:\n- Red comments: abnormal data to verify\n- Yellow background: marked high values\n- Remove these comments after review"
    title_comment.Width = 300
    title_comment.Height = 150
    title_comment.Visible = True

    workbook.SaveToFile(output_file, ExcelVersion.Version2013)
    workbook.Dispose()

    print(f"Review report generated: {output_file}")

# Usage example
create_review_report("sales_data.csv", "sales_review.xlsx")

This example demonstrates:

Automatic detection: Identify anomalies based on business rules
Smart comments: Provide different messages for different situations
Visual cues: Use both colors and comments to mark important cells
Traceability: Keep review records in the workbook

7. Practical Tips for Working with Excel Comments

1. When Should You Use Comments?

Use comments for:

Temporary review suggestions
Cell-level explanations
Notes that need to be shown or hidden

Avoid comments for:

Long explanations, which are better placed in a separate worksheet
Permanent important information, which should be placed in cells, headers, or footers
Structured data, which should be stored in additional columns

2. Performance Considerations

A large number of comments, such as hundreds or more, may slow down file opening.
Rich text comments take more space than plain text comments.
Comments with background images can significantly increase file size.

Suggestions:

Clean up unnecessary comments regularly.
Remove review comments before publishing the final version.
Use a separate worksheet for large amounts of explanatory content.

3. Best Practices for Excel Comments

✅ Do:

Keep comments concise and clear.
Include the author and timestamp.
Make important comments visible.
Review and clean up comments regularly.

❌ Do not:

Store important data in comments.
Write overly long comments. If a comment exceeds around 100 words, consider another approach.
Forget to remove temporary comments.
Include sensitive information in comments, as comments are easy to overlook and may lead to accidental disclosure.

4. Comments vs. Other Solutions

Requirement	Recommended Solution	Reason
Cell-level explanation	Comment	Does not occupy cell space
Long documentation	Separate worksheet	Easier to read and manage
Data validation prompt	Data validation	Provides instant input guidance
Formula explanation	Named ranges + description	More structured
Version history	Separate history worksheet	Easier to track

8. Frequently Asked Questions

Q1: How can I export all comments in bulk?

A: Iterate through all cells, extract comment content, and save it to a text file or database:

comments_export = []
for row in range(1, max_row + 1):
    for col in range(1, max_col + 1):
        cell = sheet.Range[row, col]
        if cell.Comment:
            comments_export.append({
                'location': f"{chr(64+col)}{row}",
                'text': cell.Comment.Text
            })

# Save to CSV or database

Q2: Do comments affect printing?

A: By default, comments are not printed. If you need to print them:

In Excel: Go to Page Layout > Print Titles and set comments to At end of sheet.
In code: Configure page setup options depending on the library version and available APIs.

Q3: Can comments be protected from editing?

A: You can protect the worksheet, but this also locks cells. A better approach is to:

Remove comments after the review is complete.
Or copy comment content to a protected worksheet.

Conclusion

Comments are an underrated Excel feature. Used properly, they can greatly improve collaboration efficiency and data quality. Through the examples in this article, you should now understand how to:

Perform basic operations: Add, read, edit, and delete comments
Apply rich text formatting: Use multiple colors, fonts, and hierarchical formatting
Customize comments: Control position, add background images, and rotate text
Build automation workflows: Process comments in batches, mark data intelligently, and create review systems
Follow best practices: Know when to use comments, how to use them, and what to avoid

Core principle: Comments should help readers understand data, not store data. Keep comments concise, relevant, and regularly cleaned up so they can provide real value.

I hope this guide helps with your Excel automation work.

Building Interactive Excel Dashboards in Java: A Practical Guide to Slicers

Leon Davis — Fri, 22 May 2026 13:59:03 +0000

If you've ever built data analysis reports, you've probably encountered this scenario: your Excel sheet is packed with data, and your manager wants to see figures for a specific region or month. You end up spending ages manually filtering through dropdowns. Wouldn't it be great if you could just click a button to switch views? That's exactly what Slicers solve.

Slicers, introduced in Excel 2010, are essentially visual filter panels. Today, I'll share how to add slicers to Excel files using Java code, transforming your reports from "static spreadsheets" into "interactive dashboards."

What Exactly is a Slicer?

In simple terms, a slicer is a floating panel of filter buttons. Imagine you have a sales dataset with fields like Product, Region, and Time. Traditional filtering uses dropdown menus, but slicers are more intuitive:

Click the "East Region" button → shows only East Region data
Then click "2024" → further filters to East Region in 2024
Want to deselect? Just click again

Compared to traditional auto-filtering, slicers offer several advantages:

Visual clarity: All options are visible at once—no need to open dropdowns
Easy multi-select: Hold Ctrl to select multiple criteria
Visual feedback: Selected items are highlighted
Linked controls: Multiple slicers can control the same data source

Enough theory—let's dive into the code.

Environment Setup

Add this to your pom.xml:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>https://repo.e-iceblue.com/repository/maven-public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.12.0</version>
    </dependency>
</dependencies>

Note: The free version has limitations (up to 5 sheets, 200 rows per sheet). For production use or larger files, consider the commercial license. For learning and small projects, the free version works fine.

1. Creating Slicers from Regular Tables

The most basic usage: start with a data table, then create a slicer based on it.

import com.spire.xls.*;
import com.spire.xls.core.IListObject;
import com.spire.xls.core.spreadsheet.slicer.*;

public class CreateSlicerFromTable {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Prepare sample data
        worksheet.getCellRange("A1").setValue("Fruit");
        worksheet.getCellRange("A2").setValue("Grape");
        worksheet.getCellRange("A3").setValue("Blueberry");
        worksheet.getCellRange("A4").setValue("Kiwi");
        worksheet.getCellRange("A5").setValue("Cherry");

        worksheet.getCellRange("B1").setValue("Year");
        worksheet.getCellRange("B2").setValue2(2020);
        worksheet.getCellRange("B3").setValue2(2020);
        worksheet.getCellRange("B4").setValue2(2021);
        worksheet.getCellRange("B5").setValue2(2021);

        worksheet.getCellRange("C1").setValue("Sales");
        worksheet.getCellRange("C2").setValue2(50);
        worksheet.getCellRange("C3").setValue2(60);
        worksheet.getCellRange("C4").setValue2(70);
        worksheet.getCellRange("C5").setValue2(80);

        // Create a table object
        IListObject table = worksheet.getListObjects()
            .create("SalesTable", worksheet.getCellRange("A1:C5"));

        // Get the slicer collection
        XlsSlicerCollection slicers = worksheet.getSlicers();

        // Create a slicer based on the "Fruit" column (column index 0)
        int index = slicers.add(table, "E2", 0);

        // Get the slicer object and set its style
        XlsSlicer xlsSlicer = slicers.get(index);
        xlsSlicer.setName("FruitSlicer");
        xlsSlicer.setStyleType(SlicerStyleType.SlicerStyleLight2);

        wb.saveToFile("table_slicer.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

After running this, open the Excel file and you'll see a floating panel on the right with buttons like "Grape," "Blueberry," etc. Click any button to filter instantly—it's super intuitive.

2. Creating Slicers from Pivot Tables (More Common)

In real-world scenarios, slicers are more commonly used with pivot tables. Pivot tables handle aggregation, while slicers handle filtering—a perfect combination.

import com.spire.xls.*;
import com.spire.xls.collections.PivotTablesCollection;
import com.spire.xls.core.IPivotField;
import com.spire.xls.core.spreadsheet.slicer.*;

public class CreateSlicerFromPivotTable {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        Worksheet worksheet = wb.getWorksheets().get(0);

        // Prepare data (similar to above)
        worksheet.getCellRange("A1").setValue("Fruit");
        worksheet.getCellRange("A2").setValue("Grape");
        worksheet.getCellRange("A3").setValue("Blueberry");
        worksheet.getCellRange("A4").setValue("Kiwi");
        worksheet.getCellRange("A5").setValue("Cherry");
        worksheet.getCellRange("A6").setValue("Grape");
        worksheet.getCellRange("A7").setValue("Blueberry");

        worksheet.getCellRange("B1").setValue("Year");
        worksheet.getCellRange("B2").setValue2(2020);
        worksheet.getCellRange("B3").setValue2(2020);
        worksheet.getCellRange("B4").setValue2(2020);
        worksheet.getCellRange("B5").setValue2(2021);
        worksheet.getCellRange("B6").setValue2(2021);
        worksheet.getCellRange("B7").setValue2(2021);

        worksheet.getCellRange("C1").setValue("Sales");
        worksheet.getCellRange("C2").setValue2(50);
        worksheet.getCellRange("C3").setValue2(60);
        worksheet.getCellRange("C4").setValue2(70);
        worksheet.getCellRange("C5").setValue2(80);
        worksheet.getCellRange("C6").setValue2(90);
        worksheet.getCellRange("C7").setValue2(100);

        // Create pivot table cache
        CellRange dataRange = worksheet.getCellRange("A1:C7");
        PivotCache cache = wb.getPivotCaches().add(dataRange);

        // Create pivot table at position A12
        PivotTable pt = worksheet.getPivotTables()
            .add("SalesPivot", worksheet.getCellRange("A12"), cache);

        // Configure pivot table fields
        PivotField pf = (PivotField)pt.getPivotFields().get("Fruit");
        pf.setAxis(AxisTypes.Row);  // Row area

        PivotField pf2 = (PivotField)pt.getPivotFields().get("Year");
        pf2.setAxis(AxisTypes.Column);  // Column area

        // Add data field (sum)
        pt.getDataFields().add(
            pt.getPivotFields().get("Sales"), 
            "Total Sales", 
            SubtotalTypes.Sum
        );

        // Set pivot table style
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium10);
        pt.calculateData();

        // Create first slicer (based on "Fruit" field)
        XlsSlicerCollection slicers = worksheet.getSlicers();
        int index = slicers.add(pt, "E12", 0);

        XlsSlicer fruitSlicer = slicers.get(index);
        fruitSlicer.setName("FruitFilter");
        fruitSlicer.setWidth(150);
        fruitSlicer.setHeight(120);
        fruitSlicer.setStyleType(SlicerStyleType.SlicerStyleLight2);

        // Lock position to prevent accidental dragging
        fruitSlicer.isPositionLocked(true);

        // Create second slicer (based on "Year" field)
        IPivotField yearField = pt.getPivotFields().get("Year");
        int index2 = slicers.add(pt, "I12", yearField);

        XlsSlicer yearSlicer = slicers.get(index2);
        yearSlicer.setName("YearFilter");
        yearSlicer.setStyleType(SlicerStyleType.SlicerStyleLight3);
        yearSlicer.setRowHeight(35);

        wb.saveToFile("pivot_slicer.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

This example creates two slicers: one for filtering fruits and another for years. Both slicers work together to control the same pivot table, providing an excellent user experience.

3. Customizing Slicer Appearance

The default slicer style might not match your UI design. Here's how to customize it:

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.slicer.*;

public class CustomizeSlicer {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        wb.loadFromFile("existing_with_slicer.xlsx");

        Worksheet worksheet = wb.getWorksheets().get(0);
        XlsSlicerCollection slicers = worksheet.getSlicers();

        // Get the first slicer
        XlsSlicer slicer = slicers.get(0);

        // Modify caption
        slicer.setCaption("Product Filter");

        // Set dimensions
        slicer.setWidth(200);
        slicer.setHeight(150);

        // Set number of columns (buttons per row)
        slicer.setNumberOfColumns(2);

        // Set button height
        slicer.setRowHeight(40);

        // Choose dark theme style
        slicer.setStyleType(SlicerStyleType.SlicerStyleDark4);

        // Show or hide caption
        slicer.isShowCaption(true);

        wb.saveToFile("customized_slicer.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Available style types:

SlicerStyleLight1 ~ SlicerStyleLight6: Light themes
SlicerStyleMedium1 ~ SlicerStyleMedium2: Medium contrast
SlicerStyleDark1 ~ SlicerStyleDark6: Dark themes

Choose the color scheme that matches your UI design.

4. Programmatically Controlling Filter State

Sometimes you need to preset filter conditions—for example, showing only data from a specific region by default:

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.slicer.*;

public class ControlSlicerSelection {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        wb.loadFromFile("with_slicer.xlsx");

        Worksheet worksheet = wb.getWorksheets().get(0);
        XlsSlicerCollection slicers = worksheet.getSlicers();
        XlsSlicer slicer = slicers.get(0);

        // Get the slicer's cache item collection
        XlsSlicerCacheItemCollection items = 
            slicer.getSlicerCache().getSlicerCacheItems();

        // Iterate through all items, select only "East Region"
        for (int i = 0; i < items.getCount(); i++) {
            XlsSlicerCacheItem item = items.get(i);
            String value = item.getDisplayValue();

            if ("East Region".equals(value)) {
                item.isSelected(true);   // Select
            } else {
                item.isSelected(false);  // Deselect
            }
        }

        // Set cross-filter type
        XlsSlicerCache cache = slicer.getSlicerCache();

        // Option 1: Show all items (including those with no data)
        cache.setCrossFilterType(SlicerCacheCrossFilterType.ShowItemsWithNoData);

        // Option 2: Items with data appear first
        // cache.setCrossFilterType(SlicerCacheCrossFilterType.ShowItemsWithDataAtTop);

        wb.saveToFile("filtered_slicer.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Use cases: Default to current month's data when opening reports, preset filters based on user permissions, etc.

5. Reading Slicer Information

If you need to audit or document slicer configurations, you can read their properties:

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.slicer.*;

public class ReadSlicerInfo {
    public static void main(String[] args) throws Exception {
        Workbook wb = new Workbook();
        wb.loadFromFile("with_slicers.xlsx");

        Worksheet worksheet = wb.getWorksheets().get(0);
        XlsSlicerCollection slicers = worksheet.getSlicers();

        System.out.println("Number of slicers: " + slicers.getCount());

        for (int i = 0; i < slicers.getCount(); i++) {
            XlsSlicer slicer = slicers.get(i);

            System.out.println("\n=== Slicer " + (i+1) + " ===");
            System.out.println("Name: " + slicer.getName());
            System.out.println("Caption: " + slicer.getCaption());
            System.out.println("Width: " + slicer.getWidth());
            System.out.println("Height: " + slicer.getHeight());
            System.out.println("Columns: " + slicer.getNumberOfColumns());
            System.out.println("Row Height: " + slicer.getRowHeight());
            System.out.println("Show Caption: " + slicer.isShowCaption());
            System.out.println("Position Locked: " + slicer.isPositionLocked());

            // Read associated data source
            XlsSlicerCache cache = slicer.getSlicerCache();
            System.out.println("Data Source: " + cache.getSourceName());

            // Read current selection state
            XlsSlicerCacheItemCollection items = cache.getSlicerCacheItems();
            System.out.print("Selected Items: ");
            for (int j = 0; j < items.getCount(); j++) {
                if (items.get(j).isSelected()) {
                    System.out.print(items.get(j).getDisplayValue() + " ");
                }
            }
            System.out.println();
        }

        wb.dispose();
    }
}

This is useful for report template validation, automated testing, or documentation purposes.

6. Removing Slicers

When slicers are no longer needed, you can remove them:

import com.spire.xls.*;
import com.spire.xls.core.spreadsheet.slicer.*;

public class RemoveSlicer {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        wb.loadFromFile("with_slicers.xlsx");

        Worksheet worksheet = wb.getWorksheets().get(0);
        XlsSlicerCollection slicers = worksheet.getSlicers();

        // Method 1: Remove slicer at specific position
        if (slicers.getCount() > 0) {
            slicers.removeAt(0);  // Remove the first one
        }

        // Method 2: Clear all slicers
        // slicers.clear();

        wb.saveToFile("without_slicers.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

7. Real-World Example: Sales Analysis Dashboard

Let's put everything together with a complete example—creating a sales analysis dashboard with multiple slicers:

import com.spire.xls.*;
import com.spire.xls.core.IPivotField;
import com.spire.xls.core.spreadsheet.slicer.*;
import java.util.*;

public class SalesDashboard {

    static class SaleRecord {
        String region;
        String product;
        String month;
        double amount;

        SaleRecord(String region, String product, String month, double amount) {
            this.region = region;
            this.product = product;
            this.month = month;
            this.amount = amount;
        }
    }

    public static void main(String[] args) {
        Workbook wb = new Workbook();
        Worksheet sheet = wb.getWorksheets().get(0);

        // 1. Prepare sales data
        List<SaleRecord> records = Arrays.asList(
            new SaleRecord("East", "Product A", "Jan", 10000),
            new SaleRecord("East", "Product B", "Jan", 15000),
            new SaleRecord("North", "Product A", "Jan", 8000),
            new SaleRecord("North", "Product B", "Feb", 12000),
            new SaleRecord("South", "Product A", "Feb", 9000),
            new SaleRecord("South", "Product B", "Feb", 11000),
            new SaleRecord("East", "Product A", "Mar", 13000),
            new SaleRecord("North", "Product B", "Mar", 14000)
        );

        // Write headers
        String[] headers = {"Region", "Product", "Month", "Revenue"};
        for (int i = 0; i < headers.length; i++) {
            sheet.getCellRange(1, i + 1).setValue(headers[i]);
        }

        // Write data
        for (int i = 0; i < records.size(); i++) {
            SaleRecord r = records.get(i);
            int row = i + 2;
            sheet.getCellRange(row, 1).setValue(r.region);
            sheet.getCellRange(row, 2).setValue(r.product);
            sheet.getCellRange(row, 3).setValue(r.month);
            sheet.getCellRange(row, 4).setNumberValue(r.amount);
        }

        // 2. Create pivot table
        CellRange dataRange = sheet.getCellRange("A1:D" + (records.size() + 1));
        PivotCache cache = wb.getPivotCaches().add(dataRange);
        PivotTable pt = sheet.getPivotTables()
            .add("SalesSummary", sheet.getCellRange("G2"), cache);

        // Configure pivot table
        ((PivotField)pt.getPivotFields().get("Region")).setAxis(AxisTypes.Row);
        ((PivotField)pt.getPivotFields().get("Product")).setAxis(AxisTypes.Column);
        pt.getDataFields().add(
            pt.getPivotFields().get("Revenue"),
            "Total",
            SubtotalTypes.Sum
        );
        pt.setBuiltInStyle(PivotBuiltInStyles.PivotStyleMedium12);
        pt.calculateData();

        // 3. Create three slicers
        XlsSlicerCollection slicers = sheet.getSlicers();

        // Region slicer
        int idx1 = slicers.add(pt, "G15", 0);
        XlsSlicer regionSlicer = slicers.get(idx1);
        regionSlicer.setCaption("Region Filter");
        regionSlicer.setStyleType(SlicerStyleType.SlicerStyleLight4);
        regionSlicer.setWidth(120);
        regionSlicer.isPositionLocked(true);

        // Product slicer
        IPivotField productField = pt.getPivotFields().get("Product");
        int idx2 = slicers.add(pt, "K15", productField);
        XlsSlicer productSlicer = slicers.get(idx2);
        productSlicer.setCaption("Product Filter");
        productSlicer.setStyleType(SlicerStyleType.SlicerStyleLight5);
        productSlicer.setWidth(120);

        // Month slicer
        IPivotField monthField = pt.getPivotFields().get("Month");
        int idx3 = slicers.add(pt, "O15", monthField);
        XlsSlicer monthSlicer = slicers.get(idx3);
        monthSlicer.setCaption("Month Filter");
        monthSlicer.setStyleType(SlicerStyleType.SlicerStyleLight6);
        monthSlicer.setWidth(100);

        // 4. Beautify the worksheet
        sheet.setName("Sales Dashboard");
        sheet.setDefaultColumnWidth(15);

        wb.saveToFile("sales_dashboard.xlsx", ExcelVersion.Version2013);
        wb.dispose();

        System.out.println("Dashboard generated successfully!");
        System.out.println("Open the file and click slicer buttons to interactively filter data.");
    }
}

This example creates a complete sales analysis dashboard:

Left side: Raw data
Center: Pivot table summary
Right side: Three slicers (Region, Product, Month)

Users can freely combine filter criteria—for example, viewing "Product A sales in the East region for January"—with just a few clicks, no manual filtering required.

Practical Tips

1. Where to Place Slicers?

Dashboard scenarios: Place next to or above the pivot table for easy access
Report scenarios: Position at the top as global filters
Multi-sheet scenarios: If multiple sheets share the same data source, consider placing slicers on a separate "Control Panel" sheet

2. Performance Considerations

With large datasets (tens of thousands of rows), slicer response time may slow down
Best practice: Aggregate data with pivot tables first, then create slicers based on them
Avoid placing too many slicers in one worksheet (more than 5 can impact user experience)

3. UX Optimization

Lock positions: Use isPositionLocked(true) to prevent accidental dragging
Reasonable sizing: Make buttons large enough for easy clicking
Clear captions: Use setCaption() to set understandable names
Preset filters: Based on common query patterns, pre-select certain filter options

4. Integration with Charts

Slicers aren't limited to controlling pivot tables—they can also control charts. If your report includes bar charts or line graphs, you can use slicers to dynamically switch the displayed data series.

Summary

Slicers are an excellent tool for bringing Excel reports to life. Through the examples in this article, you should now understand:

Creating slicers from tables: Suitable for simple data filtering
Creating slicers from pivot tables: The most common scenario, paired with aggregation analysis
Customizing appearance: Adjusting styles, dimensions, and layout
Programmatic control: Presetting conditions and dynamic updates
Reading and removing: Managing and maintaining slicers
Real-world dashboard: Comprehensive application for creating interactive reports

Compared to traditional auto-filtering, slicers offer a much better interactive experience. If your reports require frequent view switching or multi-dimensional analysis, I strongly recommend adding slicer functionality.

Hope this guide helps you build more engaging Excel reports! Feel free to share your own tips and experiences in the comments.

Note: Examples in this article use Spire.XLS for Java. Apache POI currently has limited support for slicers. If you need this functionality, consider specialized Excel processing libraries. Choose tools based on your project requirements.

How to Work with Excel Rows and Columns in Java

Leon Davis — Fri, 22 May 2026 13:34:57 +0000

In daily development, scenarios such as report exporting, batch data formatting, and template modifications often require dynamically inserting or deleting rows and columns in Excel spreadsheets. This article will introduce how to manipulate rows and columns in Excel files using Java, including adding, deleting, grouping, and copying rows and columns, in both .xls and .xlsx formats, with complete runnable examples included.

Environment Setup

This article uses Spire.XLS for Java, a specialized library for Excel file manipulation in Java. You can install it from Maven by adding the following command in your pom.xml file:

Maven Dependency

Add this to your pom.xml:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>https://repo.e-iceblue.com/repository/maven-public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.12.0</version>
    </dependency>
</dependencies>

1. Inserting Rows and Columns

The most basic operation is inserting rows and columns. Here's how to insert a single row at position 2:

import com.spire.xls.*;

public class InsertRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Insert a row at position 2
        worksheet.insertRow(2);

        // Insert a column at position 2
        worksheet.insertColumn(2);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Batch insertion is just as easy—use the second parameter to specify how many to insert:

// Insert 2 rows starting from row 5
worksheet.insertRow(5, 2);

// Insert 2 columns starting from column 5
worksheet.insertColumn(5, 2);

Real-world use case: I often use this when generating reports dynamically. For example, if I have 10 records, I insert 10 rows after the header.

2. Deleting Rows and Columns

Deleting works similarly to inserting, but remember: deleting shifts subsequent rows/columns forward.

import com.spire.xls.*;

public class DeleteRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Delete 4 rows starting from row 5 (deletes rows 5, 6, 7, 8)
        worksheet.deleteRow(5, 4);

        // Delete 2 columns starting from column 2 (deletes columns 2, 3)
        worksheet.deleteColumn(2, 2);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Common pitfall: When deleting multiple rows in a loop, always iterate backwards, otherwise you'll skip rows or delete the wrong ones due to index shifting.

3. Deleting Blank Rows and Columns

This is super useful! Ever received an Excel export with tons of blank rows? Manually deleting them is a pain.

import com.spire.xls.*;

public class DeleteBlankRows {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("messy_data.xlsx");

        Worksheet sheet = workbook.getWorksheets().get(0);

        // Delete blank rows (iterate backwards)
        for (int i = sheet.getRows().length - 1; i >= 0; i--) {
            if (sheet.getRows()[i].isBlank()) {
                sheet.deleteRow(i + 1);  // Note: row numbers start from 1
            }
        }

        // Delete blank columns (iterate backwards)
        for (int j = sheet.getColumns().length - 1; j >= 0; j--) {
            if (sheet.getColumns()[j].isBlank()) {
                sheet.deleteColumn(j + 1);  // Note: column numbers start from 1
            }
        }

        workbook.saveToFile("cleaned.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Why iterate backwards? Because when you delete a row, all subsequent row numbers shift. If you iterate forward, you might skip rows or delete the wrong ones.

Use cases: Cleaning up database exports, removing placeholder rows from templates, etc.

4. Copying Rows and Columns

Sometimes you need to copy rows within the same sheet or across different sheets.

Copying Within the Same Sheet

import com.spire.xls.*;

public class CopyRows {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet sheet = workbook.getWorksheets().get(0);

        // Copy row 1 to row 3
        // Parameters: source row, target row, copy values, copy formatting, copy formulas
        sheet.copy(sheet.getRows()[0], sheet.getRows()[2], true, true, true);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2010);
        workbook.dispose();
    }
}

Copying Across Sheets

Worksheet sheet1 = workbook.getWorksheets().get(0);
Worksheet sheet2 = workbook.getWorksheets().get(1);

// Copy row 1 from Sheet1 to row 2 in Sheet2
sheet1.copy(sheet1.getRows()[0], sheet2.getRows()[1], true, true, true);

The three boolean parameters:

First: whether to copy cell values
Second: whether to copy formatting (fonts, colors, etc.)
Third: whether to copy formulas

Practical use: I frequently use this to copy headers to multiple sheets or generate data rows from a template row.

5. Hiding and Showing Rows/Columns

Sometimes you want to hide sensitive data without deleting it:

import com.spire.xls.*;

public class HideRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Hide column 2
        worksheet.hideColumn(2);

        // Hide row 4
        worksheet.hideRow(4);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

To unhide:

// Show column 2
worksheet.showColumn(2);

// Show row 4
worksheet.showRow(4);

Use cases: Hiding intermediate calculations in financial reports, protecting sensitive information, etc.

6. Setting Row Height and Column Width

Default dimensions might not fit your content. Here's how to customize them:

import com.spire.xls.*;

public class SetHeightWidth {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Set column 4 width to 30
        worksheet.setColumnWidth(4, 30);

        // Set row 4 height to 30
        worksheet.setRowHeight(4, 30);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Setting default dimensions for all rows/columns:

// Set default row height for all rows
worksheet.setDefaultRowHeight(20);

// Set default column width for all columns
worksheet.setDefaultColumnWidth(15);

Pro tip: Chinese content usually needs wider columns than English. I typically preset different widths based on content type.

7. Auto-Fitting Column Width

Not sure how wide to make columns? Let Excel figure it out:

import com.spire.xls.*;

public class AutoFitColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Auto-fit all columns in the used range
        worksheet.getAllocatedRange().autoFitColumns();

        // Or auto-fit specific columns
        // worksheet.getCellRange("A1:C10").autoFitColumns();

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Performance note: Auto-fitting scans all cells, so it can be slow with large datasets. If performance matters, consider setting fixed widths instead.

8. Grouping Rows and Columns (Outline Feature)

Excel's outline feature lets you collapse related rows/columns—perfect for hierarchical data:

import com.spire.xls.*;

public class GroupRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet sheet = workbook.getWorksheets().get(0);

        // Group rows 1 to 5 (can be collapsed)
        // Third parameter: whether to show summary below
        sheet.groupByRows(1, 5, false);

        // Group columns 1 to 3
        sheet.groupByColumns(1, 3, false);

        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Real-world scenarios: Grouping financial data by month, organizing project plans by phases, etc. Users can expand or collapse to view data at different granularity levels.

9. Conditional Row Deletion

Sometimes you need to filter and delete rows based on content, like removing rows containing specific keywords:

import com.spire.xls.*;

public class RemoveRowByKeyword {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");

        Worksheet sheet = workbook.getWorksheets().get(0);

        String keyword = "Test";

        // Iterate backwards and delete rows containing the keyword
        for (int i = sheet.getRows().length - 1; i >= 0; i--) {
            boolean shouldDelete = false;

            // Check all cells in the row
            for (Object cell : sheet.getRows()[i]) {
                if (cell != null && cell.toString().contains(keyword)) {
                    shouldDelete = true;
                    break;
                }
            }

            if (shouldDelete) {
                sheet.deleteRow(i + 1);
            }
        }

        workbook.saveToFile("filtered.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}

Use cases: Cleaning test data, filtering invalid records, removing rows marked as obsolete, etc.

10. Real-World Example: Dynamic Report Generation

Let's put everything together with a complete example: generating a formatted product report dynamically.

import com.spire.xls.*;
import java.util.List;
import java.util.ArrayList;

public class DynamicReport {

    // Product data class
    static class Product {
        String name;
        double price;
        int quantity;

        Product(String name, double price, int quantity) {
            this.name = name;
            this.price = price;
            this.quantity = quantity;
        }
    }

    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);

        // Prepare data
        List<Product> products = new ArrayList<>();
        products.add(new Product("Product A", 99.9, 100));
        products.add(new Product("Product B", 199.9, 50));
        products.add(new Product("Product C", 299.9, 30));

        // 1. Write headers
        String[] headers = {"Product Name", "Unit Price", "Quantity", "Total"};
        for (int i = 0; i < headers.length; i++) {
            sheet.getCellRange(1, i + 1).setValue(headers[i]);
        }

        // Format headers
        sheet.getCellRange(1, 1, 1, headers.length).getStyle().getFont().isBold(true);
        sheet.getCellRange(1, 1, 1, headers.length).getStyle()
            .setKnownColor(ExcelColors.LightBlue);

        // 2. Dynamically insert data rows
        for (int i = 0; i < products.size(); i++) {
            int row = i + 2;  // Start from row 2 (row 1 is header)
            Product p = products.get(i);

            sheet.getCellRange(row, 1).setValue(p.name);
            sheet.getCellRange(row, 2).setNumberValue(p.price);
            sheet.getCellRange(row, 3).setNumberValue(p.quantity);

            // Total = Price * Quantity (using formula)
            sheet.getCellRange(row, 4).setFormula("=B" + row + "*C" + row);
            sheet.getCellRange(row, 4).getStyle().setNumberFormat("$#,##0.00");
        }

        // 3. Add summary row
        int summaryRow = products.size() + 2;
        sheet.getCellRange(summaryRow, 1).setValue("Total");
        sheet.getCellRange(summaryRow, 1).getStyle().getFont().isBold(true);

        // Summary formulas
        sheet.getCellRange(summaryRow, 3).setFormula("=SUM(C2:C" + (summaryRow - 1) + ")");
        sheet.getCellRange(summaryRow, 4).setFormula("=SUM(D2:D" + (summaryRow - 1) + ")");
        sheet.getCellRange(summaryRow, 4).getStyle().setNumberFormat("$#,##0.00");

        // 4. Set column widths
        sheet.setColumnWidth(1, 20);  // Product Name
        sheet.setColumnWidth(2, 15);  // Unit Price
        sheet.setColumnWidth(3, 15);  // Quantity
        sheet.setColumnWidth(4, 20);  // Total

        // 5. Auto-fit (optional)
        // sheet.getAllocatedRange().autoFitColumns();

        // 6. Add borders
        sheet.getCellRange(1, 1, summaryRow, 4).getBorders()
            .setLineStyle(LineStyleType.Thin);

        workbook.saveToFile("product_report.xlsx", ExcelVersion.Version2013);
        workbook.dispose();

        System.out.println("Report generated successfully!");
    }
}

This example demonstrates:

Dynamic row insertion based on data volume
Using formulas for automatic calculations
Formatting (bold, background color, number format)
Column width adjustment
Adding borders

Summary

Here are the key takeaways:

Insert rows/columns using insertRow() and insertColumn(), supports batch insertion
Delete rows/columns using deleteRow() and deleteColumn(), iterate backwards when deleting in loops
Delete blanks using isBlank() check—great for cleaning dirty data
Copy rows/columns using copy() method, control what to copy (values, formatting, formulas)
Hide/show using hideRow/Column() and showRow/Column()
Set dimensions using setRowHeight() and setColumnWidth()
Auto-fit using autoFitColumns(), but watch performance with large datasets
Group rows/columns using groupByRows/Columns(), perfect for hierarchical data

In practice, the most important thing is understanding your business requirements. Not every scenario needs complex operations—sometimes simple insert and delete are all you need.

Hope these tips help you work more efficiently with Excel in Java! Feel free to share your own tricks in the comments.

How to Add and Remove Attachments in PDF Using Java

Leon Davis — Fri, 15 May 2026 12:50:40 +0000

In many professional and academic scenarios, PDF documents often need to carry additional files, such as Excel spreadsheets, PowerPoint slides, or supplementary data. Being able to manage attachments within a PDF can improve document organization, facilitate sharing, and make it easier for recipients to access related materials.

In this article, we will explore four practical ways to add and remove attachments in PDF files using Java, covering both direct attachments and annotation-based attachments:

Adding a standard attachment to a PDF
Attaching a file via annotation
Removing standard attachments
Removing annotation-based attachments

Installing the Required Library

To implement these examples, we use Spire.PDF for Java, a library that provides APIs for loading PDF documents, adding attachments, managing annotations, and saving changes. You can download the JAR package or include it via Maven:

<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.pdf</artifactId>
        <version>12.4.4</version>
    </dependency>
</dependencies>

Once added to your project, you can start manipulating attachments in PDFs programmatically.

1. Adding a Standard Attachment

The simplest way to attach a file to a PDF is by using a direct attachment. This approach embeds the file into the PDF and makes it available in the document’s attachment panel. For example, we can attach an Excel file to a report:

import com.spire.pdf.PdfDocument;
import com.spire.pdf.attachments.PdfAttachment;

public class AttachFilesToPdf {

    public static void main(String[] args) {
        // Create a new PDF document object
        PdfDocument doc = new PdfDocument();

        // Load an existing PDF file
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.pdf");

        // Create a PdfAttachment object for an external file
        PdfAttachment attachment = new PdfAttachment("C:\\Users\\Administrator\\Desktop\\Data.xlsx");

        // Add the attachment to the PDF
        doc.getAttachments().add(attachment);

        // Save the updated PDF to a new file
        doc.saveToFile("Attachment.pdf");

        // Close the document
        doc.close();
    }
}

Details: Direct attachments are visible in the PDF’s attachment panel and can be opened by standard PDF viewers. This method is suitable for providing supplementary data or reference files without embedding them visibly on the page.

2. Attaching a File via Annotation

For more interactive scenarios, you can attach a file through a PDF annotation. This allows you to display a visual cue on the page and provide instructions or labels for users. For example, placing the text “Here is the report:” on a page and attaching a PowerPoint file next to it:

import com.spire.pdf.PdfPageBase;
import com.spire.pdf.annotations.*;
import com.spire.pdf.graphics.*;
import com.spire.pdf.PdfDocument;

import java.awt.*;
import java.awt.geom.Dimension2D;
import java.awt.geom.Rectangle2D;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.EnumSet;

public class AnnotationAttachment {

    public static void main(String[] args) throws IOException {
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.pdf");

        // Get the first page of the PDF
        PdfPageBase page = doc.getPages().get(0);

        // Draw a label on the page
        String label = "Here is the report:";
        PdfTrueTypeFont font = new PdfTrueTypeFont(new Font("Arial", Font.PLAIN, 13));
        double x = 35;
        double y = page.getActualSize().getHeight() - 220;
        page.getCanvas().drawString(label, font, PdfBrushes.getRed(), x, y);

        // Convert external file to byte array
        String filePath = "C:\\Users\\Administrator\\Desktop\\Report.pptx";
        byte[] data = toByteArray(filePath);

        // Determine the annotation bounds based on the label width
        Dimension2D size = font.measureString(label);
        Rectangle2D bound = new Rectangle2D.Float((float) (x + size.getWidth() + 5), (float) y, 10, 15);

        // Create the attachment annotation
        PdfAttachmentAnnotation annotation = new PdfAttachmentAnnotation(bound, filePath, data);
        annotation.setColor(new PdfRGBColor(new Color(0, 128, 128)));
        annotation.setFlags(EnumSet.of(PdfAnnotationFlags.Default));
        annotation.setIcon(PdfAttachmentIcon.Graph);
        annotation.setText("Click here to open the file");

        // Add the annotation to the page
        page.getAnnotationsWidget().add(annotation);

        // Save the updated PDF
        doc.saveToFile("Attachments.pdf");
        doc.close();
    }

    // Utility method to convert a file to byte array
    public static byte[] toByteArray(String filePath) throws IOException {
        File file = new File(filePath);
        long fileSize = file.length();
        if (fileSize > Integer.MAX_VALUE) {
            System.out.println("File too big...");
            return null;
        }
        FileInputStream fi = new FileInputStream(file);
        byte[] buffer = new byte[(int) fileSize];
        int offset = 0;
        int numRead;
        while (offset < buffer.length
                && (numRead = fi.read(buffer, offset, buffer.length - offset)) >= 0) {
            offset += numRead;
        }
        if (offset != buffer.length) {
            throw new IOException("Could not completely read file " + file.getName());
        }
        fi.close();
        return buffer;
    }
}

Details: Annotation attachments allow you to provide visual cues and instructions. Users can click the annotation to open the attached file, which is particularly useful for presentations or supplementary documents.

3. Removing Standard Attachments

Sometimes you may need to remove attachments that are no longer relevant. You can do this by accessing the PDF’s attachment collection:

import com.spire.pdf.PdfDocument;
import com.spire.pdf.attachments.PdfAttachmentCollection;

public class RemoveAttachments {

    public static void main(String[] args) {
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Attachments.pdf");

        // Get the collection of standard attachments (excluding annotation attachments)
        PdfAttachmentCollection attachments = doc.getAttachments();

        // Remove all attachments
        attachments.clear();

        // Or remove a specific attachment by index
        // attachments.removeAt(0);

        // Save the updated PDF
        doc.saveToFile("output/DeleteAttachments.pdf");
        doc.close();
    }
}

Details: Standard attachment removal affects only the directly embedded files, leaving any annotation-based attachments intact.

4. Removing Annotation-Based Attachments

If attachments were added as annotation attachments, you need to remove them by iterating through the page annotations:

import com.spire.pdf.PdfDocument;
import com.spire.pdf.annotations.PdfAnnotation;
import com.spire.pdf.annotations.PdfAnnotationCollection;
import com.spire.pdf.annotations.PdfAttachmentAnnotationWidget;

public class RemoveAnnotationAttachments {

    public static void main(String[] args) {
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\Attachments.pdf");

        // Loop through each page
        for (int i = 0; i < doc.getPages().getCount(); i++) {
            PdfAnnotationCollection annotationCollection = doc.getPages().get(i).getAnnotationsWidget();

            // Iterate through the annotations and remove attachment annotations
            for (Object annotation : annotationCollection) {
                if (annotation instanceof PdfAttachmentAnnotationWidget) {
                    annotationCollection.remove((PdfAnnotation) annotation);
                }
            }
        }

        // Save the updated PDF
        doc.saveToFile("output/DeleteAnnotationAttachments.pdf");
        doc.close();
    }
}

Details: This method ensures that all annotation-based attachments are removed while leaving standard attachments intact.

Conclusion

Managing attachments in PDF files can be done in multiple ways:

Standard attachments – Embed files directly into the PDF.
Annotation attachments – Provide interactive cues on the page with clickable attachments.
Removing standard attachments – Clear outdated or unnecessary files.
Removing annotation attachments – Remove interactive file annotations while keeping the PDF content intact.

These approaches provide flexibility depending on whether you want the attachment to be directly visible in the attachment panel or embedded as part of the page annotations.

How to Find and Highlight Text in PDF Using Java: 4 Practical Methods

Leon Davis — Fri, 15 May 2026 12:39:56 +0000

PDF documents are widely used in reports, contracts, textbooks, and exams. Often, we need to search for specific text and highlight it—for example, marking key terms in reports, highlighting important clauses in contracts, or extracting certain content for analysis. Manual processing is time-consuming and prone to errors, so automating text search and highlighting in PDFs is essential.

In this guide, we’ll demonstrate four practical ways to find and highlight text in PDF files using Java:

Highlight text on a single page
Highlight text within a specific rectangular area
Highlight text throughout the entire document
Highlight text using regular expressions

Installing the Required Library

We use Spire.PDF for Java, a powerful PDF processing library that allows you to load PDFs, search text, edit content, and highlight text. You can download the JAR package or include it via Maven:

<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.pdf</artifactId>
        <version>12.4.4</version>
    </dependency>
</dependencies>

Once installed, you can manipulate PDFs entirely in Java without third-party software like Adobe Acrobat.

Method 1: Highlight Text on a Single Page

If you only need to highlight text on a single page—for instance, the term “Database” on the first page—this method is ideal for quick annotations.

import com.spire.ms.System.Collections.Generic.List;
import com.spire.pdf.FileFormat;
import com.spire.pdf.PdfDocument;
import com.spire.pdf.PdfPageBase;
import com.spire.pdf.texts.PdfTextFinder;
import com.spire.pdf.texts.PdfTextFragment;
import com.spire.pdf.texts.TextFindParameter;

import java.awt.*;
import java.util.EnumSet;

public class HighlightTextInPage {

    public static void main(String[] args) {
        // Load the PDF document
        PdfDocument doc = new PdfDocument();
        doc.loadFromFile("C:\\Users\\Administrator\\Desktop\\SampleDocument.pdf");

        // Get the first page
        PdfPageBase page = doc.getPages().get(0);

        // Create a text finder for this page
        PdfTextFinder finder = new PdfTextFinder(page);

        // Set search options: whole word match, ignore case
        finder.getOptions().setTextFindParameter(EnumSet.of(TextFindParameter.WholeWord));
        finder.getOptions().setTextFindParameter(EnumSet.of(TextFindParameter.IgnoreCase));

        // Find all occurrences of the word "Database"
        List<PdfTextFragment> results = finder.find("Database");

        // Highlight each found text fragment
        for (PdfTextFragment textFragment : results) {
            textFragment.highLight(Color.LIGHT_GRAY);
        }

        // Save the highlighted PDF to a new file
        doc.saveToFile("output/HighlightTextInPage.pdf", FileFormat.PDF);
        doc.dispose();
    }
}

Use cases: Page-level annotation, report review, quick content highlighting.

Method 2: Highlight Text Within a Specific Rectangular Area

Sometimes you only want to search a specific area, such as the header, footer, or a table, to avoid highlighting irrelevant text. Use setFindArea() to define the search rectangle.

import java.awt.geom.Rectangle2D;

// Define a rectangular area (e.g., top header region)
finder.getOptions().setFindArea(new Rectangle2D.Float(0, 0, 841, 180));

// Search for the word "Report" within this area
List<PdfTextFragment> results = finder.find("Report");

// Highlight each found fragment
for (PdfTextFragment textFragment : results) {
    textFragment.highLight(Color.LIGHT_GRAY);
}

Use cases: Highlight header text in reports, mark section titles, or annotate tables.

Method 3: Highlight Text Throughout the Entire Document

To process an entire document—for example, to highlight “Contract Clause” throughout a PDF—you can iterate through each page.

for (Object pageObj : doc.getPages()) {
    PdfPageBase page = (PdfPageBase) pageObj;
    PdfTextFinder finder = new PdfTextFinder(page);

    // Set search options: whole word match, ignore case
    finder.getOptions().setTextFindParameter(EnumSet.of(TextFindParameter.WholeWord));
    finder.getOptions().setTextFindParameter(EnumSet.of(TextFindParameter.IgnoreCase));

    // Find all occurrences of "Contract Clause"
    List<PdfTextFragment> results = finder.find("Contract Clause");

    // Highlight each found fragment
    for (PdfTextFragment textFragment : results) {
        textFragment.highLight(Color.LIGHT_GRAY);
    }
}

Use cases: Full-document contract review, batch report annotation, textbook content marking.

Method 4: Highlight Text Using Regular Expressions

Regular expressions are useful for pattern-based searches, such as section headings or numbered entries. For example, highlight all chapter headings starting with “Chapter” followed by a number:

// Enable regex search
finder.getOptions().setTextFindParameter(EnumSet.of(TextFindParameter.Regex));

// Define a regex pattern for "Chapter" followed by any number
String pattern = "\\bChapter\\s\\d+\\b";

// Find all matches
List<PdfTextFragment> results = finder.find(pattern);

// Highlight each match
for (PdfTextFragment textFragment : results) {
    textFragment.highLight(Color.LIGHT_GRAY);
}

Use cases: Highlight chapter titles, extract numbered entries, detect structured content.

Conclusion

These four methods demonstrate practical ways to find and highlight text in PDF files using Java:

Single-page highlighting – Quickly annotate specific pages.
Rectangular area highlighting – Precisely control search scope.
Full-document highlighting – Batch process large PDFs.
Regex-based highlighting – Flexibly match complex patterns.

With Spire.PDF for Java, you can automate PDF text processing efficiently, making it ideal for contract review, report annotation, textbook marking, and other professional applications.

DEV Community: Leon Davis

Java - Extract Images from PDF and Compress PDF Image Size

1. Preparation

2. Extract Images from a PDF in Java

1. Implementation Logic

2. Sample Code

3. Code Explanation

3. Compress Images in a PDF in Java

1. Applicable Scenarios

2. Sample Code

4. Explanation of setIncrementalUpdate(false)

5. Frequently Asked Questions

1. Why can’t some images be extracted from a PDF?

2. What if the PDF file size does not change much after compression?

3. Will compression affect image clarity?

6. Conclusion

Print Word Document in Java

Environment setup

1. Simple Way to Print Word Document in Java

2. Java Word Printing with PrinterJob for Layout Control

3. Custom Paper Size Configuration for Word Printing

4. Page Margins and Duplex Printing in Java

Duplex printing modes

5. Batch Printing Multiple Word Documents in Java

6. Common Issues When Printing Word Documents in Java

Print dialog appears during execution

Document layout is cut off or misaligned

Duplex printing does not work as expected

7. Practical Considerations for Production Printing Systems

Always release document resources

Validate environment before printing

Conclusion

How to Add Hyperlinks to Word Documents in Java

Environment Setup

1. Add the Maven Dependency

2. Prepare Test Files

Add Web and Email Links to a Word Document

Link to an External File

Link to a Bookmark Inside the Word Document

Add a Hyperlink to an Image in Word

Add a Hyperlink to Existing Text

Complete Example: Batch Add Hyperlinks to Keywords

Summary

How to AutoFit Excel Row Height and Column Width in C#

Preparation

Auto Fit All Rows and Columns in an Excel Worksheet

Auto Fit a Specific Column in Excel

Auto Fit a Specific Row in Excel

Auto Fit a Specific Cell Range in Excel

Notes and Best Practices

1. Enable Text Wrapping When Needed

2. Be Careful with Merged Cells

3. Avoid Processing Blank Areas

4. Consider Performance for Large Workbooks

Conclusion

Convert PDF to PDF/A or PDF/A to PDF in Java: Complete Guide

Understanding PDF/A

Environment Setup

Maven Dependency

Gradle Configuration

1. Basic Conversion: PDF to Various PDF/A Formats

2. Handling Encrypted PDFs

3. Preserving Metadata

4. Converting PDF/A Back to Regular PDF

5. Creating PDF/A with Attachments

6. Practical Example: Batch Conversion Tool

7. Common Issues and Solutions

Issue 1: Conversion Fails Due to Font Problems

Issue 2: File Size Increases Dramatically After Conversion

Issue 3: How to Verify Generated PDF/A Compliance?

Issue 4: Slow Conversion Speed

8. Best Practices Summary

1. Choose the Right PDF/A Level

2. Resource Management

3. Error Handling

4. Performance Monitoring

9. Comparison with Alternative Solutions

Spire.PDF vs Apache PDFBox

Conclusion

How to Convert Between OFD and PDF in Java

4. Explanation of `setIncrementalUpdate(false)`