We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copy an Excel "regional settings-specific" Cell's Value in a Word document using Aspose

Hello,

We’ve encounterd some dificulties in performing the following operation using Aspose - we want to get an Excel Cell’s value (as calculated based on a specific regional setting) and paste it in a Word document paragraph.

I’ve attached a small console application describing this scenario. TestFile.xlsx has a on Sheet1!A1 a datetime which uses the “long date” formatting; this will display different when using different regional settings, e.g. in English the value will display “Thursday, January 20, 1944”, in Hindi (hi-IN culture) it will display 20 जनवरी 1944.

We’ve seen Aspose’s Cell.StringValue correctly computes to the “20 जनवरी 1944” value, but when pasting this into a Word Run the “20 जनवरी 1944” characters will not be correctly displayed by Office, see the Generated.docx as generated by the console app - it seems having just 1 Run containing this value is not enough.

If you paste this Hindi text directly in Word, Office will produce a paragraph containing 3 Runs, the one for the “troubled” जनवरी characters using the “Mangal” font - attached the HindiTextDirectlyPastedInWord.docx describing this.

I’ve looked further In Aspose.Cells but could not found a way to get the Excel cell’s value as splitted in 3, with the corresponding usage of the “Managal” font for the middle part, in order to be able to correctly import the calculated value in a Word document. Nor a way to identify that a substring of the Cell’s StringValue is to be displayed using a different encoding. E.g. Cell.GetStyle().Font or Cell.GetDisplayStyle().Font will result in Calibri, cell.GetCharacters() returns NULL.

Is there a way to achieve our task with Aspose ? If not at the moment, can you tell us when we could get such a support from Aspose ?

We are using Aspose.Cells version 8.0.1.0, Aspose.Words version 14.3.0.0.

Any feedback will be highly appreciated.

Thank you,
Mihai Andrei
Sr Software Engineer
IBM Romania

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

It seems to be MS-Excel run time behavior. It selects font Mangal to display जनवरी. I think, this information is not stored in Cell Style.

However, we have logged an investigation ticket in this regard. We will look into it and see if any such information is stored inside the xlsx file or Aspose.Cells could be used to find out this information.

Once, there is some update for you regarding this, we will let you know asap.

This issue has been logged as CELLSNET-42658.

I have attached the screenshots relating to this issue and the Excel 2013 generated pdf for a reference.

Hi,

Thanks for using Aspose.Cells.

We have looked into this issue further and found that it is MS Excel’s behavior when displaying cell contents. User may
specify one font for a cell or the cell can inherit one font from the
default style of the workbook. When characters in the cell content
cannot be displayed by the specified font, MS Excel will choose another
available font which can display those characters automatically. This
process is necessary for UI components to show contents correctly.


However, it is unnecessary for our component for most situations,
especially when taken performance into consideration because this
process (check every character and search proper font for those
characters that cannot be displayed by the specified one) is quite
time-consuming.

We may consider to provide some methods in future to
simulate this process and provide user the expected result as what be
shown in MS Excel, but it is a complicated process and we are afraid it
cannot be available soon.

Hello,

Thank you for your feedback.

In terms of performance, I am guessing there could be a simpler solution: as you already correctly resolve the formula based on the underalying Encoding, maybe you can store this kind of information during the formula calculation, e.g. to store what parts from the Cell.StringValue were “localized” and are using a different Encoding. Having these associations available after formula calculation, finding a suitable font to display it should be then simpler… I am just guessing, not sure this assumption is correct as per the way the Aspose is designed/implemented.

We really need this kind of method from Aspose, so we will wait for it, hopefully you will cover it in a reasonable amount of time.

Thank you,
Mihai Andrei

Hi,


In fact it is not only the issue of calculated
result, even for a simple cell value, you can input any characters as you like.
Such as, see the below code sample:

e.g

Sample code:

Cell cell = cells[“A1”];

cell.PutValue("20 जनवरी 1944 中");

style.Font.Name=”Calibri”;

cell.SetStyle(style);

For your situation, maybe you need the string value of this cell to be split into 4 runs. To support that we also have to check all characters one by one and try many fonts to find available one. If this process is made as automatic, the time cost is sure to be considerable.

Thanks for your understanding!