Free Support Forum - aspose.com

Leading apostrophe when using datasources

I am using smartmarkers and the WorkbookDesigner to add data into a excel workbook.

when i add a data element with a string property this can contain a leading apostrophe.

like a name in the netherlands could be like… --> 't hart

when this gets injected into excel the leading apostrophe will be used a an indicator that the value should be considered as string.

but in excel the apostrophe will not be visible… even formula’s will ignore this first character.

question can you indicate that the leading apostrophe should be used as a element of the value.

workaround:
i can iterate over the data and add an additional apostrophe but i don’t want to go in that direction (performance)

is there a solution for this?

@Pietswieb,
Please share your sample file and runnable code snippet (preferably console application) with us for our testing. We will reproduce the problem and provide our feedback after analysis.

@Pietswieb,
We have investigated the issue more and would like share that there seems to be no other workaround to cope with this requirement other than the one which you have devised in your post. If we enter "'s", we will get "s" and if we enter "''s", we will get"'s" similar to the MS Excel.

Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

ok thanks for the feedback. I will implement this workaround. Though I think it will be handy to have some settings on the datasources to have this fix generally.

Thanks…

@Pietswieb,
Let us know your feedback after implementing this and we will check further if some general setting can be used in API.

if have implemented the workarround and it works for now…

what i would like is to have a setting on the data so on setting the datasource to indicate that some characters should be escaped…

i can imagine that there are some more sophisticated solutions…

eg:

 // Instantiating a WorkbookDesigner object
WorkbookDesigner designer = new WorkbookDesigner();

// Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(designerFile);

// Set the data source for the designer spreadsheet
designer.SetDataSource(dataset, escapeApostrophe = true);

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

@Pietswieb,
Thank you for the feedback. Regarding this new feature, could you please perform the same task using MS Excel and share the steps with us. We will reproduce the scenario here and provide assistance to perform the same task using Aspose.Cells.

@Pietswieb,
We have investigated this bit more, if you enter a string value in a cell starting with apostrophe, the first apostrophe will be removed and Style.QuotePrefix property will be true. So you can check if cell value contains apostrophe with the codes:

if(cell.GetStyle().QuotePrefix)
{
....
}

If you need further help then share template file and code to create dataset as well as the steps with MS Excel requested above.

[quote=“ahsaniqbalsidiqui, post:8, topic:205522”]
QuotePrefix
[/quote]’

yes this will help me identifying which items there are with a single quote…

  • but i cannot tell if they came from a smartmarker or not
  • i have to iterate through all the data myself to add one again… reason i want to have the cell showing the actual name.

i added a sample with the fault, workaround and prefered implementation. Reason for that is that you have to iterate over the data already so checking the characters and escape them is most handy to do there

POC_Aspose_Datasource_Apostrophe.zip (587.1 KB)

@Pietswieb,
We have analysed your requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix (if possible). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNETCORE-45 – Load data from datasource with option to skip some character like Apostrpohe

@Pietswieb,
We will add Workbook.Settings.QuotePrefixToStyle property to indicate whether setting Style.QuotePrefix property.

@Pietswieb.

This is to inform you that your issue is resolved now and we will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

@Pietswieb,
Please try our latest version/fix: Aspose.Cells for .NET v19.11.6:
Aspose.Cells19.11.6 For .NetStandard20.Zip (4.1 MB)

Your issue should be fixed in it.

Let us know your feedback.

when i tried to use the new version in my solution it did’t work because of missing files

Message: Test method POC_Aspose_Datasource_Apostrophe.UnitTest1.Test2 threw exception:
System.TypeInitializationException: The type initializer for ‘Aspose.Cells.Workbook’ threw an exception. —> System.IO.FileNotFoundException: Could not load file or assembly ‘System.Text.Encoding.CodePages, Version=4.1.2.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’. Het systeem kan het opgegeven bestand niet vinden.

@Pietswieb,
I have tried this fixed version by adding its reference manually and no issue is observed as the solution is compiled fine without any error. Please give a try to the complete solution shared for your reference and provide the feedback.
POC_Aspose_Datasource_Apostrophe (2).zip (4.9 MB)

it will compile fine but when executing the new WorkbookDesigner() it fails…

@Pietswieb,
I have prepared a new independent console application based project which is attached here. When you add reference to .NetStandard version of Aspose.Cells manually, you should also add reference to the following libraries as mentioned in the dependencies section for .NetStandard 2.0 here:
System.Drawing.Common
System.Text.Encoding.CodePages

A working project is attached here for your reference.
Screenshot 2019-12-06 at 6.50.45 AM.png (411.3 KB)
TestConsoleApp.zip (5.6 MB)

i have tested it and it works… Thanks :slight_smile:

@Pietswieb,

Good to know that your issue is sorted out now. 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.