Sorted output when merging and grouping using Smart Markers

Aspose.Cells Product Family
1

Hello,

we are trying to sort and group the output in a case where we are using Smart Markers to merge data into an Excel template.

We have created a small application that performs a minimum merge, se code at the end of this post.

See the attached files "MergeTemplate not Sorted.xlsx" with accompanying data "MergeData.xml" for reference. (with result file "Output not Sorted.xlsx") As the output file shows, the data follows the same order as in the xml data.

From an article we found regarding use of Smart Markers (http://www.aspose.com/docs/display/cellsnet/Smart+Markers) we are trying to find out how we can get the desired behaviour where we first group on one column (Project) and then sort the data on the same column (Project) and then on another column (Date). We have yet not figured out how this can be done.

We have tried to create a template with both sorting and grouping but the result from this merge is not as desired. See attached files "MergeTemplate Sorted on Date.xlsx" and "Output Sorted on Date.xlsx" for reference. As you can see, the rows are placed out of order compared with how we would expect them to appear.

Should it possible to group data (using the group:normal attribute) at the same time as other columns are sorted (with the ascending:N attribute)? Or should we try to sort the data in advance before we merge the data into the document?

thanks,

Johnny Andersen

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,


Thanks for providing us template files and sample code.

I have evaluated your issue a bit (checked and tested your files e.g “MergeTemplate Sorted on Date.xlsx” with your template XML file and sample codes). Well, I am afraid, we don’t support sorting while grouping data in Smart Markers, so we request you to kindly try to sort your data by yourself in your data source prior processing with markers.

Thank you.
3

Hello,

and thanks for your response. Would it be possible that you could implement sorting in Smart Markers?

From my testing and failing attempts, it seems that sorting is kind of implemented, but is performed after the grouping operation has been performed, which again leads to incorrect order of the data rows.

thanks

Johnny Andersen

4

Hi,


I have logged a ticket with an id “CELLSNET-41460” for your enhancements/issue. Our concerned developer may look into it if we can support it. Once we have any update on it, we will let you know here.

Thank you.
5

Hi,

Thanks for using Aspose.Cells.

We are afraid, we could not support sorting data in Group in smart marker.

We process the sort attribute after processing all other smart marker, so it will corrupt the group layout.

It’s hard to implement it that we have to sort the data source with group setting before processing other smart markers.

So please sort the data by yourself. Thanks for your understanding.