Aspose.Cell - Unused smart markers

Aspose.Cells Product Family
1

Hi all,

beginning with SmartMarker, I use the code below to merge Excel template and input DataSet.

The output document is attached.

Looking to documentation, I can read:

"Smart Markers consist of the Data Source and Field Name parts for most information. Special information may also be passed with variables and variable arrays. Variables always fill only one cell whereas variable arrays may fill several ones. You may only use one data marker per cell. Unused smart markers will be removed."

The problem is is that the unused smart markers stay in the document (working with Aspose.Cell 7.0.3.0).

Am I missing something or the code should contain a setting?

Thanks.

Mirek

public static Workbook ExcelAspose(Template selectedTemplate, DataSet inputDataSet)

{

TraceLineToDiagnostic("OfficeTemplatesAspose.ExcelAspose called");

InicializeAsposeLicence();

var templateStream = new MemoryStream(selectedTemplate.TemplateDocument.ToArray());

var wd = new WorkbookDesigner

{

Workbook = new Workbook(templateStream)

};

wd.SetDataSource(inputDataSet);

wd.Process(true);

return wd.Workbook;

}

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use WorkbookDesigner.Process(false) or WorkbookDesigner.Process() methods.

When you will use parameter less or when you use false parameter, unused smart markers will be eliminated.

3

Hi,


I think you may try to change the line of your code:
//…
wd.Process(true);
to:
wd.Process(false);
//..........

Put "False" for isPreserved Boolean parameter for the WorkbookDesigner.Process() method.

Thank you.
4

Hi,

Thanks very much for your advice, this helped.

However, we have one more issue: we need a few formulas inserted into the spreadsheet, but they have to be dynamic as we don't know the row numbers.

We found on the Aspose Web site the use of Dynamic Formulas. They worked. The only problem was that they do not disappear if there was no data, the formula - &=&=ROUND((G{r}/E{r})-1,3) still remains in rows where there are no products. Therefore, even though we are now hiding empty rows, placing this formula in the row makes the row now 'not empty'.

Is it possible to suppress any rows that just have empty formulas?

I have attached the example.

Thanks very much.

Mirek
5

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the latest version:

Aspose.Cells for .NET 7.2.2


Please provide us your sample runnable project replicating this problem, we will soon look into it and give you advice.

If there is some bug, we will provide you a fix.

6

Hi,

thanks for your tip => using the ver.7.2.2 we have a change:

- It appears now that the formula is properly rendered, whereas before is was still Aspose tag

- However, the row that the formula resides in is not deleted, as it does not recognize as 'empty' row.

So what we have is a series of DIV/0 errors throughout doc, pls. see the attached docuement.

Below is the code used to render the Excel template.

Thanks for your advice how to remove the DIV/0 errors and the rows where they sit.

Mirek

------------------------------------------------------------------------------------------------

public static Workbook ExcelAspose(Template selectedTemplate, DataSet inputDataSet, bool removeEmptyParagraphs)

{

TraceLineToDiagnostic("OfficeTemplatesAspose.ExcelAspose called");

InicializeAsposeLicence();

var templateStream = new MemoryStream(selectedTemplate.TemplateDocument.ToArray());

var wd = new WorkbookDesigner

{

Workbook = new Workbook(templateStream)

};

wd.SetDataSource(inputDataSet);

wd.Process(false);

TryToDeleteBlankRows(wd, removeEmptyParagraphs);

return wd.Workbook;

}

private static void TryToDeleteBlankRows(WorkbookDesigner wd, bool removeEmptyParagraphs)

{

if (!removeEmptyParagraphs) return;

foreach (Worksheet sheet in wd.Workbook.Worksheets)

sheet.Cells.DeleteBlankRows();

}
7

Hi,


Thank you for your feedback. Please share your Unpopulated designer file, containing smart markers, as well for our reference. We will look into this issue and update you ASAP.
8

Hi,


Well, I think you may try a workaround. After your markers are processed and data or error(formula) values are filled into the cells. You may delete the error values (iterating through the cells) and then remove the blank rows. See the sample code segment below for your reference.

Sample code:

‘Your code goes here.
’…

'Iterate all the rows in the dynamic formula column cells.
‘e.g
For Each cell As Aspose.Cells.Cell In workbook.Worksheets(0).Cells
If cell.IsErrorValue Then
cell.PutValue(Nothing)
End If

Next cell
workbook.Worksheets(0).Cells.DeleteBlankRows()

’…

Thank you.
9

Thanks very much for your tip.

It seems that the workaround works for the smart markers, however the problem stays with dynamic formulas.

I have attached two files:

1) SmartMarkers.xls is the input template

2) MailMergedSmartMarkersExcel.xls is similar to the output file in case I use the code below where I try to implement your tip.

It seems that the inputTemplate's formulas (the cell H12 and others in H column) left in sheet after the Process(false) because cell.IsErrorValue is false.

So, hopefully the last question is if there is a trick how to remove unused "&=&=..." formulas

Thanks again for your advice.

Mirek

-------------------------------------------------------------------------------------

        public static Workbook ExcelAspose(Template selectedTemplate, DataSet inputDataSet, bool removeEmptyParagraphs)
        {
            TraceLineToDiagnostic("OfficeTemplatesAspose.ExcelAspose called");

        InicializeAsposeLicence();

        <span style="color: blue;">var</span> templateStream = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">MemoryStream</span>(selectedTemplate.TemplateDocument.ToArray());

        <span style="color: blue;">var</span> wd = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">WorkbookDesigner</span>
                     {
                         Workbook = <span style="color: blue;">new</span> <span style="color: rgb(43, 145, 175);">Workbook</span>(templateStream)
                     };

        wd.SetDataSource(inputDataSet);

        wd.Process(<span style="color: blue;">false</span>);

        TryToDeleteBlankRows(wd, removeEmptyParagraphs);

        <span style="color: blue;">return</span> wd.Workbook;
    }

    <span style="color: blue;">private</span> <span style="color: blue;">static</span> <span style="color: blue;">void</span> TryToDeleteBlankRows(<span style="color: rgb(43, 145, 175);">WorkbookDesigner</span> wd, <span style="color: blue;">bool</span> removeEmptyParagraphs)
    {
        <span style="color: blue;">if</span> (!removeEmptyParagraphs) <span style="color: blue;">return</span>;

        <span style="color: blue;">foreach</span> (<span style="color: rgb(43, 145, 175);">Worksheet</span> sheet <span style="color: blue;">in</span> wd.Workbook.Worksheets)
        {
            <span style="color: blue;">foreach</span> (<span style="color: blue;">var</span> cell <span style="color: blue;">in</span> sheet.Cells.Cast<<span style="color: rgb(43, 145, 175);">Cell</span>>().Where(cell => cell.IsErrorValue))
            {
                cell.PutValue(<span style="color: blue;">null</span>);
            }

            sheet.Cells.DeleteBlankRows();
        }
    }
10

Hi,


I think you should call Workbook.CalculateFormula() before iterating through cells to find the error values, so that all the formulas should be evaluated fine e.g
//…
wd.SetDataSource(inputDataSet);

        wd.Process(<span style="color: blue; ">false</span>);</pre><pre style="font-size: 13px; background-color: white; font-family: Consolas; "><span class="Apple-tab-span" style="white-space:pre">	</span><b>wd.Workbook.CalculateFormula();</b>

        TryToDeleteBlankRows(wd, removeEmptyParagraphs);</pre></div><div>//.......</div><div><br></div><div><br></div><div>Here is my previous sample code that works fine. I used your output file and tried to test if it deletes the blank rows after setting the error values to null.</div><div><br></div><div><b>Sample code:</b></div><div><br></div><div><div>     Workbook excel = new Workbook(@"e:\test2\MailMergedSmartMarkersExcel.xls");</div><div>            </div><div>            //Iterate all the rows in the processed dynamic formula column cells/error values.</div><div>            foreach(Aspose.Cells.Cell cell in excel.Worksheets[0].Cells)</div><div>            {</div><div>                </div><div>                if (cell.IsErrorValue)</div><div>                {</div><div>                    cell.PutValue(null);</div><div>                }</div><div>                </div><div>            }</div><div>            excel.Worksheets[0].Cells.DeleteBlankRows();</div><div>            excel.Save(@"e:\test2\outputfile.xls");</div></div><div><br></div><div>Thank you.</div>