poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Murphy <jmarkmur...@gmail.com>
Subject Re: POI Formulas & local languages
Date Tue, 13 Dec 2016 02:26:43 GMT
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