How to set integer value in the excel sheet created through 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.

    public boolean prepareExcelFilefromQuery(String chanType,
                    Collection<List> queryDataRowWise, HttpServletResponse response)

            {
                List<String> queryDataColWise;
                HSSFRow row = null;
                HSSFCell cell = null;
                Integer rowCounter = 0;
                Integer colCounter;
                boolean success = false;
                try {

                    Iterator<List> rowIterator = queryDataRowWise.iterator();
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    HSSFSheet sheet = workbook.createSheet(chanType + " Report");
                    System.out.println("First row/..." + sheet.getFirstRowNum());
                    while (rowIterator.hasNext()) {
                        colCounter = 0;

                        queryDataColWise = (List) rowIterator.next();
                        Iterator colIterator = queryDataColWise.iterator();

                        row = sheet.createRow(rowCounter++);
                        while (colIterator.hasNext()) {
                            cell = row.createCell(colCounter++);
                            Object o = colIterator.next();

                            if (o instanceof java.lang.String) {
                                String s = (String) o;
                                cell.setCellValue(s);
                            } else if (o instanceof java.lang.Double) {
                                Double d = (Double) o;
                                cell.setCellValue(d);
                            } else if (o instanceof java.lang.Integer) {
                                Integer i = (Integer) o;
                                 cell.setCellType(cell.CELL_TYPE_NUMERIC);
                                cell.setCellValue(i);
                            } else if (o instanceof java.util.Date) {
                                Date date = (Date) o;

                                SimpleDateFormat FORMATTER;

                                FORMATTER = new SimpleDateFormat("MM/dd/yyyy");
                                String date11 = FORMATTER.format(date);
                                HSSFCellStyle cellStyle = workbook.createCellStyle();
                                cellStyle.setDataFormat(HSSFDataFormat
                                        .getBuiltinFormat("m/d/yy"));
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(FORMATTER.parse(date11));
                            }
                        }
                    }

                    workbook.write(response.getOutputStream());
                    success = true;

                } catch (Exception e) {
                    logger.debug(
                            "Exception caught in prepareExcelFilefromQuery class ", e);
                    System.out
                            .println("Exception caught in prepareExcelFilefromQuery class ");
                    e.printStackTrace();

                } 
                }
                return success;
            }

These are not Java standard library classes. If you point us to the javadoc or the library, you will have more chances to get an answer. — ignis
– ignis, Commented Oct 29, 2012 at 11:35

Zaw Than oo · Accepted Answer · 2012-10-29 11:54:44Z

1

It just use format like 0. More Format

CellStyle numberStyle = wb.createCellStyle();
numberStyle.setDataFormat(format.getFormat("0"));

CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(format.getFormat("d-mmm-yy"));

answered Oct 29, 2012 at 11:54

Zaw Than oo

9,94513 gold badges87 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

How to set integer value in the excel sheet created through java?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related