Apache-POI在Excel中设置值，但是另一个单元格的公式无法使用该值，除非我在处理栏中手动按Enter [英] Apache-POI sets values in Excel, but the formula of another cell is unable to work with the value until I manually press enter in the processing strip
问题描述
我编写了一个程序，将值写入xlsx文件.乍一看，它似乎可以正常工作.但是在我的xlsx文件中，如果我手动输入该值，则另一个单元格中的公式应该可以正常工作，但是如果我的程序设置的值完全相同，则它将无法正常工作.如果我在程序写入一个值后手动打开文件，然后按处理栏中的Enter按钮确认写入的值，则它的工作原理应与预期相同.没有抛出异常.我的程序正在写入单元格N4和O4.这是我正在谈论的单元格的Excel公式:
I wrote a programm, that writes values into a xlsx-file. At the first glance it seems to work like it should. But in my xlsx-file I've got a Formula in another cell that should works correct if I type in the value manually, but if my programm sets exactly the same value it doesn't work like it should. If I open the file manually after my programm wrote a value and confirm the wrote value by pressing the enter button in the processing strip it works like it should. There are no Exceptions thrown. My programm is writing in the cells N4 and O4. Here is the Excel Formula of the cell I'm talking about:
= + MAX(; MIN(O4 +(N4> O4); MAX(($ N $ 2> $ O $ 2); $ O $ 2))-MAX(N4; $ N $ 2))+ MAX(; MIN( O4; MAX(($ N $ 2> $ O $ 2); $ O $ 2))-MAX(; $ N $ 2))(N4> O4)+ MAX(; MIN(O4 +(N4> O4); MIN ((($ N $ 2> $ O $ 2); $ O $ 2))-MAX(N4;))+ MIN(O4; MIN(($$$ 2> $ O $ 2); $ O $ 2))( N4> O4)
=+MAX(;MIN(O4+(N4>O4);MAX(($N$2>$O$2);$O$2))-MAX(N4;$N$2))+MAX(;MIN(O4;MAX(($N$2>$O$2);$O$2))-MAX(;$N$2))(N4>O4)+MAX(;MIN(O4+(N4>O4);MIN(($N$2>$O$2);$O$2))-MAX(N4;))+MIN(O4;MIN(($N$2>$O$2);$O$2))(N4>O4)
N2和O2包括预选值，这些值与N4和O4的值进行比较. 这是我的Java代码:
N2 and O2 include preselected Values, that are compared with the values of N4 and O4. And here is my Java code:
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.text.SimpleDateFormat;
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 javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JTable;
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 searchWriter(String path, JTable t1) 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);
String uname = "404";
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date parsed = null;
java.sql.Date date = null;
//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);
//time style
CellStyle csTime = book.createCellStyle();
csTime.setBorderTop(XSSFCellStyle.BORDER_THIN);
csTime.setBorderBottom(XSSFCellStyle.BORDER_THIN);
csTime.setBorderLeft(XSSFCellStyle.BORDER_THIN);
csTime.setBorderRight(XSSFCellStyle.BORDER_THIN);
csTime.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
csTime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm"));
csTime.setFillPattern(CellStyle.SOLID_FOREGROUND);
csTime.setVerticalAlignment(CellStyle.ALIGN_CENTER);
csTime.setAlignment(CellStyle.ALIGN_CENTER);
System.out.println(getFilledRows(t1));
for(int i=0; i<getFilledRows(t1); i++){
System.out.println(r);
XSSFRow row = sheet.getRow(r);
//fill username column in xlsx file
XSSFCell celluName = row.getCell(0, Row.RETURN_BLANK_AS_NULL);
if(celluName != null){
r++;
}
if(celluName == null){
celluName = sheet.getRow(r).createCell(0);
celluName.setCellStyle(csUname);
uname = t1.getValueAt(i, 0).toString().charAt(0) +""+ t1.getValueAt(i, 1);
celluName.setCellValue(uname);
r++;
}
r--;
//fill date column in xlsx file
XSSFCell cellDate = row.getCell(5, Row.RETURN_BLANK_AS_NULL);
System.out.println("r = " + r);
cellDate = sheet.getRow(r).createCell(5);
cellDate.setCellStyle(csDate);
try{
parsed = sdf.parse(t1.getValueAt(i, 2).toString());
}
catch(Exception e){
System.out.println(e);
}
date = new java.sql.Date(parsed.getTime());
cellDate.setCellValue(date);
//fill zeit von
if(!(t1.getValueAt(i, 6).toString().equals("Standby not activated"))){
XSSFCell cellTimeF = row.getCell(13, Row.RETURN_BLANK_AS_NULL);
cellTimeF = sheet.getRow(r).createCell(13);
String tf = t1.getValueAt(i, 3).toString();
String timeF = tf.substring(0, 5);
cellTimeF.setCellValue(timeF);
cellTimeF.setCellStyle(csTime);
}
//fill zeit bis
if(!(t1.getValueAt(i, 6).toString().equals("Standby not activated"))){
XSSFCell cellTimeT = row.getCell(14, Row.RETURN_BLANK_AS_NULL);
cellTimeT = sheet.getRow(r).createCell(14);
String tt = t1.getValueAt(i, 4).toString();
String timeT = tt.substring(0, 5);
cellTimeT.setCellValue(timeT);
cellTimeT.setCellStyle(csTime);
}
//set crosses
XSSFCell cellStandbyP = row.getCell(7, Row.RETURN_BLANK_AS_NULL);
XSSFCell cellStandbyA = row.getCell(8, Row.RETURN_BLANK_AS_NULL);
XSSFCell cellSpecial = row.getCell(9, Row.RETURN_BLANK_AS_NULL);
if(t1.getValueAt(i, 6).equals("Standby not activated")){
cellStandbyP = sheet.getRow(r).createCell(7);
cellStandbyP.setCellStyle(csUname);
cellStandbyP.setCellValue("x");
}
if(t1.getValueAt(i, 6).equals("Planned work")){
cellSpecial = sheet.getRow(r).createCell(9);
cellSpecial.setCellStyle(csUname);
cellSpecial.setCellValue("x");
}
if(t1.getValueAt(i, 6).equals("Standby Activated")){
cellStandbyA = sheet.getRow(r).createCell(8);
cellStandbyA.setCellStyle(csUname);
cellStandbyA.setCellValue("x");
}
r++;
}
FileOutputStream fos = new FileOutputStream(path);
book.setForceFormulaRecalculation(true);
book.write(fos);
fos.flush();
fos.close();
}
}
在图片中您可以看到各列，我的程序正在使用.由于德国的数据安全政策，我需要删掉其他列..这些列中都填充了字符串
推荐答案
在代码部分之后:
...
String timeF = tf.substring(0, 5);
cellTimeF.setCellValue(timeF);
...
单元格内容将始终是字符串(文本)单元格内容.此内容是功能MIN和MAX无法使用的.这些功能需要数字内容.在Excel中，日期和时间也是数字内容，仅格式化为日期时间.默认设置为1 = 1天= 01/01/1900 00:00:00. 1小时= 1/24，1分钟= 1/24/60，1秒= 1/24/60/60.
the cell content will always be string (text) cell content. This content the functions MINand MAX cannot work with. Those functions needs numeric content. In Excel date and time also is numeric content only formatted as date-time. With default settings 1 = 1 day = 01/01/1900 00:00:00. 1 hour = 1/24, 1 minute = 1/24/60, 1 second = 1/24/60/60.
如果String timeF是格式为"HH:MM:SS"的字符串，则
If String timeF is string of format "HH:MM:SS", then DateUtil.convertTime can be used to convert this string into a Excel valuable time.
一个完整的示例，该示例显示哪些无效以及哪些有效:
Complete example which shows what not works and what works:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
public class ExcelCalculateTimeValues {
public static void main(String[] args) throws Exception {
Workbook workbook = new XSSFWorkbook();
CreationHelper createHelper = workbook.getCreationHelper();
CellStyle styletime = workbook.createCellStyle();
styletime.setDataFormat(createHelper.createDataFormat().getFormat("hh:mm:ss"));
Sheet sheet = workbook.createSheet();
sheet.createRow(0).createCell(0).setCellValue("Start");
sheet.getRow(0).createCell(1).setCellValue("End");
sheet.getRow(0).createCell(3).setCellValue("Start");
sheet.getRow(0).createCell(4).setCellValue("End");
String[][] tableData = new String[][]{
{"12:34:00", "22:45:00"},
{"23:45:00", "01:34:00"},
{"08:01:00", "13:23:00"}
};
int r = 1;
for (String[] rowData : tableData) {
Row row = sheet.createRow(r++);
int c = 0;
for (String cellData : rowData) {
Cell cell = row.createCell(c);
cell.setCellValue(cellData); //this sets string cell data
cell.setCellStyle(styletime);
cell = row.createCell(3 + c++);
cell.setCellValue(DateUtil.convertTime(cellData)); //this sets datetime cell data
cell.setCellStyle(styletime);
}
}
sheet.createRow(r).createCell(0).setCellFormula("MIN(A2:A4)"); //cannot work because of string values in A2:A4
sheet.getRow(r).createCell(1).setCellFormula("MIN(B2:B4)"); //cannot work because of string values in B2:B4
sheet.getRow(r).createCell(3).setCellFormula("MIN(D2:D4)"); //will work
sheet.getRow(r).createCell(4).setCellFormula("MIN(E2:E4)"); //will work
workbook.setForceFormulaRecalculation(true);
workbook.write(new FileOutputStream("ExcelCalculateTimeValues.xlsx"));
workbook.close();
}
}
使用您的代码:
...
cellTimeF.setCellValue(DateUtil.convertTime(timeF));
...
和
...
cellTimeT.setCellValue(DateUtil.convertTime(timeT));
...
应该工作.
这篇关于Apache-POI在Excel中设置值，但是另一个单元格的公式无法使用该值，除非我在处理栏中手动按Enter的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
