Apache POI setCellType未正确设置数值的单元格类型

Question

我正试图将一些数据导出到Excel文件中。我在用POI。

据我所知，Cell.SetCellType（double）设置适当的excel等效数值。但这不会改变细胞的类型。

打开生成的excel文件时的列类型是开始选项卡中的General。

package com.example;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcelMain {
    public static void main(String[] args) throws Exception {
        System.out.println("started");

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        cell.setCellValue(3.14159);

        workbook.write(new FileOutputStream("Test.xlsx"));

        System.out.println("finished");
    }
}

package com.example;

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcelMain {
    public static void main(String[] args) throws Exception {
        System.out.println("started");

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);

        cell.setCellValue(3.14159);
        cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);

        workbook.write(new FileOutputStream("Test.xlsx"));

        System.out.println("finished");
    }
}

编辑

正如Jim Garrison回答的那样，设置单元格样式确实会将类型改为数字。下面是工作代码。

package com.example;

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcelMain {
    public static void main(String[] args) throws Exception {
        System.out.println("started");

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);

        cell.setCellValue(3.14159);
        cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);

        XSSFDataFormat format = workbook.createDataFormat();
        XSSFCellStyle style = workbook.createCellStyle(); 
        style.setDataFormat(format.getFormat("0.0"));

        cell.setCellStyle(style);

        workbook.write(new FileOutputStream("Test.xlsx"));

        System.out.println("finished");
    }
}

Answer 1

从Javadoc forsetcellvalue（双值）（强调是我的）：

value-要将此单元格设置为的数值。对于公式，我们将设置预先计算的值，对于数值，我们将设置它的值。对于其他类型，我们将把单元格更改为数字单元格并设置其值。

因此，无论以前的单元格是什么，该方法都会将单元格类型更改为numeric。