CODEWITHSUNDEEP
javaapacheexcelapache-poixls
raulgomis
raulgomis

Reputation: 115

Excel POI API changing cell value

I have to read a excel file and fill some cells with POI API for JAVA. The I write the whole workbook into another. That is actually working.

The problem is that then (once the cells are filled) I have to open the file and click and enter in the cells I've modified in order to refresh the values of the other formula's cells.

The code is like that:

cellHorasPrevistas.setCellValue("01:00:00")

When I enter in the xls file it seems to be a raw String instead of being formatted as "[h]:mm:ss" which was the initial format of the cell.

I have tried to rewrite the format manually, but it didn't work:

    HSSFCellStyle cs = wb.createCellStyle();
    HSSFDataFormat df = wb.createDataFormat();
    cs.setDataFormat(df.getFormat("[h]:mm:ss"));
    cellHorasPrevistas.setCellStyle(cs);
    cellHorasPrevistas.setCellValue("01:00:00")

I have also tried with no lucky

HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); 
fe.clearAllCachedResultValues()
fe.notifyUpdateCell(cellHorasPrevistas)
fe.evaluate(cellHorasPrevistas)

I must be quite easy, just to change a cell value a get that cell the same if I changed it manually in the xls.

Thanks in advance, Raúl

Found a partial explanation....The problem is that de CellType is changing from number to String and consequently excel cannot use it in formulas. I want to mantain the number format setting the value as "12:00:00" and not to 0.5. Is it possible?

Upvotes: 1

Views: 15165

Answers (2)

J.Barnett
J.Barnett

Reputation: 13

Firstly to update your formulas so you don't have to go into the file to keep hiting enter:

for .xlsx files

XSSFWorkbook workbook= new XSSFWorkbook(FilePath);
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

for .xls files

HSSFWorkbook workbook= new HSSFWorkbook(FilePath);
HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

For putting time into excel I created a simple class file, so you can very easily just change all the inputs for the rows and columns. This is however for XSSF, but that shouldn't be an issue to change to HSSF.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class test {

    public static void main(String [] args) {
        System.out.println(Time());
    }

    public static String Time () {
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat s = new SimpleDateFormat("HH:mm:ss"); //Changeable
        return s.format(cal.getTime()).toString();
    }

    public void addtoExcel() throws IOException {
        InputStream ExcelFileToRead = new FileInputStream(file_path);
        XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);
        XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
        XSSFSheet sheet = wb.getSheetAt(0);

        sheet.createRow(rowIndex).createCell(columnIndex).setCellValue(Time());

        FileOutputStream out =  new FileOutputStream(new File(file_path));                
        wb.write(out);
        out.close();
    }
}

Upvotes: 0

oers
oers

Reputation: 18714

You could try using a Date or Calendar-Object to set the value and not a String. You may have to create a Dummy-Object, that only has the appropriate time set .

I had to set the YEAR to 1970, Month to January and Day to 1, to make it work like this:

  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFRow row = wb.createSheet().createRow(0);
  CellStyle cellStyle = wb.createCellStyle();
  cellStyle.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("HH:mm:ss"));
  HSSFCell cell = row.createCell(1);
  Calendar cal = Calendar.getInstance();
  cal.set(Calendar.YEAR, 1970);
  cal.set(Calendar.MONTH, 0);
  cal.set(Calendar.DATE, 1);
  //you can set the time you need here ...
  cell.setCellValue(cal);
  cell.setCellStyle(cellStyle);

Result:

enter image description here

If I left the YEAR/MONTH/DATE part normal, the hours would be all the Hours since January, 1st 1970.

Upvotes: 3

Related Questions