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(($N$2>$O$2);$O$2))(N4>O4)
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();
}
}
After your code part:
...
String timeF = tf.substring(0, 5);
cellTimeF.setCellValue(timeF);
...
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.
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();
}
}
So with your code :
...
cellTimeF.setCellValue(DateUtil.convertTime(timeF));
...
and
...
cellTimeT.setCellValue(DateUtil.convertTime(timeT));
...
should work.