none
how to make a cell usable as a number when we apply a format with thousand separators ? RRS feed

Answers

  • It works by changing my stylesheet like this :

    public static Stylesheet GenerateStyleSheet()
            {
                return new Stylesheet(
                    new Fonts(
                        new Font(                                                               // Index 0 - The default font.
                            new FontSize() { Val = 11 },
                            new Color() { Rgb = new HexBinaryValue() { Value = "00000000" } },
                            new FontName() { Val = "Calibri" }),
                        new Font(                                                               // Index 1 - The bold font.
                            new Bold(),
                            new FontSize() { Val = 11 },
                            new Color() { Rgb = new HexBinaryValue() { Value = "00000000" } },
                            new FontName() { Val = "Calibri" })
                    ),
                    new Fills(
                        new Fill(                                                           // Index 0 - The default fill.
                            new PatternFill() { PatternType = PatternValues.None })
                    ),
                    new Borders(
                        new Border(                                                         // Index 0 - The default border.
                            new LeftBorder(),
                            new RightBorder(),
                            new TopBorder(),
                            new BottomBorder(),
                            new DiagonalBorder())
                    ),
                    new CellFormats(new CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 },                   // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                                    new CellFormat() { NumberFormatId = 0, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyFont = true }, // Index 1 - Bold 
                                    new CellFormat() { NumberFormatId = 4, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = true })
    
                );
            }

    And defining the content of my CellValue like this : 

    double doubleVal = 10000.12345;

    string sValue = doubleVal.ToString();

    new CellValue(sValue.Replace(',', '.'))

    I have no choice but to use a string as it is the only accepted type for CellValue.Text . The problem is that using ToString() changes 10000.12345 to "10000,12345". That's why I have to use the Replace method, otherwise my file opens with an error.

    Whether I use "cell.DataType = new EnumValue<CellValues>(CellValues.Number);" or not, it works in both cases.

    Does someone see a way to suppress the need to use the Replace method in order to achieve a better solution?

    • Marked as answer by jmclej Friday, March 27, 2015 3:44 PM
    Friday, March 27, 2015 3:42 PM

All replies

  • why are you insert a text but want to compute as a number


    • Edited by Lanlan0901 Friday, March 27, 2015 9:07 AM
    Friday, March 27, 2015 9:06 AM
  • Hello,

    Because that is the only way I have found so far to have the right format (space as a thousand separator and displaying 2 decimals).

    But indeed, I am looking for a way to have this format AND be able to have the cell considered as a number. Do you know how to do that? Maybe the stylesheet should be written differently? But I don't know how. If someone knows, thanks for telling (based on the GenerateStylesheet method shown in the other post where I gave the link above).

    Regards,

    Julien

    Friday, March 27, 2015 10:59 AM
  • It works by changing my stylesheet like this :

    public static Stylesheet GenerateStyleSheet()
            {
                return new Stylesheet(
                    new Fonts(
                        new Font(                                                               // Index 0 - The default font.
                            new FontSize() { Val = 11 },
                            new Color() { Rgb = new HexBinaryValue() { Value = "00000000" } },
                            new FontName() { Val = "Calibri" }),
                        new Font(                                                               // Index 1 - The bold font.
                            new Bold(),
                            new FontSize() { Val = 11 },
                            new Color() { Rgb = new HexBinaryValue() { Value = "00000000" } },
                            new FontName() { Val = "Calibri" })
                    ),
                    new Fills(
                        new Fill(                                                           // Index 0 - The default fill.
                            new PatternFill() { PatternType = PatternValues.None })
                    ),
                    new Borders(
                        new Border(                                                         // Index 0 - The default border.
                            new LeftBorder(),
                            new RightBorder(),
                            new TopBorder(),
                            new BottomBorder(),
                            new DiagonalBorder())
                    ),
                    new CellFormats(new CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 },                   // Index 0 - The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                                    new CellFormat() { NumberFormatId = 0, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyFont = true }, // Index 1 - Bold 
                                    new CellFormat() { NumberFormatId = 4, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = true })
    
                );
            }

    And defining the content of my CellValue like this : 

    double doubleVal = 10000.12345;

    string sValue = doubleVal.ToString();

    new CellValue(sValue.Replace(',', '.'))

    I have no choice but to use a string as it is the only accepted type for CellValue.Text . The problem is that using ToString() changes 10000.12345 to "10000,12345". That's why I have to use the Replace method, otherwise my file opens with an error.

    Whether I use "cell.DataType = new EnumValue<CellValues>(CellValues.Number);" or not, it works in both cases.

    Does someone see a way to suppress the need to use the Replace method in order to achieve a better solution?

    • Marked as answer by jmclej Friday, March 27, 2015 3:44 PM
    Friday, March 27, 2015 3:42 PM