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
Sample POI.XLSX
Description: MS-Excel 2007 spreadsheet
Sample POI.XLS
Description: MS-Excel spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
