java - 在Java中将Excel列格式从数字更改为文本

Question

我正在尝试创建一个 Excel 模板，其中所有列/单元格都作为文本。Excel 工作表有 5 列

这是我的代码:

@RequestMapping(value = "/app/downloadTemplateForAssociateBankingDetails", method = RequestMethod.GET)
public void downloadTemplateForAssociateBankingDetails(HttpServletRequest request,HttpServletResponse response) throws Exception{
  @SuppressWarnings("resource")
  HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet("AssociateBankingTemplate");
  String[] tableColumns = Constants.DATA_MIGRATION_ASSOCIATE_BANKING_EXCEL_HEADERS;
  DataFormat fmt = workbook.createDataFormat();
  CellStyle cellStyle = workbook.createCellStyle();

  Row row = sheet.createRow(0);
  int cellnum = 0;
  for (String key : tableColumns) 
  {
    Cell cell = row.createCell(cellnum++);
    cellStyle.setDataFormat(
        fmt.getFormat("@"));
    cell.setCellStyle(cellStyle);
    cell.setCellValue((String)key);

  }

  try {
    File file = new File("Associate_Banking_Template.xls");
    FileOutputStream out = new FileOutputStream(file);
    workbook.write(out);

    out.close();
    out.flush();

    String mimeType = URLConnection.guessContentTypeFromName(file.getName());
    if (mimeType == null) {
      mimeType = "application/octet-stream";
    }
    response.setContentType(mimeType);
    StringBuilder fileNameSB = new StringBuilder();
    response.setHeader("Content-Disposition", fileNameSB.append("attachment; filename=\"").append(file.getName()).append("\"").toString());
    response.setContentLength((int) file.length());
    InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
    FileCopyUtils.copy(inputStream, response.getOutputStream());
    inputStream.close();
    response.getOutputStream().close();
    response.getOutputStream().flush();
  }
  catch (Exception e) {
    e.printStackTrace();
  }

}

Answer 1

谁告诉您单元格不是文本格式的？微软Excel？ LibreOffice Calc？

以下代码适用于我，应该为您提供一个文本格式的单元格。

public class TextFormat {

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

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("Text");

    CellStyle cellStyle = workbook.createCellStyle();
    DataFormat fmt = workbook.createDataFormat();
    cellStyle.setDataFormat(fmt.getFormat("@"));
    cell.setCellStyle(cellStyle);

    OutputStream fileOut = new FileOutputStream("workbook.xls");
    workbook.write(fileOut);
    workbook.close();
  }
}