Java Excel POI API 更改单元格值

Excel POI API changing cell value

提问by raulgomis

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.

我必须读取一个 excel 文件并用 JAVA 的 POI API 填充一些单元格。我将整个工作簿写入另一个。这实际上是有效的。

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.

当我输入 xls 文件时，它似乎是一个原始字符串，而不是被格式化为“[h]:mm:ss”，这是单元格的初始格式。

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.

我必须很容易，只要更改一个单元格值，如果我在 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?

找到了部分解释....问题是 de CellType 正在从数字更改为字符串，因此 excel 无法在公式中使用它。我想保留数字格式，将值设置为“12:00:00”而不是 0.5。是否可以？

采纳答案by oers

You could try using a Dateor 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 .

您可以尝试使用 aDate或Calendar-Object 来设置值而不是字符串。您可能必须创建一个虚拟对象，它只设置了适当的时间。

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

我必须将 YEAR 设置为 1970，将 Month 设置为 1 月，将 Day 设置为 1，以使其像这样工作：

  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:

结果：

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

如果我离开YEAR/ MONTH/DATE一部分正常的，时间是所有月份以来的时间，1970年1号。

回答by J.Barnett

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

首先更新你的公式，这样你就不必进入文件继续按回车：

for .xlsx files

对于 .xlsx 文件

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

for .xls files

对于 .xls 文件

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.

为了节省时间，我创建了一个简单的类文件，因此您可以非常轻松地更改行和列的所有输入。然而，这是针对 XSSF 的，但更改为 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();
    }
}