Home     Products      Support      Corporate     Sign In 
Support Knowledge Base, Article 1192
Product
ExcelWriter
Version
5.x and later
Title
Populating dates in non-US formats using ExcelWriter
Problem
How do I populate dates to display in non-US formats using ExcelWriter?
Solution

Setting dates to be displayed in non-US formats is no different than setting any other type of format on a cell. With a date (as with other types), this is done in two steps:

  1. Set the format of the cell to a date string.
  2. Set the value of the cell to a date with a Date (Java) or DateTime (.NET) object.

(Note: The order of these steps can be reversed.)

1. Set the format of the cell to a date string

First, set the format of the cells based on how you want the dates to be displayed. This can be done in Excel if we are planning to open an existing file, or can be set in code using the ExcelApplication class.

To set the format in Excel, select the cell and then choose "Cells" from the "Format" menu. In the case of a UK format, select "Custom" and then enter the formatting string in the "Type" box:

To set the format with ExcelWriter, use the Cell.getStyle().setNumberFormat() method (Java) or Cell.Style.NumberFormat property (.NET), passing in or assigning the same number format string that you would use in Excel.

2. Set the value of the cell to a date

Next, set the value of the cell by passing a Date object to the Cell.setValue() method (Java) or by assigning a DateTime object to the Cell.Value property (.NET). Be sure not to pass a String. ExcelWriter uses the type of the object that the value is assigned with to determine how to write it to the Excel file. If you do have a date that was inserted as a String instead of as a Date (Java) or DateTime (.NET) object, the formatting will not be applied by Excel when you view the spreadsheet and a little arrow on that cell will warn you that this data is stored as a string.

Sample code

The following example demonstrates how to set the date March 12, 2005 to be displayed in a US format (m/d/yyyy) and in a UK format (d/m/yyyy). It demonstrates formatting and setting the dates using ExcelWriter.

[C#] [VB.NET] [Java]  
//--- The following namespace has been imported:
//---   SoftArtisans.OfficeWriter.ExcelWriter

//--- Instantiate ExcelApplication and create a new Workbook
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet sheet1 = wb.Worksheets[0];

//--- WRITE DESCRIPTIONS (for illustrative purposes)

//--- Write description for US format in cell A1
Cell a1 = sheet1.Cells["A1"];
a1.Value = "US Date format (m/d/yyyy): ";

//--- Write description for UK format in cell A2
Cell a2 = sheet1.Cells["A2"];
a2.Value = "UK Date format (d/m/yyyy): ";

//--- Autofit the width for column A
sheet1.GetColumnProperties(0).AutoFitWidth();

//--- CREATE, WRITE AND FORMAT DATES

//--- Create a DateTime object for March 12, 2005
DateTime d = new DateTime(2005, 3, 12);

//--- Write date to cell B1, and display it with a US format
Cell b1 = sheet1.Cells["B1"];
b1.Style.NumberFormat = "m/d/yyyy";
b1.Value = d;

//--- Write date to cell B2, and display it with a UK format
Cell b2 = sheet1.Cells["B2"];
b2.Style.NumberFormat = "d/m/yyyy";
b2.Value = d;

//--- Save Workbook
xla.Save(wb, Response, "out.xls", false);

The generated file looks like this:

Related Links
OfficeWriter Home Page
Purchase OfficeWriter
OfficeWriter Enterprise Edition
Latest OfficeWriter News
OfficeWriter: Programmatic Runtime Control

Created : 10/6/2005 3:14:11 PM (last modified : 8/22/2006 12:55:18 PM)
Rate this article!
 
Comments