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
Thanks for the template file and screenshot.
Could you also provide sample code (runnable) to show the issue, we will check it soon.
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;
}
}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.
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.
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.
Thanks for using Aspose APIs.
Please download and try the following fix and let us know your feedback.
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: Install Aspose Cells through NuGet|Documentation
Hi @shakeel.faiz ,
I am facing this issue again with this xls. demo.zip (195.7 KB)
Could you help me please?
Thanks
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.
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
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.
Okay Thank you
Hello @Amjad_Sahi,
We are waiting for the update,could you please tell me , how long it would take to resolve this??
Thanks
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)
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.