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 <[email protected]>
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:[email protected]]
> Envoyé : dimanche 11 décembre 2016 16:43
> À : POI Users List <[email protected]>
> Objet : Re: POI Formulas & local languages
>
> Can you share a sample file?
>
> Thanks... Dominik
>
> On Dec 11, 2016 12:29, "Pierre MIEHE" <[email protected]> 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: [email protected]
> For additional commands, e-mail: [email protected]
>
