我编写了一个程序，将值写入xlsx文件。乍一看，它似乎可以正常工作。但是在我的xlsx文件中，如果我手动输入该值，则另一个单元格中的公式应该可以正常工作，但是如果我的程序设置的值完全相同，则它将无法正常工作。如果我在程序写入值后手动打开文件，然后通过按处理栏中的Enter键确认写入的值，则它的工作原理应与预期相同。没有抛出异常。我的程序正在写入单元格N4和O4。这是我正在谈论的单元格的Excel公式：
= + 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代码：
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();
}
}
in the picture u can see the columns, my programm is working with. I needed to cut out the other columns because of the german data security policy.. the columns are filled with strings
最佳答案
在您的代码部分之后：
...
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。如果
String timeF是格式为“ HH：MM：SS”的字符串，则DateUtil.convertTime可用于将该字符串转换为Excel有价值的时间。完整的示例显示了哪些无效以及哪些有效：
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));
...
应该管用。