Sai Smriti Mohapatra wrote:
>
> Hi,
>
> I am writing some data (Strings, Numeric as well as Dates) into an excel.
> The structure of the sheet is similar to what's shown below:
> STR_COL, DATE1_COL, NUM1_COL, DATE2_COL, DATE3_COL, DATE4_COL, NUM2_COL,
> DATE5_COL
>
> The dates are formatted with a custom formatter allowing for Timestamp.
> On creation, the format structure of the sheet is fine.
> On editing (not through code, manually updating the sheet) the NUM2_COL
> only
> after I have edited a DATE*_COL in the sheet, the format of the NUM2_COL
> changes to the date format automatically. Strangely, this behaviour is not
> seen with the NUM1_COL.
>
> Following is some part of the code I am using...
> sheet3 = wb.createSheet("Sheet_3");
> createHeaderRow(2, sheet3);
> // Set the cell format for the dates
> HSSFCellStyle cellStyle = wb.createCellStyle();
> HSSFCellStyle cellStyleDate = wb.createCellStyle();
> HSSFCellStyle cellStyleGeneral = wb.createCellStyle();
> HSSFCellStyle cellStyleDateGrayed = wb.createCellStyle();
> HSSFDataFormat dataFormat = wb.createDataFormat();
> cellStyleDate.setDataFormat(dataFormat.getFormat("dd-MMM-yyyy
> hh:mm:ss"));
>
> cellStyleGeneral.setDataFormat(dataFormat.getBuiltinFormat("General"));
>
> cellStyleDateGrayed.setDataFormat(dataFormat.getFormat("dd-MMM-yyyy
> hh:mm:ss"));
>
> cellStyleDateGrayed.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
>
> cellStyleDateGrayed.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
>
> styles.add(cellStyleGeneral);
> styles.add(cellStyleDate);
> styles.add(cellStyleDateGrayed);
>
> ................
>
> HSSFRow row = null;
> HSSFCell cell = null;
>
> row = sheet.createRow(rowNumber);
> // STR1
> cell = row.createCell(0);
> cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> cell.setCellStyle(styles.get(0));
> cell.setCellValue(str1);
> // DATE1
> cell = row.createCell(1);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(1));
> cell.setCellValue(date1);
> // NUM1
> cell = row.createCell(2);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(0));
> cell.setCellValue(num1);
> // DATE2
> cell = row.createCell(3);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(1));
> cell.setCellValue(date2);
> // DATE3
> cell = row.createCell(4);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(1));
> cell.setCellValue(date3);
> // DATE4
> cell = row.createCell(5);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(1));
> cell.setCellValue(date4);
> // NUM2
> cell = row.createCell(6);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(0));
> cell.setCellValue(num2);
> // DATE5
> cell = row.createCell(7);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellStyle(styles.get(2));
> Calendar prepCalendar = Calendar.getInstance();
> prepCalendar.setTime(date3);
> prepCalendar.add(Calendar.HOUR, 8);
> cell.setCellValue(prepCalendar.getTime());
>
>
> Smriti
>
>
I am adding more information regarding the issue above.
1. styles is an ArrayList
2. The issue occurs only when I delete the contents of any cell in the NUM2
column in Excel. Any data I write into that cell after that is in the custom
Date format. It happens irrespective of whether I edit a cell in any of the
DATE columns.
3. I am using POI 3.2
Also, the dynamic change in format doesnt occur with #2 above if I change
the structure of the sheet while creation:
a. move the NUM2 column after the NUM1 column!
b. move the NUM2 column after the DATE2 column!
However if I move the NUM2 column after the DATE3 column, I see the same
behaviour.
Has anybody seen this behaviour before?
Can anybody say whether this is an issue in my implementation or an issue in
POI HSSF?
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/HSSF-Fromatting-Issues-with-Date-tp2473156p2581680.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
