Styjun

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

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

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.