java - Excel 工作表删除以零开头的数字的零

Question

我需要编写 CSV/Xls 文件，其中有一列包含诸如 00078 之类的数字，但即使在将数字写入字符串后， Excel 工作表将其显示为 78。

我已经尝试过 apache poi 库并尝试将单元格类型设置为字符串。

HSSFWorkbook workbook = new HSSFWorkbook(); 

// Create a blank sheet 
HSSFSheet sheet = workbook.createSheet("student Details"); 

// This data needs to be written (Object[]) 
Map<String, Object[]> data = new TreeMap<String, Object[]>(); 
data.put("1", new Object[]{ "ID", "NAME", "LASTNAME" }); 
data.put("2", new Object[]{ 00078, "Pankaj", "Kumar" }); 

// Iterate over data and write to sheet 
Set<String> keyset = data.keySet(); 
int rownum = 0; 
for (String key : keyset) { 
    // this creates a new row in the sheet 
    Row row = sheet.createRow(rownum++); 
    Object[] objArr = data.get(key); 
    int cellnum = 0; 
    for (Object obj : objArr) { 
        // this line creates a cell in the next column of that row 
        Cell cell = row.createCell(cellnum++); 
        if (obj instanceof String) 
            cell.setCellValue((String)obj); 
        else if (obj instanceof Integer){
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(obj));
        }
    } 
} 

我希望该列将 00078 视为字符串并显示相同的内容。

Answer 1

如果ID必须有前导零，则该ID的数据类型必须是String。数字没有前导零。数字 000123 在数学上与 123 完全相同。

此外，如果是 Excel，则包含此 ID 值的单元格应具有 Text 格式的单元格样式。为了防止通过编辑单元格将单元格样式更改为数字样式，另外单元格样式应加上引号前缀。否则编辑单元格可能会导致 000123 再次变为 123。

以下代码显示了一个完整的示例，其中 ID 列以这种方式受到保护。

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.GregorianCalendar;

class CreateExcelSheetFromDataObjectArray {

 public static void main(String[] args) throws Exception {

  Object[][] data = new Object[][]{
   new Object[]{"ID", "NAME", "LASTNAME", "AMOUNT", "DATE"},
   new Object[]{"000123", "John", "Doe", 1234.56, new GregorianCalendar(2019, 0, 1) },
   new Object[]{"000456", "Jane", "Stiles", 7890.12, new GregorianCalendar(2019, 1, 11) },
   new Object[]{"000789", "Mary", "Major", 3456.78, new GregorianCalendar(2019, 2, 22) }
  };

  String filePath = "./Excel";

  String wantedXLFormat = 
   //"XSSF";
   "HSSF";

  try (Workbook workbook = ("XSSF".equals(wantedXLFormat))?new XSSFWorkbook():new HSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream(filePath + (("XSSF".equals(wantedXLFormat))?".xlsx":".xls")) ) {

   DataFormat dataFormat = workbook.createDataFormat();
   CellStyle dateStyle = workbook.createCellStyle();
   dateStyle.setDataFormat(dataFormat.getFormat("DDDD, MMMM, DD, YYYY"));
   CellStyle numberStyle = workbook.createCellStyle();
   numberStyle.setDataFormat(dataFormat.getFormat("#,##0.00 \" Coins\""));

   // the cell style for the ID column has Text format and is quote prefixed
   CellStyle idStyle = workbook.createCellStyle();
   idStyle.setDataFormat(dataFormat.getFormat("@"));
   idStyle.setQuotePrefixed(true);

   Sheet sheet = workbook.createSheet(); 

   for (int r = 0; r < data.length; r++) {
    Row row = sheet.createRow(r);
    for (int c = 0; c < data[0].length; c++) {
     Cell cell = row.createCell(c);

     if (r == 0) cell.setCellValue((String)data[r][c]); // the header row, all columns are strings

     if (r > 0 && c == 0) { // the ID column
      cell.setCellValue((String)data[r][c]);
      cell.setCellStyle(idStyle);
     } else if (r > 0 && c == 3) { // the number column
      cell.setCellValue((Double)data[r][c]);
      cell.setCellStyle(numberStyle);
     } else if (r > 0 && c == 4) { // the date column
      cell.setCellValue((GregorianCalendar)data[r][c]);
      cell.setCellStyle(dateStyle);
     } else if (r > 0) { // all other columns are strings
      cell.setCellValue((String)data[r][c]);
     }
    }
   }

   for (int c = 0; c < data[0].length; c++) {
    sheet.autoSizeColumn(c);
   }

   workbook.write(fileout);
  }
 }
}

该代码使用 apache poi 4.1.0 进行了测试。