Apache POI 公式中的引用表
我正在使用 Apache POI 3.6 生成 excel (2003) 工作表。我想在单元格中插入一个公式,该公式计算多张纸上某些单元格的总和。
我有名为 a、b 和 c 的工作表,想要计算我尝试过的单元格 A1 的总和
:
cell.setCellFormula("a!A1+b!A1+c!A1");
POI 不会产生任何错误,但是当我打开工作表时,我在 OpenOffice 中收到错误:
Err: 522 - =$#REF!.A1+$#REF!.A1+$#REF!.A1
我做了一些研究,显然存在引用多个工作表时会出现错误。 (例如 https://issues.apache.org/bugzilla/show_bug.cgi? id=46670) 有谁知道如何在 POI 中使用多个工作表的公式吗?
- - - - - - - 源代码 - - - - - - - - - -
public static void main(String args[]){
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Total");
Row row = sheet.createRow((short)0);
Cell cell = row.createCell(0);
cell.setCellFormula("a!A1+b!A1+c!A1");
Sheet sheet1 = wb.createSheet("a");
Sheet sheet2 = wb.createSheet("b");
Sheet sheet3 = wb.createSheet("c");
Sheet sheet4 = wb.createSheet("d");
createVal(sheet1, createHelper, 5);
createVal(sheet2, createHelper, 10);
createVal(sheet3, createHelper, 15);
createVal(sheet4, createHelper, 20);
try {
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("done");
} catch (IOException e) {
e.printStackTrace();
} }
public static void createVal(Sheet sheet, CreationHelper createHelper, int i){
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
// Or do it on one line.
row.createCell(0).setCellValue(i);
}
I am using Apache POI 3.6 to generate excel (2003) sheets. I want to insert a formula to a cell which calculates a sum of a certain cells on several sheets.
I have sheets named a, b and c and want to calculate the sum the cells A1
I tried:
cell.setCellFormula("a!A1+b!A1+c!A1");
POI does not produce any errors, but when I open the sheet I get an error in OpenOffice:
Err: 522 - =$#REF!.A1+$#REF!.A1+$#REF!.A1
I did a bit of research and apparently there are bugs when referencing multiple sheets. (eg https://issues.apache.org/bugzilla/show_bug.cgi?id=46670) Does anyone have an idea how to use formulas using multiple sheets in POI?
-------------- source code -------------------
public static void main(String args[]){
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Total");
Row row = sheet.createRow((short)0);
Cell cell = row.createCell(0);
cell.setCellFormula("a!A1+b!A1+c!A1");
Sheet sheet1 = wb.createSheet("a");
Sheet sheet2 = wb.createSheet("b");
Sheet sheet3 = wb.createSheet("c");
Sheet sheet4 = wb.createSheet("d");
createVal(sheet1, createHelper, 5);
createVal(sheet2, createHelper, 10);
createVal(sheet3, createHelper, 15);
createVal(sheet4, createHelper, 20);
try {
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
System.out.println("done");
} catch (IOException e) {
e.printStackTrace();
} }
public static void createVal(Sheet sheet, CreationHelper createHelper, int i){
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
// Or do it on one line.
row.createCell(0).setCellValue(i);
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如果您将“总计”工作表的创建移至填充各个工作表之后,您的代码将正常工作。
如下所示:
单元格
Total!A1
显示30
Your code will work fine if you move the creaton of "Total" sheet to after populating the individual sheets.
Like below:
Cell
Total!A1
shows30