cell formatting in excel using jsp

[Avneet Singh]

Hi

I have to create a report in excel using jsp.Which has been done by setting the content type as "application\vnd.ms-excel" .
Report comes out fine, but a field which has no. in it comes like
"9.19812E+11" the actual data once u double click on the cell comes as "919811911126" . To correct this we have to format the cell by rt clicking(cell formatting) on it and making it no. with 0 decimals.

I am creating this excel file using <table></table> in jsp.Data gets populated to the cells.

Can we do this formatting at time of report creation on jsp.

Thanks

[Ulf Dittmer]

Hello Avn-

Welcome to JavaRanch.

On your way in you may have missed that JavaRanch has a policy on display names, and yours does not comply with it - please adjust it accordingly, which you can do right here. Thanks for your prompt attention to this matter.

As to your question, Excel will treat anything that looks like a number as a number -even if it is not- and handle it as it sees fit, which by default means scientific nmber format. I think -since there is no way to specify formatting in CSV or HTML- there is no way to prevent this. Do you have a particular reason why you're not generating an actual XLS file, where you can control the formatting of the field? An API like POI is not hard to use.
[ April 25, 2006: Message edited by: Ulf Dittmer ]

[Jeroen T Wenting]

You're going to have to do a lot more work than just telling IE that an HTML table is an Excel sheet.
You're going to have to create the data as a SYLK document and stream that to the browser.

Best do that in a servlet rather than a JSP, maybe using a script rather than hardcoding the SLK layout for flexibility.

Here's a small example, see the file format specs for more advanced features (you can find it online, I believe http://www.wotsit.org has it).

response.setDateHeader("Expires", 0);
response.setContentType("text/plain");
                sheet = toSylk(data);
String response.setContentLength(sheet.length());
try
{
ServletOutputStream out = response.getOutputStream();
out.print(sheet);
out.flush();
}
catch (IOException e)
{
e.printStackTrace();
Debug.log(this, "error IOException", e);
}

This creates a simple spreadsheet containing 3 columns with different formating options.

public String toSylk(List data)
{
String periode = "";
int size = 0;
if (data != null) size = data.size();
 
StringBuffer sylk = new StringBuffer(4096);
sylk.append("ID;PDocument;N;E")
.append(CRLF);
sylk.append("P;PGeneral")// Picture 0
.append(CRLF);
sylk.append("P;P0")// Picture 1
.append(CRLF);
sylk.append("P;P0.00")// Picture 2
.append(CRLF);
sylk.append("F;P0;DG0G10;M255")// Format general, default 10 chars/cell
.append(CRLF);
sylk.append("B;Y").append(size + 1)
.append(";X").append(10)// Boundary rows columns
.append(";D0 0 ").append(size)
.append(" 10")// range 0,0 t/m row,column
.append(CRLF);
sylk.append("O;L")// Options A1-mode
.append(";D")//???
.append(";V0")//???
.append(";K47")//???
.append(";G100 0.001")//???
.append(CRLF);
sylk.append("F;W1 1 ")// Format width column 1
.append(15)
.append(CRLF);
sylk.append("F;W2 2 ")// Format width column 2
.append("Long columntitle".length())
.append(CRLF);
sylk.append("F;W3 3 ")// Format width column 3
.append(24)
.append(CRLF);
// header
sylk.append("C;Y1;X1;K\"Column 1\"")
.append(CRLF);
sylk.append("C;X2;K\"Long column title\"")
.append(CRLF);
sylk.append("C;X3;K\"Column 3\"")
.append(CRLF);
// body
Iterator iter = null;
if (data != null) iter = data.iterator();

Map row = null;
int idx = 0;
if (iter != null)
while (iter.hasNext()) {
row = (Map)iter.next();
sylk.append("F;P0;Y").append(idx++ + 2)
.append(";X1")
.append(CRLF);
sylk.append("C;K\"").append(row.get("col1")).append("\"")
.append(CRLF);
sylk.append("F;P0;X2")
.append(CRLF);
sylk.append("C;K\"").append((String)row.get("col2")).append("\"")
.append(CRLF);
sylk.append("F;P2;X3")
.append(CRLF);
sylk.append("C;K\"").append((String)row.get("col3")).append("\"")
.append(CRLF);
}
sylk.append("E")
.append(CRLF);
return sylk.toString();
}
 

[Avneet Singh]

Thaks for your help guys il use the poi api then. I had a look and it seems easy to implement. Il do this and get to you guys if i run into some prob.

Thanks alot.
Avneet.