Suport for DateTime when merging using Smart Markers

Aspose.Cells Product Family
1

Hello,

I am currently using Smart Markers in a project with numeric values and dates working as expected. The data is merged from an Xml-document into an Xlsx-template, see code at the end of this post for an example application. I have also attached sample Xml data and Merge Template.

However, when trying to use DateTime values these show up as text in the resulting document, while normal dates work fine. In the template, I have used the "numeric" parameter on the cells containing dates and datetimes. Formatting also works fine on the regular date values.

Is merge of both Date and DateTime values supported when executing Smart Markers, and if not, would it be possible to add support for this in a later version?

thanks,

Henrik

namespace AsposeCellsMerge
{
    using System.Diagnostics;
    using System.IO;
    using System.Data;

<span style="color: blue;">using</span> Aspose.Cells;

<span style="color: blue;">class</span> <span style="color: rgb(43, 145, 175);">Program</span>
{
    <span style="color: blue;">static</span> <span style="color: blue;">void</span> Main(<span style="color: blue;">string</span>[] args)
    {
        <span style="color: blue;">if</span> (args.Length < 2)
        {
            <span style="color: rgb(43, 145, 175);">Trace</span>.WriteLine(<span style="color: rgb(163, 21, 21);">"Invalid number of arguments: Expecting AsposeCellsMerge.exe <template> <data>"</span>);
            <span style="color: blue;">return</span>;
        }

        <span style="color: blue;">string</span> l_sourceFile = args[0];

        <span style="color: blue;">if</span> (!<span style="color: rgb(43, 145, 175);">File</span>.Exists(l_sourceFile))
        {
            <span style="color: rgb(43, 145, 175);">Trace</span>.WriteLine(<span style="color: rgb(163, 21, 21);">"Specified source file does not exist: "</span> + l_sourceFile);
            <span style="color: blue;">return</span>;
        }

        <span style="color: blue;">string</span> l_dataFile = args[1];

        <span style="color: blue;">if</span> (!<span style="color: rgb(43, 145, 175);">File</span>.Exists(l_sourceFile))
        {
            <span style="color: rgb(43, 145, 175);">Trace</span>.WriteLine(<span style="color: rgb(163, 21, 21);">"Specified data file does not exist: "</span> + l_dataFile);
            <span style="color: blue;">return</span>;
        }

        <span style="color: blue;">string</span> l_targetFile = l_sourceFile + <span style="color: rgb(163, 21, 21);">".xlsx"</span>;

        Aspose.Cells.<span style="color: rgb(43, 145, 175);">Workbook</span> l_workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(l_sourceFile);

        <span style="color: rgb(43, 145, 175);">DataSet</span> l_xmlDataSet = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">DataSet</span>();

        l_xmlDataSet.ReadXml(l_dataFile);

        <span style="color: blue;">var</span> l_designer = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">WorkbookDesigner</span>();
        l_designer.Workbook = l_workbook;

        l_designer.SetDataSource(l_xmlDataSet);

        <span style="color: green;">// Process the smart markers</span>
        l_designer.Process();

        l_designer.Workbook.CalculateFormula(<span style="color: blue;">true</span>);

        l_workbook.Save(l_targetFile, <span style="color: rgb(43, 145, 175);">FileFormatType</span>.Xlsx);
    }
}



}
2

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v7.4.1.2.

I have tested using your attached files and it works fine. The DateTime values in both fields “Created” and “Modified” are displayed as DateTime as I can check it by opening the output file into MS Excel and right click on the C2 and D2 cells to display the Format Cells… dialog box.

Moreover, Please note Aspose.Cells works the same way as MS Excel does for your so called DateTime formatting used in the xml file. You may insert or import the xml datetime values into MS Excel manually and check it for confirmation.

Here is the sample code that I am using.

Sample code:

string l_sourceFile = “e:\test2\MergeTemplate.xlsx”;

Aspose.Cells.Workbook l_workbook = new Workbook(l_sourceFile);

DataSet l_xmlDataSet = new DataSet();

l_xmlDataSet.ReadXml(“e:\test2\MergeData.xml”);

var l_designer = new WorkbookDesigner();
l_designer.Workbook = l_workbook;

l_designer.SetDataSource(l_xmlDataSet);

// Process the smart markers
l_designer.Process();

l_designer.Workbook.CalculateFormula(true);

l_workbook.Save(“e:\test2\outMergedatafile.xlsx”);

I have also attached the output file here.

If you still think that Aspose.Cells does not work as per MS Excel, please give us your file that you may manually create and input your desired formatting for your datetime values. We will check it soon.

Thank you.

3

Hello,

and thanks for your response. I tried to open the file you attached in your last post (outMergeDataFile.xlsx) and when opening the file in Excel 2013 the DataTime values in the Modified column are not formatted as dates. (They are strings formatted as they were imported - XML DateTime) See attached screenshot.

thanks,

Henrik

4

Hi,


How could you do this in MS Excel, how MS Excel should understand your data i.e. “2012-12-20T16:00:08” is date? there is not any such formatting involved I think. I requested you to insert this value into MS Excel and you will notice that MS Excel too will take it as General or string and not as DateTime even after clicking double click and pressing enter. If you have other thoughts, let us know with your Excel template file having your desired formatting for your data that you can make it manually in MS Excel, attach the file here with details, we will check it soon.

Thank you.
5

Hello again,

I have tested what you suggested in Excel, and as you said, dates on the format "yyyy-mm-dd" are interpreted as dates when entered, while "DateTime" formatted as "yyyy-mm-ddThh:mm:ss" are interpreted as text. So in this way I agree that Aspose.Cells works the same way as Excel would do.

However, it would still be very convenient if Aspose.Cells could interpret the format "yyyy-mm-ddThh:mm:ss" when processing Smart Markers during merge from Xml, as this would allow the use of the standard Xml DateTime type as specified in the Xml Schema (http://www.w3.org/2001/XMLSchema). With the current solution, we will have to use a non-standard format on DateTime values when sending xml-data to Aspose.Cells for merging.

I understand that this request does not conform exactly to how Excel is working, but since there is already special handing in Aspose.Cells when performing merge using Smart Markers (for instance the parameters numeric, ascending, skip++) it would be very useful to also add better support for dates than the current version can offer.

thanks,

Henrik

6

Hi,


Could you attach your expected output file (by manually updating the output file (in MS Excel) generated by Aspose.Cells after procssing the smart markers) with your desired DateTime formatting set for the “Modified” field. We will check and log a ticket into our database for it if we can support it.

Thank you.
7

Hello,

and thanks for your response. I have created an Xlsx file where I have manually entered desired (and expected) result from merging with the file "mergeData.xml" attached in the first post of this issue. See the attached file "desired merge result.xlsx". I have also tried to illustrate how I would expect the merge to work in the attached illustration "Desired Result.png".

By inspecting the current output result (the file you posted as "outMergeDatafile.xlsx"), I also found a much more severe problem:

When merging in the Xml date "2012-03-12", which means "12-mar-2012", it is saved in the Xlsx file as "03-dec-2012". This is very critical for us as it will lead to actual data errors in our clients' reports.

Hope you can look into this issue soon.

thanks,

Henrik

8

Hi,


Thanks for providing further details and expected file.

Since you have manually inserted your desired date time which may not have any links when you insert your datetime value into MS Excel directly to automatically convert them to your desired DateTime formatting. We have to look into it if it can be feasible to build a valid logic for it as after all we have to follow MS Excel standards.

Anyways, I have logged a ticket with an id “CELLSNET-41482” for your issue/case. We will look into it. Once we have any update on it, we will let you know here.

Also for: "By inspecting the current output result (the file you posted as “outMergeDatafile.xlsx”), I also found a much more severe problem:
When merging in the Xml date “2012-03-12”, which means “12-mar-2012”, it is saved in the Xlsx file as “03-dec-2012”. This is very critical for us as it will lead to actual data errors in our clients’ reports"

I think it is due to my locale/region and time zone setting for my OS on my pc. Anyways, we will also look into it further.

Thank you.
9

Hi,


Well, we have looked into your issue. If you input "2012-12-20T16:00:08" into MS Excel, MS Excel will not convert to a date time value as it is not a standard DateTime format by any means. So, we cannot build a logic for it. Aspose.Cells works the same way as MS Excel.


Thanks for your understanding!

10
Hi,

I specified a custom format for a cell within my smart marker block as "dd-mmm-yyyy", but when I pass in a string value of "20/02/2012" it just drops it like that in that field. If I double-click into the cell and press Enter, the formatting then appears on the Excel Spreadsheet.

Can you advise me on how to solve this problem without any manual intervention?

Regards,

Kwex
11

Hi,


"I specified a custom format for a cell within my smart marker block as “dd-mmm-yyyy”, but when I pass in a string value of “20/02/2012” it just drops it like that in that field. If I double-click into the cell and press Enter, the formatting then appears on the Excel Spreadsheet."

Well, surely, when you pass a string value (pased as Date value) into the cell, it will be taken as string and not DateTime value, so your formatting “dd-mmm-yyyy” won’t be implemented. You have to input “20/02/2012” as DateTime value and not string. Moreover, you may confirm this behavior in MS Excel, right click on a cell and click “Format Cells…”, now click “Text”. Now, when you specify any formatting to the cell, it won’t be taken into account until you convert it into Numeric or DateTime value (you have to double click on the cell and press so that Excel could convert the string to numeric values).

Thank you.
12

Hi,

Thanks for your response. Would you be able to demonstrate how you can programmatically pass in a value to MS Excel as a DateTime value using SmartMarkers as per your suggestion?

I populate the SmartMarkers using a DataSet datasource as below. Using this method, the datatype cannot be specified when processing the template.

// Build dataset
var dsXML = new DataSet();
dsXML.ReadXml(new XmlTextReader(new StringReader(xml)));
designer.SetDataSource(dsXML);

// Process
designer.Process(false);

Thanks.

13

Hi,


Well, as I told you, to apply DateTime formatting on the data, it should be converted to DateTime value first. I am afraid, there is no option or way in Smart Markers to specify or convert to DateTime for your string value. I think either you may try to convert your data to DateTime in the DateTable after you import the XML file to fill into the DataSet table by yourself or you have to do this by using e.g. cells [“B2”].PutValue(Convert.ToDateTime(cells[“B2”].StringValue)); after your markers are processed and data is filled into the cells.

Thank you.

14

Hi,

Thanks for using Aspose.Cells.

It is to inform you that we have fixed your issue CELLSNET-41482 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

15

The issues you have found earlier (filed as CELLSNET-41482) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.