ApachePOI如何使用JavaPOI从公式中获取当前单元格引用?我想相应地更新它们
例如,有一个公式是“VLOOKUP($B2,'03 meest recente 格格文$A:$V,5,0“
我想把这个公式拖到某一行,这样做我需要 当前行位置,并向其中添加一个$B2到$B3,依此类推 每当我调用copyFormula方法时,它都在复制,但公式 正在换上VLOOKUP(B2美元,最近03次会议) 格格文$A:$V,5,0)改为VLOOKUP($B3,'03 meest recente 格格文!A:V,5,0) 您可以看到“$”符号正在更改。 因此,我创建了另一个方法dragFormula(),其中我使用regex 获取行号,每行增加1 有没有其他方法可以解决这个问题,因为我所做的是。。。 看起来很静态,因为假设您想拖动到公式下面 =日期(左(G2,4)、中(G2,5,2)、右(G2,2)) G2需要根据行和我的绘图公式(args..)进行调整方法可以做到这一点。。。。。但是,这可以通过copyFormula(args..)实现方法我感到困惑的是,有时需要复制公式法,有时需要拖拉公式法 如何解决这个问题帮助。。。先谢谢你
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.Area3DPtg;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class DragFormula {
public static void main(String[] args) {
DragFormula from = new DragFormula();
File file = new File("C:\\Users\\mayank.kumar01\\Desktop\\Project\\Mailmerge\\format_mailmerge_20140527(template).xlsx");
try{
OPCPackage pkg = OPCPackage.open(file);
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet2 = workbook.getSheetAt(1);
CellReference cellReference = new CellReference("F2");
Row oldRow = sheet2.getRow(cellReference.getRow());
Row newRow = sheet2.createRow(2);
Cell oldCell = oldRow.getCell(cellReference.getCol());
Cell newCell = newRow.createCell(cellReference.getCol());
newCell.setCellFormula(oldCell.getCellFormula());
String oldFormula = oldCell.getCellFormula();
System.out.println("oldFolmula : "+oldFormula);
from.copyFormula(sheet2, oldCell, newCell, 1);
from.dragFormula(oldCell, sheet2, 25);
workbook.write(new FileOutputStream(file));;
}
catch(Exception e){
e.printStackTrace();
}
finally{
System.exit(0);
}
}
private void copyFormula(Sheet sheet, Cell org, Cell dest,int inc) {
if (org == null || dest == null || sheet == null
|| org.getCellType() != Cell.CELL_TYPE_FORMULA)
return;
if (org.isPartOfArrayFormulaGroup())
return;
String formula = org.getCellFormula();
XSSFEvaluationWorkbook workbookWrapper =
XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
, sheet.getWorkbook().getSheetIndex(sheet));
for (int i=0;i<ptgs.length;i++) {
if (ptgs[i] instanceof RefPtgBase) // base class for cell references
{
RefPtgBase ref = (RefPtgBase) ptgs[i];
if (ref.isColRelative())
ref.setColumn(ref.getColumn());
if (ref.isRowRelative())
ref.setRow(ref.getRow()+inc);
}
else if (ptgs[i] instanceof Area3DPtg) // base class for range references
{
Area3DPtg ref = (Area3DPtg) ptgs[i];
}
}
formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
System.out.println("Modified Formula : "+formula);
dest.setCellFormula(formula);
}
public void dragFormula(Cell oldCell,XSSFSheet sheet,int uptoRow){
String formula = "VLOOKUP($B2,'03 meest recente gegevens'!$A:$V,5,0)";
String regex = "(\\$)?([A-Za-z]+)(\\d+)"; // creating the regex
Pattern p = Pattern.compile(regex);
int rowIndex = oldCell.getRowIndex()+1;
int inc = 1;
for(int i=0;i<=uptoRow;i++){
Row row = sheet.createRow(rowIndex++);
Matcher match = p.matcher(formula);
if(match.find()){ // matching the pattern
formula = formula.replaceFirst(match.group(3),((Integer)(Integer.parseInt(match.group(3))+inc)).toString());
}
Cell newCell = row.createCell(oldCell.getColumnIndex());
newCell.setCellFormula(formula);
System.out.println("Modified Formula"+formula);
}
}
}
共 (0) 个答案