JSP response content type Excel

Question

when i set response content type is excel. In excel sheet it is dsiplaying 1-Jan instead-of 1-1. here is code snippet

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%
    response.setContentType("application/vnd.ms-excel");
%>
<table><tr><td>1-1</td></tr></table>

Appending single quote "'" will work. is there any other solution to set cell format or correct text?

Solution 1

You could use the Apache POI project to return an Excel file to the output stream, which will allow you to do proper data/cell formatting.

See http://poi.apache.org/

Would that work for you?

Solution 2

You're fooling the webbrowser and Excel with a plain HTML table with a wrong content type and you're complaining that it doesn't work well in Excel due to an Excel-specific behaviour?

That doesn't sound right.

If you want an Excel file, create a real Excel file, not a plain HTML table along with a wrong content type. You can use the aforementioned Apache POI HSSF or Andy Khan's JExcelAPI for this.

As an alternative you can always go ahead with CSV. Less opaque and more easy. You can find here an example I recently posted: JSP page without HTML code for exporting data to Excel Sheet

Solution 3

I got quick workqround is

<%@page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%
    response.setContentType("application/vnd.ms-excel");
%>
<table>
<tr><td style='mso-number-format:"\@";'>1-1< /td></tr>
</table>

We are planning to use poi api.

Thanks to all.