POI / Excel：在“相对”中应用公式。办法 [英] POI / Excel : applying formulas in a "relative" way

问题描述

我正在使用Apache的POI来使用Java操作Excel（.xls）文件。

我正在尝试创建一个新的单元格，其内容是公式的结果，就好像用户已经复制/粘贴了公式（我称之为亲属）这是一个简单的例子：
Cell A1包含1，B1包含 2，A2包含3，B2包含4。
单元格A3包含以下公式= A1 + B1。
如果我将公式复制到excel下的A4单元格，它将变为= A2 + B2：excel正在动态调整公式的内容。

不幸的是我无法以编程方式获得相同的结果。我找到的唯一解决方案是将公式标记化并自己完成脏工作，但我真的怀疑这应该是这样做的。我无法在指南或API中找到我想要的内容。

有没有更简单的方法来解决这个问题？如果是这样的话，请指点我正确的方向吗？

祝你好运，

Nils

解决方案

我也认为没有一种简单的方法可以做到这一点。

甚至 HSSF和XSSD示例，例如 TimesheetDemo 手动执行公式构建。例如第110行

  String ref =（char）（'A'+ j）+3：+（char）（ 'A'+ j）+12; 
 cell.setCellFormula（SUM（+ ref +））; 
  

I'm using Apache's POI to manipulate Excel (.xls) files with Java.

I'm trying to create a new cell whom content is the result of a formula as if the user had copied/pasted the formula (what i call the "relative" way, as opposite to "absolute").

To make myself clearer, here is a simple example : Cell A1 contains "1",B1 contains "2", A2 contains "3", B2 contains "4". Cell A3 contains the following formula "=A1+B1". If I copy the formula to the A4 cell under excel, it becomes "=A2+B2" : excel is adapting the content of the formula dynamically.

Unfortunately I cannot get the same result programatically. The only solution I found is to tokenize the formula and do the dirty work myself, but I really doubt that this is supposed to be done that way. I was not able to find what I'm looking for in the guides or in the API.

Is there an easier way to solve this problem ? If it's the case, can you please point me in the right direction ?

Best regards,

Nils

解决方案

I too think that there isn't an easy way to do this.

Even the HSSF and XSSD examples on the POI site e.g. TimesheetDemo do the formula construction manually. e.g. around line 110

String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");

这篇关于POI / Excel：在“相对”中应用公式。办法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！