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:
- Set the format of the cell to a date string.
- 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 following namespace has been imported:
'--- SoftArtisans.OfficeWriter.ExcelWriter
'--- Instantiate ExcelApplication and create a new Workbook
Dim xla As New ExcelApplication
Dim wb As Workbook = xla.Create()
Dim sheet1 As Worksheet = wb.Worksheets(0)
'--- WRITE DESCRIPTIONS (for illustrative purposes)
'--- Write descripton for US format in cell A1
Dim a1 As Cell = sheet1.Cells("A1")
a1.Value = "US Date format (m/d/yyyy): "
'--- Write description for UK format in cell A2
Dim a2 As Cell = 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
Dim d As New DateTime(2005, 3, 12)
'--- Write date to cell B1, and display it with a US format
Dim b1 As Cell = sheet1.Cells("B1")
b1.Style.NumberFormat = "m/d/yyyy"
b1.Value = d
'--- Write date to cell B2, and display it with a UK format
Dim b2 As Cell = sheet1.Cells("B2")
b2.Style.NumberFormat = "d/m/yyyy"
b2.Value = d
'--- Save Workbook
xla.Save(wb, Response, "out.xls", False)
//--- The following packages have been imported:
//--- java.util.*
//--- com.softartisans.excelwriter.*
//--- Instantiate ExcelApplication and create a new Workbook
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.create();
Worksheet sheet1 = wb.getWorksheet(0);
//--- WRITE DESCRIPTIONS (for illustrative purposes)
//--- Write description for US format in cell A1
Cell a1 = sheet1.getCell("A1");
a1.setValue("US Date format (m/d/yyyy): ");
//--- Write description for UK format in cell A2
Cell a2 = sheet1.getCell("A2");
a2.setValue("UK Date format (d/m/yyyy): ");
//--- Autofit the width for column A
sheet1.getColumnProperties(0).autoFitWidth();
//--- CREATE, WRITE AND FORMAT DATES
//--- Create a Date object for March 12, 2005
Calendar cal = Calendar.getInstance();
cal.set(Calendar.MONTH, Calendar.MARCH);
cal.set(Calendar.DAY_OF_MONTH, 12);
cal.set(Calendar.YEAR, 2005);
Date d = cal.getTime();
//--- Write date to cell B2, and display it with a US format
Cell b1 = sheet1.getCell("B1");
b1.getStyle().setNumberFormat("m/d/yyyy");
b1.setValue(d);
//--- Write date to cell B2, and display it with a UK format
Cell b2 = sheet1.getCell("B2");
b2.getStyle().setNumberFormat("d/m/yyyy");
b2.setValue(d);
//--- Save Workbook
xla.save(wb, response, "out.xls", false);
|
The generated file looks like this:
|
