SUM(ABOVE) functionality in apache poi or docx4j

I am trying to implement =SUM(ABOVE) function in docx which is used to sum all elements above to tht column. I was able to implement this with apache poi using :

    CTSimpleField sumAbove = paragraphInCell.getCTP().addNewFldSimple();
  sumAbove.setInstr("=SUM(ABOVE)");
  //set sum field dirty, so it must be calculated while opening the document
  sumAbove.setDirty(STOnOff.TRUE);

This is ok when someone opens the document and it calculates it. But if I need to convert the document without opening it to pdf then this function doesnt run. Aspose has function called :

Document.UpdateFields

which does the required functionality but this is paid application.

Can we implement the same functionality using apche poi or docx4j

Answer

If you don’t want set the field dirty, then you need calculating the sum your own. Following is a working draft for a method to calculate the sum of table cell values of a special column of a XWPFTable:

 Double calculateSum(XWPFTable table, int col) {
  Double result = null;
  for (XWPFTableRow row : table.getRows()) {
   if (row.getTableCells().size() > col) {
    XWPFTableCell cell = row.getCell(col);
    String cellContent = cell.getText();
    try {
     Number cellValue = java.text.NumberFormat.getInstance().parse(cellContent);
     if (result == null) result = 0d;
     result += cellValue.doubleValue();
    } catch(Exception ex) {
     //could not parse text to number
     //ex.printStackTrace();
    }
   }
  }
  return result;
 }

If you have the sum, then CTSimpleField needs to get a text run having the sum set as text value. Then this the same as Word does to set cached value for the field.

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.xwpf.usermodel.*;

import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTSimpleField;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STOnOff;

public class CreateWordTableSumAbove {

 static Double calculateSum(XWPFTable table, int col) {
  Double result = null;
  for (XWPFTableRow row : table.getRows()) {
   if (row.getTableCells().size() > col) {
    XWPFTableCell cell = row.getCell(col);
    String cellContent = cell.getText();
    try {
     Number cellValue = java.text.NumberFormat.getInstance().parse(cellContent);
     if (result == null) result = 0d;
     result += cellValue.doubleValue();
    } catch(Exception ex) {
     //could not parse text to number
     //ex.printStackTrace();
    }
   }
  }
  return result;
 }

 static void setText(XWPFTableCell cell, String text) {
  XWPFParagraph par = null;
  if (cell.getParagraphs().size() == 0) par = cell.addParagraph();
  else par = cell.getParagraphs().get(0);
  par.createRun().setText(text);
 }

 public static void main(String[] args) throws Exception {

  XWPFDocument document= new XWPFDocument();

  XWPFParagraph paragraph = document.createParagraph();
  XWPFRun run=paragraph.createRun();  
  run.setText("The table:");

  //create the table
  XWPFTable table = document.createTable(4,3);
  table.setWidth("100%");
  for (int row = 0; row < 3; row++) {
   for (int col = 0; col < 3; col++) {
    if (col < 2) {
     setText(table.getRow(row).getCell(col), "row " + row + ", col " + col);
    } else {
     setText(table.getRow(row).getCell(col), "" + ((row + 1) * 1234));
    }
   }
  }

  //set Sum row
  setText(table.getRow(3).getCell(0), "Sum:");

  //get paragraph from cell where the sum field shall be contained
  XWPFParagraph paragraphInCell = null;
  if (table.getRow(3).getCell(2).getParagraphs().size() == 0) paragraphInCell = table.getRow(3).getCell(2).addParagraph();
  else paragraphInCell = table.getRow(3).getCell(2).getParagraphs().get(0);

  //set sum field in
  CTSimpleField sumAbove = paragraphInCell.getCTP().addNewFldSimple();
  sumAbove.setInstr("=SUM(ABOVE)");
  Double sum = calculateSum(table, 2);
System.out.println(sum);
  if (sum != null) {
   //if there is a sum, set that sum to be the cached result of the field
   sumAbove.addNewR().addNewT().setStringValue(new java.text.DecimalFormat("#.#").format(sum));
  } else {
   //set sum field dirty, so it must be calculated while opening the document
   sumAbove.setDirty(STOnOff.TRUE);
  }

  paragraph = document.createParagraph();

  FileOutputStream out = new FileOutputStream("create_table.docx"); 
  document.write(out);
  out.close();
  document.close();
 }
}

If the Word-to-PDF converter don’t read the value, then it is incomplete. Of course you could stop using the field and put the calculated sum directly into the table cell. But then updating the sum using Word is not possible since there is not a field anymore.