Seems the reply I sent did not reached to you.

[Birendar Waldiya <bw...@cisco.com>]

Hi Michael,

First of all that a lot for replying , as I rarely get any reply . 

The Issue is not creating xls , I am using POI api to read a excel sheet
that that data with String and Date 

There is no problem with string , but the problem is with date column 

Lets say I have a column "Sales Order Date" and there are 10 rows with some
dates in say DD-MON-YYY

Column --> COL1 COL2 COL3 SalesOrderDate COL$ 

Data ---> ABC JKL 200 23-May-2005 ITALY

Now for some reason I need to change the date format to dd/mm/yyyy using
excel fecility I go and do right click

And select format dd/mm/yyyy my date get converted to 23/5/2006 and I save
this xls file. Now Iread this file in my appliaction and display the
reasults in console :

Instead of appearing it as date it come as 404102 which I know is how excel
stores the dates . But when this date goes further it now as become number
so obviously causes tonnes of problem. 

It does not happen for the first time I chage the date format, but happens
if I change in 2 3 date format.

Attached is the simple code I am using .

<code>

import java.io.*;

import java.sql.Connection;

import java.sql.Statement;

import java.util.Calendar;

import java.util.Iterator;

import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**

* A class used to parse spreadsheet data in CSV file format.

*

* @version 1.0

*/

public final class XLSParser {

private HSSFWorkbook wb = null;

private POIFSFileSystem fs = null;

private String filename =null ; 

private int numOfSheets = 0 ;

private int columnCount_ = -1;

private int currentLine_ = 0;

public XLSParser(String filename, CSVParserRoutine routine ) throws
XLSParserException, IOException

{

if (filename == null)

throw new XLSParserException(XLSParserException.XLS_FILE_NAME_NULL);

if (!filename.toLowerCase().endsWith(".xls")) {

throw new XLSParserException(XLSParserException.SINGLE_SHEET_SUPPORTED);

} 

boolean done = false ;

try {

try{

fs = new POIFSFileSystem (new FileInputStream(filename));

}catch(FileNotFoundException e){

throw new XLSParserException(XLSParserException.SINGLE_SHEET_SUPPORTED);

}

wb = new HSSFWorkbook (fs);

numOfSheets =wb.getNumberOfSheets();

if(numOfSheets>=2){

throw new XLSParserException(XLSParserException.SINGLE_SHEET_SUPPORTED);

}

for (int i=0; i<numOfSheets; i++) {

HSSFSheet sheet = wb.getSheetAt (i);

for (Iterator rows = sheet.rowIterator(); rows.hasNext(); ) {

HSSFRow row = (HSSFRow) rows.next ();

short c1 = row.getFirstCellNum();

short c2 = row.getLastCellNum();

String entries[] = new String[c2-c1];

for (short c=c1; c<c2; c++) { 

HSSFCell cell = row.getCell(c);

String cellValue = getCellValue (cell);

entries[c]=cellValue;

}

try{

if (entries != null){

CheckValues(entries); 

}

else if(entries == null && currentLine_==0) {

throw new XLSParserException(XLSParserException.XLS_PARSE_FILE_INVALID);

} 

} catch(Exception e){

System.out.println("INSIDE EXCEPTION");

e.printStackTrace();

}

currentLine_++;

}

wb.removeSheetAt(i);

}

} catch (IOException e) {

e.printStackTrace();

}

catch(Throwable th)

{

th.printStackTrace();

}

finally {

try {

System.out.println("###FINALLY EXECUTED###");

close();

}

catch (IOException io_e) {System.out.println(io_e);}

}

}

public final void close() throws IOException

{

if (wb == null) throw new IllegalStateException("The Workbook reader object
is null.");

fs = null;

}

public final int getCurrentLineNumber()

{

return currentLine_;

}

public final static void parse(String filename, CSVParserRoutine routine)

throws XLSParserException, Exception

{

/**

* Create a new parser for the csv file.

*/

XLSParser parser = new XLSParser(filename,routine);

}

/**

* THIS IS THE METHOD THAT DEALS WITH THE MICROSOFT CELL

* This is a helper method to retrieve the value of a

* cell regardles of its type, which will be converted

* into a String.

*

* @param cell

* @return

*/

public final String getCellValue (HSSFCell cell) {

Calendar cal = Calendar.getInstance();

if (cell == null) return "";

String result = null;

int cellType = cell.getCellType();

// cell.

int formast = HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm");

System.out.println("BLANKFORMAT=>"+formast);

switch (cellType) {

case HSSFCell.CELL_TYPE_BLANK:

result = "BLANK";

System.out.println("BLANK=>"+cell.getCellType());

break;

case HSSFCell.CELL_TYPE_BOOLEAN:

result = cell.getBooleanCellValue() ?"TRUE" : "FALSE";

System.out.println("BOOLEAN=>"+cell.getCellType());

break;

case HSSFCell.CELL_TYPE_ERROR:

result = "ERROR: " + cell.getErrorCellValue();

System.out.println("ERROR=>"+cell.getCellType());

break;

case HSSFCell.CELL_TYPE_FORMULA:

result = cell.getCellFormula(); 

System.out.println("FORMULA=>"+cell.getCellType());

break;

case HSSFCell.CELL_TYPE_NUMERIC:

double d = cell.getNumericCellValue();

System.out.println("NUMERIC=>"+cell.getCellType());

if (HSSFDateUtil.isCellDateFormatted(cell)) {

cal.setTime(HSSFDateUtil.getJavaDate(d));

result =(String.valueOf(cal.get(Calendar.YEAR))).substring(2);

result = cal.get(Calendar.MONTH)+1 + "/" +cal.get(Calendar.DAY_OF_MONTH) +
"/" +result;

}else{

result = String.valueOf(cell.getNumericCellValue());

}

break;

case HSSFCell.CELL_TYPE_STRING:

cell.setEncoding(HSSFCell.ENCODING_COMPRESSED_UNICODE);

System.out.println("STRING=>"+cell.getCellType());

result = cell.getStringCellValue();

break;

default: break;

}

return result; 

}

//TESTING IF ENTRIES ARE GOING BALNK OR NULL;

public void CheckValues(String ar[]){

String array1[] = null;

array1 = ar;

for(int k= 0;k<array1.length;k++){

System.out.println("CHECKIG ACTUAL VALUES==>"+array1[k]);

}

System.out.println("\n");

}

public static void main(String[] args) throws Exception {

String ext ="xls" ;

String fileName ="";

char TAB = '\t';

if(ext.equalsIgnoreCase("xls")){

fileName="D:\\TEMPLATE-JUNE-06\\Samples.xls";

try{

XLSParser.parse(fileName,ref) ;

}catch(Exception e){

}

}

}

}

 

</code>

-----Original Message-----

From: Donahue, Michael [ <ma...@pearson.com>
mailto:michael.donahue@pearson.com]

Sent: Wednesday, May 17, 2006 8:33 PM

To: 'POI Users List'

Subject: RE: HSSF has not 'HSSFRowllStyle' to set row style?How to set row
sytle?thanks

I'm not sure if this is what you are looking for or not. If this isn't what
you are looking for perhaps you could post a commented version of you code
that show where you're having you problem...

HSSFCellStyle style = workbook.createCellStyle();

style.set ... ( ... );

style.set ... ( ... );

style.setDataFormat(HSSFDataFormat.getFormat("dd-mm-yyyy"));

cell.setCellStyle(style);

-----Original Message-----

From: Birendar Waldiya [ <ma...@cisco.com>
mailto:bwaldiya@cisco.com]

Sent: Wednesday, May 17, 2006 7:29 AM

To: 'POI Users List'

Subject: RE: HSSF has not 'HSSFRowllStyle' to set row style?How to set row
sytle?thanks

Hi Gurus , 

Can any one give me some pointer , that how POI decides a particular field
is a date field or A number ??/

Because I am facing a problem of following While reading a excel file I have
a column in which I give date - initialy it reads as date no issues now I
try to change the date format to some say from say dd-mon-yyyy to other
format dd/mm/yyyy & save it , now this never reads this date as date but
number Unless I delete the column and re- enter the date 

Needed pointers urgently .

Thanks

Regrads

Birendar S Waldiya