setSharedFormula能支持其他sheet2中的公式吗

Aspose.Cells Product Family
1

package test;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import com.aspose.cells.Cells;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;

public class Test {

public static void main(String[] args) throws Exception {
	FileInputStream fis = new FileInputStream("c:/test/test.xlsx");
	FileOutputStream fos = new FileOutputStream("c:/test/test-out.xlsx");
	Workbook workbook = new Workbook(fis);
	Cells cells = workbook.getWorksheets().get(0).getCells();
	cells.get("B2").setSharedFormula("=A2*0.09", 2, 1);
	// 此时B3=A3*0.09
	cells.get("C2").setSharedFormula("=Sheet2!A2", 2, 1);
	// 此时C3=Sheet2!A2，但是我想要的结果为C3=Sheet2!A3
	workbook.save(fos, SaveFormat.XLSX);
	workbook.dispose();
	fis.close();
	fos.close();
}

}

我用poi解决了该问题，aspose.cells有类似的功能吗？
/*****************************************************************************************************************/
package test;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.RefPtgBase;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Poi {

public static void main(String[] args) throws Exception {
	FileInputStream fis = new FileInputStream("c:/test/test.xlsx");
	FileOutputStream fos = new FileOutputStream("c:/test/test-out.xlsx");
	XSSFWorkbook book = new XSSFWorkbook(fis);
	XSSFSheet sheet = book.getSheetAt(0);
	XSSFCell cell = sheet.getRow(0).getCell(0);
	XSSFCell cellTarget = sheet.createRow(1).createCell(0);
	if (cell.getCellType() == CellType.FORMULA) {
		copyFormula(cell, cellTarget, book);
	}
	book.setForceFormulaRecalculation(true);
	book.write(fos);
	book.close();
	fos.close();
	fis.close();
}

/**
 * 
 * @param srcCell 复制源
 * @param destCell 复制目标
 * @param book
 * @date 2021年8月14日 上午11:31:50
 */
public static void copyFormula(Cell srcCell, Cell destCell, XSSFWorkbook book) {
	// 复制相对引用的公式
	// srcCell复制源，destCell 复制目标

	String formula = srcCell.getCellFormula();
	EvaluationWorkbook ew;
	FormulaRenderingWorkbook rw;
	Ptg[] ptgs;

	ew = XSSFEvaluationWorkbook.create((XSSFWorkbook) book);
	// 最后一个参数是从0开始指定工作表编号
	ptgs = FormulaParser.parse(formula, (XSSFEvaluationWorkbook) ew, FormulaType.CELL, 0);
	rw = (XSSFEvaluationWorkbook) ew;

	for (Ptg ptg : ptgs) {
		// 计算坐标
		int shiftRows = destCell.getRowIndex() - srcCell.getRowIndex();
		int shiftCols = destCell.getColumnIndex() - srcCell.getColumnIndex();

		if (ptg instanceof RefPtgBase) {
			RefPtgBase ref = (RefPtgBase) ptg;

			if (ref.isColRelative()) {
				ref.setColumn(ref.getColumn() + shiftCols);
			}

			if (ref.isRowRelative()) {
				ref.setRow(ref.getRow() + shiftRows);
			}

		} else if (ptg instanceof AreaPtg) {
			AreaPtg ref = (AreaPtg) ptg;

			if (ref.isFirstColRelative()) {
				ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
			}

			if (ref.isLastColRelative()) {
				ref.setLastColumn(ref.getLastColumn() + shiftCols);
			}

			if (ref.isFirstRowRelative()) {
				ref.setFirstRow(ref.getFirstRow() + shiftRows);
			}

			if (ref.isLastRowRelative()) {
				ref.setLastRow(ref.getLastRow() + shiftRows);
			}
		}
	}
	destCell.setCellFormula(FormulaRenderer.toFormulaString(rw, ptgs));
}

}

2

@baicai,

You are right, shared formula feature does not accurately when referring other worksheet cells in the formula string. To cope with it, you may use Range.copy() method to accomplish your task easily. See the updated code segment for your reference.
e.g.
Sample code:

.......
Cells cells = workbook.getWorksheets().get(0).getCells();
cells.get("B2").setSharedFormula("=A2*0.09", 2, 1);
// 此时B3=A3*0.09

cells.get("C2").setFormula("=Sheet2!A2");
Range sourceRange = cells.createRange("C2:C2");
Range destRange = cells.createRange("C3:C9");//set your desired detination range of cells.
destRange.copy(sourceRange);
.......

Hope, this helps a bit.

3

谢谢！你的回答解决了我的问题。

4

@baicai,

不客气。 很高兴知道建议的代码段可以满足您的需求。