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-22 11:01:12Z

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, 2013 at 11:01

answered Oct 22, 2013 at 10:37

FazoM

4,9666 gold badges47 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

22 Comments

Work World Over a year ago

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 Over a year ago

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<selecteditems;j++) { row.createCell((short)j).setCellValue(odb.getUID());}I am confused here. plz help

FazoM Over a year ago

Very similar, you just need to invoke setCellType(...) method for a cell, like for headers.

Work World Over a year ago

Is this correct.. I added like this HSSFCell row0=row.createCell((short)j); row0.setCellType(cell0.CELL_TYPE_NUMERIC); setCellValue(odb.getOpportunityUID());

Work World Over a year ago

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.

|

Collectives™ on Stack Overflow

Converting text into numeric in xls using Java

1 Answer 1

22 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

22 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related