Java 添加、修改、读取、复制、删除Excel批注

心已赠人 2023-07-01 06:29 26阅读 0赞

本文介绍通过Java程序来操作Excel批注的方法。操作内容包括批注添加（添加批注文本、背景色、字体、自适应等）、修改、读取（文本、图片）、复制、删除等。

**工具**：**Free Spire.XLS for Java** （免费版）

***注：***Jar文件获取可通过[官网下载包][Link 1]，并解压将lib文件夹下的jar文件导入java程序；或通过[maven][]下载导入。

导入结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70][]

--------------------

# **Java****代码示例** #

## **【示例1****】添加批注** ##

import com.spire.xls.*;
    
    import java.awt.*;
    
    public class AddComment {
        public static void main(String[] args) {
            //加载Excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("test.xlsx");
    
            //获取工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //创建字体
            ExcelFont font = wb.createFont();
            font.setFontName("Arial");
            font.setSize(11);
            font.setKnownColor(ExcelColors.Orange);
            ExcelFont fontBlue = wb.createFont();
            fontBlue.setKnownColor(ExcelColors.LightBlue);
            ExcelFont fontGreen = wb.createFont();
            fontGreen.setKnownColor(ExcelColors.LightGreen);
    
            //给指定的Excel单元格添加普通批注
            CellRange range = sheet.getCellRange("H2");
            range.getComment().setText("税前价格");
            range.getComment().setTextRotation(TextRotationType.TopToBottom);
            range.autoFitColumns();
            range.getComment().setVisible(true);//设置批注是否隐藏
            range.getComment().getFill().customPicture("logo.png");//背景图片填充
            //range.getComment().getFill().setForeColor(new Color(255,228,225));//颜色填充
    
    
            //给指定的Excel单元格添加富文本批注
            range = sheet.getCellRange("F2");
            range.getRichText().setFont(0, 8, font);
            range.autoFitColumns();
            range.getComment().getRichText().setText("已入库数量");
            range.getComment().getRichText().setFont(0, 4, fontGreen);
            range.getComment().getRichText().setFont(3, 4, fontBlue);
    
    
            //保存结果文档
            wb.saveToFile("AddComments.xlsx", ExcelVersion.Version2013);
            wb.dispose();
        }
    }

批注添加效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 1][]

## **【示例2****】修改批注** ##

import com.spire.xls.*;
    
    public class ModifyComment {
        public static void main(String[] args) {
            //加载excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("AddComments.xlsx");
    
            //获取工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //获取指定单元格中的批注，设置新的批注文本、填充色
            sheet.getRange().get("H2").getComment().setText("不含税价");
            sheet.getRange().get("F2").getComment().setText("最新数据");
    
            //保存文档
            wb.saveToFile("ModifyComment.xlsx",ExcelVersion.Version2013);
            wb.dispose();
        }
    }

批注修改结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 2][]

## **【示例3****】读取批注** ##

import com.spire.xls.*;
    
    import javax.imageio.ImageIO;
    import java.awt.*;
    import java.awt.image.BufferedImage;
    import java.io.File;
    import java.io.IOException;
    
    public class ExtractComment {
        public static void main(String[] args)throws IOException {
            //加载excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("AddComments.xlsx");
    
            //获取工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //打印指定单元格的文本批注内容
            System.out.println("H2 的批注文本 = " + sheet.getCellRange("H2").getComment().getText());
            System.out.println("F2 的批注文本 = " + sheet.getCellRange("F2").getComment().getRichText().getRtfText());
    
            //获取指定单元格中批注背景颜色
            Color color = sheet.getRange().get("H2").getComment().getFill().getForeColor();
            System.out.print(color);
    
            //获取指定单元格中的批注背景图片
            /*BufferedImage image = sheet.getRange().get("H2").getComment().getFill().getPicture();
            ImageIO.write(image,"png",new File("ExtractedImages.png"));*/
    
        }
    }

批注读取结果：

![20200119171720678.png][]

读取的图片可在程序文件夹下查看。

## **【示例4****】复制批注** ##

import com.spire.xls.*;
    
    import java.awt.*;
    import java.awt.image.BufferedImage;
    
    public class CopyComment {
        public static void main(String[] args) {
            //加载Excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("AddComments.xlsx");
    
            //获取指定工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //获取源单元格批注内容
            CellRange range = sheet.getRange().get("H2");//获取指定单元格
            String commenttext = range.getComment().getText();//获取批注文本
            BufferedImage image  = range.getComment().getFill().getPicture();//获取批注填充图片
           // Object object = range.getComment().getFill().getForeColor();//获取批注填充色
    
    
    
            //获取新的单元格，添加批注文本和图片（颜色）填充
            CellRange range1 = sheet.getRange().get("D3");
            range1.getComment().setText(commenttext);
            range1.getComment().getFill().customPicture(image," ");
            //range1.getComment().getFill().setForeColor((Color) object);
    
    
            //保存文档
            wb.saveToFile("CopyComment.xlsx",ExcelVersion.Version2013);
            wb.dispose();
        }
    }

批注复制结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 3][]

# #

## **【示例5****】删除批注** ##

import com.spire.xls.ExcelVersion;
    import com.spire.xls.FileFormat;
    import com.spire.xls.Workbook;
    import com.spire.xls.Worksheet;
    
    public class DeleteComment {
        public static void main(String[] args) {
            //加载Excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("AddComments.xlsx");
    
            //获取工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //获取指定单元格中的批注，并删除
            sheet.getRange().get("H2").getComment().remove();
    
            //保存文档
            wb.saveToFile("DeleteComment.xlsx", ExcelVersion.Version2013);
            wb.dispose();
        }
    }

批注删除结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 4][]

（本文完）

[Link 1]: https://www.e-iceblue.cn/Downloads/Free-Spire-XLS-JAVA.html
[maven]: https://www.e-iceblue.cn/licensing/install-spirepdf-for-java-from-maven-repository.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020011917145763.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200119171554152.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200119171628402.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70
[20200119171720678.png]: https://img-blog.csdnimg.cn/20200119171720678.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200119171838346.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200119171921249.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70