CODEWITHSUNDEEP
javaexceldate
user1900662
user1900662

Reputation: 299

Excel changing the number format to date when the month starts with zero.Is it a bizzare bug?

I have a date in the format 04/08/2014(dd/mm/yyy). I copied the string and placed it in excel it gets turned it into 04-08-2014. For other dates where the "mm" doesn't starts with 0 its normal for Example if i copied 12/11/2013 and placed it in excel it gets normally pasted as 12/13/2014.

Actually i am trying to generate an excel with java the code is attached below

public void exportExcel(){      
    FacesContext fc = FacesContext.getCurrentInstance();
    ExternalContext ec = fc.getExternalContext();
    String filename = "ExcelList.csv";

    try{

        ec.responseReset();
        ec.setResponseContentType("text/csv");
        ec.setResponseHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");
        Writer writer = ec.getResponseOutputWriter();



        if(PageList != null && PageList.size() > 0){

            writer.append(toCsvField("Name")).append(',')
            .append(toCsvField("Code")).append(',')
            .append(toCsvField("Description")).append(',')              
            .append('\n');

            for (PageBean View : PageList) {
                writer.append(toCsvField(View.Name)).append(',')
                .append(toCsvField(View.Number)).append(',')
                .append(toCsvField(View.Description)).append(',');  

            }
        }

        fc.responseComplete();

    }catch(Exception e){

    }

}

public static String toCsvField(Object value) {
    if (value == null) {
        return "";
    }

    String field = String.valueOf(value).replace("\"", "\"\"");

    if (field.indexOf(',') > -1 || field.indexOf('"') > -1) {
        field = '"' + field + '"';
    }

    return field;
}

whats wrong with the month field gets starts with 0. Please help.

Upvotes: 2

Views: 958

Answers (2)

OCJP
OCJP

Reputation: 1033

Try the below code using apache poi framework this should help.

public void exportExcel(){          
    FacesContext fc = FacesContext.getCurrentInstance();
    ExternalContext ec = fc.getExternalContext();       
    String filename = "ExportList.xlsx";

    XSSFWorkbook workbook = new XSSFWorkbook(); 
    XSSFSheet sheet = workbook.createSheet("Export Data");  

    int rownum = 0;

    Row headRow = sheet.createRow(rownum++);    

    Cell cell1 = headRow.createCell(0);
    Cell cell2 = headRow.createCell(1);
    Cell cell3 = headRow.createCell(2);
    Cell cell4 = headRow.createCell(3);

     cell1.setCellValue("Name");                 
     cell2.setCellValue("Age");             
     cell3.setCellValue("Sex");     


     for(Bean view : list){
         int cellnum = 0;
         Row row = sheet.createRow(rownum++);

         row.createCell(cellnum++).setCellValue(view.name);              
         row.createCell(cellnum++).setCellValue(view.age);              
         row.createCell(cellnum++).setCellValue(view.sex);      
     }
   try{
      ec.responseReset();         
      ec.setResponseContentType("text/xlsx");
      ec.setResponseHeader("Content-Disposition", "attachment; filename=\"" + filename + "\"");     

      //to align column vertically
      sheet.autoSizeColumn(0);
      sheet.autoSizeColumn(1);
      sheet.autoSizeColumn(3); 

      workbook.write(ec.getResponseOutputStream()); 

      fc.responseComplete();                    
   } 
  catch (Exception e){
            e.printStackTrace();
  }
  }

Upvotes: 1

Braj
Braj

Reputation: 46861

Steps to follow:

  • get the locale of client that is set as browser language
  • format the data based on client locale
  • write formatted data in csv

Note: csv file must be opened in the same locale of the system in which it is download.

If csv is download in German locale (read from browser) and you are tring to open it in French locale (read from system regional & language settings) then it will mismatch/corrupt the data in excel.

    import java.text.DateFormat;
    import java.text.DecimalFormat;
    import java.text.NumberFormat;
    import java.util.Date;
    import java.util.Locale;

    // 04/08/2014(dd/mm/yyy)
    Calendar cal = Calendar.getInstance();
    cal.set(2014, 7, 4, 0, 0, 0);
    double no = 123456.7890;
    Date date = cal.getTime();
    System.out.println(date);

    for (Locale locale : Locale.getAvailableLocales()) {
        NumberFormat numberFormat = DecimalFormat.getNumberInstance(locale);
        DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.SHORT, locale);

        System.out.println("========================================");
        System.out.println(locale.getDisplayCountry() + " - " + locale.toString());
        System.out.println(numberFormat.format(no));
        System.out.println(dateFormat.format(date));
    }

Output for Portugal is 04-08-2014

  • It means if your system's regional & language setting is set for Portugal and you try to paste 04/08/2014(dd/mm/yyy) in excel then it will be automatically converted to 04-08-2014.

  • Add sep=; at the first line of csv so that excel can read it to split the columns into cells because by default comma(,) is used as separator but it creates problem in few locales where comma is used as grouping separator or decimal separator in numbers.

Your csv will look like this for Portugal locale:

    sep=;
    "Name";"Number";"DOB"
    "A";"123.456,789";"04-08-2014"
    "B";"123.456,789";"12-11-2013"

Links to read client's locale:

Upvotes: 2

Related Questions