java - Apache POI 在 xlsx 文件的单元格中设置值，但不在处理条中确认它们

Question

我正在尝试使用 java 和 POI 将值写入 xlsx 文件。它几乎按我想要的方式工作，但我有以下问题:我的程序写入 xlsx 文件的值显示在那里，但 Excel 不使用我的 VLOOKUP 并显示 #N/A，直到我进入处理条并按 Enter，然后就可以了..

这是我的代码:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;

public class XLSXEditor {

    public XLSXEditor(){

    }

    public void xlsxWriter(String path, String uName, Date date/*, String timeF, String timeT*/) throws FileNotFoundException, IOException{
        File excel = new File(path);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook book = new XSSFWorkbook(fis);
        XSSFSheet sheet = book.getSheetAt(1);
        int r = getNonBlankRowCount(path);
        XSSFRow row = sheet.getRow(r);

        //Datum Style
        CellStyle csDate = book.createCellStyle();
        CreationHelper createHelper = book.getCreationHelper();
        csDate.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
        csDate.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        csDate.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csDate.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        csDate.setFillPattern(CellStyle.SOLID_FOREGROUND);

        //uname style
        CellStyle csUname = book.createCellStyle();
        csUname.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderTop(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        csUname.setBorderRight(XSSFCellStyle.BORDER_THIN);
        csUname.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
        csUname.setFillPattern(CellStyle.SOLID_FOREGROUND);


        //fill username column in xlsx file
        XSSFCell celluName = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
        while(celluName != null){
            r++;
            row = sheet.createRow(r);
            celluName = row.createCell(0);
        }
        if(celluName == null){

            celluName = sheet.getRow(r).createCell(0);
            celluName.setCellStyle(csUname);
        }
        celluName.setCellValue(uName);

        //fill date column in xlsx file
        XSSFCell cellDate = row.getCell(5, Row.RETURN_BLANK_AS_NULL);
        while(cellDate != null){
            r++;
            row = sheet.createRow(r);
            cellDate = row.getCell(5);
        }
        if(cellDate == null){
            cellDate = sheet.getRow(r).createCell(5);
            cellDate.setCellValue(date);
            cellDate.setCellStyle(csDate);
        }

        //fill PNr column in xlsx file
        int r1 = r+1;
        XSSFCell cellPNr = row.getCell(1, Row.RETURN_BLANK_AS_NULL);
        cellPNr = sheet.getRow(r).createCell(1);
        cellPNr.setCellValue("=SVERWEIS(A"+r1+";'MA Stamm'!$A$2:$E$98;2;FALSCH)");

        FileOutputStream fos = new FileOutputStream(path);
        book.write(fos);
        fos.flush();
        fos.close();
    }
    public int getNonBlankRowCount(String path) throws IOException{
        File excel = new File(path);
        FileInputStream fis = new FileInputStream(excel);
        XSSFWorkbook book = new XSSFWorkbook(fis);
        XSSFSheet sheet = book.getSheetAt(1);
        int rowCount = 0;
        int index = book.getSheetIndex(sheet);
        if(index==-1){
            rowCount=-1;
            return rowCount;
        }
        else{
            sheet=book.getSheetAt(index);
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowCount=0;
            while(rowIterator.hasNext()){
                Row row = (Row) rowIterator.next();
                XSSFCell cell =(XSSFCell) row.getCell(0, Row.RETURN_BLANK_AS_NULL);
                if(cell == null){
                    break;
                }
                rowCount++;
            }
            return rowCount;
        }
    }
}

Answer 1

公式单元格内容与 Excel 中的文本单元格内容根本不同。您的代码行:

cellPNr.setCellValue("=SVERWEIS(A"+r1+";'MA Stamm'!$A$2:$E$98;2;FALSCH)");

设置字符串单元格值而不是公式。使用Cell.setCellFormula而不是设置公式单元格内容。

由于 Excel 在其文件中仅存储采用 English_US 表示法的公式，因此您需要使用公式的 English_US 表示法:

cellPNr.setCellFormula("VLOOKUP(A"+r1+",'MA Stamm'!$A$2:$E$98,2,FALSE)");

English_US 表示法表示英文函数名称(例如 VLOOKUP 而不是 SVERWEIS)、英文常量名称(例如 FALSE 而不是FALSCH)，逗号作为参数分隔符(例如 VLOOKUP(A1,F1:G100,2,FALSE) 而不是 VLOOKUP(A1;F1:G100; 2;FALSE))和点作为浮点文字中的小数分隔符(例如 SUM(1.23, 3.45, 4) 而不是 SUMME(1,23; 3, 45; 4).

打开文件后会在本地化的 Excel GUI 版本中进行本地化。

然后你需要某种 Formula Recalculation 。最简单的方法是通过设置将重新计算委托(delegate)给 Excel:

book.setForceFormulaRecalculation(true);

在写出书之前。