Java Poi向下填充公式

忘是亡心i 2023-10-06 18:58 40阅读 0赞

![20210814123102786.png][]

向下拉一行后，公式跟随改变

![20210814123257553.png][]

VBA公式自动向下填充方法：

Selection.AutoFill Destination:=Range(Cells(0, 0), Cells(1, 0))

java poi excel公式自动向下填充方法：

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/poi.xlsx");
    		FileOutputStream fos = new FileOutputStream("c:/test/poi-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));
    	}
    
    }

使用aspose.cells实现下拉填充公式

package test;
    
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    
    import com.aspose.cells.Cells;
    import com.aspose.cells.Range;
    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", 5, 1);//下拉4行
    		//setSharedFormula不支持其他工作表单元格
    		
    		
    		//如果公式引用了其他工作表单元格，需要使用这种方式
    		cells.get("C2").setFormula("=LAN1!A1");
    		Range sourceRange = cells.createRange("C2:C2");
    		Range destRange = cells.createRange("C3:C9");//设置所需的单元格范围。下拉7行
    		destRange.copy(sourceRange);
    		
    		
    		workbook.save(fos, SaveFormat.XLSX);
    		workbook.dispose();
    		fis.close();
    		fos.close();
    	}
    }

[20210814123102786.png]: https://img-blog.csdnimg.cn/20210814123102786.png
[20210814123257553.png]: https://img-blog.csdnimg.cn/20210814123257553.png