Converting text into numeric in xls using Java

Question

When I create excel sheet through java ,the column which has number datatype in the oracle table, get converted to text format in excel.I want it to remain in the number format.Below is my code snippet for excel creation.

    FileWriter fw = new FileWriter(tempFile.getAbsoluteFile(),true);
    //BufferedWriter bw = new BufferedWriter(fw);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Excel Sheet");

    //Column Size of excel
    for(int i=0;i<10;i++)
    {
        sheet.setColumnWidth((short) i, (short)8000); 
    }

    String userSelectedValues=result;   

    HSSFCellStyle style = wb.createCellStyle();
    ///HSSFDataFormat df = wb.createDataFormat();

    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    //style.setDataFormat(df.getFormat("0"));

    HSSFFont font = wb.createFont();
    font.setColor(HSSFColor.BLACK.index);
    font.setBoldweight((short) 700);
    style.setFont(font);

    int selecteditems=userSelectedValues.split(",").length;
    // HSSFRow rowhead = sheet.createRow((short)0);
    //System.out.println("**************selecteditems************" +selecteditems);

    for(int k=0; k<selecteditems;k++)
    {
        HSSFRow rowhead = sheet.createRow((short)k);

        if(userSelectedValues.contains("O_UID"))
        {   
          HSSFCell cell0 = rowhead.createCell((short) k);
          cell0.setCellValue("O UID");
          cell0.setCellStyle(style); 
          k=k+1;
        }   
    ///some columns here..
    }

    int index=1;
    for (int i = 0; i<dataBeanList.size(); i++) 
    {
        odb=(OppDataBean)dataBeanList.get(i);
        HSSFRow row = sheet.createRow((short)index);

        for(int j=0;j<selecteditems;j++)
        {
            if(userSelectedValues.contains("O_UID"))
            {            
                HSSFCell row1=row.createCell((short)j);
                row1.setCellType(row1.CELL_TYPE_NUMERIC);
                row1.setCellValue(odb.getUID());
                j=j+1;
            }
        }
        index++;
        }

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(path.toString()+"/temp.xls");
    } catch (FileNotFoundException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
     try {
        wb.write(fileOut);
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
     try {
        fileOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

Excel screenshot

FazoM · Accepted Answer · 2013-10-22T11:01:12.567

2

Try this:

Cell cell = row.createCell(...);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html

In your example it will be:

Cell cell = row.createCell((short)j);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(odb.getUID());

edited Oct 22 '13 at 11:01

answered Oct 22 '13 at 10:37

FazoM

4,777
6
43
61

How to add in the below code? Since, Im getting the values as bean from the database. if(userSelectedValues.contains("O_UID")) { row.createCell((short)j).setCellValue(odb.getUID()); j=j+1; } – Work World Oct 22 '13 at 10:56
Hi, I added like this.HSSFRow rowhead = sheet.createRow((short)k); if(userSelectedValues.contains("O_UID")){ HSSFCell cell0 = rowhead.createCell((short) k); cell0.setCellType(cell0.CELL_TYPE_NUMERIC); cell0.setCellValue("0_UID"); cell0.setCellStyle(style); k=k+1; }this is the header part. Now, how to inculde in the below code since, i am fetching the data from bean from database. odb=(OpportunityDataBean)dataGridBeanList.get(i); HSSFRow row = sheet.createRow((short)index); for(int j=0;j – Work World Oct 22 '13 at 11:33
Very similar, you just need to invoke setCellType(...) method for a cell, like for headers. – FazoM Oct 22 '13 at 11:38
Is this correct.. I added like this HSSFCell row0=row.createCell((short)j); row0.setCellType(cell0.CELL_TYPE_NUMERIC); setCellValue(odb.getOpportunityUID()); – Work World Oct 22 '13 at 11:44
I added as u suggested. But in excel when i select the values from the columns there sum is not shown. Still it is considering as "Text" in excel. Please help. – Work World Oct 22 '13 at 12:01
Are you sure you changed this in the right place? This is the way you set a cell to be numeric: cell.setCellType(Cell.CELL_TYPE_NUMERIC); and that's it. Maybe your code have some bugs (i.e. I can see you do: k=k+1; and j=j+1; but your for loops already have k++ and j++. Maybe this is the problem? (it skips some rows/columns) – FazoM Oct 22 '13 at 12:19
Here we are selecting only selected columns to display in excel. So, k=k+1 are included. I included correctly as u mentioned. But, when excel sheet is extracted the numeric cell are not adding up since, they are coming as text. In excel it showing only count not sum of numeric values .. please help. – Work World Oct 23 '13 at 07:12
Hi, what sums? Do you create any formulas in Java? In that case cell with formula should be of type Cell.CELL_TYPE_FORMULA: cell.setCellType(Cell.CELL_TYPE_FORMULA) – FazoM Oct 23 '13 at 08:21
Hi, Can u please provide me ur email id. SO, I can send the screen shot of the excel file. So, it will be very clear. – Work World Oct 23 '13 at 08:56
No. Host it on some server and paste link here. – FazoM Oct 23 '13 at 09:20
Hi, I have added the Screen shot. Here numeric values are not getting addedup when,it is selected. Since, it is considering as text. Even, though i added the above code as u mentioned. plz help. – Work World Oct 23 '13 at 09:53
Can you paste your whole current code? Sorry, it's getting messy. – FazoM Oct 23 '13 at 10:01
Can you confirm that odb.getUID() returns numeric type (NOT a String?) If it returns String, than you need to parse it to int ( Integer.parseInt(odb.getUID()) ). – FazoM Oct 23 '13 at 10:21
It is odb.getUID() returns string. I added the Integer.parseInt(odb.getUID()). Suppose, if string values is like "0.0" then, how to parse these values ? – Work World Oct 23 '13 at 10:34
It will be: Double.parseDouble( odb.getUID() ) – FazoM Oct 23 '13 at 10:43
ok, but in some scenario the return String may be empty. then,parse wont execute and result in exception. Then, how to handle this condition? – Work World Oct 23 '13 at 10:57
If there will be exception (NumberFormatException), than catch that exception and do whatever you want i.e.: setting a cell value to 0 might be a good idea. Or change cell type to TEXT and set value to "error" (?) – FazoM Oct 23 '13 at 11:19
One more clarification how to handle String when it contains date formate (eg:2013-02-28) or timestamp formate(e.g: 2013-07-09 16:09:24.805) ? – Work World Oct 23 '13 at 11:47
Take a look here: http://stackoverflow.com/questions/4216745/java-string-to-date-conversion/4216767#4216767 – FazoM Oct 23 '13 at 11:57
Hi, For Date i added the code like this is it correct?HSSFCell row=row.createCell((short)j); row.setCellType(row.CELL_TYPE_NUMERIC); try { row32.setCellValue( Date.parse(odb.getCloseDate())); } catch(Exception e) { row.setCellValue(odb.getCloseDate()); } – Work World Oct 24 '13 at 07:21
Sorry, can't do all things for you, ask in separate question or google a bit, i.e.: http://stackoverflow.com/questions/5794659/poi-how-do-i-set-cell-value-to-date-and-apply-default-excel-date-format – FazoM Oct 24 '13 at 08:19
Hi, Thanks for the all the guidance and help. The Solution you provided worked. THANK U :-) – Work World Oct 24 '13 at 08:21

Converting text into numeric in xls using Java

1 Answers1