Hi team,
during last tests of printing with TMS.Flexcel 7.16.0, we are were facing an issue with our .xlsx print templates. It seems that the date formats of cells are ignored when generating output files.
How the output looked before:
We created a simple app that demonstrate how we use the TMS.Flexcel for printing.
AndroidFlexcelTestApp.zip (101.1 KB)
Thank you,
Kind regards,
Megan
Hi,
I don't think this is related with the FlexCel version, but more to the SQLite component you are using. Maybe you changed from System.Data.SQLite to Microsoft.Data.Sqlite lately (as many of us did)?
If so, probably the answer is here: Comparison to System.Data.SQLite - Microsoft.Data.Sqlite | Microsoft Learn
Blockquote
Microsoft.Data.Sqlite doesn't try to hide the underlying quirkiness of SQLite, which allows any arbitrary string to be specified as the column type, and only has four primitive types: INTEGER, REAL, TEXT, and BLOB.
There is no datetime above. If you add this code to your example:
fr.DisableSQLValidation = true;
connection.Open();
SQLiteDataAdapter adapter = PrepareColdStartDatabase(connection);
// Add here
adapter.SelectCommand = connection.CreateCommand();
adapter.SelectCommand.CommandText = "Select DB_VERSION, CREATED_DATE, DELIVERY_DATE, datetime('now') as NOW_DATE from _dbinfo";
System.Data.DataTable table = new System.Data.DataTable("test");
adapter.Fill(table);
for (int i = 0; i < table.Columns.Count; i++)
{
Alert(table.Columns[i].ColumnName , table.Columns[i].DataType.ToString());
}
fr.AddConnection("db", adapter, CultureInfo.InvariantCulture);
fr.Run(coldStartTemplateStream, flexCelReportResultStream);
connection.Close();
You'll see this result:
[DOTNET] DB_VERSION -> System.Double
[DOTNET] CREATED_DATE -> System.String
[DOTNET] DELIVERY_DATE -> System.String
[DOTNET] NOW_DATE -> System.String
As you can see, even when you defined the field DELIVERY_DATE as DateTime, it is handled by Microsoft.SQLite as TEXT.
Now, of course the problem here is that if you pass TEXT to FlexCel, it will enter the text into the cell. And even if the string "1-1-2000" looks the same as the date 1-1-2000 they are completely different things to Excel. This is why you see the text left-aligned in Excel: because in General alignment in Excel, strings are left-aligned and numbers or dates are right-aligned.
But text strings don't obey numeric formats (how would you format the string "potato" for example?), so that's why you see the strings as the SQLite adapter passed them to FlexCel. You passed the string "2022-11-28" to FlexCel, and it wrote that string into the cell. FlexCel didn't even realized it was supposed to be a date.
Now, about how to fix it... I am not really sure. My advise would be to use some db engine that doesn't store the datetimes as strings. Or I don't know if Microsoft.Data.SQLite has an option to use the datatypes of the columns, like the old System.Data.SQLite did. But they seem kind of proud that "Microsoft.Data.Sqlite doesn't try to hide the underlying quirkiness of SQLite" so I am not sure if there is an option.
Other (quirky) option is to define DELIVERY_DATE and the others as numeric instead of DateTime (which in Microsoft.SQLite means "string"). You would have to store the equivalent number instead of a string with text, but it should work. You can use code like:
Date_Number = FlexCel.Core.FlxDateTime.ToOADate(net_datetime, false);
To get the number you need to store from a DateTime. (in Excel, all dates are numbers).
Other option could be to define those fields as string, not datetimes, and pass the strings you want displayed directly. Of course, now the numeric format in Excel won't matter, because you have a string in the cell not a real date. But it should work.
Finally, you can tell FlexCel to try to convert those strings SQLite is passing to FlexCel back to numbers, but this really means 2 conversions and can lead to bugs, so I would avoid this option. But if you really need it, you can write in E18:
<#evaluate(VALUE(<#_dbinfo.DELIVERY_DATE>))>
And it will convert that string back to a date. But as said, I wouldn't recommend this and use it just as a last resort. The correct way to solve this is to pass the correct datatypes to FlexCel so it enters them into the cell, not to convert the dates to strings, then the strings back to dates.