Apache POI:Как обновить Excel-файл со многими формулами
Почему в новом файле значение ячейки D5: #DIV/0!
-- Program output:
Formula is: C34
1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886
Formula is: C34
-- Cell values:
Value of the cell D5 in file excelFileOrig.xlsm is: 407,25
Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0!
Value of the cell C8 in file excelFileOrig.xlsm is: 5,0
Value of the cell D5 in file excelFileNew.xlsm is: 15,0
-- Code:
Код:
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class POITestRB {
static String excelFileOrig = "C:/Test/excelFileOrig.xlsm";
static String excelFileNew = "C:/Test/excelFileNew.xlsm";
static FileInputStream fis;
static XSSFWorkbook workbook;
public static void main(String[] args) throws IOException {
fis = new FileInputStream(excelFileOrig);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
updateCell(workbook, 15.0);
fis.close();
workbook.close();
fis = new FileInputStream(excelFileNew);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
}
private static void gettingCellContents(XSSFWorkbook workbook, String cellId) {
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference(cellId);
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
switch (cell.getCellTypeEnum()) {
case STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case FORMULA:
System.out.println("Formula is: " + cell.getCellFormula());
switch(cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.println("1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println("2. case Cell.CELL_TYPE_STRING --> Last evaluated as "" + cell.getRichStringCellValue() + """);
break;
}
break;
case BLANK:
System.out.println();
break;
default:
System.out.println();
}
}
public static void updateCell(XSSFWorkbook workbook, Double newData) {
try {
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference("C8");
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
if (cell != null) {
cell.setCellValue(newData);
}
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
OutputStream os = new FileOutputStream(excelFileNew);
workbook.write(os);
os.flush();
os.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class POITestRB {
static String excelFileOrig = "C:/Test/excelFileOrig.xlsm";
static String excelFileNew = "C:/Test/excelFileNew.xlsm";
static FileInputStream fis;
static XSSFWorkbook workbook;
public static void main(String[] args) throws IOException {
fis = new FileInputStream(excelFileOrig);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
updateCell(workbook, 15.0);
fis.close();
workbook.close();
fis = new FileInputStream(excelFileNew);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
}
private static void gettingCellContents(XSSFWorkbook workbook, String cellId) {
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference(cellId);
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
switch (cell.getCellTypeEnum()) {
case STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case FORMULA:
System.out.println("Formula is: " + cell.getCellFormula());
switch(cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.println("1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println("2. case Cell.CELL_TYPE_STRING --> Last evaluated as "" + cell.getRichStringCellValue() + """);
break;
}
break;
case BLANK:
System.out.println();
break;
default:
System.out.println();
}
}
public static void updateCell(XSSFWorkbook workbook, Double newData) {
try {
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference("C8");
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
if (cell != null) {
cell.setCellValue(newData);
}
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
OutputStream os = new FileOutputStream(excelFileNew);
workbook.write(os);
os.flush();
os.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
Чтобы в новом файле все результаты пересчитались по новому, с учётом внесённых изменений...
Проблема теперь с выводом значения ячейки в новом файле:
-------------------------------------------------------------------------------------------------
Formula is: C34
cell.getCachedFormulaResultType(): 0
0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886
Formula is: C34
cell.getCachedFormulaResultType(): 5
5. case Cell.CELL_TYPE_ERROR -->
-------------------------------------------------------------------------------------------------
Почему выводит CELL_TYPE_ERROR ?
Новый файл хранит новые значения всех ячеек.
Что надо ещё добавить в коде?
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
public class POITestRB {
static String excelFileOrig = "C:/Test/1.xlsm";
static String excelFileNew = "C:/Test/excelFileNew.xlsm";
static FileInputStream fis;
static XSSFWorkbook workbook;
public static void main(String[] args) throws IOException {
fis = new FileInputStream(excelFileOrig);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
updateCell(workbook, 10.0);
fis.close();
workbook.close();
fis = new FileInputStream(excelFileNew);
workbook = new XSSFWorkbook(fis);
gettingCellContents(workbook, "D5");
fis.close();
workbook.close();
}
private static void gettingCellContents(XSSFWorkbook workbook, String cellId) {
workbook.setForceFormulaRecalculation(true);
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference(cellId);
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
switch (cell.getCellTypeEnum()) {
case STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case FORMULA:
System.out.println("Formula is: " + cell.getCellFormula());
System.out.println("cell.getCachedFormulaResultType(): " + cell.getCachedFormulaResultType());
switch(cell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.println("0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println("4. case Cell.CELL_TYPE_STRING --> Last evaluated as "" + cell.getRichStringCellValue() + """);
break;
case Cell.CELL_TYPE_ERROR:
System.out.println("5. case Cell.CELL_TYPE_ERROR --> ");
break;
}
break;
case BLANK:
System.out.println();
break;
default:
System.out.println("default");
}
}
public static void updateCell(XSSFWorkbook workbook, Double newData) {
try {
XSSFSheet sheet = workbook.getSheetAt(1);
CellReference ref = new CellReference("C8");
int row = ref.getRow();
int col = ref.getCol();
Cell cell = sheet.getRow(row).getCell(col);
if (cell != null) {
cell.setCellValue(newData);
}
workbook.getCreationHelper().createFormulaEvaluator().clearAllCachedResultValues();
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
OutputStream os = new FileOutputStream(excelFileNew);
workbook.write(os);
os.flush();
os.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}