poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Javen O'Neal" <one...@apache.org>
Subject Re: POI Formulas & local languages
Date Tue, 13 Dec 2016 05:03:03 GMT
In Excel with the locale set to English, entering the formula PRIX.TITRE
gives a NAME error. Additionally, opening the sample XLS workbook in
English Excel results in a NAME error when reevaluating the PRIX.TITRE
formula.

Therefore, this name is not reserved (in an English locale workbook) and
could be used by a macro.

To have the same behavior as Excel, POI would need to be told the locale
(either from information in the XLS file or by setting the locale from Java
code) so that it could translate the formula names.

https://support.office.com/fr-fr/article/PRIX-TITRE-PRIX-TITRE-fonction-3ea9deac-8dfa-436f-a7c8-17ea02c21b0a

On Dec 12, 2016 6:27 PM, "Mark Murphy" <jmarkmurphy@gmail.com> wrote:

Dominick, do the formulas still work in the English locale?

If so, it is possible that we could translate the formula names by adding a
translation table. Somehow we would have to find out all the locale
specific names of each function to do that though.

On Mon, Dec 12, 2016 at 5:41 AM, Dominik Stadler <dominik.stadler@gmx.at>
wrote:

>
> Hi,
>
> it seems this is a limitation of the Excel XLS format, because in this
> case Excel actually stores the french names of the functions as part of
the
> formulas for XLS. When I open this on my English-locale-Windows, I still
> get the french names in Excel for the XLS:
>
> [image: Inline image 1]
>
>
> When using the dev-tool BiffBiewer to print out the binary-format contents
> of the XLS, I get
>
> Offset=0x000033A4(13220) recno=303 sid=0x0023 size=0x0016(22)
> [EXTERNALNAME]
>     .options      = 0
>     .ix      = 0
>     .name    = PRIX.TITRE
> org.apache.poi.ss.formula.ptg.ErrPtg [#REF!].
> [/EXTERNALNAME]
>
> Offset=0x000033BE(13246) recno=304 sid=0x0023 size=0x001A(26)
> [EXTERNALNAME]
>     .options      = 0
>     .ix      = 0
>     .name    = DUREE.MODIFIEE
> org.apache.poi.ss.formula.ptg.ErrPtg [#REF!].
> [/EXTERNALNAME]
>
>
>
> So there is probably not much that Apache POI can do differently here
> unless you convince Excel to store the formulas differently somehow.
>
> Dominik.
>
> On Sun, Dec 11, 2016 at 5:02 PM, Pierre MIEHE <miehe.pierre@gmail.com>
> wrote:
>
>> Dear Dominik,
>>
>> Sure, please find attached a sample xlsx file (for which CellFormula with
>> XSSF wb gives the English name of the function) and the same file on xls
>> format (for which CellFormula with HSSF wb gives the Local name of the
>> function - for me French).
>> The cells to look at are M3 and O3.
>>
>> The code used (in C# with NPOI) is the following (simplified version):
>>
>> "
>>         public static string BuildXmlModelFromExcel(string
>> FilePathExcelModel)
>>         {
>>
>>         var fileExt = Path.GetExtension(FilePathExcelModel);
>>                 FileStream fileInputStream = new
>> FileStream(FilePathExcelModel,
>>                 FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
>>
>>                 HSSFWorkbook hssfwb = null;
>>                 XSSFWorkbook xssfwb = null;
>>
>>         if (fileExt == ".xls")
>>                 {
>>                 hssfwb = new HSSFWorkbook(fileInputStream);
>>                 sheet = hssfwb.GetSheetAt(0);                   }
>>             else
>>             {
>>                xssfwb = new XSSFWorkbook(fileInputStream);
>>         sheet = xssfwb.GetSheetAt(0);
>>             }
>>         String FormulaDuration = sheet.GetRow(2).GetCell(12).CellFormula;
>>         String FormulaPrice =  sheet.GetRow(2).GetCell(14).CellFormula;
>>         Return FormulaDuration+" / "+FormulaPrice;
>>          }
>> "
>>
>> Thank you!
>>
>> Pierre
>>
>> Pierre MIEHE
>> Actuary IA
>> Tel.: +33 (0)6 10 40 68 91
>> Linkedin: https://fr.linkedin.com/in/pierremiehe
>> Skype: pierre.miehe1
>> Twitter: https://twitter.com/miehepro
>>
>> -----Message d'origine-----
>> De : Dominik Stadler [mailto:dominik.stadler@gmx.at]
>> Envoyé : dimanche 11 décembre 2016 16:43
>> À : POI Users List <user@poi.apache.org>
>> Objet : Re: POI Formulas & local languages
>>
>> Can you share a sample file?
>>
>> Thanks... Dominik
>>
>> On Dec 11, 2016 12:29, "Pierre MIEHE" <miehe.pierre@gmail.com> wrote:
>>
>> > Dear all,
>> >
>> >
>> >
>> > I am having an issue using POI to read formulas coming from XLS files,
>> > when they use functions of the Financial pack from Excel like PRICE
>> > and MDURATION.
>> >
>> > Indeed using CellFormula I get with XLSX file (XSSFWorkbook) the
>> > correct
>> > spelling: PRICE and MDURATION.
>> >
>> > But with XLS files (HSSFWorkbook) I get the local version of the
>> > function
>> > names: e.g. DUREE.MODIFIEE and PRIX.TITRE (in French).
>> >
>> > How could I get the English version of the formula with HSSFWorkbooks?
>> >
>> >
>> >
>> > Many thanks in advance for your help and best wishes,
>> >
>> >
>> >
>> > Pierre
>> >
>> >
>> >
>> > Pierre MIEHE
>> >
>> > Actuary IA
>> >
>> > Tel.: +33 (0)6 10 40 68 91
>> >
>> > Linkedin:  <https://fr.linkedin.com/in/pierremiehe>
>> > https://fr.linkedin.com/in/pierremiehe
>> >
>> > Skype:  <skype:pierre.miehe1?add> pierre.miehe1
>> >
>> > Twitter:  <https://twitter.com/miehepro> https://twitter.com/miehepro
>> >
>> >
>> >
>> >
>> >
>> > ---
>> > L'absence de virus dans ce courrier électronique a été vérifiée par le
>> > logiciel antivirus Avast.
>> > https://www.avast.com/antivirus
>> >
>>
>>
>> ---
>> L'absence de virus dans ce courrier électronique a été vérifiée par le
>> logiciel antivirus Avast.
>> https://www.avast.com/antivirus
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message