CODEWITHSUNDEEP
javajakarta-eeapache-poi
Work World
Work World

Reputation: 45

Converting text into numeric in xls using Java

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

Upvotes: 2

Views: 5408

Answers (1)

FazoM
FazoM

Reputation: 4956

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());

Upvotes: 2

Related Questions