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();
}
}
