0

Having worked with COM Interop Excel before and having the same problem, which was solved here. I now face the same issue with the EPPlus library (version 4.0.4.0).

This code should format the numbers in the outputfile:

for (int i = 10; i > 27; i += 4) {
     //worksheetOut.Cells[String.Format("{0}:{1}", i, i + 1)].Style.Numberformat.Format = null;
     worksheetOut.Cells[String.Format("{0}:{1}", i, i + 1)].Style.Numberformat.Format = "€###.###.###";
}

gives this result

I don't know how to use the solution from the question listed above, seeing that the 'only' way to set this format is by a string so I can't add culture settings.

Edit:

apparently you can't place a number-format on an entire row. I tried this code:

//range with only numbers, so no empty values or 'General' values
worksheetOut.Cells["A18:F19"].Style.Numberformat.Format = "###.###.###";

gives this result, I don't think I can change the decimal seperator to , and thousand separator to .

Community
  • 1
  • 1
Edito
  • 3,030
  • 13
  • 35
  • 67
  • 1
    You never mentioned, what *is* the problem? *You* can't change the separators, it's an Excel user setting that affects how numbers are *displayed*. In fact, your format string is wrong and probably ignored - `.` is the culture-specific decimal *placeholder*. In some cultures it's `.`, in others it will be rendered to `,`. Use `€###,###,###` if you want to specify the thousands placeholder, `€###,###,###.##` if you want to restrict decimals – Panagiotis Kanavos Sep 17 '15 at 10:02
  • 2
    Why don't you (a) set the format you want using Excel in a throwaway workbook; (b) examine the resulting `NumberFormat.Format` property; (c) use this value in your code. – Joe Sep 17 '15 at 10:02
  • 1
    The linked question has nothing to do with Excel (it's about String.Format) and if it appeared to work, it's because the Excel user had the same locale as the one used by your application, or Excel treated the cell values as text, not numbers. – Panagiotis Kanavos Sep 17 '15 at 10:07
  • @Panagiotis Kanavos, in my previous question it was possible to change the culture settings, and gave me the wanted result(this was in COM interop Excel though). I guess this is not possible in EPPlus? I've read the last comment, I see why it isn't possible then. – Edito Sep 17 '15 at 10:08
  • You misunderstood the issue in the first place. EPPlus doesn't do something different from Interop - it's the exact same Excel file format. The exact same values are written. You could use `String.Format` here as well to change from your French, German or Greek formatting to a US formatting. The Excel application though uses the decimal, thousand *AND LIST* separators according to the user's settings. That's why you need to use `;` as a separator when all documentations says that you should use `,` in formulas. – Panagiotis Kanavos Sep 17 '15 at 10:15
  • 2
    In that other question, no number format was specified and the values were treated as *text*. In this case, a number format is specified (though invalid) which means the values will be treated as *numbers*. If you try to change the regional settings of your user account, you'll see that Excel will display thousands and decimals in a different way – Panagiotis Kanavos Sep 17 '15 at 10:19

1 Answers1

1

Should be I think (remove Format from the end)

worksheetOut.Cells[String.Format("{0}:{1}", i, i + 1)].Style.NumberFormat= "€###.###.###";