Java 添加、删除、移动、隐藏/显示Excel工作表

怼烎@ 2023-06-24 13:24 67阅读 0赞

本文内容将介绍通过Java程序如何添加、删除、移动工作表，以及设置工作表隐藏、显示等操作。文中使用了Java Excel类库（Free Spire.XLS for Java 免费版），可通过[官网下载][Link 1]包，解压，并将lib文件夹下的Spire.Xls.jar文件导入Java程序；或者通过maven仓库下载并导入，仓库路径配置及依赖添加可参考[教程][Link 2]。

导入效果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70][]

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

## Java 代码示例（供参考） ##

**1.** **添加、删除工作表**

import com.spire.xls.*;
    
    public class AddWorksheet {
        public static void main(String[] args) {
            //加载文档
            Workbook wb = new Workbook();
            wb.loadFromFile("test.xlsx");
    
            //添加一个新的工作表
            Worksheet newsheet = wb.getWorksheets().add("NewSheet");
            newsheet.getCellRange("E3").setText("新添加的内容");
    
            //删除第二个工作表
            Worksheet sheet1 = wb.getWorksheets().get(1);
            sheet1.remove();
    
            //保存文档
            wb.saveToFile("AddAndRemoveWorksheet.xlsx");
            wb.dispose();
        }
    }

工作表添加、删除结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 1][]

**2.** **移动工作表**

import com.spire.xls.*;
    
    public class MoveWorksheet {
        public static void main(String[] args) {
            //加载文档
            Workbook wb = new Workbook();
            wb.loadFromFile("test.xlsx");
    
            //获取工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //移动工作表作为第三张工作表
            sheet.moveWorksheet(2);
    
            //保存文档
            wb.saveToFile("MoveWorksheet.xlsx",FileFormat.Version2013);
            wb.dispose();
        }
    }

工作表移动结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 2][]

**3.** **隐藏工作表**

import com.spire.xls.*;
    
    public class HideWorksheet {
        public static void main(String[] args) {
            //加载文档
            Workbook wb = new Workbook();
            wb.loadFromFile("test.xlsx");
    
            //获取第一个工作表，隐藏（获取工作表，可以通过索引值或者工作表名称来获取）
            Worksheet sheet = wb.getWorksheets().get(0);
            Worksheet sheet1 = wb.getWorksheets().get("Sheet3");
            sheet.setVisibility(WorksheetVisibility.Hidden);
            sheet1.setVisibility(WorksheetVisibility.Hidden);
    
            //保存文档
            wb.saveToFile("HideWorksheet.xlsx");
            wb.dispose();
        }
    }

工作表隐藏结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 3][]

**4.** **显示（取消隐藏）工作表**

import com.spire.xls.*;
    
    public class ShowWorksheet {
        public static void main(String[] args) {
            //加载文档
            Workbook wb = new Workbook();
            wb.loadFromFile("HideWorksheet.xlsx");
    
            //遍历所有工作表，显示所有隐藏的工作表
            for (int i = 0; i < wb.getWorksheets().getCount();i++){
                Worksheet sheet =  wb.getWorksheets().get(i);
                sheet.setVisibility(WorksheetVisibility.Visible);
            }
    
            /*//显示指定工作表
            Worksheet sheet1 = wb.getWorksheets().get("Sheet1");
            sheet1.setVisibility(WorksheetVisibility.Visible);*/
    
            //保存文档
            wb.saveToFile("ShowWorksheet.xlsx");
            wb.dispose();
        }
    }

全部工作表显示结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 4][]

指定工作表显示结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 5][]

（本文完）

[Link 1]: https://www.e-iceblue.cn/Introduce/Free-Spire-XLS-JAVA.html
[Link 2]: 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/20191223151103667.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/2019122315121855.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/20191223151334834.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 3]: https://img-blog.csdnimg.cn/20191223151513484.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/20191223151703642.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 5]: https://img-blog.csdnimg.cn/20191223151729537.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70