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 <[email protected]> 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 <[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] >> > >
