CSV Generation, Date and Long number formats

Question

I am generating a CSV file on the fly/runtime with JSF and the code is as follows

FacesContext context = FacesContext.getCurrentInstance();
HttpServletResponse response = HttpServletResponse)context.getExternalContext().getResponse();

int read = 0;
byte[] bytes = new byte[1024];

response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + "\"");

ServletOutputStream os = null;

StringBuffer stringBuffer1 = new StringBuffer("");

stringBuffer1.append("Disconnect Time");
stringBuffer1.append(',');
stringBuffer1.append("Calling Number");

stringBuffer1.append("01/06/2010 01:00:35 AM");
stringBuffer1.append(", ");
stringBuffer1.append("447744369900");


ByteArrayInputStream bis1;
try {
    bis1 = new ByteArrayInputStream(stringBuffer1.toString().getBytes("UTF-8"));

    os = response.getOutputStream();

    while ((read = bis1.read(bytes)) != -1) {
        os.write(bytes, 0, read);
    }

    os.flush();
    os.close();
} catch (UnsupportedEncodingException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

FacesContext.getCurrentInstance().responseComplete();

and below is the content of the file when opened

Disconnect Time Calling Number
1/6/2010 1:00   4.47744E+11

The Actual expected result will be complete date format w.r.t AM/PM and number in complete length.

I have already tried example of double quotes as given Excel CSV - Number cell format and some other of adding blank space but they did not worked. Also the user will be performing arithmetic operation on the number columns.

Thanks in advance.

Have no Excel at hand right now, but are the calculations actually failing? To me it seems to be only a formatting issue in Excel. — Turismo, Sep 09 '12 at 11:29

RonK · Accepted Answer · 2012-09-09T10:58:06.030

1

I'm no JSF expert but this seems entirely related to how Excel reads this information.

I tried playing a bit with the data, but could not make it do what you want it to do in CSV.

I created a sample CSV file with this content (using Nopepad++):

Disconnect Time, Calling Number
01/06/2010 01:00:35 AM,447744369900

And when I viewed it using Excel it gave me the same output you got - so the question you are facing is related to showing CSV in Excel - and not JSF.

edited Sep 09 '12 at 10:58

answered Sep 09 '12 at 10:51

RonK

9,472
8
51
87

I have already mentioned in my question that double quotes are not working in my case as it gives output like 1/6/2010 1:00 "447744369900" and user will not be able to do calculations like sum etc on 2nd column – Asad Nauman Sep 09 '12 at 11:03
so in the modified example can you please share the CSV raw contents from notepad file – Asad Nauman Sep 09 '12 at 11:13
@AsadNauman: The modified example is the CSV raw contents. When viewed in Excel it looks exactly like in your question - this is just how Excel shows the values. The original data is still there - but Excel "guesses" how to show it best. – RonK Sep 09 '12 at 12:53
Thanks RonK, I tried ="447744369900" and now CSV is showing correct format – Asad Nauman Sep 09 '12 at 13:19

score 1 · Answer 2 · answered Sep 19 '12 at 22:39

You've mentioned that you're generating the file dynamically. If you are exporting data from a dataTable you might want to consider using the p:dataExporter component from PrimeFaces (you'll have to use a p:dataTable though). It may save you programatically creating the excel file if it's already in the correct structure in your dataTable.

Then it's as easy as attaching p:dataExporter to a link, and pointing it to your dataTable (tbl in this case).

<h:commandLink>  
    <p:graphicImage value="/images/excel.png" />  
    <p:dataExporter type="xls" target="tbl" fileName="cars" />  
</h:commandLink>

It also supports CSV and PDF by the way.

score 0 · Answer 3 · edited Sep 09 '12 at 12:51

RonK is right. There is no way you can tell Excel how to format the data when using a CSV file. Excel just uses a best guess as to what the data in the CSV columns are and then applies the default format for that type of data.
It is very likely that Excel still has the correct data (i.e. still has the seconds of your time column) and it is just an issue with formatting in Excel.
Have you tried applying a different format in Excel?

You will either have to tell the users how to format the data correctly in Excel or you have to create an Excel file directly (e.g. using Apache POI) and applying the correct format that way.

Thanks Turismo, so is there any library to create a excel file with xls extension in which ww can pre-define the cell format — Asad Nauman, Sep 09 '12 at 12:10

score 0 · Answer 4 · answered Sep 09 '12 at 12:54

Sorry, looks like a bug ("feature", "deficit") in Excel.

It assumes anything that looks like a number is a number (and loses precision when reading it).

The simplest way probably is to ensure that the phone numbers do not look like numbers. For example by including ( and similar characters.

Remeber that in Excel you are sometimes expected to write '=123+456 if you don't want it to compute the equation. Try this, too.

Date is probably just formatting, try formatting the column to include seconds.

score 0 · Answer 5 · edited May 23 '17 at 12:12

To show correct format for dates and numbers in CSV I used =("447744369900") approach. The disadvantage is this that we cannot perform arithmetic operations on the cell. Now as Discussed with RonK and Turismo I am now shifted to Apache Excel library that allows you to define the format of the Excel file and create a XLS file instead of CSV. It is little slower than CSV but fulfills my requirements. See API to write huge excel files using java

Thanks again RonK and Turismo

score 0 · Answer 6 · answered Sep 09 '12 at 14:55

0

After you open the CSV in excel, you can change the format of the "calling number" cells as Number. Select column -> Format Cells -> Change to Number

answered Sep 09 '12 at 14:55

gammay

5,957
7
32
51

CSV Generation, Date and Long number formats

6 Answers6