Bug within ExportDataTableAsString

Aspose.Cells Product Family
1
Please note my culture is EN-GB.

The date at cell D138 is "11/12/2015" (11 Dec 2015) however when this value is processed via ExportDataTableAsString it becomes "12/11/2015" (12 Nov 2015).

How to re-produce:

===
Workbook workbook = new Workbook(@"C:\Users\Chris\Desktop\FutureAppointments.xls");
Worksheet worksheet = workbook.Worksheets[0];

object test;
// test = worksheet.Cells["D138"].Value; // This works


DataTable excelImport = worksheet.Cells.ExportDataTableAsString(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1, true);
test = excelImport.Rows[136]["AppointmentDate"]; // But this does not

if (test is DateTime)
{
DateTime aa = Convert.ToDateTime(test);
MessageBox.Show("DateTime: " + aa.ToString() + " Month: " + aa.Month.ToString());
}
else
{
MessageBox.Show(test.ToString());
}
===

If the spreadsheet is opened and re-saved it will correctly return 11 December 2015 for some odd reason.

Could you make sure ExportDataTableAsString is looking at the correct culture as aspose & excel does know its a datetime value.

See attached picture this is how it looks in excel / and note how "DateTimeValue" And "DisplayStringValue" have the month swapped around.
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue by executing your sample code with your source excel file using the latest version: Aspose.Cells
for .NET v8.6.2.2. ExportDataTableAsString is not exporting EN-GB culture date correctly as you have mentioned.

We have therefore logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44098 - ExportDataTableAsString is not exporting EN-GB culture date correctly

3

Hi,

Thanks for using Aspose.Cells.

We have looked into this issue further and found you need to set the region before exporting table.

Please see the following sample code for your reference which gives correct output.

C#
Workbook workbook = new Workbook(path + “FutureAppointments.xls”);
workbook.Settings.Region = CountryCode.UnitedKingdom;
Console.WriteLine(workbook.Worksheets[0].Cells[“D138”].DisplayStringValue);

4

Thanks for the fix.


If the purpose of Aspose.Cells is to match excel strictly speaking you should default the region to the calling applications current culture by default.

How you have it currently is inconsistent with .NET in general its not as though I have to do this each time I want an american date:

foreach (string culture in new string[] { “en-US”, “en-GB” })
{
CultureInfo ci = new CultureInfo(culture);
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci;

Console.WriteLine(DateTime.Now.ToString() + “\t” + culture);
}

It can easily be changed within app.config / web.config to whatever I want:


I can understand why you want to leave it as-is for backwards compatibility reasons, but if you want to stand by the above you need to amend your documentation.

Otherwise people will write their own functions, which actually work:

private DataTable ExportDataTableAsString(Worksheet worksheet, int firstRow, int firstColumn, int totalRows, int totalColumns, bool exportColumnName)
{
int exportCol = 1;

DataTable tbl = new DataTable();
for (int i = firstColumn; i < firstColumn + totalColumns; i++)
{
if (exportColumnName)
{
tbl.Columns.Add(worksheet.Cells[firstRow, i].StringValue);
}
else
{
tbl.Columns.Add(“Column” + exportCol);
exportCol++;
}
}

for (int row = (exportColumnName) ? firstRow + 1 : firstRow; row < firstRow + totalRows; row++)
{
exportCol = 0;
DataRow dr = tbl.NewRow();
for (int col = firstColumn; col < firstColumn + totalColumns; col++)
{
dr[exportCol] = worksheet.Cells[row, col].Value;
exportCol++;
}
tbl.Rows.Add(dr);
}

return tbl;
}
5

Hi,

Thanks for sharing your valuable insight and using Aspose.Cells.

It is good to know that the code suggested in the above post works for you. We have also logged your comment in our database for product team consideration. We will look into it and see if your suggested solution could be employed in future versions. Once there is some update for you, we will let you know asap.

6

Hi,

Thanks for using Aspose.Cells.

We have enhanced the document for WorkbookSettings.Region from release 8.7.2. For your issue, it is because the region saved in your template file is not the default one of the environment. For such kind of template, you have to change the region manually. If the saved region is default or loading template files such as XLSX, XLSB…, we will use the default regional settings of the environment automatically.