Java添加、读取Excel公式

「爱情、让人受尽委屈。」 2023-06-29 05:42 27阅读 0赞

操作excel表格用公式来处理数据时，可通过创建公式来运算数据，或通过读取公式来获取数据信息来源。本文以通过Java代码来演示在Excel中创建及读取公式的方法。这里使用了Excel Java类库（Free Spire.XLS for Java 免费版），在[官网下载][Link 1]获取文件包后，解压，将lib文件夹下的jar文件导入Java程序；或者通过[maven仓库下载][maven]并导入。导入结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70][]

### **1.** **创建公式** ###

import com.spire.xls.*;
    
    public class AddFormula {
        public static void main(String[] args) {
            //创建Workbook对象
            Workbook wb = new Workbook();
    
            //获取第一个工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //声明两个变量
            int currentRow = 1;
            String currentFormula = null;
    
            //设置列宽
            sheet.setColumnWidth(1, 32);
            sheet.setColumnWidth(2, 16);
    
            //写入用于测试的数据到单元格
            sheet.getCellRange(currentRow,1).setValue("测试数据：");
            sheet.getCellRange(currentRow,2).setNumberValue(1);
            sheet.getCellRange(currentRow,3).setNumberValue(2);
            sheet.getCellRange(currentRow,4).setNumberValue(3);
            sheet.getCellRange(currentRow,5).setNumberValue(4);
            sheet.getCellRange(currentRow,6).setNumberValue(5);
    
            //写入文本
            currentRow += 2;
            sheet.getCellRange(currentRow,1).setValue("公式：") ; ;
            sheet.getCellRange(currentRow,2).setValue("结果：");
    
            //设置单元格格式
            CellRange range = sheet.getCellRange(currentRow,1,currentRow,2);
            range.getStyle().getFont().isBold(true);
            range.getStyle().setKnownColor(ExcelColors.LightGreen1);
            range.getStyle().setFillPattern(ExcelPatternType.Solid);
            range.getStyle().getBorders().getByBordersLineType(BordersLineType.EdgeBottom).setLineStyle(LineStyleType.Medium);
    
            //算数运算
            currentFormula = "=1/2+3*4";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //日期函数
            currentFormula = "=TODAY()";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
            sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("YYYY/MM/DD");
    
            //时间函数
            currentFormula = "=NOW()";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
            sheet.getCellRange(currentRow,2).getStyle().setNumberFormat("H:MM AM/PM");
    
            //IF函数
            currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //PI函数
            currentFormula = "=PI()";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //三角函数
            currentFormula = "=SIN(PI()/6)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //计数函数
            currentFormula = "=Count(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //最大值函数
            currentFormula = "=MAX(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //平均值函数
            currentFormula = "=AVERAGE(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //求和函数
            currentFormula = "=SUM(B1:F1)";
            sheet.getCellRange(++currentRow,1).setText(currentFormula);
            sheet.getCellRange(currentRow,2).setFormula(currentFormula);
    
            //保存文档
            wb.saveToFile("AddFormulas.xlsx",FileFormat.Version2013);
            wb.dispose();
        }
    }

公式创建结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 1][]

### **2.** **读取公式** ###

import com.spire.xls.*;
    
    public class ReadFormula {
        public static void main(String[] args) {
            //加载Excel文档
            Workbook wb = new Workbook();
            wb.loadFromFile("AddFormulas.xlsx");
    
            //获取第一个工作表
            Worksheet sheet = wb.getWorksheets().get(0);
    
            //遍历B1到B13的单元格
            for (Object cell: sheet.getCellRange("B1:B13"))
            {
                CellRange cellRange = (CellRange)cell;
    
                //判断单元格是否含有公式
                if (cellRange.hasFormula())
                {
                    //打印单元格及公式
                    String certainCell = String.format("单元格[%d, %d]含有公式：", cellRange.getRow(), cellRange.getColumn());
                    System.out.println(certainCell + cellRange.getFormula());
                }
            }
        }
    }

公式读取结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl_size_16_color_FFFFFF_t_70 2][]

（文本完）

[Link 1]: https://www.e-iceblue.cn/Introduce/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/20200110155313980.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/2020011015541167.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/20200110155506661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VpY2VibHVl,size_16,color_FFFFFF,t_70