| Custom date format localized and Excel compatibility [message #664272] |
Fri, 08 April 2011 08:15  |
 RWIL Mising name
Messages: 30
Registered: February 2011 |
Member |
|
|
Hi,
I would like to display a date in my report. If I keep the default format for date ("unformatted"), my date is localized ("31 dec. 2010" in English, "31 déc. 2010" in French, ...) and when the report is exported to Excel, the field is an Excel date.
But I would like to display the date in another format : I would like to use the format dd/MM/yyyy. But when I define this custom format for my date, the date is no longer localized : it should be MM/dd/yyyy in American English, dd/MM/yyyyy in French ou in UK English, dd.MM.yyyy in german, ...It's frustrating, because it works with the number format : when I use the custom format #,##0.00, this custom format is correctly localized (1.234,56 in French, 1,234.56 in English, 1'234,56 in Switzerland, ...).
That's the first issue : how can I define a custom date format that will be localized ?
And there is also a second issue : as soon as I use a custom date format, the export to Excel is no longer working : Excel see the date as a text value, not a date. Of course, this is bad.
I've tried to use a javascript function to format my date accordingly to the locale. It works, of course, but as for custom date format, the export to Excel is no longer working : the date becomes an Excel text field, not a Excel date field.
Any idea ?
Here is a very simple report in which the problem occurs : the same date is displayed twice, one with the default date format and one with the custom date format. You have to run the report with different locale to see the first issue and you have to export to Excel to see the second issue.
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.6.2.r262_v20110209 Build <2.6.2.v20110214-1523></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
<data-sources>
<script-data-source name="myScriptedDataSource" id="7"/>
</data-sources>
<data-sets>
<script-data-set name="myScriptedDataSet" id="8">
<list-property name="resultSetHints">
<structure>
<property name="position">0</property>
<property name="name">myColumnDate</property>
<property name="dataType">date</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">myColumnDate</property>
<text-property name="displayName">myColumnDate</text-property>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">myColumnDate</property>
<property name="dataType">date</property>
</structure>
</list-property>
</structure>
<method name="beforeOpen"><![CDATA[this.iCount = 0;]]></method>
<property name="dataSource">myScriptedDataSource</property>
<method name="fetch"><![CDATA[// Ensure that one row will be fetched
if (this.iCount > 0) {
return false;
}
this.iCount++;
// Init the date by default to 31 december 2010
row.myColumnDate = (new Packages.java.text.SimpleDateFormat("yyyy-MM-dd")).parse("2010-12-31 ");
return true;]]></method>
</script-data-set>
</data-sets>
<styles>
<style name="report" id="4">
<property name="fontFamily">sans-serif</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="9">
<property name="dataSet">myScriptedDataSet</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">myColumnDate</property>
<text-property name="displayName">myColumnDate</text-property>
<expression name="expression" type="javascript">dataSetRow["myColumnDate"]</expression >
<property name="dataType">date</property>
</structure>
</list-property>
<column id="18"/>
<column id="31"/>
<header>
<row id="10">
<cell id="11">
<label id="12">
<text-property name="text">myColumnDate</text-property>
</label>
</cell>
<cell id="27"/>
</row>
</header>
<detail>
<row id="13">
<cell id="14">
<label id="20">
<property name="display">block</property>
<text-property name="text">The default date format :</text-property>
</label>
</cell>
<cell id="28">
<data id="15">
<property name="whiteSpace">nowrap</property>
<property name="display">inline</property>
<property name="resultSetColumn">myColumnDate</property>
</data>
</cell>
</row>
<row id="23">
<cell id="24">
<label id="25">
<property name="display">block</property>
<text-property name="text">The date format I would like to have :</text-property>
</label>
</cell>
<cell id="29">
<data id="26">
<structure name="dateTimeFormat">
<property name="category">Custom</property>
<property name="pattern">dd/MM/yyyy</property>
</structure>
<property name="whiteSpace">nowrap</property>
<property name="display">inline</property>
<property name="resultSetColumn">myColumnDate</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="16">
<cell id="17"/>
<cell id="30"/>
</row>
</footer>
</table>
</body>
</report>
|
|
|
|
Powered by
FUDForum. Page generated in 0.03518 seconds
] 
Search
Help
Register
Login
Home
