CODEWITHSUNDEEP
javaexcel-2007apache-poi
markiz
markiz

Reputation: 2184

Applying specific cell data format using POI

I need to apply specific data format to cell.

XSSFCellStyle cellStyle  = workBook.createCellStyle();          
XSSFDataFormat format = workBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("m/d/yy h:mm"));
dataCell.setCellType(1); //String
dataCell.setCellStyle(cellStyle);

The data is being written but the problem is that the format is applied only when I open excel sheet (in Excel application), click inside the cell with my data.
And then pressing Enter key. Only then the format is applied.

How can I apply the format without clicking on every cell?

Upvotes: 4

Views: 23276

Answers (2)

Dinesh Ranjeew Silva
Dinesh Ranjeew Silva

Reputation: 21

This is how you change the cell format to Date.

 var workBook = new XSSFWorkbook();

 var sheet = workBook.CreateSheet("Sheet1");

 var dateStyle = workBook.CreateCellStyle();

 var dataFormat = workBook.CreateDataFormat();

 dateStyle.DataFormat = dataFormat.GetFormat("M/D/YYYY");

 row.CreateCell(0).SetCellValue(item.ModifiedDate.HasValue ?  
           item.ModifiedDate.Value.ToShortDateString(): "");
 row.Cells[0].CellStyle = dateStyle; // This will change your cell to date 
format.

Upvotes: 2

Gagravarr
Gagravarr

Reputation: 48376

Your problem is that you're setting the cell to be a string. For the most part, cell formatting rules only apply to numeric cells. They don't apply to string cells, those don't get formatted (as they're already formatted to a string!)

XSSFCellStyle cellStyle  = workBook.createCellStyle();          
XSSFDataFormat format = workBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("m/d/yy h:mm"));
dataCell.setCellStyle(cellStyle);

// Set Cell Type not really needed, the setCellValue does it
dataCell.setCellType(Cell.CELL_TYPE_NUMERIC);
// Set a date value (2012-06-05 08:50)
dataCell.setCellValue(new Date(1338878999635));

When you open that in Excel, it ought to be have as expected

Upvotes: 3

Related Questions