cell style

Dec 17, 2009 at 8:35 PM

I have the following code for creating a cell and also setting the format to a built in number format.  When I open the file in Excel 2007, it is not converting the cell to a number format.  I am not sure why the cell is not being formatted to a number.

 

<font size="2">

cellStyle.DataFormat =

</font>

HSSFCell cell = row.CreateCell(c);

HSSFDataFormat.GetBuiltinFormat("0.00");

cell.SetCellValue(cells[c].ToString().Trim());

cell.CellStyle = cellStyle;

Coordinator
Dec 19, 2009 at 1:28 AM

Why you convert cells[c] to String? This will set the cell's type to string instead of numeric, which may be the root cause.

Dec 21, 2009 at 2:10 PM

That was my original code.  I changed it too the following and still getting the same results.

 

<font size="2">

cellStyle.DataFormat =

</font>

HSSFCell cell = row.CreateCell(c);

HSSFDataFormat.GetBuiltinFormat("0.00");

cell.SetCellValue(cells[c]);

cell.CellStyle = cellStyle;

Mar 3, 2010 at 3:58 PM

Did you find solution to cell formatting issue?  I am running Excel 2010 and trying to use built in format for string and date but they are not working.

Thanks

 

Mar 3, 2010 at 4:03 PM

Yes. I was converting my data type to a string. If I did not convert the data type, it remained the same type in Excel. So try converting to date before assigning it to Excel. Same with your string. Format it before assigning to Excel and see if that works.

Mar 3, 2010 at 4:17 PM

But the cell.SetCellValue function takes string as parameter so even if i convert my values to double or date it will have to be reconverted to string.

Can you provide an example?

Thanks

 

Mar 3, 2010 at 4:27 PM

Nevermind.  I didnt realize that SetCellValue had overloads. 

 

Thank you.

 

Mar 3, 2010 at 5:34 PM

Here is an example that I use.

cell.SetCellValue(Convert.ToDouble(cells[c]));

Jul 8, 2010 at 7:35 PM

Ok, question about this one still....

When I create a spreadsheet and use the setvalue logic listed above, the column shows as a double value and not as a date time.   How can I assign a date format to this cell so that it will display as a date time when the user opens the document.

 

Jul 9, 2010 at 1:53 PM

Well in my example above, instead of Convert.ToDouble(), try Convert.ToDateTime(). The other thing you might want to try is just passing the date as a string.

Coordinator
Jul 15, 2010 at 11:54 PM

Excel save Datetime type as a number internally. To make it display as a date, you should get date time format from from HSSFDataFormat. If you get the DataFormat '0.00', it means you would like to display the datetime as a float number.

Jun 28, 2012 at 5:59 PM
Edited Jun 28, 2012 at 6:04 PM
tonyqus wrote:

If you get the DataFormat '0.00', it means you would like to display the datetime as a float number.

I've tried changing the DataFormat of a column to show 2-decimal places, but the value that's in the Excel worksheet is not displayed as expected; i.e. "32.9" is getting written to the Excel file as 32.9, vs. 32.90. See code snippet below.

Ideas anyone?

// the value in dr[4] is "32.9", a string for some wierd reason
row.CreateCell(4, CellType.NUMERIC).SetCellValue(Convert.ToDouble(dr[4]));

// this bit I thought would cause the values in column #4 to be 
// displayed with 2-decimal places, this code is executed after all 
// the rows in datatable have been processed
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
sheetPayroll.SetDefaultColumnStyle(4, cellStyle);