Change the Excel column format from number to Text in Java

Question

I'm trying to create an Excel template with all the columns / cells as text. The Excel sheet has 5 columns

Here is my code:

@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();
  }

}

Everytime getting cell format as Number not text

Rajat Rana
– Rajat Rana

2019-03-27 11:25:11 +00:00
Commented Mar 27, 2019 at 11:25 — Rajat Rana
– Rajat Rana, Commented Mar 27, 2019 at 11:25

Martin · Accepted Answer · 2019-03-27 16:18:30Z

2

Who is telling you that the cell isn't in text format? MS Excel? LibreOffice Calc?

The following code works for me and should give you a cell in text format.

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();
  }
}

edited Mar 27, 2019 at 16:18

answered Mar 27, 2019 at 16:00

Martin

18810 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rajat Rana Over a year ago

LibreOffice Calc

Martin Over a year ago

When you try my code, open workbook.xls, right click on the first cell and select “Format Cells...” then you should see the format code is set to @.

Adrián · Accepted Answer · 2019-03-27 11:53:58Z

0

Something like:

Worksheets("Sheet1").Range("A17").NumberFormat = "@"

I am not sure but I think that "@" means Text format. That is only for one cell, if you want all cells I think it could be something like this:

Worksheets("Sheet1").Range("B:B").NumberFormat="@"

in case you want only column B

`Worksheets("Sheet1").Range("A:E").NumberFormat="@"`

if you want all your data is read like a text.

answered Mar 27, 2019 at 11:53

Adrián

14 bronze badges

Collectives™ on Stack Overflow

Change the Excel column format from number to Text in Java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related