Invalid data before reference sign '!':


#1

Hello ,
I am getting Invalid data before reference sign ‘!’: L|18-21|%(Based on cell Total Summary!A7) error while capturing screen shot using aspose of the sheet.
i tried to delete the content from the sheet too… but still throws some error.
image.png (38.9 KB)
test.zip (194.7 KB)
Please find the xls in attachment.
thanks


#2

@Gauravmiri,

Thanks for the template file and screenshot.

Could you also provide sample code (runnable) to show the issue, we will check it soon.


#3

public async Task MergeToDoc(SeymourContext spContext,
string tempFile)
{
try
{
DateTime now = DateTime.Now;

            //Download Template File from SharePoint
            //  tempFile = TemplatePath + "/test.xls";
            var templateFileXls = new FileStream(tempFile, FileMode.Open);
            var templateFileDoc = new FileStream(TemplatePath + "/" + MERGEDOCTEMPLATE, FileMode.Open);
            var afterTemplateFile = (DateTime.Now - now).TotalMilliseconds;
            //Create Aspose Document
            var doc = new Document(templateFileDoc);
            Workbook workbook = new Workbook(templateFileXls);
            //Get the first worksheet in the book.
            Worksheet worksheet = workbook.Worksheets[0]; //**Code breaks here**
            doc.CompatibilityOptions.UseWord2010TableStyleRules = true;
            var afterMergeCommon = (DateTime.Now - now).TotalMilliseconds;
            var customPlaceholders = new SffPlaceholderModel();
            //read from sheet and store
            customPlaceholders.BodyCorporate = worksheet.TextBoxes.Where(a => a.Name == "BodyCorporate").Select(b => b.Text).Single().ToString();

            customPlaceholders.ContingencyAllowance = worksheet.TextBoxes.Where(a => a.Name == "ContingencyAllowance").Select(b => b.Text).Single().ToString();
            customPlaceholders.BuildingType = worksheet.Cells["F47"].Value.ToString();
            customPlaceholders.RegisteredForGST = worksheet.Cells["F71"].Value.ToString();
            customPlaceholders.ForecastPeriod = worksheet.Cells["F73"].Value.ToString();
            customPlaceholders.InspectionDate = worksheet.TextBoxes.Where(a => a.Name == "InspectionDate").Select(b => b.Text).Single().ToString();
            customPlaceholders.WorkerId = Convert.ToInt32(worksheet.TextBoxes.Where(a => a.Name == "WorkOrder").Select(b => b.Text).Single().ToString());
            doc = await base.MergeCommon(customPlaceholders, doc, null);
            var docBuilder = new DocumentBuilder(doc);
            //Add screenshots for other sheets  using on bookmarks
            docBuilder.MoveToBookmark("ForecastSummary");
            GenerateImage(docBuilder, workbook, 0);
            docBuilder.MoveToBookmark("TotalSummary");
            GenerateImage(docBuilder, workbook, 1);
            docBuilder.MoveToBookmark("SinkingFundDetails");
            GenerateImage(docBuilder, workbook, 2);

            //Add Page Number
            docBuilder.MoveToHeaderFooter(HeaderFooterType.FooterPrimary);
            var afterMerge = (DateTime.Now - now).TotalMilliseconds;
            var fileName = string.Format("Sff-{0}-{1}.docx", customPlaceholders.PropertyName, customPlaceholders.WorkerId);
            //Return Docx Merged BYTES
            using (var ms = new MemoryStream())
            {
                Aspose.Words.Saving.OoxmlSaveOptions saveOptions = new Aspose.Words.Saving.OoxmlSaveOptions(Aspose.Words.SaveFormat.Docx);
                saveOptions.Compliance = Aspose.Words.Saving.OoxmlCompliance.Iso29500_2008_Strict;
                doc.Save(ms, saveOptions);
                FileModel fileModel = new FileModel(fileName, ms.ToArray());
                var afterDocSave = (DateTime.Now - now).TotalMilliseconds;
                return fileModel;
            }

        }
        catch (Exception ex)
        {
            //TO DO, add loggging
            throw;
        }

    }

#4

@Gauravmiri,

Thanks for the sample code.

After an initial test, I am able to observe the issue as you mentioned by loading your template file via Aspose.Cells APIs. I found an exception “Invalid data before reference sign ‘!’: L|18-21|%(Based on cell Total Summary!A7)” when loading the file:
e.g
Sample code:

Workbook workbook = new Workbook("e:\\test2\\test.xlsx");

I have logged a ticket with an id “CELLSNET-46212” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.


#5

Thank you @Amjad_Sahi .
We are waiting for the response from Aspose as we have to deliver the project soon.
Hope we would get it soon.


#6

@Gauravmiri,

This is to inform you that we have fixed your issue “CELLSNET-46212” now. We will soon provide you the fixed version in the next few days (3-5) after performing QA and incorporating other enhancements and fixes.


#7

@Gauravmiri

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.


#8

Thanks @shakeel.faiz .
This is working for me now.


#9

@Gauravmiri,

You are welcome.


#10

The issues you have found earlier (filed as CELLSNET-46212) have been fixed in Aspose.Cells for .NET v18.7. Please also check the document/article for your reference: https://docs.aspose.com/display/cellsnet/Installation


#11

Hi @shakeel.faiz ,
I am facing this issue again with this xls. demo.zip (195.7 KB)
Could you help me please?

Thanks


#12

@Gauravmiri,

Please try our latest version/fix: Aspose.Cells for .NET v18.7.2

I tried using the simplest lines of code with your newly attached file and it works fine and I do not find the exception:
e.g
Sample code:

  Workbook workbook = new Workbook("e:\\test2\\SFFTemplate-QLD.xlsx");
            //Get the first worksheet in the book.
            Worksheet worksheet = workbook.Worksheets[0];

If you still find the issue with v18.7.2, kindly do paste sample code (runnable) using Aspose.Cells APIs only, we will check it soon.


#13

Alright @Amjad_Sahi ! Updated to recent (108.7.2.0) version. But now facing this issue.
Please see the screenshot.
clipboard-image.png (54.9 KB)
However it was working fine with the old version.

using Aspose.Cells;
using Aspose.Words;
using Aspose.Words.Drawing;
using Aspose.Cells.Drawing;
using Aspose.Cells.Rendering;
using Aspose.Cells.Properties;
using Seymour.Model;
using Seymours.SpAppWeb.BusinessLogic.Merge.ReplaceDelegates;
using Seymours.SpAppWeb.BusinessLogic.Model;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web;
using System.Drawing;

 public async Task<FileModel> MergeToDoc(SeymourContext spContext,
            string tempFile, SinkingFundForecast spSffItem)
        {
            try
            {
                DateTime now = DateTime.Now;

                //Download Template File from SharePoint
                //  tempFile = TemplatePath + "/test.xls";
                var templateFileXls = new FileStream(tempFile, FileMode.Open);
                var templateFileDoc = new FileStream(TemplatePath + "/" + MERGEDOCTEMPLATE, FileMode.Open);
                var afterTemplateFile = (DateTime.Now - now).TotalMilliseconds;
                //Create Aspose Document
                var doc = new Document(templateFileDoc);
                Workbook workbook = new Workbook(templateFileXls);
                //Get the first worksheet in the book.
                Worksheet worksheet = workbook.Worksheets[0];

                doc.CompatibilityOptions.UseWord2010TableStyleRules = true;
                var afterMergeCommon = (DateTime.Now - now).TotalMilliseconds;
                var customPlaceholders = new SffPlaceholderModel();
                //read from sheet and store
                customPlaceholders.BodyCorporate = worksheet.TextBoxes.Where(a => a.Name == "PropertyName").Select(b => b.Text).Single().ToString();
                customPlaceholders.CTSP = worksheet.TextBoxes.Where(a => a.Name == "CTSSP").Select(b => b.Text).Single().ToString();
                customPlaceholders.PropertyName = worksheet.TextBoxes.Where(a => a.Name == "PropertyName").Select(b => b.Text).Single().ToString();
                customPlaceholders.PhysicalAddress = worksheet.TextBoxes.Where(a => a.Name == "PhysicalAddress").Select(b => b.Text).Single().ToString();
                customPlaceholders.PropertySuburb = worksheet.TextBoxes.Where(a => a.Name == "PropertySuburb").Select(b => b.Text).Single().ToString();
                customPlaceholders.PropertyPostcode = worksheet.TextBoxes.Where(a => a.Name == "PropertyPostcode").Select(b => b.Text).Single().ToString();
                customPlaceholders.ReportDate = worksheet.TextBoxes.Where(a => a.Name == "ReportDate").Select(b => b.Text).Single().ToString();
                customPlaceholders.Year = Convert.ToDateTime(worksheet.TextBoxes.Where(a => a.Name == "ReportDate").Select(b => b.Text).Single()).Year;
                customPlaceholders.ConstructionYear = Convert.ToDateTime(worksheet.TextBoxes.Where(a => a.Name == "YearOfConstruction").Select(b => b.Text).Single()).ToString("dd MMM yyyy");
                customPlaceholders.StartDate = worksheet.TextBoxes.Where(a => a.Name == "FinancialYearStartDate").Select(b => b.Text).Single().ToString();
                customPlaceholders.OpeningBalance = worksheet.TextBoxes.Where(a => a.Name == "OpeningBalance").Select(b => b.Text).Single().ToString();
                customPlaceholders.NoOfLots = Convert.ToDecimal(worksheet.TextBoxes.Where(a => a.Name == "NumberofLots").Select(b => b.Text).Single().ToString());
                customPlaceholders.Entitlements = worksheet.TextBoxes.Where(a => a.Name == "NumberofEntitlements").Select(b => b.Text).Single().ToString();
                customPlaceholders.PSFPerLot = worksheet.TextBoxes.Where(a => a.Name == "ProposedSFPerLotEntitlement").Select(b => b.Text).Single().ToString();
                customPlaceholders.PSFAnnual = worksheet.TextBoxes.Where(a => a.Name == "ProposedSFAnnual").Select(b => b.Text).Single().ToString();
                customPlaceholders.TaxationRate = worksheet.TextBoxes.Where(a => a.Name == "CompanyTaxationRate").Select(b => b.Text).Single().ToString();
                customPlaceholders.AllowanceforFutureBuildingCost = worksheet.TextBoxes.Where(a => a.Name == "AllowanceforFutureBuildingCost").Select(b => b.Text).Single().ToString();
                customPlaceholders.ContingencyAllowance = worksheet.TextBoxes.Where(a => a.Name == "ContingencyAllowance").Select(b => b.Text).Single().ToString();
                customPlaceholders.GoodsServiceTax = worksheet.TextBoxes.Where(a => a.Name == "GoodsServiceTax").Select(b => b.Text).Single().ToString();
                customPlaceholders.BuildingType = worksheet.Cells["F48"].Value.ToString();
                customPlaceholders.RegisteredForGST = worksheet.Cells["F72"].Value.ToString().ToUpper();
                customPlaceholders.ForecastPeriod = worksheet.Cells["F74"].Value.ToString();
                customPlaceholders.AsbestosSurvey = worksheet.Cells["F77"].Value.ToString();
                customPlaceholders.InspectionDate = worksheet.TextBoxes.Where(a => a.Name == "InspectionDate").Select(b => b.Text).Single().ToString();
                customPlaceholders.WorkerId = Convert.ToInt32(worksheet.TextBoxes.Where(a => a.Name == "WorkOrder").Select(b => b.Text).Single().ToString());
                doc = await base.MergeCommon(customPlaceholders, doc, null);
                var docBuilder = new DocumentBuilder(doc);
                //Add screenshots for other sheets  using on bookmarks
                docBuilder.MoveToBookmark("ForecastSummary");
                GenerateImage(docBuilder, workbook.Worksheets[1], 530, 590, 100);
                docBuilder.MoveToBookmark("TotalSummary");
                GenerateImage(docBuilder, workbook.Worksheets[2], 800, 590, 75);
                docBuilder.MoveToBookmark("SinkingFundDetails");
                GenerateImage(docBuilder, workbook.Worksheets[3], 800, 590, 58);
                //Add Page Number
                docBuilder.MoveToHeaderFooter(HeaderFooterType.FooterPrimary);
                var afterMerge = (DateTime.Now - now).TotalMilliseconds;
                var fileName = string.Format("Sff-{0}-{1}.docx", customPlaceholders.PropertyName, customPlaceholders.WorkerId);
                //Return Docx Merged BYTES
                using (var ms = new MemoryStream())
                {
                    Aspose.Words.Saving.OoxmlSaveOptions saveOptions = new Aspose.Words.Saving.OoxmlSaveOptions(Aspose.Words.SaveFormat.Docx);
                    saveOptions.Compliance = Aspose.Words.Saving.OoxmlCompliance.Iso29500_2008_Strict;
                    doc.Save(ms, saveOptions);
                    FileModel fileModel = new FileModel(fileName, ms.ToArray());
                    var afterDocSave = (DateTime.Now - now).TotalMilliseconds;
                    //Update the list with xls data
                    updateSffList(spContext, customPlaceholders, spSffItem.ID, spSffItem.WorkOrderID);
                    //retur the file
                    return fileModel;
                }

            }
            catch (Exception ex)
            {
                //TO DO, add loggging
                throw;
            }

        }

Thanks


#14

@Gauravmiri,

I think as we provided the fix Aspose.Cells for .NET v18.7.2 compiled on .NET 2.0 so you are getting this issue. Please wait a bit (2/3 days or so) as we will provide you the fix compiled on .NET framework 4.0 which will work fine for your project/sample code.

Keep in touch.


#15

Okay Thank you


#16

Hello @Amjad_Sahi,
We are waiting for the update,could you please tell me , how long it would take to resolve this??
Thanks


#17

@Gauravmiri,

Please find attached our latest version/fix: Aspose.Cells for .NET v18.7.4 (.NET 4.0)

Your issue should be fixed in it.

Let us know your feedback.
Aspose.Cells18.7.4 For .Net4.0.Zip (3.8 MB)


#18

Thank you @Amjad_Sahi
This has worked well.
:slight_smile:


#19

@Gauravmiri,

Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.